MySQL：硬编码外键ID与关联查询的性能差异疑问

我将基于外键的关联替换为硬编码值后，直觉上查询速度应该相当甚至更快，但实际第一个查询比第二个慢30-40秒，想了解原因。

第一个查询（硬编码fk_id=13） #

SELECT date_column, currency, SUM(IF(key = 1,amount *-1, amount * 1)) AS amt
FROM main_table 
WHERE ((x = "123" and y in ("a","b"))
     OR (x = "234" and y in("a")))

AND amount is NOT NULL 
AND fk_id = 13
AND code IN ("xyz","xy")
GROUP BY date_column, currency   

执行计划 #

id select_type  table   partitions  type    possible_keys   key     key_len     ref     rows        filtered    Extra           
1   SIMPLE      table1  NULL        ref     fk_id           fk_id       5       const   29234242        0.54    Using index condition; Using where; Using temporary; Using filesort     

第二个查询（关联lookup表） #

SELECT main_table.date_column, main_table.currency, SUM(IF(main_table.key = 1,main_table.amount *-1, main_table.amount * 1)) AS amt
FROM main_table 
INNER JOIN lookup  
ON main_table.fk_id = lookup.pk_id
WHERE ((main_table.x = "123" and main_table.y in ("a","b"))
     OR (main_table.x = "234" and main_table.y in("a")))

AND main_table.amount is NOT NULL 
AND lookup.name LIKE "%test%"
AND main_table.code IN ("xyz","xy")
GROUP BY main_table.date_column, main_table.currency

执行计划 #

id select_type  table       partitions  type    possible_keys   key         key_len     ref             rows        filtered    Extra           
1   SIMPLE      lookup          NULL    index   PRIMARY         unique_key  416         NULL            13          11.11       Using where;Using index;Using temporary;Using filesort 
1   SIMPLE      main_table      NULL    ref     fk_id           fk_id       5           lookup.pk_id    7308560     0.54        Using where;        

关键背景信息 #

• main_table约6100万行，lookup_table仅13行
• main_table.fk_id与lookup.pk_id关联覆盖全表6100万行
• lookup.name LIKE "%test%"仅筛选出1行，对应main_table中1688万行（与fk_id=13的行数一致）
• main_table.fk_id为非唯一索引，lookup.pk_id是主键（唯一索引）
• lookup.name属于首字段为name的唯一复合索引
• main_table.currency是某非唯一复合索引的第二个字段
• 其他列无索引
• 执行前清空Handler_read_*计数后，两个查询读取行数几乎相同（约1688万行），第二个仅多读取13行
• 使用MySQL 5.7，InnoDB存储引擎

性能差异原因分析 #

1. 驱动表选择与数据访问模式差异 #

第一个查询直接以main_table作为驱动表，通过fk_id索引获取所有匹配fk_id=13的行，之后再回表过滤x/y/code/amount等条件。这种方式下，InnoDB需要先批量加载大量索引条目，再逐个回表读取聚簇索引数据，缓存命中率较低，大量随机IO的开销较高。

第二个查询则先以极小的lookup表作为驱动表：

• 先通过lookup的unique_key复合索引（包含name字段）快速筛选出1行匹配数据，这一步仅需扫描13行，几乎无开销
• 再以lookup的pk_id作为关联条件，去main_table的fk_id索引中匹配数据。这种小表驱动大表的嵌套循环连接模式，让MySQL可以更高效地分批访问main_table的索引与数据，缓存利用效率更高，随机IO压力被分散。

2. 优化器统计信息偏差影响执行路径 #

第一个查询的执行计划中，优化器预估fk_id=13的行数为2923万，远高于实际的1688万，统计信息偏差可能让优化器错误预估过滤后的行数，导致内存分配、排序/分组的资源调度不合理。

而第二个查询中，lookup表的统计信息非常准确（仅13行），优化器可以精准判断先处理lookup表的成本极低，进而选择更高效的关联执行路径。

3. 分组排序的开销时机差异 #

两个查询都需要执行GROUP BY date_column, currency，因此都会触发Using temporary和Using filesort。但第一个查询中，这些操作是在过滤完所有1688万行之后执行；而第二个查询的分组排序逻辑，是在嵌套循环连接的过程中逐步处理数据，MySQL可以利用连接过程中的数据局部性，减少临时表的IO开销。

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