错误"1038：排序内存不足，请考虑增加排序缓冲区大小

Question

在Symfony2和Doctrine2中，我有一个查询会触发错误：

Error "1038 Out of sort memory, consider increasing server sort buffer size

查询:

$queryBuilder = $this
    ->createQueryBuilder('object')
    ->leftJoin('object.objectCategory', 'c')
    ->leftJoin('object.medias', 'm')
    ->leftJoin('object.recipients', 'r')
    ->leftJoin('object.answers', 'a')
    ->leftJoin('object.tags', 't')
    ->leftJoin('object.user', 'u')
    ->leftJoin('object.votes', 'v')
    ->leftJoin('object.comments', 'comments')
    ->leftJoin('v.user', 'vuser')
    ->addSelect('c, t, v, u')
    ->groupBy('object, c, t, v, u')
    ->where('object.isVisible = :isVisible')
    ->orderBy('object.createdAt', 'DESC')
    ->setParameter('isVisible', true)
    ->addSelect('SUM(v.value) AS HIDDEN vote_value')
    ->orderBy('vote_value', 'DESC')
    ;

如果我省略group by，它就可以正常运行。 如果我添加select和group by的元素较少，它也可以正常运行，但是在我的twig模板中会启动更多的子查询。

如何优化此查询以避免错误，或通过分配更多内存（理想情况下仅针对此查询）来消除错误？

- Sébastien

---

我通过将用户从“addselect”中删除来解决了这个问题。它不会影响查询数量。下面的答案也可以。 - Sébastien

Answer 1

只需运行MySQL查询

SET GLOBAL sort_buffer_size = 256000000 // It'll reset after server restart

设置永久：

编辑以下文件并添加：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

sort_buffer_size = 256000000

sudo service mysql restart

- Rohit Parte

---

[mysqld] sort_buffer_size = 256000000 MySQL 8标准配置 - Diego de la Riva

感谢Diego de la Riva。在Ubuntu上安装了MySQL 8之前，配置文件中/etc/mysql/、/etc/mysql/mysql.conf.d/和/etc/mysql/conf.d/中的所有头都是[mysql]，因此必须将其更改为[mysqld] sort_buffer_size = 256000000。 - Jeff

它能工作，但尝试设置为永久不起作用！ - John

Answer 2

尝试对具有json列的表进行排序时，存在MySQL问题，导致出现此错误。看起来它影响MySQL >= 8.0.18版本。截至我在这里的回复，团队仍在考虑是否这是一个bug。如果您受到影响，请在该线程中提供反馈。

虽然增加缓冲区大小可以解决此问题，但在我看来，这真的不应该是必要的，因为即使按带有json列的表的数字主键排序也会导致此问题。

根据我的经验，调整缓冲区大小并不是非常可靠，因为缓冲区大小相对于json列中内容的长度，因此虽然它可能解决了立即的问题，但如果数据集增长，则需要再次进行调整。

- Ben

---

已修复于MySQL 8.0.28版本。 - muttonUp

听起来 TEXT 和 BLOB 列也可能会触发这个问题。 - Illya Moskvin

MySQL 8.0.29中仍存在Bug。 - Jimbolino

阅读了相关问题后：我并不确定它是否已经得到了所有人的满意解决，但其中一条评论建议添加一个明确的索引，与排序条件完全匹配，这在MySQL 8.0.23上为我的问题提供了解决方案（主机RDS Serverless V2）。 - Rupert Rawnsley

我在主键列上有一个 ORDER BY。在 8.0.28 中修复后，如果选择的列只有几列，它可以正常工作。但是如果选择了所有列，则问题仍然存在。 - PAX

尽管在许多情况下，在排序列上添加索引对我有所帮助。然而，在我的当前经验中，仅选择目标JSON列并进行排序的SQL查询会导致错误，尽管已经在排序列上建立了索引。当我选择所有列时，问题消失了。我筛选了列并确定了最小集合。只有一个列是必须始终包括以保证没有错误。它不是ID，也不是主键，而是我唯一拥有的LONGTEXT列。我知道这是无意义的，但认为分享可能会有所帮助。 - Khoubeib Bouthour

