【MySQL】Mysql提示:Out of sort memory, consider increasing server sort buffer size

Java接口忽然报错，错误信息是Out of sort memory, consider increasing server sort buffer size。

字面意思就是 sort内存溢出，考虑增加服务器的排序缓冲区(sort_buffer_size)大小。

[root@localhost ~]# docker exec -it mysql8 mysql -uroot -p********
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 69597
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show variables like '%sort_buffer_size%'; 
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+
3 rows in set (0.01 sec)

mysql> SET GLOBAL sort_buffer_size = 1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# docker exec -it mysql8 mysql -uroot -p******
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 69601
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 1048576 |
+-------------------------+---------+
3 rows in set (0.01 sec)

mysql> exit
Bye

设置大了，可以提升性能，但占用更多的内存资源。设置小了，可能会降低性能。