MySQL嵌套内连接慢查询优化求助（非索引方案）

问题背景 #

原查询语句：

select count(*) from transaction_entry 
inner join inventory_item on inventory_item_id = inventory_item.id
inner join transaction on transaction_id = transaction.id
inner join product on inventory_item.product_id = product.id

添加product表连接后，查询耗时从5秒飙升至40秒。单独执行transaction_entry+inventory_item+product或transaction_entry+transaction的查询速度都很快，但同时连接transaction_entry+transaction+inventory_item时性能骤降。各表ID字段已配置索引，现需非索引类优化方案。

执行计划分析 #

当前执行计划显示查询从inventory_item表启动（全索引扫描73470行），依次关联product、transaction_entry（每行inventory_item对应21行transaction_entry）、transaction。这种执行顺序会先扫描小表再放大结果集，产生大量中间数据，是性能瓶颈的核心原因。

非索引类优化方案 #

1. 强制调整连接顺序，从大表切入 #

MySQL优化器可能选择了低效的连接顺序，可通过STRAIGHT_JOIN强制以最大的transaction_entry表为起点，减少中间结果集规模：

select count(*) 
from transaction_entry 
STRAIGHT_JOIN transaction on transaction_entry.transaction_id = transaction.id
STRAIGHT_JOIN inventory_item on transaction_entry.inventory_item_id = inventory_item.id
STRAIGHT_JOIN product on inventory_item.product_id = product.id;

原理：从最大的transaction_entry表出发，通过关联字段直接过滤匹配的transaction和inventory_item行，避免先扫小表再放大结果的低效逻辑。

2. 子查询预聚合，削减关联数据量 #

先预计算transaction_entry与transaction、inventory_item的有效关联结果，再关联product：

select count(*)
from (
    select te.inventory_item_id
    from transaction_entry te
    join transaction t on te.transaction_id = t.id
    join inventory_item ii on te.inventory_item_id = ii.id
) as temp
join product p on exists (select 1 from inventory_item ii where ii.id = temp.inventory_item_id and ii.product_id = p.id);

原理：通过子查询先筛选出符合条件的transaction_entry行，缩小后续关联的数据范围。

3. 调大InnoDB缓冲池 #

当前innodb_buffer_pool_size仅128MB，远小于transaction_entry（994MB）和transaction（504MB）的总数据量，导致大量磁盘IO。建议根据服务器内存情况调整为内存的50%-70%：

innodb_buffer_pool_size = 2G  # 示例值，8G内存服务器可设为4G-5G

原理：让更多热数据缓存到内存，大幅减少磁盘读取次数，提升关联查询效率。

4. 用EXISTS替代JOIN，简化计数逻辑 #

原查询仅需计数，无需返回具体字段，可通过EXISTS判断存在性，减少数据传递：

select count(*)
from transaction_entry te
where exists (select 1 from transaction t where t.id = te.transaction_id)
  and exists (select 1 from inventory_item ii where ii.id = te.inventory_item_id)
  and exists (select 1 from product p join inventory_item ii on ii.product_id = p.id where ii.id = te.inventory_item_id);

原理：EXISTS仅验证匹配关系，不返回完整行数据，降低中间结果集的内存占用。

5. 临时表存储中间结果 #

将transaction_entry与transaction的关联结果存入临时表，再与其他表关联：

create temporary table temp_te_trans (
    entry_id char(38) primary key
) engine=innodb;

insert into temp_te_trans
select te.id
from transaction_entry te
join transaction t on te.transaction_id = t.id;

select count(*)
from temp_te_trans tt
join inventory_item ii on tt.entry_id = ii.id
join product p on ii.product_id = p.id;

drop temporary table temp_te_trans;

原理：临时表数据优先存储在内存（内存足够时），且结构简单，关联时性能更优。

6. 显式指定字符集，避免隐式转换 #

所有ID字段为char(38)，查询时显式指定字符集匹配，防止隐式转换损耗性能：

select count(*) 
from transaction_entry te
join transaction t on te.transaction_id = t.id collate utf8mb3_general_ci
join inventory_item ii on te.inventory_item_id = ii.id collate utf8mb3_general_ci
join product p on ii.product_id = p.id collate utf8mb3_general_ci;

原理：确保关联时字符集一致，避免MySQL自动进行字符集转换带来的额外开销。

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