Skip to main content
性能优化的核心是“度量-诊断-验证”。本节从慢查询日志、EXPLAIN/ANALYZE、索引与 SQL 重写、关键配置入手给出系统化方法，并介绍性能观测工具。

​
优化流程速记

1

发现问题

通过慢查询日志、应用监控、APM 捕获耗时 SQL 或资源瓶颈。
2

定位根因

使用 EXPLAIN、performance_schema、系统指标分析问题来源。
3

制定方案

从索引调整、SQL 重写、缓存策略与参数调优中选择最合适的手段。
4

验证与回归

在测试环境验证效果，再平滑上线并持续监测。

​
定位慢查询

-- 开启慢查询日志（示例）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5; -- 超过 0.5s 记为慢
SET GLOBAL log_output = 'FILE';
分析工具：mysqldumpslow、pt-query-digest（Percona），或使用云厂商/监控平台提供的慢日志分析。
线上变更全局变量前确认是否会影响其他实例；可优先设置 SET SESSION long_query_time = ... 对当前会话生效。

​
执行计划与验证

EXPLAIN SELECT ...;
EXPLAIN ANALYZE SELECT ...; -- 8.0+ 返回实际耗时与行数
关注字段：访问类型（type）、命中索引（key）、扫描行数（rows）、Extra 中是否 Using filesort/temporary。 可配合 optimizer_trace 深入理解优化器决策： 
SET optimizer_trace="enabled=on";
EXPLAIN FORMAT=JSON SELECT ...;
SELECT trace FROM information_schema.optimizer_trace\G

​
索引与 SQL 重写

  • 避免 SELECT *，明确需要的列，争取覆盖索引
  • 合理的复合索引顺序：高选择性列优先；与 WHERE、JOIN、ORDER BY 对齐
  • 使用等值连接优先于子查询；必要时用 EXISTS 替代 IN
  • 分页优化：WHERE id > ? ORDER BY id LIMIT n 而非大量 OFFSET
  • 使用虚拟列和功能性索引帮助命中表达式条件
-- 覆盖索引示例
CREATE INDEX idx_orders_user_status_created ON orders(user_id, status, created_at);
SELECT user_id, status, created_at FROM orders
WHERE user_id = 1 AND status = 'PAID'
ORDER BY created_at DESC LIMIT 20;

​
关键参数参照

  • innodb_buffer_pool_size：通常设为物理内存的 50%~70%
  • innodb_log_file_size：大事务场景适当增大，减少写入阻塞
  • innodb_flush_log_at_trx_commit：1 最安全；2/0 性能更好但可能丢失 1 秒内事务
  • max_connections：结合连接池使用，避免过大导致上下文切换
  • query_cache_type/query_cache_size：MySQL 8.0 移除查询缓存，建议通过应用层缓存
参数调整前请备份配置并记录当前值，逐步调节并监控效果，避免一次改动过多项。

​
观测与统计

  • performance_schema + sys schema：方便查看热点表/索引、语句摘要
  • sys.schema_table_statistics：按表统计读写量
  • sys.statement_analysis：按摘要查看平均/最大执行时间
  • INFORMATION_SCHEMA.TABLE_STATISTICS：统计信息检查
SELECT * FROM sys.schema_table_statistics ORDER BY rows_fetched DESC LIMIT 10;
SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10;

​
资源瓶颈排查

  • CPU 高：确认是否大量排序、哈希或全表扫描；考虑索引优化或拆分
  • IO 高：检查缓冲池是否足够、是否存在大查询或备份任务
  • 内存紧张：查看 SHOW ENGINE INNODB STATUS 中缓冲池命中率
  • 连接数暴涨：应用是否缺乏连接池、是否存在长事务
可以使用 performance_schema.events_waits_summary_global_by_event_name 分析等待事件。

​
Profiling 与基线

SET profiling = 1;
SELECT ...; -- 仅在开发环境使用
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
MySQL 8.0 默认关闭 profiling，建议改用 performance_schema。在测试环境建立基准脚本（sysbench、自定义压测）持续追踪性能。

​
查询重写与缓存

  • 通过应用层缓存（Redis、内存缓存）减少重复查询
  • 使用物化视图策略：定时刷新统计表
  • MySQL 8.0 引入的 CREATE VIEW 仍为逻辑视图，注意查询展开成本
  • 可以借助 ProxySQL 或 MySQL Router 实现读写分离与路由

​
持续优化建议

  • 建立 SQL 审核流程：上线前通过工具检测危险语句
  • 定期检查慢日志并打标签（业务模块、操作来源）
  • 将指标接入监控平台（Prometheus、Grafana），设定告警阈值

​
小结

优化建立在度量与验证之上，借助慢日志、EXPLAIN ANALYZE 与合理索引能快速见效。持续监控与自动化分析可帮助你提前发现潜在问题。