英语和算法是程序员的两条腿
本文适用于 MySQL 5.6 及以上版本
0.先抛问题
假设字段category
无索引且有重复值,order by category
和limit
组合使用的结果会和预期不符。
问题复现:
表结构(就是两个字段)
CREATE TABLE `ratings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
对所有数据按category
字段排序:select * from ratings order by category;
id | category |
---|---|
1 | 1 |
5 | 1 |
10 | 1 |
3 | 2 |
4 | 2 |
6 | 2 |
9 | 2 |
2 | 3 |
7 | 3 |
8 | 3 |
当我们想分页展示前5条时使用select * from ratings order by category limit 5;
期望得到的ID
顺序是1 5 10 3 4
。
但实际结果如下:
id | category |
---|---|
1 | 1 |
10 | 1 |
5 | 1 |
3 | 2 |
4 | 2 |
怎么肥似?MySQL 出 Bug 了?
可能有同学遇到过这个问题,百度或谷歌一下解决了,你有没有想过,你查到的办法是最优解吗?别人是怎么得出这个办法的?MySQL 为什么会这样做,跟版本有关吗?
先抛结论:
- 最优解是后面再加个列值唯一的排序字段,如:
order by category,id
- MySQL 为什么这样做?答案是为了快!(
MySQL 5.6
及其之后才有此优化) - 次优解是对
order by
后面的category
加索引(为什么是次优解?看完本文你将会有答案)
下面课代表将还原一下这 3 条结论的产出过程。
1. 最优解
MySQL 文档 8.2.1.19 LIMIT Query Optimiz