sort limit 中有一个相同 sort 项随机选择的逻辑,比如:
我们 sort 之后得到的结果是 a, b, c, d
加上 limit 3 之后,可能返回的是 b, a, d(a、b 的排序列相同,c、d 的排序列相同)
和预期的不一定是吻合的,这个可能需要注意一下。
官方文档的描述:
If you combine
LIMIT row_count
withORDER BY
, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result.
如果将LIMIT row_count
和ORDER BY
结合使用,MySQL 会在找到排序结果的前 row_count 行后立即停止排序,而不是对整个结果进行排序。
CREATE TABLE `testSortLimit` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`sort` INT(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`) USING BTREE
)
ENGINE=InnoDB;
INSERT INTO testSortLimit (name, sort) VALUES
("aaa", 0), ("eee", 1), ("iii", 1), ("mmm", 1),
("bbb", 0), ("fff", 1), ("jjj", 1), ("nnn", 1),
("ccc", 0), ("ggg", 1), ("kkk", 1), ("ooo", 1),
("ddd", 0), ("hhh", 1), ("lll", 1), ("ppp", 1);
SELECT * FROM testSortLimit;
SELECT * FROM testSortLimit ORDER BY sort ASC;
SELECT * FROM testSortLimit ORDER BY sort ASC LIMIT 6;
id | name | sort |
---|---|---|
1 | aaa | 0 |
5 | bbb | 0 |
9 | ccc | 0 |
13 | ddd | 0 |
2 | eee | 1 |
3 | iii | 1 |
4 | mmm | 1 |
6 | fff | 1 |
7 | jjj | 1 |
8 | nnn | 1 |
10 | ggg | 1 |
11 | kkk | 1 |
12 | ooo | 1 |
14 | hhh | 1 |
15 | lll | 1 |
16 | ppp | 1 |
id | name | sort |
---|---|---|
5 | bbb | 0 |
9 | ccc | 0 |
13 | ddd | 0 |
1 | aaa | 0 |
12 | ooo | 1 |
11 | kkk | 1 |
参考资料与拓展阅读
- MySQL 8.0, 8.2.1.19 LIMIT Query Optimization
- MySQL 5.7, 8.2.1.17 LIMIT Query Optimization
- MySQL 5.7 中文文档, 8.2.1.17 LIMIT 查询优化
- MySQL 5.1 中文文档, 7.2.14. MySQL如何优化LIMIT