前言

我们知道MySQL的数据都存储在磁盘中，在执行SQL语句时需要对进行大量磁盘IO，这使得磁盘性能可能成为性能的瓶颈，这是我们不希望出现，或者说希望优化的。

索引基础知识

什么是索引

索引其实在我们的生活中普遍存在，比如查字典，我们知道先从目录开始查而不是去一页一页的找，字典里的目录就是索引的一种实现。

索引是数据库中有序存储特定列值的数据结构（如B+树、哈希表），通过缩小扫描范围实现高效查询。

索引有什么用

根据索引的本质其实也不难理解，使用索引在大部分情况下都能提高数据库的性能，包括但不限于提高查询速度，减少磁盘IO，减少分组和排序时间等等。为啥用索引就能"快"? 🤔🤔

为某一列字段创建索引，这一列的字段会基于一定的数据结构排列好，我们用排列好的数据查询这一行的所有数据就会更快，因为这一行数据已经和该索引建立了关联，这得益于存放索引的数据结构。

索引的存放方式

索引的存放方式有很多，这里介绍基于B+树的索引存放方式，也是目前InnoDB引擎使用的方式。

B+树是什么数据结构？这里推荐一篇文章非常详细的解释什么是B+树。

MySQL索引底层：B+树详解 - 知乎

没看懂也没关系，这里只需要理解：

  • B+树的叶子结点存放数据，非叶子结点存放key起到导航作用
  • B+树的叶子结点通过双向链表有序连接
  • B+树一个结点会有多个子节点

这里引入一级索引（聚簇索引）和二级索引（非聚簇索引）的概念。

  • 一级索引：叶子节点存放数据行，根据索引能直接获取这一行的数据。

  • 二级索引：叶子结点存放主键，根据主键回表查询数据。

回表查询：指数据库先通过二级索引找到主键值，再根据主键值回到聚簇索引中查找完整数据行的过程。如同查字典：

  1. 先查部首目录（二级索引）→ 找到目标字所在页码（主键）
  2. 再翻到正文页（聚簇索引）→ 获取字的详细解释（数据行）

现在我们能回答为什么使用索引就能快：

比如我们执行这样一条SQL语句

1
2
# 找到id < 500的用户的昵称
select nick_name from tb_user where id < 500;

我们利用二叉搜索树思想分析一下，id < 500我们要不断向下搜索所有索引小于500的叶子结点，然后获取这一行的数据，接着再将nick_name返回。

索引的分类方式：

类型 创建示例（MySQL） 特点
主键索引 PRIMARY KEY (id) 唯一且非空，聚簇索引（InnoDB）
唯一索引 CREATE UNIQUE INDEX idx_email ON users(email) 列值必须唯一
普通索引 CREATE INDEX idx_name ON users(name) 最基本的索引类型
联合索引 CREATE INDEX idx_age_name ON users(age, name) 多列组合，最左匹配原则
覆盖索引 查询字段全在索引中时触发 避免回表查询，性能极高
前缀索引 CREATE INDEX idx_city ON users(city(5)) 对文本前N字符建索引，节省空间

索引的使用

我们在使用索引之前肯定需要先创建索引。我们定义一张表结构如下，并向其中添加一千条数据。

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `phone` varchar(11) NOT NULL COMMENT '手机号码',
  `password` varchar(128) DEFAULT NULL COMMENT '密码',
  `nick_name` varchar(32) DEFAULT NULL COMMENT '昵称',
  `icon` varchar(255) DEFAULT NULL COMMENT '人物头像',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_key_phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=1010 DEFAULT CHARSET=utf8mb4;

image-20250731004842127

添加聚簇索引和唯一索引

如图所示，我们在创建完成表结构的同时，数据库会自动为唯一字段和主键字段创建唯一索引和聚簇索引。

添加联合索引和覆盖索引

1
explain select phone, nick_name from tb_user where phone = 13688668889 and nick_name = 'user_88arndojw9';

创建索引前EXPLAIN：

image-20250731005441690

创建索引后EXPLAIN：

image-20250731005529181

最左前缀匹配原则

最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。

最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配。匹配列可以使用索引，其余列无法使用索引。

假设有一个联合索引 (column1, column2, column3)，其从左到右的所有前缀为 (column1)、(column1, column2)、(column1, column2, column3)（创建 1 个联合索引相当于创建了 3 个索引），包含这些列的所有查询都会走索引而不会全表扫描。

我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

我们这里简单演示一下最左前缀匹配的效果。

1
2
3
4
5
6
#可以使用索引
explain select phone from tb_user where phone = '13688668889';
#不能使用索引
explain select nick_name from tb_user where nick_name = 'user_xn5wr3hpsv';
#可以使用索引
explain select phone, nick_name from tb_user where phone = '13688668889' and nick_name = 'user_88arndojw9';

索引创建的一些注意事项

  • 索引不是越多越好，索引过多会导致优化器难以找到最佳的优化路径，反而不利于查询优化
  • 不要为经常更新的字段创建索引，更新索引字段意味着要对B+树进行删除和插入操作，这会造成更新的效率过低
  • 尽量建立联合索引而非单列索引，我们创建一个联合索引（a， b， c）只需要创建一颗B+树，但是创建三个单列索引需要创建三颗同样大小的B+树，磁盘空间占用是前者的三倍，可见我们应该优先考虑联合索引

