MySQL高频八股——索引

大家好，我是钢板兽！

今天来更新MySQL高频八股的最后一篇文章，包括很多内容：索引分类、最左匹配原则、范围查询使联合索引失效、索引下推、给联合查询加索引、索引失效情况。

在MySQL的第一篇八股文章，我写了MySQL的索引结构B+树，今天来重新学习一下索引。索引类似于书的目录，可以加快查询数据的定位速度，而无需遍历整个数据表。

1 索引分类

如果要将索引分类，会有很多分法：按字段特性分类、按底层数据结构分类、按数据存储方式分类。

（1）按字段特性分类

• 主键索引（PRIMARY KEY）：表的主键字段上自动创建的索引，具有唯一性。每个表只能有一个主键索引，且不能为空（NOT NULL）。
• 唯一索引（UNIQUE INDEX）：某个字段（或字段组合）的值在表中是唯一的，但允许NULL（多个 NULL 视为不同值），适用于邮箱、身份证等唯一性字段。
• 普通索引（INDEX）：最基本的索引类型，没有唯一性限制，主要用于加速查询。
• 联合索引（INDEX）：由多个字段组合而成的索引，可加速多个字段的查询。
• 全文索引（FULLTEXT INDEX）：用于文本字段的全文搜索，支持自然语言查询。
• 空间索引（SPATIAL INDEX）：用于 地理信息数据，支持空间查询。

括号里的英文是创建索引要用的字段，比如要创建一个唯一索引：

CREATE UNIQUE INDEX unique_name ON students(name);

（2）按底层结构分类

按照索引的底层结构分类，其实在《MySQL高频八股—— MySQL的存储引擎及索引结构》这篇文章已经讲过了，我在文章里写了B+树作为索引的底层结构，相对于红黑树、哈希表等数据结构的优点。

这里简单介绍一下索引按照底层结构的分类。

• ‌B+树索引：MySQL默认的索引类型，支持范围查询和排序操作。
• ‌哈希索引（Hash Index）：仅支持等值查询。
• ‌R-Tree索引‌：用于空间数据，支持地理信息查询。

（3）按数据存储方式分类

• 聚簇索引：数据和索引存储在一起，索引的非叶子节点存储主键，而叶子节点直接存储整行数据。

  只有主键索引是聚簇索引。适用于主键查询，避免额外回表。

  回表是指回到原始表中根据主键去取完整数据。

• 非聚簇索引：索引和数据分开存储，索引的非叶子节点存储索引列的值，而叶子节点存储的只是主键的地址（指向数据行的主键值）。

  查询非主键索引时，需要回表查询（即先查索引，再查主键）。

我觉得有必要深入了解一下聚簇索引，这有助于帮助我们明白MySQL中索引是怎么“起效”的。

聚簇索引在带来很多方便（由于无需回表所以查询主键很快，适合范围查询）的同时，也带来了一些缺点。

（1）插入数据会导致页分裂或和磁盘碎片化

由于聚簇索引的数据存储顺序与索引顺序一致，新插入的数据必须按照索引的顺序存放。如果新插入的数据的主键值是随机的（如 UUID、无序的整数），则新数据可能需要插入到 B+ 树的中间位置，而不是直接追加到末尾。这会导致页分裂（Page Split），MySQL 需要拆分已有数据页，以腾出空间放入新数据。频繁的页分裂会导致磁盘碎片化，数据分散存储，影响查询性能。

解决方案：使用自增主键，避免使用 UUID 或无序主键。

（2）主键变更代价高

在聚簇索引中，数据的物理存储顺序是按照主键排序的。如果更新了主键的值，MySQL 需要删除原来的数据行，插入一条新的数据行，重新组织 B+ 树索引结构。

解决方案：尽量避免更新主键字段，可以使用代理键（如 AUTO_INCREMENT 自增 ID），而不是业务字段作为主键。

2 联合索引的使用

刚才在索引的分类已经提到联合索引，联合索引是指一个索引包含多个字段。对于联合索引 (a, b, c)，能够在多个列上进行索引查找，从而加速查询。

我们要知道的一点是，被索引的列会自动进行排序，包括单列索引和联合索引，只是联合索引的排序要复杂一些，会按照a、b、c依次排序。

