MySQL慢查询优化从30秒到300毫秒的完整过程

-- 查看是否开启
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';

# 用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

select * 
from orders 
where user_id = 12345 
  and status = 1 
order by create_time desc 
limit 0, 20;

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 |
+----+-------------+--------+------+---------------+------+---------+------+----------+-----------------------------+

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;

-- 创建联合索引
alter table orders add index idx_user_status_time (user_id, status, create_time);

+----+-------------+--------+------+---------------------+---------------------+---------+-------------+------+-------+
| 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  |
+----+-------------+--------+------+---------------------+---------------------+---------+-------------+------+-------+

联合索引顺序：(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

优化前：28.35秒，扫描500万行
优化后：0.003秒，扫描156行

提升：9000倍+

-- 差：查询所有字段
select * from orders where ...

-- 好：只查需要的字段
select id, user_id, amount, create_time from orders where ...

-- 索引：idx_user_status_time (user_id, status, create_time)

-- 这个查询可以用覆盖索引
select user_id, status, create_time from orders where user_id = 12345;

-- explain显示 using index

-- ❌ 对索引列使用函数
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%';

-- ❌ 深分页很慢
select * from orders order by id limit 1000000, 20;
-- 需要扫描100万行

-- ✅ 用游标分页
select * from orders where id > 1000000 order by id limit 20;
-- 直接定位，很快

原则：
1. 区分度高的列放前面
2. 等值查询的列放前面
3. 排序列放最后

# mysqldumpslow
mysqldumpslow -s t -t 10 slow.log  # 按时间排序，前10条

# pt-query-digest（推荐）
pt-query-digest slow.log > report.txt

type（重要，从好到差）：
- system/const：常量查询
- eq_ref：主键/唯一索引
- ref：普通索引
- range：范围扫描
- index：索引全扫描
- all：全表扫描 ❌

extra（重要）：
- using index：覆盖索引 ✅
- using where：需要回表过滤
- using filesort：额外排序 ⚠️
- using temporary：临时表 ⚠️

# 组网后直接用虚拟ip连接
mysql -h 192.168.188.10 -u root -p

1. 开启慢查询日志 → 发现问题sql
2. explain分析 → 定位问题原因
3. 添加/优化索引 → 解决问题
4. 再次explain → 验证效果
5. 线上执行 → 监控观察