MySQL DELETE 語句不使用索引，儘管相同的 SELECT 查詢使用

我有一張大約有 3000 萬行的表格（很快就會增加兩倍/三倍），我必須定期更新。表結構如下：

id, 
cookie_id VARCHAR(45), 
country VARCHAR(45), 
category VARCHAR(45), 
other_non_relevant_columns

索引如下所示：

SHOW INDEX FROM data;
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| data  |          0 | PRIMARY                |            1 | id          | A         |    24767570 |     NULL | NULL   |      | BTREE      |         |               |
| data  |          1 | cookie_index           |            1 | cookie_id   | A         |    14440214 |     NULL | NULL   |      | BTREE      |         |               |
| data  |          1 | country_category_index |            1 | country     | A         |         498 |     NULL | NULL   |      | BTREE      |         |               |
| data  |          1 | country_category_index |            2 | category    | A         |         997 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

因此，cookie_id 上有一個非唯一索引，國家+類別列上有一個非唯一索引。現在的情況是，每週我都應該執行查詢

1. 刪除屬於 country=‘Y’ AND category=‘X’ 的所有數據（5 到 2000 萬行）
2. 導入新鮮數據（類似數量）

問題是，刪除數據需要花費大量時間——這就是我在國家+類別列上設置索引的原因。但是，“DELETE”語句仍然不使用索引，而是嘗試掃描整個表：

mysql> EXPLAIN DELETE FROM data WHERE country='Y' and category='X';
+----+-------------+-------+------------+------+------------------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys          | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+------------------------+------+---------+------+----------+----------+-------------+
|  1 | DELETE      | data  | NULL       | ALL  | country_category_index | NULL | NULL    | NULL | 24767570 |   100.00 | Using where |
+----+-------------+-------+------------+------+------------------------+------+---------+------+----------+----------+-------------+

選擇工作正常：

mysql> EXPLAIN SELECT id, cookie_id FROM data WHERE country='Y' and category='X';
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------------+----------+----------+-------+
| id | select_type | table | partitions | type | possible_keys          | key                    | key_len | ref         | rows     | filtered | Extra |
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------------+----------+----------+-------+
|  1 | SIMPLE      | data  | NULL       | ref  | country_category_index | country_category_index | 365     | const,const | 10130630 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------------+----------+----------+-------+

有什麼方法可以優化 DELETE 查詢嗎？

通過首先刪除數據，您實際上是在使數據無法訪問。您不想避免這種“停機時間”嗎？

考慮將替換數據載入到臨時表中，然後執行 IODKU 更新主數據：

INSERT INTO main (...)
       ON DUPLICATE KEY UPDATE 
            col1 = VALUES(col1),
            ...
   SELECT ... FROM temp;

如果可以刪除行；IODKU 不會提供該功能。但是，你可以在它前面加上類似的東西

ALTER TABLE temp ADD INDEX (...);  -- to speed up the LEFT JOIN below

DELETE FROM main
      USING main
       LEFT JOIN temp ON ...
      WHERE temp... IS NULL;

你試過批量刪除嗎？也許 SQL 優化器認為 FULL DELETE 太大，所以它沒有使用索引，而是訪問整個表。嘗試分 5-10 批吐出，我認為解釋刪除會有所不同

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