主线:抓慢 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) DAYFROM information_schema.columns a, information_schema.columns bLIMIT 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:
# 按累计总时间排序,取前 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 > ALL | ALL 即全表扫描,大表要警惕 |
| 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 列 | 补齐最左列或单独建索引 |
| 5 | OR 两侧列不一致 | WHERE user_id = 1 OR status = 'paid'(status 无索引) | 拆 UNION,或给两列都建索引 |
| 6 | 范围后列失效 | (a,b,c) 上 a = 1 AND b > 2 AND c = 3 | c 用不上索引排序/定位,调整列序或接受现状 |
| 7 | 不等于与 NOT IN | WHERE 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_latencyFROM sys.statement_analysisORDER BY total_latency DESCLIMIT 10;更完整的监控体系与告警,见系列收官篇 数据库性能监控与常见故障排障。
还有两个配套习惯值得养成:
- 索引命名规范:普通索引
idx_前缀、唯一索引uk_前缀,后面跟列名缩写,半年后看名字就知道用途。 - 删索引要谨慎:
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 安装与初始配置。