mysqlICP(Index Condition Pushdown) 索引条件下推

语法：explain  select ....

变体：

1.explain extended select ....

将执行计划“反编译”成select语句；

运行show warnings 可以得到被mysql优化器优化后的语句

2.explain partitions select ...

用于分区表的explain

 

运行结果含义：

type： all ，index，range，ref，eq_ref,const,system null  从左到右，最差到最好；

all: full table scan ;mysql将遍历全表以找到匹配的行；

index ： index scan; index 和 all的区别在于index类型只遍历索引；

range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值的行，常见与between ，< ,>等查询；

ref：非唯一性索引扫描，返回匹配某个单独值的所有行，常见于使用非唯一索引即唯一索引的非唯一前缀进行查找；

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或者唯一索引扫描；

const，system：当mysql对某查询某部分进行优化，并转为一个常量时，使用这些访问类型。如果将主键置于where列表中，mysql就能将该查询转化为一个常量。

 

possible keys：

指出mysql能使用哪个索引在表中找到行，查询涉及到的字段若存在索引，则该索引被列出，但是不一定被查询使用。

key：显示mysql在查询中实际使用的索引，若没有使用索引，则显示为null。

key_len:表示索引中使用的字节数，可以通过该列计算查询中使用的索引的长度。key_len显示的值为索引字段的最大长度，并非实际使用长度。即key_len是根据表定义计算而得，不是通过表内检索出的。

ref：表示上述表的连接匹配条件，即哪些列或者常量被用于查找索引上的值；

rows：表示mysql根据表统计信息以及索引选用情况，估算的找到所需记录所需要的行数；

extra：包含不适合在其他列中显示但是十分重要的额外信息

a：using index：该值表示相迎的select操作中使用了覆盖索引（cover index）

b：using where：表示mysql服务器层在收到存储引擎层的记录后进行“过滤”filter；

c：using temporary ：表示mysql需要使用临时表来存储结果集，常见于排序和分组查询；

d：using filesort：mysql中无法利用索引完成排序，称为“文件排序”；

 

 

explain局限：

 

explain不会告诉你关于触发器，存储过程的信息或者用户自定义的函数对查询的影响情况。

explain不会考虑cache。

 

 

ICP(Index Condition Pushdown) 索引条件下推到存储层

MySQL5.6之Index Condition Pushdown(ICP,索引条件下推)

ICP（index condition pushdown）是mysql利用索引（二级索引）元组和筛字段在索引中的where条件从表中提取数据记录的一种优化操作。ICP的思想是：存储引擎在访问索引的时候检查筛选字段在索引中的where条件（pushed index condition，推送的索引条件），如果索引元组中的数据不满足推送的索引条件，那么就过滤掉该条数据记录。ICP（优化器）尽可能的把index condition的处理从server层下推到storage engine层。storage engine使用索引过过滤不相关的数据，仅返回符合index condition条件的数据给server层。也是说数据过滤尽可能在storage engine层进行，而不是返回所有数据给server层，然后后再根据where条件进行过滤。使用ICP（mysql 5.6版本以前）和没有使用ICP的数据访问和提取过程如下（插图来在MariaDB Blog）：

优化器没有使用ICP时，数据访问和提取的过程如下：

1)    当storage engine读取下一行时，首先读取索引元组（index tuple），然后使用索引元组在基表中（base table）定位和读取整行数据。

2)    sever层评估where条件，如果该行数据满足where条件则使用，否则丢弃。

3)    执行1），直到最后一行数据。

优化器使用ICP时，server层将会把能够通过使用索引进行评估的where条件下推到storage engine层。数据访问和提取过程如下：

1)    storage engine从索引中读取下一条索引元组。

2)    storage engine使用索引元组评估下推的索引条件。如果没有满足wehere条件，storage engine将会处理下一条索引元组（回到上一步）。只有当索引元组满足下推的索引条件的时候，才会继续去基表中读取数据。

3)    如果满足下推的索引条件，storage engine通过索引元组定位基表的行和读取整行数据并返回给server层。

4)    server层评估没有被下推到storage engine层的where条件，如果该行数据满足where条件则使用，否则丢弃。

而使用ICP时，如果where条件的一部分能够通过使用索引中的字段进行评估，那么mysql server把这部分where条件下推到storage engine（存储引擎层）。存储引擎通过索引元组的索引列数据过滤不满足下推索引条件的数据行。

