MySQL 高级优化

官方文档：https://dev.mysql.com/doc/refman/8.0/en/optimization.html
最后更新：2026-04-11

一、执行计划深度解读

EXPLAIN 基本用法

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;

EXPLAIN 各列含义

select_type 值说明

type 访问类型（性能从高到低）

生产环境要求：至少达到 range，核心查询要求 ref 及以上。

Extra 字段重要值解读

EXPLAIN FORMAT=JSON

-- 输出 JSON 格式，包含 cost 估算信息
EXPLAIN FORMAT=JSON
SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 1;

JSON 输出中重点关注：

• cost_info.query_cost：整体代价估算
• rows_examined_per_scan：每次扫描的行数
• using_index：是否使用覆盖索引

EXPLAIN ANALYZE（MySQL 8.0+）

-- 实际执行并返回每步的真实耗时和行数
EXPLAIN ANALYZE
SELECT * FROM orders WHERE created_at > '2026-01-01';

输出示例（树状格式）：

-> Filter: (orders.created_at > '2026-01-01')  (cost=102.50 rows=312) (actual time=0.045..1.234 rows=300 loops=1)
    -> Index range scan on orders using idx_created_at  (cost=102.50 rows=312) (actual time=0.040..0.980 rows=300 loops=1)

actual time=开始..结束 单位为毫秒，loops 为该节点执行次数。

二、索引优化

最左前缀原则

联合索引 INDEX idx_abc (a, b, c) 的命中规则：

规律：从索引最左列开始，遇到范围查询（>、<、BETWEEN、LIKE 'prefix%'）则后续列失效。

覆盖索引（Covering Index）

查询所需的列全部包含在索引中，无需回表读取行数据，EXPLAIN 的 Extra 显示 Using index。

-- 假设有索引 INDEX idx_user_status (user_id, status, created_at)
-- 以下查询可完全走覆盖索引，不回表
SELECT user_id, status, created_at
FROM orders
WHERE user_id = 100;

设计覆盖索引的思路：将 SELECT 列表中频繁出现的列追加到联合索引末尾。注意不要把大字段（TEXT、BLOB）放入索引。

索引下推（ICP，Index Condition Pushdown）

MySQL 5.6+ 默认开启。在存储引擎层利用索引中的列提前过滤，减少回表次数。

-- 索引 INDEX idx_name_age (last_name, age)
-- 未开启 ICP：引擎先用 last_name 找到所有行，回表，Server 层再过滤 age
-- 开启 ICP：引擎用 last_name 定位后，在索引中直接判断 age 条件，减少回表
SELECT * FROM users WHERE last_name LIKE 'Zhang%' AND age > 25;

EXPLAIN 的 Extra 显示 Using index condition 即表示 ICP 生效。

-- 手动关闭/开启
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

联合索引字段顺序选择原则

-- 查询区分度
SELECT
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity
FROM orders;
-- user_id 区分度远高于 status，应放在前面
-- 推荐：INDEX (user_id, status) 而非 INDEX (status, user_id)

索引失效场景

修复方案：

-- 函数 -> 改写为范围查询
-- 原：WHERE YEAR(created_at) = 2026
-- 改：
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

-- 隐式类型转换 -> 显式转换或统一类型
WHERE phone = '13812345678'

FORCE INDEX 与 IGNORE INDEX

-- 强制使用指定索引（优化器选错索引时使用）
SELECT * FROM orders FORCE INDEX (idx_user_id)
WHERE user_id = 100 ORDER BY created_at DESC;

-- 忽略某个索引（避免优化器使用不合适的索引）
SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE user_id = 100;

-- 查看表上所有索引
SHOW INDEX FROM orders;

三、慢查询分析

开启慢查询日志

-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 动态开启（重启后失效，生产建议写入配置文件）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;           -- 超过 1 秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = ON;  -- 未使用索引的查询也记录

my.cnf 配置：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10  -- 每分钟最多记录 10 条无索引查询，避免日志膨胀

mysqldumpslow 分析

# 按总耗时取前 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 过滤含 user 的语句，按次数排序
mysqldumpslow -s c -g "user" /var/log/mysql/slow.log

pt-query-digest

Percona Toolkit 中功能最强的慢查询分析工具，支持按指纹分类聚合。

# 安装
apt install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log

# 只显示最慢的 5 类查询
pt-query-digest --limit 5 /var/log/mysql/slow.log

# 分析 tcpdump 抓包
pt-query-digest --type tcpdump traffic.cap

# 将结果写入数据库便于对比
pt-query-digest \
    --review h=127.0.0.1,D=percona,t=query_review \
    --history h=127.0.0.1,D=percona,t=query_history \
    /var/log/mysql/slow.log

performance_schema 查询分析

-- 开启 performance_schema（MySQL 5.6+ 默认开启）
SHOW VARIABLES LIKE 'performance_schema';

