MySQL性能调优实战：慢查询定位与索引优化全流程指南

小编 • • 数据库

MySQL性能调优是数据库运维中最核心的技能。线上系统随着数据量增长，慢查询逐渐成为性能瓶颈。本文系统讲解慢查询定位、EXPLAIN执行计划分析、索引优化策略和SQL改写技巧，提供一套可复用的调优流程。适用于MySQL 8.0+环境。

慢查询定位：开启Slow Query Log与pt-query-digest分析

定位慢查询的第一步是开启慢查询日志，记录执行时间超过阈值的SQL语句：

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

-- 动态开启慢查询日志（运行时生效，重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
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

-- 查看慢查询数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';

收集一段时间后，使用Percona Toolkit的pt-query-digest分析慢查询日志，按执行频次和总耗时排序，快速定位TOP N问题SQL：

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

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

# 按数据库过滤
pt-query-digest --filter '$event->{db} eq "production"' /var/log/mysql/slow.log

# 输出示例（重点关注部分）：
# Profile
# Rank Query ID                     Response time  Calls  R/Call  V/M
# ==== ============================ ============== ====== ======= =====
#    1 0x1E8A3F5B2C7D4A6B  152.3400 45.2%    3421 0.0445  0.08
#    2 0x7B2C4D5E6F1A3B8C   78.9200 23.4%     156 0.5062  0.15
#    3 0x3A5B6C7D8E9F0A1B   35.6700 10.6%    8934 0.0040  0.02
#
# Query 1: 按总响应时间排第一，调优优先级最高

EXPLAIN执行计划深度解读：type字段与Extra字段分析

拿到问题SQL后，用EXPLAIN分析执行计划。重点关注type、key、rows和Extra四个字段：

-- 分析单条SQL执行计划
EXPLAIN SELECT * FROM orders
WHERE user_id = 10086 AND status = 'PAID'
ORDER BY create_time DESC
LIMIT 20;

-- 查看详细执行计划（JSON格式）
EXPLAIN FORMAT=JSON SELECT * FROM orders
WHERE user_id = 10086 AND status = 'PAID'
ORDER BY create_time DESC LIMIT 20;

-- 查看实际执行成本（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 10086 AND status = 'PAID'
ORDER BY create_time DESC LIMIT 20;

type字段的性能排序（从好到差）：

-- system > const > eq_ref > ref > range > index > ALL
--
-- system: 表只有一行（系统表）
-- const:  通过主键或唯一索引等值查询，最多匹配一行
-- eq_ref: JOIN时被驱动表使用主键或唯一索引
-- ref:    通过非唯一索引等值查询
-- range:  索引范围扫描（BETWEEN, >, <, IN）
-- index:  全索引扫描（扫描整棵索引树）
-- ALL:    全表扫描（最差，必须优化）
--
-- Extra关键字段：
-- Using index:        覆盖索引，不回表（最优）
-- Using where:        使用WHERE条件过滤
-- Using temporary:    使用临时表（需优化）
-- Using filesort:     文件排序（需优化）
-- Using join buffer:  JOIN使用BNL算法（需优化）

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

索引设计是MySQL调优的核心。联合索引的字段顺序决定其使用效率。遵循最左前缀原则，将区分度高的字段放在前面：

-- 问题SQL: 多条件查询 + 排序
SELECT * FROM orders
WHERE user_id = 10086 AND status = 'PAID'
ORDER BY create_time DESC
LIMIT 20;

-- 错误索引方案1: 单列索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
-- EXPLAIN: type=ref, key=idx_user_id, rows=15600
-- Extra=Using where; Using filesort

-- 错误索引方案2: 联合索引顺序不当
CREATE INDEX idx_status_user ON orders(status, user_id);
-- status区分度低，索引效率差

-- 正确索引方案: 联合索引，区分度高的字段在前，排序字段在后
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);
-- EXPLAIN: type=ref, key=idx_user_status_time, rows=35
-- Extra=Using index condition
-- WHERE条件是等值查询，create_time用于ORDER BY
-- 索引天然有序，避免filesort

索引设计的关键经验：等值条件字段在前，范围查询字段在后，排序字段紧跟条件字段。避免索引字段上使用函数或类型转换，否则索引失效：

-- 索引失效案例1: 函数操作
CREATE INDEX idx_create_time ON orders(create_time);
-- 失效: 对索引列使用函数
SELECT * FROM orders WHERE DATE(create_time) = '2026-07-23';
-- 正确: 改为范围查询
SELECT * FROM orders
WHERE create_time >= '2026-07-23 00:00:00'
  AND create_time <  '2026-07-24 00:00:00';

-- 索引失效案例2: 隐式类型转换
-- user_id字段类型为VARCHAR，但查询传入整数
SELECT * FROM orders WHERE user_id = 10086;
-- MySQL会将user_id转为数字比较，导致全表扫描
-- 正确: 传入字符串
SELECT * FROM orders WHERE user_id = '10086';

