MySQL慢查询是数据库运维中最常见性能瓶颈，一条未优化的SQL可能导致整个数据库实例响应变慢。通过慢查询日志定位问题SQL，结合EXPLAIN执行计划分析索引使用情况，设计合理的覆盖索引是MySQL性能调优的核心技能。本文从慢查询定位到索引优化，给出完整的诊断和优化流程。

慢查询日志配置与采集

慢查询日志是MySQL提供的SQL性能诊断基础工具。生产环境建议开启慢查询日志，设置合理的阈值（通常1秒），并配合pt-query-digest工具做聚合分析。

-- 查看慢查询日志配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 动态开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;        -- 超过1秒的SQL记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的SQL
SET GLOBAL min_examined_row_limit = 100;  -- 扫描行数低于100不记录

-- 慢查询日志文件位置
SHOW VARIABLES LIKE 'slow_query_log_file';
-- 默认: /var/lib/mysql/hostname-slow.log

-- 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
log_slow_slave_statements = 1
*/

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

# 输出示例：
# Profile
# Rank Query ID                      Response time  Calls  R/Call  V/M
# ==== ============================= ============== ====== ======= =====
#    1 0x1E8B2C3D4E5F6A7B8C9D...     150.5 45.2%   1200  0.1254  0.08
#    2 0xA1B2C3D4E5F6A7B8C9D0...      80.3 24.1%    500  0.1606  0.12
#    3 0xF1E2D3C4B5A697887960...      50.1 15.0%    300  0.1670  0.05

# 按响应时间排序，优先优化Rank 1的查询
# V/M值越大说明查询执行时间波动越大，可能存在锁等待

EXPLAIN执行计划详解与索引使用判断

EXPLAIN是分析SQL执行路径的核心工具。通过type、key、rows、Extra四个字段可以快速判断索引使用情况和扫描效率。

-- 分析慢查询执行计划
EXPLAIN SELECT o.order_id, o.order_no, u.user_name, p.product_name
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN products p ON o.product_id = p.product_id
WHERE o.status = 'PAID' 
  AND o.created_at >= '2026-07-01'
  AND u.region = '华东'
ORDER BY o.created_at DESC
LIMIT 20;

-- EXPLAIN输出关键字段分析:
-- id: 查询序号，越大越先执行
-- select_type: SIMPLE(简单查询)/PRIMARY(复杂查询最外层)/DERIVED(派生表)
-- table: 表名
-- type: 访问类型（性能从好到差）
--   system > const > eq_ref > ref > range > index > ALL
-- key: 实际使用的索引名
-- key_len: 使用索引的字节长度（判断复合索引用了几个字段）
-- rows: 预估扫描行数
-- Extra: 额外信息
--   Using index: 覆盖索引，无需回表（最优）
--   Using where: 通过WHERE条件过滤
--   Using temporary: 使用临时表（需优化）
--   Using filesort: 文件排序（需优化）
--   Using join buffer: 使用BNL/BKA连接（需优化）

-- 索引使用情况诊断
-- 查看索引使用统计
SELECT 
    OBJECT_SCHEMA as db,
    OBJECT_NAME as table_name,
    INDEX_NAME as index_name,
    COUNT_READ, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
    AND OBJECT_SCHEMA = 'your_db'
ORDER BY COUNT_READ DESC;

-- 查找冗余索引
SELECT 
    s1.table_schema, s1.table_name, s1.index_name,
    s2.index_name as redundant_index
FROM statistics s1
JOIN statistics s2 ON s1.table_schema = s2.table_schema
    AND s1.table_name = s2.table_name
    AND s1.seq_in_index = 1
    AND s2.seq_in_index = 1
    AND s1.index_name < s2.index_name
    AND s1.column_name = s2.column_name
WHERE s1.table_schema NOT IN ('mysql','information_schema','performance_schema');

-- 查找未使用的索引
SELECT 
    t.table_schema, t.table_name, t.index_name,
    t.non_unique, t.seq_in_index, t.column_name
FROM statistics t
LEFT JOIN information_schema.table_io_waits_summary_by_index_usage iu
    ON t.table_schema = iu.object_schema
    AND t.table_name = iu.object_name
    AND t.index_name = iu.index_name
WHERE iu.count_read IS NULL 
    AND iu.count_write IS NULL
    AND t.index_name != 'PRIMARY'
    AND t.table_schema = 'your_db';

覆盖索引设计与回表优化

