用IN查询更新军力索引

Question

问题:当IN (...)中有超过396项时，Mysql没有在更新查询中使用索引。甚至我也强迫引擎使用主键。

表Def'n：

CREATE TABLE `store_product` (
  `store_id` bigint(20) NOT NULL,
  `product_id` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL,
  `last_updated_at` timestamp NOT NULL,
  `status` varchar(255) NOT NULL,
  `active_promotion_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`store_id`,`product_id`),
  KEY `store_product_status_index` (`status`),
  KEY `store_product_active_promotion_id_index` (`active_promotion_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

查询：

UPDATE store_product FORCE INDEX (PRIMARY) SET active_promotion_id = NULL,  WHERE (store_id, product_id) IN ((x1, y1), ..., (xn, yn))

其中n> 396 (共59m行)。

这个神奇数字的意义是什么？为什么MySQL在更新时不听我的话，甚至不使用primary key？

版本：MySQL5.7.17.R1

Answer 1

如果MySQL优化器认为使用索引比用硬方式扫描表要昂贵得多，它就不使用索引。

类似于书后面的索引。为什么不包括像"the“这样非常常见的词呢？因为它最终会告诉你这个常见的词出现在书的每一页上。这不是帮助你找到正确页面的有效方法。

MySQL会做类似的事情，如果它认为您正在搜索的值发生在表的一个足够大的子集上，那么它将跳过索引。这没有记录在案的阈值，但根据我的经验，当优化器估计您的条件与表的20%以上匹配时，就会发生这种情况。

如果您知道使用索引比使用表扫描更好，那么FORCE INDEX优化器提示就是为了解决这个问题，尽管优化器的估计是这样的。但也许这样做只是为了避免表扫描，而不是索引扫描。

另一种可能是: MySQL没有很好地优化元组比较谓词，比如(a,b) IN ((val, val)...)。这是一个MySQL 5.7中的新特性，看起来它仍然不能像简单的谓词那样工作。

这是在使用EXPLAIN测试查询时得到的优化计划，在IN()子句中只有两个值对。

+----+-------------+---------------+-------+---------------+---------+---------+------+------+------------------------------+
| id | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra                        |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+------------------------------+
|  1 | SIMPLE      | store_product | index | NULL          | PRIMARY | 16      | NULL |    1 | Using where; Using temporary |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+------------------------------+

type: index不是一个很好的迹象。这意味着它将扫描整个主键索引。这几乎和桌面扫描一样糟糕。

Using temporary也是一项昂贵的操作。我猜它正在创建一个临时表来存储IN()谓词的元组列表。

下面是一个等价的查询，它可以在不使用元组比较的情况下获得更好的优化方案：

EXPLAIN UPDATE store_product SET active_promotion_id = NULL
WHERE (store_id=1 AND product_id=1) OR (store_id=2 AND product_id=2)

+----+-------------+---------------+-------+---------------+---------+---------+-------------+------+-------------+
| id | select_type | table         | type  | possible_keys | key     | key_len | ref         | rows | Extra       |
+----+-------------+---------------+-------+---------------+---------+---------+-------------+------+-------------+
|  1 | SIMPLE      | store_product | range | PRIMARY       | PRIMARY | 16      | const,const |    1 | Using where |
+----+-------------+---------------+-------+---------------+---------+---------+-------------+------+-------------+

type: range是一个更有利的优化。也没有临时桌子。