索引条件下推的意思就是筛选字段在索引中的where条件从server层下推到storage engine层，这样可以在存储引擎层过滤数据。由此可见，ICP可以减少存储引擎访问基表的次数和mysql server访问存储引擎的次数。

注意一下ICP的使用条件：

1. 只能用于二级索引(secondary index)。
2. explain显示的执行计划中type值（join 类型）为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据，即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)。
3. ICP可以用于MyISAM和InnnoDB存储引擎，不支持分区表（5.7将会解决这个问题）。

 

ICP的开启优化功能与关闭

MySQL5.6可以通过设置optimizer_switch([global|session],dynamic)变量开启或者关闭index_condition_push优化功能，默认开启。

mysql > set optimizer_switch=’index_condition_pushdown=on|off’

用explain查看执行计划时，如果执行计划中的Extra信息为“using index condition”，表示优化器使用的index condition pushdown。

在mysql5.6以前，还没有采用ICP这种查询优化，where查询条件中的索引条件在某些情况下没有充分利用索引过滤数据。假设一个组合索引（多列索引）K包含（c1,c2,…,cn）n个列，如果在c1上存在范围扫描的where条件，那么剩余的c2,…,cn这n-1个上索引都无法用来提取和过滤数据（不管不管是唯一查找还是范围查找），索引记录没有被充分利用。即组合索引前面字段上存在范围查询，那么后面的部分的索引将不能被使用，因为后面部分的索引数据是无序。比如，索引key（a，b）中的元组数据为(0,100)、(1,50)、（1，100） ，where查询条件为 a < 2 and b = 100。由于b上得索引数据并不是连续区间，因为在读取（1，50）之后不再会读取（1，100），mysql优化器在执行索引区间扫描之后也不再扫描组合索引其后面的部分。

表结构定义如下：

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE `person` ( `person_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `postadlcode` int(11) DEFAULT NULL, `age` tinyint(4) DEFAULT NULL, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`person_id`), KEY `idx_p_a` (`postadlcode`,`age`), KEY `idx_f_l` (`first_name`,`last_name`) ) ENGINE=InnoDB  DEFAULT CHARSET=utf8

关闭ICP优化，Extra信息为“Using Where”

1 2 3 4 5 6 7

mysql> set optimizer_switch = "index_condition_pushdown=off"; mysql> explain select  *   from person  where postadlcode between 300000 and 400000 and age > 40; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ |  1 | SIMPLE      | person | range | idx_p_a       | idx_p_a | 7       | NULL |   21 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

开启ICP之后，Extra信息为“Using Index Condition”

1 2 3 4 5 6 7

mysql> set optimizer_switch = "index_condition_pushdown=on"; mysql> explain select  *   from person  where postadlcode between 300000 and 400000 and age > 40; +----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+ | id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                 | +----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+ |  1 | SIMPLE      | person | range | idx_p_a       | idx_p_a | 7       | NULL |   21 | Using index condition | +----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+

 

SHOW PROFILING;

案例一：MySQL Sending data导致查询很慢的问题详细分析

1. 现象：

　　使用show full processlist；观察发现大量相同的sql处于 sending data状态。

　　使用show profile for query 1;查看sending data的时间长达15s，而其它都可忽略。

2. 分析：

　　第一想到的是否结果集太大，但是实质上查询结果集才一行数据。那是什么问题呢？

　　查询一下“Sending data”状态的含义，原来这个状态的名称很具有误导性，所谓的“Sending data”并不是单纯的发送数据，而是包括“收集 + 发送 数据”。

3. 处理：

　　看到这里，不是发送数据的问题，自然是收集数据有问题，业务SQL信息如下：

SELECT *
FROM (`wb_oauth_session`)
WHERE `client_id` =  '10000006'
AND `site_id` =  '53ec30121162c047'
AND `owner_type` =  'member'
AND `owner_id` =  '20459561';

　　使用explain得到是使用了前两列作为索引，于是进行选择性测试。

SELECT count(*)
FROM (`wb_oauth_session`)
WHERE `client_id` =  '10000006'
AND `site_id` =  '53ec30121162c047'；

　　得到的结果集非常大，而mysql的data filter在mysql server层实现，那么存储引擎层必须把这么大的结果集发送到server层进行条件过滤，这个过程花费了时间，是问题所在。

　　经测试owner_id列的选择性最好，于是删除(client_id,site_id)的复合索引，添加owner_id列的索引，优化索引非常明显，从15s到0.01s。