MySQL左外连接查询优化：特定账号条件查询过慢求助

数据老陈

2026-8-21

慢查询优化：账号限制订单查询性能提升方案

问题背景

现有三张业务表：

orders（5,429,850行）：订单主表
order_lines（主键id，外键order_id，18,530,647行）：订单行表，单账号场景存储账号信息，allocation_count=0时生效
order_line_allocations（主键id，外键order_line_id，112,594行）：订单行分配表，多账号场景存储账号信息

需求是查询符合指定账号限制的订单，当前查询耗时7秒，但仅修改账号筛选条件（account_type_id=8+segment_1='2D'）时，查询耗时仅0.03秒。两者差异在于匹配账号数量：

快查询匹配账号：175,667个
慢查询匹配账号：5,648个

慢查询SQL

SELECT `orders`.`id` FROM `orders` 
LEFT JOIN `order_lines` ON `order_lines`.`order_id` = `orders`.`id` 
LEFT JOIN order_line_allocations ON order_line_allocations.order_line_id = order_lines.id
LEFT JOIN (select sec_accounts.id from accounts sec_accounts
  WHERE (sec_accounts.account_type_id = 344 AND ((`sec_accounts`.`segment_1` = 'MS')))
) as non_alloc_accounts ON non_alloc_accounts.id = order_lines.account_id AND order_lines.allocation_count = 0
LEFT JOIN (select sec_accounts.id from accounts sec_accounts
  WHERE (sec_accounts.account_type_id = 344 AND ((`sec_accounts`.`segment_1` = 'MS')))
) as alloc_accounts ON alloc_accounts.id = order_line_allocations.account_id 
WHERE (`orders`.line_count = 0 OR
       `orders`.account_type_id IS NULL OR
       `orders`.account_type_id IN (NULL) OR 
       (non_alloc_accounts.id is not null) OR
       (alloc_accounts.id is not null)
) ORDER BY `orders`.`id` ASC LIMIT 90 \G;

现有索引配置

orders表索引

PRIMARY KEY (`id`),
UNIQUE KEY `index_orders_on_po_number` (`po_number`),
KEY `index_orders_on_account_type_id` (`account_type_id`),
KEY `index_orders_on_created_by` (`created_by_id`),
KEY `index_orders_on_last_exported_at` (`last_exported_at`),
KEY `index_orders_on_status` (`status`),
KEY `index_orders_on_updated_at` (`updated_at`),
KEY `index_orders_on_updated_by` (`updated_by_id`),
KEY `index_orders_on_created_at_and_status` (`created_at`,`status`),
KEY `index_status_supplier_id` (`status`,`supplier_id`),
KEY `index_orders_on_line_count` (`line_count`)

order_lines表索引

PRIMARY KEY (`id`),
UNIQUE KEY `index_order_lines_on_bulk_price_id` (`bulk_price_id`),
KEY `index_order_lines_on_account_type_id` (`account_type_id`),
KEY `index_order_lines_on_created_at` (`created_at`),
KEY `index_order_lines_on_created_by` (`created_by_id`),
KEY `index_order_lines_on_order_id` (`order_id`),
KEY `index_order_lines_on_status` (`status`),
KEY `index_order_lines_on_updated_at` (`updated_at`),
KEY `index_order_lines_on_updated_by` (`updated_by_id`),
KEY `index_order_lines_on_order_id_and_position` (`order_id`,`position`),
KEY `index_order_lines_on_order_id_and_line_num` (`order_id`,`line_num`),
KEY `index_ol_on_header_id_reporting_total_savings_pct_created_at` (`order_id`,`reporting_total`,`savings_pct`,`created_at`),
KEY `index_ol_on_order_id_supplier_id_reporting_total` (`order_id`,`supplier_id`,`reporting_total`),
KEY `index_ol_on_order_id_commodity_id_reporting_total_ela_id` (`order_id`,`commodity_id`,`reporting_total`,`extra_line_attribute_id`),
KEY `index_order_lines_on_allocation_count_and_account_id` (`allocation_count`,`account_id`),
KEY `index_order_lines_on_account_id` (`account_id`),
KEY `index_order_lines_on_allocation_count` (`allocation_count`),
KEY `index_ol_on_acc_id_alloc_count_order_id` (`account_id`,`allocation_count`,`order_id`),
KEY `index_ol_on_order_id_account_id_alloc_count` (`order_id`,`account_id`,`allocation_count`)

order_line_allocations表索引

