Hello World

吞风吻雨葬落日 欺山赶海踏雪径

0%

MySQL索引详解

发表于 更新于 分类于

MySQL中的索引就如同指向数据条目的指针一样，可以快速找到匹配查询条件的数据条目。所有的MySQL数据类型都支持索引。

虽然可以为表中的每个查询的字段增加索引，但是不必要的索引会消耗更多的存储空间，查询中MySQL决定使用何种索引也会增加查询耗时。索引还会增加插入、更新、删除数据的耗时，因为每个索引都需要更新。
所以，哪些字段建立索引需要深思熟虑，来达到最佳的性能。

索引的分类

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

按数据结构分类

存储引擎支持的索引类型如下图
20230515111800.png

InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

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

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

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

B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询速度快，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。

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

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

所以根据二级索引的字段查询，会先在二级索引的 B+Tree 中找到主键值，然后在主键索引的 B+Tree 中找到查询的具体数据，再次查找主键索引的这个过程叫做回表 也就是说要查两个 B+Tree 才能查到数据。

不过当查询的数据在二级索引中能够直接获取到的，就不需要在根据主键索引在继续查询了，比如直接查询 id ，就不需要在做一次回表操作了。这种在二级索引的 B+Tree 就能查询到结果的过程就叫作 「覆盖索引」 ，也就是只需要查一个 B+Tree 就能找到数据。

索引覆盖与索引下推

如何识别是否索引覆盖？

判断是否索引覆盖，可以查看执行计划中的 Extra 字段是否包含 Using index 。比如

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CREATE TABLE user_test
(
    id   int(11)     NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    age  int(11)     NOT NULL,
    gender char(1),
    email varchar(64),
    phone varchar(64),
    PRIMARY KEY (id),
    KEY idx_age (age)
) ENGINE = InnoDB;
INSERT INTO user_test(name, age, gender, email, phone) VALUES ('Amy', 22, 'F', 'amy@example.com', '12345678');
INSERT INTO user_test(name, age, gender, email, phone) VALUES ('Bob', 35, 'M', 'bob@example.com', '23456789');
INSERT INTO user_test(name, age, gender, email, phone) VALUES ('Cathy', 28, 'F', 'cathy@example.com', '34567890');
INSERT INTO user_test(name, age, gender, email, phone) VALUES ('David', 44, 'M', 'david@example.com', '45678901');
INSERT INTO user_test(name, age, gender, email, phone) VALUES ('Eric', 19, 'M', 'eric@example.com', '56789012');
INSERT INTO user_test(name, age, gender, email, phone) VALUES ('Fiona', 29, 'F', 'fiona@example.com', '67890123');
INSERT INTO user_test(name, age, gender, email, phone) VALUES ('George', 33, 'M', 'george@example.com', '78901234');
INSERT INTO user_test(name, age, gender, email, phone) VALUES ('Harry', 27, 'M', 'harry@example.com', '89012345');
INSERT INTO user_test(name, age, gender, email, phone) VALUES ('Ivy', 31, 'F', 'ivy@example.com', '90123456');
INSERT INTO user_test(name, age, gender, email, phone) VALUES ('Jack', 38, 'M', 'jack@example.com', '01234567');

mysql> explain SELECT  id, age FROM user_test WHERE age = 22;
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user_test | NULL       | ref  | idx_age       | idx_age | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

另外两种常见的 Extra 的值：

  • Using where： 表示Server端使用了WHERE子句中的条件进行过滤。
  • Using index condition： 表示使用了索引下推优化。

索引下推(Index Condition Pushdown / ICP) 简单的说就是把where条件中 二级索引能够判断的交由存储引擎来判断，而不是交给Server层做过滤。
ps.很多文章说索引下推只会出现在多字段的联合索引中，但是根据官方文档，并没有这个限制，且的确通过二级索引的范围查询是可以触发Using index condition的：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain SELECT name FROM user_test WHERE age = 35;
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user_test | NULL       | ref  | idx_age       | idx_age | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+---------+---------+-------+------+----------+-------+

mysql> explain SELECT  name FROM user_test WHERE age > 35;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user_test | NULL       | range | idx_age       | idx_age | 4       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+

