前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MYSQL INNODB_SORT_BUFFER_SIZE 和 SORT BUFFER SIZE 有什么不同如何调整优化

MYSQL INNODB_SORT_BUFFER_SIZE 和 SORT BUFFER SIZE 有什么不同如何调整优化

作者头像
AustinDatabases
发布2020-10-30 10:31:10
2.9K0
发布2020-10-30 10:31:10
举报
文章被收录于专栏:AustinDatabases

有些应用系统就是要在(MYSQL 5.x)使用ORDER BY ,当然知道MYSQL 在ORDER BY DESC 上有问题的人都会强烈建议,不要在MYSQL上经常使用ORDER BY ,同时使用GROUP BY 的时候后面还建议带一个 order by null.

MYSQL 上的配置参数中带有 innodb_sort_buffer_size ,Sort_buffer_size 两个参数的意义有什么不同.

Sort_buffer_size 针对的是每一个SESSION连接,他并不是仅仅服务于INNODB数据库引擎的,他是对于数据提取后的一种排序方式的优化.

这里MYSQL 中SORT BUFFER SIZE 的配置如果要生效的情况下,必须你给的最小值能容纳至少15行数据,如果你的SORT BUFFER SIZE 设置的过小则无法对你的系统提取数据时的排序生效.

当然这里又的引出另一个MYSQL的系统参数MAX_SORT_LENGTH,到底这两个参数sort buffer size 和 max_sort_length 之间有什么关系,我们画一个图.

MAX_SORT_LENGTH 仅仅锁定排序中最大支持的一行的字节数, SORT_BUFFER_SIZE 设定的是整体我们给排序中多少容量的字节来支持整体的排序, 大白话, SORT_BUFFER_SIZE 支持的是整体, MAX_SORT_LENGTH支持的每一行的"人设". 如果SORT_BUFFER_SIZE是固定 MAX_SORT_LENGTH 越大,则支持的容纳的行数就越少.

那么反过来,innodb-sort-buffer-size到底是一个什么鬼, 这里的innodb_sort_buffer_size 是指对数据插入时,针对数据写入内存,排序后,在一次写入到磁盘的缓冲区的大小. 实际上innodb_sort_buffer_size 本身和查询无关,和DML 操作有关,如果系统上的表有索引的情况下,并且UPDATE, INSERT数据频繁,则 innodb_sort_buffer_size 可以提高数据的写入索引的速度.

那么问题来了,平时MYSQL 中的语句都写的还是不错的,很少使用order by语句,可需求变化了,这边需要进行排序,那我要不要动我原来的sort_buffer_size的设置.

这个问题可以通过对某些语句的修改,来达到临时对这个语句对于sort_buffer_size的扩大化使用.

如 select /*+ SET_VAR(sort_buffer_size = 10M) */ host,user,max_connections from mysql.user order by user desc;

通过这样的方式来在执行这条语句的时候,改变这条语句使用sort_buffer_size的大小.

另外从MYSQL 8.012 后对这个sort_buffer_size 有了相关的优化,对于order by中排序的字段没有索引的情况下,肯定是更多的内存的切入会让查询返回结果的速度更快. 优化的点是 sort_buffer_size 的分配是一点点的来分配内存来适应需要,直到到指定的最大值.

这样的主要的目的是,如果我们指定一个比较大的值来使用,会浪费内存空间,而优化后,则可以在预先设置一个比较大的值,而不必担心浪费内存.

注意: 此优化在WINDOWS 版本的MYSQL 无效

另外在MYSQL 5.X 上还有一个说法是SORT BUFFER SIZE 不要超过 2MB ,否则达不到预想的效果,会让系统的性能变得更糟. (当然这个需要斟酌,如果你见过一些设计的比较"惨"的系统,一个劲的使用ORDER BY DESC 而且数据量还不小的情况下).

最后借用某篇文章中的对于MAX_SORT_LENGTH,调整的后的对于ORDER BY的执行的时间的变化,列一个表. 这里的sort_buffer_size 的配置是20MB.

当然实际上这个要灵活掌握,例如如果需要排序的数据 前面字头的数据就很容易能进行排序,则越小的 MAX_SORT_LENGTH 越好,反过来,如果你的数据排序的字段,前面大致相同,需要后面的数据来进行排序的区分,那MAX_SORT_LENGHT 大一些,同时将 SORT_BUFFER_SIZE 也需要提高.

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

本文分享自 AustinDatabases 微信公众号,前往查看

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

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

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