mysql索引类型

mysql索引类型

InnoDB存储引擎中使用的是B+Tree索引。

B+TREE索引

b+tree索引：

存储引擎从根节点开始搜索，根节点的槽中存放了指向子节点的指针，存储引擎根据指针向下层查找。通过比较节点页的值和要查找的值，可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中的值的上限和下限。

 

b+tree索引树特点：

1、b+tree索引树由根节点、分支节点、叶子节点组成。

2、在根节点和叶子节点中间可能有多层节点页。树的深度和表的大小有直接关系。

 

B-Tree和B+Tree索引的区别：

1、B+Tree索引的非叶子节点不存储data，所以可以存储更多的key，从而减小了树的高度，从而减少了磁盘的IO次数。

2、添加了指向相邻叶节点的指针，形成了带有顺序访问指针的B+Tree，这样做是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

 

下图是某个子节点及其叶节点的图。

 

B-Tree索引的查询类型（同时也适用于order by/group by /distinct）：

1、全值匹配：和索引中的所有列进行匹配

2、匹配最左前缀：只使用索引的第一列

3、匹配列前缀：匹配某一列的开头部分

4、匹配范围值

5、精确匹配某一列并范围匹配另一列

6、只访问索引的查询：查询只需访问索引，不需访问数据行。

 

B-Tree索引的限制：

以key(c1,c2,c3)索引为例

1、如果不是按照索引的最左列查找，则无法使用索引。比如无法使用索引查找c2='111'

2、无法使用索引查找c1列后缀是某个值的行

3、不能跳过索引中的列：即无法使用索引查找c1='111' and c3='222'的行

4、如果查询中有某个列的查询范围，则其右边所有列都无法使用索引查询。例如：c1='111' and c2 like '%2' and c3='333'，这个查询只会使用索引的前两列。

 

哈希索引

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

 

哈希索引的特点：

1、如果多个列的哈希值相同，索引会以链表的形式存放多个记录指针到同一个哈希条目中。

2、索引自身只需要存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查询的速度非常快。

 

哈希索引的限制：

1、哈希索引只包含哈希值和行指针，所以不能使用索引中的值来避免读取行。

2、哈希索引并不按照索引值顺序存储，所以索引无法用于排序。

3、不支持部分索引列匹配查找。因为是根据索引列的所有值来计算哈希值的。

4、只支持等值比较，包括=、in()、<=>

5、可能会出现哈希冲突，这时存储引擎需要遍历链表中的所有指针，逐行进行比较，直到找到符合条件的行。哈希冲突过多，也会付出很大代价。

 

InnoDB存储引擎的自适应哈希索引：

当InnoDB注意到某些索引值被使用的非常频繁时，会在基于B-Tree索引之上创建哈希索引，即使用哈希值而不是键本身进行索引查找。自适应哈希索引使用于索引列的值非常长（比如是一个url）时，但是需要提高查询的速度。

 

创建自适应哈希索引：

1、表中增加一列，这一列的值为B-Tree索引列的哈希值。此列哈希值需要手工维护，可以使用触发器的方式维护。

2、需要做的是在where子句中手动指定哈希函数。如：where  a_crc=CRC32('111')  and  a='111' （在查询条件中需要有一个常量值，当哈希后的值一样时，可以根据常量值进行查找）

3、不要用MD5()和SHA1()哈希函数，因为其设计的目的是最大限度消除冲突，哈希串会非常长，这样会降低查询的效率。可以用CRC32()和FNV64()