MySQL-索引

索引

1、索引基础

• 索引在MySQL中也叫做键（key），是存储引擎用于快速找到记录的一种数据结构

• 索引对于良好的性能非常关键，索引应该是对查询性能优化最好的手段了，索引能够轻易将查询性能
  提高好几个数量级。

• 索引是存储引擎层实现的，一个索引可以包含多个列的值，如果索引包含多个列那么列的顺序也很重要

2、索引的分类

2.1从功能来分类

• 普通索引：仅加速查询

• 唯一索引：加速查询 + 列值唯一（可以有null）

• 主键索引： 加速查询 + 列值唯一（不可以有null）+ 表中只有一个

• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

• 全文索引：对文本的内容进行分词，进行搜索(关键字匹配)

2.2从结构来分类

• BTree索引
  大多数MySQL支持这种索引，B-Tree索引通常意味着所以的值都是按顺序存储的，并且每个叶子页到根的距离相同。该索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。树的深度和表的大小直接相关。

• Hash索引
  哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希值，哈希索引将所有的哈希值存储在索引中，同时在哈希表中保存每个数据行的指针。
  在MySQL中，只有Memory引擎显式支持哈希索引，这也是Memory引擎表的默认索引

2.3、其他

• 聚集索引/非聚集索引

• 覆盖索引

• 压缩索引

3、B-Tree索引

3.1 B-Tree结构

• B-Tree 每个节点都是一个二元数组: [key, data]，所有节点都可以存储数据。key为索引key,data为除key之外的数据。结构如下
  

3.1、为什么不用平衡二叉树

• 二叉树导致树高度非常高,逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，IO次数多查找慢,效率低。逻辑上相邻节点没法直接通过顺序指针关联，可能需要迭代回到上层节点重复向下遍历找到对应节点，效率低。

3.2、B-Tree(平衡多叉查找树)索引的查询类型

• 全值匹配：这个指的是和索引中的所有列进行匹配

• 匹配最左前缀： 只使用索引的第一列(即只使用索引第一列)

• 匹配列前缀：只匹配某一列的开头部分

• 匹配范围值：设置匹配范围比如匹配id为1~1000

• 精确匹配某一列并范围匹配另一列：综合前面的类型

• 只访问索引的查询：查询只需要访问索引，而无需访问数据行

3.3、B-Tree索引的优点

• B-Tree能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索

• 因为索引树的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的order by操作。一般来说，如果BTree可以按照某种方式查询到的值，那么也可以按照这种方式用于排序

3.3、B+Tree

• 与B-Tree相比，B+Tree有以下不同点：非叶子节点不存储data，只存储索引key；只有叶子节点才存储data。结构如下图：
  
• MySQL的B+Tree：在经典B+Tree的基础上进行了优化，增加了顺序访问指针。在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。这样就提高了区间访问性能：如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率(无需返回上层父节点重复遍历查找减少IO操作)。
  

更详细参考 https://www.jianshu.com/p/486a514b0ded

4、哈希索引

• 哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每行数据，存储引擎会根据其索引列创建一个hash值。同时hash表中保存了指向每个数据行的行指针。

• 哈希索引只包含哈希值和行指针，而不存储字段值。

• 哈希索引是无序的

• 哈希索引不支持部分索引列匹配查找

• 哈希索引只支持等值比较查询，包括=、IN()、<=>等。不支持范围查询

• InnoDB不支持显示的Hash索引，但是有个功能叫做“自适应哈希索引”当InnoDB注意到某些索引值
  被使用得非常频繁时，它会在内存中就B+Tree索引之上再创建一个哈希索引。这样B-Tree也具有哈希
  索引的一些优点。但这是一个完全自动的内部行为，用户无法控制。

5、全文索引

• 全文索引是一种特殊的索引，它查找的是文本中的关键词有些类似搜索引擎做的事情而不是简单的where条件匹配。

6、覆盖索引

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。
只扫描索引而无需回表的优点：

• 1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
• 2.因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
• 3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
• 4.innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)

覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引。

参考资料
《高性能MySQL》
链接：https://www.jianshu.com/p/486a514b0ded 作者：kimze1107