如何优化MySQL查询以进行更新？

Question

我是一位有用的助手，可以为您进行翻译。

我有一个拥有300000条记录的表格。这个表格中有重复的行，我想要更新"flag"列。

表格：

------------------------------------
|number | flag | ... more column ...|
------------------------------------
|ABCD   |  0   | ...................|
|ABCD   |  0   | ...................|
|ABCD   |  0   | ...................|
|BCDE   |  0   | ...................|
|BCDE   |  0   | ...................|

我使用以下查询来更新“flag”列：

UPDATE table i 
INNER JOIN (SELECT number FROM table
            GROUP BY number HAVING count(number) > 1 ) i2
ON i.number = i2.number
SET i.flag = '1'

这个查询对于30万条记录非常缓慢（超过600秒）。

我该如何优化这个查询？

我的表结构如下：

CREATE TABLE IF NOT EXISTS `inv` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pn` varchar(10) NOT NULL COMMENT 'Part Number',
  `qty` int(5) NOT NULL,
  `qty_old` int(5) NOT NULL,
  `flag_qty` tinyint(1) NOT NULL,
  `name` varchar(60) NOT NULL,
  `vid` int(11) NOT NULL ,
  `flag_d` tinyint(1) NOT NULL ,
  `flag_u` tinyint(1) NOT NULL ,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `pn` (`pn`),
  KEY `name` (`name`),
  KEY `vid` (`vid`),
  KEY `pn_2` (`pn`),
  KEY `flag_qty` (`flag_qty`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

如果“name”重复，我想更新flag_qty。

- dido

---

表结构是什么？这个表上是否有主键可用？ - user1191247

设置这个标志的目的是什么？您是否根据此标志进行了进一步的处理？ - user1191247

这个标志的目的是，如果表中有重复行，则该标志为1。之后，我想创建一个带有重复行的表，并从主表中删除这些行。 - dido

所有其他列的数据也是重复的还是它们有不同的值？我问这些问题是因为我怀疑在你尝试做的事情中可能有更好的解决方案？这是一次性的去重过程还是定期发生的？你是如何出现重复的？如果你不想让number重复，为什么不将其设为主键（或至少有一个唯一键）？ - user1191247

其他列的数据不同。数字匹配，但是另一列（“...更多列...”）中的信息并没有重复。因此，我想更新字段标记。 - dido

Answer 1

如果您还没有在number上创建索引，那么您应该添加一个 -

CREATE INDEX table_number ON table (number);

更新 试试这个 -

UPDATE inv t1
INNER JOIN inv t2
    ON t1.name = t2.name
    AND t1.id <> t2.id
SET t1.flag_qty = 1;

你可以直接将这些重复的数据选择到另一个表中来创建你的表格，而不是先进行标记更新。

INSERT INTO duplicate_invs
SELECT DISTINCT inv1.*
FROM inv AS inv1
INNER JOIN inv AS inv2
    ON inv1.name = inv2.name
    AND inv1.id < inv2.id

如果你能解释哪些行从inv表中被删除，那么整个过程可能可以一步完成。

- nnichols

---

在这种情况下，请发布 SHOW INDEXES FROM table_name 的输出。我在测试中对3M行运行了您的查询，并在我的笔记本电脑上仅用1秒多就返回了结果。添加完整的表结构（从 SHOW CREATE TABLE table_name 的输出）可能会有所帮助。 - user1191247

我添加了我的表的SQL结构。 - dido

Answer 2

我会使用临时表。 1.) 将所有相关记录选择到临时表中，在id上设置INDEX索引。 2.) 使用类似于以下内容更新表

UPDATE table i, tmp_i
SET i.flag = '1'
WHERE i.id = tmp_i.id

- scibuff

Answer 3

你可以尝试（假设使用VB.net，但可以用任何语言实现）。

Dim ids As String =   Cmd.ExectueScalar("select group_concat(number) from (SELECT number FROM table  GROUP BY number HAVING count(number) > 1)")

在获取ID列表（逗号分隔）之后，使用

UPDATE i  
SET i.flag = '1' 
WHERE i.number in ( .... )

它可能也会慢，但首先- SELECT，不会锁定数据库和复制等，UPDATE会更快。

- Moshe L

Answer 4

让MySQL向您解释查询。然后您将看到哪些索引可以改善查询性能。

- user207421

Answer 5

EXPLAIN 会告诉你哪里出现了性能问题，以下是一些提高性能的想法：

• 添加索引
• 使用InnoDB外键
• 将查询拆分为两个部分，并在你使用的语言中分别处理它们。
• 在MySQL存储过程中编写相同的想法（不确定是否快速）。

- kirilloid