优化带整数列排序的MySQL查询：提升百万级数据查询速度

针对你的百万级products表查询慢的问题，核心原因是ORDER BY FIELD()或自定义排序条件无法利用索引，导致MySQL不得不对大量符合条件的行进行文件排序——这就是为什么无排序时快到0.005秒，加排序就飙到5秒的根源。结合你的需求（优先返回provider_count>0的记录，按指定顺序排列，最后放provider_count=0的），给你几个针对性的优化方案：

1. 优化索引，让分组和筛选更快 #

你之前加了联合索引但没见效，大概率是索引顺序不对。创建**(provider_count, search_name)**的联合索引：

CREATE INDEX idx_provider_search ON products (provider_count, search_name);

这个索引的作用是：

• 快速定位到provider_count符合IN条件的所有行
• 因为search_name在索引里，GROUP BY时可以直接利用索引的有序性，避免创建临时表和文件排序

如果MySQL没自动选择这个索引，可以用FORCE INDEX强制指定：

SELECT products.* 
FROM products FORCE INDEX (idx_provider_search)
WHERE products.provider_count IN (12,2,3,4,5,6,7,8,9,10,11,13,14,18,19,21,22,42,46,58,0)
GROUP BY products.search_name
ORDER BY FIELD(provider_count, 12,2,3,4,5,6,7,8,9,10,11,13,14,18,19,21,22,42,46,58,0)
LIMIT 48;

2. 拆分查询+UNION ALL，减少排序数据量 #

既然你要把非0的放前面，0的放最后，完全可以把查询拆成两部分：先取非0的按指定顺序排列的记录，不足48条再补0的。这样排序操作只需要处理小数据集，速度会快很多：

-- 第一部分：取非0的、按指定顺序的记录（最多48条）
SELECT * FROM (
    SELECT *
    FROM products
    WHERE provider_count IN (12,2,3,4,5,6,7,8,9,10,11,13,14,18,19,21,22,42,46,58)
    GROUP BY search_name
    ORDER BY CASE provider_count
        WHEN 12 THEN 1
        WHEN 2 THEN 2
        WHEN 3 THEN 3
        WHEN 4 THEN 4
        WHEN 5 THEN 5
        WHEN 6 THEN 6
        WHEN 7 THEN 7
        WHEN 8 THEN 8
        WHEN 9 THEN 9
        WHEN 10 THEN 10
        WHEN 11 THEN 11
        WHEN 13 THEN 12
        WHEN 14 THEN 13
        WHEN 18 THEN 14
        WHEN 19 THEN 15
        WHEN 21 THEN 16
        WHEN 22 THEN 17
        WHEN 42 THEN 18
        WHEN 46 THEN 19
        WHEN 58 THEN 20
    END ASC
) AS non_zero
LIMIT 48

UNION ALL

-- 第二部分：只在非0记录不足48条时，补0的记录
SELECT * FROM (
    SELECT *
    FROM products
    WHERE provider_count = 0
    GROUP BY search_name
) AS zero
LIMIT GREATEST(0, 48 - (SELECT COUNT(*) FROM (
    SELECT 1
    FROM products
    WHERE provider_count IN (12,2,3,4,5,6,7,8,9,10,11,13,14,18,19,21,22,42,46,58)
    GROUP BY search_name
) AS non_zero_count));

这个方案的优势：如果非0的记录数已经≥48，第二部分查询不会执行，直接返回结果；即使需要补0的，排序操作也只针对非0的小数据集，完全避免了对百万级数据的排序。

3. 用窗口函数（MySQL 8.0+）精准控制分组和排序 #

如果你的MySQL版本是8.0及以上，窗口函数可以更优雅地解决“每个search_name只取一条符合排序优先级的记录”的问题，同时避免不必要的排序：

SELECT * FROM (
    SELECT *,
        -- 按search_name分组，每组内按指定顺序排序，取第一条
        ROW_NUMBER() OVER (PARTITION BY search_name ORDER BY CASE provider_count
            WHEN 12 THEN 1
            WHEN 2 THEN 2
            WHEN 3 THEN 3
            WHEN 4 THEN 4
            WHEN 5 THEN 5
            WHEN 6 THEN 6
            WHEN 7 THEN 7
            WHEN 8 THEN 8
            WHEN 9 THEN 9
            WHEN 10 THEN 10
            WHEN 11 THEN 11
            WHEN 13 THEN 12
            WHEN 14 THEN 13
            WHEN 18 THEN 14
            WHEN 19 THEN 15
            WHEN 21 THEN 16
            WHEN 22 THEN 17
            WHEN 42 THEN 18
            WHEN 46 THEN 19
            WHEN 58 THEN 20
            WHEN 0 THEN 21 -- 0的优先级最低
        END ASC) AS rn
    FROM products
    WHERE provider_count IN (12,2,3,4,5,6,7,8,9,10,11,13,14,18,19,21,22,42,46,58,0)
) AS t
WHERE rn = 1 -- 每个search_name只保留第一条
ORDER BY CASE provider_count
    WHEN 12 THEN 1
    WHEN 2 THEN 2
    WHEN 3 THEN 3
    WHEN 4 THEN 4
    WHEN 5 THEN 5
    WHEN 6 THEN 6
    WHEN 7 THEN 7
    WHEN 8 THEN 8
    WHEN 9 THEN 9
    WHEN 10 THEN 10
    WHEN 11 THEN 11
    WHEN 13 THEN 12
    WHEN 14 THEN 13
    WHEN 18 THEN 14
    WHEN 19 THEN 15
    WHEN 21 THEN 16
    WHEN 22 THEN 17
    WHEN 42 THEN 18
    WHEN 46 THEN 19
    WHEN 58 THEN 20
    WHEN 0 THEN 21
END ASC
LIMIT 48;

窗口函数可以利用idx_provider_search索引快速完成分组内的排序，效率比传统GROUP BY+排序高很多。

额外优化建议 #

• *避免SELECT ： 只查询你需要的列，减少数据传输和内存占用，能显著提升速度。
• 检查GROUP BY合理性： 如果同一个search_name对应多个provider_count，确认你是否需要保留特定优先级的那条记录（上面的窗口函数方案已经帮你处理了这个问题）。
• 查看执行计划： 用EXPLAIN命令分析你的查询，重点看是否有Using filesort或Using temporary——如果有，说明索引没生效，需要调整索引或查询逻辑。

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