SQL 优化 - order by 与 group by

排序与分组的共同优化思路：让 MySQL 直接利用索引的有序性，而不是先取数据再进缓冲区排序。

一、order by 优化

1.1 两种排序方式

Extra	名称	说明
`Using filesort`	文件排序	通过表的索引或全表扫描读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序。凡是不能通过索引直接返回排序结果的排序都叫 FileSort 排序
`Using index`	索引排序	通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高

# 没有创建索引时，根据 age, phone 进行排序
explain select id, age, phone from tb_user order by age, phone;

# 创建索引
create index idx_user_age_phone_aa on tb_user(age, phone);

# 创建索引后，根据 age, phone 进行升序排序
explain select id, age, phone from tb_user order by age, phone;

# 创建索引后，根据 age, phone 进行降序排序
explain select id, age, phone from tb_user order by age desc, phone desc;

1.2 实验：排序字段与索引的匹配关系

索引 idx_user_age_phone_aa(age, phone) 的对比结果：

排序语句	type	key	Extra	结论
`order by age, phone`（建索引前）	ALL	NULL	`Using filesort`	全表扫描后在缓冲区排序
`order by age`	index	idx_user_age_phone_aa	`Using index`	索引本身按 age 有序，直接返回
`order by age, phone`	index	idx_user_age_phone_aa	`Using index`	排序字段与索引列顺序一致
`order by age desc, phone desc`	index	idx_user_age_phone_aa	`Backward index scan; Using index`	两列同向，可反向扫描索引
`order by phone, age`	index	idx_user_age_phone_aa	`Using index; Using filesort`	违背最左前缀，需在缓冲区二次排序
`order by age asc, phone desc`	index	idx_user_age_phone_aa	`Using index; Using filesort`	一升一降，与索引的默认 ASC 方向不符

一升一降需要按方向建索引：

# 根据 age, phone 排序：一个升序，一个降序
explain select id, age, phone from tb_user order by age asc, phone desc;

# 创建索引（显式指定 ASC / DESC）
create index idx_user_age_pho_ad on tb_user(age asc, phone desc);

# 再次 explain：key = idx_user_age_pho_ad，Extra = Using index
explain select id, age, phone from tb_user order by age asc, phone desc;

age asc, phone asc  →  索引 (age asc, phone asc)  可直接顺序扫描
age asc, phone desc →  索引 (age asc, phone desc) 才能直接顺序扫描

1.3 覆盖索引

-- select * 需要回表取全部列，优化器放弃索引 → type = ALL，Extra = Using filesort
explain select * from tb_user order by age asc, phone asc;

排序时尽量使用覆盖索引：返回列都在索引中，才能既走索引顺序扫描、又避免回表。

1.4 优化要点

根据排序字段建立合适的索引，多字段排序时也遵循最左前缀法则；
尽量使用覆盖索引；
多字段排序一个升序一个降序时，需要注意联合索引在创建时的规则（ASC / DESC）；
若不可避免出现 filesort，大数据量排序时可以适当增大排序缓冲区大小 sort_buffer_size（默认 256k）。

二、group by 优化

# 删除掉目前的联合索引 idx_user_pro_age_sta
drop index idx_user_pro_age_sta on tb_user;

# 执行分组操作，根据 profession 字段分组
explain select profession, count(*) from tb_user group by profession;

# 创建索引
create index idx_user_pro_age_sta on tb_user(profession, age, status);

# 执行分组操作，根据 profession 字段分组
explain select profession, count(*) from tb_user group by profession;

# 执行分组操作，根据 profession, age 字段分组
explain select profession, count(*) from tb_user group by profession, age;

以 idx_user_pro_age_sta(profession, age, status) 为例：

分组语句	type	key	Extra	结论
`group by profession`（建索引前）	ALL	NULL	`Using temporary`	需要借助临时表完成分组
`group by profession`	index	idx_user_pro_age_sta	`Using index`	索引有序，直接分组
`group by age`	index	idx_user_pro_age_sta	`Using index; Using temporary`	跳过最左列，索引无法用于分组
`group by profession, age`	index	idx_user_pro_age_sta	`Using index`	满足最左前缀
`where profession = '软件工程' group by age`	ref	idx_user_pro_age_sta	`Using index`	最左列写在 where 中，`group by` 用第二列仍可用索引

在分组操作时，可以通过索引来提高效率；分组操作时索引的使用同样满足最左前缀法则。
分组前若用 where 过滤掉最左列，group by 的字段顺序要求可放宽（如上面的 where profession = … group by age）。

速查

-- order by：排序字段建索引，多字段遵循最左前缀
explain select id, age, phone from tb_user order by age, phone;   -- 无索引 → Using filesort
create index idx_user_age_phone_aa on tb_user(age, phone);
explain select id, age, phone from tb_user order by age, phone;   -- Using index
explain select id, age, phone from tb_user order by age desc, phone desc;  -- Backward index scan
explain select id, age, phone from tb_user order by phone, age;            -- 违背最左前缀 → Using filesort

-- order by：一升一降需按方向建索引
explain select id, age, phone from tb_user order by age asc, phone desc;   -- 索引方向不符 → Using filesort
create index idx_user_age_pho_ad on tb_user(age asc, phone desc);
explain select id, age, phone from tb_user order by age asc, phone desc;   -- Using index

-- group by：分组字段建索引，同样遵循最左前缀
drop index idx_user_pro_age_sta on tb_user;
explain select profession, count(*) from tb_user group by profession;      -- 无索引 → Using temporary
create index idx_user_pro_age_sta on tb_user(profession, age, status);
explain select profession, count(*) from tb_user group by profession;      -- Using index
explain select profession, age, count(*) from tb_user group by profession, age;   -- Using index
explain select age, count(*) from tb_user where profession = '软件工程' group by age;  -- Using index

目标	手段
排序避免 `Using filesort`	按排序字段（含 ASC / DESC 方向）建联合索引；尽量覆盖索引
分组避免 `Using temporary`	按分组字段建索引；多字段分组满足最左前缀
无法避免 filesort	大数据量排序时调大 `sort_buffer_size`（默认 256k）