MySQL非唯一索引值分布不均匀与优化器参数eq_range_index_dive_limit

最新推荐文章于 2024-04-27 13:00:00 发布

weixin_33851604

最新推荐文章于 2024-04-27 13:00:00 发布

阅读量454

点赞数

文章标签：数据库 php python

原文链接：https://my.oschina.net/markwang/blog/789148

版权

2019独角兽企业重金招聘Python工程师标准>>>

MySQL5.6引入了一个新的系统变量eq_range_index_dive_limit。这可能会显着影响查询执行计划。这里我举一个典型的例子。

有一个表“t”。主键由从“id1”开始的多个列组成。表t中有1.67M行，id1的基数是46K（这些数字可以通过SHOW TABLE STATUS / SHOW INDEX收集）。因此，每个id1平均有36行（1.67M / 46K = 36），但实际的id1分布是不均匀的。有接近1M行，其中id1在1和10之间。

mysql> explain select count（*）from t force index（PRIMARY）where id1 in（1,2,3,4,5,6,7,8,9）\ G
*************************** 1.行******************** *******
           id：1
  select_type：SIMPLE
        table：t
         type：range
possible_keys：PRIMARY
          key：PRIMARY
      key_len：8
          ref：NULL
         rows：912388
       extra：using where;using index
1 row（0.00 sec）

MySQL估计912K行匹配，其中id1 IN（1..9）。这接近实际数字。 MySQL5.6引入了持久化优化器统计，使统计信息更准确。

mysql>explain select count（*）from t force index（PRIMARY）where id1 in（1,2,3,4,5,6,7,8,9,10）\ G
*************************** 1.行******************** *******
           id：1
  select_type：SIMPLE
        table：t
         type：range
possible_keys：PRIMARY
          key：PRIMARY
      key_len：8
          ref：NULL
         rows：360
        extra：using where;using index
1 row（0.00 sec）

当添加一个IN条件（id1 IN（1..10））时，突然估计的行数下降到360！这比实际匹配的行数小得多。估计的行数越来越少（或更大）经常使MySQL选择不正确的查询执行计划，所以这是真的很严重。

估计的行数变化很大的原因是一个新的系统变量eq_range_index_dive_limit。如在线手册所述，“如果eq_range_index_dive_limit大于0，如果有eq_range_index_dive_limit或更多相等范围”，优化器将使用现有索引统计信息而不是索引潜水。默认eq_range_index_dive_limit为10.因此，当设置10个或更多IN条件时，MySQL会跳过索引dive，并从统计信息中估计行数。在这个例子中，MySQL估计360行（1.67M（表t的估计总行数）/ 46K（基数id1）* 10（IN条件）== 360）。

通过增加eq_range_index_dive_limit足够大，MySQL不会错误地估计行。

mysql> set session eq_range_index_dive_limit = 1000;
query OK，0 row affected（0.00秒）

mysql>explain select count（*）from t force index（PRIMARY）where id1 in（1,2,3,4,5,6,7,8,9,10）\ G
*************************** 1.行******************** *******
           id：1
  select_type：SIMPLE
        table：t
         type：range
possible_keys：PRIMARY
          key：PRIMARY
      key_len：8
          ref：NULL
         rows：937684
        extra：using where;using index
1 row（0.00 sec）

设置10个或更多的IN条件是很常见的，不均匀分布的索引也很常见。 eq_range_index_dive_limit有助于减少查询执行计划的index dive成本，但我们认为10太小了。MySQL 5.7目前默认设置为200

第一次发表在

http://www.yougemysqldba.com/discuz/viewthread.php?tid=500&extra=page%3D1

转载于:https://my.oschina.net/markwang/blog/789148