MYSQL排序加分页报错Out of sort memory, consider increasing server sort buffer size

前言

出现该问题的是在做分页查询时出现的，确切的说是order by 和 limit一起用的时候出现的。而起是由于limit m，n 中m过大时会出现Out of sort memory, consider increasing server sort buffer size

解决

SHOW variables like ‘%sort_buffer_size%’

通过命令查询后，发现默认的sort_buffer_size大小为262144（也就是256Kb）;

所以，我们需要修改这个值，将这个值是介于256kb-2M之间，我们将其设置为1M。设置过大连接多的时候，占用的内存也会很大。

SET SESSION sort_buffer_size = 1024 * 1024; – 修改当前session连接的值
SET GLOBAL sort_buffer_size = 1024 * 1024；-- 修改全局变量的值；

方案

方案一：直接修改配置文件

vi /etc/my.cnf
[mysqld]
sort_buffer_size = 1048576 

重启服务

service mysqld restart

方案二：在命令行里通过SET来设置,然后再修改参数文件

如果要修改全局变量,必须要显示指定"GLOBAL"或者"@@global.",同时必须要有SUPER权限.

SET GLOBAL sort_buffer_size = 1024*1024

如果查询时使用的是show variables的话,会发现设置并没有生效,除非重新登录再查看.这是因为使用show variables的话就等同于使用show session variables,查询的是会话变量,只有使用show
global variables查询的才是全局变量.如果仅仅想修改会话变量的话,可以使用类似sort_buffer_size =1048576 ;或者set sort_buffer_size =1048576 ;这样的语法.

当前只修改了正在运行的MySQL实例参数,但下次重启mysqld又会回到默认值,所以别忘了修改参数文件:

vi /etc/my.cnf
[mysqld]
sort_buffer_size =1048576 

总结

一定记得，通过命令设置了全局变量后，要在配置文件中做修改，否则重启服务后，设置的全局变量就失效了。