MySQL性能调优实战：慢查询诊断到索引优化的全链路方案

MySQL慢查询诊断从哪里开始

数据库性能问题的80%来自慢查询。定位慢查询的第一步是开启慢查询日志，拿到问题SQL再做EXPLAIN分析。这篇实战指南从慢查询捕获、执行计划解读、索引设计到分库分表方案，把MySQL性能调优的全链路走通。

慢查询日志捕获与分析

开启慢查询日志：

-- my.cnf配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

线上环境设置long_query_time=1秒，避免日志量过大。定位问题后可以临时降到0.1秒精细分析。

用mysqldumpslow统计Top慢查询：

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-s t按总耗时排序，-t 10取前10条。输出中关注：出现次数、平均执行时间、扫描行数。

EXPLAIN执行计划逐字段解读

拿到慢SQL后，第一步就是EXPLAIN：

EXPLAIN SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID' AND o.created_at > '2026-07-01'
ORDER BY o.amount DESC
LIMIT 20;

关键字段及含义：

– type：访问类型，性能从好到差：system > const > eq_ref > ref > range > index > ALL。出现ALL说明全表扫描，必须优化
– key：实际用到的索引，NULL表示没走索引
– rows：预估扫描行数，越大越需要优化
– Extra：额外信息，Using filesort（额外排序）和Using temporary（临时表）都是性能杀手

上面SQL可能看到orders表type=ALL、Extra=Using filesort。原因：status和created_at没有联合索引，ORDER BY amount无法利用索引排序。

索引设计原则与实战优化

为上面的查询创建联合索引：

CREATE INDEX idx_status_created ON orders(status, created_at);

但ORDER BY amount DESC仍然导致filesort。如果业务确实需要按金额排序，可以考虑：

CREATE INDEX idx_status_created_amount ON orders(status, created_at, amount);

索引设计核心原则：

1. 最左前缀匹配：联合索引(A,B,C)能覆盖查询条件A、AB、ABC，不能覆盖B、C、BC
2. 等值条件放前面：范围查询字段放联合索引末尾，status=PAID是等值、created_at>是范围，所以status在前
3. 覆盖索引避免回表：如果索引包含SELECT的所有列，Extra会显示Using index，不需要回主键索引取数据

覆盖索引示例：

CREATE INDEX idx_cover ON orders(status, created_at, amount, id, user_id);

索引不是越多越好，每个索引都要占用磁盘空间、增加写入开销。单表索引数量控制在5-8个。

SQL查询优化常见模式

模式1：避免SELECT *

-- 差：回表取所有列
SELECT * FROM orders WHERE user_id = 100;

-- 好：覆盖索引直接返回
SELECT id, amount, status FROM orders WHERE user_id = 100;

模式2：分页优化

深度分页（OFFSET很大）性能极差，因为MySQL需要扫描OFFSET+LIMIT行再丢弃前OFFSET行。

-- 差：扫描100020行
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 好：游标分页
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

模式3：子查询改JOIN

-- 差：子查询执行效率低
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE level > 5);

-- 好：改写为JOIN
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.level > 5;

MySQL高可用架构中的性能考量

主从复制架构下，慢查询对从库延迟的影响经常被忽视。一条主库执行3秒的UPDATE，从库需要同样3秒重放，期间从库数据滞后。

从库延迟监控：

SHOW SLAVE STATUS\G
-- 关注 Seconds_Behind_Master

缓解方案：

1. 从库开启并行复制：slave_parallel_workers = 4
2. 大事务拆分：一次更新100万行改成每次5000行分批执行
3. 读写分离时，关键读请求走主库，避免读到过期数据

数据备份恢复与分库分表

大表（单表超过5000万行）性能必然下降，分库分表是最终方案。ShardingSphere-JDBC是目前最成熟的中间件：

# application.yml
spring:
  shardingsphere:
    datasource:
      names: ds0, ds1
      ds0:
        jdbc-url: jdbc:mysql://localhost:3306/order_db_0
      ds1:
        jdbc-url: jdbc:mysql://localhost:3306/order_db_1
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds$->{0..1}.orders_$->{0..15}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-db-mod
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: order-tbl-mod

MySQL性能调优没有银弹，核心是：慢查询日志定位问题 → EXPLAIN分析执行计划 → 按索引原则优化 → 大表走分库分表。掌握这套链路，大部分数据库性能问题都能解决。