“IN”子句中的许多项目导致全表扫描早于预期

我们有一个由 CMS 提供的搜索索引表，其形式为：

CREATE TABLE `craft_searchindex` (
  `elementId` int NOT NULL,
  `attribute` varchar(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `fieldId` int NOT NULL,
  `locale` char(12) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `keywords` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  PRIMARY KEY (`elementId`,`attribute`,`fieldId`,`locale`),
  FULLTEXT KEY `craft_searchindex_keywords_idx` (`keywords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

作为 CMS 提供的搜索功能的一部分，形成以下查询：

SELECT * FROM `craft_searchindex` WHERE (`keywords` LIKE '% tom %') AND `locale` = 'en_au' AND `elementId` IN (<ids>)

其中是基于先前查询的 sids过滤列表。elementId

我们观察到，当我们在该子句中达到一定数量的项目时IN，性能下降并且查询执行时间变得恒定，如迁移到 InnoDB 之前收集的图表所示（y 轴执行时间以毫秒为单位，x-子句中的轴项目数IN，系列最小值/平均值/最大值）：

EXPLAIN ANALYZE查询的最后一行显示约 33500 个项目的以下内容：

-> Index range scan on craft_searchindex using PRIMARY over (elementId = 1) OR (elementId = 128) OR (33500 more)  (cost=55355 rows=234514) (actual time=0.0376..537 rows=469028 loops=1)

对于约 34000 件商品：

-> Table scan on craft_searchindex  (cost=335885 rows=3.27e+6) (actual time=0.0303..3237 rows=3.72e+6 loops=1)

我对此的解释（与图表相符）是：

• 扫描主键索引达到某个阈值（一对一，提供线性增长），并且
• 超过该阈值时，始终会扫描整个表（提供常量值）

我的问题是，考虑到紧邻该阈值的执行时间与高于该阈值的执行时间之间存在显着差异，为什么查询规划器选择放弃扫描索引[*]，并且可以对此采取任何措施（带有约束）查询准备是由 CMS 执行的，所以大部分不在我们手中）？

加入临时表

虽然考虑到 CMS 处理的查询的限制，这对我们来说不是一个解决方案，但我已经采纳了 @Akina 使用索引临时表的建议。这将查询（有效地）更改为：

SET SESSION group_concat_max_len = 1 << 19;
PREPARE ids_stmt FROM 'SELECT GROUP_CONCAT(`id`)
INTO @ids_clause
FROM (
  SELECT `elements`.`id`
  FROM `craft_elements` `elements`
  JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
  JOIN `craft_content` `content` ON content.elementId = elements.id
  JOIN `craft_users` `users` ON users.id = elements.id
  WHERE ((elements_i18n.locale = ?) AND (content.locale = ?)) AND (elements.archived = 0)
) AS `ids`';
SET @l = 'en_au';
EXECUTE ids_stmt USING @l, @l;  
DEALLOCATE PREPARE ids_stmt;

SET @search_query = CONCAT("SELECT `craft_searchindex`.* FROM `craft_searchindex` WHERE (`keywords` LIKE '% tom %') AND `locale` = 'en_au' AND `elementId` IN (", @ids_clause, ')');

PREPARE search_stmt FROM @search_query;
EXECUTE search_stmt;
DEALLOCATE PREPARE search_stmt;

到：

PREPARE ids_stmt FROM 'CREATE TEMPORARY TABLE ids_table
(PRIMARY KEY(`id`))
SELECT `elements`.`id`
FROM `craft_elements` `elements`
JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
JOIN `craft_content` `content` ON content.elementId = elements.id
JOIN `craft_users` `users` ON users.id = elements.id
WHERE ((elements_i18n.locale = ?) AND (content.locale = ?)) AND (elements.archived = 0)
LIMIT 1';
SET @l = 'en_au';
EXECUTE ids_stmt USING @l, @l; 
DEALLOCATE PREPARE ids_stmt;

SELECT `craft_searchindex`.*
FROM `craft_searchindex`
JOIN `ids_table` ON `craft_searchindex`.`elementId` = `ids_table`.`id`
WHERE (`keywords` LIKE '% tom %') AND `locale` = 'en_au';

这使得执行时间的线性增长远远超过之前的阈值。

有趣的是（也许是因为临时表只有一列）删除主键对执行时间没有影响。

下面是比较这些的图表（时间现在以秒为单位）：

[*]：从一些简单的粗略计算来看，继续扫描索引而不是执行全表扫描仍然低于全表扫描执行时间，直到子句中的项目数IN超过 ~200,000