我正在運行查詢以從具有 ~ 3M 記錄的表中洗掉重復項（用戶定義）。查詢是：
DELETE t1
FROM 'path_alias_revision' t1
INNER JOIN 'path_alias_revision' t2
WHERE t1.id < t2.id AND t1.path=t2.path AND binary(t1.alias) = binary(t2.alias)
輸出show create table：
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
--------------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| path_alias_revision | CREATE TABLE `path_alias_revision` (
`id` int(10) unsigned NOT NULL,
`revision_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`langcode` varchar(12) CHARACTER SET ascii NOT NULL,
`path` varchar(255) DEFAULT NULL,
`alias` varchar(255) DEFAULT NULL,
`status` tinyint(4) NOT NULL,
`revision_default` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`revision_id`),
KEY `path_alias__id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=80213807 DEFAULT CHARSET=utf8mb4 COMMENT='The revision table for path_alias entities.' |
--------------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
解釋輸出：
explain DELETE t1 FROM path_alias_revision t1 INNER JOIN path_alias_revision t2 WHERE t1.id < t2.id AND t1.path=t2.path AND binary(t1.alias) = binary(t2.alias);
---- ------------- ------- ------------ ------ ---------------- ------ --------- ------ --------- ---------- ------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- ------- ------------ ------ ---------------- ------ --------- ------ --------- ---------- ------------------------------------------------
| 1 | DELETE | t1 | NULL | ALL | path_alias__id | NULL | NULL | NULL | 3105455 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | path_alias__id | NULL | NULL | NULL | 3105455 | 3.33 | Range checked for each record (index map: 0x2) |
---- ------------- ------- ------------ ------ ---------------- ------ --------- ------ --------- ---------- ------------------------------------------------
我無法判斷查詢是掛起還是需要很長時間。的輸出show processlist是：
MySQL [acquia]> show processlist \G;
*************************** 1. row ***************************
Id: 11
User: acquia
Host: 172.18.0.3:37498
db: acquia
Command: Query
Time: 602
State: Sending data
Info: DELETE t1
FROM path_alias_revision t1
INNER JOIN path_alias_revision t2
WHERE t1.id < t2.
*************************** 2. row ***************************
Id: 15
User: acquia
Host: 172.18.0.3:37512
db: acquia
Command: Query
Time: 0
State: starting
Info: show processlist
2 rows in set (0.000 sec)
ERROR: No query specified
我可以做些什么來改進這個查詢？我知道我可以將我想要保留的資料移動到臨時表并重命名它，但我想了解這里發生了什么。我已將一些 mysql 屬性升級為：
max_allowed_packet = 128M
innodb_buffer_pool_chunk_size = 128M
innodb_buffer_pool_size = 8G
但這沒有幫助。
uj5u.com熱心網友回復：
更新：在我寫完答案后，這個問題被編輯了。OP 添加了一個條件，即他們不想創建索引。但這是優化 DELETE 查詢的解決方案。我將在下面留下我的原始答案。
您缺少索引，并且如果您想優化alias列的二進制比較，那么您應該更改其排序規則，以便索引基于二進制位元組。
mysql> alter table path_alias_revision
modify column alias varchar(255) collate utf8mb4_bin,
add index (path, alias);
現在您可以在 EXPLAIN 中看到改進。自然，查詢仍然需要對 進行表掃描t1，但它可以使用索引來查找匹配的行。和表明它能夠為兩列使用索引ken_len。ref
explain DELETE t1 FROM path_alias_revision t1
INNER JOIN path_alias_revision t2
WHERE t1.id < t2.id AND t1.path=t2.path AND t1.alias = t2.alias
*************************** 1. row ***************************
id: 1
select_type: DELETE
table: t1
partitions: NULL
type: ALL
possible_keys: path_alias__id,path
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ref
possible_keys: path_alias__id,path
key: path
key_len: 1791
ref: test2.t1.path,test2.t1.alias
rows: 1
filtered: 100.00
Extra: Using where
在我的測驗中，它顯示是rows: 1因為我沒有創建測驗資料。
uj5u.com熱心網友回復：
您沒有提到要洗掉多少重復行。所以這是一個猜測。InnoDb（存盤引擎）將您的整個DELETE陳述句放入單個事務中。它建立一個事務日志并立即提交整個事情（為了ACID的緣故）。該事務可能非常龐大，并且會使用資源（IO 和 CPU）。
避免超大交易的訣竅？分塊進行洗掉。
試試這個：首先獲取您需要洗掉的行的 PK。
/* make a temp table with the PK values for the rows you want to delete
* this may take a lot of time but that's OK */
CREATE TEMPORARY TABLE path_revision_alias_dups
SELECT t1.revision_id
FROM 'path_alias_revision' t1
INNER JOIN 'path_alias_revision' t2
WHERE t1.id < t2.id AND t1.path=t2.path AND binary(t1.alias) = binary(t2.alias);
現在我們有一個臨時表，其中包含要洗掉的行。讓我們使用它。
您將分塊進行洗掉（此處以 1000 行的塊為單位）。因此，您需要一遍又一遍地重復下一大堆 SQL，直到沒有任何內容可洗掉。
/* retrieve a subset -- a chunk -- of the IDs to delete, 1000 at a time */
CREATE TEMPORARY TABLE dups_to_delete_now
SELECT revision_id
FROM path_revision_alias_dups
LIMIT 1000;
/* delete the rows from your table */
DELETE FROM path_alias_revision
WHERE revision_id IN (SELECT * FROM dups_to_delete_now);
/* and delete the batch from your first temp table */
DELETE FROM path_revision_alias_dups
WHERE revision_id IN (SELECT * FROM dups_to_delete_now);
/* clean up, ready for the next chunk */
DROP TABLE dups_to_delete_now;
在進行大規模表維護時，這是一種相當常見的查詢模式。
我建議的第一個 CREATE TEMPORARY TABLE 可能需要很長時間，因為您無法添加任何索引。它可能：沒有索引，查詢的復雜度是O(n 2 )。如果確實需要太長時間，您將需要某個地方的索引。
uj5u.com熱心網友回復：
最初的嘗試，涉及t1.id < t2.id非常低效；在一百萬行的表上執行一萬億次操作。（我希望參考手冊不包括它。）
有很多方法可以DELETE提高效率。并且有一種方法可以在INDEX不鎖定表格的情況下添加（如果這是您真正的恐懼）。
另外，WHERE binary(alias) ...不會用INDEX(alias)！
那么該怎么辦？？
如果您要洗掉大部分表格，請不要使用
DELETE; 而是使用SELECT到一個新表中，然后玩弄表。這適用于幾乎任何將洗掉“大部分”表的大洗掉。（什么是“很多”？我沒有任何好的數字；也許是 1/3，也許是 1/2，當然是 3/4。）不要使用該
t1.id < t2.id技術；它可能是有史以來為大型表發明的最糟糕的，即使是索引的。如果您無法擺脫
binary(alias)，那么讓我們從創建一個新表開始，其中包含表的主鍵和主鍵。CREATE TABLE helper ( balias ... NOT NULL, -- from binary(alias) id ... NOT NULL, -- whatever is the PK of your table PRIMARY KEY(balias) ) ENGINE=InnoDB;
現在填充它：
INSERT INTO helper ( balias, id )
SELECT binary(alias), id FROM t1;
然后使用該表查看要做什么
DELETE（如果只有少量要洗掉）；使用多表 DELETESELECT使用 a 來“保留”的行（如果數量很大）LEFT JOIN；交換表。
uj5u.com熱心網友回復：
如果您不關心要保留哪些副本，這是解決此問題的另一種方法。
首先，讓我們做一個子查詢來檢索您實際想要保留的行的 PK 值。
SELECT MAX(revision_id) revision_id
FROM path_alias_revision
GROUP BY path, binary(alias)
這將只revision_id從每組重復行中選擇一個，即最大的一個。它會生成一個怪物排序操作，需要一段時間。但是這種排序本質上是O(n log(n))而不是O(n 2 )的復雜性。
然后您可以使用該子查詢來運行您的洗掉操作。像這樣。
DELETE FROM path_alias_revision
WHERE revision_id NOT IN (
SELECT MAX(revision_id) revision_id
FROM path_alias_revision
GROUP BY path, binary(alias));
因為revision_id是主鍵，NOT IN()謂詞可以訪問現有索引。
如果要洗掉的行數不多，您可以一次完成所有操作。如果它很大，您需要分塊洗掉以避免太大的事務。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/428861.html
上一篇：SQL案例和區別