警惕 InnoDB 和 MyISAM 建立 Hash 索引陷阱，innodbmyisam
MySql 常見儲存引擎 InnoDB 和 MyISAM 都不支援 Hash 索引，它們預設的索引都是 B-Tree。但是如果你在建立索引的時候定義其類型為 Hash，MySql 並不會報錯，而且你通過 SHOW CREATE TABLE 查看該索引也是 Hash，只不過該索引實際上還是 B-Tree。
比如表 data_dict 的 DDL：
CREATE TABLE `data_dict` ( `data_type` varchar(32) NOT NULL COMMENT '資料字典類型', `data_code` tinyint(4) NOT NULL COMMENT '資料字典代碼', `data_name` varchar(64) NOT NULL COMMENT '資料字典值', PRIMARY KEY (`data_type`,`data_code`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='資料字典表';
我們為 data_name 欄位建立 Hash 索引：
ALTER TABLE data_dict ADD INDEX data_dict_dn USING HASH (data_name);
列印結果：
受影響的行: 0
時間: 0.345s
然後查看建表 DDL：
SHOW CREATE TABLE data_dict;
列印結果：
CREATE TABLE `data_dict` (
`data_type` varchar(32) NOT NULL COMMENT '資料字典類型',
`data_code` tinyint(4) NOT NULL COMMENT '資料字典代碼',
`data_name` varchar(64) NOT NULL COMMENT '資料字典值',
PRIMARY KEY (`data_type`,`data_code`),
KEY `data_dict_dn` (`data_name`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='資料字典表'
是 Hash，所以我們以為建立 Hash 索引成功。
事實上並非如此，我們都被 MySql 給騙了，我們使用 SHOW INDEXES FROM 語句對該表索引進行檢索：
SHOW INDEXES FROM data_dict;
列印結果：
打回原形了。不過也不要失望，雖然常見儲存引擎並不支援 Hash 索引，但 InnoDB 有另一種實現方法：自適應雜湊索引。InnoDB 儲存引擎會監控對錶上索引的尋找，如果觀察到建立雜湊索引可以帶來速度的提升，則建立雜湊索引。
我們可以通過 SHOW ENGINE INNODB STATUS 來查看當前自適應雜湊索引的使用狀況：
=====================================
2015-07-07 10:51:19 1d68 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 36 seconds
......
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 2633, seg size 2635, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 348731, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
......
從中我們可以看到自適應雜湊索引的相關資訊：有使用大小、使用方式、每秒使用自適應雜湊索引搜尋的情況等。
MySql 各種儲存引擎的特性對比詳單：
從中我們可以看出，
- InnoDB 支援事務，支援行層級鎖定，支援 B-tree、Full-text 等索引，不支援 Hash 索引；
- MyISAM 不支援事務，支援表層級鎖定，支援 B-tree、Full-text 等索引，不支援 Hash 索引；
- Memory 不支援事務，支援表層級鎖定，支援 B-tree、Hash 等索引，不支援 Full-text 索引；
- NDB 支援事務，支援行層級鎖定，支援 Hash 索引，不支援 B-tree、Full-text 等索引；
- Archive 不支援事務，支援表層級鎖定，不支援 B-tree、Hash、Full-text 等索引；
可以使用 SHOW ENGINES 語句查看你的 MySql Server 所支援的儲存引擎，比如筆者用於本機測試的 5.6.25-log Win 版的查看結果如下：
從中可以看出，InnoDB 是該版本 MySql 的預設儲存引擎，也只有 InnoDB 能夠支援事務、行層級鎖定、外鍵；支援的 MEMORY 是基於雜湊的，資料都存放於記憶體，適用於暫存資料表。沒有看到既支援事務又支援雜湊索引的 NDB 的身影。
參考資料
- http://dev.mysql.com/doc/refman/5.6/en/storage-engines.html
- http://dba.stackexchange.com/questions/2817/why-does-mysql-not-have-hash-indices-on-myisam-or-innodb
著作權聲明：本文為博主原創文章，未經博主允許不得轉載。
