MySQL的eq_range_index_dive_limit参数对in查询影响

目录

• 参数说明
• 场景复现

参数说明

MySQL5.7的官方说明文档：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_eq_range_index_dive_limit

eq_range_index_dive_limit变量影响优化器在等值比较的条件中，如何评估符合条件的行数。

等值比较的表达式类似以下

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

当col_name是非唯一索引时，优化器评估行数的方式有2种：

• 索引下潜（index dives）：实际读取索引来获取统计信息。速度慢，但是估计准确

• 索引统计信息(index statistics)：MySQL会定期收集和存储索引的统计信息，优化器在需要的时候直接使用这些存储的统计信息。速度快，但是估计可能不准

那么

• 设置eq_range_index_dive_limit = 0：始终使用index dives
• 设置eq_range_index_dive_limit = 1：始终使用index statistics
• 设置eq_range_index_dive_limit = N（N>0）：in的个数<N时，使用index dives，in的个数>=N时，使用index statistics

这个值，在mysql5.6里默认为10，5.7里默认为200

场景复现

这里会存在一个问题，当非唯一索引的基数（Cardinality）比较低时，使用index statistics的方式评估，可能会出现不走索引的情况。

1. 创建表

CREATE TABLE `user_order` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `status` tinyint NOT NULL
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

其中status设置了非唯一索引

2. 插入10w条测试数据

mysql> select status,count(*) from user_order group by status;
+--------+----------+
| status | count(*) |
+--------+----------+
|      1 |       98 |
|      2 |    50006 |
|      3 |    49896 |
+--------+----------+

其中status=1的插入了98条，其余的5w条左右

通过show index from user_order查看status索引基数（Cardinality）为2，通过show table status like 'user_order'查看统计信息里的行数为109073

3. 正常执行查询

mysql> explain select * from user_order where status=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_order
   partitions: NULL
         type: ref
possible_keys: idx_status
          key: idx_status
      key_len: 1
          ref: const
         rows: 98
     filtered: 100.00
        Extra: NULL

通过explain可以看到，预估的rows=98

3. 模拟使用index statistics的方式

# 先关闭索引下潜的方式
mysql> set session eq_range_index_dive_limit=1;
Query OK, 0 rows affected (0.01 sec)

mysql> explain select * from user_order where status=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_order
   partitions: NULL
         type: ref
possible_keys: idx_status
          key: idx_status
      key_len: 1
          ref: const
         rows: 54536
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

可以看到，mysql评估status=1的行数为54536，和实际差距很大。

这里统计信息的的rows，就是通过统计数据的行数（109073） / status索引基数(2)来的。

由于会错误估计行数，可能会最终导致执行计划不走索引