优化MySQL查询提升扩展性：按输入名称顺序返回且重复查询返回对应结果

你原有写法性能差的核心原因是每一个UNION ALL分段都会单独扫描一次目标表，几百个姓名就会触发几百次表扫描，开销自然很高。优化的核心思路是仅扫描一次表，通过单独构建的排序映射关系匹配顺序，以下是可落地的方案：

通用兼容方案（支持绝大多数主流数据库） #

先通过常量构造一个带排序序号的映射表，再和目标表做关联查询，仅扫描一次目标表即可得到结果，同时天然满足你的两个规则：

• 映射表的序号完全对应你传入的姓名列表顺序，最终按序号排序即可保证结果顺序
• 传入的姓名重复时，映射表中会对应生成多条记录，关联后结果自然会重复返回

示例SQL如下：

SELECT t.* 
FROM (
  -- 映射表按传入姓名的顺序构造，position是自增的序号，和传入顺序一一对应
  VALUES 
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Charlie'),
    (4, 'Dan'),
    (5, 'Bob') -- 重复传入的姓名直接加对应序号即可
) AS sort_map(position, name)
JOIN your_table t ON t.name = sort_map.name
ORDER BY sort_map.position;

如果使用的是不支持VALUES派生表的旧版本MySQL，可以改用以下写法构造映射表，构造映射表的UNION ALL仅处理常量，没有IO开销，几百条数据拼接也不会有性能问题：

SELECT t.*
FROM (
  SELECT 1 position, 'Alice' name UNION ALL
  SELECT 2 position, 'Bob' name UNION ALL
  SELECT 3 position, 'Charlie' name UNION ALL
  SELECT 4 position, 'Dan' name UNION ALL
  SELECT 5 position, 'Bob' name
) sort_map
JOIN your_table t ON t.name = sort_map.name
ORDER BY sort_map.position;

PostgreSQL 便捷方案 #

如果使用PostgreSQL，可以直接用unnest函数配合WITH ORDINALITY生成映射表，不需要手动拼接大量常量段，Python侧直接传入姓名数组即可：

SELECT t.*
FROM unnest(ARRAY['Alice','Bob','Charlie','Dan','Bob']) WITH ORDINALITY AS sort_map(name, position)
JOIN your_table t ON t.name = sort_map.name
ORDER BY sort_map.position;

额外优化建议 #

• 给目标表的name字段建立普通索引，关联查询时会直接走索引匹配，性能可以提升数倍到数十倍
• Python侧构造映射表时直接遍历用户传入的姓名列表，给每一项分配自增的position即可，逻辑实现非常简单

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