-- 查看执行次数最多的 TOP 10 语句
SELECT
    digest_text,
    count_star,
    avg_timer_wait / 1e9 AS avg_ms,
    sum_timer_wait / 1e9 AS total_ms,
    sum_rows_examined,
    sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- 查看某条语句的详细等待事件
SELECT event_name, count_star, avg_timer_wait / 1e9 AS avg_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name NOT LIKE 'idle%'
ORDER BY sum_timer_wait DESC
LIMIT 20;

-- sys schema 提供更友好的视图（MySQL 5.7.7+）
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.statements_with_sorting LIMIT 10;
SELECT * FROM sys.statements_with_temp_tables LIMIT 10;

四、锁与事务优化

锁的类型

间隙锁只在 RR（REPEATABLE READ）隔离级别下存在，降级为 RC（READ COMMITTED）可消除间隙锁，但会引入幻读问题。

-- 查看当前事务隔离级别
SELECT @@transaction_isolation;

-- 修改为 READ COMMITTED（减少间隙锁）
SET SESSION transaction_isolation = 'READ-COMMITTED';

死锁检测与解决

-- 查看最近一次死锁详情
SHOW ENGINE INNODB STATUS\G

重点关注 LATEST DETECTED DEADLOCK 段落，格式说明：

*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 5 sec starting index read
-- 事务 1 的 SQL 语句
*** (1) HOLDS THE LOCK(S):
-- 事务 1 持有的锁
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
-- 事务 1 等待的锁

*** (2) TRANSACTION:
-- 事务 2 信息（结构同上）

*** WE ROLL BACK TRANSACTION (1)
-- InnoDB 选择回滚代价较小的事务

-- 查看当前所有锁等待（MySQL 8.0）
SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- 强制终止阻塞事务
KILL <blocking_thread_id>;

死锁预防原则：

1. 多个事务访问多张表时，保持相同的加锁顺序
2. 减少事务粒度，尽快提交
3. 使用 SELECT ... FOR UPDATE 时加 NOWAIT 或 SKIP LOCKED

长事务危害与排查

长事务会导致：undo log 无法回收（磁盘膨胀）、锁长时间持有（阻塞其他事务）、主从延迟加大。

-- 查看当前运行超过 60 秒的事务
SELECT
    trx_id,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
    trx_mysql_thread_id,
    trx_query,
    trx_rows_locked,
    trx_rows_modified
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY duration_sec DESC;

-- 设置最大事务执行时间（MySQL 5.7.4+）
SET GLOBAL innodb_trx_rollback_on_timeout = ON;
SET GLOBAL innodb_lock_wait_timeout = 10;  -- 秒

应用层规范：

• 不在事务中调用外部 HTTP 接口
• 不在事务中执行复杂的业务逻辑循环
• 框架层配置事务超时（如 Spring @Transactional(timeout=30)）

锁等待超时配置

-- 生产建议：降低锁等待超时，快速失败
SET GLOBAL innodb_lock_wait_timeout = 10;

五、Buffer Pool 与 IO 优化

innodb_buffer_pool_size

Buffer Pool 是 InnoDB 最重要的内存区域，缓存数据页和索引页。

-- 查看当前配置
SHOW VARIABLES LIKE 'innodb_buffer_pool%';

-- 查看 Buffer Pool 命中率（应 > 99%）
SELECT
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
    AS buffer_pool_hit_rate
FROM (
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) a,
(
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) b;

# my.cnf 配置示例（16GB 内存服务器）
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8      -- 每个实例约 1.5GB
innodb_buffer_pool_dump_at_shutdown = ON  -- 关机时持久化热点页列表
innodb_buffer_pool_load_at_startup = ON   -- 启动时预热，避免冷启动

innodb_flush_log_at_trx_commit

控制 redo log 的刷盘策略，直接影响性能与数据安全的权衡：

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

# my.cnf
innodb_flush_log_at_trx_commit = 1  -- 生产推荐
sync_binlog = 1                     -- 与上面配合，保证 binlog 也每次刷盘

六、最佳实践

查询优化检查清单

1. EXPLAIN 确认 type 不是 ALL，Extra 无 Using filesort / Using temporary
2. 确认 key 列使用了预期的索引
3. rows 列的值合理（不应扫描全表行数）
4. 对高频查询考虑覆盖索引
5. 慢查询日志持续监控，阈值设置为 1 秒

索引维护

-- 查看索引使用情况（performance_schema）
SELECT
    object_schema,
    object_name,
    index_name,
    count_read,
    count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_db'
ORDER BY count_read DESC;

-- 找出未使用的索引（count_read = 0 且存在较长时间）
SELECT object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_db'
  AND index_name IS NOT NULL
  AND count_star = 0
ORDER BY object_name;

-- 重建索引（消除碎片）
ALTER TABLE orders ENGINE = InnoDB;
-- 或（MySQL 5.6+ 在线操作）
ALTER TABLE orders DROP INDEX idx_old, ADD INDEX idx_new (col1, col2), ALGORITHM=INPLACE, LOCK=NONE;