这就意味着，(a,b,c)联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按c排序。所以，b和c是全局无序，局部相对有序的。因此，联合索引的使用要遵循最左匹配原则。

如上图所示，索引 a 数据是全局有序的，而索引 b 数据是全局无序的，局部有序的（12，7 8，2，3，8，10，5，3），在同样的 a 数据情况下，索引 b 的数据是有序的。

2.1 最左匹配原则

联合索引的顺序非常关键，MySQL 会根据索引中字段的顺序，从左到右依次匹配查询条件中的字段。如果查询条件符合索引中的最左字段，则 MySQL 会使用该字段的索引来过滤数据。

假设有一个联合索引 (a, b, c)。

• 如果查询条件中包括 a，MySQL 会使用索引中 a 的部分。
• 如果查询条件中同时包含 a 和 b，MySQL 会利用索引中 (a, b) 部分。
• 如果查询条件中包含 a、b 和 c，MySQL 会完全利用索引 (a, b, c) 来加速查询。
• 如果查询条件是 a = 1 AND c = 1，MySQL 只会利用 a 的部分使用索引，而不会使用 （a, c）。

2.2 为什么范围查询会使联合索引失效

推荐阅读文章：https://blog.51cto.com/zhangxueliang/12684318。

范围查询是指查询条件中包含了某种不等式，比如 a > 1、a <= 5 或 a BETWEEN 1 AND 10 等。

来看一个范围查询的例子：

SELECT * FROM table WHERE a > 1 AND b = 2

（a,b）联合索引的B+树结构如下图（还是刚才那张表），我们刚才说过：当 a 数据相同时，b数据有序。

但是现在 a 数据是一个范围，而不是一个定值，所有 大于1 的 a 数据相对应的 b 数据自然就是无序的了，所以联合索引就失效了，只有 a 可以用到联合索引。

同样的，其他范围查询a <= 5 或 a BETWEEN 1 AND 10 等也会使联合查询失效。

2.3 索引下推

索引下推（Index Condition PushDown，ICP）是 MySQL 5.6 版本后引入的优化功能。

索引下推是把本应该在 server 层进行筛选的条件，下推到存储引擎层来进行筛选判断，允许存储引擎在使用索引时，就直接执行查询条件的部分判断，减少回表的次数，提高查询效率。

例如，如果有一个查询条件 zipcode > '431200' AND month = 3，使用联合索引 (zipcode, month)。

根据前面的范围查询会使联合索引失效，我们知道，zipcode > '431200' 会让联合索引失效，而在MySQL5.6版本之后，索引下推的引入使得查询可以走联合索引。

• 在没有索引下推的情况下，存储引擎会首先用最左前缀索引查找 zipcode > '431200'的所有记录，然后逐一进行回表扫描，去聚簇索引找到完整的行记录返回给server层，最后server层再筛选非前缀条件 month = 3的记录。

• 但在索引下推优化后，存储引擎 会在查找到 zipcode > '431200'的所有记录后，直接筛选 month = 3的记录，符合所有条件的数据才会被一一回表扫描。

索引下推通过大幅减少需要访问的数据页数量，来提高查询性能。

当然索引下推的生效也是有要求的（只能存在联合索引里），具体可以阅读这篇文章：https://blog.csdn.net/qq_42672839/article/details/134733197。

2.4 怎么给联合查询加索引

在实际的数据库设计中，以下是一些常见的查询场景和如何添加索引的例子：

1. 查询：SELECT * FROM table WHERE a > 1 AND b = 2;

   为 (b, a) 创建联合索引。b = 2 是等值查询，b 在索引中排在最左侧，能够快速定位数据。索引 (b, a) 会首先利用 b = 2 来定位符合条件的记录，然后再根据 a > 1 来进一步筛选数据。

2. 查询：SELECT * FROM table WHERE a = 1 ORDER BY b;

   • 解决方案：为 (a, b) 创建联合索引。 a = 1 是等值查询，首先会利用 a = 1 进行过滤，然后直接利用索引对 b 进行排序，MySQL 就不需要额外的排序操作，直接返回已经排序好的结果，

