union和子查询中order by一起使用导致排序失效问题

深碍√TFBOYSˉ_ 2022-08-28 12:48 277阅读 0赞

# 一、前言 #

分页查询的需求如同家常便饭，多数情况下主要利用`order by`和`limit`即可实现，有些稍复杂一点的可能需要用到`union`操作去连接多个子查询结果集。然而这三个操作是有一些需要留意的问题，下文将列举出3个可能碰到的情况。  
**MySQL版本：5.7.21**

# 二、问题列举 #

### 2.1 子查询中不能使用order by ###

简单的`union`操作如下：

SELECT * FROM tb_artist WHERE id >= 10
    UNION
    SELECT * FROM tb_artist WHERE id < 10

其结果为：  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGF5RGF5VXDkuLY_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]若子查询中包含`order by`，写法假设如下：

SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC
    UNION
    SELECT * FROM tb_artist WHERE id < 10

则语法报错：***1221 - Incorrect usage of UNION and ORDER BY***  
应当将子查询括起来，正确写法如下：

(SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC)
    UNION
    (SELECT * FROM tb_artist WHERE id < 10)

其结果为：  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGF5RGF5VXDkuLY_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 1]语法没问题了，但是注意到子查询的`order by`却没有效果，故引出下面第二种问题。

### 2.2 子查询order by无效 ###

解决方式是在子查询的`order by`后添加`limit`操作即可，具体可以`limit`一个不小于子查询结果集大小的数值，如下：

(SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC LIMIT 99999)
    UNION
    (SELECT * FROM tb_artist WHERE id < 10)

其结果为：  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGF5RGF5VXDkuLY_size_20_color_FFFFFF_t_70_g_se_x_16]

### 2.3 排序条件不够严格导致分页数据重复 ###

该问题与`union`无直接联系，属于`order by`及`limit`本身的注意点。  
即，如果SQL中的`order by`条件比较宽松不够严格，或者说是结果集中的每行记录存在并列或不唯一的次序的话，MySQL可能会随机给并列记录行进行排序，特别是排序又分页查询配合了`limit`操作，可能会导致上一页有的记录行又出现在了下一页的结果集之中。  
对一些后台系统的分页表格中可能感觉不明显，但对于同样SQL查询逻辑的C端下拉列表的效果一目了然，特别是对带有图片的，很容易看到数据的重复。

#### 解决方案： ####

所以在做分页查询时，要尽量保证每行记录都有唯一确定的次序，具体做法可以在原有排序条件后添加id或编号等这类唯一值的字段（索引字段也可以）。  
例如，在艺术家artist创建时间降序排序的基础上，再对唯一code进行排序：

SELECT * FROM tb_artist WHERE logic_delete = 0 
    ORDER BY 
    	create_time DESC, 
    	code ASC 
    LIMIT 0, 5

#### 原因分析： ####

排序离不开算法，在关系型数据库中，往往会存在多种排序算法。通过`MySQL`的源码和官方文档介绍可以得知，它的排序规律可以总结如下：

1.  当`order by`不能使用索引进行排序时，将使用排序算法进行排序；
2.  若排序内容能全部放入内存，则仅在内存中使用快速排序；
3.  若排序内容不能全部放入内存，则分批次将排好序的内容放入文件，然后将多个文件进行归并排序；
4.  若排序中包含`limit`语句，则使用堆排序（不稳定）优化排序过程。

所以解决排序分页数据重复问题有两种方式，第一种就是，在排序中加上唯一值，比如主键 id，这样由于 id 是唯一的，就能确保参与排序的 key 值不相同；第二种就是避免使用堆排序，让`order by`根据索引来排序。说白了，就是`order by`后面的字段要有索引。  
另外，使用`JPA`分页查询时，若`order by`的是非索引字段，通过查看`JPA`的sql发现，不会再去自动在`order by`后添加索引或id字段，需要注意。

**MySQL5.7 相关文档：**

*  [https://dev.mysql.com/doc/refman/5.7/en/union-optimization.html][https_dev.mysql.com_doc_refman_5.7_en_union-optimization.html]
 *  [https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html][https_dev.mysql.com_doc_refman_5.7_en_order-by-optimization.html]
 *  [https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html][https_dev.mysql.com_doc_refman_5.7_en_limit-optimization.html]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGF5RGF5VXDkuLY_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20220828/78eebbbef0c64242bb7b54e7f6bce2fa.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGF5RGF5VXDkuLY_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 1]: /images/20220828/582d95c3f80d4828ba3fc653d4618e36.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBARGF5RGF5VXDkuLY_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/7926996e98d4441684f564ecee943eda.png
[https_dev.mysql.com_doc_refman_5.7_en_union-optimization.html]: https://dev.mysql.com/doc/refman/5.7/en/union-optimization.html
[https_dev.mysql.com_doc_refman_5.7_en_order-by-optimization.html]: https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html
[https_dev.mysql.com_doc_refman_5.7_en_limit-optimization.html]: https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html