MySQL LEFT JOIN+ORDER BY大表查询缓慢（已建索引）

1. 为何已建索引，查询仍缓慢？ #

从你的执行计划能直接定位问题：MySQL先对organization做全表扫描，再通过主键逐个关联city，把所有结果存入临时表后，才对临时表做filesort排序。

你给city.cityName建的idx_city_name (cityName DESC, id)索引确实能用于city表自身的排序，但当前查询的关联顺序是从organization到city，MySQL无法提前利用city的索引完成排序——因为要先凑齐所有关联后的结果，才能对cityName排序，而临时表没有索引，只能做全量内存/磁盘排序，这就是耗时的核心原因。

另外，organization的headquarterId没有索引，导致关联时虽然city用了主键，但organization是全表扫描，也会增加前期的关联耗时。

2. 能否让MySQL更高效利用索引？ #

可以，核心思路是调整查询执行顺序，让MySQL在关联前就利用city的索引完成排序，同时优化关联环节的索引：

步骤1：给organization.headquarterId加索引 #

先优化关联效率：

ALTER TABLE organization ADD INDEX idx_headquarter_id (headquarterId);

这能减少organization表的扫描耗时，让关联更快。

步骤2：利用city的索引驱动排序 #

如果需要保留所有organization数据（包括headquarterId为NULL的行），可以拆分查询，先通过city的索引获取排序后的关联数据，再合并无关联的organization数据：

-- 先取有城市关联的机构，利用city的索引排序
SELECT o.*, c.*
FROM city c
JOIN organization o ON o.headquarterId = c.id
ORDER BY c.cityName DESC
LIMIT 15
UNION ALL
-- 再取无城市关联的机构（cityName为NULL，默认排在最后）
SELECT o.*, NULL AS cityName, NULL AS id -- 按需列出city表字段
FROM organization o
WHERE o.headquarterId IS NULL
LIMIT 15;

这个写法中，city表会直接用idx_city_name索引按cityName DESC顺序扫描，不需要临时表和filesort，大幅降低排序耗时。

3. 关联表字段排序是否有更优模式？ #

有两种常用的最优模式：

• 索引驱动排序：让排序字段所在的表作为查询的驱动表（先扫描排序字段的索引，再关联其他表），避免临时表和filesort。
• 预排序分页：对于分页场景，不要先关联所有数据再排序分页，而是先获取排序后的主键列表，再关联获取完整数据。比如：

SELECT o.*, c.*
FROM (
    -- 只排序和获取需要的主键，数据量极小
    SELECT o.id, COALESCE(c.cityName, '') AS sort_col
    FROM organization o
    LEFT JOIN city c ON c.id = o.headquarterId
    ORDER BY sort_col DESC
    LIMIT 15 OFFSET 0 -- 替换为你的分页参数
) AS sorted_ids
JOIN organization o ON o.id = sorted_ids.id
LEFT JOIN city c ON c.id = o.headquarterId
ORDER BY sorted_ids.sort_col DESC;

这种方式把排序的数据集缩小到只有主键和排序字段，排序速度会快很多。

4. 重构查询或子查询预排序是否有效？ #

完全有效，而且是解决这类问题的常用方案。

比如刚才提到的预排序分页，就是通过子查询先完成小数据集的排序，再关联获取完整数据，避免对全量关联结果排序。

另外，如果你不需要保留headquarterId为NULL的organization，可以直接把city作为驱动表，用索引排序后关联organization：

SELECT o.*, c.*
FROM city c
JOIN organization o ON o.headquarterId = c.id
ORDER BY c.cityName DESC
LIMIT 15;

这个查询会直接利用city的idx_city_name索引，无需临时表和filesort，耗时会降到毫秒级。

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