MySQL基础LEFT JOIN查询速度极慢，求原因分析

问题概述 #

执行一条简化的LEFT JOIN查询耗时2.9秒，实际业务中的完整查询耗时会更久；但将该查询改为INNER JOIN后，仅需0.005秒即可返回结果。已找到替代查询方案，但需明确LEFT JOIN慢查询的根本原因。

表结构与数据情况 #

main_collection表（约2000条数据） #

表结构：

CREATE TABLE `main_collection` (
  `main_index` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id` varchar(50) DEFAULT NULL,
  `service` varchar(30) DEFAULT NULL,
  `title` varchar(130) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `publish_time` varchar(50) DEFAULT NULL,
  `channel` varchar(30) DEFAULT NULL,
  `level` varchar(30) DEFAULT NULL,
  `language` varchar(30) DEFAULT NULL,
  `language_id` int(11) DEFAULT NULL,
  `native_only` tinyint(1) DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 0,
  `hidden` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`main_index`),
  UNIQUE KEY `id` (`id`,`service`)
) ENGINE=InnoDB AUTO_INCREMENT=60288 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 

数据示例：

+------------+
| main_index |
+------------+
|      37987 |
|       6967 |
|       4424 |
|      12647 |
|      11771 |
|       2569 |
|       7352 |
|       7156 |
|      13760 |
|       6666 |
+------------+

mc_revs_language_id表（约12000条数据） #

表结构：

CREATE TABLE `mc_revs_language_id` (
  `index` int(11) NOT NULL AUTO_INCREMENT,
  `row_index` int(11) DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  `mw_id` int(11) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`index`)
) ENGINE=InnoDB AUTO_INCREMENT=43763 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

数据示例：

+-----------+-------+
| row_index | value |
+-----------+-------+
|     11771 |     1 |
|      7352 |     1 |
|     50605 |    61 |
|     50605 |    63 |
|     50605 |    61 |
|     11771 |    62 |
|     50605 |    16 |
|         3 |    16 |
|         3 |     2 |
|         3 |     2 |
+-----------+-------+

执行的查询语句 #

SELECT COUNT(`main_collection`.`main_index`) AS `cnt`
FROM `main_collection`
LEFT JOIN `mc_revs_language_id` ON mc_revs_language_id.row_index = main_collection.main_index

EXPLAIN执行计划 #

+------+-------------+---------------------+-------+---------------+------+---------+------+-------+-------------------------------------------------+
| id   | select_type | table               | type  | possible_keys | key  | key_len | ref  | rows  | Extra                                           |
+------+-------------+---------------------+-------+---------------+------+---------+------+-------+-------------------------------------------------+
|    1 | SIMPLE      | main_collection     | index | NULL          | id   | 326     | NULL | 10776 | Using index                                     |
|    1 | SIMPLE      | mc_revs_language_id | ALL   | NULL          | NULL | NULL    | NULL |  2182 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+---------------------+-------+---------------+------+---------+------+-------+-------------------------------------------------+

慢查询原因分析 #

1. 核心问题：关联字段无索引
   mc_revs_language_id表的row_index字段没有创建索引，导致JOIN时无法通过索引快速匹配main_collection的main_index，只能对mc_revs_language_id执行全表扫描（EXPLAIN中type为ALL，possible_keys为NULL）。

2. LEFT JOIN与INNER JOIN的执行逻辑差异

   • INNER JOIN：优化器可以选择先过滤出两张表中匹配的行，甚至利用驱动表的索引快速查找关联表的匹配数据，最终只保留两边都有匹配的记录，数据量小，执行效率高。
   • LEFT JOIN：必须保留main_collection的所有行，即使mc_revs_language_id中没有匹配的记录。优化器无法提前过滤数据，只能采用BNL（块嵌套循环）连接：将main_collection的行分批加载到连接缓冲区，然后对每一批行，全表扫描mc_revs_language_id查找匹配的row_index。当main_collection有2000行、mc_revs_language_id有12000行时，会产生大量匹配操作，导致耗时剧增。

3. 额外的索引选择问题
   EXPLAIN显示main_collection使用的是id唯一索引而非主键main_index，虽然标注了Using index（覆盖索引），但优化器选择的索引并非关联字段的主键，可能增加了不必要的IO开销，但这不是核心原因。

验证方案 #

给mc_revs_language_id的row_index字段添加索引后，LEFT JOIN的执行效率会大幅提升：

CREATE INDEX idx_row_index ON mc_revs_language_id(row_index);

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