PRIMARY KEY (`id`),
KEY `index_order_line_allocations_on_account_id` (`account_id`),
KEY `index_order_line_allocations_on_account_type_id` (`account_type_id`),
KEY `index_order_line_allocations_on_order_id` (`order_id`),
KEY `index_order_line_allocations_on_order_line_id` (`order_line_id`)

accounts表索引

PRIMARY KEY (`id`),
KEY `index_accounts_on_account_type_id_and_active` (`account_type_id`,`active`),
KEY `index_accounts_on_segment_1` (`segment_1`),
KEY `index_accounts_on_segment_10` (`segment_10`),
KEY `index_accounts_on_segment_11` (`segment_11`),
KEY `index_accounts_on_segment_12` (`segment_12`),
KEY `index_accounts_on_segment_13` (`segment_13`),
KEY `index_accounts_on_segment_14` (`segment_14`),
KEY `index_accounts_on_segment_15` (`segment_15`),
KEY `index_accounts_on_segment_16` (`segment_16`),
KEY `index_accounts_on_segment_17` (`segment_17`),
KEY `index_accounts_on_segment_18` (`segment_18`),
KEY `index_accounts_on_segment_19` (`segment_19`),
KEY `index_accounts_on_segment_2` (`segment_2`),
KEY `index_accounts_on_segment_20` (`segment_20`),
KEY `index_accounts_on_segment_3` (`segment_3`),
KEY `index_accounts_on_segment_4` (`segment_4`),
KEY `index_accounts_on_segment_5` (`segment_5`),
KEY `index_accounts_on_segment_6` (`segment_6`),
KEY `index_accounts_on_segment_7` (`segment_7`),
KEY `index_accounts_on_segment_8` (`segment_8`),
KEY `index_accounts_on_segment_9` (`segment_9`),
KEY `index_accounts_on_account_type_id` (`account_type_id`)

执行计划

慢查询执行计划

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: index
possible_keys: index_orders_on_account_type_id,index_orders_on_line_count
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_lines
   partitions: NULL
         type: ref
possible_keys: index_order_lines_on_order_id,index_order_lines_on_order_id_and_position,index_order_lines_on_order_id_and_line_num,index_ol_on_header_id_reporting_total_savings_pct_created_at,index_ol_on_order_id_supplier_id_reporting_total,index_ol_on_order_id_commodity_id_reporting_total_ela_id,index_ol_on_order_id_account_id_alloc_count
          key: index_ol_on_order_id_commodity_id_reporting_total_ela_id
      key_len: 4
          ref: perf_amazon_qas1481_utf8mb4.orders.id
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_line_allocations
   partitions: NULL
         type: ref
possible_keys: index_order_line_allocations_on_order_line_id
          key: index_order_line_allocations_on_order_line_id
      key_len: 5
          ref: perf_amazon_qas1481_utf8mb4.order_lines.id
         rows: 3
     filtered: 100.00
        Extra: NULL
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: sec_accounts
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,index_accounts_on_account_type_id_and_active,index_accounts_on_segment_1,index_accounts_on_account_type_id
          key: PRIMARY
      key_len: 4
          ref: perf_amazon_qas1481_utf8mb4.order_lines.account_id
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: sec_accounts
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,index_accounts_on_account_type_id_and_active,index_accounts_on_segment_1,index_accounts_on_account_type_id
          key: PRIMARY
      key_len: 4
          ref: perf_amazon_qas1481_utf8mb4.order_line_allocations.account_id
         rows: 1
     filtered: 100.00
        Extra: Using where

快查询执行计划

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: index
possible_keys: index_orders_on_account_type_id,index_orders_on_line_count
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_lines
   partitions: NULL
         type: ref
possible_keys: index_order_lines_on_order_id,index_order_lines_on_order_id_and_position,index_order_lines_on_order_id_and_line_num,index_ol_on_header_id_reporting_total_savings_pct_created_at,index_ol_on_order_id_supplier_id_reporting_total,index_ol_on_order_id_commodity_id_reporting_total_ela_id,index_ol_on_order_id_account_id_alloc_count
          key: index_ol_on_order_id_commodity_id_reporting_total_ela_id
      key_len: 4
          ref: perf_amazon_qas1481_utf8mb4.orders.id
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_line_allocations
   partitions: NULL
         type: ref
