sql - MySQL:优化大表中多行的更新

Question

该表是使用 nested set model 组织的.当我插入一些东西时，我需要将所有具有左/右值>目标的东西移动到左边。

UPDATE projects SET rgt = rgt + 2 WHERE rgt >= @superRgt;

此查询可能需要几秒钟才能完成，这是 Not Acceptable 。我的问题是；如何优化此查询？有没有可能..

• 对表的物理布局进行规范化/碎片整理/重新排序？
• 创建更好的索引？
• 只是变得更聪明并避免问题？

我们正在使用 Innodb 表，并且已经有左、右和左/右索引。该表有大约 10 万行。

Answer 1

没有 Elixir ，但有一些想法......

我知道它被提出了很多，我从未见过表的物理布局改变 MySQL 对真实工作负载的性能(具有任何意义)。

虽然索引可以加快查询速度，但索引过多会减慢更新速度，因为索引需要反射(reflect)数据的更新。请注意，您的 left 索引实际上是无关紧要的，因为它是 left/right 索引上的前导字段。话虽如此，由于您通常可能会使用范围查询，因此 left 和 right 上的索引可能就足够了(也就是说，我可能倾向于删除 left/right 索引，除非你知道它正在被使用)。笼统地说，如果前面的所有列都用于相等引用，MySQL 只能使用复合索引的后面部分。

无论如何，为了加快查询的执行速度，您的左/右字段是否有可能取负值？如果是这种情况，那么您可以向左或向右“移动”枢轴点较小一侧的数据——无论哪个都会导致更新的行数较少。

请注意，如果您更新的行太多，MySQL 根本不会使用索引。有一个表行百分比的启发式阈值(通常报告为 ~30%)，超过该阈值后 MySQL 将拒绝使用索引。也就是说，在某个时刻，最好进行一次磁盘搜索并扫描整个表，而不是对表中 >30% 的行进行磁盘搜索。

回到基础，您是否偏离了(糟糕的)默认 innodb 配置设置？参见 this article一些指针。至少，请确保您的数据集适合 innodb_buffer_pool_size(如果您有 RAM)，并更改 innodb_flush_log_at_trx_commit如果您的应用程序允许，则为 0 或 2。