一、慢查询为什么让人头疼
每次打开系统发现页面加载要十几秒，DBA的报警短信接二连三发过来，这种场景相信很多开发同学都遇到过。数据库慢查询就像堵车时的红绿灯，明明看着很近，就是过不去。更可怕的是，慢查询往往像滚雪球一样，一个慢查询拖垮整个数据库，导致其他正常查询也跟着遭殃。
我见过最夸张的一个案例是，某电商平台大促时因为一个未优化的联表查询，导致数据库CPU直接飙到100%，整个下单系统瘫痪了2小时。事后分析发现，这个查询在全表扫描一个百万级数据表，而且每分钟被执行上千次。
二、找出罪魁祸首的几种方法
1. 慢查询日志分析
MySQL自带了一个非常实用的慢查询日志功能，就像汽车的故障诊断仪。要开启它只需要在my.cnf中配置：
# 技术栈：MySQL 5.7+
# 启用慢查询日志
slow_query_log = 1
# 指定日志文件路径
slow_query_log_file = /var/log/mysql/mysql-slow.log
# 定义超过多少秒算慢查询
long_query_time = 1
# 记录未使用索引的查询
log_queries_not_using_indexes = 1
这个配置会让MySQL记录所有执行时间超过1秒的查询。拿到日志后，可以用mysqldumpslow工具分析：
# 分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
这个命令会显示最耗时的10个查询，就像下面这样：
Count: 100 Time=2.34s (234s total) Lock=0.00s (0s total) Rows=10.0 (1000), user1[user1]@[10.0.0.1]
SELECT * FROM orders WHERE create_time > '2023-01-01' AND status = 'PENDING'
2. 实时监控正在运行的查询
有时候问题发生时需要立即抓现行，这时可以查看当前运行的查询：
-- 查看正在执行的查询
SHOW FULL PROCESSLIST;
-- 更详细的查看方式
SELECT * FROM information_schema.processlist
WHERE TIME > 10
ORDER BY TIME DESC;
我曾经用这个方法抓到一个"隐身"的慢查询：一个后台job每隔5分钟执行一次全表扫描，但刚好避开了慢查询日志的监控。
三、对症下药的优化技巧
1. 索引优化实战
索引就像书的目录，没有索引的查询就像要从头到尾翻完整本书。来看一个实际的优化案例：
-- 优化前（没有合适索引）
SELECT * FROM user_activities
WHERE user_id = 10086
AND activity_date BETWEEN '2023-01-01' AND '2023-03-31';
-- 优化后（添加复合索引）
ALTER TABLE user_activities ADD INDEX idx_user_activity (user_id, activity_date);
但索引不是越多越好，我曾经见过一个表建了20多个索引，导致插入性能下降90%。记住这几个原则：
- 为WHERE条件中的列建索引
- 考虑创建复合索引时，把区分度高的列放在前面
- 避免对频繁更新的列建过多索引
2. 查询语句重构的艺术
有时候稍微改写一下查询，性能就能提升几十倍：
-- 优化前（使用子查询）
SELECT * FROM products
WHERE category_id IN (
SELECT category_id FROM hot_categories WHERE is_active = 1
);
-- 优化后（改用JOIN）
SELECT p.* FROM products p
JOIN hot_categories hc ON p.category_id = hc.category_id
WHERE hc.is_active = 1;
再来看一个分页查询的优化例子：
-- 优化前（传统分页）
SELECT * FROM orders
WHERE user_id = 10086
ORDER BY create_time DESC
LIMIT 10000, 20;
-- 优化后（记住上一页的最后一条记录）
SELECT * FROM orders
WHERE user_id = 10086
AND create_time < '2023-03-01 12:00:00'
ORDER BY create_time DESC
LIMIT 20;
3. 巧用EXPLAIN分析执行计划
EXPLAIN是我们的诊断神器，它能显示MySQL如何执行查询：
EXPLAIN SELECT * FROM orders
WHERE user_id = 10086
AND status = 'SHIPPED';
关键要看这些列：
- type：最好看到const/eq_ref/ref，避免看到ALL（全表扫描）
- key：确认使用了正确的索引
- rows：预估扫描的行数
- Extra：注意是否有"Using filesort"或"Using temporary"
四、高级优化策略
1. 数据库参数调优
MySQL的配置参数就像汽车的发动机调校，合适的参数能让性能大幅提升：
# my.cnf 关键参数优化
innodb_buffer_pool_size = 4G # 设置为可用内存的70-80%
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2 # 非关键业务可以牺牲一点安全性换取性能
query_cache_size = 0 # 在MySQL 8.0中查询缓存已被移除
2. 读写分离架构
对于读多写少的应用，可以考虑读写分离：
// Java Spring Boot配置示例
@Configuration
public class DataSourceConfig {
@Bean
@Primary
@ConfigurationProperties("spring.datasource.master")
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.slave")
public DataSource slaveDataSource() {
return DataSourceBuilder.create().build();
}
}
3. 缓存层引入
对于热点数据，可以引入Redis缓存：
# Python Django示例
from django.core.cache import cache
def get_user_orders(user_id):
cache_key = f"user_orders_{user_id}"
orders = cache.get(cache_key)
if not orders:
orders = Order.objects.filter(user_id=user_id).all()
cache.set(cache_key, orders, timeout=300) # 缓存5分钟
return orders
五、避坑指南
- 不要在WHERE子句中对字段使用函数，这会导致索引失效：
-- 错误的写法
SELECT * FROM orders WHERE DATE(create_time) = '2023-01-01';
-- 正确的写法
SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02';
- 小心OR条件，可能导致索引失效：
-- 可能不走索引的写法
SELECT * FROM products WHERE category_id = 1 OR price > 100;
-- 可以改写成UNION
SELECT * FROM products WHERE category_id = 1
UNION
SELECT * FROM products WHERE price > 100;
- 大表ALTER操作要谨慎，可能导致锁表：
-- 危险的直接修改
ALTER TABLE big_table ADD COLUMN new_column INT;
-- 更安全的pt-online-schema-change
pt-online-schema-change --alter "ADD COLUMN new_column INT" D=test,t=big_table
六、总结
慢查询优化是个系统工程，需要从查询语句、索引设计、数据库配置、架构设计等多个层面综合考虑。就像医生看病一样，要先诊断再治疗。我建议每个项目都应该：
- 定期分析慢查询日志
- 对新上线的SQL进行EXPLAIN分析
- 建立性能测试机制
- 关键查询要有监控告警
记住，优化不是一劳永逸的，随着数据量增长和业务变化，今天快的查询明天可能就变慢了。保持对数据库性能的关注，才能让系统持续稳定运行。
评论