当前位置:首页 > mysql > 正文内容

mysql中eq_range_index_dive_limit参数学习

phpmianshi4年前 (2017-04-12)mysql376

概念

官方文档如下描述:
This variable indicates the number of equality ranges in an equality comparison condition when the optimizer should switch from using index dives to index statistics in estimating the number of qualifying rows. It applies to evaluation of expressions that have either of these equivalent forms, where the optimizer uses a nonunique index to look up col_name values:

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

In both cases, the expression contains N equality ranges. The optimizer can make row estimates using index dives or index statistics. If eq_range_index_dive_limit is greater than 0, the optimizer uses existing index statistics instead of index dives if there are eq_range_index_dive_limit or more equality ranges. Thus, to permit use of index dives for up to N equality ranges, set eq_range_index_dive_limit to N + 1. To disable use of index statistics and always use index dives regardless of N, set eq_range_index_dive_limit to 0.


这是在说: 优化器为每一个范围段(如“a IN (10, 20, 30)”或者 a=10 or a=20 or a=30 是等值比较, 括3个范围段实则简化为3个单值,分别是10,20,30)估计每个范围段(用范围段来表示是因为MySQL的“range”扫描方式多数做的是范围扫描,此处单值可视为范围段的特例)中包括的元组数, 而估计方法有2种,一是dive到index中即利用索引完成元组数的估算,简称index dive; 二是使用索引的统计数值,进行估算

相比这2种方式,在效果上:

1 index dive: 速度慢,但能得到精确的值(MySQL的实现是数索引对应的索引项个数,所以精确)

2 index statistics: 速度快,但得到的值未必精确,例如索引统计信息计算出每个等值影响100条数据,那么IN条件中包含5个等值则影响5*100条记录


在MySQL 5.6版本中引入eq_range_index_dive_limit参数,默认值为10。如果eq_range_index_dive_limit大于0,如果有eq_range_index_dive_limit或更多相等范围,优化器将使用现有索引统计信息而不是索引潜水。当设置10个或更多IN条件时,MySQL会跳过索引dive,并从统计信息中估计行数,通常业务在使用IN时会超过10个值,因此在MySQL 5.7版本中将默认阀值设为200。


为什么要区分这2种方式呢?


简单地说:

1 查询优化器使用代价估算模型计算每个计划的代价,选择其中代价最小的

2 单表扫描时,需要计算代价;所以单表的索引扫描也需要计算代价

3 单表的计算公式通常是:代价=元组数*IO平均值

4 所以不管是哪种扫描方式,都需要计算元组数

5 当遇到“a IN (10, 20, 30)”这样的表达式的时候,发现a列存在索引,则需要看这个索引可以扫描到的元组数由多少而计算其索引扫描代价,所以就用到了本文提到的“index dive”、“index statistics”这2种方式。


场景

1、唯一条件的等值查询也不会使用索引下探(= in or )。

2、一般是非唯一索引或者范围查询(< > <= >=)才会用到索引下探,实际上他们都是‘RANGE’。


示例

1、检查参数是否开启

show variables like '%eq_range_index_dive_limit%';
#结果
eq_range_index_dive_limit    10

2、修改参数

set eq_range_index_dive_limit=100;

3、索引下探

"analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "sex",
                        "ranges": [
                          "M <= sex <= M"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 29,
                        "cost": 35.81,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],

4、禁用索引下探

"analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "sex",
                        "ranges": [
                          "M <= sex <= M"
                        ],
                        "index_dives_for_eq_ranges": false,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 15,
                        "cost": 19.01,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],



版权声明:本文由PHP面试资料网发布,如需转载请注明出处。
分享给朋友:

相关文章

mysql中count(x)到底慢不慢

总结MyISAM 表不支持事务,但是count(*) 很快,因为他直接记录了一个总数MyISAM在统计表的总行数的时候会很快,但是有个大前提,不能加有任何WHERE条件。这是因为:MyISAM对于表的...

mysql中Query语句对系统性能的影响

背景我们重点分析实现同样功能的不同 SQL语句在性能方面会产生较大的差异的根本原因,并通过一个较为典型的示例来对我们的分析做出相应的验证。为什么返回完全相同结果集的不同SQL语句,在执行性能方面存在差...

mysql中AnalyzeTable优化

Analyze TableMySQL的Optimizer(优化元件)在优化SQL语句时,首先需要收集一些相关信息,其中就包括表的cardinality(可以翻译为“散列程度”),它表示某个索引对应的列...

mysql中performance_schema(三) 实践篇

背景前一篇文章我们分析了performance_schema中每个表的用途,以及主要字段的含义,比较侧重于理论的介绍。这篇文章我主要从DBA的角度出发,详细介绍如何通过performance_sche...

mysql中Query语句优化基本思路和原则

基本思路和原则在分析如何优化 MySQL Query 之前,我们需要先了解一下 Query 语句优化的基本思路和原则。一般来说,Query 语句的优化思路和原则主要体现在以下几个方面: 1. 优化更需...

mysql中锁详解读这一篇就够了

InnoDB的七种锁1. 自增锁(Auto-inc Locks)2. 共享/排他锁(Shared and Exclusive Locks)3. 意向锁(Intention Locks)4. 插入意向锁...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。