MySQL: Out of sort memory问题的解决

问题

用如下简单的sql进行mysql查询时，发现了出现了out of memory结果。

SELECT * FROM `tb_survey` WHERE survey_id = 'xx' AND (game_id = '10000' AND area_id = 'asia') ORDER BY page_no, block_no

Error 1038 (HY001): Out of sort memory, consider increasing server sort buffer size。

排查

当我把order by排序去掉，语句就正常返回了，这说明是排序的时候出了问题。

询问了chatgpt表示是sort_buffer_size设置过小的原因，所以我就看了下自己的sort_buffer_size大小：

大概也有个800多KB了。查看了表格定义(如下所示)，非常简单，而且整个表只有8条数据啊，排序怎么会出现内存溢出的问题呢？

CREATE TABLE `tb_survey_block` (
  `block_id` varchar(64) NOT NULL,
  `survey_id` varchar(64) NOT NULL DEFAULT '0',
  `page_no` int(11) NOT NULL DEFAULT '0' COMMENT '页码',
  `block_no` int(11) NOT NULL DEFAULT '0' COMMENT '问题序号',
  `block_data` JSON NOT NULL COMMENT '区块数据, 使用json格式',
  ...
  PRIMARY KEY (`block_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='问卷调查问题定义表';

带着疑问从网上找了一些资料，原来为了确保排序更加高效，自从mysql8.0.20版本开始，在进行排序的时候，会将类型JSON字段也带到内存中，假如sort buffer size过小，就会导致mysql直接爆出out of sort memory错误。

官方文献如下所示：

Previously, including any column of a blob type larger than TINYBLOB or BLOB as the payload in an ordering operation caused the server to revert to sorting row IDs only, rather than complete rows; this resulted in a second pass to fetch the rows themselves from disk after the sort was completed. Since JSON and GEOMETRY columns are implemented internally as LONGBLOB, this caused the same behavior with these types of columns even though they are almost always much shorter than the 4GB maximum for LONGBLOB (or even the 16 MB maximum for MEDIUMBLOB). The server now converts columns of these types into packed addons in such cases, just as it does TINYBLOB and BLOB columns, which in testing showed a significant performance increase. The handling of MEDIUMBLOB and LONGBLOB columns in this regard remains unchanged.

One effect of this enhancement is that it is now possible for Out of memory errors to occur when trying to sort rows containing very large (multi-megabtye) JSON or GEOMETRY column values if the sort buffers are of insufficient size; this can be compensated for in the usual fashion by increasing the value of the sort_buffer_size system variable.

上面这段话解释是，在之前，如果在排序操作中包含任何大于TINYBLOB或BLOB的blob类型列作为负载，服务器会回退到仅排序行ID，而不是完整的行；这导致在排序完成后需要进行第二次传递，从磁盘中获取行本身。由于JSON和GEOMETRY列在内部实现为LONGBLOB，即使它的大小很小，也会导致这种行为。现在，服务器在这种情况下会将这些JSON类型的列转换为更有效率的格式，使得排序和检索操作更加快速(极有可能是将json数据也读取到了sort buffer中，这种改变可能还减少了对磁盘的读取次数，可以在排序操作中直接处理更多的数据)。

而这个优化就会导致如果排序缓冲区的大小不足，当试图排序包含非常大（多兆字节）的JSON或GEOMETRY列值的行时，可能会出现内存不足的错误。

这就是为什么即使请求的是少量的数据，当数据中包含大容量的json字段，且请求语句中又包含了排序逻辑，就会出现sort memory溢出的问题了。

发现了出现这个问题的原因， 那么解决方案就呼之欲出了。

解决方法

总结上述内容，解决这个问题的手段有如下两种：

1,  为排序和查找字段添加合适的索引。

通过index排序时，只会将order字段读到内存排序，并不会加载其他查询数据。等到排序完成了，才会通过查找到的主键id或唯一键回表获取数据。添加下述索引后，sort buffer溢出的问题就没出现过了。

ALTER TABLE tb_survey_block ADD INDEX survey_order (`survey_id`, `game_id`, `area_id`,`page_no`,`block_no`);

2，增大mysql的sort_buffer_size的设置值。

如何判定sort buffer size是否过小呢？目前还在找具体的公式，但好像官方并未明说。询问了chatgpt和腾讯云助手也没有得到答案。我自己查了下这个表中json字段的最大的尺寸，发现明显是比我的sort buffer size要大的。

那至少设置sort buffer size要大于这个值，才能保证buffer可以装的下这一行包含大容量json的数据才行。后面我将sort buffer size设置为了16MB，这个问题也没有再出现了。

参考文献

MySQL Bugs: #103225: "Out of sort memory error" has an inconsistent relationship with the buffer size

MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.20 (2020-04-27, General Availability)

sorting - MySQL 8.0.33 error when selecting json column : Out of sort memory, consider increasing server sort buffer size - Stack Overflow