MySQL慢查询定位与索引优化实战手册

慢查询日志配置与有效数据提取

MySQL慢查询日志是性能调优的起点，但默认配置通常不够用——long_query_time默认10秒，大部分需要优化的查询在0.1-2秒之间。生产环境建议设置为0.1秒甚至更低，配合pt-query-digest做聚合分析。

-- my.cnf 核心配置
slow_query_log = ON
long_query_time = 0.1          # 记录执行超过0.1秒的查询
log_queries_not_using_indexes = ON  # 未使用索引的查询也记录
min_examined_row_limit = 100   # 扫描行数低于100的查询不记录
slow_query_log_file = /var/log/mysql/slow.log

日志文件增长很快，需要定期轮转：

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

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

# 按Query Time排序Top 20
pt-query-digest --order-by Query_time:sum --limit 20 /var/log/mysql/slow.log

pt-query-digest输出中最关键的信息：Query ID（同类查询指纹）、执行次数、平均执行时间、扫描行数与返回行数的比值。扫描/返回比超过100的查询，大概率存在索引缺失或索引失效问题。

EXPLAIN执行计划深度解读

拿到慢查询SQL后，第一步是看EXPLAIN执行计划。很多开发者只关注type列是否为ALL，但实际需要看更多列：

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

执行计划重点关注以下列：

Using filesort表示排序操作无法使用索引，需要在内存中额外排序。数据量大时会触发磁盘临时文件，性能急剧下降。Using temporary表示查询需要创建临时表（GROUP BY、DISTINCT、UNION等），同样在高数据量下性能堪忧。

索引失效的六种常见场景

建了索引但不生效，是慢查询最常见的原因：

1. 隐式类型转换

-- user_id是varchar类型，但查询传了整数
-- MySQL会将user_id隐式转换为数字，索引失效
SELECT * FROM orders WHERE user_id = 12345;  -- 索引失效
SELECT * FROM orders WHERE user_id = '12345'; -- 索引生效

这种问题在ORM框架中常见——Java的Long类型映射到VARCHAR列，MyBatis自动传参时触发隐式转换。

2. 左模糊查询

-- B+树索引按左前缀匹配，左模糊无法利用索引
SELECT * FROM users WHERE name LIKE '%张';   -- 索引失效
SELECT * FROM users WHERE name LIKE '张%';   -- 索引生效

-- 必须左模糊的场景，考虑全文索引或ES
ALTER TABLE users ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张' IN BOOLEAN MODE);

3. 对索引列使用函数或运算

-- 在索引列上使用函数，索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-28'; -- 失效
SELECT * FROM orders WHERE created_at >= '2026-07-28' 
                      AND created_at < '2026-07-29';        -- 生效

-- 在索引列上运算，索引失效
SELECT * FROM goods WHERE price * 0.8 > 100;  -- 失效
SELECT * FROM goods WHERE price > 100 / 0.8;  -- 生效

4. 联合索引最左前缀规则违反

-- 联合索引 idx_status_created_at(status, created_at)
SELECT * FROM orders WHERE created_at > '2026-07-01'; -- 缺少status，索引失效
SELECT * FROM orders WHERE status = 'pending' 
                      AND created_at > '2026-07-01';   -- 索引生效

-- 跳跃扫描（MySQL 8.0+）有限支持，但条件严格
SELECT * FROM orders WHERE status = 'pending'; -- 只用status部分，生效

5. OR条件中有一个条件无索引

-- name有索引，email无索引，整个OR条件索引失效
SELECT * FROM users WHERE name = 'Alice' OR email = 'alice@test.com'; -- 失效

-- 解决方案：给email也加索引，或用UNION拆分
SELECT * FROM users WHERE name = 'Alice'
UNION
SELECT * FROM users WHERE email = 'alice@test.com'; -- 两个查询都能用索引

6. NOT IN / NOT EXISTS / != 等否定条件

SELECT * FROM orders WHERE status != 'cancelled'; -- 索引效果差

-- 改写为IN
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'completed'); -- 索引生效

覆盖索引与延迟关联优化分页查询

深分页（LIMIT 100000, 20）是MySQL最经典的性能杀手。扫描100020行只为返回20行，大量时间浪费在无用的行读取上。

优化方案：延迟关联（Deferred Join），先用子查询在覆盖索引上定位ID，再回表取数据：

-- 原始查询：扫描100020行
SELECT * FROM orders 
WHERE status = 'pending'
ORDER BY id 
LIMIT 100000, 20;

-- 延迟关联：子查询在覆盖索引上快速定位，主查询只需回表20行
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    WHERE status = 'pending'
    ORDER BY id 
    LIMIT 100000, 20
) t ON o.id = t.id;

覆盖索引的关键：子查询只需要id和status列，联合索引idx_status_id(status, id)完全覆盖，无需回表扫描100000行。主查询通过主键回表只需20次随机IO。

更激进的优化是使用游标分页（Cursor Pagination），避免OFFSET：

-- 第一页
SELECT * FROM orders WHERE status = 'pending' ORDER BY id LIMIT 20;

-- 下一页：基于上一页最后一条记录的ID
SELECT * FROM orders 
WHERE status = 'pending' AND id > 100020
ORDER BY id LIMIT 20;

索引选择性评估与联合索引列顺序

不是所有列都适合建索引。索引选择性（Cardinality / Total Rows）低于0.1的列，单独建索引效果有限——比如性别列只有2个值，选择性约0.001，全表扫描反而更快（因为索引回表是随机IO）。

-- 评估列的选择性
SELECT 
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity,
    COUNT(DISTINCT created_at) / COUNT(*) AS created_at_selectivity
FROM orders;

-- 输出示例：
-- status_selectivity: 0.0003（5个状态值，不适合单独索引）
-- user_id_selectivity: 0.85（高选择性，适合索引）
-- created_at_selectivity: 0.98（极高选择性）

联合索引的列顺序遵循”选择性高的列在前”原则。对于WHERE user_id = ? AND status = ?这样的查询，索引应该是(user_id, status)而非(status, user_id)——高选择性的user_id在前，可以快速缩小范围。

但存在特殊情况：如果查询是WHERE status = ‘pending’ ORDER BY created_at，索引应该是(status, created_at)——status在前用于过滤，created_at在后避免filesort。索引列顺序要根据实际查询模式决定，不能只看选择性。

在线索引变更与锁表风险规避

大表添加索引时，MySQL 5.6+支持Online DDL，但仍需注意锁表风险：

-- 在线添加索引（推荐）
ALTER TABLE orders ADD INDEX idx_user_created(user_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 查看DDL进度
SHOW PERFORMANCE_SCHEMA.EVENTS_STAGES_CURRENT;

-- 估算索引创建时间
-- 索引大小 ≈ 行数 × (索引列长度 + 主键长度) × 1.5
-- 1000万行 × (8 + 4) × 1.5 ≈ 180MB
-- 默认innodb_sort_buffer_size=1MB，创建时间约3-10分钟

LOCK=NONE允许DML操作并行执行，但创建期间会产生额外的IO负载，建议在业务低峰期操作。对于超过5000万行的表，考虑使用pt-online-schema-change工具，通过创建影子表+增量同步的方式避免长时间锁表。