MySQL性能调优实战：慢查询定位分析与索引优化策略详解

MySQL慢查询日志配置与定位方法

MySQL性能调优的第一步是找到执行缓慢的SQL语句。慢查询日志（Slow Query Log）是MySQL内置的慢SQL记录机制，记录所有执行时间超过阈值的SQL。数据库运维实践中，慢查询日志是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
log_slow_admin_statements = 1
log_slow_slave_statements = 1

开启慢查询日志后，使用mysqldumpslow或pt-query-digest分析慢SQL。pt-query-digest是Percona Toolkit工具，按SQL指纹聚合统计，输出更丰富的分析报告：

# 按执行次数排序
pt-query-digest /var/log/mysql/slow.log --order-by Count

# 只分析执行时间超过5秒的SQL
pt-query-digest /var/log/mysql/slow.log --filter '$event->{Exec_time} > 5'

# 输出示例
# Rank Query ID           Response time Calls R/Call    V/M
# ==== ================== ============= ===== ========= =====
#    1 0xABC123...         120.5600 45.2%   50 2.4112  0.15
#    2 0xDEF456...          80.3000 30.1%  200 0.4015  0.05

Response time占比和Calls执行次数是判断优先优化哪条SQL的两个维度。高Response time + 低Calls意味着单次执行极慢，适合通过索引优化解决；低Response time + 高Calls意味着频繁执行，适合通过缓存或批处理解决。

EXPLAIN执行计划深度解读

定位慢SQL后，使用EXPLAIN分析执行计划。EXPLAIN输出12个字段，其中type、key、rows、Extra四个字段最为关键。

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

+----+--------+-----------+-------+--------------------+-----------+---------+-------+------+-----------------------+
| id | select | table     | type  | possible_keys      | key       | key_len | ref   | rows | Extra                 |
+----+--------+-----------+-------+--------------------+-----------+---------+-------+------+-----------------------+
|  1 | SIMPLE | o         | ref   | idx_user,idx_status| idx_status| 4       | const | 5000 | Using index condition |
|  1 | SIMPLE | u         | eq_ref| PRIMARY            | PRIMARY   | 8       | o.user_id| 1 | NULL                  |
+----+--------+-----------+-------+--------------------+-----------+---------+-------+------+-----------------------+

type字段的性能排序从好到差：system > const > eq_ref > ref > range > index > ALL。其中eq_ref出现在主键或唯一索引等值连接时，是连接查询中最优的类型。ALL表示全表扫描，在生产环境要彻底消除。

Extra字段的常见值及其含义：

• Using index：覆盖索引，无需回表，性能最优
• Using where：通过WHERE条件过滤，需要关注rows估算值
• Using index condition：索引条件下推（ICP），在存储引擎层过滤
• Using temporary：使用临时表，常见于GROUP BY和DISTINCT
• Using filesort：文件排序，常见于ORDER BY未命中索引
• Using join buffer：使用连接缓冲区，被驱动表未命中索引

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

索引优化是MySQL性能调优最有效的手段。联合索引的设计遵循最左前缀原则，索引列的顺序应按照区分度从高到低排列。数据库高可用架构中，合理的索引设计可以将查询响应时间从秒级降到毫秒级。

-- 订单表结构
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_user_status_created (user_id, status, created_at),
    INDEX idx_status_created (status, created_at),
    UNIQUE INDEX uk_order_no (order_no)
) ENGINE=InnoDB;

-- 场景1：按用户查订单列表（命中idx_user_status_created）
SELECT * FROM orders 
WHERE user_id = 10086 AND status = 'PAID' 
ORDER BY created_at DESC LIMIT 20;
-- type=ref, key=idx_user_status_created

-- 场景2：按状态查最近订单（命中idx_status_created）
SELECT * FROM orders 
WHERE status = 'PAID' AND created_at > '2026-07-01'
ORDER BY created_at DESC LIMIT 20;
-- type=range, key=idx_status_created

-- 场景3：按用户查订单（部分命中索引）
SELECT * FROM orders WHERE user_id = 10086 ORDER BY created_at DESC;
-- Using filesort（status未指定）
-- 优化：添加idx_user_created(user_id, created_at)

覆盖索引是索引优化的高级技巧。当查询所需的所有列都包含在索引中时，InnoDB直接从索引树返回数据，无需回表查询聚簇索引：

-- 覆盖索引：所有查询列都在索引中
ALTER TABLE orders ADD INDEX idx_cover_list 
    (user_id, status, created_at, order_no, amount);
-- EXPLAIN Extra显示Using index，完全避免回表

SQL查询重写技巧与分库分表方案

索引优化之外，SQL本身的写法也影响执行性能。常见SQL查询优化技巧：

-- 避免：SELECT * 查询不需要的列
SELECT * FROM orders WHERE user_id = 10086;
-- 优化：只查需要的列
SELECT id, order_no, amount, created_at FROM orders WHERE user_id = 10086;

-- 避免：函数操作导致索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-01';
-- 优化：改为范围查询
SELECT * FROM orders 
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02';

-- 避免：隐式类型转换导致索引失效
SELECT * FROM orders WHERE order_no = 123456789;
-- 优化：使用正确的字符串类型
SELECT * FROM orders WHERE order_no = '123456789';

-- 避免：NOT IN效率低
SELECT * FROM orders WHERE user_id NOT IN (SELECT user_id FROM blacklist);
-- 优化：LEFT JOIN + IS NULL
SELECT o.* FROM orders o
LEFT JOIN blacklist b ON o.user_id = b.user_id
WHERE b.user_id IS NULL;

-- 分页优化：深分页使用游标方案
SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20;
-- 优化：子查询先走索引找到ID
SELECT * FROM orders 
WHERE id <= (SELECT id FROM orders ORDER BY id DESC LIMIT 1 OFFSET 100000)
ORDER BY id DESC LIMIT 20;

数据备份恢复和分库分表方案是数据库运维的高级话题。当单表数据量超过1000万行后，B+树索引层数增加，查询性能下降。分库分表方案通过ShardingSphere或MyCat中间件将数据分散到多个物理库表。数据迁移实战中，影子表双写+一致性校验是平滑过渡的标准方案。

MySQL性能调优是一个系统工程。单条SQL的索引优化可以解决80%的性能问题，剩余20%需要从SQL写法、表结构设计、分库分表、Redis缓存策略等多维度协同解决。定期使用pt-query-digest分析慢日志，结合EXPLAIN逐条审查高频SQL，建立持续的性能优化机制。国产数据库方面，OceanBase和TiDB在分布式SQL兼容性上的进步也为MySQL迁移提供了更多选择。