覆盖索引是指索引包含查询所需的所有字段，数据库引擎直接从索引中返回数据，无需回表查询聚簇索引。覆盖索引能显著减少IO操作，是MySQL索引优化的核心手段。

-- 优化前：需要回表查询
SELECT order_id, order_no, user_id, total_amount, status
FROM orders
WHERE user_id = 10086 
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

-- 假设当前索引: idx_user_id (user_id)
-- 执行计划: type=ref, key=idx_user_id, rows=5000, Extra=Using where; Using filesort
-- 问题: 1.需要回表5000次读取行数据 2.Using filesort排序

-- 优化方案1：覆盖索引（包含查询字段）
ALTER TABLE orders ADD INDEX idx_cover (user_id, status, created_at, order_no, total_amount);

-- 优化后执行计划: 
-- type=ref, key=idx_cover, rows=20, Extra=Using index
-- Using index表示覆盖索引，无需回表

-- 优化方案2：复合索引+延迟关联（查询字段过多时）
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);

-- 延迟关联：先通过覆盖索引定位主键，再回表查询
SELECT o.* FROM orders o
INNER JOIN (
    SELECT order_id FROM orders
    WHERE user_id = 10086 AND status = 'PAID'
    ORDER BY created_at DESC
    LIMIT 20
) t ON o.order_id = t.order_id;

-- 子查询使用覆盖索引idx_user_status_time定位20条记录的order_id
-- 外层查询通过主键精确回表20次，远优于全表扫描5000行

-- 复合索引字段顺序设计原则
-- 最左前缀匹配：索引 (a, b, c) 可用于:
--   WHERE a = ?              ✓ 使用a
--   WHERE a = ? AND b = ?    ✓ 使用a, b
--   WHERE a = ? AND b = ? AND c = ?  ✓ 使用a, b, c
--   WHERE b = ? AND c = ?    ✗ 无法使用索引
--   WHERE a = ? AND c = ?    ✓ 仅使用a（b缺失导致c无法使用）

-- 字段顺序设计原则:
-- 1. 等值查询字段在前，范围查询字段在后
-- 2. 高选择性字段在前（区分度高）
-- 3. 排序字段在最后
-- 4. 覆盖索引尽量包含所有查询字段

-- 错误示范：范围查询字段在前
-- 索引 (created_at, user_id, status)
-- WHERE user_id = ? AND created_at > ? 
-- 只能使用created_at范围扫描，无法利用user_id过滤

-- 正确示范：等值查询在前
-- 索引 (user_id, status, created_at)
-- WHERE user_id = ? AND status = ? AND created_at > ?
-- 三个字段都能被索引利用

分页查询深度优化与游标方案

LIMIT分页在偏移量大时性能急剧下降。LIMIT 1000000, 20需要扫描1000020行记录再丢弃前100万行。深度分页优化通常采用游标（Cursor）方案。

-- 传统分页（性能差）
SELECT * FROM orders 
WHERE user_id = 10086
ORDER BY created_at DESC 
LIMIT 1000000, 20;
-- 扫描1000020行，耗时数秒

-- 优化方案1：游标分页（记录上一页最后一条记录的值）
-- 第一页
SELECT * FROM orders
WHERE user_id = 10086
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

-- 第N页（传入上一页最后一条记录的created_at和order_id）
SELECT * FROM orders
WHERE user_id = 10086
  AND (created_at < ? OR (created_at = ? AND order_id < ?))
ORDER BY created_at DESC, order_id DESC
LIMIT 20;
-- 使用索引 (user_id, created_at, order_id)，直接定位20条，扫描行数=20

-- 优化方案2：延迟关联（兼容现有分页参数）
SELECT o.* FROM orders o
INNER JOIN (
    SELECT order_id FROM orders
    WHERE user_id = 10086
    ORDER BY created_at DESC
    LIMIT 1000000, 20
) t ON o.order_id = t.order_id;
-- 子查询通过覆盖索引快速定位20个order_id
-- 外层通过主键回表，避免大偏移量扫描

连接查询优化与索引联动设计

多表JOIN的性能取决于驱动表选择和被驱动表连接字段索引。MySQL优化器基于成本选择驱动表，但有时需要通过STRAIGHT_JOIN强制指定驱动表。

-- 优化前：三个表JOIN，扫描行数过多
EXPLAIN SELECT o.*, u.user_name, p.product_name
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN products p ON o.product_id = p.product_id
WHERE o.status = 'PAID' AND o.created_at >= '2026-07-01';

