MySQL的Equality Range Optimization of Many-Valued Comparisons

最近新上线的系统中遇到一个SQL，明明可以使用索引，但是发现实际执行并没有使用到索引。SQL的主要特点是where子句中，in判断中有大量的条件。类似如下：

select * FROM a WHERE td in ('2023-08-01') and fuid in ('2','3','41','42','44','44','45','46','47','48','49','31','32','34','33','35','36','37','38','39','320','412','414','414','415','416','417','418','419','420','421','424','424','425','426','401','402','404','404','405','406','407','408','409','410','411','412','414','414','415','416','417','418','419','420','421','424','424','425','426','18','273','249','226','42','46','277','49','48','53','58','222','60','73','75','76','77','81','83','91','93','95','97','102','104','107','111','112','114','116','117','118','121','120','2','124','125','126','132','130','136','137','1','215','216','224','223','168','166','157','217','179','173','198','195','225','212','207','127','148','170','142','164','171','156','160','135','165','161','153','151','150','180','172','174','218','186','204','189','232','205','209','200','202','199','201','106','167','210','219','213','214','220','208','252','227','259','260','261','262','265','267','269','270','271','274','278','279','282','280','285','288','290','291','292','297');

于是研究了一下系统变量eq_range_index_dive_limit。

 

eq_range_index_dive_limit变量表示在相等比较条件中，优化器在估算合格行数时，应从使用索引下潜(index dives)转换为使用索引统计信息(index statistics)的相等范围数。
它适用于具有下面两种等价形式的表达式的评估，其中优化器使用非唯一索引查找col_name值：

col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN

在这两种情况下，表达式都包含N个相等范围。优化器可以使用索引下潜(index dives)或索引统计信息(index statistics)进行行估计。
如果eq_range_index_dive_limit大于0，那么如果存在eq_range_index_dive_limit或更多的相等范围，优化器就会使用现有的索引统计信息(index statistics)而不是索引下潜(index dives)。
因此，要允许对最多N个相等范围使用索引下潜(index dives)，请将eq_range_index_dive_limit设置为N+1。
如果要禁用索引统计信息(index statistics)，并始终使用索引下潜(index dives)，而不考虑N，则应将eq_range_index_dive_limit设置为 0。

要更新表索引统计数据以获得最佳估计值，请使用ANALYZE TABLE。

Equality Range Optimization of Many-Valued Comparisons

考虑下面这些表达式，其中col_name是索引列：

col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN

如果col_name等于多个值中的任意一个，则每个表达式都为真。这些比较是相等范围比较（其中"范围"是单个值）。优化器会按以下方式为相等范围比较评估读取合格记录的成本：
·如果col_name上有唯一索引，则每个范围的行评估值为1，因为最多只能有一条记录等于给定值。
·否则，col_name上的任何索引都是非唯一的，优化器可以通过索引下潜(index dives)或索引统计信息(index statistics)来评估每个范围的行数。

通过索引下潜(index dives)，优化器会在范围的两端下潜，并使用范围内的行数作为估计值。例如，表达式col_name IN(10, 20, 30)有三个相等范围，优化器会在每个范围进行两次下潜，以生成行估计值。每一对下潜都会产生等于给定值的行数估计值。

索引下潜(index dives)可以提供准确的行估计值，但随着表达式中比较值数量的增加，优化器生成行估计值所需的时间也会延长。使用索引统计信息(index statistics)的准确性不如索引下潜(index dives)，但可以更快地对大量值列表进行行估计。

通过eq_range_index_dive_limit系统变量，可以配置优化器从一种行估计策略切换到另一种行估计策略的阈值。要允许在最多N个相等范围的比较中使用索引下潜(index dives)，请将eq_range_index_dive_limit设置为N+1。如果要禁用索引统计信息(index statistics)，并始终使用索引下潜(index dives)，而不考虑N，则应将eq_range_index_dive_limit设置为0。

在MySQL 8.0之前，除了使用eq_range_index_dive_limit系统变量外，无法跳过使用索引下潜(index dives)来估计索引有用性。在MySQL 8.0中，满足所有这些条件的查询都可以跳过索引下潜(index dives)：
·查询针对的是单个表，而不是多个表的JOIN。
·存在对单个索引的FORCE INDEX提示。这样做的目的是，如果强制使用索引，则不会从执行索引下潜(index dives)的额外开销中获得任何收益。
·索引是非唯一索引，不是FULLTEXT索引。
·不存在子查询。
·没有DISTINCT、GROUP BY或ORDER BY子句。

 

对于EXPLAIN FOR CONNECTION，如果跳过索引下潜(index dives)，输出结果会发生如下变化：
·对于传统输出，rows和filtered值均为NULL。
·对于JSON输出，不显示rows_examined_per_scan和rows_produced_per_join，skip_index_dive_due_to_force为true，并且成本计算不准确。

如果不使用FOR CONNECTION，跳过索引下潜(index dives)时EXPLAIN输出不会发生变化。

在执行了跳过索引下潜(index dives)的查询后，information_schema.optimizer_trace表中的相应行包含一个index_dives_for_range_access，值为 skipped_due_to_force_index。

在MySQL中，"index dives"和"index statistics"是优化器在进行查询计划选择时使用的两种不同的统计信息获取方式。
1.Index Dives：这是一种动态的统计信息收集方式。当优化器需要决定是否使用某个索引时，它会"潜入"这个索引，也就是实际读取索引的部分页面来获取统计信息。这种方式获取的统计信息是实时的，但是会消耗一些资源。
2.Index Statistics：这是一种静态的统计信息收集方式。MySQL会定期收集和存储索引的统计信息，优化器在需要的时候直接使用这些存储的统计信息。这种方式获取的统计信息可能并不是实时的，但是开销较小。

在决定使用哪种方式时，MySQL会考虑多种因素，包括但不限于查询的复杂性、表的大小、系统变量的设置等。例如，`eq_range_index_dive_limit`就是一个可以影响优化器选择的系统变量。

回到最初的问题，通过调节eq_range_index_dive_limit分别为10、200的条件下，执行上面的sql，发现都没啥效果，还要继续分析找原因。但是通过xplain format=json 解析sql后，明显可以比较出估算出的成本有很大的差异。