2361 字
12 分钟
MySQL 索引与慢查询优化
2026-11-15

主线:抓慢 SQL → 看执行计划 → 建对索引 → 复验#

上一篇 MySQL 常用命令与操作实战 解决了「怎么操作」,这一篇解决「为什么慢」。MySQL 查询优化不需要玄学,流程就四步:慢查询日志负责抓现行,EXPLAIN 负责解释原因,索引负责解决问题,最后复验数字。这一篇把四步全部走通,索引原理部分可以和 PostgreSQL 索引与查询优化入门 对照着看,两边思路相通、细节有别。

测试表沿用订单场景,造一百万行:

CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id),
KEY idx_user (user_id)
) ENGINE = InnoDB;
INSERT INTO orders (user_id, status, amount, created_at)
SELECT FLOOR(1 + RAND() * 10000),
ELT(1 + FLOOR(RAND() * 3), 'paid', 'pending', 'cancelled'),
ROUND(RAND() * 1000, 2),
NOW() - INTERVAL FLOOR(RAND() * 365) DAY
FROM information_schema.columns a, information_schema.columns b
LIMIT 1000000;

第一步:用慢查询日志抓元凶#

安装篇的 my.cnf 已经开启了慢查询日志(阈值 long_query_time = 1 秒)。确认状态并临时调低阈值抓现行:

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
-- 本会话临时把阈值降到 0.1 秒观察(全局修改需 SUPER 权限)
SET GLOBAL long_query_time = 0.1;

原始日志可读性一般,用官方自带的 mysqldumpslow 做聚合统计,按总耗时找最值得优化的 SQL:

Terminal window
# 按累计总时间排序,取前 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 按出现次数排序,找高频小慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

优化优先级看两个维度:总耗时 = 单次耗时 × 出现次数。一条 3 秒的 SQL 一天跑 3 次,不如一条 0.3 秒的 SQL 一天跑十万次更值得先治。

第二步:看懂 EXPLAIN#

EXPLAIN SELECT * FROM orders WHERE status = 'paid' AND created_at >= '2026-06-01';

重点盯五列:

列看什么危险信号
type访问方式,从好到差:system > const > eq_ref > ref > range > index > ALLALL 即全表扫描,大表要警惕
key实际用到的索引为 NULL 说明没用上索引
rows预估扫描行数与实际返回行数差几个数量级就不对劲
filtered条件过滤后剩余比例很低说明大量行被读出来又扔掉
Extra附加信息Using filesort、Using temporary 要重点看

EXPLAIN ANALYZE(8.0.18+)会真实执行并给出每一步的实际耗时与行数,排障时优先用它。JSON 格式更详细:EXPLAIN FORMAT=JSON,能看到成本估算细节。

第三步:索引怎么建才有效#

联合索引与最左前缀#

ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

联合索引 (a, b, c) 能支撑的查询形态:a、a+b、a+b+c、a+b+范围(c)。跳过 a 直接查 b、c,索引用不上,这就是最左前缀原则,与 PostgreSQL 的规则一致。列顺序口诀:等值条件在前,范围条件在后;等值列之间把区分度高的放前面。

覆盖索引#

查询要的列全部包含在索引里时,Extra 显示 Using index,不需要回表查聚簇索引:

-- 这条查询只取 user_id、status、amount
-- 若索引为 (user_id, status, amount),即为覆盖索引
SELECT user_id, status, amount FROM orders WHERE user_id = 42;

前缀索引#

长字符串列(如 URL、邮箱)全列建索引太占空间,可以只索引前 N 个字符:

ALTER TABLE users ADD INDEX idx_email_prefix (email(16));

代价是无法用索引完成排序与覆盖查询,N 的取值要按实际区分度测试决定。

索引失效的八大经典场景#

索引建了却不走,是日常最高频的问题。对照这张表逐项排查:

#失效场景示例改法
1对列做函数运算WHERE DATE(created_at) = '2026-01-01'改写为范围条件 >= '2026-01-01' AND < '2026-01-02',或建函数索引
2隐式类型转换字段是 VARCHAR 却传数字 WHERE phone = 13800138000参数加引号,类型对齐
3前导模糊查询LIKE '%abc'前缀匹配 LIKE 'abc%' 可用索引;全文需求上全文索引
4联合索引跳列只查 b 列补齐最左列或单独建索引
5OR 两侧列不一致WHERE user_id = 1 OR status = 'paid'(status 无索引)拆 UNION,或给两列都建索引
6范围后列失效(a,b,c) 上 a = 1 AND b > 2 AND c = 3c 用不上索引排序/定位,调整列序或接受现状
7不等于与 NOT INWHERE status <> 'paid' 选择性差优化器常主动放弃,属正常决策居多
8低区分度列单建索引给 status 这种三值列单建索引并入联合索引,别单建
WARNING

