MySQL查询优化-让你的SQL飞起来

一、为什么需要SQL优化？

SQL优化是数据库性能提升的核心，合理优化可以让查询速度提升几十甚至几百倍。

1.1 慢查询的影响

┌─────────────────────────────────────────────────────────────────┐
│                      慢查询的影响                                 │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  🚫 用户体验差 → 页面加载慢，流失用户                             │
│  💰 资源消耗大 → CPU、内存、IO飙升                               │
│  🔒 连接耗尽 → 其他请求无法获取连接                              │
│  💣 引发故障 → 严重时导致数据库宕机                               │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

1.2 优化目标

指标	说明	优化建议
响应时间	SQL执行时间	< 100ms
吞吐量	每秒查询数	根据业务需求
资源占用	CPU/内存/IO	合理利用

二、慢查询日志

2.1 开启慢查询日志

-- 查看慢查询是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询阈值（秒）
SET GLOBAL long_query_time = 2;

-- 查看阈值
SHOW VARIABLES LIKE 'long_query_time';

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

2.2 配置文件永久生效

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

2.3 分析慢查询日志

# 使用 mysqldumpslow 工具分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 参数说明
# -s: 排序方式 (t=时间, c=次数)
# -t: 取前N条

# 示例
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log  # 访问次数最多的20条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log  # 最慢的10条

三、EXPLAIN分析

3.1 EXPLAIN基本使用

-- 分析SQL执行计划
EXPLAIN SELECT * FROM users WHERE id = 1;

EXPLAIN SELECT u.name, o.total 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id 
WHERE u.age > 25;

3.2 EXPLAIN结果字段

字段	说明
id	查询序号，越大越先执行
select_type	查询类型
table	访问的表
type	连接类型（重要）
possible_keys	可能使用的索引
key	实际使用的索引
key_len	索引长度
ref	与索引比较的列
rows	扫描行数（重要）
Extra	额外信息（重要）

3.3 type（连接类型）

类型	说明	性能
const	主键或唯一索引查找	最好
eq_ref	唯一索引连接	好
ref	非唯一索引查找	一般
range	索引范围查询	一般
index	全索引扫描	较差
ALL	全表扫描	最差

┌─────────────────────────────────────────────────────────────────┐
│                        type 性能排序                             │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  最好 ← const → eq_ref → ref → range → index → ALL → 最差     │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

3.4 Extra（额外信息）

值	说明
Using filesort	需要额外排序，效率低
Using temporary	使用临时表，效率低
Using index	使用索引覆盖，不需要回表
Using where	使用WHERE过滤
Using index condition	索引下推

四、SQL优化技巧

4.1 避免全表扫描

-- ❌ 慢：全表扫描
SELECT * FROM users WHERE age + 1 = 26;
SELECT * FROM users WHERE LEFT(name, 1) = '张';

-- ✅ 快：使用索引
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE name LIKE '张%';

-- ✅ 使用覆盖索引
SELECT id, name, age FROM users WHERE age = 25;

4.2 避免索引失效

-- ❌ 失效：使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2026;

-- ✅ 生效：直接比较
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- ❌ 失效：类型转换
SELECT * FROM users WHERE id = '1';  -- id是INT类型

-- ✅ 生效：类型匹配
SELECT * FROM users WHERE id = 1;

-- ❌ 失效：IS NULL
SELECT * FROM users WHERE name IS NULL;

-- ✅ 替代方案
SELECT * FROM users WHERE name = '' OR name IS NULL;

4.3 优化LIMIT分页

-- ❌ 慢：OFFSET大时性能差
SELECT * FROM users ORDER BY id LIMIT 100000, 10;

-- ✅ 快：使用ID范围
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 10;

-- ✅ 快：子查询
SELECT * FROM users 
WHERE id >= (SELECT id FROM users ORDER BY id LIMIT 100000, 1) 
LIMIT 10;

4.4 优化JOIN

-- ✅ 小表驱动大表
SELECT * FROM users u 
INNER JOIN orders o ON u.id = o.user_id;

-- 添加合适的索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- ❌ 避免子查询在WHERE中
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- ✅ 改为JOIN
SELECT u.* FROM users u 
INNER JOIN orders o ON u.id = o.user_id;

4.5 优化聚合函数

-- ✅ 使用索引
SELECT COUNT(*) FROM users WHERE age > 25;

-- ✅ 使用覆盖索引
SELECT COUNT(*) FROM users USE INDEX (idx_age);

-- ✅ 预先计算
ALTER TABLE orders ADD COLUMN monthly_count INT DEFAULT 0;
-- 定期更新统计表

五、实战案例

5.1 案例一：分页查询优化

-- 原始SQL（慢）
SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 10000, 10;

-- 优化方案1：使用ID
SELECT * FROM orders 
WHERE id > (SELECT id FROM orders ORDER BY id LIMIT 10000, 1)
ORDER BY id 
LIMIT 10;

-- 优化方案2：使用游标
SELECT * FROM orders 
WHERE created_at < '2026-01-01'
ORDER BY created_at DESC 
LIMIT 10;