但其实这种情况判断是否有ICP意义不大，应该本身就是对与二级索引的范围筛选，存储引擎层肯定是做了筛选的，所以通常情况我们还是讨论在联合索引下执行ICP。
在看下官网的的翻译来深入理解一下ICP：

1
2
3
4
5
6
7
8
要理解 ICP 这种优化是如何工作的，首先考虑当没有使用ICP时索引扫描是如何进行的:
    1.获取下一行，首先通过读取索引元组，然后使用索引元组定位和读取整个表行。 
    2.检查WHERE条件中应用于此表的部分。根据检查结果接受或拒绝行。
使用ICP,则会变成下面这样：
    1.获取下一行的索引元组(但不是整个表行，还没有进行回表)。 
    2.检查应用于此表的WHERE条件的部分，仅使用索引列即可进行检查。如果条件不满足，则进入下一行的索引元组。（因为索引条件下推到了存储引擎层）
    3.如果条件满足，则使用index元组定位和读取整个表行。
    4.测试应用于此表的WHERE条件的其余部分。根据测试结果接受或拒绝行

还有就是索引下推是Server层决定的（优化器、执行器都是在Server层），当Server层决定使用索引下推之后，会吧下推的条件带给存储引擎，存储引擎每读取一条数据后就会去判断记录是否匹配下推下来的where条件，不匹配就直接获取下一条记录了。

另外需要额外说明的是对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取的数量，从而减少I/O操作。对于InnoDB聚集索引，完整的记录已经被读取到InnoDB缓冲区中。在这种情况下使用ICP并不会减少I/O。

索引结构为什么是 B+tree

B+Tree vs B Tree

B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个非叶子节点可以存储更多的索引值，在相同的磁盘 I/O 次数下，就能查询更多的节点。
另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

B+Tree vs 二叉树

对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。
在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 34 层左右，也就是说一次数据查询操作只需要做 34 次的磁盘 I/O 操作就能查询到目标数据。

而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

B+Tree vs Hash

Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。
但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

按物理存储分类

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

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

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

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

按字段特性分类

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

主键索引

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

创建主键索引的方式如下：

1
2
3
4
CREATE TABLE table_name  (
....
PRIMARY KEY (index_column)
);

唯一索引

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

创建唯一索引的方式如下：

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

或者在建表之后创建唯一索引

1
2
CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

普通索引

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

创建普通索引的方式如下：

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

或

1
2
CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

前缀索引

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

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

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

或

1
2
CREATE INDEX index_name
ON table_name(column_name(length));

按字段个数分类

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

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

联合索引

通过将多个字段组合成一个索引，该索引就被称为联合索引。
使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效，这样就无法利用到索引快速查询的特性了。