看到 type = ALL 先别急着下「缺索引」的结论。小表全表扫描、或查询本来就要返回大半张表时,优化器放弃索引是正确决策。强行 FORCE INDEX 把慢查询「修好」的案例,十有八九在数据量变化后翻倍反弹。先看 rows 与实际返回行数的比例再判断。

其它索引类型速览#

除了最常用的 B-Tree(普通、唯一、联合、前缀都属此类),MySQL 还有几种专用索引,知道边界即可:

索引类型用途关键限制
唯一索引(UNIQUE)保证列值不重复,顺带加速查询业务唯一性应由它兜底,不要只靠应用层判断
全文索引(FULLTEXT)对 TEXT / VARCHAR 做关键词检索中文需用 ngram 解析器:WITH PARSER ngram
空间索引(SPATIAL)地理坐标的范围查询列须为几何类型且建索引时列不能为 NULL
函数索引(8.0.13+)给表达式结果建索引索引定义里表达式要加双层括号

函数索引正好对应失效场景表里的第 1 条,示例:

-- 老办法:改写查询为范围条件(优先)
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02';
-- 改不了查询时:给表达式建索引
ALTER TABLE orders ADD INDEX idx_date_created ((DATE(created_at)));

全文索引的中文用法:

ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body) WITH PARSER ngram;
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);

数据量与检索质量要求再上一个台阶时,应考虑 Elasticsearch 等专用搜索引擎,MySQL 全文索引适合的是中小规模站内搜索。

统计信息:优化器的「眼睛」也要保养#

优化器选不选索引、选哪个,依据的是统计信息(基数估算)。数据大幅变化后统计过期,是「同样的 SQL 以前快、现在慢」的常见原因:

-- 手动刷新某张表的统计信息
ANALYZE TABLE orders;
-- 查看索引的基数估算(某列 distinct 值约数)
SHOW INDEX FROM orders;

InnoDB 默认开启自动统计采样,一般不用人工干预;但在大批量导入、删除归档之后,手动 ANALYZE TABLE 一次成本很低、收益明确。还要注意直方图(Histogram)这个进阶工具:对分布严重倾斜的列(如 status 里 99% 是同一值),给优化器补一份直方图能显著改善行数估算:

ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;

第四步:复验与持续观察#

改完索引重新 EXPLAIN,并对比慢查询日志里同一条 SQL 的耗时变化。还可以借助 Performance Schema 做持续聚合统计:

-- 按总耗时排序的 SQL 摘要(sys 库格式化版本)
SELECT query, exec_count, total_latency, avg_latency
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

更完整的监控体系与告警,见系列收官篇 数据库性能监控与常见故障排障。

还有两个配套习惯值得养成:

  1. 索引命名规范:普通索引 idx_ 前缀、唯一索引 uk_ 前缀,后面跟列名缩写,半年后看名字就知道用途。
  2. 删索引要谨慎:SHOW INDEX FROM orders; 盘点后,疑似无用的索引先改名为观察(8.0 支持 ALTER TABLE ... ALTER INDEX idx INVISIBLE;),确认无影响再真正删除:
ALTER TABLE orders ALTER INDEX idx_user INVISIBLE; -- 先隐形观察
ALTER TABLE orders DROP INDEX idx_user; -- 确认无恙再删

常见问题#

Q:EXPLAIN 里 key 用了索引,为什么还是慢? 可能扫描行数本身就大(rows 很高),或回表次数太多。看 Extra 有没有 Using filesort(排序没走索引)、回表量能否用覆盖索引消掉,或者查询条件本身区分度不足。

Q:Using filesort 一定要消除吗? 不一定。小结果集的内存排序很快;只有大行数排序才值得为它调整索引列序或改写查询。

Q:新加的索引线上没生效? 先 ANALYZE TABLE orders; 刷新统计信息;再确认查询写法没踩失效场景表;最后检查是否被优化器认为成本更高而主动放弃——用 EXPLAIN FORMAT=JSON 看成本对比。

Q:索引是不是建在 WHERE 里出现最多的列上就行? 还要看 JOIN 的关联列、ORDER BY / GROUP BY 的列。三者综合考虑,联合索引设计本质是给「过滤 + 关联 + 排序」的组合买单。

小结#

  • 优化四步走:慢日志抓 SQL、EXPLAIN 定原因、索引解决问题、复验看数字,优先级按总耗时排序。
  • EXPLAIN 五个关键列:type、key、rows、filtered、Extra。
  • 联合索引守最左前缀,等值在前、范围在后;覆盖索引靠免回表提速。
  • 索引失效八场景对照排查;优化器放弃索引有时是正确的,别滥用 FORCE INDEX。

MySQL 部分到此完整。下一篇切换到 Redis:Redis 安装与初始配置。

MySQL 索引与慢查询优化
https://www.anexns.cloudns.org/posts/mysql-index-slow-query/
作者
Anexns
发布于
2026-11-15
许可协议
CC BY-NC-SA 4.0