MySQL慢查询诊断与索引优化实战：EXPLAIN执行计划深度解析

小编 • • 数据库

MySQL性能调优工作中，慢查询诊断是最常见也最关键的任务。一条低效SQL可能导致整个数据库实例的连接池耗尽，引发雪崩效应。本文从慢查询日志采集、EXPLAIN执行计划解读、索引优化策略到SQL重写技巧，系统化梳理MySQL慢查询排查的完整工作流，所有命令和配置均在MySQL 8.0环境中验证。

慢查询日志开启与pt-query-digest分析工具

开启慢查询日志，设置阈值和输出格式：

-- 动态开启（无需重启）
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;  -- 未使用索引的查询也记录
SET GLOBAL min_examined_row_limit = 100;  -- 检查行数小于100不记录
SET GLOBAL log_slow_admin_statements = 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_verbosity = QUERY_PLAN,EXPLAIN   -- 记录执行计划

使用Percona Toolkit的pt-query-digest分析慢查询日志，按指纹聚合相似查询：

# 安装pt-query-digest
yum install -y percona-toolkit

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

# 只分析最近1小时的慢查询
pt-query-digest --since 1h /var/log/mysql/slow.log

# 输出按执行时间排序的TOP 10慢查询
pt-query-digest --order-by Query_time:sum \
    --limit 10 /var/log/mysql/slow.log

pt-query-digest输出示例解读：

# Profile 中关键列：
# Rank: 慢查询排名
# Query ID: 查询指纹ID（相同SQL模板聚合）
# Response time: 总响应时间及占比
# Calls: 执行次数
# R/Call: 平均每次执行时间
# V/M: 方差/均值比，值越大表示执行时间越不稳定

# 10秒内出现5000次的慢查询
#  rank count  time    query
#  1    5000   120.5s  SELECT * FROM orders WHERE user_id = ? AND status = ?

# 高V/M值的查询需要重点排查——参数不同导致执行计划差异

EXPLAIN执行计划字段深度解读

EXPLAIN是MySQL查询优化的核心诊断工具，每个字段都承载着执行计划的关键信息：

EXPLAIN SELECT o.order_id, o.amount, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID' AND o.created_at > '2026-01-01'
ORDER BY o.amount DESC
LIMIT 20;
+----+-------------+-------+------------+--------+---------------------+---------------------+---------+------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys       | key                 | key_len | ref              | rows | filtered | Extra                 |
+----+-------------+-------+------------+--------+---------------------+---------------------+---------+------------------+------+----------+-----------------------+
|  1 | SIMPLE      | o     | NULL       | range  | idx_status,idx_date | idx_status          | 102     | NULL             | 8500 |    33.33 | Using index condition |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY             | PRIMARY             | 8       | test.o.user_id   |    1 |   100.00 | NULL                  |
+----+-------------+-------+------------+--------+---------------------+---------------------+---------+------------------+------+----------+-----------------------+

关键字段分析：

type（访问类型），性能从好到差：system > const > eq_ref > ref > range > index > ALL。生产环境要求至少达到range级别，ALL代表全表扫描必须优化。

key_len（索引长度），反映索引使用情况。计算公式：字段字节数 × 字符集系数 + 可空标记(1字节)。上述示例中idx_status的key_len=102，表示status字段为varchar(50) utf8mb4（50×4+1可空+2长度=203）但实际只用了前缀。

rows（预估扫描行数），优化器基于统计信息估算。rows值与实际行数偏差大时需要执行ANALYZE TABLE更新统计信息。

Extra（额外信息），包含执行细节：

  • Using index：覆盖索引，无需回表，最优情况
  • Using index condition：索引条件下推（ICP），减少回表次数
  • Using filesort：需要额外排序操作，需关注是否可优化
  • Using temporary：使用临时表，通常出现在GROUP BY/DISTINCT中
  • Using join buffer：使用BNL/BKA连接算法，被驱动表无可用索引

