如何优化数千行数据场景下的MySQL JOIN查询执行速度

性能骤降的核心原因是MySQL优化器选择了错误的关联执行顺序：

• 执行计划选择了t2作为驱动表，先遍历全表378条预估行，对每一行的id值，去t1的i1索引匹配data='something'且fkey = t2.id的记录，总匹配次数达到数百次量级，实际数据量更大的情况下就会出现严重耗时。
• 你的实际数据分布下，t1匹配data='something'的结果集只有1-2个不同的fkey，如果反过来用t1作为驱动表，仅需要关联t21-2次即可完成查询，性能会回归到毫秒级。

方案1：强制指定关联顺序（最简单高效，直接符合预期逻辑） #

使用STRAIGHT_JOIN强制让t1作为驱动表，语句修改如下：

SELECT STRAIGHT_JOIN `t1`.`id` FROM `t1` 
INNER JOIN `t2` 
   ON `t2`.`id`=`t1`.`fkey` 
   AND `t2`.`enabled`=1 
WHERE `t1`.`data`='something';

这个写法会强制MySQL先执行t1的条件过滤，再用过滤后得到的少量fkey去关联t2，完全符合你预期的执行逻辑。

方案2：优化索引帮助优化器做出正确选择 #

把t1上的i1索引从单列索引(data)调整为联合索引(data, fkey)：

ALTER TABLE t1 DROP INDEX i1, ADD INDEX i1(data, fkey);

调整后：

1. 查询data='something'时可以直接从索引中获取fkey值，不需要回表
2. 优化器可以更准确的统计到匹配结果的fkey基数极低，大概率会自动选择t1作为驱动表，不需要强制指定关联顺序

方案3：改写为半连接逻辑 #

如果你不想强制指定关联顺序，也可以改写为IN子查询的形式：

SELECT `id` FROM `t1` 
WHERE `data`='something'
AND `fkey` IN (SELECT `id` FROM `t2` WHERE `enabled`=1);

MySQL 8.0及以上版本的优化器对这类半连接的优化非常成熟，也会自动选择更高效的执行路径。

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