七、踩坑与注意事项

分页大偏移性能问题

-- 坏写法：LIMIT 100000, 20 需要扫描 100020 行，只返回 20 行
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 方案一：游标分页（适合顺序翻页）
-- 记录上一页最后一条记录的 id
SELECT * FROM orders WHERE id > 100050 ORDER BY id LIMIT 20;

-- 方案二：延迟关联（先查主键，再 JOIN 回原表）
SELECT o.*
FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) t ON o.id = t.id;

游标分页的限制：只能顺序翻页，不能跳页；适合下拉加载、滚动分页等场景。

COUNT(*) vs COUNT(1) vs COUNT(col)

结论：统计总行数始终用 COUNT(*)，仅当需要排除 NULL 时用 COUNT(col)。

InnoDB 的 COUNT(*) 没有 MyISAM 那样的计数器，必须扫描索引树。对超大表的频繁 COUNT，可以：

• 使用单独的计数表维护总数
• 使用 Redis 缓存计数

其他常见坑

-- 坑 1：UPDATE/DELETE 忘加 WHERE，全表修改
-- 防护：SET SQL_SAFE_UPDATES = 1

-- 坑 2：IN 子查询返回 NULL 导致整个结果为空
-- NOT IN (1, 2, NULL) 永远返回空结果，因为 x != NULL 无法确定
-- 改用 NOT EXISTS 或确保子查询结果无 NULL

-- 坑 3：JOIN 两张大表不走索引
-- 检查 JOIN 字段的数据类型是否一致、字符集是否一致

-- 坑 4：ORDER BY RAND() 极慢
-- 原因：对全表每行计算随机数，再排序
-- 改写：
SELECT * FROM users
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users)))
ORDER BY id
LIMIT 1;

最佳实践

在修改查询前先用 EXPLAIN 分析，确认命中了索引：养成先 EXPLAIN SELECT ... 的习惯，确认 type 不是 ALL（全表扫描），key 列显示了使用的索引，再评估是否需要优化。

复合索引按选择性从高到低、按查询条件顺序排列：选择性高的列（值种类多）放在最左，利用"最左前缀"规则；并非越多索引越好，每个索引增加写入开销。

避免在 WHERE 子句中对索引列做函数运算或隐式类型转换：WHERE DATE(created_at) = '2026-01-01' 会让索引失效，改为范围条件 WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'。

大量写入时关闭唯一索引检查（批量导入场景）：SET unique_checks = 0; SET foreign_key_checks = 0; 在批量 INSERT 前关闭检查，完成后再开启，可大幅提升写入速度，但必须确保数据本身无重复。

定期用 ANALYZE TABLE 更新统计信息：优化器的执行计划依赖表统计信息，大量数据变动后（如导入、删除大量记录），统计信息可能过期导致优化器选错索引，手动 ANALYZE 触发更新。

常见陷阱

陷阱：SELECT * 查询走了索引但仍然很慢

现象： EXPLAIN 显示用了索引，但实际查询依然耗时数秒。

原因： SELECT * 选取了索引以外的列，触发"回表"（从索引叶子节点回到主键索引取完整行数据）。若数据量大，回表次数与全表扫描相当，反而不如覆盖索引快。

解决： 只 SELECT 需要的列；或为高频查询建立覆盖索引（把查询所需列都包含在索引中，避免回表）。

-- 回表查询（慢）
SELECT * FROM orders WHERE status = 'pending';

-- 覆盖索引（把 status, id, created_at 都加入联合索引）
SELECT id, created_at FROM orders WHERE status = 'pending';

陷阱：OR 条件导致索引失效

现象： 查询含 WHERE a = 1 OR b = 2，EXPLAIN 显示全表扫描，即使 a 和 b 各自都有索引。

原因： MySQL 对 OR 的处理取决于优化器：若两侧列不在同一个联合索引里，优化器可能选择全表扫描而非 Index Merge。

解决： 改用 UNION ALL 把两个条件分开查询，让每个子查询独立命中索引，然后合并结果。

-- 可能全表扫描
SELECT * FROM t WHERE a = 1 OR b = 2;

-- 各自命中索引
SELECT * FROM t WHERE a = 1
UNION ALL
SELECT * FROM t WHERE b = 2 AND a != 1;

陷阱：更新高频表后执行计划突然变慢

现象： 某张表在批量导入或大量删除后，原本快的查询突然变得极慢，重建索引也没效果。

原因： MySQL 优化器依赖统计信息（information_schema.STATISTICS）选择索引，批量操作后统计信息严重失真，优化器选错执行计划。

解决： 执行 ANALYZE TABLE table_name; 强制更新统计信息；对于 InnoDB，也可 OPTIMIZE TABLE 重建表和索引（会锁表，生产谨慎使用）。

参见

• MySQL基础完全指南
• 数据库设计规范
• Redis完全指南
• PostgreSQL完全指南