Answer 3

您可能需要增加/etc/mysql/my.cnf中mysql的缓冲区大小，类似于：

[mysqld]
sort_buffer_size=256k

- Rahul Tripathi

---

请参考 http://www.xaprb.com/blog/2010/05/09/how-to-tune-mysqls-sort_buffer_size/ ... 顺便说一句：我也是个初学者 ;) - Trinimon

@Trinimon：确实，想法是增加sort_buffer_size的值。好链接。 :) - Rahul Tripathi

我已经阅读过有些情况下你可以修改单个查询的参数。你知道怎么做吗？（因为在不需要的情况下，这会影响性能） - Sébastien

PDO::exec("set sort_buffer_size=bytes") - ben3000

尽量避免根据MySQL文档更改此配置值。我可以通过将旧表从myISAM转换为InnoDB来修复它。 - Markus Zeller

排序缓冲区大小是256k还是256M？ - John

Answer 4

如果您使用Docker Mysql容器，请在docker-compose.yml中添加以下设置：

db:
    image: mysql:8
    command: --sort_buffer_size=512K

- Dmitry

Answer 5

所以答案几乎从来不是增加缓冲区大小（一般情况下避免更改MySQL的默认设置），而是要检查查询的质量和/或向您经常查询的列添加索引！！！

语法：

CREATE INDEX [index name] ON [table name]([column name]);

文件： https://dev.mysql.com/doc/refman/8.0/zh/create-index.html

- Reza ODB

---

所以基本上你想做的就是使用 ORDER BY 在你排序的列上创建一个索引。 - DustWolf

Answer 6

在我这种情况下，实际上有所帮助的是为我要排序的字段创建索引，而不是增加排序缓冲区的大小。我不确定在连接表时是否有效，但在尝试对一个非常大的单个表进行排序时，它会起作用。

- apokryfos

---

可能有助于索引用于连接的字段。在MsSQL中，我会查看执行计划。 - Leif Neland

Answer 7

看起来选择包含大型json数据的列可能会导致此错误。我的json列中包含了html页面，通过将其从select语句中删除来解决了这个错误。

- Andriy Lozynskiy

---

我认为这不仅仅是JSON数据；我有一个小的联合查询，其中每个SELECT都包含类似于“a long text string” AS COLUMN1的术语。在某些情况下，“a long text string”的长度达到了186k（字符，而不是字节）- 如果我在PHP中检查该字符串并在包含它在SELECT中之前将其截断，我就避免了内存问题（并且在这种特定情况下没有实质性地影响我的结果）。 - Mark Bradley

Answer 8

我在我的Laravel应用程序中遇到了同样的问题，MySQL正在Docker中运行。

我创建了文件夹

mkdir /home/user/mysql

mkdir /home/user/mysql/config

mkdir /home/user/mysql/mysql-data

nano /home/user/mysql/config/my.conf

[mysqld]
lower_case_table_names = 1
default-authentication-plugin=mysql_native_password 
bind-address=0.0.0.0
sort_buffer_size = 256000000

在终端输入命令：nano /home/user/mysql/docker-compose.yml

version: '3.9'
services:
mysql:
    image: mysql:latest
    volumes:
    - ./config/my.cnf:/etc/mysql/my.cnf
    - ./mysql-data:/var/lib/mysql
    environment:
    - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
    - MYSQL_USER=${MYSQL_USER}
    - MYSQL_PASSWORD=${MYSQL_PASSWORD}
    ports:
    - "3334:3306"
    restart: always

nano /home/user/mysql/.env

MYSQL_ROOT_PASSWORD=12345
MYSQL_USER=admin
MYSQL_PASSWORD=12345

进入/home/user/mysql目录

执行docker-compose up -d命令

问题已经解决

- Юрий Ярвинен