mysql btree与hash索引

btree索引

        btree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索，根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找,通过比较节点页的值和要查找的值可以找到合适的指针进入下一层子节点，这些指针实际上定义了子节点页中值的上限和下限，最终存储引擎要么是找到对应的值，要么是该记录不存在。

        叶子节点比较特别，他们的指针指向的是被索引的数据，而不是其他的节点页（不同的引擎指针类型不同），其实在根节点与叶子节点之间可能有很多层节点页，树的深度和表的大小直接相关。

        btree树索引列是顺序组织存储的，所以很适合查找范围数据 !

        注意：索引对多个值进行排序的依据是create table语句中定义索引时的列顺序 !

        可以使用btree索引的查询类型，btree索引使用用于全键值、键值范围、或者键前缀查找，其中键前缀查找只适合用于根据最左前缀的查找。前面示例中创建的多列索引对如下类型的查询有效：
    A：全值匹配 : 全值匹配指的是和索引中的所有列进行匹配
    B：匹配最左前缀 : 即只使用索引的第一列
    C：匹配列前缀 : 也可以只匹配某一列值的开头部分
    D：匹配范围值 : 只使用索引的第一列进行区间匹配
    E：精确匹配某一列并范围匹配另外一列 : 第一列精确匹配，第二列范围匹配
    F：只访问索引的查询 : btree通常可以支持只访问索引的查询，即查询只需要访问索引，而无需访问数据行，即，这个就是覆盖索引的概念。需要访问的数据直接从索引中取得。

        因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的order by操作，一般来说，如果btree可以按照某种方式查找的值，那么也可以按照这种方式用于排序，所以，如果order by子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

下面是关于btree索引的限制：

    A：如果不是按照索引的最左列开始查找的，则无法使用索引(注意，这里不是指的where条件的顺序，即where条件中，不管条件顺序，只要where中出现的列在多列索引中能够从最左开始连贯起来就能使用到多列索引)

    B：不能跳过索引中的列，如果查询条件为不连续列，跳过了中间列，则多列索引只能使用到第一列
    C：如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询

HASH 索引

        基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列的值计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码不一样，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

        在mysql中，只有memory引擎显式支持哈希索引，这也是memory引擎表的默认索引类型，memory也支持btree，值得一提的是，memory引擎是支持非唯一哈希索引的。在数据库世界里是比较与众不同，如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

每个槽的编号是顺序的，但是数据行不是顺序的 !

        因为索引自身只需要存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快，然而，哈希索引也有限制，如下：
    A：哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行（即不能使用哈希索引来做覆盖索引扫描），不过，访问内存中的行的速度很快（因为memory引擎的数据都保存在内存里），所以大部分情况下这一点对性能的影响并不明显。
    B：哈希索引数据并不是按照索引列的值顺序存储的，所以也就无法用于排序
    C：哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引的全部列值内容来计算哈希值的。如：数据列（a,b）上建立哈希索引，如果只查询数据列a，则无法使用该索引。
    D：哈希索引只支持等值比较查询，如：=,in(),<=>(注意，<>和<=>是不同的操作)，不支持任何范围查询（必须给定具体的where条件值来计算hash值，所以不支持范围查询）。
    E：访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
    F：如果哈希冲突很多的话，一些索引维护操作的代价也很高，如：如果在某个选择性很低的列上建立哈希索引（即很多重复值的列），那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应的引用，冲突越多，代价越大。

        从上面描述可知，哈希索引只适合某些特定的场景，而一旦适合哈希索引，则它带来的性能提升非常明显，除了memory引擎外，NDB引擎也支持唯一哈希索引，且NDB存储引擎中作用非常特殊，但这里不讨论。

        innodb引擎有一个特殊的功能叫做自适应哈希索引，当innodb注意到某些索引值被使用的非常频繁时，它会在内存中基于btree索引之上再创建一个哈希索引，这样就让btree索引也具有哈希索引的一些优点，比如：快速的哈希查找，这是一个全自动的，内部的行为，用户无法控制或者配置，不过如果有必要，可以选择关闭这个功能（innodb_adaptive_hash_index=OFF，默认为ON）。