為什麼 DELETE 比 SELECT 慢得多，然後按 id 刪除？

我有一個相當繁忙的 InnoDB 表（200,000 行，我猜每秒有幾十個查詢）。由於一個錯誤，我得到了 14 行（相同的）無效電子郵件地址，並想刪除它們。

我只是嘗試DELETE FROM table WHERE email='invalid address'並在大約 50 秒後得到“超過鎖定等待超時”。這並不奇怪，因為行列沒有被索引。

但是，然後我做到了SELECT id FROM table WHERE email='invalid address'，這花了 1.25 秒。執行DELETE FROM table WHERE id in (...)，從 SELECT 結果中複製粘貼 id，耗時 0.02 秒。

到底是怎麼回事？有人可以解釋為什麼帶有條件的 DELETE 太慢以至於超時，但是執行 SELECT 然後按 id 刪除卻如此之快？

謝謝。

**編輯：**應要求，我發布了表結構以及一些explain結果。我還應該注意，沒有引用該表的外鍵。

但是，情況對我來說似乎很簡單：我選擇了一個未索引的欄位。這需要掃描整個表，但不是很大。id是主鍵，所以按 id 刪除非常快，應該是這樣。

mysql> show create table ThreadNotification2 \G
*************************** 1. row ***************************
      Table: ThreadNotification2
Create Table: CREATE TABLE `ThreadNotification2` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `alertId` bigint(20) DEFAULT NULL,
 `day` int(11) NOT NULL,
 `frequency` int(11) DEFAULT NULL,
 `hour` int(11) NOT NULL,
 `email` varchar(255) DEFAULT NULL,
 `highlightedTitle` longtext,
 `newReplies` bit(1) NOT NULL,
 `numReplies` int(11) NOT NULL,
 `postUrl` longtext,
 `sendTime` datetime DEFAULT NULL,
 `sent` bit(1) NOT NULL,
 `snippet` longtext,
 `label_id` bigint(20) DEFAULT NULL,
 `organization_id` bigint(20) DEFAULT NULL,
 `threadEntity_hash` varchar(255) DEFAULT NULL,
 `user_uid` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `FK3991E9D279251FE` (`organization_id`),
 KEY `FK3991E9D35FC0C96` (`label_id`),
 KEY `FK3991E9D3FFC22CB` (`user_uid`),
 KEY `FK3991E9D5376B351` (`threadEntity_hash`),
 KEY `scheduleSentReplies` (`day`,`frequency`,`hour`,`sent`,`numReplies`),
 KEY `sendTime` (`sendTime`),
 CONSTRAINT `FK3991E9D279251FE` FOREIGN KEY (`organization_id`) REFERENCES `Organization` (`id`),
 CONSTRAINT `FK3991E9D35FC0C96` FOREIGN KEY (`label_id`) REFERENCES `Label` (`id`),
 CONSTRAINT `FK3991E9D3FFC22CB` FOREIGN KEY (`user_uid`) REFERENCES `User` (`uid`),
 CONSTRAINT `FK3991E9D5376B351` FOREIGN KEY (`threadEntity_hash`) REFERENCES `ThreadEntity` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=4461945 DEFAULT CHARSET=utf8
1 row in set (0.08 sec)

mysql> explain SELECT * FROM ThreadNotification2 WHERE email='invalid address';
+----+-------------+---------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | ThreadNotification2 | ALL  | NULL          | NULL | NULL    | NULL | 197414 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.03 sec)


mysql> explain select * from ThreadNotification2 where id in (3940042,3940237,3941132,3941255,3941362,3942535,3943064,3944134,3944228,3948122,3953081,3957876,3963849,3966951);
+----+-------------+---------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table               | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | ThreadNotification2 | range | PRIMARY       | PRIMARY | 8       | NULL |   14 | Using where |
+----+-------------+---------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)



mysql> delete from ThreadNotification2 where email='invalid address';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select id from ThreadNotification2 where email='invalid address';
+---------+
| id      |
+---------+
| 3940042 |
| 3940237 |
| 3941132 |
| 3941255 |
| 3941362 |
| 3942535 |
| 3943064 |
| 3944134 |
| 3944228 |
| 3948122 |
| 3953081 |
| 3957876 |
| 3963849 |
| 3966951 |
+---------+
14 rows in set (1.25 sec)

mysql> delete from ThreadNotification2 where id in (3940042,3940237,3941132,3941255,3941362,3942535,3943064,3944134,3944228,3948122,3953081,3957876,3963849,3966951);
Query OK, 14 rows affected (0.02 sec)

如果該欄位未編入email索引，則兩者都DELETE應該SELECT同樣緩慢。

我能想到的唯一可能是：您說該表被大量訪問。在您嘗試執行DELETE.

我想也許你應該在那裡插入一些模擬行並嘗試刪除它們。這樣做 2 或 3 次。如果 的持續時間有很大差異DELETE，那麼可能是 DB 負載的原因。

PS：只有當人們不會被那些模擬行惹惱時才這樣做：D。

引用自：https://dba.stackexchange.com/questions/21449