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

Question

我有一个应用程序，它需要更新分层结构中的节点，从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语句的解释结果，我发现检查"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 table的缩写版本：

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

我没有尝试添加综合索引(实际上，我没有当场添加该索引所需的访问级别)；但我不认为这会有什么帮助，我试图思考数据库引擎将如何尝试解决双重不平等问题。

Answer 1

您可以通过将拆分的第一部分作为子查询，然后将其用作派生表并连接到表A，从而“强制”(至少到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树索引的效率。

Answer 2

我想你最大的性能问题是你正在使用不需要的JOIN。你可以只做两个小的子查询，而不是连接两个大的表。

示例如下：

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

Answer 3

这只是一个建议，我不知道它是否行得通。

您的查询的问题是您在两列上存在不等式。这使得为它们使用索引变得非常困难--这反过来又使join非常低效。这个想法是执行两个连接，每个连接分别对应于不等式的每一边，然后在on条件中包含id。因此，只有同时通过这两个节点的节点才会通过：

UPDATE node a JOIN 
      (SELECT lft, rgt
       FROM node
       WHERE id = ? 
      ) l
      ON a.lft <= l.lft  join
      (SELECT lft, rgt
       FROM node
       WHERE id = ? 
      ) r
      on a.rgt >= r.rgt
    SET a.count = a.count + 1 ; 

正如我所说的，我不知道这是否会奏效。但是，您应该能够很容易地检查查询的explain，以查看计划是否对这两个不等式都使用了索引。