為什麼要使用子查詢檢查這個刪除查詢的 22 億行？

我們使用社區軟體 Invision Power Suite 4.1.15。社區有 15+ 百萬個文章，我們設置了搜尋索引表，它必須包含一年的文章。

我們在慢速日誌中發現了以下查詢：

# Query_time: 6466.229122  Lock_time: 0.000053 Rows_sent: 0 Rows_examined: 2287405946
SET timestamp=1475416358;
/*IPS\Content\Search\Mysql\_Index::prune:179*/
   DELETE FROM `ibf_core_search_index_item_map`
       WHERE index_item_id IN(
           SELECT index_item_id
               FROM `ibf_core_search_index` AS `core_search_index`
               WHERE index_date_updated < 1443873892 );

如您所見，此查詢顯然檢查了 22 億行。這是……糟糕……

但是，對 core_search_index_item_map 表的計數僅給出 434926 行。對 ibf_core_search_index 的計數給出了 1559806 行。

此外，如果我現在執行以下查詢（上面的子選擇查詢）

SELECT index_item_id FROM `ibf_core_search_index` AS `core_search_index`
   WHERE index_date_updated < 1443873892

我被退回 99 行。

如果我執行以下選擇查詢（我已將 DELETE FROM 替換為 SELECT * FROM）：

SELECT * FROM `ibf_core_search_index_item_map`
   WHERE index_item_id IN(
       SELECT index_item_id
           FROM `ibf_core_search_index` AS `core_search_index`
               WHERE index_date_updated < 1443873892 );

然後我將返回 301 行。

選擇查詢的解釋給出了這個： http: //pastebin.com/kivhZ0An

雖然刪除查詢的解釋給出了這個： http: //pastebin.com/0Pzvi5Qg

儘管所有選擇查詢單獨給出的行數都少於 22.8 億行，但這顯然是刪除查詢正在使用的內容。在過去幾天啟動刪除查詢時，這種情況一直在發生，所以這不僅僅是一次性的。

這在我們的社區執行時引起了嚴重的問題。並且軟體 IPS 自動希望每天執行一次此查詢。目前我已禁用它，但我需要了解這是如何發生的，然後才能向他們報告。

這是涉及的兩個表的 show create 語句： CREATE TABLES

MySQL [*]> SHOW CREATE TABLE ibf_core_search_index_item_map\G
*************************** 1. row ***************************
      Table: ibf_core_search_index_item_map
Create Table: CREATE TABLE "ibf_core_search_index_item_map" (
 "index_author_id" int(10) NOT NULL DEFAULT '0',
 "index_item_id" int(10) NOT NULL DEFAULT '0',
 "index_class" varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
 UNIQUE KEY "search_map" ("index_author_id","index_item_id","index_class")
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

MySQL [*]> SHOW CREATE TABLE ibf_core_search_index\G
*************************** 1. row ***************************
      Table: ibf_core_search_index
Create Table: CREATE TABLE "ibf_core_search_index" (
 "index_class" varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Content type class e.g. \\IPS\\forums\\Topic',
 "index_id" bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
 "index_object_id" int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The ID of the object. pid for posts, tid for topics etc',
 "index_item_id" int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'For comments and reviews, the ID of the item it pertains to. For \\IPS\\Content\\Item this would just be NULL',
 "index_container_id" int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The id of the container for items/comments/reviews',
 "index_content" mediumtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The plain-text content to search',
 "index_permissions" text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'A comma-delimited list of groups which have permission to view',
 "index_author" int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The object author id',
 "index_title" varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Content title',
 "index_date_created" int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The object creation date',
 "index_date_updated" int(10) unsigned DEFAULT NULL COMMENT 'Object last updated date',
 "index_hidden" tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 = visible, -1 = hidden (previously visible), 1 = unapproved, 2 = parent item hidden',
 "index_item_index_id" bigint(20) unsigned DEFAULT NULL COMMENT 'The index ID of the item',
 "index_item_author" mediumint(8) DEFAULT NULL COMMENT 'The author of the item',
 "index_is_last_comment" tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Is this the last comment/review on an item?',
 PRIMARY KEY ("index_id"),
 UNIQUE KEY "object" ("index_class"(181),"index_object_id"),
 KEY "author_lookup" ("index_author","index_hidden","index_date_updated"),
 KEY "item" ("index_class"(181),"index_item_id"),
 KEY "container" ("index_class"(181),"index_container_id"),
 KEY "index_date_updated" ("index_date_updated"),
 KEY "index_date_created" ("index_date_created"),
 KEY "index_hidden" ("index_hidden"),
 FULLTEXT KEY "index_content" ("index_content","index_title"),
 FULLTEXT KEY "index_title" ("index_title")
) ENGINE=InnoDB AUTO_INCREMENT=1571104 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

我們使用Mysql 5.7.15-0ubuntu0.16.04.1-log

MySQL 的優化器在IN使用子查詢進行優化時從來沒有太多選擇：

WHERE column IN (SELECT ....)

在 5.6 和 5.7 版本中，這方面有一些改進，但似乎仍然存在無法找到最佳計劃的情況。

我建議您使用以下方法重寫查詢JOIN：

DELETE item_map.* 
FROM ibf_core_search_index_item_map AS item_map
 JOIN ibf_core_search_index AS core_search_index
   ON item_map.index_item_id = core_search_index.index_item_id 
WHERE core_search_index.index_date_updated < 1443873892 ;

或（引導優化器實現子結果）：

DELETE item_map.* 
FROM ibf_core_search_index_item_map AS item_map
 JOIN 
   ( SELECT index_item_id 
     FROM ibf_core_search_index 
     WHERE index_date_updated < 1443873892 
   ) AS core_search_index
 ON item_map.index_item_id = core_search_index.index_item_id ;

關於為什麼你DELETE和SELECT顯示不同的計劃：不確定所有優化器內部，但有時這些額外的選項/改進不會同時為所有語句添加。即，可以進行僅影響SELECT語句而不影響語句的改進UPDATE/DELETE，在版本 X 和隨後的版本 Y 中，添加了這些。並且細節可能會有所不同（由於鎖定考慮、隔離級別等）。修改表的數據與僅選擇它們不同。

至於 22 億，大約是 (1.5M / 300) * 434K。

---

其他影響效率的因素：

• 列 ( index_item_id) 在 2 個表中具有不同的類型。一個是signed int，另一個unsigned int。更改其中之一，使其匹配。
• 上沒有索引item_map (index_item_id)。
• 上的索引(index_date_updated, index_item_id)也會有所幫助。

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