MySQL慢查询排查与索引优化：从EXPLAIN到覆盖索引的实战方法论

小编 • • 数据库

慢查询定位与日志分析

MySQL性能调优的第一步是找到慢查询。生产环境中开启慢查询日志是最直接的方式。数据库运维中，慢查询的识别和优化通常能带来立竿见影的性能提升。

开启慢查询日志：

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

-- 动态开启（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;    -- 超过1秒记录
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_slow_admin_statements = 1
min_examined_row_limit = 100       -- 检查行数少于100不记录

用mysqldumpslow或pt-query-digest分析慢查询日志。pt-query-digest按指纹聚合相似SQL，更适合生产分析：

# 安装percona toolkit
yum install -y percona-toolkit

# 分析慢查询日志，按总耗时排序
pt-query-digest /var/log/mysql/slow.log --order-by Query_time:sum

# 输出示例
# Rank  Query ID           Response time   Calls  R/Call   V/M
# ====  ================== =============== ====== ======== =====
#    1  0xABC123...        1520.5 45.2%    3421  0.4444  0.12
#    2  0xDEF456...         830.2 24.7%     521  1.5935  0.45

# 查看排名第一的SQL详情
pt-query-digest /var/log/mysql/slow.log --filter '$event->{arg} =~ /SELECT.*FROM.*orders/' --print

EXPLAIN执行计划深度解读

找到慢SQL后，用EXPLAIN分析执行计划。这是SQL查询优化的核心工具：

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

重点关注EXPLAIN输出中的几个字段：

type：访问类型，从好到差依次为 system > const > eq_ref > ref > range > index > ALL。ALL是全表扫描必须优化。index是全索引扫描，比ALL好但仍然不理想。

key：实际使用的索引。如果为NULL表示没有走索引。

rows：预估扫描行数。这个值越接近实际返回行数越好。rows很大但实际返回很少，说明索引过滤性差。

Extra：额外信息。Using index表示覆盖索引，Using filesort表示需要额外排序，Using temporary表示使用临时表。后两者在高并发下是性能杀手。

+----+--------+-------+---------+------+---------+------+--------+----------------+
| id |select_ |table  | type    |key  | key_len | rows |filtered| Extra          |
+----+--------+-------+---------+------+--------+------+--------+----------------+
| 1  |SIMPLE  | o     | ALL     |NULL | NULL    |500000|  10.00 | Using filesort |
| 1  |SIMPLE  | u     |eq_ref   |PRIMARY| 8      | 1   | 100.00 | NULL           |
+----+--------+-------+---------+------+--------+------+--------+----------------+

上面的执行计划显示orders表走的是ALL（全表扫描），且Using filesort（额外排序）。50万行全表扫描+文件排序，查询必然慢。

索引优化策略与覆盖索引

针对上面的慢查询，创建联合索引：

-- 创建联合索引：WHERE条件字段在前，排序字段在后
ALTER TABLE orders ADD INDEX idx_status_create (status, create_time);

-- 再次执行EXPLAIN
EXPLAIN SELECT ...;
+----+--------+-------+-------+--------------------+--------+------+--------+-------------+
| id |select_ |table  | type  | key                | key_len| rows |filtered| Extra       |
+----+--------+-------+-------+--------------------+--------+------+--------+-------------+
| 1  |SIMPLE  | o     | range | idx_status_create  | 26     | 5000 | 100.00 | Using index |
| 1  |SIMPLE  | u     |eq_ref | PRIMARY            | 8      | 1    | 100.00 | NULL        |
+----+--------+-------+-------+--------------------+--------+------+--------+-------------+

加索引后type变为range，扫描行数从50万降到5000，且Using index表示走了覆盖索引。覆盖索引意味着查询所需的所有字段都能从索引中获取，不需要回表查主键索引，减少了大量IO。

但上面的查询还select了order_id、amount、user_name等字段，联合索引(status, create_time)并不能覆盖这些字段。要实现真正的覆盖索引：

-- 创建覆盖索引，包含查询需要的所有字段
ALTER TABLE orders ADD INDEX idx_cover (status, create_time, order_id, amount, user_id);

-- 执行计划Extra列显示Using index，完全不回表
-- 但这个索引很大，索引列越多写入开销越大，需权衡

索引设计的原则：

最左前缀原则：联合索引(a, b, c)可以用于WHERE a=1、WHERE a=1 AND b=2、WHERE a=1 AND b=2 AND c=3，但不能用于WHERE b=2或WHERE c=3。查询条件的顺序不需要和索引列顺序一致，优化器会自动调整，但条件中必须包含最左列。

