MySQL — 索引

索引

索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

简单类比一下，数据库如同书籍，索引如同书籍目录，假如我们需要从书籍查找与 xx 相关的内容，我们可以直接从目录中查找，定位到 xx 内容所在页面，如果目录中没有 xx 相关字符或者没有设置目录（索引），那只能逐字逐页阅读文本查找，效率可想而知。

索引结构 — B树与B+树

在MySQL的InnoDB存储引擎中，使用的是B+树来实现索引。使用这种数据结构可以快速定位到目标数据，也可以有效减少磁盘IO开销。

下面先简单了解一下B树和B+树。

B树

平衡多路查找树（B-Tree），是为磁盘等外存储设备设计的一种平衡查找树。

B树简略示意图:

B树的两个特点：

• 树内的每个节点都存储数据
• 叶子节点之间无指针连接

B+树

B+树简略示意图：

B+树的两个特点：

• 数据只出现在叶子节点
• 所有叶子节点增加了一个链指针

为什么是B+树而不是B树呢？原因有两点：

• B树每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点能存储的key的数量很小，要保存同样多的key，就需要增加树的高度。树的高度每增加一层，查询时的磁盘I/O次数就增加一次，进而影响查询效率。而在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以增加每个节点存储的key值数量，降低B+树的高度。
• B+树的叶子节点上有指针进行相连，因此在做数据遍历的时候，只需要对叶子节点进行遍历即可，这个特性使得B+树非常适合做范围查询。

聚簇索引与非聚簇索引

聚簇索引

聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。

非聚簇索引

非聚簇索引，数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录，而是主键值。

虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

优缺点

优点：

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快

缺点：

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键（主键列不要选没有意义的自增列，选经常查询的条件列才好，不然无法体现其主键索引性能）
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

覆盖索引与回表

在InnoDB中的主键索引就是聚簇索引，主键索引的查询效率也是非常高的，非聚簇索引，其查询效率稍逊。

覆盖索引其形式就是，搜索的索引键中的字段恰好是查询的字段（或是组合索引键中的其它字段）。覆盖索引的查询效率极高，原因在于其不用做回表查询。

回表查询，先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。

实现索引覆盖常见的方法就是建立联合索引。

联合索引

联合索引，也称多列索引，就是建立在多个字段上的索引，这个概念是跟单列索引相对的。联合索引依然是B+树，但联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

索引覆盖

假如在 t 表的a,b,c三个列上建立联合索引。

如果在查询记录时，返回的列刚好是a, b, c或其中几个，那么这个过程可以实现索引覆盖，避免回表查询。

select a, b, c from t where a=1 and b=2 and c=3;

最左前缀匹配

联合索引中有一个重要的概念，就是最左前缀匹配原则。

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

假如在 t 表的a,b,c三个列上建立联合索引，简要分类分析下联合索引的最左前缀匹配。

1、全值匹配查询时（where子句搜索条件顺序调换不影响索引使用，因为查询优化器会自动优化查询顺序 ），可以用到联合索引

SELECT * FROM t WHERE a=1 AND b=3 AND c=2
SELECT * FROM t WHERE b=3 AND c=4 AND a=2

2、匹配左边的列时，可以用到联合索引

SELECT * FROM t WHERE a=1
SELECT * FROM t WHERE a=1 AND b=3

3、未从最左列开始时，无法用到联合索引

SELECT * FROM t WHERE b=1 AND b=3

4、查询列不连续时，无法使用联合索引（会用到a列索引，但c排序依赖于b，所以会先通过a列的索引筛选出a=1的记录，再在这些记录中遍历筛选c=3的值，是一种不完全使用索引的情况）

SELECT * FROM t WHERE a=1 AND c=3