MySQL 索引查找效率对比 mysql索引范围查找

转载

mob64ca140c75c7 2023-10-13 21:12:03

文章标签 MySQL 索引查找效率对比 mysql sql 优化 range 文章分类 MySQL 数据库

范围查询的优化
范围访问方法使用单一索引去检索表中的数据包含一个或者多个索引值的行记录。范围查询能够用于单一索引或者联合索引。下面的部分说明了优化器使用范围查询的条件。
>范围访问方法对单一索引
对于单一索引，索引间隙值可以很容易的通过在where条件中正确的表现出来，表示为区域条件而不是间隙值
给一个单一索引值定义一个区域条件如下：
1）对于BTREE和HASH索引，当使用=,<=>,in(),is null,和is not null操作来比较一个索引的部分值时就是一个区域条件。
2）另外，对于BTREE索引，当使用>,<,>=,<=,BETWEEN,!=,和<>操作来比较一个索引的部分值时或者通过like比较，并且参数不是以通配符开头的比较，都可以是区域条件。
3）对于全部索引类型，多个索引区间的条件以OR或者AND形式的单区间来结合而成。
在之前描述的"常数值"可以是下列几种中的一种：
1）来自查询字符串的常量
2）来自相同的连接，且是const表或者是system表中的列
3）一个无关子查询的结果
4）任何由上述子表达式组成的表达式
下面给出一些在where条件中使用区域条件查询的例子：
SELECT * FROM t1
WHERE key_col > 1
AND key_col < 10;

SELECT * FROM t1
WHERE key_col = 1
OR key_col IN (15,18,20);

SELECT * FROM t1
WHERE key_col LIKE 'ab%'
OR key_col BETWEEN 'bar' AND 'foo';
一些非常量值也许可以通过优化器的常量转变调整而转变为常量
MYSQL尝试获取区域条件从WHERE条件表达式中对于每一个可能的索引值。通过获取过程，删除没有用于构造区域查询的条件，合并那些重合的条件，并且移除那些产生空区域的条件。
例如下面的语句，where条件中的key1是一个索引列，且nonkey是没有索引的列
SELECT * FROM t1 WHERE
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z');
对于key1的提取过程如下：
1）从最原始的where条件开始：
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')
2）移除nonkey=4 和key1 like '%b'，因为他们不能用于一个区域扫描。而正确的移除方式是通过用TRUE来取代他们，所以当我们用True取代表达式以后，我们没有错过任何的匹配行，取代后如下：
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')
3）找到那些始终为TRUE或者false的条件：
(key1 LIKE 'abcde%' OR TRUE) is always true #始终为true
(key1 < 'uux' AND key1 > 'z') is always false #始终为false
通过常量值取代这些条件，如下：
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
移除无用的true和false条件值：
(key1 < 'abc') OR (key1 < 'bar')
4）合并在同一属性上重复的范围，最终用于区域查询的条件为：
(key1 < 'bar')
大体上（同过上面的例子说明),使用于一个区域扫描的条件表达式没有where表达式中的条件严格。mysql会执行一个额外的查询对于满足区域查询但是不满足where条件表达式的数据。
区域条件的提取过程能够处理任意嵌套深度的AND/OR，并且其输出不依赖于条件在where表达式中的顺序。
对于空间索引的范围访问方法，mysql不支持多个范围的合并。为了绕过这个限制，你可以通过union来关联独立的select语句，每一个独立的空间条件在不同的select查询中。
>范围访问方法对联合索引的使用
在联合索引上的区域条件是对单一索引区域条件查询的扩展。在联合索引上的区域条件限制了索引列必须处于一个或多个元组间隙内部。通过使用索引的顺序，要查询的索引集合是由一个索引集合来决定的。
例如，key1(key_part1,key_part2,key_part3)是一个联合索引，下面的索引元组集合的展示顺序如下：
key_part1 key_part2 key_part3
NULL 1 'abc'
NULL 1 'xyz'
NULL 2 'foo'
1 1 'abc'
1 1 'xyz'
1 2 'abc'
2 1 'aaa'

