MySql - 自加入 - 全表扫描（无法扫描索引） [英] MySql - Self Join - Full Table Scan (Cannot Scan Index)

问题描述

我有以下自我加入查询：

  SELECT A.id 
 FROM mytbl AS A 
 LEFT JOIN mytbl AS B 
 ON（A.lft BETWEEN B.lft AND B.rgt）
  

查询速度很慢，查看执行计划后，原因似乎是JOIN中的全表扫描。该表只有500行，并怀疑这是问题我将其增加到100,000行，以查看它是否对优化程序的选择产生了影响。
它没有，有100k行它仍在进行全表扫描。

我的下一步是尝试用以下查询强制索引，但是出现同样的情况，全表扫描：

  SELECT A.id 
 FROM categories_nested_set AS A 
 LEFT JOIN categories_nested_set AS B 
 FORCE INDEX（idx_lft，idx_rgt）
 ON（A.lft BETWEEN B.lft AND B.rgt）
  

所有列（id， lft，rgt）是整数，都被编入索引。

为什么MySql在这里进行全表扫描？

如何更改查询以使用索引而不是全表扫描？

  CREATE TABLE mytbl（lft int（11）NOT NULL DEFAULT'0'，
 rgt int（11）DEFAULT NULL，
 id int（11）DEFAULT NULL，
 category varchar（128）DEFAULT NULL，
 PRIMARY KEY（lft），
 UNIQUE KEY id（id），
 UNIQUE KEY rgt（rgt），
 KEY idx_lft（lft），
 KEY idx_rgt（rgt））ENGINE = InnoDB DEFAULT CHARSET = utf8 
  

谢谢

解决方案

以下SO问题对解决方案至关重要，因为关于邻接列表和索引的组合的信息非常少：

I have the following self-join query:

SELECT A.id
FROM mytbl      AS A
LEFT JOIN mytbl AS B 
ON (A.lft BETWEEN B.lft AND B.rgt)

The query is quite slow, and after looking at the execution plan the cause appears to be a full table scan in the JOIN. The table has only 500 rows, and suspecting this to be the issue I increased it to 100,000 rows in order to see if it made a difference to the optimizer's selection. It did not, with 100k rows it was still doing a full table scan.

My next step was to try and force indexes with the following query, but the same situation arises, a full table scan:

SELECT A.id
FROM categories_nested_set      AS A
LEFT JOIN categories_nested_set AS B 
FORCE INDEX (idx_lft, idx_rgt)
ON (A.lft BETWEEN B.lft AND B.rgt)

All columns (id, lft, rgt) are integers, all are indexed.

Why is MySql doing a full table scan here?

How can I change my query to use indexes instead of a full table scan?

CREATE TABLE mytbl ( lft int(11) NOT NULL DEFAULT '0', 
 rgt int(11) DEFAULT NULL, 
 id int(11) DEFAULT NULL,
 category varchar(128) DEFAULT NULL,
  PRIMARY KEY (lft), 
  UNIQUE KEY id (id), 
  UNIQUE KEY rgt (rgt), 
  KEY idx_lft (lft), 
  KEY idx_rgt (rgt) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Thanks

解决方案

The following SO question is critical to the solution, as there is very little information on the combination of adjacency lists and indices:

MySQL & nested set: slow JOIN (not using index)

It appears that adding a basic comparison condition triggers the use of an index, like so:

SELECT A.id
FROM mytbl      AS A
LEFT JOIN mytbl AS B ON (A.lft BETWEEN B.lft AND B.rgt)
-- THE FOLLOWING DUMMY CONDITIONS TRIGGER INDEX
WHERE A.lft > 0
AND B.lft > 0
AND B.rgt > 0

And no more table scans.

EDIT: Comparison of EXPLAIN function between fixed and unfixed version of the query:

这篇关于MySql - 自加入 - 全表扫描（无法扫描索引）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！