mysql - 为什么这个update-with-join mysql查询这么慢？

我有一个应用程序需要更新一个层次结构中的节点，从一个已知id的特定节点向上。我使用以下mysql语句来执行此操作：

update node as A
join node as B
   on A.lft<=B.lft and A.rgt>=B.rgt
set A.count=A.count+1 where B.id=?

表的id上有一个主键，lft和rgt上有索引。这句话行得通，但我发现它有性能问题。查看对应select语句的explain结果，我发现检查“b”表的行数非常大（可能是整个表）。
我可以很容易地将查询分离为两个单独的查询：

select lft, rgt from node where id=?
LFT=result.lft
RGT=result.rgt
update node set count=count+1 where lft<=LFT and rgt>=RGT

但是，为什么最初的声明没有达到预期的效果，我需要如何重新制定才能更好地工作呢？
根据请求，下面是create表的缩写版本：

CREATE TABLE `node` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`lft` decimal(64,0) NOT NULL,
`rgt` decimal(64,0) NOT NULL,
`count` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `location` (`location`(255)),
KEY `lft` (`lft`),
KEY `rgt` (`rgt`),
) ENGINE=InnoDB

我没有尝试添加复合索引（实际上，我没有在现场执行该操作所需的访问级别）；但是我不知道它会有什么帮助，我试图思考数据库引擎将如何解决双重不平等。

最佳答案

您可以“强制”（至少5.5版，5.6版在优化器上有几个改进，这可能会使重写成为多余的）mysql首先评估表b上的条件，方法是将拆分的第一部分作为子查询，然后将其用作派生表并连接到表。答：

UPDATE node AS a
  JOIN
    ( SELECT lft, rgt
      FROM node
      WHERE id = ?
    ) AS b
    ON  a.lft <= b.lft
    AND a.rgt >= b.rgt
SET
    a.count = a.count + 1 ;

效率仍然取决于选择哪一个索引来限制要更新的行。在使用了这两个索引中的任何一个之后，仍然需要表查找来检查另一列。所以，我建议您在(lft, rgt)和(rgt, lft)上添加一个复合索引，这样就只使用一个索引来查找哪些行应该更新。
我假设您使用的是嵌套集，并且此更新在大表上的效率不会很高，因为查询有两个范围条件，这限制了b树索引的效率。