查询优化（自动编号）：千万级数据量下预约序列编号SQL性能优化

你的问题核心是相关子查询导致的性能瓶颈——原来的写法里，每一条预约记录都会触发一次子查询去统计同病例下更早的预约数，500万条数据就会产生500万次额外查询，这在大数据量场景下完全不可行。

最优优化方案：使用窗口函数ROW_NUMBER() #

数据库的窗口函数是专门为这种分组内排序、编号的场景设计的，它会一次性计算所有分组的序号，避免了重复的子查询开销。修改后的SQL如下：

SELECT
    a.appointment_date AS Appointment,
    e.name AS Episode,
    ROW_NUMBER() OVER (PARTITION BY a.episode_id ORDER BY a.appointment_date ASC) AS "Appointment number"
FROM Appointment a
JOIN Episodes e ON e.episode_id = a.episode_id
WHERE a.person_id = 1
ORDER BY e.name, "Appointment number" DESC; -- 可选，用来匹配你给出的示例结果顺序

为什么这个方案更高效？ #

• ROW_NUMBER()是集合运算：数据库引擎会一次性扫描并分组处理所有符合条件的记录，而不是逐行触发子查询。
• 避免了重复IO：不需要反复查询同一张表统计计数，大幅减少磁盘和CPU消耗。

进一步优化：添加合适的索引 #

为了让这个查询的性能最大化，建议给Appointment表创建复合索引：

CREATE INDEX idx_appointment_person_episode_date ON Appointment (person_id, episode_id, appointment_date);

这个索引可以让数据库直接通过索引筛选person_id=1的记录，同时按episode_id分组、appointment_date排序时不需要额外排序操作（索引本身是有序的），实现索引覆盖查询，完全避免回表读取数据。

另外确保Episodes表的episode_id是主键（或有唯一索引），这样JOIN操作的效率也会更高。

结果验证 #

这个查询会按照每个病例（episode_id）分组，对组内的预约按日期升序排序，最早的预约编号为1，完全符合你预期的结果格式。

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