如何优化2000万行数据集的SQL排序性能？查询调优需求

优化方案 #

1. 创建适配过滤+排序的联合表达式索引 #

当前查询耗时核心原因是：过滤后返回138万条数据再排序，完全依赖内存/磁盘排序。要让数据库直接通过索引完成过滤+排序，避免全量数据排序。

针对每个需要排序的字段（firstName/lastName/phone/email/createdAt），构建包含过滤条件和空字符串处理逻辑的联合索引——用CASE表达式将空字符串标记为高优先级（排序时后置），索引直接固化该逻辑，避免运行时计算。

以firstName升序为例，创建索引：

CREATE INDEX idx_conversation_teambot_location_filter_firstname ON conversation
(
    teamBotId,
    teamBotLocationId,
    isDeleted,
    isBlocked,
    isBroadcast,
    isGroup,
    (CASE WHEN firstName = '' THEN 1 ELSE 0 END), -- 空字符串标记为1，排序时自动靠后
    firstName,
    id DESC -- 兜底唯一排序字段，避免排序歧义
);

同理，为createdAt降序创建索引：

CREATE INDEX idx_conversation_teambot_location_filter_createdat_desc ON conversation
(
    teamBotId,
    teamBotLocationId,
    isDeleted,
    isBlocked,
    isBroadcast,
    isGroup,
    createdAt DESC,
    id DESC
);

进阶优化：如果isDeleted/isBlocked等字段绝大多数值为false，可以创建部分索引缩小体积：

CREATE INDEX idx_conversation_teambot_location_filter_firstname ON conversation
(
    teamBotId,
    teamBotLocationId,
    (CASE WHEN firstName = '' THEN 1 ELSE 0 END),
    firstName,
    id DESC
) WHERE isDeleted = false AND isBlocked = false AND isBroadcast = false AND isGroup = false;

2. 改写SQL：先取排序后的ID，再关联小表 #

当前查询先关联所有符合条件的teamBotLocation再排序，会处理大量冗余数据。改为先筛选并排序出目标conversation的ID，再关联小表，将关联数据量从138万降到10条：

SELECT
    c.*,
    t."id" AS "teamBotLocation.id",
    t."name" AS "teamBotLocation.name"
FROM
    (
        SELECT id
        FROM conversation
        WHERE
            teamBotId = 'some_id'
            AND teamBotLocationId IN ('some_id','some_id')
            AND isDeleted = false
            AND isBlocked = false
            AND isBroadcast = false
            AND isGroup = false
        ORDER BY
             CASE WHEN firstName = '' THEN 1 ELSE 0 END ASC,
             firstName NULLS LAST,
             id DESC
        LIMIT 10 OFFSET 100
    ) AS sub
JOIN conversation c ON sub.id = c.id
LEFT JOIN teamBotLocation t ON c.teamBotLocationId = t.id;

3. 替换OFFSET分页为键集分页（推荐） #

OFFSET 100会让数据库扫描并丢弃前100条数据，页码越大效率越低。改用键集分页，通过记录上一页最后一条数据的排序字段值，直接定位下一页起始位置：
假设上一页最后一条数据的CASE值为0、firstName为"Smith"、id为12345，下一页查询改写为：

SELECT
    c.*,
    t."id" AS "teamBotLocation.id",
    t."name" AS "teamBotLocation.name"
FROM
    (
        SELECT id
        FROM conversation
        WHERE
            teamBotId = 'some_id'
            AND teamBotLocationId IN ('some_id','some_id')
            AND isDeleted = false
            AND isBlocked = false
            AND isBroadcast = false
            AND isGroup = false
            AND (
                (CASE WHEN firstName = '' THEN 1 ELSE 0 END) > 0,
                (CASE WHEN firstName = '' THEN 1 ELSE 0 END) = 0 AND firstName > 'Smith',
                (CASE WHEN firstName = '' THEN 1 ELSE 0 END) = 0 AND firstName = 'Smith' AND id < 12345
            )
        ORDER BY
             CASE WHEN firstName = '' THEN 1 ELSE 0 END ASC,
             firstName NULLS LAST,
             id DESC
        LIMIT 10
    ) AS sub
JOIN conversation c ON sub.id = c.id
LEFT JOIN teamBotLocation t ON c.teamBotLocationId = t.id;

这种方式完全利用索引定位，分页效率不随页码增加下降。

4. 确认teamBotLocation表索引 #

虽然当前teamBotLocation扫描开销极小，但需确保主键id存在索引（主键默认带索引，此步骤仅作验证）：

CREATE INDEX IF NOT EXISTS idx_teambotlocation_id ON teamBotLocation(id);

效果验证 #

优化后查看执行计划，应满足：

• 子查询直接使用联合索引完成过滤+排序，无Sort操作
• 关联操作仅处理10条数据
• 整体执行时间控制在1-2秒内

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