-- 检查各表索引:
-- orders: PK(order_id), idx_status_time(status, created_at)
-- users: PK(user_id)
-- products: PK(product_id)

-- 优化分析:
-- 1. orders作为驱动表，通过idx_status_time过滤，扫描行数少
-- 2. JOIN users ON o.user_id = u.user_id -> 使用users主键，每次O(1)
-- 3. JOIN products ON o.product_id = p.product_id -> 使用products主键，每次O(1)

-- 如果users表很大且无主键索引，被驱动表需要全表扫描
-- 解决方案：为连接字段添加索引
ALTER TABLE users ADD UNIQUE INDEX uk_user_id (user_id);

-- Nested Loop Join算法:
-- for each row in driving_table (filtered by WHERE):
--     for each row in driven_table (matched by JOIN condition):
--         if all conditions met: output row
-- 驱动表扫描行数 × 被驱动表每次查找成本 = 总成本

-- 强制驱动表顺序（优化器选择错误时使用）
SELECT STRAIGHT_JOIN o.*, u.user_name, p.product_name
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN products p ON o.product_id = p.product_id
WHERE o.status = 'PAID' AND o.created_at >= '2026-07-01';
-- STRAIGHT_JOIN强制按SQL书写顺序JOIN，左表为驱动表

索引优化完成后，通过Performance Schema持续监控SQL平均执行时间。对P95延迟超过500ms的查询建立告警，定期运行pt-query-digest对比优化效果。数据库高可用架构中，慢查询治理是降低主从延迟、提升读库吞吐的基础工作。

慢查询日志的正确开启与采集配置

MySQL慢查询日志是性能诊断的数据源头，但默认是关闭的。生产环境的开启方式需要考虑日志量和性能影响：

-- 动态开启慢查询日志（无需重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1;  -- 100ms阈值
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL slow_query_log_file = '/data/mysql/slow.log';

-- 持久化到配置文件（my.cnf）
[mysqld]
slow_query_log = 1
long_query_time = 0.1
log_queries_not_using_indexes = 1
slow_query_log_file = /data/mysql/slow.log

long_query_time的设置策略：不要用默认的10秒，那会漏掉大量有优化价值的查询。线上经验值，OLTP系统设0.1秒，OLAP系统设1秒。对于核心交易系统，甚至可以设0.05秒。

慢日志文件需要定期轮转，否则会撑满磁盘：

# 用mysqladmin flush-logs轮转
mysqladmin -u root -p flush-logs slow

# 或配置logrotate
/var/lib/mysql/slow.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    postrotate
        mysqladmin -u root flush-logs slow
    endscript
}

EXPLAIN执行计划的深度解读

EXPLAIN是慢查询诊断的核心工具。但很多人只看type列是否为ALL，这是不够的。完整的EXPLAIN分析需要逐列解读：

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

type列：访问类型，从好到差排序——system > const > eq_ref > ref > range > index > ALL。OLTP系统中，单行查询至少要达到ref级别，范围查询至少range级别。出现ALL必须优化。

key列：实际使用的索引。如果为NULL，说明没有使用任何索引。注意，possible_keys列列出的是候选索引，key列才是实际选中的索引，二者不一致时往往是优化器成本估算偏差。

rows列：预估扫描行数。这个值是优化器基于统计信息估算的，可能不准确。当实际行数与估算差距过大时，需要ANALYZE TABLE更新统计信息。

Extra列：这是信息量最大的列。几个关键标志：

• Using filesort：排序没有使用索引，需要额外排序操作。高并发下filesort是CPU和内存杀手
• Using temporary：使用了临时表，通常出现在GROUP BY和DISTINCT没有合适索引时
• Using index：覆盖索引，查询不需要回表，这是最优状态
• Using index condition：索引下推（ICP），存储层过滤部分数据后再回表
• Backward index scan：反向索引扫描，MySQL 8.0+的降序索引特性

EXPLAIN FORMAT=JSON比表格输出信息更丰富，尤其是attached_condition字段可以看到完整的下推条件：

-- JSON格式输出关键字段
{
  "query_block": {
    "table": {
      "table_name": "o",
      "access_type": "range",
      "key": "idx_create_time_status",
      "used_index_parts": ["create_time", "status"],
      "attached_condition": "((`o`.`status` = 'PAID') and (`o`.`create_time` between '2026-07-01' and '2026-07-28'))"
    }
  }
}

