带LIMIT的MySQL JOIN查询性能差异问题排查与优化

一、性能差异产生的核心原因 #

两个查询仅差一个currency.currency字段，但执行计划和性能天差地别，本质是MySQL优化器成本估算偏差+索引覆盖性变化导致执行路径完全不同：

1. 第一个查询（仅取currency.currency_id）的高效执行路径 #

• 利用invoice的PRIMARY索引（天然按invoice_id排序）直接读取最后30条数据，无需额外排序
• 每条invoice数据通过currency_id关联currency时，仅需读取currency的PRIMARY索引（覆盖currency_id字段），属于覆盖索引查询，无需回表读取数据页
• 总操作仅涉及30次invoice行读取+30次currency索引查找，几乎无额外开销，执行速度极快

2. 第二个查询（新增currency.currency字段）的低效执行路径 #

• 当需要获取currency.currency时，currency的PRIMARY索引不再是覆盖索引，必须回表读取数据页获取该字段
• MySQL优化器错误估算成本：认为沿用第一个查询的嵌套循环路径，30次回表的成本高于先扫描所有invoice数据（6万+条）、通过哈希关联currency、再排序取前30的成本
• 实际执行时，哈希关联需要扫描所有invoice的FK_invoice_currency索引（6万+条），之后对6万+条结果做排序，最后取前30，导致大量IO和CPU开销，性能骤降

二、查询优化方案 #

方案1：给currency表添加覆盖索引 #

创建包含currency_id和currency的联合索引，让第二个查询也能使用覆盖索引，避免回表：

CREATE INDEX idx_currency_id_currency ON currency(currency_id, currency);

添加后，优化器会自动选择和第一个查询类似的高效路径：读取invoice的30条数据，再通过覆盖索引关联currency，无需全表扫描和排序。

方案2：强制指定invoice走PRIMARY索引 #

通过FORCE INDEX提示优化器优先使用PRIMARY索引，保持嵌套循环的高效路径：

SELECT invoice.invoice_id, currency.currency_id, currency.currency
FROM invoice FORCE INDEX (PRIMARY)
INNER JOIN currency ON (currency.currency_id = invoice.currency_id) 
ORDER BY invoice.invoice_id DESC
LIMIT 30;

即使需要回表读取currency.currency，由于currency仅3条数据，回表成本极低，远低于全表扫描和排序的开销。

方案3：改写查询逻辑，先取小数据集再关联 #

通过子查询先获取30条invoice数据，再关联currency表，强制优化器优先处理小数据集：

SELECT t.invoice_id, c.currency_id, c.currency
FROM (
    SELECT invoice_id, currency_id 
    FROM invoice 
    ORDER BY invoice_id DESC 
    LIMIT 30
) t
INNER JOIN currency c ON c.currency_id = t.currency_id;

子查询会快速读取30条invoice数据，再关联currency，从根源避免全表扫描和排序操作。

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