MySQL调优：别再盲目调大join_buffer_size等三个缓冲参数

在MySQL调优的众多参数里join_buffer_size、sort_buffer_size、max_length_for_sort_data这三个名字看起来都跟“缓冲”和“排序”有关实际踩过坑的人都知道它们三个既不是一个级别的东西也不是越大越好。很多朋友接手一台新数据库第一件事就是把三四个buffer参数从默认值调到几十M甚至几百M结果慢查询没少内存倒先告警了。这篇文章就专门把这几个参数的工作方式、适用场景和调优思路一次讲清楚适合刚接触MySQL性能优化、以及在生产环境摸爬滚打过的DBA或后端开发参考。1. 三个参数分别管什么先把场景圈出来1.1 join_buffer_size为“没走索引的关联”兜底很多人第一次看到join_buffer_size以为它是给JOIN操作的通用内存池调大它就能加速所有关联查询——这是最大的误解。这个参数实际上不负责有索引的关联它服务的是那些“关联字段上没有可用索引”的查询典型就是Extra里出现Using join buffer (Block Nested Loop)或Using join buffer (hash join)的场景。MySQL在执行关联时如果内层表比如ordersJOINorder_itemsorder_items就是内层表的关联列上没走索引那就只能用“块嵌套循环”Block Nested Loop简称BNL先把外表的一批行放进join_buffer_size这块内存里然后一次性去扫描内层表做匹配。这样可以减少内层表的扫描次数属于“用内存换IO”的设计。所以这个参数更像一块“暂存区”而不是通用的JOIN加速器。到了MySQL 8.0.18以后官方给等值关联引入了Hash Join这种情况下join_buffer_size又成了哈希表的内存上限。换句话说不管新旧算法这个参数都在为“无索引关联”兜底。一个让新手非常困惑的点是ORDER BY、GROUP BY、UNION在某些条件下也会临时申请这块buffer用来缓存行数据所以你别只看JOIN语句出了排序慢的问题也得留意它。所以结论先放这儿join_buffer_size调大是给“不理想的SQL”做补偿而不是根治手段。真正该做的事是给关联列加上合适的索引让JOIN从Using join buffer变成Using index。1.2 sort_buffer_size会话级排序内存用完即走sort_buffer_size是MySQL里经常被误解成“全局排序缓存”的另一个参数。实际上它是**线程级会话级**的每个连接在执行需要排序的操作时才会临时分配这么一块内存。排序结束或连接断开这块内存就释放了和InnoDB的buffer pool那种全局常驻内存完全不是一回事。什么时候会用到它ORDER BY、GROUP BY、DISTINCT、UNION以及窗口函数8.0开始在无法直接利用索引有序性时都会走filesort这个排序流程。执行计划里Extra出现Using filesort就意味着MySQL要把查询结果先送到这层排序缓冲区里排一遍排不下就落盘到临时文件用归并排序合并。这里有个关键点sort_buffer_size的分配是“按需”的不是连接一建立就给你锁多少内存。我在生产环境里用performance_schema观察过一个普通查询如果没有排序需求这个buffer根本不会被分配。所以设置过大的风险主要体现在大量会话同时做排序操作的时候——比如连接池100个连接同时跑带ORDER BY的报表查询每个连接分到16M那瞬间就是1.6G堆外内存。后面我会给具体排查方法。1.3 max_length_for_sort_data决定排序走“单次”还是“两次”max_length_for_sort_data这个参数的名字很长但它干的活其实很聚焦MySQL在做filesort时需要判断“排序单元”也就是一行要参与排序和返回的数据有多大拿这个大小去和max_length_for_sort_data比较从而决定用哪种排序算法。具体来说如果这一行数据的总长度不超过这个阈值MySQL会用“单次排序”算法读取所有需要的列放进sort_buffer_size在缓冲区里排完序以后直接输出结果不需要再回表取数据。如果一行数据的大小超过这个阈值MySQL就会改用“两次排序”算法第一次只把排序列和主键或rowid放进sort buffer里排排完以后第二次再根据主键回原表读取需要返回的其他列。这个设计的目的很现实——减少sort buffer里单条记录占用的空间让一块bufffer能多存一些排序记录。但代价是后面多了一次回表随机IO。所以max_length_for_sort_data不是越大越好也不是越小越好。它本质上是一条分界线决定你是“多占内存、少回一次表”还是“省着内存用、多跑一次回表”。默认值1024字节在绝大多数业务里已经够用后面我会讲怎么判断要不要动它。2. 底层机制理解这些设计调参才有依据2.1 从Block Nested Loop到Hash Joinjoin_buffer_size是怎么被消耗的要真正理解join_buffer_size得把BNL的执行流程走一遍。假设SQL是这样SELECT o.order_no, i.sku_name FROM orders o JOIN order_items i ON o.order_no i.order_no;如果orders是驱动表外表order_items的order_no没有索引MySQL会从orders里取出一批行放进大小为join_buffer_size的缓冲区中然后扫描order_items全表和缓冲区里的每一行做匹配。这一批处理完再取下一批orders行重复这个过程。这里的关键是join buffer里能装下“越多外表行”内层表被全表扫描的次数就越少。如果buffer只有256KB可能一次只能装下2000行外表记录调大到8MB一次能装下更多行order_items的扫描次数就会显著下降。这也是为什么在无法加索引的前提下调大join_buffer_size确实能直观看到查询时间缩短。但Hash Join的情况不一样。从8.0.18开始对于等值连接MySQL优先使用Hash Join先扫描内层表在join_buffer_size可容纳的范围内构建一个哈希表然后再扫描外层表去探测。如果哈希表膨胀超过了缓冲区限制MySQL会把一部分数据写到磁盘临时文件做成“磁盘哈希连接”。这种情况下join_buffer_size相当于哈希表的内存预算上限。所以你会发现这个参数在不同版本、不同执行计划里扮演的角色完全不同。我给一个非常朴素的经验如果执行计划里看到Using join buffer不要第一时间调参数先看关联列索引在不在索引加不上的极端场景再考虑放大buffer。否则每次都靠buffer扛全表扫描并发一上来内存立刻被打爆。2.2 filesort的两种模式单次扫描和两次扫描的取舍sort_buffer_size负责提供排序空间而max_length_for_sort_data负责决定怎么用这块空间。我实际排查排序慢查询时经常发现max_length_for_sort_data被网上教程改成几万甚至十几万理由是“避免回表”。但真相需要掰开揉碎来讲。假设一张表有8个字段SELECT id, user_name, amount, status ... ORDER BY amount。MySQL先估算排序单元的大小:需要排序的amount列加上查询里要返回的user_name、status等列以及一些变长字段的额外开销VARCHAR的长度标记、NULL标志等。如果总长度估算出来是600字节默认max_length_for_sort_data1024够用那就走“单次排序”把这600字节的记录装进sort buffer排序完成后直接返回结果整个过程中不需要再访问原表。如果这条记录要返回的字段很多比如里面有十几个VARCHAR(255)估算总长度超过1024MySQL会退化成“两次排序”先在sort buffer里只装amount和主键id这种情况下每条排序记录可能只有几十字节同样一块sort buffer能承载更多记录排序完以后再拿着这一批id回聚簇索引取完整行返回给客户端。看起来“两次排序”还要回表一次似乎更慢但现代MySQL选择它是因为它能显著降低排序对内存的需求。很多情况下sort buffer不够而落盘的开销比一次回表严重得多。max_length_for_sort_data这个阈值设得越大单条排序记录就越“胖”sort buffer里能装的行数就越少反而更容易触发磁盘归并排序。这就是我常说的这个参数不是调大就准赢它是用来平衡“内存占用”和“回表IO”的。2.3 为什么参数调大以后某些场景反而更慢我在线上不止一次遇到这种情况把sort_buffer_size从256KB调到64MB结果慢查询没快多少反而出现了内存抖动甚至把整个实例的Swap给拉起来了。原因有三层值得每个调优的人记下来。第一层是并发放大。sort_buffer_size是会话级内存每个连接排序时都会各自分配一份。假设你有50个连接同时在做排序64MB*503.2GB如果这台实例的物理内存总共才16GB还要腾给buffer pool瞬间的分配压力很可能导致操作系统开始Swap性能断崖式下跌。第二层是分配成本。大buffer的分配不可能是免费的尤其当并发高、短查询多的时候频繁申请释放大块内存会让内存碎片化和页面分配的开销变大。别看单条SQL似乎提速了整个实例的稳定性和吞吐反而被拖累。第三层是算法退化。就像前面说的max_length_for_sort_data如果被调得很大sort buffer里每条记录的“体积”变大同样大小的buffer能装的记录数量急剧减少导致一次能排序的行数变少归并合并的次数变多。Sort_merge_passes这个状态值会疯狂上涨排序从内存操作退化成了大量磁盘临时文件读写——速度能不慢吗所以我的调优原则非常明确先用索引消除排序再在索引消除不了的场景里给排序适量的内存兜底而不是靠内存硬抗所有排序。3. 实操调优从监控定位到改参数落地3.1 先看现状用状态变量和performance_schema定位瓶颈调参之前必须搞清楚当前数据库到底是不是被这几个参数卡住了。我一般用一组状态变量快速判断SHOW GLOBAL STATUS LIKE Sort%; SHOW GLOBAL STATUS LIKE Select_full_join; SHOW GLOBAL STATUS LIKE Select_range_check; SHOW GLOBAL STATUS LIKE TeMPTable%;重点关注三个值Sort_merge_passes表示排序过程中因为sort buffer装不下数据需要把中间结果写入临时文件并执行归并合并的次数。这个值如果在持续增长说明sort buffer可能配小了或者排序的数据量太大了。Sort_rows已经排序的行数。这个值本身不代表有问题但可以结合Sort_merge_passes / Sort_rows估算一次排序的落盘比例。Select_full_join没有索引而进行全量JOIN的次数。这个值高说明关联查询大量走了join_buffer这时候优先看索引而不是急着调buffer。如果想看得更细可以用performance_schema观察这两个buffer实际分配了多少内存SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED, CURRENT_NUMBER_OF_BYTES_ALLOCATED FROM performance_schema.memory_summary_by_account_by_event_name WHERE EVENT_NAME LIKE %memory/sql/filesort% OR EVENT_NAME LIKE %memory/sql/join_buffer% ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC;对于join_buffer_size还有个更直接的办法EXPLAIN一条慢连接查询看Extra里有没有Using join buffer (Block Nested Loop)。只要出现这个就说明这个JOIN没走索引这是最明显的信号。3.2 参数设置的正确姿势单位、级别、生效范围很多人在my.cnf里把参数写成join_buffer_size 64M但并不知道这几个参数的生效逻辑。这里列几个关键点它们是动态参数可以用SET GLOBAL或SET SESSION在线修改不需要重启。join_buffer_size在5.7里的默认值是256KB8.0里也是256KBsort_buffer_size默认是256KB5.7和8.0都是。如果只执行了SET GLOBAL已经存在的连接不会立即生效新建立的连接才会继承新的global值。所以线上改完参数后连接池里的老连接可能还在用旧值。这个点特别容易被人忽略尤其在Spring Boot HikariCP这种长连接池里。写完my.cnf并重启是一种更彻底的生效方式但要小心里面如果同时设置了systemd限制的内存等场景。推荐的设置方式是这样# 会话级别临时调大当前连接 SET SESSION sort_buffer_size 1024 * 1024; SET SESSION join_buffer_size 1024 * 1024; # 全局动态生效 SET GLOBAL sort_buffer_size 2 * 1024 * 1024; SET GLOBAL join_buffer_size 2 * 1024 * 1024;如果是改配置文件要写在[mysqld]片段下[mysqld] # join buffer 默认256K8G以上内存的OLTP库一般建议1M-4M join_buffer_size 2M # 排序缓冲同理不建议一上来就几十M sort_buffer_size 2M # 这个值默认1024就够绝大多数场景不要动 max_length_for_sort_data 10243.3 一个订单统计慢查询的完整调优过程还是用实际案例说话。某次线上有个报表查询慢SQL简写如下SELECT o.user_id, SUM(oi.amount) AS total_amount FROM orders o JOIN order_items oi ON o.order_no oi.order_no WHERE o.created_at 2025-01-01 00:00:00 AND o.created_at 2025-06-01 00:00:00 GROUP BY o.user_id ORDER BY total_amount DESC LIMIT 100;orders约1200万行order_items约3500万行。EXPLAIN的结果里order_items的order_no没有索引Extra直接显示Using join buffer (Block Nested Loop)而且最终的Using temporary; Using filesort也点明了排序不轻松。接手这台库的时候前一位同事已经把它调成了join_buffer_size64M、sort_buffer_size64M结果查询还是要2秒多。我先看状态变量Sort_merge_passes在持续上涨内存压力也不小。我的处理顺序是这样的第一给order_items.order_no加索引ALTER TABLE order_items ADD INDEX idx_order_no (order_no);这一步做完JOIN从Using join buffer变成了Using index conditionSelect_full_join不再跳。其实这一项才是核心内存再大也替代不了索引带来的性能收益。第二把join_buffer_size从64M调回2M。因为索引加完之后大部分连接不再依赖join buffer保留64M只是白白占内存。第三sort_buffer_size从64M调成2M保留一个比默认大一点的缓冲值应对GROUP BY和ORDER BY带来的filesort。去掉64M的“核弹配置”后实例内存压力明显下降。调完以后这个查询从2.1秒降到了0.3秒左右而且不是靠压榨内存换来的是索引结构真正解决了扫描问题。这个案例告诉我一个道理buffer参数是辅助手段SQL结构和索引设计才是一等公民。4. 常见问题与排查技巧实录4.1 明明改了参数为什么排序还是慢最常见的“改完没效果”发生在三种情况里第一种改的是GLOBAL但长连接池里的连接没有重连。我用HikariCP的时候踩过连接池里大部分连接还是旧值。解决方法是改完SET GLOBAL之后重启应用或强制刷新连接池或者直接在配置文件里改后重启MySQL。第二种SQL本身没有触发filesort。比如ORDER BY的字段和索引顺序一致MySQL走了索引的有序性根本不分配sort buffer。这时候你去调sort_buffer_size就是白费功夫因为瓶颈根本不在排序。用EXPLAIN看一眼Extra有没有Using filesort就明白了。第三种排序确实落盘了但磁盘IO才是瓶颈。即使sort_buffer_size已经很大如果排序的数据量超过内存临时文件写入依然存在。Sort_merge_passes告诉你归并次数而SHOW VARIABLES LIKE tmpdir告诉你临时文件写到了哪里。很多时候把tmpdir从机械盘换到SSD比盲目调大sort buffer更有效。4.2 内存突增、间歇性Swap的排查路径如果你的实例最近经常内存告警free -h能看到Swap占用升高别再只盯着buffer pool了很可能就是这几个buffer被放得太大。排查思路可以这样第一步看当前的并发连接数SHOW STATUS LIKE Threads_connected;如果这个数长期在200以上假设join_buffer_size16M且大量连接都在做无索引JOIN理论上最多可能吃满3.2GB内存。第二步用performance_schema看内存实际使用SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED FROM performance_schema.memory_summary_by_global_by_event_name WHERE EVENT_NAME IN (memory/sql/filesort, memory/sql/join_buffer);如果看到的分配值很大再对应到具体连接或账号确认是哪些查询在吃内存。第三步审视慢日志里是否有大量“短查询但频繁排序”的语句。常见场景是后台定时任务、报表系统在整点瞬间并发执行复杂查询buffer一旦被放大内存峰值就特别吓人。这种情况下我会把buffer先缩到合理范围然后对慢周期任务做分批或错峰处理。4.3 不同业务场景下的推荐值速查表根据我个人的线上经验给几类常见的场景提供一个参考起点注意这是起点不是终点。单位都是字节。场景join_buffer_sizesort_buffer_sizemax_length_for_sort_data备注小型OLTP内存8G以下256K-1M256K-1M1024默认优先加索引尽量少依赖buffer中型OLTP内存16-32G1M-4M1M-4M1024短查询多别给太大有较多JOIN报表内存32G4M-8M4M-8M1024强烈建议先优化SQL别靠buffer硬扛大量离线排序/统计任务8M-16M8M-16M1024-2048建议做任务错峰防止并发放大内存我自己在线上环境里的习惯是先看索引有没有到位、执行计划里的Using filesort和Using join buffer到底出现在哪一步再动参数。宁可把内存留给InnoDB buffer pool也不在sort_buffer_size上赌一把大的。参数调优从来不是抄一个配置文件过来而是给慢查询找到它真正缺的东西。最后再分享一个小技巧改完这几个参数以后过半小时回来重新看一眼Sort_merge_passes和Select_full_join的增长趋势。如果这两个值还在缓慢上涨说明SQL侧的问题没根治排序和关联的内存兜底仍然在起作用如果这两个值基本平稳说明你这次调整的方向大概率是对的。