-- 优化方案3：使用延迟关联
SELECT o.* FROM orders o 
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 10000, 10) t 
ON o.id = t.id;

5.2 案例二：COUNT(*)优化

-- 原始（慢）
SELECT COUNT(*) FROM orders WHERE status = 1;

-- 优化1：使用索引
ALTER TABLE orders ADD INDEX idx_status (status);
SELECT COUNT(*) FROM orders WHERE status = 1;

-- 优化2：使用近似值
SHOW TABLE STATUS LIKE 'orders';

-- 优化3：使用统计表
CREATE TABLE order_stats (
    date DATE PRIMARY KEY,
    total_count INT DEFAULT 0,
    paid_count INT DEFAULT 0
);
-- 定时更新统计表

5.3 案例三：复杂查询优化

-- 原始SQL
SELECT 
    u.name,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count,
    (SELECT SUM(total) FROM orders WHERE user_id = u.id) as total_amount
FROM users u
WHERE u.status = 1;

-- 优化：使用JOIN
SELECT 
    u.name,
    COUNT(o.id) as order_count,
    COALESCE(SUM(o.total), 0) as total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 1
GROUP BY u.id, u.name;

-- 添加索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

六、索引优化

6.1 选择合适的列

-- ✅ 适合建索引
-- 1. WHERE条件频繁的列
WHERE age = 25
-- 2. ORDER BY排序的列
ORDER BY created_at
-- 3. JOIN连接的列
ON user_id = id

-- ❌ 不适合建索引
-- 1. 区分度低的列（性别、状态）
-- 2. 很少查询的列
-- 3. 表数据量小

6.2 复合索引

-- 创建复合索引
ALTER TABLE orders ADD INDEX idx_status_date (status, created_at);

-- 使用原则：最左前缀
-- ✅ 生效
WHERE status = 1
WHERE status = 1 AND created_at > '2026-01-01'
WHERE created_at > '2026-01-01' AND status = 1

-- ❌ 不生效
WHERE created_at > '2026-01-01'

6.3 索引覆盖

-- 覆盖索引：查询的列都在索引中
-- 创建覆盖索引
ALTER TABLE users ADD INDEX idx_age_name (age, name);

-- 使用覆盖索引，不需要回表
SELECT name, age FROM users WHERE age = 25;

-- ❌ 无法使用覆盖索引
SELECT * FROM users WHERE age = 25;

七、优化配置

7.1 关键配置参数

[mysqld]
# 连接数
max_connections = 200

# 缓冲池大小（重要！）
innodb_buffer_pool_size = 1G

# 日志文件大小
innodb_log_file_size = 256M

# 刷新方式
innodb_flush_log_at_trx_commit = 2

# 查询缓存（MySQL 8.0已移除）
# query_cache_type = 1
# query_cache_size = 64M

7.2 查看性能

-- 查看当前连接
SHOW PROCESSLIST;

-- 查看查询缓存状态
SHOW STATUS LIKE 'Qcache%';

-- 查看线程状态
SHOW STATUS LIKE 'Threads%';

-- 查看表统计
SHOW TABLE STATUS FROM database_name;

-- 查看索引统计
SHOW INDEX FROM table_name;

八、常见问题

8.1 查询突然变慢

-- 1. 检查是否有锁等待
SHOW ENGINE INNODB STATUS;

-- 2. 检查慢查询
SHOW FULL PROCESSLIST;

-- 3. 检查表是否需要 ANALYZE
ANALYZE TABLE table_name;

-- 4. 检查是否有死锁
SHOW ENGINE INNODB STATUS;

8.2 CPU使用率高

-- 1. 查找消耗CPU的SQL
SHOW PROCESSLIST;

-- 2. 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 3. 分析慢查询
EXPLAIN SELECT ...

-- 4. 添加索引
ALTER TABLE ...

九、工具推荐

工具	说明
mysqldumpslow	慢查询日志分析
EXPLAIN	SQL执行计划分析
SHOW PROFILE	性能分析
Performance Schema	性能监控
pt-query-digest	Percona工具

十、优化口诀

┌─────────────────────────────────────────────────────────────────┐
│                      SQL优化口诀                                 │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  1. 索引建立要趁早，WHERE条件不能少                               │
│  2. 模糊查询尽量少，最左前缀要记牢                               │
│  3. 排序字段建索引，SELECT只取需要的                             │
│  4. 分页大OFFSET，改为游标或ID                                   │
│  5. JOIN要小表驱动，复合索引最左走                               │
│  6. 慢查询日志开，分析工具来帮忙                                 │
│  7. EXPLAIN细细看，type要达到ref以上                             │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

参考资料

持续更新中…欢迎收藏！

#MySQL #SQL优化 #性能优化 #EXPLAIN #教程

数据库

#MySQL #性能优化 #SQL优化 #EXPLAIN

MySQL查询优化-让你的SQL飞起来

https://r0f2.my/post/18-mysql-query-optimization/

作者

发布于

2026年2月14日

许可协议

MySQL索引详解-高效查询的秘密上一篇

VSCode连接云服务器SSH-远程开发指南下一篇