记Mysql 查询报Out of sort memory错误的总结

一、起因:

        使用一个SQL查询的时候，由于业务需要，需要对相关字段按照某个字段进行排序，且进行LIMIT 数据分页，发现查询的报错。

二、分析

2.1 为什么会出现这个问题呢？又怎么解决这个问题呢？

经过搜索资料查询，大概确定了几个问题：

1）当使用MySQL服务器执行大量排序操作时，就可能会遇到以下错误：“Out of sort memory，重新分配了XXX字节”。这个错误表示MySQL服务器在执行排序时耗尽了所有分配给排序操作的内存，导致操作失败。

出现这个错误的原因是，MySQL服务器会在内存中创建一个排序缓存区，用于存储将要排序的数据。如果排序操作涉及的数据太多，超过了MySQL服务器为排序操作分配的缓存空间大小，那么就会出现“Out of sort memory”错误。

可在Mysql 的sql 界面排序缓存区的大小查询：

SHOW VARIABLES LIKE '%SORT_BUFFER_SIZE%';

 我这跟一些人的大小不一样，我的约 1M左右，

应对这个问题，可以采取以下措施：

1. 增加排序缓存区的大小。可以通过在服务器上修改MySQL的配置文件，适当调整排序缓存区的大小。

两个方案：

1）修改mysql的配置文件 my.cnf

[mysqld]
sort_buffer_size = 1048576

修改后重启服务即可

2）直接navicat 命令工具中通过SET来设置（必须要有SUPER权限）

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

上述命令只会修改当前的mysql实例的排序缓存区大小，不修改配置文件，重启后会恢复配置文件的大小

直接修改配置文件，

2. 降低排序查询返回的数据量。可以通过限制查询语句的返回结果数量，或者增加查询的限制条件，来减少排序查询涉及的数据量。

3. 重新设计查询语句。可以通过重新设计查询语句，去除掉排序操作，或将排序操作转移到客户端执行，从而避免在MySQL服务器上执行排序操作。

        一般出现这个报错，sql的执行计划会伴随出现，Using where;Using Filesort

这就是SQL查询语句中一般出现了WHERE 、ORDER BY 加 LIMIT ,我们可以对 相关的where字段以及ORDER BY的字段做组合索引，按照where 条件的字段、order by的字段遵循最左原则，从左到右新增。

4. 修改服务器内存配置。可以通过增加服务器的物理内存，或者调整MySQL服务器的内存配置参数，来扩大排序缓存区的大小。 

注意事项：Mysql配置文件修改后注意重启