MySQL存儲引擎和表類型分析

MYSQL支持數個存儲引擎作為對不同表的類型的處理器。

mysql> SHOW ENGINES;
+------------+---------+--------------------------------------------------------
--------+
| Engine | Support | Comment
|
+------------+---------+--------------------------------------------------------
--------+
| MyISAM | YES | Default engine as of MySQL 3.23 with great performance
|
| MEMORY | YES | Hash based, stored in memory, useful for temporary tabl
es |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign k
eys |
| BerkeleyDB | NO | Supports transactions and page-level locking
|
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disa
ppears) |
| EXAMPLE | NO | Example storage engine
|
| ARCHIVE | YES | Archive storage engine
|
| CSV | NO | CSV storage engine
|
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables
|
| FEDERATED | NO | Federated MySQL storage engine
|
| MRG_MYISAM | YES | Collection of identical MyISAM tables
|
| ISAM | NO | Obsolete storage engine
|

選擇如何存儲和檢索你的數據的這種靈活性是MySQL為什麼如此受歡迎的主要原因。其它數據庫系統(包括大多數商業選擇)僅支持一種類型的數據存儲。遺憾 的是，其它類型的數據庫解決方案採取的「一個尺碼滿足一切需求」的方式意味著你要麼就犧牲一些性能，要麼你就用幾個小時甚至幾天的時間詳細調整你的數據 庫。使用MySQL，我們僅需要修改我們使用的存儲引擎就可以了。

默認存儲引擎

MYSQL的默認存儲引擎為：MyISAM，除非我們顯示的指定存儲引擎。如下例：

mysql> CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB;
Query OK, 0 rows affected (0.83 sec)

為了做出選擇哪一個存儲引擎的決定，我們首先需要考慮每一個存儲引擎提供了哪些不同的核心功能。這種功能使我們能夠把不同的存儲引擎區別開來。我們一般把 這些核心功能分為四類:支持的字段和數據類型、鎖定類型、索引和處理。一些引擎具有能過促使你做出決定的獨特的功能。

下邊我們詳細講述一下MYSQL各個存儲引擎：

MyISAM

每個MyISAM在磁盤上存儲成三個文件。第一個文件的名字以表的名字開始，擴展名指出文件類型。.frm文件存儲表定義。數據文件的擴展名為.MYD (MYData)。索引文件的擴展名是.MYI (MYIndex)。

MyISAM引擎是大多數MySQL安裝程序的默認引擎，起源於早期版本MySQL支持的ISAM引擎。這種引擎提供了最佳的性能和功能的組合，儘管它缺少事務處理功能(使用InnoDB或者BDB引擎)並且使用表級鎖定。

但是執行一下查詢發現，我在測試的時候使用的兩個測試表在創建的時候沒有指定引擎，但是發現這兩個表的存儲引擎都為InnoDB。（當然我們修改配職文件my.ini中的default-storage-engine=INNODB來修改）。

mysql> select table_name,engine from tables where table_name like 'test%';
+------------+--------+
| table_name | engine |
+------------+--------+
| test | InnoDB |
| test1 | InnoDB |
+------------+--------+
2 rows in set (0.08 sec)

MYSQL文檔的解釋是：「存儲引擎和表類型:當MySQL被用MySQL配置嚮導安裝在Windows平台上，InnoDB存儲引擎替代MyISAM存儲引擎作為替代。 」


為了測試MyISAM引擎表級鎖定，我們是用MyISAM引擎創建測試表TEST_ISAM。

測試中打開兩個數據庫連接，一個連接執行call p_tst_isam();另外一個執行單條mysql> insert into test_isam(id,mc) values(1,'1');結果在第一個連接還沒有執行完的時候，第二個就完畢，沒有發現MyISAM引擎鎖表，這個問題我們暫時不再繼續測試下去。測 試中發現一個問題，MyISAM引擎的表的INSERT速度遠遠大於InnoDB引擎：

