MySQL SQL 调优完整指南

SQL 调优是一个系统性工程，需要从发现问题到解决问题的全流程掌握。下面从方法论到具体技巧详细讲解。

二、发现问题：定位慢查询

1. 开启慢查询日志

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

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过1秒的记录

-- 查看慢查询日志
mysqldumpslow -s t -t 10 /var/lib/mysql/slow-query.log

2. 查看正在执行的慢查询

-- 查看当前正在执行的所有查询
SHOW PROCESSLIST;

-- 找出执行时间长的
SELECT * FROM information_schema.PROCESSLIST
WHERE TIME > 5 AND COMMAND != 'Sleep'
ORDER BY TIME DESC;

三、分析问题：使用 EXPLAIN 

1. EXPLAIN 基本用法

EXPLAIN SELECT * FROM users WHERE name = '张三'\G

-- 输出关键字段

2. 关注 Extra 字段

-- ✅ 好
Using index -- 覆盖索引，不需要回表
Using index condition -- 索引下推

-- ⚠️ 需要优化
Using filesort -- 需要额外排序
Using temporary -- 用了临时表

四、解决问题：核心优化技巧

1. 索引优化

-- 为 WHERE 条件建索引
CREATE INDEX idx_name ON users(name);

-- 为 ORDER BY 建索引
CREATE INDEX idx_create_time ON orders(create_time);

-- 复合索引注意最左前缀
CREATE INDEX idx_name_age ON users(name, age);

2. 避免 SELECT *

-- ❌ 不好
SELECT * FROM users WHERE name = '张三';

-- ✅ 好（只查需要的字段）
SELECT id, name FROM users WHERE name = '张三';

3. 避免在索引列上使用函数

-- ❌ 无法使用索引
SELECT * FROM orders WHERE YEAR(create_time) = 2024;

-- ✅ 可以走索引
SELECT * FROM orders
WHERE create_time >= '2024-01-01'
AND create_time < '2025-01-01';

4. 分页优化

-- ❌ 深分页问题
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

-- ✅ 使用游标分页
SELECT * FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 10;

5. JOIN 优化

-- 小表驱动大表
-- 为 JOIN 字段建索引
CREATE INDEX idx_user_id ON orders(user_id);

五、高级优化技巧

1. 使用覆盖索引

-- 创建包含所有查询字段的索引
CREATE INDEX idx_covering ON users(name, age, id);

-- 查询可以直接从索引获取数据
SELECT id, name, age FROM users WHERE name = '张三';
-- Extra: Using index

2. 合理使用 EXISTS 替代 IN

-- IN 在大数据量时可能慢
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- EXISTS 可能更快
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.amount > 1000);

3. 批量操作优化

-- 批量插入
INSERT INTO users (name) VALUES
('张三'), ('李四'), ('王五'); -- 一次插入多条

-- 批量更新使用临时表
CREATE TEMPORARY TABLE temp_updates (
id INT PRIMARY KEY,
age INT
);

六、监控和验证

1. 查看索引使用情况

-- 查看索引使用次数
SELECT
index_name,
rows_selected,
rows_inserted
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'db_name';

-- 查看从未使用的索引
SELECT * FROM sys.schema_unused_indexes;

2. 查看查询缓存命中率

SHOW STATUS LIKE 'Qcache%';
SHOW STATUS LIKE 'Handler_read%';