索引优化策略与联合索引设计原则

联合索引设计遵循最左前缀原则，字段顺序决定索引可用性。以订单查询场景为例：

-- 业务查询模式分析：
-- 1. WHERE status = 'PAID' AND created_at > '2026-01-01'  (高频)
-- 2. WHERE user_id = 123 AND status = 'PAID'              (高频)
-- 3. WHERE status = 'PAID' ORDER BY amount DESC            (中频)

-- 错误索引：分别为每个字段建单列索引
-- MySQL优化器只能选择一个索引，无法同时利用idx_status和idx_user_id
CREATE INDEX idx_status ON orders(status);        -- 冗余
CREATE INDEX idx_user_id ON orders(user_id);      -- 冗余
CREATE INDEX idx_created ON orders(created_at);   -- 冗余

-- 正确索引：按查询频率和区分度设计联合索引
CREATE INDEX idx_status_created ON orders(status, created_at);
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 覆盖索引优化：将查询字段纳入索引避免回表
CREATE INDEX idx_status_created_covering ON orders(status, created_at, order_id, amount);

索引失效的常见场景排查：

-- 1. 隐式类型转换：字段为varchar，查询传int
EXPLAIN SELECT * FROM orders WHERE order_no = 20260723001;
-- type = ALL（全表扫描），索引失效
-- 修正：WHERE order_no = '20260723001'

-- 2. 函数操作导致索引失效
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2026-07-23';
-- type = ALL，索引失效
-- 修正：WHERE created_at >= '2026-07-23' AND created_at < '2026-07-24'

-- 3. LIKE以通配符开头
EXPLAIN SELECT * FROM users WHERE username LIKE '%zhang%';
-- type = ALL，索引失效
-- 修正：使用全文索引或右匹配 LIKE 'zhang%'

-- 4. OR连接条件中一侧无索引
EXPLAIN SELECT * FROM orders WHERE status = 'PAID' OR remark LIKE '%urgent%';
-- type = ALL，整个查询走全表扫描
-- 修正：拆分为UNION查询或确保两侧都有索引

-- 5. 联合索引非最左前缀
-- 索引: (status, created_at, user_id)
EXPLAIN SELECT * FROM orders WHERE created_at > '2026-01-01';
-- type = ALL，跳过了status无法使用联合索引
-- 修正：添加status条件或创建created_at单列索引

复杂SQL查询重写与性能对比

子查询优化为JOIN：

-- 优化前：相关子查询，每行执行一次子查询
SELECT * FROM orders o
WHERE o.user_id IN (
    SELECT id FROM users WHERE vip_level >= 5
);
-- 执行时间: 3.2s, rows: 850000

-- 优化后：改写为JOIN
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.vip_level >= 5;
-- 执行时间: 0.15s, rows: 1200

-- 进一步优化：使用EXISTS替代IN（MySQL 8.0优化器已自动改写）
SELECT o.* FROM orders o
WHERE EXISTS (
    SELECT 1 FROM users u WHERE u.id = o.user_id AND u.vip_level >= 5
);

分页查询深度优化：

-- 优化前：深度分页，OFFSET越大越慢
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 20;
-- 执行时间: 2.8s（需扫描100020行）

-- 优化方案1：延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 20
) t ON o.id = t.id;
-- 执行时间: 0.08s（子查询走覆盖索引）

-- 优化方案2：游标分页（记住上一页最后一条记录的ID）
SELECT * FROM orders
WHERE id < ?  -- 上一页最后一条记录的ID
ORDER BY id DESC LIMIT 20;
-- 执行时间: 0.001s（走主键索引）

生产环境慢查询监控与预防机制

建立持续的慢查询监控体系，通过Performance Schema实时采集：

-- 启用statements digest采集
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';

