MySQL查询常见问题

ronshi

已于 2023-05-04 19:36:33 修改

阅读量300

点赞数

分类专栏：数据治理文章标签： sql 数据库

于 2023-03-18 17:31:14 首次发布

本文链接：https://blog.youkuaiyun.com/ronshi/article/details/129633447

版权

数据治理专栏收录该内容

10 篇文章

订阅专栏

一、先排序后分组

1、MySQL版本5.6

首先order by然后group by

SELECT
    id,
    chat_id,
    content,
    create_time 
FROM
    ( SELECT id, chat_id, content, create_time FROM im_chat_message ORDER BY create_time DESC ) a 
GROUP BY
    chat_id 
ORDER BY
    create_time DESC

2、MySQL版本5.7

MySQL5.7对子查询进行了优化，认为子查询中的order by可以进行忽略，只要Derived table里不包含如下条件就可以进行优化：UNION clause、GROUP BY、DISTINCT、Aggregation、LIMIT or OFFSET

SELECT
    id,
    chat_id,
    content,
    create_time 
FROM
    ( SELECT id, chat_id, content, create_time FROM im_chat_message ORDER BY create_time DESC LIMIT 999999999 ) a 
GROUP BY
    chat_id 
ORDER BY
    create_time DESC

3、MySQL版本>=8.0

采用 ROW_NUMBER() over ( PARTITION BY chat_id ORDER BY create_time DESC )

SELECT
    id,
    chat_id,
    content,
    create_time 
FROM
    (
    SELECT
        id,
        chat_id,
        content,
        create_time,
        ROW_NUMBER() over ( PARTITION BY chat_id ORDER BY create_time DESC ) AS rn 
    FROM
        im_chat_message 
    ORDER BY
        create_time DESC 
    ) a 
WHERE
    rn =1

二、order by limit导致分页出现重复数据

从 MySQL 5.6 版本开始，优化器在使用 order by limit 时做了优化，导致排序字段没有使用索引时使用堆排序。堆排序是不稳定的，多次排序后，各个数的相对位置发生了变化。

解决措施有两种方式：

1、排序字段加上索引。

2、在 order by 排序字段里，添加有索引的字段，比如主键ID。在排序时可以保证顺序稳定。

三、新增数据导致分页出现重复数据

新增数据导致数据总量发生了变化。解决措施如下：

查询出当页数据后，记录本次拉取位置。下次请求时，将上次获取的拉取位置传给后端，后端从该拉取位置开始分页。

 WHERE
   1 =1
   <if test="null != query.minId">
      and id &lt; #{query.minId}
   </if>
 LIMIT #{query.size}