开发者

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

目录
  • 一、前言
  • 二、问题列举
    • 2.1 子查询中不能使用order by
    • 2.2 子查询order by无效
    • 2.3 排序条件不够严格导致分页数据重复
  • 总结

    一、前言

    分页查询的需求如同家常便饭,多数情况下主要利用order bylimit即可实现,有些稍复杂一点的可能需要用到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
    

    其结果为:

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

    若子查询中包含order by,写法假设如下:

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

    则语法报开发者_MsSql错: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)
    

    其结果为:

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

    语法没问题了,但是注意到子查询的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)
    

    其结果为:

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

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

    该问题与union无直接联系,属于order bylimit本身的注意点。

    即,如果SQL中的order by条件比较宽松不够严格,或者说是结果集中的每行记录存在并列或不唯一的次序的话,MySQL可能会随机给并列记录行进行排序,特别是排序又分页查询配合了limandroidit操作,可能会导致上一页有的记录行又出现在了下一页的结果集之OeGGqX中。

    对一些后台系统的分页表格中可能感觉不明显,但对于同样SQL查询逻辑的C端下拉列表的效果一目了然,特别是对带有图片的,很容易看到数据的重复。

    解决方案:

    所以在做分页查询时,要尽量保证每行记录都有唯一确定的次序,具体做法可以在原有排序条件后添加id或编号等这类唯一值的字段(索引字段也可以)。

    例如,在艺术家artist创建时间降序排序的基础上,再对唯一code进行排序:

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

    原因分析:

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

    • order by不能使用索引进行排序时,将使用排序算法进行排序;
    • 若排序内容能全部放入内存,则仅在内存中使用快速排序;
    • 若排序内容不能全部放入内存,则分批次将排好序的内容放入文件,然后将多个文件进行归并排序;
    • 若排序中包含limit语句,则使用堆排序(不稳定)优化排序过程。

    所以解决排序分页数据重复问题有两种方式,第一种就是,在排序中加上唯一值,比如主键 id,这样由于 id 是唯一的,就能确保参与排序的 key 值不相同;第二种就是避免使用堆排序,让order by根据索引来排序。

    说白了,就是order by后面的字段要有索引。

    另外,使用JPA分页查询http://www.devze.com时,若order by的是非索引字段,通过查看JPA的sql发现,不会再去自动在javascriptorder by后添加索引或id字段,需要注意。

    MySQL5.7 相关文档:

    https://dev.mysql.com/doc/refman/5.7/en/union.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

    总结

    以上为个人经验,希望能给大编程客栈家一个参考,也希望大家多多支持我们。

    0

    上一篇:

    下一篇:

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    最新数据库

    数据库排行榜