我要评论最近接手一个老项目，某个列表接口响应时间30秒，用户疯狂投诉。
排查下来是sql问题，优化后降到300毫秒。记录一下完整过程。
一、发现问题
1.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秒记录 set global slow_query_log_file = '/var/log/mysql/slow.log';
1.2 分析慢查询日志
# 用mysqldumpslow分析 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # 输出 count: 1532 time=28.35s (43424s) lock=0.00s (0s) rows=100.0 (153200) select * from orders where user_id = n and status = n order by create_time desc limit n, n
找到了！这条sql执行了1532次，平均28秒。
二、分析sql
2.1 问题sql
select * from orders where user_id = 12345 and status = 1 order by create_time desc limit 0, 20;
看起来很简单，为什么慢？
2.2 explain分析
explain select * from orders where user_id = 12345 and status = 1 order by create_time desc limit 0, 20;
+----+-------------+--------+------+---------------+------+---------+------+----------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | rows | filtered | extra | +----+-------------+--------+------+---------------+------+---------+------+----------+-----------------------------+ | 1 | simple | orders | all | null | null | null | 5000000 | 0.10 | using where; using filesort | +----+-------------+--------+------+---------------+------+---------+------+----------+-----------------------------+
问题暴露了：
type = all：全表扫描key = null：没用到索引rows = 5000000：扫描500万行using filesort：额外排序
2.3 查看表结构
show create table orders;
create table `orders` ( `id` bigint not null auto_increment, `user_id` bigint not null, `status` tinyint not null default '0', `amount` decimal(10,2) not null, `create_time` datetime not null, `update_time` datetime not null, primary key (`id`) ) engine=innodb;
果然，只有主键索引，没有业务索引。
三、优化方案
3.1 添加联合索引
-- 创建联合索引 alter table orders add index idx_user_status_time (user_id, status, create_time);
再次explain：
+----+-------------+--------+------+---------------------+---------------------+---------+-------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | +----+-------------+--------+------+---------------------+---------------------+---------+-------------+------+-------+ | 1 | simple | orders | ref | idx_user_status_time| idx_user_status_time| 9 | const,const | 156 | null | +----+-------------+--------+------+---------------------+---------------------+---------+-------------+------+-------+
完美：
type = ref：使用索引rows = 156：只扫描156行extra = null：不需要额外排序
3.2 为什么这样设计索引？
联合索引顺序：(user_id, status, create_time) 查询条件：where user_id = ? and status = ? 排序条件：order by create_time 索引匹配过程： 1. user_id = 12345 → 定位到用户的订单 2. status = 1 → 进一步过滤状态 3. create_time → 索引本身有序，无需filesort
联合索引设计原则：
- 等值查询的列放前面
- 排序的列放最后
- 遵循最左前缀原则
3.3 优化效果
优化前：28.35秒，扫描500万行 优化后：0.003秒，扫描156行 提升：9000倍+
四、更多优化技巧
4.1 避免select *
-- 差：查询所有字段 select * from orders where ... -- 好：只查需要的字段 select id, user_id, amount, create_time from orders where ...
好处：
- 减少网络传输
- 可能用到覆盖索引
4.2 覆盖索引
如果查询的字段都在索引里，不需要回表：
-- 索引：idx_user_status_time (user_id, status, create_time) -- 这个查询可以用覆盖索引 select user_id, status, create_time from orders where user_id = 12345; -- explain显示 using index
4.3 避免索引失效
-- ❌ 对索引列使用函数 select * from orders where date(create_time) = '2024-01-01'; -- ✅ 改写 select * from orders where create_time >= '2024-01-01' and create_time < '2024-01-02'; -- ❌ 隐式类型转换 select * from orders where user_id = '12345'; -- user_id是bigint -- ✅ 类型一致 select * from orders where user_id = 12345; -- ❌ like前置通配符 select * from orders where order_no like '%abc'; -- ✅ like后置通配符（可以用索引） select * from orders where order_no like 'abc%';
4.4 分页优化
-- ❌ 深分页很慢 select * from orders order by id limit 1000000, 20; -- 需要扫描100万行 -- ✅ 用游标分页 select * from orders where id > 1000000 order by id limit 20; -- 直接定位，很快
五、索引设计原则
5.1 什么时候建索引？
| 场景 | 是否建索引 |
|---|---|
| where条件频繁查询的列 | ✅ 是 |
| order by排序的列 | ✅ 是 |
| join关联的列 | ✅ 是 |
| 区分度低的列（如性别） | ❌ 否 |
| 频繁更新的列 | ⚠️ 权衡 |
5.2 联合索引顺序
原则： 1. 区分度高的列放前面 2. 等值查询的列放前面 3. 排序列放最后
六、实用工具
6.1 慢查询分析
# mysqldumpslow mysqldumpslow -s t -t 10 slow.log # 按时间排序，前10条 # pt-query-digest（推荐） pt-query-digest slow.log > report.txt
6.2 explain详解
type（重要，从好到差）： - system/const：常量查询 - eq_ref：主键/唯一索引 - ref：普通索引 - range：范围扫描 - index：索引全扫描 - all：全表扫描 ❌ extra（重要）： - using index：覆盖索引 ✅ - using where：需要回表过滤 - using filesort：额外排序 ⚠️ - using temporary：临时表 ⚠️
七、远程数据库排查技巧
有时候问题数据库在测试环境，本地连不上怎么办？
我的做法是用组网工具把本地和测试服务器连起来。之前用vpn，经常断还慢。现在用星空组网，本地直接连测试环境的mysql：
# 组网后直接用虚拟ip连接 mysql -h 192.168.188.10 -u root -p
explain、慢查询分析都能直接在本地跑，比登服务器方便多了。
总结
sql优化核心步骤：
1. 开启慢查询日志 → 发现问题sql 2. explain分析 → 定位问题原因 3. 添加/优化索引 → 解决问题 4. 再次explain → 验证效果 5. 线上执行 → 监控观察
记住几个原则：
- 避免全表扫描
- 利用覆盖索引
- 注意索引失效场景
- 联合索引最左前缀
以上就是mysql慢查询优化从30秒到300毫秒的完整过程的详细内容，更多关于mysql慢查询优化30秒到300毫秒的资料请关注代码网其它相关文章！
发表评论