-- 索引失效案例3: LIKE前缀通配符
SELECT * FROM products WHERE name LIKE '%手机%';
-- 正确: 前缀匹配才能走索引
SELECT * FROM products WHERE name LIKE '手机%';
-- 或使用全文索引
ALTER TABLE products ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('手机');

覆盖索引设计：消除回表提升查询性能

当查询所需的所有字段都包含在索引中时，MySQL直接从索引树返回数据，无需回表读取数据行。这种技术称为覆盖索引：

-- 场景: 订单列表页只查询ID、状态、金额
SELECT id, status, amount FROM orders
WHERE user_id = 10086 ORDER BY create_time DESC LIMIT 20;

-- 不带amount的联合索引需要回表取amount
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);
-- Extra: Using index condition（需要回表）

-- 覆盖索引: 将查询字段全部纳入索引
CREATE INDEX idx_user_time_status_amount
ON orders(user_id, create_time, status, amount);
-- Extra: Using index（覆盖索引，不回表）
-- 性能提升: IO从随机读变为顺序读索引，减少50%-80%的IO操作

-- 使用 Invisible Index 灰度测试索引效果
ALTER TABLE orders ALTER INDEX idx_user_time_status_amount INVISIBLE;
-- 确认查询性能后再设为VISIBLE
ALTER TABLE orders ALTER INDEX idx_user_time_status_amount VISIBLE;

覆盖索引会增加索引大小和写入开销，适用于读多写少的高频查询。在数据备份恢复场景中，覆盖索引也能加速恢复过程中的数据校验速度。

SQL改写技巧：子查询优化与分页查询改造

部分SQL写法会导致MySQL选择低效执行计划。通过改写SQL可以引导优化器选择更优路径：

-- 优化1: 子查询改JOIN
-- 低效: 相关子查询，每行执行一次子查询
SELECT * FROM orders o
WHERE o.user_id IN (
    SELECT id FROM users WHERE vip_level >= 5
);
-- 高效: 改为INNER JOIN
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.vip_level >= 5;

-- 优化2: 分页查询优化（深分页问题）
-- 低效: OFFSET越大，扫描的无效行越多
SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20;
-- 高效: 使用游标分页（记住上一页最后一条记录的ID）
SELECT * FROM orders WHERE id < 100000
ORDER BY id DESC LIMIT 20;

-- 优化3: 大批量UPDATE分批执行
-- 低效: 单条大事务，长时间锁表
UPDATE orders SET status = 'EXPIRED'
WHERE status = 'PENDING' AND create_time < '2026-01-01';
-- 高效: 分批更新，每批1000条，每批之间SLEEP 0.1秒

-- 优化4: COUNT优化
-- 低效: COUNT(*)扫描全表或索引
SELECT COUNT(*) FROM orders WHERE status = 'PENDING';
-- 高效: 维护汇总表，实时更新计数
CREATE TABLE order_stats (
    status VARCHAR(20) PRIMARY KEY,
    cnt INT NOT NULL DEFAULT 0
);
INSERT INTO order_stats(status, cnt)
VALUES('PENDING', 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;
SELECT cnt FROM order_stats WHERE status = 'PENDING';

索引监控与维护：碎片整理与使用率统计

索引上线后需要持续监控其使用情况，清理无用索引，定期维护索引碎片：

-- 查看索引使用情况（基于performance_schema）
SELECT
    object_schema AS db,
    object_name AS table_name,
    index_name,
    count_read,
    sys.format_time(sum_timer_wait) AS total_latency
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'production'
ORDER BY count_read ASC;

-- 查找从未使用的索引（清理候选）
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_read = 0
  AND object_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY object_schema, object_name;

-- 查看索引碎片率
SELECT
    table_name,
    ROUND(data_length/1024/1024, 2) AS data_mb,
    ROUND(index_length/1024/1024, 2) AS index_mb,
    ROUND(data_free/(data_length+index_length)*100, 2) AS frag_pct
FROM information_schema.tables
WHERE table_schema = 'production'
  AND data_free > 0
ORDER BY frag_pct DESC;

-- 碎片率超过30%的表需要OPTIMIZE
-- OPTIMIZE TABLE会锁表，线上使用gh-ost或pt-online-schema-change
OPTIMIZE TABLE orders;

-- MySQL 8.0使用不可见索引安全测试删除索引的影响
ALTER TABLE orders ALTER INDEX idx_old_index INVISIBLE;
-- 观察一段时间，确认无影响后删除
ALTER TABLE orders DROP INDEX idx_old_index;

索引优化是一个持续过程。建议建立定期的慢查询巡检机制，每周分析一次pt-query-digest报告，对TOP 10慢查询逐一优化。同时通过performance_schema监控索引使用率，及时清理无用索引减少写入开销。数据库高可用架构层面，读写分离将分析查询分流到只读副本，进一步降低主库压力。分库分表方案在单表数据量超过千万行时也需要纳入考虑，通过ShardingSphere等中间件实现透明化分片路由。Redis缓存策略配合数据库使用，热点数据走缓存，减轻数据库读压力。

原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql-xing-neng-diao-you-shi-zhan-man-cha-xun-ding-wei-yu/

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

相关推荐