MySQL 8.0.33选择JSON列时出错：排序内存不足，请考虑增加服务器排序缓冲区大小。

Question

MySQL 8.0.33选择JSON列时出错：排序内存不足，请考虑增加服务器排序缓冲区大小。

3

我有一个带有JSON列的表格。我想在JSON列中选择特定的数据。我的查询在MySQL 5.7（Ubuntu 16.04）上运行得很好，但在MySQL 8.0.33（Ubuntu 22.04）上不起作用：

查询：

select json_unquote(json_extract(`campaigns`.`model`, '$.\"migration\"')) as `migration` 
from `campaigns` 
order by `created_at` desc 
limit 10 offset 0;

错误：

排序内存不足，请考虑增加服务器排序缓冲区大小

sort_buffer_size变量当前值为：
SHOW global variables LIKE 'sort_buffer_size';
结果：262144

我尝试增加sort_buffer_size并重启服务器，但没有成功。

这是mysql 8的一个错误吗？我应该用更优化的形式重新编写我的查询吗？

谢谢

- superfive33

2

奇怪的是，8.0.30+版本应该有额外的JSON支持，但之前并没有。 - undefined

1

这也是我在其他问题上读到的。但是我仍然遇到了错误/bug！ - undefined

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bill Karwin · Accepted Answer

这是一个小错误，是MySQL 8.0.20版本中的一项改进所导致的后果。详见https://bugs.mysql.com/bug.php?id=103225。

最好的解决方法是通过在created_at列上创建索引来优化查询排序。如果查询按照索引顺序读取行，即与您想要的顺序相同，那么就不需要使用排序缓冲区。

如果无法创建该索引，MySQL必须以其他顺序（可能是主键顺序）读取行，并且必须对行进行分批排序。这些批次存储在内存中的排序缓冲区中，而排序缓冲区的大小必须足够大以容纳多个行。默认的排序缓冲区大小相当适中，正如您发现的256KB。

您提到过尝试增加它，但如果您的JSON文档很大，您将不得不大幅增加排序缓冲区的大小。我猜测应该是该表中存储的最大JSON文档的10-20倍。

您可以检查您最大的JSON文档有多大（至少是当前存储在表中的文档）。

SELECT MAX(JSON_STORAGE_SIZE(model)) FROM campaigns;

这是来自MySQL发布说明关于此更改的解释： https://dev.mysql.com/doc/relnotes/mysql/8.0/zh/news-8-0-20.html说：

以前，将大于 TINYBLOB 或 BLOB 的 blob 类型列作为排序操作中的有效负载时，服务器会改为仅对行 ID 进行排序，而不是完整的行；这导致在排序完成后需要进行第二次读取磁盘获取行数据。由于 JSON 和 GEOMETRY 列在内部被实现为 LONGBLOB，即使它们通常比 LONGBLOB 的 4GB 上限（甚至是 MEDIUMBLOB 的 16MB 上限）要短得多，但这些类型的列也会出现相同的行为。现在，服务器在这种情况下将这些类型的列转换为打包方式的附加列，就像处理 TINYBLOB 和 BLOB 列一样，经测试显示性能显著提高。但是，对于 MEDIUMBLOB 和 LONGBLOB 列的处理保持不变。

这个增强的一个影响是，如果排序缓冲区大小不足，当尝试对包含非常大（多兆字节）的 JSON 或 GEOMETRY 列值的行进行排序时，可能会发生 Out of memory 错误；通常可以通过增加 sort_buffer_size 系统变量的值来进行补偿。

在我看来，这是我们应该谨慎并且对于在MySQL中使用JSON持有犹豫态度的众多原因之一。尽可能使用普通的行和列。