MySQL：索引常见问题

1. 什么是索引

索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。

2. 索引分类

  • 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
  • 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
  • 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
  • 按「字段个数」分类：单列索引、联合索引。

2.1 按数据结构分类

2.1.1 索引的选择

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

  • 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
  • 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
  • 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。

创建的主键索引和二级索引默认使用的是 B+Tree 索引。

2.1.2 B+ Tree索引结构特点

B+Tree的结构特点如下：

  • B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引。
  • 每个节点里的数据是按主键顺序存放的。
  • 每一层父节点的索引值都会出现在下层子节点的索引值中。
  • 每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。

2.1.3 聚簇索引和非聚簇索引区别

主键索引的 B+Tree 和二级索引的 B+Tree 区别如下：

  • 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
  • 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

2.1.4 B+ Tree优点

  1. B+Tree vs B Tree
    1. B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更大，在相同的磁盘 I/O 次数下，就能查询更多的节点。
    2. B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找。
  2. B+ Tree vs 二叉树
    1. 对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为d 个。在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右。
    2. 而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为O(logN)。
  3. B+ Tree vs hash
    1. Hash 表不适合做范围查询，它更适合做等值的查询。

2.1.5 举例说明

例如：

我新建一个表，如下图所示：

表中添加了这些数据：

那么其聚簇索引的B+ Tree应该向下面一样（这里应该是双向链表，图中是单向链表）：

其二级索引的B+Tree应该向下面一样：

如果我用 product_no 二级索引查询商品，如下查询语句：

1
select * from product where product_no = '0002';

会先检二级索引中的 B+Tree 的索引值（商品编码，product_no），找到对应的叶子节点，然后获取主键值，然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据。

2.2 按物理存储分类

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

这两个区别在前面也提到了：

  • 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
  • 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

2.3 按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

2.3.1 主键索引

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

在创建表时，创建主键索引的方式如下：

1
2
3
4
CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
  );

2.3.2 唯一索引

唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

在创建表时，创建唯一索引的方式如下：

1
2
3
4
5
CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

2.3.3 普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

在创建表时，创建普通索引的方式如下：

1
2
3
4
5
CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);

2.3.4 前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

在创建表时，创建前缀索引的方式如下：

1
2
3
4
5
CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
);

2.4 按字段个数分类

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

  • 建立在单列上的索引称为单列索引，比如主键索引；
  • 建立在多列上的索引称为联合索引；

2.4.1 联合索引

通过将多个字段组合成一个索引，该索引就被称为联合索引。比如，将商品表中的 product_no 和 name 字段组合成联合索引(product_no, name)，创建联合索引的方式如下：

1
CREATE INDEX index_product_no_name ON product(product_no, name);

联合索引(product_no, name) 的 B+Tree 示意图如下（叶子节点之间应该是双向链表）：

最左匹配原则：联合索引查询的 B+Tree 是先按 product_no 进行排序，然后再 product_no 相同的情况再按 name 字段排序。

2.4.2 举例

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

  • where a=1；
  • where a=1 and b=2 and c=3；
  • where a=1 and b=2；

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

  • where b=2；
  • where c=3；
  • where b=2 and c=3；

上面这些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的

2.4.3 联合索引的范围查询

联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

例如，select * from t_table where a > 1 and b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？

由于联合索引（二级索引）是先按照 a 字段的值排序的，所以符合 a > 1 条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合 a > 1 条件的第一条记录，但是a>1时的b字段是无序的，所以这条查询语句只有 a 字段用到了联合索引进行索引查询，而 b 字段并没有使用到联合索引。

但是注意，并不是所有的范围查询都会有部分字段无法使用联合索引。

例如，select * from t_table where a >= 1 and b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？

虽然在符合 a>= 1 条件的二级索引记录的范围里，b 字段的值是「无序」的，但是对于符合 a = 1 的二级索引记录的范围里，b 字段的值是「有序」的。 于是，在确定需要扫描的二级索引的范围时，当二级索引记录的 a 字段值为 1 时，可以通过 b = 2 条件减少需要扫描的二级索引记录范围。所以，Q2 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

2.4.4 总结

**联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。** 注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。

2.4.5 索引下推优化

现在我们知道，对于联合索引（a, b），在执行select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，那在联合索引的 B+Tree 找到第一个满足条件的主键值ID 为 2）后，还需要判断其他条件是否满足（看 b 是否等于 2），那是在联合索引里判断？还是回主键索引去判断呢？

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

2.4.6 索引区分度

实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。

区分度就是某个字段 column 不同值的个数「除以」表的总行数，计算公式如下：

$$
区分度 =\frac{\operatorname{distinct}(\operatorname{column})}{\operatorname{count}(*)}
$$

比如，性别的区分度就很小，不适合建立索引或不适合排在联合索引列的靠前的位置，而 UUID 这类字段就比较适合做索引或排在联合索引列的靠前的位置。

注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配

3. 什么时候需要/不需要索引

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

  • 需要占用物理空间，数量越大，占用空间越大；
  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
  • 会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

3.1 什么时候适用索引

  • 字段有唯一性限制的，比如商品编码；
  • 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
  • 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

3.2 什么时候不需要创建索引

  • WHERE 条件，GROUP BY，ORDER BY 里用不到的字段
  • 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女。
  • 表数据太少的时候，不需要创建索引
  • 经常更新的字段不用创建索引

3.3 优化索引的方法

3.3.1 前缀索引优化

使用某个字段中字符串的前几个字符建立索引，使用前缀索引是为了减小索引字段大小。

3.3.2 覆盖索引优化

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？

我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。

3.3.3 主键索引自增

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。

3.3.4 索引最好设置为NOT NULL

第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。

NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式中至少会用 1 字节空间存储 NULL 值列表，如下图的紫色部分：

3.3.5 防止索引失效

之后会说

4. 本章总结