深入浅出 MySQL 优先队列

 

本文转载自微信公众号「Java课代表」，深入作者Java课代表 。浅出转载本文请联系Java课代表公众号。先队

本文适用于 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 Optimization 中对此场景有如下描述：

If multiple rows have 深入identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns. One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders.

总结来说就是：

当 ORDER BY 列的字段值存在重复，那么这条 ORDER BY 语句返回的浅出数据顺序会因为LIMIT的存在而变得不一样

这是 MySQL 默认对该场景做的优化，如果你需要保证加不加 LIMIT 顺序都要一致，先队官方也给出了办法：

If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic.

就是源码下载在ORDER BY 后面再多加一个排序字段(比如 ID 字段)。

以上描述最早出现在MySQL 5.6文档中，从这个版本开始，引入了这个针对ORDER BY LIMIT的优化。

好了， 针对文中的场景，我们只需要select * from ratings order by category,id;即可解决。

那么问题来了，MySQL 为什么要做这么一个看似是 Bug 的优化?

2.MySQL 的 ORDER BY 逻辑

顾名思义，ORDER BY 就是排序。

执行一下explain select * from ratings order by category limit 5;

本文地址：http://www.bzuk.cn/html/090e30099609.html