3. 查询：SELECT * FROM table WHERE a > 1 ORDER BY b;

   • 解决方案：仅在 a 列上建立索引即可。 a > 1 是范围查询，在使用联合索引时，如果 a 是范围查询，那么 b 列的顺序就不再有意义，MySQL 会忽略 b 的排序优化。

4. 查询：SELECT * FROM table WHERE a = 1 AND b = 2 AND c > 3 ORDER BY c;

   • 解决方案：为 (a, b, c) 创建联合索引。a = 1 和 b = 2 都是等值查询，MySQL 会首先使用这两列的索引进行过滤，而 c > 3 是范围查询，虽然 c 的顺序不再被保持，但在索引中进行筛选仍然能够大幅提升查询效率。

3 索引失效情况

索引失效是 MySQL 查询优化时常见的问题，当索引无法被有效使用时，查询性能可能会大幅下降。

（1）不满足最左匹配原则

联合索引（复合索引） 必须按照索引字段的最左前缀开始匹配，否则索引无法有效使用，这点已经在上文讲过了。

（2）使用 SELECT *

SELECT * FROM employees WHERE name = 'Alice';

因为 SELECT * 需要所有字段，而索引 name 只包含部分字段，所以 MySQL 无法仅使用索引完成查询，必须回表。

所以最好使用索引覆盖查询（只查询索引列），避免回表：

SELECT name FROM employees WHERE name = 'Alice';

（3）索引列上有计算

SELECT * FROM employees WHERE salary / 2 = 5000;

MySQL 无法直接使用 salary 的索引，因为 salary / 2 需要计算，索引列的值无法直接匹配。

所以要改写查询，让索引列保持原值：

SELECT * FROM employees WHERE salary = 5000 * 2;

（4）索引列使用了函数

SELECT * FROM employees WHERE UPPER(name) = 'ALICE';

UPPER(name) 使 name 索引失效，因为索引存储的是 name 的原始值。

所以我们要避免对索引列使用函数，或者在业务层实现函数的功能。

（5）字段类型不同

索引列和查询条件的数据类型不一致，可能导致隐式类型转换，进而导致索引失效。

表的字段如下：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary INT
);

执行语句：

SELECT * FROM employees WHERE salary = '10000';

salary 是 INT 类型，而 '10000' 是 VARCHAR，导致 MySQL 进行隐式转换。

应当保持查询类型与索引列类型一致：

SELECT * FROM employees WHERE salary = 10000;

（6） LIKE 左边包含 %

SELECT * FROM employees WHERE name LIKE '%Alice%';

LIKE '%keyword' 不能使用 B+Tree 索引，因为 MySQL 无法确定字符串的开头，必须进行全表扫描。

所以我们要避免前导 %，可以使用后导 %。

SELECT * FROM employees WHERE name LIKE 'Alice%';

或者使用 全文索引（FULLTEXT INDEX）：

CREATE FULLTEXT INDEX idx_name ON employees(name);

（7）列对比

SELECT * FROM employees WHERE age = salary;

索引只能用于变量和常量对比。age 和 salary 都是变量，导致索引失效。

（8）OR 关键字

如果OR 连接的查询条件中，某个条件未使用索引，整个查询就会索引失效。

SELECT * FROM employees WHERE name = 'Alice' OR salary = 10000;

只有name 和 salary 都是索引时，索引才会生效，否则不会生效。

我们可以使用 UNION ALL 代替 OR，让每个查询使用索引：

SELECT * FROM employees WHERE name = 'Alice'
UNION ALL
SELECT * FROM employees WHERE salary = 10000;

（9）NOT IN 和 NOT EXISTS

MySQL 无法高效排除索引范围，导致索引失效，所以NOT IN 和 NOT EXISTS 会导致 MySQL 无法使用索引，必须执行全表扫描。

SELECT * FROM employees WHERE salary NOT IN (10000, 20000);

（10）ORDER BY 可能导致索引失效

索引必须匹配 ORDER BY 的顺序，否则索引可能无法使用。

SELECT * FROM employees ORDER BY salary DESC, name ASC;

salary 和 name 排序方向不一致，索引可能无法使用。

这篇文章到这里就结束啦，欢迎留言你对索引的见解。如果这篇文章对你有帮助，欢迎点赞、转发、留言！