范围查询中断：联合索引(a, b, c)中，WHERE a=1 AND b>5 AND c=3，c条件无法走索引。b的范围查询导致后面的列中断。解决方案是将范围查询列放最后。

避免索引失效的常见场景：

-- 1. 函数操作导致索引失效
SELECT * FROM orders WHERE DATE(create_time) = '2026-07-01';  -- 不走索引
-- 改写为范围查询
SELECT * FROM orders WHERE create_time >= '2026-07-01' AND create_time < '2026-07-02';

-- 2. 隐式类型转换
SELECT * FROM orders WHERE order_no = 123456;  -- order_no是varchar，不走索引
SELECT * FROM orders WHERE order_no = '123456';  -- 加引号，走索引

-- 3. LIKE以通配符开头
SELECT * FROM products WHERE name LIKE '%手机%';  -- 不走索引
SELECT * FROM products WHERE name LIKE '手机%';   -- 走索引

-- 4. OR连接非索引列
SELECT * FROM orders WHERE status = 'PAID' OR remark = '加急';  -- remark无索引则全表扫描
-- 改用UNION ALL
SELECT * FROM orders WHERE status = 'PAID'
UNION ALL
SELECT * FROM orders WHERE remark = '加急' AND status != 'PAID';

分库分表方案与查询优化

单表数据量超过千万行后，即使索引优化到位，查询性能仍会出现瓶颈。分库分表方案成为必然选择。ShardingSphere是Java生态中最成熟的分库分表中间件。

// ShardingSphere-JDBC配置
// application.yml
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://192.168.1.10:3306/order_db_0
        username: root
        password: xxx
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://192.168.1.11:3306/order_db_1
        username: root
        password: xxx
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds$->{0..1}.orders_$->{0..3}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: db-inline
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: table-inline
            key-generate-strategy:
              column: order_id
              key-generator-name: snowflake
        sharding-algorithms:
          db-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{user_id % 2}
          table-inline:
            type: INLINE
            props:
              algorithm-expression: orders_$->{order_id % 4}

分库分表后的查询优化要点：分片键必须包含在查询条件中，否则会广播到所有分片。跨分片排序和分页需要合并多个分片结果，深度分页场景性能急剧下降。解决方案是用游标分页替代offset分页：

-- 传统的offset分页，深度分页时扫描行数线性增长
SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 20;
-- MySQL需要扫描1000020行，丢弃前100万行

-- 游标分页，每次记录上一页最后一条的create_time
SELECT * FROM orders 
WHERE create_time < '2026-07-01 12:00:00'  -- 上一页最后一条的时间
ORDER BY create_time DESC 
LIMIT 20;
-- 无论翻到第几页，扫描行数恒定为20

数据备份恢复与高可用架构

索引优化和分库分表解决查询性能问题，数据安全同样重要。数据库高可用架构方面，MySQL主从复制是基础：

# 主库配置 my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON

# 从库配置 my.cnf  
[mysqld]
server-id = 2
relay-log = relay-bin
read-only = ON
gtid-mode = ON
enforce-gtid-consistency = ON

# 从库配置复制
CHANGE MASTER TO 
  MASTER_HOST='192.168.1.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_pwd',
  MASTER_AUTO_POSITION=1;
START SLAVE;

数据备份恢复方面，物理备份用Percona XtraBackup实现热备不停服：

# 全量备份（不停服）
xtrabackup --backup --target-dir=/backup/full --user=root --password=xxx

# 增量备份
xtrabackup --backup --target-dir=/backup/inc1   --incremental-basedir=/backup/full --user=root --password=xxx

# 恢复流程
# 1. 准备全量备份
xtrabackup --prepare --target-dir=/backup/full
# 2. 合并增量备份
xtrabackup --prepare --target-dir=/backup/full   --incremental-dir=/backup/inc1
# 3. 拷贝数据到MySQL目录
xtrabackup --copy-back --target-dir=/backup/full
# 4. 修改权限并启动
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

SQL查询优化是一个持续的过程。线上数据库启用慢查询自动采集和告警，定期Review排名前10的慢SQL。国产数据库如OceanBase、TiDB在兼容MySQL协议的同时提供了分布式能力，但在迁移时索引行为和执行计划可能与MySQL存在差异，需要重新评估索引策略。数据迁移实战中，使用gh-ost或pt-online-schema-change做在线DDL变更，避免锁表影响业务。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql-man-cha-xun-pai-cha-yu-suo-yin-you-hua-cong-explain/

赞 (0)
小编小编
0
上一篇 16小时前
下一篇 16小时前

相关推荐