-- 查询TOP 10慢SQL
SELECT 
    DIGEST_TEXT,
    COUNT_STAR as exec_count,
    ROUND(AVG_TIMER_WAIT/1000000000, 2) as avg_ms,
    ROUND(SUM_TIMER_WAIT/1000000000, 2) as total_ms,
    SUM_ROWS_EXAMINED as rows_examined,
    SUM_ROWS_SENT as rows_sent,
    ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0) as avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT > 1000000000  -- 平均超过1秒
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

SQL审核流程中引入索引检查规则：所有上线的DML语句必须通过EXPLAIN验证，type字段不得为ALL，rows预估不得超过1万行。CI/CD流水线中集成SQL审核工具（如Archery、Yearning），自动拦截全表扫描和缺失索引的SQL。

定期维护统计信息准确性，避免优化器选择错误的执行计划：

-- 每日凌晨低峰期执行
ANALYZE TABLE orders, users, order_items PERSISTENT FOR ALL;

-- 查看统计信息采样页数
SELECT table_name, sample_size, table_rows 
FROM information_schema.tables 
WHERE table_schema = 'production';

采样页数默认20，大表可调高至200-500以提升统计精度。统计信息过期会导致rows预估偏差，直接影响JOIN顺序选择和索引选择。配合pt-index-usage-tool定期分析索引使用率，清理冗余索引——每个多余索引增加写入开销和存储成本。

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

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

相关推荐

MySQL慢查询诊断与索引优化实战：EXPLAIN执行计划解读指南

小编 • • 数据库

MySQL慢查询定位与开启配置

MySQL性能调优的第一步是发现慢查询。慢查询日志记录所有执行时间超过阈值的SQL语句，是数据库运维中定位性能瓶颈的核心工具。生产环境慢查询阈值建议设为1秒，开发环境可设为0.1秒捕获更多待优化SQL。

查看和开启慢查询日志：

-- 查看慢查询配置
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;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL min_examined_row_limit = 100;

-- 持久化配置（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
min_examined_row_limit = 100

log_queries_not_using_indexes开启后，未使用索引的查询即使执行时间未超过阈值也会被记录。min_examined_row_limit过滤扫描行数过少的查询，减少日志噪声。

使用mysqldumpslow工具聚合分析慢日志，按总耗时排序找出最需要优化的SQL：

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

# 按返回行数排序
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

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

# 输出示例
# Count: 245  Time=3.12s (764s)  Lock=0.01s (2s)  Rows=15000.0 (3675000)
# SELECT * FROM orders WHERE user_id = # AND status = 'S' ORDER BY created_at DESC

Count表示执行次数，Time为平均单次耗时，括号内为总耗时。上例中该SQL执行了245次，累计耗时764秒，每次返回15000行，是明显的优化目标。

EXPLAIN执行计划深度解读

定位到慢SQL后，使用EXPLAIN分析执行计划。EXPLAIN输出的12个字段中，重点关注type、key、rows、Extra四个字段。

EXPLAIN SELECT * FROM orders 
WHERE user_id = 10086 AND status = 'PAID' 
ORDER BY created_at DESC LIMIT 20;

各字段含义与判断标准：

type字段（访问类型，性能从好到差）：

system > const > eq_ref > ref > range > index > ALL

-- const: 主键或唯一索引等值查询，最多匹配1行
-- eq_ref: JOIN时被驱动表使用主键或唯一索引
-- ref: 非唯一索引等值查询
-- range: 索引范围扫描（BETWEEN, >, <, IN）
-- index: 全索引扫描，遍历整棵索引树
-- ALL: 全表扫描，性能最差，必须优化

type为ALL或index时需要重点优化。ref和range是生产环境常见且可接受的访问类型。

key字段：实际使用的索引名称。NULL表示未使用索引。key_len字段表示索引使用的字节数，可用于判断联合索引用了几个字段。

rows字段：MySQL预估需要扫描的行数。rows越小越好，理想值接近LIMIT值。

Extra字段（附加信息）：