索引失效

一、对索引列进行计算或函数操作

1
2
3
4
5
6
7
8
-- 1. 使用函数（索引失效）
SELECT * FROM users WHERE YEAR(create_time) = 2023;

-- 2. 数学运算（索引失效）
SELECT * FROM products WHERE price * 1.1 > 100;

-- 3. 字符串操作（索引失效）
SELECT * FROM users WHERE SUBSTRING(phone, 1, 3) = '138';
1
2
3
4
5
6
7
8
9
-- 改用范围查询
SELECT * FROM users 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

-- 调整计算位置
SELECT * FROM products WHERE price > 100 / 1.1;

-- 使用前缀查询
SELECT * FROM users WHERE phone LIKE '138%';

二、隐式类型转换

1
2
3
4
5
-- phone 是 varchar 类型（索引失效）
SELECT * FROM users WHERE phone = 13800138000; 

-- id 是 int 类型（索引失效）
SELECT * FROM orders WHERE id = '1001';

三、模糊查询以通配符开头

1
2
3
4
5
-- 索引失效（全表扫描）
SELECT * FROM users WHERE nick_name LIKE '%小明%';

-- 仅后缀通配符可使用索引
SELECT * FROM users WHERE nick_name LIKE '小明%';

四、复合索引未遵循最左前缀原则

1
2
3
4
5
6
7
8
9
10
-- 索引 (status, create_time)
-- 有效：使用最左列
SELECT * FROM orders WHERE status = 1;

-- 失效：跳过最左列
SELECT * FROM orders WHERE create_time > '2023-01-01';

-- 失效：未连续使用
SELECT * FROM orders 
WHERE status = 1 AND amount > 100;  -- 缺少中间的 create_time

五、使用OR连接非索引列

1
2
3
-- phone 有索引，email 无索引（索引失效）
SELECT * FROM users 
WHERE phone = '13800138000' OR email = 'test@example.com';
1
2
3
4
5
6
7
-- 方案1：为 email 添加索引
CREATE INDEX idx_email ON users(email);

-- 方案2：改用 UNION
SELECT * FROM users WHERE phone = '13800138000'
UNION
SELECT * FROM users WHERE email = 'test@example.com';

六、负向查询条件

1
2
3
-- 索引失效
SELECT * FROM users WHERE status != 1;
SELECT * FROM products WHERE id NOT IN (1001, 1002);
1
2
3
4
5
6
7
8
-- 改写为正向查询
SELECT * FROM users WHERE status IN (0, 2, 3);

-- 改用 EXISTS
SELECT * FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM excluded_ids e WHERE e.id = p.id
);

七、数据分布不均导致优化器放弃索引

当查询条件匹配 >30% 的数据时，优化器可能选择全表扫描。

八、索引列参与JOIN时类型不匹配

连接条件两边的列数据类型不一致。

1
2
3
4
5
6
7
-- users.id (BIGINT) vs orders.user_id (INT)
SELECT * 
FROM users u
JOIN orders o ON u.id = o.user_id; -- 索引失效

-- 统一数据类型
ALTER TABLE orders MODIFY user_id BIGINT;

九、错误使用 ORDER BY

1
2
3
4
5
6
7
8
-- 索引 (create_time)
SELECT * FROM logs ORDER BY create_time DESC; -- 有效

-- 失效：混合排序方向
SELECT * FROM logs ORDER BY create_time ASC, update_time DESC;

-- 失效：排序列不在索引中
SELECT * FROM users ORDER BY last_login; -- 无索引
1
2
3
4
5
-- 创建复合索引
CREATE INDEX idx_time ON logs(create_time, update_time DESC);

-- 添加 last_login 索引
CREATE INDEX idx_last_login ON users(last_login);

十、索引统计信息过期

当数据发生重大变化后，索引统计信息未更新。

索引下推

索引下推（Index Condition Pushdown，简称 ICP） 是 MySQL 5.6 版本中提供的一项索引优化功能，它允许存储引擎在索引遍历过程中，执行部分 WHERE 字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。

我们来分析一下使用和不使用索引下推对一条SQL的执行流程的影响

1
2
3
# 查询 zipcode 为 431200 且生日在 3 月的用户
# birthdate 字段使用函数索引失效
SELECT * FROM user WHERE zipcode = '431200' AND MONTH(birthdate) = 3;

没有索引下推之前，即使 zipcode 字段利用索引可以帮助我们快速定位到 zipcode = '431200' 的用户，但我们仍然需要对每一个找到的用户进行回表操作，获取完整的用户数据，再去判断 MONTH(birthdate) = 3。

有了索引下推之后，存储引擎会在使用 zipcode 字段索引查找 zipcode = '431200' 的用户时，同时判断 MONTH(birthdate) = 3。这样，只有同时满足条件的记录才会被返回，减少了回表次数。

我们将数据库简单分为Server层和存储引擎层，这里相当于把本应该由Server层执行的操作（回表查询）交给存储引擎层去做（直接判断where条件），所以称之为下推。

MySQL索引详解 | JavaGuide

感谢你看到这里! ❤️ ❤️