比如，如果创建了一个 (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 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。

联合索引范围查询

联合索引有一些特殊情况，并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，也就是可能存在部分字段用到联合索引的 B+Tree，部分字段没有用到联合索引的 B+Tree 的情况。

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

范围查询有很多种，具体哪些范围会导致联合索引的最左匹配原则会停止匹配呢？下面来几个例子：

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

由于联合索引（二级索引）是先按照 a 字段的值排序的，所以符合 a > 1 条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合 a > 1 条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 a > 1 条件位置。所以 a 字段可以在联合索引的 B+Tree 中进行索引查询。

但是在符合 a > 1 条件的二级索引记录的范围里，b 字段的值是无序的,不能根据查询条件 b = 2 来进一步减少需要扫描的记录数量，因此，Q1 这条查询语句只有 a 字段用到了联合索引进行索引查询，而 b 字段并没有使用到联合索引。

我们也可以在执行计划中的key_len字段佐证：

举例个例子 ，a 和 b 都是 int 类型且不为 NULL 的字段，那么 Q1 这条查询语句执行计划如下，可以看到 key_len 为 4 字节（int类型4字节，如果字段允许为 NULL，就在字段类型占用的字节数上加 1，也就是 5 字节），说明只有 a 字段用到了联合索引进行索引查询，而且可以看到，即使 b 字段没用到联合索引，key 为 idx_a_b，说明 Q1 查询语句使用了 idx_a_b 联合索引。

ps. key_len的计算方式：

1
2
3
4
5
6
7
8
9
10
11
12
13
varchar()：表字符集长度*列长度+2(变长)+1(nullable) 若是列被定义为"not null",则不需要加1
char()：表字符集长度*列长度+1(null)  若是列被定义为"not null",则不需要加1
常用的字符集：
utf8mb4：4个字节
utf8：3个字节
gbk：2个字节
latin：1个字节

示例：
表的字符集选用utf8mb4,计算
varchar(10),not null  --->  key_len=4*10+2=42
char(10),null         --->  key_len=4*10+1=41
int not null          --->  key_len=4

通过 Q1 查询语句我们可以知道，a 字段使用了 > 进行范围查询，联合索引的最左匹配原则在遇到 a 字段的范围查询（ >）后就停止匹配了，因此 b 字段并没有使用到联合索引。

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

Q2 和 Q1 的查询语句很像，唯一的区别就是 a 字段的查询条件「大于等于」。
由于联合索引（二级索引）是先按照 a 字段的值排序的，所以符合 >= 1 条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合 >= 1 条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 a>= 1 条件位置。所以 a 字段可以在联合索引的 B+Tree 中进行索引查询。
虽然在符合 a>= 1 条件的二级索引记录的范围里，b 字段的值是「无序」的，但是对于符合 a = 1 的二级索引记录的范围里，b 字段的值是「有序」的（_因为对于联合索引，是先按照 a 字段的值排序，然后在 a 字段的值相同的情况下，再按照 b 字段的值进行排序_）。
于是，在确定需要扫描的二级索引的范围时，当二级索引记录的 a 字段值为 1 时，_可以通过 b = 2 条件减少需要扫描的二级索引记录范围_（b 字段可以利用联合索引进行索引查询的意思）。也就是说，从符合 a = 1 and b = 2 条件的第一条记录开始扫描，而不需要从第一个 a 字段值为 1 的记录开始扫描。
所以，Q2 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

通过 Q2 查询语句我们可以知道，虽然 a 字段使用了 >= 进行范围查询，但是联合索引的最左匹配原则并没有在遇到 a 字段的范围查询（ >=）后就停止匹配了，b 字段还是可以用到了联合索引的（虽然是部分数据，但还是用到了）。

Q3: SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？

由于 MySQL 的 BETWEEN 包含 value1 和 value2 边界值，所以类似于 Q2 查询语句，因此 Q3 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

Q4: SELECT * FROM t_user WHERE name like ‘j%’ and age = 22，联合索引（name, age）哪一个字段用到了联合索引的 B+Tree？

由于联合索引（二级索引）是先按照 name 字段的值排序的，所以前缀为 ‘j’ 的 name 字段的二级索引记录都是相邻的， 于是在进行索引扫描的时候，可以定位到符合前缀为 ‘j’ 的 name 字段的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录的 name 前缀不为 ‘j’ 为止。

虽然在符合前缀为 ‘j’ 的 name 字段的二级索引记录的范围里，age 字段的值是「无序」的，但是对于符合 name = j 的二级索引记录的范围里，age字段的值是「有序」的（因为对于联合索引，是先按照 name 字段的值排序，然后在 name 字段的值相同的情况下，再按照 age 字段的值进行排序）

所以，Q4 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

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

索引下推

上面也介绍过了，这里在用例子说明下：对于联合索引（a, b），在执行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，那在联合索引的 B+Tree 找到第一个满足条件的主键值（ID 为 2）后，还需要判断其他条件是否满足（看 b 是否等于 2），那是在联合索引里判断？还是回主键索引去判断呢？
在 MySQL 5.6 之前，只能从 ID2 （主键值）开始一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。
而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

当你的查询语句的执行计划里，出现了 Extra 为 Using index condition，那么说明使用了索引下推的优化。

索引区分度

建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。

区分度就是某个字段 column 不同值的个数「除以」表的总行数: 区分度 = distinct(column) / count(*)

因为如果索引的区分度很小，假设字段的值分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比（惯用的百分比界线是”<30%“）很高的时候，它一般会忽略索引，进行全表扫描。

联合索引进行排序

针对下面这条 SQL，你怎么通过索引来提高查询效率呢？

1
select * from order where status = 1 order by create_time asc

很多人人为单独给 status 建立一个索引就可以了。但是更好的方式给 status 和 create_time 列建立一个联合索引，因为这样可以避免 MySQL 数据库发生文件排序。

因为在查询时，如果只用到 status 的索引，但是这条语句还要对 create_time 排序，这时就要用文件排序 filesort，也就是在 SQL 执行计划中，Extra 列会出现Using filesort。

所以，要利用索引的有序性，在 status 和 create_time 列建立联合索引，这样根据 status 筛选后的数据就是按照 create_time 排好序的，避免在文件排序，提高了查询效率。

索引优化

  • 前缀索引优化；
  • 覆盖索引优化；
  • 主键索引最好是自增的；
  • 防止索引失效

前缀索引优化

前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

不过，前缀索引有一定的局限性，例如：

  • order by 就无法使用前缀索引；
  • 无法把前缀索引用作覆盖索引；

覆盖索引优化

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

假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

所以，使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。

主键索引最好是自增的

InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的效率非常高。
如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

索引最好设置为 NOT NULL

为了更好的利用索引，索引列要设置为 NOT NULL 约束。有两个原因：

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

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

20230516105523.png

防止索引失效

引发索引失效的情况：

  • 使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
  • 在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
  • 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
  • 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

可以使用执行计划来判断是否索引失效，执行计划中判断依据

  • possible_keys 字段表示可能用到的索引；
  • key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
  • key_len 表示索引的长度；
  • rows 表示扫描的数据行数。
  • type 表示数据扫描类型，我们需要重点看这个。

type 字段就是描述了找到所需数据时使用的扫描方式，常见扫描类型的执行效率从低到高的顺序为：

  1. All（全表扫描）；
  2. index（全索引扫描）；
  3. range（索引范围扫描）；
  4. ref（非唯一索引扫描）；
  5. eq_ref（唯一索引扫描）；
  6. const（结果只有一条的主键或唯一索引扫描）。

在这些类型中，all 是最坏的情况，因为采用了全表扫描。index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。

range 表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。

ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。

const 类型表示使用了主键或者唯一索引与常量值进行比较。

需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

除了 type 字段， extra字段也需要关注，常用的几种类型：

Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候， 这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。

Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。

Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。

强制使用索引

如果 索引的基数 Cardinality 计算的不准确（因为是用抽样的页去粗略计算基数的），可能会导致走错索引。
可以强制使用索引 (force index) , 也可以重新统计索引信息 analyze table 。

使用 show index form table_name 查看索引的基数
20230713152800.png

强制使用索引 select * from table force index (index_name) where condition

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> show index from inventory ;
+-----------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name             | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| inventory |          0 | PRIMARY              |            1 | inventory_id | A         |        4581 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| inventory |          1 | idx_fk_film_id       |            1 | film_id      | A         |         958 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| inventory |          1 | idx_store_id_film_id |            1 | store_id     | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| inventory |          1 | idx_store_id_film_id |            2 | film_id      | A         |        1521 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)

