关于mysql:不懂Mysql排序的特性加班到12点认了认了

小弟新写了一个性能,自测和测试环境测试都没问题,但在生产环境会呈现偶发问题。于是,加班到12点始终排查问题,终于定位了的问题起因:Mysql Limit查问优化导致。现形象出问题模型及解决方案,剖析给大家,防止大家踩坑。

问题场景

新上线一个交易记录导出性能,逻辑很简略:依据查问条件,导出对应的数据。因为数据量比拟大,在查询数据库时采纳了分页查问,每次查问1000条数据。

自测失常,测试环境失常,上线之后经营反馈导出的数据有重复记录

本来是认为业务逻辑问题,从新Review了一遍代码,仍旧未找到问题起因。最初只好把SQL语句拿进去独自执行,导出数据,比照发现居然是SQL语句查问后果乱序导致的。

起因剖析

查问语句以create_time进行倒序排序,通过limit进行分页,在失常状况下不会呈现问题。但当业务并发量比拟大,导致create_time存在大量雷同值时,再基于limit进行分页,就会呈现乱序问题。

呈现的场景是:以create_time排序,当create_time存在雷同值,通过limit分页,导致分页数据乱序

比方,查问1000条数据,其中有一批create_time记录值都为”2021-10-28 12:12:12“,当创立工夫雷同的这些数据,一部分呈现在第一页,一部分呈现在第二页,在查问第二页的数据时,可能会呈现第一页曾经查过的数据。

也就是说,数据会来回跳动,一会儿呈现在第一页,一会儿呈现在第二页,这就导致导出的数据一部分反复,一部分缺失。

查看了Mysql 5.7和8.0的官网文档,形容如下:

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.

上述内容概述:在应用ORDER BY对列进行排序时,如果对应(ORDER BY的列)列存在多行雷同数据,(Mysql)服务器会依照任意程序返回这些行,并且可能会依据整体执行打算以不同的形式返回

简略来说就是:ORDER BY查问的数据,如果ORDER BY列存在多行雷同数据,Mysql会随机返回。这就会导致尽管应用了排序,但也会产生乱序的情况。

解决方案

针对上述问题,根本的解决思路是:防止ORDER BY列的值呈现反复。因而,能够退出其余维度,比方ID等其余排序列。

select * from tb_order order by create_time ,id desc;

这样,在create_time雷同时,会依据id进行排序,而id必定是不同的,就再不会呈现上述问题了。

拓展常识

其实,上述内容在Mysql的官网曾经有明确阐明,而且还举了例子。上面对官网的内容和例子做一个简略的汇总总结。

limit查问优化

如果咱们只是查问一个后果集的一部分,那么不要查问所有数据,而后再抛弃不须要的数据,而是要通过limit条件来进行限度。

在没应用having条件时,Mysql可能会对limit条件优化:

  • 如果只查问几条数据,倡议应用limit,这样Mysql可能会用到索引,而通常状况下Mysql是全表扫描;
  • 如果将limit row_countorder by联合应用,Mysql会在找到第一个row_count后果集后立即进行排序,而不是对整个后果集进行排序。如果此时基于索引进行操作,速度会更快。如果必须进行文件排序,在找到row_count后果集之前,会对局部或所有符合条件的后果进行排序。但当找到row_count后果之后,便不会对残余局部进行排序了。这种个性的一个体现就是咱们后面提到的带有limit和不带limit进行查问时,返回的后果程序可能不同。
  • 如果将limit row_countdistinct联合应用,Mysql会在找到row_count后果集惟一行后立马进行。
  • 在某些状况下,能够通过依照程序读取索引(或对索引进行排序),而后计算摘要直到索引变动来实现group by。在这种状况下,limit row_count不会计算任何不必要的group by值。
  • 一旦MySQL向客户端发送了所需数量的行,就会停止查问,除非应用了SQL_CALC_FOUND_ROWS。在这种状况下,能够应用 SELECT FOUND_ROWS() 检索行数。
  • LIMIT 0会疾速返回一个空集合,通常可用于查看SQL的有效性。还能够用于在应用程序中取得后果集的类型。在Mysql客户端中,能够应用--column-type-info来显示后果列类型。
  • 如果应用长期表来解析查问,Mysql会应用 limit row_count来计算须要多少空间。
  • 如果order by未应用索引,且存在limit 条件,则优化器可能会防止应用合并文件,而采纳内存filesort操作对内存中的行进行排序。

理解了limit的一些个性,上面再回到本文的重点,limit row_countorder by联合应用个性。

limit与order by联合应用

在下面第二条中曾经提到,limit row_countorder by联合出现的个性之一就是后果返回的程序是不确定的。而影响执行打算的一个因素就是limit,因而带有limit与不带有limit执行同样的查问语句,返回后果的程序可能不同。

上面示例中,依据category列进行排序查问,而id和rating是不确定的:

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

当查问语句蕴含limit时,可能会影响到category值雷同的数据:

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

其中id为3和4的后果地位产生了变动。

在实践中,放弃查问后果的程序性往往十分重要,此时就须要引入其余列来保障后果的程序性了。当上述实例引入id之后,查问语句及后果如下:

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+

能够看出,当增加了id列的排序,即便category雷同,也不会呈现乱序问题。这正与咱们最后的解决方案统一。

小结

原本通过实际中偶发的一个坑,聊到了Mysql对limit查问语句的优化,同时提供了解决方案,即满足了业务需要,又防止了业务逻辑的谬误。

很多敌人都在应用order by和limit语句进行查问,但如果不晓得Mysql的这些优化个性,很可能曾经入坑,只不过数据量没有触发出现而已。

如果这篇文章帮到你了,关注一波,后续更多实战干货分享。

Mysql官网文档:https://dev.mysql.com/doc/ref…

博主简介:《SpringBoot技术底细》技术图书作者,热爱钻研技术,写技术干货文章。

公众号:「程序新视界」,博主的公众号,欢送关注~

技术交换:请分割博主微信号:zhuan2quan

【腾讯云】轻量 2核2G4M,首年65元

阿里云限时活动-云数据库 RDS MySQL  1核2G配置 1.88/月 速抢

本文由乐趣区整理发布,转载请注明出处,谢谢。

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据