带有不使用索引的查询变量的 SELECT

我正在尝试（出于兴趣）通过使用局部变量的递归查询来检索简单邻接列表中的节点树。

到目前为止我的解决方案很有趣，但我想知道（这是我唯一的问题）为什么 MySQL 拒绝使用任何INDEX来优化这个查询。 MySQL 不应该能够通过使用INDEX?

我很好奇为什么 MySQL 不这样做。即使当我使用FORCE INDEX执行计划不会改变。

这是迄今为止的查询，其中5为父节点的ID：

SELECT 
  @last_id := id AS id,
  parent_id,
  name,
  @depth := IF(parent_id = 5, 1, @depth + 1) AS depth
FROM 
  tree FORCE INDEX (index_parent_id, PRIMARY, index_both),
  (SELECT @last_id := 5, @depth := -1) vars
WHERE id = 5 OR parent_id = @last_id OR parent_id = 5

尝试 SQLfiddle 的实时示例 http://sqlfiddle.com/#!2/73b4b/2

请注意，原因不可能是小数据集，因为当我指定时行为不会改变FORCE INDEX (id) or FORCE INDEX (parent_id) or FORCE INDEX (id, parent_id) ...

文档说：

您还可以使用 FORCE INDEX，其作用与 USE INDEX (index_list) 类似，但表扫描的开销被假定为非常昂贵。换句话说，仅当无法使用给定索引之一来查找表中的行时，才会使用表扫描。

一定有什么东西导致查询无法使用索引，但我不明白它是什么。

免责声明：我知道在 SQL 中存储和检索分层数据有不同的方法。我知道嵌套集模型。我并不是在寻找替代实现。我不是在寻找嵌套集。

我也知道查询本身是疯狂的并且会产生错误的结果。

我只是想（详细地）了解为什么 MySQL 不使用INDEX在这种情况下。

原因在于使用OR条件在WHERE clause.

为了说明这一点，请尝试再次运行查询，这次仅使用id = 5条件，并得到（解释输出）：

+----+-------------+------------+--------+--------------------+---------+---------+-------+------+----------------+
| id | select_type | table      | type   | possible_keys      | key     | key_len | ref   | rows | Extra          |
+----+-------------+------------+--------+--------------------+---------+---------+-------+------+----------------+
|  1 | PRIMARY     | <derived2> | system | NULL               | NULL    | NULL    | NULL  |    1 |                |
|  1 | PRIMARY     | tree       | const  | PRIMARY,index_both | PRIMARY | 4       | const |    1 |                |
|  2 | DERIVED     | NULL       | NULL   | NULL               | NULL    | NULL    | NULL  | NULL | No tables used |
+----+-------------+------------+--------+--------------------+---------+---------+-------+------+----------------+

再说一遍，这一次只有parent_id = @last_id OR parent_id = 5条件，并得到：

+----+-------------+------------+--------+-----------------+------+---------+------+------+----------------+
| id | select_type | table      | type   | possible_keys   | key  | key_len | ref  | rows | Extra          |
+----+-------------+------------+--------+-----------------+------+---------+------+------+----------------+
|  1 | PRIMARY     | <derived2> | system | NULL            | NULL | NULL    | NULL |    1 |                |
|  1 | PRIMARY     | tree       | ALL    | index_parent_id | NULL | NULL    | NULL |   10 | Using where    |
|  2 | DERIVED     | NULL       | NULL   | NULL            | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+------------+--------+-----------------+------+---------+------+------+----------------+

MySQL 不太擅长处理同一查询中的多个索引。 AND 条件的情况稍微好一些；人们更有可能看到索引合并 http://dev.mysql.com/doc/refman/5.1/en/explain-output.html#jointype_index_merge优化比索引联合 http://dev.mysql.com/doc/refman/5.1/en/index-merge-optimization.html#index-merge-union优化。

随着版本的进步，情况正在改善，但我已经测试过您对版本的查询5.5，这是当前最新的生产版本，结果如​​您所描述。

要解释为什么这很困难，请考虑：两个不同的索引将回答查询的两个不同条件。一位将回答id = 5，另一个为parent_id = @last_id OR parent_id = 5（顺便说一句，没有问题OR在后者内部，因为这两个术语都是在同一索引内处理的）。

没有一个指数可以同时回答这两个问题，因此FORCE INDEX指令被忽略。看，FORCE INDEX说MySQL必须使用an表扫描上的索引。这并不意味着它必须在表扫描中使用多个索引。

所以MySQL遵循这里文档的规则。但为什么这么复杂呢？因为要使用两个索引来回答，MySQL 必须从两个索引收集结果，将其中一个存储在某个临时缓冲区中，同时管理第二个索引。然后必须遍历该缓冲区以过滤掉相同的行（某些行可能适合所有条件）。然后扫描该缓冲区以返回结果。

但是等等，该缓冲区本身没有索引。过滤重复项并不是一项显而易见的任务。因此，MySQL 更喜欢在原始表上工作并进行扫描，从而避免所有混乱。

当然这是可以解决的。 Oracle的工程师可能还会对此进行改进（最近他们一直在努力改进查询执行计划），但我不知道这是否是在TODO任务上，或者是否具有高优先级。