为何自关联Inner Join比单纯Where语句的SQL查询更快？

表结构 #

CREATE TABLE `cerp_oms_order` (
  `id` bigint NOT NULL,
  `company_id` bigint NOT NULL,
  `order_no` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `source_type` int NOT NULL,
  `shop_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `outer_shop` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `origin_status` int NOT NULL,
  `system_status` int NOT NULL,
  `created_time` datetime DEFAULT NULL,
  `paid_time` datetime DEFAULT NULL,
  `sent_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `modify_time` datetime DEFAULT NULL,
  `delivery_deadline_time` datetime DEFAULT NULL,
  `amount` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  `spu_kind` int NOT NULL,
  `sku_kind` int NOT NULL,
  `total_quantity` decimal(16,4) NOT NULL,
  `buyer_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `outer_buyer_identifier` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `tax_info` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `warehouse_owner` int DEFAULT NULL,
  `warehouse_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `logistics_type` int NOT NULL,
  `logistics_outer_info` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `delivery_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `delivery_no` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `wave_no` varchar(64) DEFAULT '',
  `is_deleted` tinyint NOT NULL DEFAULT '0',
  `backend_processing_type` tinyint NOT NULL,
  `create_type` tinyint NOT NULL,
  `is_hang_up` tinyint NOT NULL,
  `hang_up_case_type` smallint DEFAULT NULL,
  `hang_up_case_id` bigint DEFAULT NULL,
  `rc_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `rm_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `vat` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `weight` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `volume` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `is_abnormal` tinyint NOT NULL DEFAULT '0',
  `estimate_profit` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `business_man_id` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `business_man` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `currency` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `net_amount_summary` decimal(19,4) NOT NULL,
  `domestic_amount` decimal(19,4) NOT NULL,
  `secret_key` varchar(64) DEFAULT '',
  `secretKey` varchar(255) DEFAULT NULL,
  `sale_id` bigint DEFAULT NULL,
  `total_refund_include_tax` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `total_refund_money` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `total_refund_tax` decimal(16,4) NOT NULL DEFAULT '0.0000',
  `total_return_goods` decimal(16,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_order_no` (`order_no`),
  KEY `idx_order_company_id` (`company_id`,`created_time`),
  KEY `IDX_RM_TIME` (`rm_time`),
  KEY `IDX_IS_ABNORMAL` (`is_abnormal`),
  KEY `cerp_oms_order_company_id_index` (`company_id`),
  KEY `idx_order_company_status_deleted` (`company_id`,`is_deleted`,`system_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='system order table'

两条SQL的执行对比 #

使用Inner Join自关联的SQL #

explain select *
from cerp_oms_order
         inner join (select id
                     from cerp_oms_order
                     where source_type = 43
                     order by created_time) as tmp using (id);

执行计划：

执行耗时：execution: 7 ms, fetching: 109 ms

使用单纯Where语句的SQL #

explain
select *
from cerp_oms_order
where source_type = 43
order by created_time;

执行计划：

执行耗时：execution: 80 ms, fetching: 138 ms

性能差异的核心原因 #

两者的性能差距主要来自排序操作的开销差异：

1. 单纯Where语句的瓶颈：Using filesort
   这条SQL需要先全表扫描筛选出source_type=43的所有数据（包含全表所有字段），然后对这些完整的数据行按created_time排序。由于表中没有source_type + created_time的联合索引，MySQL无法利用索引完成排序，只能触发filesort——这是一种基于磁盘的排序操作，当需要排序的数据量较大（这里约6000行左右，全字段数据体积大）时，会产生大量IO和CPU开销，直接导致execution时间飙升。

2. 自关联SQL的优化：减少排序数据量+利用主键快速查找
   自关联的逻辑拆分了操作：

   • 子查询先筛选source_type=43的记录，但只返回id字段（数据量极小，仅8字节每行），然后对这些id按created_time排序。由于排序的数据量大幅减少，排序的开销极低。
   • 主表通过主键id与子查询的结果关联，eq_ref类型的主键查找是MySQL最快的查询方式，能直接定位到对应的完整数据行。
   • 最终结果的顺序由子查询排序后的id顺序决定，不需要再对全量数据做第二次排序，彻底避免了filesort的高额开销。

最优解决方案 #

创建联合索引idx_source_type_created_time (source_type, created_time)，这样无论使用哪种写法，MySQL都能直接通过索引筛选出符合条件的记录，同时利用索引的有序性避免排序，性能会比自关联写法更优。

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