索引设计的实战原则与常见反模式

索引设计不是加个INDEX就完事了。错误的索引比没有索引更可怕——浪费空间、拖慢写入、误导优化器。

原则一：最左前缀匹配。联合索引(a,b,c)可以匹配a、(a,b)、(a,b,c)的查询条件，不能匹配b、(b,c)或c的查询。这是B+树索引的结构决定的。

原则二：区分度高的列放前面。联合索引的列顺序决定了索引的选择性。列的区分度计算：

SELECT 
  COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
  COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity,
  COUNT(DISTINCT create_time) / COUNT(*) AS create_time_selectivity
FROM orders;

-- 输出示例：
-- status_selectivity: 0.0001 (3种状态)
-- user_id_selectivity: 0.85
-- create_time_selectivity: 0.72

-- 高区分度列(user_id)应在联合索引前面
-- CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

原则三：覆盖索引消除回表。如果查询的所有列都在索引中，就不需要回表查主键数据。覆盖索引对高并发查询的性能提升非常显著：

-- 原查询：回表
SELECT user_id, status, amount FROM orders WHERE user_id = 1001;

-- 创建覆盖索引
CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount);

-- 优化后：Using index（覆盖索引，零回表）

常见反模式：

• 在低区分度列（如status只有3种值）上建单列索引，优化器大概率不会选择它
• 为每个查询条件单独建索引，导致索引过多。多个单列索引不如一个精心设计的联合索引
• 索引列上使用函数或表达式：WHERE YEAR(create_time) = 2026 不会走索引，改为 WHERE create_time >= ‘2026-01-01’ AND create_time < ‘2027-01-01’
• 隐式类型转换：varchar列用整数查询，WHERE phone = 13800138000 不会走索引，改为 WHERE phone = ‘13800138000’

生产环境的索引变更安全流程

大表加索引是高危操作。MySQL 5.7之前的ALGORITHM=COPY会锁表，8.0默认ALGORITHM=INPLACE虽然不锁表但仍然可能造成主从延迟。

-- 安全的在线加索引流程

-- 步骤1：在从库先执行，观察复制延迟
CREATE INDEX idx_user_status ON orders(user_id, status),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 步骤2：检查从库延迟
SHOW SLAVE STATUS\G  -- Seconds_Behind_Master应为0

-- 步骤3：在主库执行，设置长事务超时保护
SET SESSION lock_wait_timeout = 5;
CREATE INDEX idx_user_status ON orders(user_id, status),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 步骤4：验证索引是否被使用
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 'PAID';
-- 确认key列显示idx_user_status

对于超过1000万行的表，即使INPLACE算法也可能需要数小时。使用gh-ost或pt-online-schema-change工具做在线DDL更安全，通过创建影子表+增量同步+原子切换的方式避免锁表。

慢查询根因的系统性分析方法

单个慢查询的优化相对简单，难的是处理持续增长的慢查询量。建立系统性的分析流程：

# 使用pt-query-digest分析慢日志
pt-query-digest /data/mysql/slow.log --since '2026-07-28 00:00:00' \
  --until '2026-07-28 23:59:59' \
  --limit 20 \
  --out report.txt

# 输出按总执行时间排序的TOP20慢查询
# 关注指标：
# Query_time: 平均和95分位执行时间
# Rows_examined: 扫描行数（与Rows_sent的比值越大，说明无效扫描越多）
# Lock_time: 等锁时间（如果占比高，说明存在锁竞争）

分析策略：

• Rows_examined / Rows_sent > 100 的查询优先优化，说明扫描效率极低
• 按总执行时间排序比按单次执行时间排序更有价值——一个执行0.5秒但每天调用100万次的查询，比一个执行10秒但每天调用1次的查询影响更大
• Lock_time占比超过30%的查询需要排查锁竞争，不一定是索引问题

最终的优化效果验证：

-- 优化前
-- Query_time: 2.3s, Rows_examined: 5000000, Rows_sent: 50

-- 优化后（添加覆盖索引）
-- Query_time: 0.02s, Rows_examined: 50, Rows_sent: 50

-- 性能提升115倍，扫描效率提升10万倍

建立慢查询治理看板，将慢查询数量作为数据库健康度的核心指标纳入SRE监控体系。目标：慢查询数量持续下降，P99延迟控制在业务SLA以内。