MySQL性能调优实战：慢查询诊断与索引优化全链路

MySQL性能调优的诊断方法论

MySQL性能问题的排查有固定路径：先定位瓶颈（CPU/IO/锁/网络），再找具体SQL，最后针对性优化。盲目调参数是生产环境的大忌——绝大多数性能问题不是参数配置不当，而是SQL写法和索引设计有问题。调参只解决特定场景的边界问题，优化SQL和索引解决的是根本问题。

慢查询日志配置与分析

慢查询日志是发现性能问题的第一道工具。生产环境必须开启，但需要控制日志量避免IO压力：

-- MySQL 8.0+ 慢查询配置
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;           -- 500ms以上记录
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 未用索引也记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL min_examined_row_limit = 100;    -- 扫描少于100行不记录

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

mysqldumpslow快速汇总慢查询模式：

# 按查询时间排序，显示前10条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按锁定时间排序
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log

# 按执行次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

更强大的分析工具pt-query-digest可以输出详细的查询Profile：

# pt-query-digest分析
pt-query-digest /var/log/mysql/slow.log > /tmp/slow_report.txt

# 分析特定时间段的慢查询
pt-query-digest --since '2026-07-24 00:00:00'   --until '2026-07-24 06:00:00'   /var/log/mysql/slow.log

EXPLAIN执行计划深度解读

拿到慢SQL后，EXPLAIN是分析执行计划的标准手段。重点关注type、key、rows和Extra列：

-- EXPLAIN FORMAT=TREE (MySQL 8.0+) 提供更直观的执行树
EXPLAIN FORMAT=TREE
SELECT o.order_id, o.total_amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.created_at >= '2026-07-01'
  AND c.region = 'east'
  AND oi.product_id = 10086
ORDER BY o.total_amount DESC
LIMIT 20\G

EXPLAIN输出中各type列的性能从优到差排序：

• system/const：单行匹配，最优
• eq_ref：唯一索引匹配，JOIN场景最优
• ref：非唯一索引匹配
• range：索引范围扫描
• index：全索引扫描
• ALL：全表扫描，必须优化

Extra列的关键标识：

• Using index：覆盖索引，无需回表
• Using temporary：使用临时表，需要优化
• Using filesort：文件排序，需要优化
• Using where; Using index：覆盖索引+WHERE过滤，理想状态

索引设计原则与实战案例

索引设计遵循最左前缀匹配原则，联合索引的列顺序决定了索引能覆盖的查询模式：

-- 典型场景：订单查询多条件组合
-- 查询1: WHERE customer_id = ? AND created_at BETWEEN ? AND ?
-- 查询2: WHERE customer_id = ? AND status = ?
-- 查询3: WHERE customer_id = ? ORDER BY created_at DESC

-- 设计联合索引（最左匹配覆盖3种查询）
ALTER TABLE orders ADD INDEX idx_customer_status_created 
  (customer_id, status, created_at);

-- 查询1走 idx_customer_status_created 的 customer_id + range(created_at)
-- 但注意：中间列status跳过后，created_at无法利用索引排序
-- 如果查询1频率最高，单独建索引
ALTER TABLE orders ADD INDEX idx_customer_created 
  (customer_id, created_at);

-- 覆盖索引避免回表
-- 如果查询只需要order_id和total_amount
SELECT order_id, total_amount 
FROM orders 
WHERE customer_id = 1001 AND status = 'paid';
-- 建立覆盖索引:
ALTER TABLE orders ADD INDEX idx_customer_status_cover 
  (customer_id, status, order_id, total_amount);

索引设计中最容易被忽略的陷阱——索引失效场景：

-- 1. 隐式类型转换：varchar列用数字查询
-- status是VARCHAR类型
SELECT * FROM orders WHERE status = 1;    -- 索引失效！
SELECT * FROM orders WHERE status = '1';  -- 走索引

-- 2. 函数操作导致索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-24';  -- 失效
SELECT * FROM orders WHERE created_at >= '2026-07-24' 
  AND created_at < '2026-07-25';          -- 走索引

-- 3. LIKE前缀通配符
SELECT * FROM products WHERE name LIKE '%手机';   -- 失效
SELECT * FROM products WHERE name LIKE '手机%';   -- 走索引

-- 4. OR条件部分无索引
SELECT * FROM orders WHERE customer_id = 1001 
  OR total_amount > 10000;  -- 如果amount无索引，整条失效
-- 优化：UNION ALL拆分
SELECT * FROM orders WHERE customer_id = 1001
UNION ALL
SELECT * FROM orders WHERE total_amount > 10000 
  AND customer_id != 1001;  -- 各走各的索引

Buffer Pool调优与内存管理

Buffer Pool是InnoDB性能的核心。合理的配置可以让热数据全部驻留内存，减少磁盘IO：

-- Buffer Pool大小：建议占物理内存的60-75%
-- 32G内存的服务器，设置20-24G
SET GLOBAL innodb_buffer_pool_size = 21474836480;  -- 20GB

-- 多实例减少锁争用
SET GLOBAL innodb_buffer_pool_instances = 8;

-- 预热Buffer Pool（重启后快速恢复性能）
SET GLOBAL innodb_buffer_pool_load_at_startup = 'ON';
SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 'ON';

-- 查看Buffer Pool命中率
-- 命中率应 > 99%
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

-- Buffer Pool状态监控
SELECT 
  POOL_ID,
  SUM(DATA_PAGES) AS data_pages,
  SUM(DIRTY_PAGES) AS dirty_pages
FROM 
  information_schema.INNODB_BUFFER_PAGE_LRU
GROUP BY POOL_ID;

Online DDL与大表变更策略

生产环境大表加索引是高风险操作。MySQL 8.0的Online DDL支持INPLACE算法，但仍需评估影响：

-- Online DDL加索引（不锁表，但仍消耗IO）
ALTER TABLE orders 
  ADD INDEX idx_created_status (created_at, status),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 大表（千万级以上）建议用gh-ost或pt-osc
# gh-ost方式（推荐，触发器无侵入）
gh-ost   --host=mysql-primary   --database=order_db   --table=orders   --alter="ADD INDEX idx_created_status (created_at, status)"   --chunk-size=1000   --max-load=Threads_running=100   --critical-load=Threads_running=200   --execute

# pt-online-schema-change方式
pt-online-schema-change   --host=mysql-primary   --database=order_db   --table=orders   --alter="ADD INDEX idx_created_status (created_at, status)"   --chunk-size=1000   --max-load=Threads_running=100   --execute

MySQL性能调优是一个持续过程：开启慢查询捕获问题 → EXPLAIN定位根因 → 索引或SQL优化 → 验证效果。每一轮优化都应该用benchmark量化收益，避免主观判断引入新的性能问题。