Mysql范围检查而不是索引使用

Question

我有一个包含35000条记录的表。我必须加入它自己。我的问题是它没有使用已定义的索引。查看下面的分析查询结果：表格：

mysql> desc tbl_organizer_tree;
+------------------------+-------------------+------+-----+---------+----------------+
| Field                  | Type              | Null | Key | Default | Extra          |
+------------------------+-------------------+------+-----+---------+----------------+
| node_id             | int               | NO   | PRI | NULL    | auto_increment |
| node_title          | varchar(255)      | YES  | MUL | NULL    |                |
| node_hint           | varchar(1000)     | NO   |     | NULL    |                |
| node_left           | int               | YES  | MUL | NULL    |                |
| node_right          | int               | YES  | MUL | NULL    |                |
+------------------------+-------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

查询：

mysql> EXPLAIN SELECT 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)G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: index
possible_keys: node_left
key: node_left
key_len: 5
ref: NULL
rows: 33931
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ALL
possible_keys: node_left,node_right
key: NULL
key_len: NULL
ref: NULL
rows: 33931
filtered: 11.11
Extra: Range checked for each record (index map: 0x30)
2 rows in set, 1 warning (0.00 sec)

查询分析：

mysql> EXPLAIN ANALYZE SELECT t1.node_id  FROM tbl_organizer_tree AS t1 INNER JOIN tbl_organizer_tree AS t2 ON (t1.node_left >= t2.node_l
eft AND t1.node_left <= t2.node_right)G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=115134799.90 rows=127898053) (actual time=0.648..298146.405 rows=32491076 loops=1)
-> Covering index scan on t1 using node_left  (cost=3449.35 rows=33931) (actual time=0.181..69.022 rows=33901 loops=1)
-> Filter: ((t1.node_left >= t2.node_left) and (t1.node_left <= t2.node_right))  (cost=0.01 rows=3769) (actual time=0.087..8.741 rows=958 loops=33901)
-> Index range scan on t2 (re-planned for each iteration)  (cost=0.01 rows=33931) (actual time=0.086..7.326 rows=20455 loops=33901)
1 row in set (5 min 0.32 sec)

尽管node_left和node_right都被定义为表索引，但它们在查询执行过程中会被忽略，并且需要5分钟才能完成。

我正在寻找任何能提高性能的建议。甚至MySQL的配置和设置也是受欢迎的。

编辑：

• MySQL版本为8.0.27

Answer 1

您可以使用索引提示，强制MySQL(或MariaDB(使用任何上述索引：

SELECT t1.node_id  
FROM tbl_organizer_tree AS t1 
INNER JOIN tbl_organizer_tree AS t2 USE INDEX(node_left,node_right)
ON (t1.node_left >= t2.node_left AND t1.node_left <= t2.node_right)

如果这有助于提高查询速度，请告诉我。

Answer 2

假设您当前拥有

INDEX(node_left),
INDEX(node_right)

我建议你用替换

INDEX(node_left, node_right),
INDEX(node_right, node_left)

可能会在某些时候帮助。

我不建议使用索引提示——在某些情况下，它们往往会损害的性能。