possible_keys: index_order_line_allocations_on_order_line_id
          key: index_order_line_allocations_on_order_line_id
      key_len: 5
          ref: perf_amazon_qas1481_utf8mb4.order_lines.id
         rows: 3
     filtered: 100.00
        Extra: NULL
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: sec_accounts
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,index_accounts_on_account_type_id_and_active,index_accounts_on_segment_1,index_accounts_on_account_type_id
          key: PRIMARY
      key_len: 4
          ref: perf_amazon_qas1481_utf8mb4.order_lines.account_id
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: sec_accounts
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,index_accounts_on_account_type_id_and_active,index_accounts_on_segment_1,index_accounts_on_account_type_id
          key: PRIMARY
      key_len: 4
          ref: perf_amazon_qas1481_utf8mb4.order_line_allocations.account_id
         rows: 1
     filtered: 100.00
        Extra: Using where

优化方案

1. 反转查询逻辑：从账号驱动订单查询

原查询从订单表全量遍历，再关联订单行和账号判断，对于匹配账号少的场景会处理大量无关数据。改为先筛选目标账号，再反向关联订单行和订单，直接缩小数据范围：

SELECT DISTINCT o.id
FROM accounts a
LEFT JOIN order_lines ol ON a.id = ol.account_id AND ol.allocation_count = 0
LEFT JOIN order_line_allocations ola ON a.id = ola.account_id
LEFT JOIN orders o ON ol.order_id = o.id OR ola.order_id = o.id OR (o.line_count = 0 AND o.account_type_id IS NULL)
WHERE a.account_type_id = 344 AND a.segment_1 = 'MS'
   OR (o.line_count = 0 AND o.account_type_id IS NULL)
ORDER BY o.id ASC LIMIT 90;

2. 补充accounts表联合索引

当前accounts表没有针对account_type_id + segment_1的联合索引，导致账号筛选无法快速定位数据，创建以下索引：

CREATE INDEX idx_accounts_type_segment1 ON accounts(account_type_id, segment_1);

3. 合并重复子查询

原查询两次重复筛选相同账号，用CTE复用结果，减少重复计算：

WITH valid_accounts AS (
    SELECT id FROM accounts WHERE account_type_id = 344 AND segment_1 = 'MS'
)
SELECT `orders`.`id` FROM `orders` 
LEFT JOIN `order_lines` ON `order_lines`.`order_id` = `orders`.`id` 
LEFT JOIN order_line_allocations ON order_line_allocations.order_line_id = order_lines.id
LEFT JOIN valid_accounts non_alloc_accounts ON non_alloc_accounts.id = order_lines.account_id AND order_lines.allocation_count = 0
LEFT JOIN valid_accounts alloc_accounts ON alloc_accounts.id = order_line_allocations.account_id 
WHERE (`orders`.line_count = 0 OR
       `orders`.account_type_id IS NULL OR
       non_alloc_accounts.id IS NOT NULL OR
       alloc_accounts.id IS NOT NULL
) 
ORDER BY `orders`.`id` ASC LIMIT 90;

注：orders.account_type_id IN (NULL)等价于orders.account_type_id IS NULL，已合并简化。

4. 利用UNION聚合有效订单ID

先聚合所有符合条件的订单ID，再排序取前90，避免大量关联判断：

WITH valid_accounts AS (
    SELECT id FROM accounts WHERE account_type_id = 344 AND segment_1 = 'MS'
),
valid_order_ids AS (
    SELECT ol.order_id FROM order_lines ol JOIN valid_accounts va ON ol.account_id = va.id AND ol.allocation_count = 0
    UNION
    SELECT ola.order_id FROM order_line_allocations ola JOIN valid_accounts va ON ola.account_id = va.id
    UNION
    SELECT id FROM orders WHERE line_count = 0 AND account_type_id IS NULL
)
SELECT id FROM valid_order_ids ORDER BY id ASC LIMIT 90;

核心优化逻辑

慢查询的本质是匹配账号数量少，但查询从订单表全量遍历，导致大量无关订单被关联判断。改为从账号出发反向关联订单，直接缩小数据范围；补充账号筛选的联合索引，提升账号查询效率；合并重复子查询减少计算开销，这些调整能显著降低查询耗时。

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

MySQL左外连接查询优化：特定账号条件查询过慢求助

问题背景 #

慢查询SQL #

现有索引配置 #

orders表索引 #

order_lines表索引 #

order_line_allocations表索引 #

accounts表索引 #

执行计划 #

慢查询执行计划 #

快查询执行计划 #

优化方案 #

1. 反转查询逻辑：从账号驱动订单查询 #

2. 补充accounts表联合索引 #

3. 合并重复子查询 #

4. 利用UNION聚合有效订单ID #

核心优化逻辑 #