mysql - 复杂的mysql更新查询速度慢

Question

我有一个包含用户数据的导入表，我需要标记具有重复字段值的行，因为它们不应被导入。

CREATE TABLE `import` (
  ID int(10) unsigned NOT NULL AUTO_INCREMENT,
  method varchar(20) DEFAULT NULL,
  f1 text,
  f2 text,
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

f1 字段可以包含重复值。选择它们的查询有效:

SELECT id, a.f1 FROM import a INNER JOIN
(
    SELECT f1 FROM import
    WHERE f1 IS NOT NULL AND f1 != ''
    GROUP BY f1
    HAVING COUNT(id) > 1
) b
ON a.f1 = b.f1

问题是执行更新的外部查询。这是整个 shebang:

UPDATE import SET method = 'ERR_DUPLICATE' WHERE import.id IN
(
    SELECT id FROM
    (
        SELECT id, a.f1 FROM import a INNER JOIN
        (
            SELECT f1 FROM import
            WHERE f1 IS NOT NULL AND f1 != ''
            GROUP BY f1
            HAVING COUNT(id) > 1
        ) b
        ON a.f1 = b.f1
    ) c
)

该构造来自 MySQL: You can't specify target table 'tasks' for update in FROM clause - 这是我之前得到的错误。上面的查询有效但需要 0.5 秒。对于具有大约 30 个重复项的 20,000 行表。我将不得不处理更大的导入表，所以这是一个阻碍。

有什么办法可以加快速度吗？

Answer 1

试试这个修改后的版本:

CREATE TEMPORARY TABLE duplicate_ids 
        SELECT MAX(id) AS id FROM import 
        WHERE f1 IS NOT NULL AND f1 != ''
        GROUP BY f1 ORDER BY NULL
        HAVING COUNT(*) > 1;
UPDATE import SET method = 'ERR_DUPLICATE' WHERE import.id IN(
      SELECT id FROM duplicate_ids
);

这将为您提供更大的 ID 记录作为副本。 ORDER BY NULL 抑制了由于分组而导致的隐式排序。此外，由于使用 TEXT 列的条件和 GROUPing 效率低下，您可以维护一个额外的列，该列将包含 f1 中文本的哈希码。

ALTER TABLE import ADD COLUMN f1_hash INT UNSIGNED NOT NULL;
ALTER TABLE import ADD KEY(f1_hash);

f1_hash 由 CRC32(f1) (http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_crc32) 返回的值填充。CRC32 可能会发生冲突，因此您最终必须检查 f1 列。

CREATE TEMPORARY TABLE duplicate_ids 
SELECT i2.id FROM import i1 JOIN import i2 
ON i2.id<>i1.id AND i1.f1_hash = i2.f2_hash   
AND i1.f1_hash > 0 WHERE i1.f1 = i2.f1

然后像以前一样执行更新。您当然不需要在 f1 列上使用 INDEX，因此最好删除它，因为它会增加不必要的开销。