数据库慢查询是后端开发最常踩的坑之一。明明SQL写得没问题,表数据量也不大,查询却卡在2秒、5秒甚至更久。绝大多数情况下,问题都出在索引上。本文从索引原理出发,结合真实场景讲解如何用 EXPLAIN 定位问题并优化。
一、索引的本质:B+树
InnoDB 引擎的索引采用 B+ 树结构。和二叉树不同,B+ 树每个节点可以有多个子节点,整棵树矮胖,3~4 层就能存储千万级数据。这意味着一次查询只需要 3~4 次磁盘IO即可定位到记录。
没有索引时,MySQL只能从第一行扫到最后一行(全表扫描);有了索引,相当于字典有目录,可以快速翻到目标页。
二、常见索引类型
- 主键索引(聚簇索引):叶子节点直接存储整行数据,一张表只能有一个。
- 普通索引(二级索引):叶子节点存储主键值,需要"回表"查询完整数据。
- 唯一索引:值唯一,可为 NULL。
- 联合索引:多列组合,遵循最左前缀原则。
- 覆盖索引:查询字段都在索引中,无需回表。
- 全文索引:用于文本检索,一般用 Elasticsearch 替代。
三、创建索引的正确姿势
-- 创建表时声明
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(128) NOT NULL,
phone VARCHAR(20),
status TINYINT,
created_at DATETIME,
UNIQUE KEY uk_email (email),
KEY idx_phone (phone),
KEY idx_status_created (status, created_at)
);
-- 已有表添加索引
ALTER TABLE users ADD INDEX idx_status_created (status, created_at);
联合索引 (status, created_at) 的顺序很关键。最左前缀原则意味着可以用于 WHERE status=? 或 WHERE status=? AND created_at>?,但不能用于 WHERE created_at>?。
四、EXPLAIN:执行计划分析
在任何 SQL 前加 EXPLAIN 就能看到 MySQL 的执行计划。重点关注以下字段:
EXPLAIN SELECT * FROM users
WHERE status = 1 AND created_at > '2026-01-01';
- type:访问类型,从好到差:
system > const > eq_ref > ref > range > index > ALL。出现 ALL 表示全表扫描,必须优化。 - key:实际使用的索引。NULL 表示没用索引。
- rows:预估扫描行数,越少越好。
- Extra:额外信息。
Using index表示覆盖索引;Using filesort表示需要额外排序;Using temporary表示用了临时表,都要警惕。
五、慢查询优化示例
示例1:函数导致索引失效
-- 索引失效,全表扫描
SELECT * FROM users WHERE DATE(created_at) = '2026-07-22';
-- 改写为范围查询,命中索引
SELECT * FROM users
WHERE created_at >= '2026-07-22 00:00:00'
AND created_at < '2026-07-23 00:00:00';
对索引列使用函数、计算、类型转换都会让索引失效。这是最常见的性能杀手。
示例2:LIKE 左模糊
-- 索引失效
SELECT * FROM users WHERE name LIKE '%张';
-- 可以走索引
SELECT * FROM users WHERE name LIKE '张%';
示例3:隐式类型转换
-- phone 是 VARCHAR,传入数字导致隐式转换,索引失效
SELECT * FROM users WHERE phone = 13800138000;
-- 正确写法:加引号
SELECT * FROM users WHERE phone = '13800138000';
示例4:分页深度问题
-- LIMIT 越深越慢,需扫描前面所有行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 用主键定位 + JOIN 优化
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;
六、索引的代价
索引不是越多越好。每个索引都是一棵独立的 B+ 树,会带来三方面成本:
- 磁盘空间:索引本身占用存储。
- 写入性能:INSERT/UPDATE/DELETE 需要同步维护索引。
- 优化器选择成本:索引多了,优化器选错索引的概率也会上升。
建议单表索引数量控制在 5 个以内,联合索引列数不超过 5 列。
七、慢查询日志的开启与分析
-- my.cnf 配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
-- 分析慢日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
线上务必开启慢查询日志,定期用 pt-query-digest 或 mysqldumpslow 分析TOP N 慢SQL,按影响范围排优先级逐条优化。
八、小结
索引优化是数据库性能调优的核心。核心心法只有三句话:先看 EXPLAIN 是否走索引、再看 rows 与 Extra 是否合理、最后审查 SQL 是否触发了索引失效的常见陷阱。把这些套路内化之后,绝大多数慢查询都能在不增加硬件成本的前提下提升一个数量级。