MySQL使用ORDER BY LOCATE出现Out of sort memory错误排查

为什么会触发Out of sort memory错误？ #

虽然最终匹配结果只有16条，但你的查询实际触发了全表级别的排序操作，超出了MySQL的排序内存限制，具体拆解：

1. WHERE条件导致全表扫描
   你的查询用了policy_id LIKE '%apple%' OR name LIKE '%apple%'，两个模糊匹配都是前缀带%的形式——这种写法会直接让collections_name_index索引失效（B+树索引只能匹配前缀固定的查询）。MySQL不得不扫描全表8万行来筛选符合条件的记录。

2. ORDER BY无法利用索引，必须手动排序
   你用ORDER BY LOCATE('apple', name)来排序，这个值是动态计算的，没有对应的预计算索引，MySQL无法通过索引直接拿到排序后的结果，只能对筛选出的记录执行文件排序（filesort）。

3. 优化器的执行路径选择错误
   当查询同时包含ORDER BY和LIMIT时，MySQL优化器可能会误判效率：它会尝试先对全表8万行进行排序，再从排序后的结果里筛选符合WHERE条件的记录并取前10条。这种情况下，排序所需的内存如果超过了RDS实例配置的sort_buffer_size上限，就会抛出Out of sort memory错误。

验证方式 #

执行EXPLAIN命令查看查询计划：

EXPLAIN
select *
from `collections`
where (`policy_id` like '%apple%' or `name` like '%apple%')
order by LOCATE('apple', `name`)
limit 10 offset 0;

如果结果里type列显示ALL（全表扫描），且Extra列包含Using filesort，就说明确实在执行全表排序。

解决办法 #

1. 强制先过滤再排序
   把查询改成子查询形式，让MySQL先筛选出符合条件的16条记录，再对这少量数据排序：

   SELECT * FROM (
       SELECT * FROM `collections`
       WHERE (`policy_id` like '%apple%' or `name` like '%apple%')
   ) AS filtered_results
   ORDER BY LOCATE('apple', `name`)
   LIMIT 10 OFFSET 0;
   

   这样只会对16条数据排序，完全不会触发内存不足问题。

2. 调整RDS排序内存参数
   如果无法修改查询，可以联系云服务商调整sort_buffer_size参数（注意不要设置过大，避免实例内存竞争），但优先推荐修改查询逻辑。

3. 改用全文索引优化模糊查询
   针对频繁的模糊搜索场景，给policy_id和name创建全文索引：

   ALTER TABLE collections ADD FULLTEXT INDEX ft_policy_name (policy_id, name);
   

   然后用全文搜索替代LIKE查询：

   SELECT * FROM collections
   WHERE MATCH(policy_id, name) AGAINST('apple' IN BOOLEAN MODE)
   ORDER BY LOCATE('apple', name)
   LIMIT 10 OFFSET 0;
   

   全文索引能避免全表扫描，大幅提升查询效率。

内容的提问来源于stack exchange，提问作者Latheesan