MySQL - RANGE优化篇

2018-01-13 11:01:24来源:http://blog.battcn.com/2018/01/03/mysql/mysql-range-optimiza作者:唐亚峰人点击

分享


Range Access
使用单个索引的方式
来检索包含在一个或多个索引值区间内的表行的子集。它也适用于单列或复合(组合)索引…


单列索引


对于单列索引,索引值区间可以方便地用WHERE语句中的相应范围条件表示
。优化器在常量传播阶段,会将一些非常量值转换为常量。


定义


对于BTREE索引和HASH索引来说,索引的范围优化基本上只适用于等值查询。譬如=, <=>, IN(), IS NULL, IS NOT NULL操作符。
对于HASH索引,BTREE索引同样支持非等值查询,譬如>, <, >=, <=, BETWEEN, !=, <>和LIKE(注意,like的常量值
不能以通配符开头)
对于所有索引类型,多个范围条件与OR
或AND
在一起会形成一个范围条件

代码示例


以下是在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会尽可能从索引中提取范围条件,在提取过程中,利用索引将不能提取范围条件的过滤掉,然后对剩下的数据进行额外的筛选


代码示例


参考以下句子,其中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');

提取步骤



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.使用 TRUE 来替换不能进行范围扫描的nonkey = 4和key1 LIKE'%b'
,因此会产生:


(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')

3.折叠始终为真或者假的条件


(key1 LIKE 'abcde%' OR TRUE)
总是如此
(key1 < 'uux' AND key1 > 'z') 总是假的

常数替代


(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子句中的顺序。


复合索引

复合索引的范围条件是单个索引范围条件提取的扩展


限制内存


使用系统变量range_optimizer_max_mem_size
可以控制优化器对范围优化时使用的内存数


0 意味着不做任何限制
大于0,则优化器在操作时发现超出指定限制后将会改变策略(如:全表扫描),同时还会给出以下警告,所以增加range_optimizer_max_mem_size
值可能会提高性能。

Warning3170Memory capacity of N bytes for
'range_optimizer_max_mem_size' exceeded. Range
optimization was not done for this query.

范围表达式内存估算准则



1.多个OR
组合,每个OR
大概占230字节,在5.7.11
之前约占700
字节,所以慎用


SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;


2.AND
组合,每个大概占用125个字节


SELECT COUNT(*) FROM t
WHERE a=1 AND b=1 AND c=1 ... N;


3.IN
,恐怖如斯,在IN
中每个内容就会视为一个OR
,如果有多个IN,那么该占用的指数是乘积(
M× N


SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
行构造器


MYSQL写法优化的一种,简化了书写,不过这种方式只支持IN
,目前为止NOT IN
是不被支持的


优雅写法


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' );
总结


最好的优化方案,跟着新版本走
推陈出新
,新版中不仅扩展更多功能,同时会加强优化力度。虽然MySQL优化器
为我们做了很多事情,但开发过程中该主意还得注意。


说点什么


关注微信公众号:battcn
后台回复
mysql
即可获得《打造扛得住的MySQL数据库架构》


个人QQ:1837307557
battcn开源群(适合新手):391619659

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台