MySQL自连接查询出现Range check未使用索引性能优化求助

你当前使用的是左右值编码的预排序遍历树结构，自连接的查询条件为双维度范围匹配，现有的单列node_left、node_right索引无法高效支撑这类查询：

• 单列索引每次只能过滤一个范围条件，剩余条件需要逐行过滤，MySQL优化器判定使用索引的收益低于全表扫描，因此触发Range checked for each record的低效逻辑
• 当前使用的嵌套循环连接需要对t1表的3.4万行数据，逐行触发t2表的范围扫描，累计扫描行数超过6.9亿，是耗时过高的核心原因
• 查询最终返回3249万行结果，结果集的传输、处理也占用了大量耗时

• 创建联合覆盖索引（优先级最高）
  针对t2表的匹配逻辑，创建包含node_left、node_right的联合覆盖索引，无需回表即可完成两个范围条件的匹配：

  CREATE INDEX idx_left_right ON tbl_organizer_tree(node_left, node_right);
  

  该索引可以直接定位node_left <= t1.node_left的行，同时在索引内直接判断node_right >= t1.node_left，查询效率会有10倍以上提升。

• 强制使用Hash Join
  MySQL 8.0.18及以上版本支持非等值连接的Hash Join算法，相比嵌套循环连接更适合这类大表非等值连接场景，可通过查询Hint强制启用：

  SELECT /*+ HASH_JOIN(t1 t2) */ t1.node_id 
  FROM tbl_organizer_tree AS t1 
  INNER JOIN tbl_organizer_tree AS t2 
  ON (t1.node_left >= t2.node_left AND t1.node_left <= t2.node_right);
  

  Hash Join仅需扫描两次表即可完成匹配，避免3.4万次循环扫描的开销。

• 业务层优化
  如果你是高频查询每个节点的祖先/后代节点，建议预计算结果存入中间表，定时更新，查询时直接读取中间表，无需每次执行自连接；如果不需要全量结果，尽量增加WHERE条件缩小查询范围，避免返回3000万+行的超大结果集。

• innodb_buffer_pool_size：调整为服务器物理内存的50%~70%，确保整张表和所有索引都能缓存到内存中，避免磁盘IO开销，例如8G内存的服务器可设置为5G。
• join_buffer_size：如果使用Hash Join，建议调整为256M~1G，避免哈希表溢写到磁盘，大幅提升Hash Join执行效率。
• range_optimizer_max_mem_size：设置为0（无限制），避免范围查询优化器因为内存限制放弃最优的索引范围扫描方案：

  SET GLOBAL range_optimizer_max_mem_size = 0;
  

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