MySQL hash index VS Btree index-白红宇

MySQL AHI(adaptive hash index)：没有牺牲任何的事物特点和可靠性;

根据搜索的匹配模式，MySQL会利用 B-Tree index key 前半部分（利用btree index 所能找到的部分）长度任意建立hash index。hash index根据需求只对访问频率较高的page中的index建立hashindex

如果一个表的数据全部在内存里面，hash index可以加快查询速度；innodb本身有监控index 查询频率的机制；通过hash index提高查询的性能远远高于监控index查询频率和维护 hash index结构开销。对于多并发连接的情况， read/write lock 会对hash index 造成竞争锁，当有部分 where column like 匹配模式的情况下，自适应哈希是不适合的，建议关闭。这种情况也很难预测是否这样的特性适合这样特殊的应用的场景。

对于innodb adaptive hash 部分的使用情况：

可以使用 show engine innodb status\G 中 SEMAPHORES部分查看，如果有很多thread处于 waiting on an RW-latch created in btr0sea.c，这个时候关闭自适应哈希是比较合适的。

B-Tree 和 Hash index 的比较：

了解 B-Tree 和 Hash 的数据结构对于我们预测在不同存储引擎上查询（利用使用这些结构index）的性能是很有帮助的，特别对于memory 存储引擎；

B-Tree index的特点：

B-tree 索引可以用于进行 =,>,>=,<,<= 和 between的计算，同样可以用于 like 匹配模式的查询；

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';（可以使用索引）

SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';（也可以使用索引）

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';（不会使用索引）

对于第三个查询语句，MySQL会使用Turbo Boyer-Moore 算法来初始化这个pattern，来更快的进行查找。

如果某个子都 col_name被索引， col_name is null 会采用索引。

btree 索引倾向于最左原则，尤其是在where and条件中，第一个字段是必须要引用上的；

以下例句是会用上索引的：

WHERE index_part1=1 AND index_part2=2 AND other_column=3

WHERE index=1 OR A=10 AND index=2 （能够使用index1 或者 index2）

WHERE index_part1='hello' AND index_part3=5

WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;（可以用上index1，但不一定能用上 index2,index3）

下面的例句不会使用上索引：

WHERE index_part2=1 AND index_part3=2；（index_part1 不会被用上）

WHERE index=1 OR A=10 （不会使用索引）

WHERE index_part1=1 OR index_part2=10（不会使用索引）

特殊情况：有些情况MySQL是不会使用索引的，尽管被查询字段有索引，

当MySQL optimizer estimates (优化模型)使用索引会扫描大部分的rows，这种情况下 table scan 全表扫描可能会因为更少的查找来降低成本。此时如果使用limit 语句，来索取一定量的rows这样会使用上索引。

hash index特征：

只能进行等值运算，不能进行< 或者范围查找运算，这种比较适合 kv类型的数据，

对于order by 语句是不能使用hash key的，可以在程序中搞定。

MySQL 不能确定大致在两个values 之间到底还有多少rows。对于myisam 转换为memory引擎的情况需要注意。

索引的键值必须全部用上，不能像btree那样只使用前半部分。

本文转自位鹏飞 51CTO博客，原文链接：http://blog.51cto.com/weipengfei/1211579，如需转载请自行联系原作者