mysql> CREATE TABLE TEST_ISAM(ID INTEGER,MC VARCHAR(60)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.38 sec)

mysql> select table_name,engine from information_schema.tables where table_name like 'test%';
+------------+--------+
| table_name | engine |
+------------+--------+
| test | InnoDB |
| test1 | InnoDB |
| test_isam | MyISAM |
+------------+--------+
3 rows in set (0.00 sec)

創建存儲過程p_test_isam

delimiter //
create procedure p_test_isam()
begin
declare counter int;
set counter = 1000000;
while counter >= 1 do
insert into test_isam(id,mc) values(counter,'test');
set counter = counter - 1;
end while;
end
//

delimiter ;

我們在以前的測試例子中： InnoDB引擎 INSERT 1000條數據花費34秒

mysql> call p_test();
Query OK, 1 row affected (34.48 sec)

MyISAM引擎INSERT 1000000 條數據花費時間20多秒：

mysql> call p_test_isam();
Query OK, 1 row affected (22.95 sec)

所以，我們如果在使用非事物處理的表(也就是一些只有單用戶使用的表)的時候可以採用MyISAM引擎來提高速度，當然了，INSERT的時候可以利用 MYSQL的BULK INSERT功能來出也是能大大提高性能的，這些我們將在MYSQL數據庫優化一

INSERT INTO TEST VALUES(VAL11,VAL12),(VAL21,VAL22)……

測試完畢，翻看一下MYSQL文檔，的確有下面一段話，和我們的測試結果吻合。

MyISAM管理非事務表。它提供高速存儲和檢索，以及全文搜索能力。MyISAM在所有MySQL配置裡被支持，它是默認的存儲引擎，除非你配置MySQL默認使用另外一個引擎。

InnoDB存儲引擎

InnoDB給MySQL提供了具有提交、回滾、崩潰恢復能力的事務安全（ACID兼容）存儲引擎。InnoDB鎖定在行級並且也在SELECT語句提供 一個Oracle風格一致的非鎖定讀。這些特色增加了多用戶部署和性能。沒有在InnoDB中擴大鎖定的需要，因為在InnoDB中行級鎖定適合非常小的 空間。InnoDB也支持FOREIGN KEY強制。在SQL查詢中，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來，甚至在同一個查詢中也可以混合。

InnoDB是為處理巨大數據量時的最大性能設計。它的CPU效率可能是任何其它基於磁盤的關係數據庫引擎所不能匹敵的。

InnoDB存儲引擎被完全與MySQL服務器整合，InnoDB存儲引擎為在主內存中緩存數據和索引而維持它自己的緩衝池。InnoDB存儲它的表＆索 引在一個表空間中，表空間可以包含數個文件（或原始磁盤分區）。這與MyISAM表不同，比如在MyISAM表中每個表被存在分離的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制為2GB的操作系統上。

InnoDB默認地被包含在MySQL二進制分發中。Windows Essentials installer使InnoDB成為Windows上MySQL的默認表。

InnoDB被用來在眾多需要高性能的大型數據庫站點上產生。著名的Internet新聞站點Slashdot.org運行在InnoDB上。 Mytrix, Inc.在InnoDB上存儲超過1TB的數據，還有一些其它站點在InnoDB上處理平均每秒800次插入/更新的負荷。

給出一個事務控制的例子：

mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.30 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A | B |
+------+--------+
| 10 | Heikki |
+------+--------+
1 row in set (0.00 sec)

InnoDB存儲引擎相關的內容非常複雜涉及到事物處理、日誌、備份和恢復、鎖定、多版本、性能、表和索引的結構、磁盤IO等很多方面的知識。

我們以上只講述了MYSQL的幾個存儲引擎，使我們能夠對MYSQL的存儲引擎有個基本的認識。MYSQL還提供了BDB (BerkeleyDB)存儲引擎、FEDERATED存儲引擎、ARCHIVE存儲引擎、CSV存儲引擎、BLACKHOLE存儲引擎等，這裡就不再詳 細說明了。