条件key_part1=1定义了区间：
(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)
区间覆盖了第四，第五和第六元组在之前的数据集中，并且能够使用区域访问方法。
相比之下，条件key_part3='abc' 不能定义为一个简单的区间也不能使用区域访问方法。
下面详细的描述了怎么样的区域条件对联合索引是有效的：
1）对于hash索引，每个包含相同值的区间条件都能被使用。也就是说区域可以生成只要是如下形式的条件：
key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;
其中，const1,const2,...是常量，cmp是=,<=>,is null操作符中的一个，并且条件覆盖了全部索引部分。(也就是说，那儿有N个部分，但是每个都是联合索引的一部分)例如，下面是一个区域条件对于三部分联合的索引来说：
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
至于对常量的定义，请看单一索引中使用区域访问方法中的定义。
2）对于BTREE索引，一个区间也许用于AND条件的合并，其中每一个条件都是通过一个常量使用=,<=>,is null,>,<,>=,<=,!=,<>,between或者是like '模式'(其中模式的开头不能是通配符)来比较索引值的一部分。一个区域能够被使用只要它能定义一个简单的key元组包含了所有的匹配条件的值(或者是两个区域，如果使用了<>或者是!=的话)。
优化器试着通过额外的key部分来定义一个区间只要比较的操作是=,<=>,或者是is null.如果操作符是 >, <, >=, <=, !=, <>, BETWEEN, or LIKE,优化器会使用区域，但是不会考虑添加额外的key部分。对于如下的表达式，优化器使用了来自第一个比较符中的=号，也使用了第二个比较符中的>=但是在生成区域的过程中不再继续考虑其他key部分也不会使用第三个操作符。
key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
一个简单的区域查询是：('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)
可能被创建的区域中包含了比初始化条件更多的行。例如区域中包含的值('foo',11,0),其并不满足初始条件。
如果条件覆盖的集合数据来自区域通过OR操作产生的数据，他们形成的条件覆盖了他们区域union的行数集合。如果条件是通过AND操作结合的，他们形成的条件覆盖的结果集在他们区域结果集的交集中。例如，如下两部分联合索引的条件：
(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
区间是：
(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)
在这个例子中，第一行的区间的左边使用了联合索引的第一部分，右边使用的是联合索引的第二部分。
第二行的区间只使用了联合索引的第一部分。在EXPLAIN输出的key_len的值将说明索引前缀使用的最长的部分。
在一些情况下，key_len说明了被使用的索引部分，但是也许那并不是你想要的。假设key_part1和key_part2能够为null,那么，对于key_len列展示的两部分的长度，对于条件：key_part1 >= 1 AND key_part2 < 2
但是实际上，条件被转换为：key_part1 >= 1 AND key_part2 IS NOT NULL
对于单一索引使用范围访问方法，说明了优化器是怎么样结合或是消除区间为了区域条件在一个单一索引上。而对于联合索引也有类似的步骤。
>多值比较类似于范围优化
考虑这样一个表达式，其中col_name是一个索引列
col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN
如果col_name和其中的一些值是等价的，那么每一个表达式都是true.这种对比和范围对比是等价的(”范围“是一个简单的值)。优化器评估读取符合条件的行数通过两种不同方式的代价如下：
1）如果在col_name上的是一个唯一索引，则评估对于每个范围内的值都是1，因为对于给出的一个值，最多只有一行数据返回。
2）否则对于其他非唯一索引在col_name列上，优化器通过嵌入索引或者索引的统计中来评估每一个范围内符合条件的行数。
随着索引的深入，优化器会选择出一个深度对于结束每一个范围查询，并且使用在范围中的行数来做出评估。例如，表达式为col_name in (10,20,30)有三个等价的范围，优化器在每个范围内进行两次深入确定行数来做评估。每两次生成的行数都是对被给出值的一次行数评估。
索引深入提供了准确的行数评估，但是当在表达式中的值越来越多时，优化器需要花费更长的时间来评估一行。对于大量值的集合通过使用索引统计来做行统计评估在损失一定准确度的情况下，会更高效。
eq_range_index_dive_limit的系统变量能够被配置用来给优化器选择从一行到另一行评估需要的值的数量。为了允许对N个有效范围使用索引深入来完成比较操作，必须设置eq_range_index_dive_limit为N+1.为了不使用索引统计，而总是使用索引深入而不考虑N.那么设置eq_range_index_dive_limit 为0.
为了更好的统计，使用analyze table命令来更新索引统计。
即使是如下条件，索引深入将不被使用，他们将会被忽略在查询中，如果满足如下条件的话：
1）一个单一索引的索引存在一个强制索引指数。也就是说如果索引被强制使用，那么通过额外的在索引中的深入查询是得不到任何有用信息的。
2）索引是非唯一索引，也不是fulltext索引。
3）没有子查询存在
4）没有DISTINCT,GROUP BY,或者order by存在。
这种忽略条件只有在单表查询中有效，对于多表连接的查询，索引深入策略(index dives)不会被忽略。

>限制范围查询优化使用的内存。
为了控制范围优化器使用的内存变量，通过使用range_optimizer_max_mem_size系统变量来设置。
1）0表示没有限制。
2）使用一个大于0的值，优化器在考虑范围查询方法时，会跟踪被消费的内存。如果存在特别的限制，则会放弃使用范围查询方法而采用其他方法比方说用全表扫描来代替。这不是最佳方案。如果真的发生了，将会产生如下的警告(其中N是当前range_optimizer_max_mem_size的值)：
Warning 3170 Memory capacity of N bytes for //给变量设置了Nbytes大小的内存，
'range_optimizer_max_mem_size' exceeded. Range
optimization was not done for this query. //使得范围优化器不能使用在当前查询中。
对于超过范围查询可用内存大小的单个查询并且最终优化器选择了非优化方案，可以通过增加range_optimizer_max_mem_size的值来提高性能。
对于一个范围查询表达式需要使用的内存数量大小的评估，可以参考如下指导：
1）对于类似如下的简单查询，其中只有一个候选的key在查询中，每一个通过OR结合的述语大概是230bytes：
SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
2）对于类似如下的查询，每一个通过AND结合的述语大概是125bytes：
SELECT COUNT(*) FROM t
WHERE a=1 AND b=1 AND c=1 ... N;
3）对于查询中使用了in述语的:
SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
每一个在in()中的字面值作为一个述语结合通过OR操作。如果有两个in()的列表集合，那么通过OR述语结合的数量是每一个列表中字面值数目的数目。这样的话，通过OR结合的数目就是M*N中，正对上述的例子。
在5.7.11版本之前，通过OR结合占用的内存更大，估计700bytes。

>关于行结构表达的范围查询优化
优化器可以使用范围扫描查询方法对于如下这样的查询：
SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));
以前，如果要使用范围扫描，则必须将查询写为如下形式：
SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );
为了优化器使用一个范围扫描，则查询必须满足这些条件：
1）只有in述语使用，没有使用not in();
2）在 in()述语的最左边，行结构包含的只有列应用；
3）在in()述语的右边，行结构包含的只是运行时常量，要么是文字常量，要么就是通过绑定常量执行生成的本地列引用。
4）在in()述语的右边，不会有多余一个的行结构。
更多关于行构造器和优化器的信息，请看8.2.1.18的行结构表达式的优化。
到此关于range查询的优化就结束了，其中有些也许并不是很准确，但是大致意义没有问题，接下来我们要处理的就是1.3的索引合并的优化。