按总耗时抓慢 SQL：MySQL 8.4 慢日志、EXPLAIN、sys 视图与锁排查
内容整理自 MySQL 8.4 Reference Manual，示例结果集为示意；最后一节两条来自 8.4.11 的实测记录。
1. 方法：测量 → 排名 → 看计划 → 只改一处 → 重测
优化的对象按总耗时挑，不是按单次延迟，也不是按语句长得难看不难看。一条 5ms 但每秒跑一万次的语句，比一条 2s 每天跑三次的语句更值得动。
- 采集：慢查询日志 +
performance_schema的语句 digest。 - 按总耗时排（执行次数 × 平均耗时），再看 Rows_examined / Rows_sent 的比值。
- 对前几条看执行计划，判断是加索引、改 SQL，还是改表结构。
- 在测试环境只改一处，对比计划和耗时，再上生产。
- 跑一天真实流量后重测，把没用上的索引清掉。
2. 慢日志：默认是关的，阈值 10 秒太宽
slow_query_log 默认 OFF，long_query_time 默认 10 秒。10 秒这个值放在线上基本等于没开。
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_output = 'FILE';
SET GLOBAL long_query_time = 1; -- 秒，支持微秒
SET GLOBAL min_examined_row_limit = 100; -- 忽略扫描行太少的语句
SET GLOBAL log_slow_extra = 1; -- 更多字段，仅对 FILE 生效
SET GLOBAL slow_query_log = 1;
SET GLOBAL 立即生效，但重启就丢，要长期生效得写进 my.cnf。long_query_time 设太低日志会迅速膨胀，先撑不住的是磁盘。
参数细节：long_query_time 最小 0；min_examined_row_limit 在 long_query_time 判定之后才检查；log_queries_not_using_indexes 默认关闭，打开会灌满日志，用 log_throttle_queries_not_using_indexes 做每分钟限流（0 为不限）；log_slow_admin_statements 把慢 ALTER TABLE 也记进去；log_slow_extra 额外输出 Thread_id、Bytes_sent、Created_tmp_disk_tables、Sort_merge_passes、Start、End。
一条典型的慢日志：
# Time: 2026-10-19T09:41:12.512338Z
# User@Host: app[app] @ 10.0.2.15 [] Id: 8812
# Query_time: 4.210551 Lock_time: 0.000132 Rows_sent: 20 Rows_examined: 3184210
SET timestamp=1760866872;
SELECT * FROM orders WHERE customer_email = 'x' ORDER BY created_at DESC LIMIT 20;
Rows_sent 20，Rows_examined 3184210，基本就是索引缺失或没被用上。注意 Rows_examined 统计的是 server 层检查的行数，不是存储引擎内部读了多少行。汇总用 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log；文件配 logrotate + FLUSH SLOW LOGS 轮转，同时盯磁盘。
3. 读 EXPLAIN：type、key、rows、Extra
先看四个字段：type、key、rows、Extra。type=ALL 且 rows 很大，就是典型的没走索引。
EXPLAIN SELECT * FROM orders WHERE customer_email='x' ORDER BY created_at DESC LIMIT 20;
EXPLAIN FORMAT=TREE SELECT ...;
EXPLAIN ANALYZE SELECT ...; -- 这条会真的执行查询
示例输出：id 1，table orders，type ALL，possible_keys NULL，key NULL，rows 3184210，filtered 0.01，Extra "Using where; Using filesort"。
join type 从好到坏：system/const（通过主键或唯一键最多一行）、eq_ref（通过主键或 NOT NULL 唯一索引，前一行对应一行）、ref（通过非唯一索引或索引前缀匹配多行）、range（BETWEEN/IN()/>/LIKE 'x%'）、index（扫整棵索引树）、ALL（全表扫，最差）。
Extra 里值得注意的几个：Using filesort 是额外排序，考虑把 ORDER BY 的列做进索引；Using temporary 用了临时表，常见于 GROUP BY 和 ORDER BY 落在不同列；Using index 是覆盖索引，好；Using index condition 是 ICP，也好。
rows 是估算值，InnoDB 上可能很粗；filtered 是过滤后剩余百分比，传给下一张表的行数约为 rows × filtered/100。
修法示例：
ALTER TABLE orders ADD INDEX idx_customer_created (customer_email, created_at);
之后应该看到 type=ref、key=idx_customer_created、没有 filesort。
复合索引的顺序：等值条件列在前，范围或排序的列在后。WHERE 里别在索引列上套函数，WHERE DATE(created_at)=... 用不上索引。join 两侧列类型要对齐。也不要给所有列都加索引。
4. 用 sys schema 排名
SELECT query, db, exec_count, total_latency, avg_latency, rows_examined_avg, rows_sent_avg
FROM sys.statement_analysis LIMIT 10;
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
常用视图：statement_analysis、statements_with_runtimes_in_95th_percentile、schema_unused_indexes、schema_redundant_indexes、innodb_lock_waits、host_summary、io_global_by_file_by_bytes、memory_global_by_current_bytes、session。带 x$ 前缀的变体返回原始单位（皮秒、字节）。这些统计重启后清零，所以判断索引是否无用要跑几周，并且覆盖到月末任务之后。
5. 无用索引和冗余索引：先 INVISIBLE，再决定删不删
SELECT * FROM sys.schema_unused_indexes WHERE object_schema='shop';
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema='shop';
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE; -- 先隐藏
ALTER TABLE orders ALTER INDEX idx_old VISIBLE; -- 需要时回滚
不可见索引仍然会被维护，只是优化器忽略它，所以隐藏后出问题可以立刻恢复。主键、唯一索引、外键支撑索引即使看起来没用也不要删。主库上没用的索引，从库上可能还在给报表查询用，删之前把从库也过一遍。
6. buffer pool 在线 resize：取整规则与阻塞
大小必须是 innodb_buffer_pool_chunk_size（默认 128M）× innodb_buffer_pool_instances 的整数倍，不是整数倍会向上取整。手册的例子：16 个实例下设 9G，实际会变成 10G。chunk 数量（size / chunk_size）别超过 1000。chunk_size 和实例数只能在启动时定。专用服务器上，物理内存的 50%–75% 是常见起点。
SET GLOBAL innodb_buffer_pool_size = 16 * 1024 * 1024 * 1024;
SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
SELECT variable_name, variable_value FROM performance_schema.global_status
WHERE LOWER(variable_name) LIKE 'innodb_buffer_pool_resize%';
状态码：0 未 resize，1 开始，2 关闭 AHI，3 回收 block，4 取全局锁，5 调整 pool，6 调整 hash，7 失败。
resize 不会立刻开始，要等所有活跃事务结束；执行期间，新事务和需要 buffer pool 的操作都得等。所以放低峰做，进度用 --log-error-verbosity=3 看。
命中率：
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
miss ratio = reads / read_requests。稳态下超过约 1%，说明内存不够或者索引不行。
7. 锁等待与死锁
SELECT * FROM sys.innodb_lock_waits\G
SELECT trx_id, trx_state, trx_started, trx_rows_locked, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 10;
锁等待的表现是应用变慢但 CPU 不高。
死锁检测默认开启，InnoDB 回滚改动行数最少的那个小事务，应用收到 1213，必须重试。只有 innodb_table_locks=1（默认）且 autocommit=0 时，InnoDB 才感知表锁，否则靠 innodb_lock_wait_timeout 兜底。等待图过大——超过 200 个事务或检查了 100 万把锁——会被当作死锁处理，报 TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH。极高并发下检测本身会成为负担，innodb_deadlock_detect=OFF 是最后手段。
SHOW ENGINE INNODB STATUS\G -- LATEST DETECTED DEADLOCK 段
SET GLOBAL innodb_print_all_deadlocks=ON;
SET SESSION innodb_lock_wait_timeout=10;
减少死锁：表和行的访问顺序保持一致，事务写短，加索引让语句锁更少的行，考虑 READ COMMITTED，以及永远对 1213 做重试。
8. 两个实测容易踩的点（MySQL 8.4.11）
KILL QUERY 不释放锁。 KILL QUERY 42 只中断正在执行的语句，会话还开着；KILL CONNECTION 42（即 KILL 42）才关会话并回滚事务。实测：一个会话执行 UPDATE 没提交，随后进入 Sleep，此时 KILL QUERY 根本没有可中断的语句，被阻塞的一方一直等到 KILL 才拿到锁。
blocking_query 不是加锁语句。 sys.innodb_lock_waits 的列有 wait_age、locked_table、waiting_pid、waiting_query、blocking_pid、blocking_query、sql_kill_blocking_query、sql_kill_blocking_connection。blocking_query 显示的是阻塞者"当前正在跑的语句"，比如 SELECT SLEEP(7)，并不是真正加锁的那条 UPDATE，别照着它去优化。另外 Innodb_row_lock_time 只在一次等锁结束时才累加（测试里等结束后记到 17284 ms），判断"现在是否有人在等"要用 Innodb_row_lock_current_waits 或直接查 sys.innodb_lock_waits。
补两条：SET PERSIST slow_query_log=ON、SET PERSIST long_query_time=1 会写进 mysqld-auto.cnf，重启仍生效，比 SET GLOBAL 省事；CHECK TABLE 不做修复，大 InnoDB 表上还可能阻塞其他线程，离线校验 .ibd 用 innochecksum。
每周过一遍
按总延迟排的前十条语句；慢日志量的趋势和最差的 Rows_examined / Rows_sent 比值；buffer pool miss ratio、Created_tmp_disk_tables、Sort_merge_passes；过去一周的锁等待与死锁，以及应用侧重试逻辑是否覆盖 1213；上线前再看一眼无用/冗余索引列表。