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那样只使用前半部分。
转载于:https://blog.51cto.com/weipengfei/1211579