前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL排序速度慢而且可能不稳定

MySQL排序速度慢而且可能不稳定

作者头像
普通程序员
发布2019-11-12 11:06:10
2.3K0
发布2019-11-12 11:06:10
举报
文章被收录于专栏:普通程序员

一、具体现象

有一个功能,按照算法得出的权重值,分页展示一批列表数据,权重值越大越靠前。研发同学反馈查询速度慢且排序不稳定。

排序不稳定的具体现象,有不少记录存在相同权重值,某条记录(假设id=100)第一页出现了,翻到第二页可能还有它(采用的limit控制哪一页)。

第1页数据

第2页数据

一个主表A,左连接两个表B、C,根据C的权重字段排序。具体SQL如下

二、问题分析

查看SQL语句的执行计划(EXPLAIN),发现有Using filesort的字样。

赶紧搜索一下MySQL说明文档,第一条是排序优化

文档中有这么一句话“如果索引不能满足ORDERBY子句,MySQL将执行文件排序(filesort)操作,读取数据行并对其进行排序。文件排序构成查询执行中的额外排序阶段。”

显然,利用索引实现有序,比采用filesort更高效。filesort并不一定都通过磁盘排序,数据量不大的时候是在内存里完成。速度不够快的原因找到了。

filesort的时候可能在内存中出现堆排序列或快速排序两种方式,具体使用哪一种排序方式是优化器决定的,基本原则如下

  • 快速排序算法:大量排序
  • 堆排序算法:排序量不大

快速排序和堆排序是不稳定的排序算法,对于重复值不能保证顺序。Order by排序不稳定的原因也定位到了

了解一下filesort的原理

(1)根据表的索引或者全表扫描,读取所有满足条件的记录。

(2)对于每一行,存储一对值到缓冲区(排序列,行记录指针),一个是排序的索引列的值,即order by用到的列值,和指向该行数据的行指针,缓冲区的大小为sort_buffer_size大小。

(3)当缓冲区满后,运行一个快速排序(qsort)来将缓冲区中数据排序,并将排序完的数据存储到一个临时文件,并保存一个存储块的指针,当然如果缓冲区不满,则不会重建临时文件了。

(4)重复以上步骤,直到将所有行读完,并建立相应的有序的临时文件。

(5)对块级进行排序,这个类似归并排序算法,只通过两个临时文件的指针来不断交换数据,最终达到两个文件,都是有序的。

(6)重复5直到所有的数据都排序完毕。

(7)采取顺序读的方式,将每行数据读入内存(这里读取数据时并不是一行一行读),并取出数据传到客户端,读取缓存大小由read_rnd_buffer_size来指定。

三、怎么优化

1、利用索引达到排序目的(针对例子的优化)

针对文章开始的例子,优化原则是Use of Indexes to Satisfy ORDER BY(让ORDER BY用上索引),即提升查询效率,又保证稳定性(索引B+树叶子结点的顺序是唯一且一定的)

MySQL的文档列出若干具体的case,把最主要整理出来如下。

MySQL文档中有这么一句话 “该查询连接了许多表,并且ORDER BY中的列并非全部来自用于检索行的第一个非恒定表。”,满足这类型的SQL也不能利用索引排序。这就是文章开头的例子。另外,使用别名,如果跟表的列名冲突可能导致索引排序失效。

看到有些文章写到下面这条语句ORDER BY不能利用索引

这个说法显然与MySQL官方文档不一致。我觉得,这个语句能不能使用索引,跟数据库引擎根据开销决定是否检索的阶段使用索引有关。

2、优化filesort

如果确实没办法利用索引,可以想办法优化filesort排序。

如果结果集太大内存装不下,filesort将根据需要使用临时磁盘文件。磁盘io速度你懂的!MySQL官方建议可以调大排序缓存参数sort_buffer_size,MySQL 8.0还对缓存利用率做了优化,调大一点也不浪费。以前版本的MySQL可以求助DBA。

可以这样优化的典型SQL 语句如下

3、其他

有些ORDER BY甚至连filesort都不能用,对这类优化感觉有点超纲了,把原文贴一下

“对于不使用filesort的慢排序查询,请尝试将“max_length_for_sort_data”参数调低到适合触发filesort的值(此参数的值设置得过高的一个表现是磁盘IO高和CPU负载低)”

四、实际效果

文章开头例子的一种场景,我们巧妙利用了索引排序,达到很好的效果。

另外一个场景仍然使用filesort的排序方式

当然更好的做法是接入ES之类的搜索引擎

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-11-07,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 普通程序员 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档