MySQL hash index VS Btree index

zhtv

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那样只使用前半部分。

posir

我妈常说,我们家要是没有电话就不会这么穷。

cqlinx

解释就是掩饰，掩饰就是编故事!

wind-cold

人生自古谁无死，啊个拉屎不用纸!

ouzhoudijie

学习了，不错，讲的太有道理了

515439429

生我之前谁是我，生我之后我是谁？

dream789

饭在锅里,我在床上*^_^*