40万+数据量订单表SQL查询耗时873ms优化求助

慢查询根因 #

你当前的SQL存在两个典型性能问题：

• 重复两次查询addresses表取region_id=12的地址ID，存在冗余查询开销
• OR连接两个IN子查询的写法，会导致数据库无法高效利用orders表上的索引，40万数据量下很容易出现索引失效、扫描行数过多的问题

第一步：必做索引优化（见效最快，成本最低） #

先给两张表补上覆盖索引，避免查询过程中回表取数：

• 给addresses表建联合索引，直接通过索引就能拿到region_id对应的所有地址ID，不需要回表：

CREATE INDEX idx_addresses_regionid_id ON addresses(region_id, id);

• 给orders表建两个联合索引，以过滤性最强的status作为最左前缀，匹配固定条件status=2：

CREATE INDEX idx_orders_status_pickupaddr ON orders(status, pickup_address_id);
CREATE INDEX idx_orders_status_deliveryaddr ON orders(status, delivery_address_id);

第二步：改写SQL逻辑，替换低效OR+IN写法 #

OR条件会让优化器无法选择最优执行计划，把OR条件拆成两个独立查询再合并去重，能让两个子查询分别命中上面建的对应索引，性能提升非常明显，改写后SQL如下：

SELECT COUNT(*) AS aggregate
FROM (
    -- 匹配取货地址符合要求的订单
    SELECT 1
    FROM orders
    WHERE status = 2
      AND pickup_address_id IN (SELECT id FROM addresses WHERE region_id = 12)
    UNION
    -- 匹配配送地址符合要求的订单，UNION自动去重，避免同一订单双地址都匹配时重复计数
    SELECT 1
    FROM orders
    WHERE status = 2
      AND delivery_address_id IN (SELECT id FROM addresses WHERE region_id = 12)
) AS matched_orders;

如果你的数据库是MySQL5.6及以下版本，对IN子查询优化较差，可以换成JOIN写法，执行计划更稳定：

SELECT COUNT(DISTINCT orders.id) AS aggregate
FROM orders
LEFT JOIN addresses a_pick ON orders.pickup_address_id = a_pick.id AND a_pick.region_id = 12
LEFT JOIN addresses a_deliver ON orders.delivery_address_id = a_deliver.id AND a_deliver.region_id = 12
WHERE orders.status = 2
  AND (a_pick.id IS NOT NULL OR a_deliver.id IS NOT NULL);

进阶优化（适合高频查询、数据量持续增长的场景） #

• 如果region_id=12对应的地址ID数量不多（通常小于1000个），可以先在业务代码中查询出所有符合要求的地址ID集合，直接拼接到SQL的IN条件中，省去SQL层的子查询开销，性能还能再提升30%以上。
• 如果这类区域维度的订单统计是高频需求，可以直接在orders表冗余pickup_region_id、delivery_region_id两个字段，创建订单时就同步存储对应的区域ID，查询时直接过滤两个冗余字段即可，配合对应索引，查询耗时可以稳定在10ms级别，后续订单量涨到千万级也不会出现性能问题。

按上述索引+SQL改写的方案优化后，你当前40万订单量的场景下，查询耗时通常可以降到50ms以内，满足性能要求。

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