mysql> explain select film_id,store_id from sakila.`inventory`  where film_id = 22 ;
+----+-------------+-----------+------------+------+-------------------------------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                       | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | inventory | NULL       | ref  | idx_fk_film_id,idx_store_id_film_id | idx_fk_film_id | 2       | const |    7 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-------------------------------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select film_id,store_id from sakila.`inventory` force index (`idx_store_id_film_id`) where film_id = 22 ;
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+------+------+----------+----------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | inventory | NULL       | range | idx_store_id_film_id | idx_store_id_film_id | 3       | NULL |   28 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+------+------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

analyze table table_name 分析并更新表的统计信息

1
2
3
4
5
6
7
8
mysql> analyze table inventory;
+------------------+---------+----------+----------+
| Table            | Op      | Msg_type | Msg_text |
+------------------+---------+----------+----------+
| sakila.inventory | analyze | status   | OK       |
+------------------+---------+----------+----------+
1 row in set (0.03 sec)

注意analyze table 命令会对表进行锁定，因此在执行该命令时需要谨慎，避免对正在运行的业务产生影响。
同时，MySQL 也会定期自动进行统计信息的更新，因此在大多数情况下并不需要手动执行 ANALYZE TABLE 命令。
如果表中的数据发生了较大的变化，例如删除了大量的记录，建议手动执行 ANALYZE TABLE 命令来更新统计信息，以便 MySQL 能够快速地优化查询计划。

参考

索引常见面试题