【MySQL笔记】MySQL有些参数的学习记录-sort_buffer_size

最新推荐文章于 2025-10-15 08:06:13 发布
转载 最新推荐文章于 2025-10-15 08:06:13 发布 · 4.1k 阅读 · 1 ·
CC 4.0 BY-SA版权
原文链接：https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html
文章标签：

#sort_buffer_siz #mysql

本文详细解读sort_buffer_size参数对MySQL ORDER BY性能的影响，提供生产环境下的配置建议，并附带官方文档链接。

 

上干货：关注sort_buffer_size的参数，

sort_buffer_size： 是 MySQL 执行排序使用的缓冲大小。 如果想要增加 ORDER BY 的速度，首先看是否可以让 MySQL 使用索引而不是额外的排序阶段。 如果不能， 可以尝试增加sort_buffer_size 变量的大小。

以上几张图摘自不知名的大佬，以及第一张图是来自mysql5.7参考手册。 以上以上，前人栽树，后人乘凉。

生产一般配置：

[mysqld]

sort_buffer_size=8m

myisam_sort_buffer_size=32m

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

官方文档：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sort_buffer_size

 

==================End====================

MySQL性能优化全攻略－服务器参数调整
ThDebug的博客
10-10 253
根据服务器的可用内存，将这个值设置为一个适当的大小，可以提高查询的性能。根据系统的需求和查询的复杂性，适当地调整这些参数的值，可以提升相关操作的性能。根据系统的需求和可用资源，将其设置为一个适当的值，可以确保不会出现连接被拒绝的情况，并提高并发性能。MySQL是一种广泛使用的关系型数据库管理系统，为了获得最佳的性能，对服务器参数进行适当的调整是非常重要的。根据系统的需求和可用内存，将其设置为一个适当的值，以避免缓存过多的查询结果，导致性能下降。选择适当的存储引擎可以根据应用程序的需求提高性能。
Mysql学习笔记（全）
热门推荐
m0_60264772的博客
12-07 2万+
目录 MyISAM和InnoDB 性能下降SQL慢的原因： Mysql执行顺序 SQLJoin 索引 索引的优劣 索引分类 mysql索引结构 那些情况建索引 哪些情况不要建索引 性能分析 Extra 索引优化 索引口诀 索引案例 Order By 索引优化 exsites Mysql慢查询日志命令 函数操作批量插入数据 show Profile分析sql 全局查询日志 Mysql锁 锁命令 表锁：读锁 表锁：写锁 行锁 事务 行锁测试 间隙锁 行
排序缓冲区调优：sort_buffer_size的合理配置
数据狐（DataFox19970-108-018）
07-06 441
是 MySQL 中用于排序操作的临时缓冲区大小，直接影响ORDER BY和GROUP BY操作的效率。合理配置需结合数据库内存资源、排序数据量及运行状态参数综合调整。‌。
MySQL innodb_buffer_pool_size、join_buffer_size、sort_buffer_size等参数优化
菜鸟学习成功之路
09-04 1156
本文介绍了MySQL几个关键性能参数的优化方法，包括innodb_buffer_pool_size、join_buffer_size、sort_buffer_size、tmp_table_size和table_open_cache等。这些参数直接影响查询性能，合理配置可显著提升SQL执行效率。文章详细说明了每个参数的作用、查看方式、优化建议和修改方法，并提供了实际案例说明参数调整对性能的影响（如join_buffer_size从128KB调至16MB使查询从10秒降至1秒内）。建议根据服务器内存和数据量大小
MySQL 使用 sort_buffer_size 参数来控制每个线程用于排序的内存缓冲区大小。
最新发布
长风破浪会有时的博客
10-15 377
这个参数在排序操作（如ORDER BY或GROUP BY）中起着关键作用，尤其是在没有索引支持时需要进行文件排序（Filesort）的情况下。
MySQL大量写入问题优化方案 MySQL参数调优
xzh_blog
07-21 2447
innodb_flush_log_at_trx_commit是数据库的事务提交设置参数，默认为1。设置为0时，数据写入是最快的，但有可能丢失上1秒的数据。 0：log buffer每隔1s进行刷盘。当机器出现宕机时，最多丢失1s的事务 1：事务提交后立即进行log buffer刷盘。 2：MySQL先将log buffer写入到os buffer，然后每隔1s将os buffer中的数据刷新到磁盘。...
sort_buffer_size:
weixin_30564901的博客
10-13 474
sort_buffer_size: 每个session 必须执行一个排序 分配一个 这个大小的buffer. sort_buffer_size 不特定的对于任何存储引擎适用于一般的优化方式。 sort_buffer_size的最小值 必须足够大 容纳15个元组在sort buffer. 同时, 增加 max_sort_length的值 可能需要增加sort_buffer_si...
mysql buffer size_sort_buffer_size参数--mysql
weixin_28698059的博客
01-25 671
sort_buffer_size是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。官方文档推荐范围为256KB~2MB，这里我们设置为2M。采用mysqlslap进行压力测试测试语句：SELECT SQL_NO_CACHE * FROM...
【MySQL技术资料】-（机构内训资料）MySQL优化学习思维笔记
08-18
5. **内存参数调优**：调整MySQL的内存配置参数，如innodb_buffer_pool_size、sort_buffer_size和query_cache_size等，可以显著提高性能。理解这些参数的意义和计算方法，根据服务器资源合理设置。 6. **日志与性能...
mysql学习笔记
02-09 1063
mysql学习笔记，mysql常用命令，常用语句等
使用docker-compose部署MySQL三主六从半同步集群（MMM架构）_docker-compose mysql集群
2401_84439805的博客
04-21 1678
rpl_semi_sync_master_timeout就是半同步复制时等待应答的最长等待时间，默认是10秒，可以根据情况自行调整。enforce_gtid_consistency = ON # GTID_MODE 和 ENFORCE_GTID_CONSISTENCY 的设置是一致的,强制全局事务标识的一致性。enforce_gtid_consistency = ON # GTID_MODE 和 ENFORCE_GTID_CONSISTENCY 的设置是一致的,强制全局事务标识的一致性。
mysql学习笔记-索引优化与查询优化
weixin_37693463的博客
01-20 444
1.8 is null可以使用索引，is not null无法使用索引。因为classid 使用了范围条件，导致name索引没用上。1.4 计算、函数、类型转换(自动或手动)导致索引失效。不同的字符集进行比较前需要转换，会造成索引失效。1.10 OR前后存在非索引的列，索引失效。= 或者<>)索引失效。1.9 like以通配符%开头索引失效。1.6 范围条件右边的列索引失效。1.5 类型转换导致索引失效。1.1 全值匹配我最爱。1.2 最佳左前缀法则。
Mysql 设置 sort_buffer_size
King的专栏
05-04 2904
你可以考虑增加 sort_buffer_size 来加速 ORDER BY 或者 GROUP BY 操作，不能通过查询或者索引优化的。每个 session 需要做一个排序分配的一个 buffer,sort_buffer_size 不指定任何的存储引擎，只有对需要大量的内存的会话，在 Linux 上，有阀值为 256KB 和 2MB , 大的值可能显著的减慢内存分配，在任何情况下，设置它大于需要的全局会减慢很多的查询。在 MySQL 5.6.4 优化器尝试解决需要多少空间，但可以分配更多，
Mysql sort_buffer_size参数
hbhe0316的专栏
11-27 6461
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sort_buffer_size按照官网的解释:Each session that must perform a sort allocates a buffer of this size. sort_buffer_size is n...
mysql buffer size_MySQL中的sort_buffer_size参数大小的设置问题
weixin_28917279的博客
01-27 2089
看到sort_buffer_size这个参数(connect级别的参数，MySQL5.7，默认值是1048576字节，也就是1MB)的默认值这么小，想着是不是可以调大一点，反正内存动不动几十个GB的，也不在乎这个几MB的。注：笔者尝试修改这个参数的值，在典型的排序(较大的表，排序字段无所索引的条件下)查询中，sort_buffer_size修改前后，性能上似乎并没有明显差异，也就是说默认的配置是够...
MYSQL INNODB_SORT_BUFFER_SIZE 和 SORT BUFFER SIZE 有什么不同如何调整优化
sql server的专栏
10-21 6999
有些应用系统就是要在(MYSQL 5.x)使用ORDER BY ,当然知道MYSQL 在ORDER BY DESC 上有问题的人都会强烈建议,不要在MYSQL上经常使用ORDER BY ...
mysql增加sort_buffer_设置sort_buffer_size
weixin_42452580的博客
01-26 2429
sort_buffer_sizemysql> show variables like ‘%sort_buffer_size%’;+————————-+———+| Variable_name | Value |+————————-+———+| innodb_sort_buffer_size | 1048576 || myisam_sort_buffer_size | 8...
sort buffer size
trigfj的博客
11-18 498
sort buffer size
MySQL 8.0设置sort_buffer_size
mikeguo's blog
07-08 3307
SET sort_buffer_size = 1024*1024 ——————————————————————————————————————————— Java程序报错如下： java.sql.SQLException: Out of sort memory, consider increasing server sort buffer size ———————————————————————————————————————————使用命令查看当前的设置： show variables li
mysql8.0.39 32G服务器sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size设置多大合适
08-03
<think>我们正在为MySQL 8.0.39在32G内存的服务器上配置以下缓冲区参数： - sort_buffer_size - join_buffer_size - read_buffer_size - read_rnd_buffer_size 这些参数都是每个线程（连接）单独分配的，因此设置过大在高并发下可能导致内存耗尽。我们需要在全局设置中找到一个平衡点。 ### 一、参数含义及默认值 1. **sort_buffer_size** - 用于排序操作（如ORDER BY、GROUP BY）的缓冲区大小。 - 默认值：256KB（MySQL 8.0）[^1]。 2. **join_buffer_size** - 用于无法使用索引的JOIN操作（Block Nested Loop）或当优化器选择使用此缓冲区时。 - 默认值：256KB（MySQL 8.0）[^1]。 3. **read_buffer_size** - 用于顺序扫描表时的缓冲区大小（如全表扫描）。 - 默认值：128KB（MySQL 8.0）[^1]。 4. **read_rnd_buffer_size** - 用于按非索引顺序读取行时的缓冲区（如排序后的结果集）。 - 默认值：256KB（MySQL 8.0）[^1]。 ### 二、设置原则 - **全局设置不宜过大**：每个连接都会独立分配这些内存，高并发时总内存消耗为：`参数值 × 最大连接数`。 - **优先优化索引**：这些缓冲区主要用于无法使用索引的操作，优化索引比调大缓冲区更有效。 - **会话级调整**：对于特定需要大缓冲区的查询，在会话中临时设置（如：`SET SESSION sort_buffer_size=2M;`）。 ### 三、推荐配置（32G内存服务器） 根据经验，建议如下（假设最大连接数`max_connections`为200）： 1. **计算总内存约束**： 这些缓冲区总内存应不超过物理内存的10%~15%（防止OOM）。 32GB的10% = 3.2GB ≈ 3200MB。 按200连接计算：每个连接可分配约16MB（3200MB/200）。 2. **具体参数推荐**： - **`sort_buffer_size`**： 通常设置为1MB~4MB。推荐： ```ini sort_buffer_size = 2M # 默认256KB，提升至2MB ``` 理由：排序操作常见，但过大会导致内存碎片[^2]。 - **`join_buffer_size`**： 由于MySQL 8.0优先使用Hash Join，此参数作用降低。推荐： ```ini join_buffer_size = 2M # 默认256KB，提升至2MB ``` 注意：若存在大量无索引JOIN，可临时在会话中调大至4MB~8MB。 - **`read_buffer_size`**： 顺序扫描较少时保持默认，若全表扫描频繁可微增： ```ini read_buffer_size = 1M # 默认128KB，提升至1MB ``` - **`read_rnd_buffer_size`**： 通常与`read_buffer_size`接近，推荐： ```ini read_rnd_buffer_size = 2M # 默认256KB，提升至2MB ``` 3. **验证总内存消耗**： 按200连接计算： - sort_buffer: 2MB × 200 = 400MB - join_buffer: 2MB × 200 = 400MB - read_buffer: 1MB × 200 = 200MB - read_rnd_buffer: 2MB × 200 = 400MB **总计**：1400MB < 3200MB（安全）。 ### 四、关键注意事项 1. **监控实际使用**： 运行后检查状态变量： ```sql SHOW GLOBAL STATUS LIKE '%buffer_size%'; ``` 观察`Sort_merge_passes`（若值大，可适当增加`sort_buffer_size`）[^1]。 2. **优先使用索引**： - 为JOIN字段、WHERE条件字段添加索引。 - 避免全表扫描。 3. **InnoDB缓冲池独立设置**： `innodb_buffer_pool_size`（数据/索引缓存）应占物理内存的70%~80%： ```ini innodb_buffer_pool_size = 24G # 32GB的75% ``` 4. **高并发场景**： 若连接数远大于200，需按比例降低缓冲区大小（如连接数=500，则所有参数减半）。 ### 五、配置文件示例 ```ini [mysqld] # 缓冲区设置 sort_buffer_size = 2M join_buffer_size = 2M read_buffer_size = 1M read_rnd_buffer_size = 2M # InnoDB核心设置 innodb_buffer_pool_size = 24G innodb_log_file_size = 2G # 连接控制 max_connections = 200 ``` ### §§相关问题§§ 1. **如何监控MySQL各缓冲区的实际内存使用情况？** 2. `innodb_buffer_pool_size` 设置过大是否会导致内存浪费？ 3. 当出现大量排序操作时，除了调大`sort_buffer_size`还能如何优化？ 4. MySQL 8.0的Hash Join算法在哪些场景下会失效？ 5. 如何根据`SHOW GLOBAL STATUS`的输出调整缓冲区参数？ [^1]: MySQL官方文档：缓冲区参数定义与监控 [^2]: 《高性能MySQL》内存参数优化章节</think>以下是为 MySQL 8.0.39 在 32GB 内存服务器上的缓冲区参数配置建议，基于最佳实践和内存分配原则： ### 一、核心缓冲区配置建议 | **参数** | **推荐值** | **说明** | |------------------------|------------|--------------------------------------------------------------------------| | `innodb_buffer_pool_size` | 24G | 应占物理内存的70-80%，缓存数据和索引的主区域[^2] | | `sort_buffer_size` | 2-4M | 每个排序线程专用，避免超过8M（过高会导致内存浪费） | | `join_buffer_size` | 2-4M | MySQL 8 优先使用Hash Join，此参数作用降低，会话级按需调整[^1] | | `read_buffer_size` | 1-2M | 顺序扫描缓冲区，全表扫描频繁时可设为2M[^1] | | `read_rnd_buffer_size` | 1-2M | 随机读缓冲区，通常与`read_buffer_size`相当 | > 💡 **总内存计算**： > 24G (InnoDB缓冲池) + (4M+4M+2M+2M)×200连接 ≈ 24G + 2.4G = 26.4G < 32G（安全边界） ### 二、配置文件示例 (`my.cnf`) ```ini [mysqld] # 核心内存配置 innodb_buffer_pool_size = 24G innodb_log_file_size = 2G # 线程级缓冲区设置 sort_buffer_size = 4M join_buffer_size = 4M read_buffer_size = 2M read_rnd_buffer_size = 2M # 连接控制 max_connections = 200 thread_cache_size = 20 # 8.0优化项 optimizer_switch = 'hash_join=on' ``` ### 三、关键配置原则 1. **InnoDB优先** 32G内存中分配24G给`innodb_buffer_pool_size`，确保热点数据内存命中率＞95%[^2] 2. **线程缓冲区节制** ```mermaid graph LR A[单个连接内存需求] --> B((sort_buffer)) A --> C((join_buffer)) A --> D((read_buffer)) A --> E((read_rnd_buffer)) F[200连接] --> G[4M+4M+2M+2M=12M/连接] G --> H[200*12M=2.4G] ``` 总内存消耗需满足： $$24G + (max\_connections \times \sum buffers) < 32G$$ 3. **动态调整策略** ```sql -- 对特定大查询临时调整 SET SESSION sort_buffer_size = 8*1024*1024; SELECT ... ORDER BY ...; ``` ### 四、验证与监控 ```sql /* 检查实际内存分配 */ SHOW VARIABLES LIKE '%buffer_size%'; /* 监控性能指标 */ SHOW STATUS WHERE Variable_name IN ( 'Sort_merge_passes', -- 若＞0需增加sort_buffer 'Handler_read_rnd_next' -- 全表扫描次数 ); ``` ### 五、注意事项 1. 避免全局设置过大缓冲区导致OOM（如设置`join_buffer_size=128M`时，200连接将消耗25.6G） 2. MySQL 8.0的Hash Join在多数场景优于BNLJ，优先保证`optimizer_switch=hash_join=on` 3. 全表扫描频繁时，优化索引比调大`read_buffer_size`更有效 > 📌 **最终建议**：先用推荐值运行1周，根据`SHOW STATUS`指标微调。优先优化查询和索引，其次调整缓冲区。 --- ###
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值