-- Using index: 覆盖索引，无需回表，最优
-- Using where: 通过WHERE条件过滤
-- Using temporary: 使用临时表，常见于GROUP BY、DISTINCT
-- Using filesort: 额外排序操作，需关注
-- Using join buffer: JOIN使用块嵌套循环，缺索引信号

Using temporary和Using filesort同时出现时，SQL性能通常较差，需要通过索引优化消除排序和临时表。

索引优化实战：联合索引设计与最左前缀原则

SQL查询优化的核心手段是建立合适的索引。联合索引遵循最左前缀匹配原则，字段顺序决定了索引的可用范围。设计联合索引时，将区分度高的字段放前面，等值查询字段放前面，范围查询字段放后面。

以上文慢查询为例，分析索引选择：

-- 原始查询
SELECT * FROM orders 
WHERE user_id = 10086 AND status = 'PAID' 
ORDER BY created_at DESC LIMIT 20;

-- 查看现有索引
SHOW INDEX FROM orders;

-- 方案一：创建联合索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 验证执行计划
EXPLAIN SELECT * FROM orders 
WHERE user_id = 10086 AND status = 'PAID' 
ORDER BY created_at DESC LIMIT 20;
-- 预期结果: type=ref, key=idx_user_status_created, 
-- Extra=Using index condition, 无filesort

联合索引(user_id, status, created_at)的设计逻辑：user_id等值查询放第一列，status等值查询放第二列，created_at用于ORDER BY排序放第三列。由于B+Tree索引有序，created_at在等值条件后天然有序，避免filesort。

常见索引失效场景诊断：

-- 1. 函数操作导致索引失效
-- 错误：WHERE DATE(created_at) = '2026-07-23'
-- 正确：WHERE created_at >= '2026-07-23' AND created_at < '2026-07-24'

-- 2. 隐式类型转换
-- 错误：user_id字段为VARCHAR，查询 WHERE user_id = 10086
-- 正确：WHERE user_id = '10086'

-- 3. LIKE以通配符开头
-- 错误：WHERE product_name LIKE '%手机%'
-- 正确：WHERE product_name LIKE '华为%'

-- 4. OR连接非索引列
-- 错误：WHERE user_id = 10086 OR product_name = 'iPhone'
-- 正确：为product_name建索引，或拆分为UNION查询

-- 5. 联合索引跳过中间列
-- 索引(a,b,c)，查询 WHERE a=1 AND c=3 不使用b列
-- 只有a走索引，c需要回表过滤

分库分表方案：ShardingSphere水平拆分配置

当单表数据量超过1000万行，B+Tree索引层级增加导致查询性能下降，数据库高可用架构中需要引入分库分表。Apache ShardingSphere-JDBC在应用层透明地完成SQL路由，对业务代码零侵入。

以订单表按user_id取模分表为例，Spring Boot配置：

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://db0:3306/order_db
        username: root
        password: 'password'
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://db1:3306/order_db
        username: root
        password: 'password'
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds${0..1}.orders_${0..3}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: db-mod
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: table-mod
        sharding-algorithms:
          db-mod:
            type: MOD
            props:
              sharding-count: 2
          table-mod:
            type: MOD
            props:
              sharding-count: 4

上述配置将orders表拆分到2个数据库共8张物理表，分片键为user_id。ShardingSphere自动将SELECT * FROM orders WHERE user_id = 10086路由到对应物理表，业务层无感知。

分库分表后跨分片查询需要特殊处理：不带分片键的查询会广播到所有分片，性能较差。数据迁移实战中，建议先双写新旧表，逐步切换读流量，最后删除旧表。NoSQL选型应用方面，高频查询的非结构化数据可同步到Redis缓存或Elasticsearch，减轻MySQL查询压力。国产数据库如OceanBase和TiDB原生支持分布式架构，在高并发场景下可作为MySQL的替代方案，避免应用层分库分表的复杂性。

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

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

相关推荐