优化起点：先把慢查询找出来，再看执行计划
在优化任何一条查询之前，先解决「怎么知道它是慢的」。慢查询日志是最基础的入口，打开 `slow_query_log` 后，MySQL 会记录执行时间超过 `long_query_time`（默认 10 秒，实际项目中常调到 0.1 到 1 秒）的语句，日志里包含语句原文、执行耗时、被扫描的行数与是否走了索引。
关键参数是 `log_queries_not_using_indexes`，打开后会把「没有使用索引」的查询也记进日志。这在排查阶段很有价值（能快速找出缺失索引的语句），但生产环境长时间开启会因为刷屏而把磁盘撑满，所以建议只在排查窗口内临时开启，或者配合较低的阈值使用。
MySQL 8.0 起可以从 performance_schema 与 sys schema 拿到聚合视图。`sys.statement_analysis` 按总耗时排序，直接给出 digest_text、query_count、total_latency、rows_examined_avg，最适合做「先修哪条」的排序。定位到具体语句后，再对单条查询执行 `EXPLAIN` 看计划。
注意区分「慢」和「扫得多」。一条 1 秒但只扫了 20 行的查询，和一条 50 毫秒却扫了 50 万行的查询，是两个完全不同的问题。前者往往受锁等待或外部依赖影响，后者才是索引与执行计划层面的问题。在归因前先看 `rows` 与实际返回行数的比值，能省掉大量无效努力。
-- 临时开启并收紧阈值（会话级，排查窗口内使用）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.2;
SET GLOBAL log_queries_not_using_indexes = ON;
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
-- MySQL 8.0：按总耗时排序找 Top 慢查询
SELECT query,
exec_count,
avg_timer_wait / 1000000000 AS avg_ms,
avg_rows_examined AS avg_rows,
digest_text
FROM sys.statements_with_full_table_scans
ORDER BY avg_timer_wait DESC
LIMIT 20;EXPLAIN 逐字段拆解：怎么读执行计划
`EXPLAIN` 每一行代表优化器决定执行的一个步骤（table 级别），对多表查询会返回多行，行的阅读顺序就是实际执行顺序。字段里最该先看的是 `type`、`key`、`rows` 和 `Extra` 这四个，其余字段是辅助判断。
`id` 表示查询块编号，单表查询为 1，多表 JOIN 时数字越大越先执行，用来理解嵌套循环的层次。`select_type` 区分 `SIMPLE`（简单查询）、`PRIMARY`（外层表）、`SUBQUERY`、`DERIVED`（派生表）等。`table` 是访问的表名或别名，派生表和子查询会显示为 `<derived2>` 这样的临时结果。
`type` 是最关键的字段，表示表访问方式。按性能从好到差大致是：const（主键或唯一索引等值匹配，一次命中）、eq_ref（对每一行用唯一索引精确匹配，JOIN 时的理想状态）、ref（非唯一索引等值匹配，可能多行）、range（索引范围扫描）、index（全索引扫描，注意它不是全表扫描）、ALL（全表扫描）。看到 ALL 基本就是索引没生效的第一嫌疑。
`possible_keys` 是优化器认为可用的索引，`key` 是实际选用的索引，`key_len` 是使用了索引的前几个字节。`key_len` 可以用来判断联合索引用到了几列：在 utf8mb4 下一个 INT 列约 4 字节，若联合索引 `(a, b, c)` 的 key_len 正好是 a 的长度，说明只用了最左一列，这是联合索引失效最直接的证据。
`rows` 是优化器估算要读取的行数，`filtered` 是经过 WHERE 条件过滤后剩余的百分比（`rows × filtered / 100` 是预计返回行数）。注意 `rows` 和 `filtered` 都是估算值，优化器依据统计信息计算，统计信息过期会导致计划离谱，所以 `rows` 明显偏离实际时先 `ANALYZE TABLE`。
`Extra` 列是最容易被忽视但信息量最大的字段。几个危险信号：`Using filesort` 表示需要额外排序，通常意味着用不上有序索引；`Using temporary` 表示需要建临时表，中间结果集偏大时 IO 成本很高；`Using where` 配合 `type: ALL` 是全表扫描的标志；`Using join buffer (hash join)` 说明没有可用索引、只能做哈希连接（通常是坏消息）。反过来 `Using index`（覆盖索引，不回表）和 `Using index condition`（索引条件下推）是好信号。
MySQL 8.0 之后强烈建议用 `EXPLAIN FORMAT=TREE` 或 `EXPLAIN ANALYZE`，它们给出真实的执行情况而不是估算：`FORMAT=TREE` 展示算子树，`EXPLAIN ANALYZE` 实际执行并给出每步的实际行数与耗时，可以直接对比优化器的预估与真实值——差异巨大就说明统计信息有问题。注意 `EXPLAIN ANALYZE` 会真正执行语句，对 UPDATE/DELETE 千万要小心。
EXPLAIN FORMAT=TREE
SELECT o.id, o.amount, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2026-09-01'
AND o.status = 'paid'
ORDER BY o.amount DESC
LIMIT 100;
-- 真实执行：对比估算行数与实际行数
EXPLAIN ANALYZE
SELECT o.id, o.amount
FROM orders o
WHERE o.created_at >= '2026-09-01' AND o.status = 'paid'
ORDER BY o.amount DESC
LIMIT 100;
-- 统计信息过期时先刷新再看计划
ANALYZE TABLE orders;
SHOW INDEX FROM orders;常见全表扫描原因：从 type: ALL 反推问题
先确认一件事：`type: ALL` 是否一定是坏事。如果查询本身就要返回表中大部分行，索引扫描带来的随机 IO 反而比顺序全表扫描更慢，优化器选 ALL 可能是正确的。判断标准是 `rows` 与返回行数的比值——比值接近 1 时 ALL 是合理选择，比值达到几百上千才说明索引缺失。
真正的问题型全表扫描有几种典型形态。第一是 WHERE 条件所在的列没有索引。第二是索引用上了但选择性太低（例如 `status` 只有三种值的列，或 `is_deleted` 只有 0 和 1），优化器判断走索引加回表比全表扫描更慢，于是主动放弃索引。
第三是范围条件把索引的后半部分废掉了。联合索引 `(a, b)` 上如果有 `WHERE a > 10 AND b = 5`，索引只能用到 a，b 的等值条件失去作用。这种情况下为 b 单独立索引通常也没用，最终还是要靠覆盖索引或改查询形态。第四是统计信息过期，`ANALYZE TABLE` 之后计划可能就正常了。
第五种最隐蔽：排序或分页方向导致无法利用有序索引。`ORDER BY created_at DESC LIMIT 20` 通常能反向扫描索引，但一旦排序方向与索引方向不一致，或排序列不是索引的最左列，索引的有序性就用不上，计划里会出现 `Using filesort`。
-- 判断 ALL 是否合理：看扫描行数与实际返回行数的比例
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE status = 'paid';
-- 刷新统计信息后重看计划
ANALYZE TABLE orders;
-- 低选择性列不适合单独立索引，观察分布先看清代价
SELECT status, COUNT(*) AS cnt, COUNT(*) / (SELECT COUNT(*) FROM orders) AS ratio
FROM orders
GROUP BY status;
-- 覆盖索引：让查询不回表，常能直接消除 filesort / temporary
CREATE INDEX idx_orders_status_created ON orders (status, created_at);索引失效的六类场景
第一类是隐式类型转换。索引列是 VARCHAR 而查询条件传了数字（或反之），比较时会发生类型转换，转换后的列值与索引中的排序不一致，索引直接失效。这个坑在 MySQL 中尤其隐蔽，因为字符串列传入数字时**不会报错**，只是悄悄全表扫描。判断方法是把 EXPLAIN 里的 `key` 与你期望的索引对比。
第二类是索引列被函数或表达式包裹。`WHERE DATE(created_at) = '2026-10-02'` 里 `DATE()` 作用在列上，索引无法使用；改成范围条件 `created_at >= '2026-10-02' AND created_at < '2026-10-03'` 就能用上。MySQL 8.0 的函数索引（functional index）可以救一部分场景，但对表达式取值有语法限制，不是万能替代。
第三类是 LIKE 前导通配。`LIKE '%abc'` 和 `LIKE '_abc'` 因为无法确定前缀，必然全表扫描；而 `LIKE 'abc%'` 可以用索引。需要模糊搜索又有性能要求时，正确的解法是引入搜索引擎或独立的倒排表，而不是硬加索引。
第四类是联合索引违反最左前缀规则。索引 `(a, b, c)` 支持 `a`、`(a, b)`、`(a, b, c)`，但单独查 `b` 用不上。范围条件之后的列也无法用于等值匹配定位（`(a, b)` 上 `a > 1 AND b = 2` 只能用到 a）。设计索引时要把最常用、等值匹配的列放在最左边，范围条件的列往后放。
第五类是 OR 混用不同索引。`WHERE a = 1 OR b = 2` 在 a 和 b 各有独立索引时，MySQL 8.0 之前的版本往往只能选一个索引扫描；MySQL 8.0 引入了 index_merge（`Using union`）可以合并多个索引的扫描结果，但并不总是被优化器选中，且成本估算容易失准。稳妥的做法是用 `UNION ALL` 拆分后由应用层合并结果。
第六类是排序方向或类型不匹配。`ORDER BY` 的列顺序与索引相反时索引用不上；字符串列与数字列混用排序时会隐式转换；而 `CHAR` 与 `VARCHAR` 长度不同但内容相同的两列，在 JOIN 时也可能因为字符集/排序规则（collation）不一致而无法使用索引。用 `SHOW INDEX` 确认索引的 `Collation` 是否为 `A`。
-- 1) 隐式类型转换：key 会变成 NULL
SELECT * FROM users WHERE phone = 13800138000; -- phone 是 VARCHAR
-- 2) 函数包裹：改成范围条件
SELECT * FROM orders WHERE DATE(created_at) = '2026-10-02'; -- filesort / ALL
SELECT * FROM orders
WHERE created_at >= '2026-10-02' AND created_at < '2026-10-03'; -- 用得上索引
-- 3) 联合索引最左前缀
CREATE INDEX idx_ab ON t (a, b, c);
SELECT * FROM t WHERE a = 1 AND c = 3; -- 只用到 a
-- 4) OR 混用 -> UNION ALL 拆分
SELECT id, name FROM t WHERE a = 1
UNION ALL
SELECT id, name FROM t WHERE b = 2;SELECT * 的实际代价，以及怎么改写
SELECT * 的代价常被低估，它至少有四层。第一层是网络与序列化：返回的列越多，传输量与驱动层的对象构造成本越高，即使应用只读其中一列。反序列化开销还会沿着调用链扩散到 JSON 编码与前端渲染。
第二层是执行计划：一旦覆盖索引失效（典型是 `WHERE` 里有个不能用索引的条件，或需要 `ORDER BY` 一个非索引列），优化器就必须回表读取整行。要命的是，一条 `SELECT *` 写的查询可能本来能走覆盖索引，改成显式列之后反而失去了这个优势——所以列清单要跟索引设计一起考虑，不是事后补的。
第三层是 schema 演进成本。`SELECT *` 会让查询结果与表结构强耦合：别人加一列，你的应用就多收到一个字段；别人删一列或改类型，你的应用可能直接崩。显式列清单把这种隐式契约变成显式契约，是重构时能够安全推进的前提。
第四层是缓存与临时表。中间结果集（派生表、UNION、GROUP BY 的输入）越大，临时表与排序的 IO 越重，`Using temporary` 与 `Using filesort` 的代价随列数上升。超长列表还会显著放大深分页（`LIMIT 100000, 20`）的成本，因为要扫描并丢弃前面所有行。
改写时的实用原则：应用层用到的查询列显式列出；列表接口不查大字段（BLOB、JSON、长文本），改为按需详情查询；导出与报表场景如果确实需要全列，至少把过滤与排序条件改成能命中覆盖索引的形式，并在深分页场景改用「基于上一次结果的主键」做游标翻页，避开 `LIMIT` 的大 offset。
-- 改写前：全列 + 深分页，慢
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 100000, 20;
-- 改写后：显式列 + 覆盖索引
CREATE INDEX idx_user_created ON orders (user_id, created_at, id);
SELECT id, status, amount, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;
-- 深分页改游标翻页（seek method），OFFSET 为 0
SELECT id, status, amount, created_at
FROM orders
WHERE user_id = 42
AND (created_at, id) < ('2026-09-01 10:00:00', 88123)
ORDER BY created_at DESC, id DESC
LIMIT 20;