ASP.NET 6 MVC调用MySQL存储过程报排序内存不足错误求助

一、确认sort_buffer_size参数是否生效 #

1. 修改my.cnf后必须重启MySQL服务，否则配置不会生效。
2. 登录MySQL客户端，执行命令检查当前参数值：

SHOW VARIABLES LIKE 'sort_buffer_size';

如果显示值不是设置的2M（或更大值），检查my.cnf路径是否正确（不同系统路径有差异，比如Ubuntu为/etc/mysql/my.cnf，CentOS为/etc/my.cnf），或确认配置项是否放在[mysqld]块下。
3. 临时生效（无需重启）：执行以下命令设置全局参数，新会话会立即生效，但重启MySQL后会还原：

SET GLOBAL sort_buffer_size = 4*1024*1024; -- 设置为4M

二、优化存储过程查询（治本方案） #

原查询的子查询和分组逻辑会产生大量临时表与排序操作，优化后可大幅降低内存开销：

优化后的存储过程 #

CREATE DEFINER=`root`@`localhost` PROCEDURE `product_list`()
BEGIN
    SELECT 
        p.p_code AS ProductCode, 
        p.p_name AS ProductName, 
        p.category AS Category, 
        SUM(pv.stock) AS TotalStock, 
        i.i_name AS ImageName
    FROM 
        tbl_products p 
    JOIN 
        tbl_productvariants pv ON p.p_Id = pv.product_id
    JOIN (
        -- 用窗口函数替代原IN子查询，减少排序开销
        SELECT product_id, i_name
        FROM (
            SELECT 
                product_id, 
                i_name,
                ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY i_id) AS rn
            FROM tbl_images
        ) t
        WHERE rn = 1
    ) i ON p.p_Id = i.product_id 
    GROUP BY 
        p.p_Id, p.p_code, p.p_name, p.category, i.i_name
    ORDER BY 
        p.p_Id;
END

添加必要索引 #

为以下字段创建索引，让MySQL避免全表扫描与不必要的排序：

1. 为tbl_productvariants的product_id字段建索引：

CREATE INDEX idx_pv_product_id ON tbl_productvariants(product_id);

2. 为tbl_images创建product_id与i_id的联合索引（支持快速获取每个产品的最小i_id）：

CREATE INDEX idx_images_product_id_i_id ON tbl_images(product_id, i_id);

三、额外建议 #

• 不要盲目调大sort_buffer_size，该参数为会话级，每个执行排序的会话都会分配对应缓冲区，过大可能导致MySQL内存耗尽，建议从4M开始尝试，逐步调整。
• 查看查询执行计划，确认是否存在Using filesort（文件排序），若存在说明MySQL无法利用索引排序，需进一步优化索引或查询逻辑：

EXPLAIN CALL product_list();

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