如何优化在100,000多个记录上运行的'col = col + 1'UPDATE查询? [英] How to optimize a 'col = col + 1' UPDATE query that runs on 100,000+ records?

问题描述

请参见前面的问题有一定的背景知识我正在尝试使用SQL重新编号损坏的MPTT树.该脚本在逻辑上运行良好，太慢了.

See this previous question for some background. I'm trying to renumber a corrupted MPTT tree using SQL. The script is working fine logically, it is just much too slow.

我反复需要执行以下两个查询:

I repeatedly need to execute these two queries:

UPDATE `tree`
SET    `rght` = `rght` + 2
WHERE  `rght` > currentLeft;

UPDATE `tree`
SET    `lft` = `lft` + 2
WHERE  `lft` > currentLeft;

该表是这样定义的:

CREATE TABLE `tree` (

  `id`        char(36) NOT NULL DEFAULT '',
  `parent_id` char(36) DEFAULT NULL,
  `lft`       int(11) unsigned DEFAULT NULL,
  `rght`      int(11) unsigned DEFAULT NULL,
  ... (a couple of more columns) ...,

  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  KEY `lft` (`lft`),
  KEY `rght` (`rght`),
  ... (a few more indexes) ...

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据库为MySQL 5.1.37.该表当前有约120,000条记录.两个UPDATE查询中的每个查询大约需要15到20秒来执行. WHERE条件可能适用于大多数记录，因此几乎所有记录都需要每次更新.在最坏的情况下，两个查询的执行次数与数据库中记录的执行次数一样.

The database is MySQL 5.1.37. There are currently ~120,000 records in the table. Each of the two UPDATE queries takes roughly 15 - 20 seconds to execute. The WHERE condition may apply to a majority of the records, so that almost all records need to be updated each time. In the worst case both queries are executed as many times as there are records in the database.

是否有一种通过将值保留在内存中，延迟写入磁盘，延迟索引更新或类似方式来优化此查询的方法?目前的瓶颈似乎是硬盘的吞吐量，因为MySQL似乎正在将所有内容立即写回到磁盘.

Is there a way to optimize this query by keeping the values in memory, delaying writing to disk, delaying index updates or something along these lines? The bottleneck seems to be hard disk throughput right now, as MySQL seems to be writing everything back to disk immediately.

任何建议表示赞赏.

推荐答案

我从没有使用过，但是如果您有足够的内存，请尝试

I never used it, but if your have enough memory, try the memory table.

创建具有与树相同结构的表，插入..中，从..中选择，针对内存表运行脚本，然后将其写回.

Create a table with the same structure as tree, insert into .. select from .., run your scripts against the memory table, and write it back.

这篇关于如何优化在100,000多个记录上运行的'col = col + 1'UPDATE查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！