我在一个表上使用CakePHP的Tree behavior来Map文件系统结构。实际的文件保存在一个与Amazon S3兼容的系统上,SQL表模拟文件夹结构,在文件的键中为每个“文件夹”分配一行。随着时间的推移,这个表已经增长到包含700K+行。当试图添加一个新文件夹或删除一个空文件夹时,将执行两个类似以下的查询:
UPDATE sys_files SET lft = ((lft - 2)) WHERE (lft > 2443)
UPDATE sys_files SET rght = ((rght - 2)) WHERE (rght > 2443)
每一个都需要超过30秒的时间来运行,这给用户带来了非常不愉快的体验,用户需要等待一分钟或更长时间才能完成操作。有时,HTTP调用只是超时,这更糟糕。
发生这种情况的原因是,这些查询几乎更新了(非常大的)表中的每一行,如这些结果所示。
mysql> SELECT COUNT(*) FROM sys_files;
+----------+
| COUNT(*) |
+----------+
| 713259 |
+----------+
1 row in set (0.09 sec)
mysql> SELECT COUNT(*) FROM sys_files WHERE lft > 2443;
+----------+
| COUNT(*) |
+----------+
| 704520 |
+----------+
1 row in set (0.11 sec)
mysql> SELECT COUNT(*) FROM sys_files WHERE rght > 2443;
+----------+
| COUNT(*) |
+----------+
| 704546 |
+----------+
1 row in set (0.11 sec)
我试过在lft
和rght
字段上添加索引,但没有什么帮助,因为缓慢的部分是700K+行的实际更新,而不是更新查询的WHERE条件。而且,添加索引并没有使性能明显变差,所以我并不担心保留它还是不保留它...问题是更新本身,带或不带索引。
2条答案
按热度按时间kpbwa7wx1#
我尝试过在
lft
和rght
字段上添加索引,但没有帮助,因为缓慢的部分是700K+行的实际更新,没有更新查询的WHERE条件。可能发生的情况是,在您更新列
lft
或rght
之后,索引必须重新创建自身。我们可以尝试使用复制表(sf_copy)上的索引,这样MySQL就不会在每个
UPDATE
上重新创建索引。示例:
然后,在
sys_files_copy
表(lft
和rght
列)上重新创建索引。然后尝试以下操作:
(请注意
sc.id = sf.id
部分。这对于进行JOIN非常重要)uxhixvfz2#
(This可能无法实施,因为第三方软件包需要控制。)
对于一个700 K行的表,这可能会运行得快得多,但对于一个10行的表,或者如果
2443
接近表的“末尾”,这就不是很快了。