MySQL调用文章搜索存储过程报错：排序内存不足，调sort buffer后遇事务错误

1. SQL Error [1038] [HY001] 排序内存不足报错 #

这类报错无法单纯靠调大sort_buffer_size根治，核心问题来自存储过程本身的逻辑缺陷，才导致排序内存消耗异常升高：

• 变量引用错误：存储过程内定义了NSortColumn、NSortOrder两个规范化的入参处理变量，但窗口排序逻辑里完全没有引用这两个变量，反而使用了从未赋值的会话变量@SortColumn、@SortOrder。这两个会话变量实际值为NULL，会导致所有CASE排序分支全部匹配失败，所有行的排序键退化为同一个值，MySQL无法走已有索引完成排序，必须将全量符合条件的文章数据加载到内存做外部排序，直接占用远超阈值的排序内存。
• 全量重复计算：两个独立CTE分别对标题匹配、描述匹配的全量结果做ROW_NUMBER()窗口排序、COUNT(*) OVER()全量计数，相当于对同一张文章表做两次全表扫描、两次全量窗口排序，内存消耗直接翻倍。
• 额外排序开销：使用UNION（而非UNION ALL）合并两个CTE结果时，MySQL会对合并后的全量结果集再做一次去重排序，进一步放大内存占用。

对应处理方案 #

• 修正变量引用错误：把排序逻辑里的未赋值会话变量@SortColumn、@SortOrder，替换为存储过程内正确赋值的NSortColumn、NSortOrder，让排序规则匹配传入参数，避免退化为全量无效排序。
• 简化逻辑匹配设计目标：不再拆分两个CTE做重复全量计算，单次扫描文章表，给匹配结果打优先级标记——标题命中的行标记为高优先级，描述命中（但未命中标题）的行标记为低优先级，合并成单个结果集后做一次排序，从根源减少内存占用。
• 合理配置sort_buffer_size：该参数是每个数据库连接独立分配的内存块，单连接值建议控制在256KB-2MB区间，SQL逻辑修正后完全可以支撑正常排序，盲目调大反而会引发多连接场景下的整体内存溢出。

2. SQL Error [S1000] 无法从服务器获取事务只读状态报错 #

这类报错是调大sort_buffer_size后的连锁问题，和业务逻辑、事务配置无关：

• 当sort_buffer_size设置值超过MySQL允许的单会话内存分配上限时，服务端在初始化新连接、查询会话事务上下文（包括只读标记、隔离级别）的阶段就会因为内存分配失败，无法向客户端返回正常的会话状态信息，客户端驱动收不到合法响应就会抛出该类S1000通用错误。

对应处理方案 #

• 执行SHOW VARIABLES LIKE 'sort_buffer_size';确认当前参数值，将其调整回合理范围，重启MySQL后先执行基础查询（如SELECT @@version;）测试连接状态，确认可以正常获取事务上下文、无报错后，再调用存储过程验证功能。
• 严禁将sort_buffer_size设置为百MB/GB级别的极端大值，这类配置会直接破坏MySQL会话初始化的正常流程，引发各类不可预期的连接类错误。

优化后存储过程核心逻辑参考 #

CREATE PROCEDURE art_usp_Article_Search(
    IN SearchTerm VARCHAR(200),
    IN SortColumn VARCHAR(100),
    IN SortOrder VARCHAR(5),
    IN PageNumber INT,
    IN PageSize INT
)
BEGIN
    DECLARE StartRow INT;
    DECLARE N_SortColumn VARCHAR(100);
    DECLARE N_SortOrder VARCHAR(100);

    SET N_SortColumn = LOWER(IFNULL(SortColumn, ''));
    SET N_SortOrder = LOWER(IFNULL(SortOrder, ''));
    SET StartRow = (PageNumber - 1) * PageSize;

    WITH SearchResult AS (
        SELECT 
            -- 匹配优先级：标题命中 > 描述命中，满足优先展示标题匹配结果的需求
            CASE 
                WHEN title LIKE CONCAT('%', IFNULL(SearchTerm, ''), '%') THEN 1
                WHEN shortDescription LIKE CONCAT('%', IFNULL(SearchTerm, ''), '%') THEN 2
            END AS match_priority,
            COUNT(*) OVER () AS TotalCount,
            id,code,categoryId ,title,shortDescription ,description ,tags ,content,date,time,typeId,isActive,isSpecial,visited,author,imageId,statusId,created_by,updated_by,created_at,updated_at
        FROM art_article aa 
        WHERE isActive = 1
          AND (
              IFNULL(SearchTerm, '') = '' 
              OR title LIKE CONCAT('%', SearchTerm, '%')
              OR shortDescription LIKE CONCAT('%', SearchTerm, '%')
          )
    )
    SELECT * FROM SearchResult
    ORDER BY 
        match_priority ASC,
        CASE WHEN N_SortColumn = 'title' AND N_SortOrder = 'asc' THEN title END ASC,
        CASE WHEN N_SortColumn = 'date' AND N_SortOrder = 'asc' THEN date END ASC,
        CASE WHEN N_SortColumn = 'title' AND N_SortOrder = 'desc' THEN title END DESC,
        CASE WHEN N_SortColumn = 'date' AND N_SortOrder = 'desc' THEN date END DESC
    LIMIT StartRow, PageSize;
END

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