在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')
;
如果我省略分组,它运行良好。如果我添加选择并按较少的元素分组,它也可以运行良好,但是在我的树枝模板中启动了更多子查询。
如何优化此查询以不会出现错误,或者通过分配更多内存(理想情况下仅用于此查询)来消除错误?
只需运行 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
MySQL 有一个突出的问题,在尝试对带有 json 列的表进行排序时会导致此错误。 看起来它会影响MySQL>= 8.0.18。 截至我在这里的回复,团队仍在考虑这是否是一个错误。 如果您受到此影响,请提供有关该线程的反馈。
虽然增加缓冲区大小在这里可能是一种解决方案,但 IMO 确实没有必要,因为即使在带有 json 列的表上按数字主键排序也会导致此问题。
根据我的经验,调整缓冲区大小不是很可靠,因为缓冲区大小是相对于 json 列中内容的长度,因此虽然它可以解决眼前的问题,但如果数据集增长,则需要再次调整。
您可能需要在/etc/mysql/my.cnf
中增加mysql的缓冲区大小,如下所示:
[mysqld]
sort_buffer_size=256k
如果您使用的是 docker mysql 容器,请在 docker-compose.yml 中添加以下设置:
db:
image: mysql:8
command: --sort_buffer_size=512K
因此,答案几乎永远不会增加缓冲区大小(并且避免接触MySQL中的默认设置),而是查看查询的质量和/或将索引添加到您经常对!!运行查询的列
语法:
CREATE INDEX [index name] ON [table name]([column name]);
.DOC:https://dev.mysql.com/doc/refman/8.0/en/create-index.html
就我而言,在不增加排序缓冲区大小的情况下,实际上有帮助的是为我订购的字段创建索引。我不确定它在连接表时是否有效,但在尝试订购非常大的单个表时它会起作用。
看起来选择具有大型 json 数据的列可能会导致此错误。我有 json 列,里面有 html 页面,并通过从 select 语句中删除它来修复错误。
我的 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
cd/home/user/mysql
docker-compose up -d
问题已解决