MySQL中ICP的特性是什么
MySQL中ICP的特性是什么,相信很多没有经验的人对此束手无策,为此本文总结了问题出现的原因和解决方法,通过这篇文章希望你能解决这个问题。
专注于为中小企业提供网站设计、网站制作服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业平定免费做网站提供优质的服务。我们立足成都,凝聚了一批互联网行业人才,有力地推动了上千企业的稳健成长,帮助中小企业通过网站建设实现规模扩充和转变。
一、ICP简述
ICP:全称为Index Condition Pushdown,是MySQL 5.6引入的一项优化策略。简单的来说就是将本该在MySQL进行过滤的条件下推到Innodb引擎层去做。但是这种策略和我们平时说的使用到了索引实际上是不同的,我们平时说的用到了索引一般指的是使用到了索引进行定位和访问,但是这里却是一种过滤操作。严格意义上来讲和MySQL层的过滤区别并不大,但是由于这里过滤发生在Innodb层,并且还没有进行回表和加行锁操作(for update),因此优点有如下几点:
减少了回表操作
减少了回表后主键加锁(for update),但是对于查询索引而言加锁没有变化。
减少了返回给MySQL层数据的数据
如果在执行计划中出现了Using index condition则说明进行了下推操作,如果想禁用ICP特性则简单设置一下即可,如下:
set optimizer_switch='index_condition_pushdown=off';
下推过滤的操作是需要下推的条件和进行Innodb层定位的条件同时包含在同一个组合索引才能完成,举个列子比如索引包含(a ,b,c)三列,如果是(where a=* and c like ‘%%’),那么下推才能完成。
当然还有很多其它的限制这里不列出来了,可以自己参考一下官方手册:
8.2.1.5 Index Condition Pushdown Optimization
二、WHERE条件解析
为了方便讨论,我们使用下面的列子:
mysql> show create table bgicp \G *************************** 1. row *************************** Table: bgicp Create Table: CREATE TABLE `bgicp` ( `id` int(11) NOT NULL, `a1` int(11) DEFAULT NULL, `a2` varchar(20) DEFAULT NULL, `a3` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a1` (`a1`,`a2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> select * from bgicp; +----+------+------+------+ | id | a1 | a2 | a3 | +----+------+------+------+ | 1 | 1 | g1 | g1 | | 2 | 2 | g2 | g2 | | 3 | 2 | g3 | g3 | | 4 | 4 | g4 | g4 | | 5 | 5 | g5 | g5 | | 6 | 6 | g6 | g6 | | 7 | 6 | g7 | g7 | | 8 | 6 | g7 | g8 | | 9 | 9 | g9 | g9 | | 10 | 10 | g10 | g10 | +----+------+------+------+ mysql> desc select * from bgicp where a1=6 and a2 like '%7'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | bgicp | NULL | ref | a1 | a1 | 5 | const | 3 | 11.11 | Using index condition | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
这里的where条件会存储在st_select_lex.m_where_cond 中。下图就是这种关系:
这里实际上我们可以通过gdb进行一下验证,断点可以放到st_select_lex::prepare上,如下:
and符号:
(gdb) p ((Item_cond *)m_where_cond)->functype() $43 = Item_func::COND_AND_FUNC (gdb) p ((Item_cond_and *)m_where_cond)->list->elements $64 = 2 (这是AND中的元素个数,这里是2,如上图)
=符号:
(gdb) p ((Item_cond_and *)m_where_cond)->list->first->info $47 = (void *) 0x7ffe7c007690 (gdb) p ((Item_cond*)((void *) 0x7ffe7c007690))->functype() $48 = Item_func::EQ_FUNCa = 6 条件:
(gdb) p ((Item_func_eq*)((void *) 0x7ffe7c007690))->args[1] $49 = (Item *) 0x7ffe7c006888 (gdb) p ((Item_int *)$49)->value $50 = 6 (这是值6) (gdb) p ((Item_func_eq*)((void *) 0x7ffe7c007690))->args[0] $59 = (Item *) 0x7ffe7c007570 (gdb) p ((Item_field *)$59)->field_name $60 = 0x7ffe7c0067c0 "a1" (这是字段a1)like符号:
(gdb) p ((Item_cond_and *)m_where_cond)->list->first->next->info $51 = (void *) 0x7ffe7c006b60 (gdb) p ((Item_cond*)((void *) 0x7ffe7c006b60))->functype() $52 = Item_func::LIKE_FUNCa2 like ‘%7’ 条件:
(gdb) p ((Item_func_like *)((void *) 0x7ffe7c006b60 ))->args[1] $54 = (Item *) 0x7ffe7c006aa8 (gdb) p ((Item_string *)$54)->str_value $55 = {m_ptr = 0x7ffe7c006aa0 "%7", m_length = 2, m_charset = 0x2e3bcc0, m_alloced_length = 0, m_is_alloced = false} (这是值 %7) (gdb) p ((Item_func_like *)((void *) 0x7ffe7c006b60 ))->args[0] $57 = (Item_field *) 0x7ffe7c007830 (gdb) p ((Item_field *)$56)->field_name $58 = 0x7ffe7c0069e0 "a2" (这是字段a2)通过这种方法我们也可以查看下推的具体条件是什么,下面我们来看看
三、ICP条件下推
实际上整个下推完成后如下图,也会是(a2 like ‘%7’)这个条件进行了下推:
条件下推的时候会调用ha_innobase::idx_cond_push函数进行下推,我们来看看上面的语句具体下推了什么条件到Innodb层。我们还是通过上面debug方式来进行,断点可以设置在ha_innobase::idx_cond_push上。具体的查看方式如下:
(gdb) p ((Item_cond *)pushed_idx_cond)->functype() $67 = Item_func::LIKE_FUNC (这是like 操作) (gdb) p ((Item_func_like *)((void *) $66 ))->args[1] $68 = (Item *) 0x7ffe7c006aa8 (gdb) p ((Item_string *)$68)->str_value $69 = {m_ptr = 0x7ffe7c006aa0 "%7", m_length = 2, m_charset = 0x2e3bcc0, m_alloced_length = 0, m_is_alloced = false} (这是字符串'%7') (gdb) p ((Item_func_like *)((void *) $66 ))->args[0] $70 = (Item *) 0x7ffe7c007830 (gdb) p ((Item_field *)$70)->field_name $71 = 0x7ffe7c99618f "a2" (这是字段a2)
四、ICP条件下推的使用
如上图,一旦(a2 like ‘%7’)条件下推过后,Innodb层就可以使用它了,使用流程大概如下:
Innodb扫描一条数据(Innodb层):本例中就是根据条件“a1=6” 获取一行数据。
对索引记录加行锁(Innodb层):比如for update语句是需要加行锁的。
根据下推条件进行数据过滤(Innodb层):本例中就是根据条件“a2 like ‘%7’” 进行数据的过滤。
进行回表操作,并且对回表后的主键记录加行锁(Innodb层):比如for update 语句是需要回表后对主键加行锁的。
返回数据给MySQL层,并且进行过滤(MySQL层):这里也就是进行其他条件的where过滤了,如果不符合条件还会进行提前解锁这行记录(RR,RC都可以),参考末尾栈帧1。
上面就是ICP使用的过程了,我们可以发现对于这种流程来讲,我们最开始总结的优点都体现出来了,它确实有机会提高查询效率。
五、其他的下推时机
实际上下推操作并非总是如例子中所描述的那样,其实总结一下
这个过程会通过row_search_mvcc->row_search_idx_cond_check->innobase_index_cond完成,如果我们没有开启ICP或者没有ICP下推的条件那么会直接不做判断,即row_search_idx_cond_check函数会直接返回。
对于函数innobase_index_cond而言也比较简单,但是他会先做一个对type=range方式的结束位置的判断如下:
其他
栈帧1:
#0 lock_rec_unlock (trx=0x7fffd7803b10, block=0x7fff44598370, rec=0x7fff44fd40fc "\200", lock_mode=LOCK_X) at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:4365 #1 0x0000000001bbb70a in row_unlock_for_mysql (prebuilt=0x7ffe70af2ec0, has_latches_on_recs=0) at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/row/row0mysql.cc:3278 #2 0x0000000001a52954 in ha_innobase::unlock_row (this=0x7ffe70af20f0) at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9237 #3 0x00000000014e3379 in rr_unlock_row (tab=0x7ffe70b02460) at /mysqldata/percona-server-locks-detail-5.7.22/sql/records.cc:821 #4 0x0000000001582226 in evaluate_join_record (join=0x7ffe70afe778, qep_tab=0x7ffe70b02460) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1705 #5 0x0000000001581372 in sub_select (join=0x7ffe70afe778, qep_tab=0x7ffe70b02460, end_of_records=false)
看完上述内容,你们掌握MySQL中ICP的特性是什么的方法了吗?如果还想学到更多技能或想了解更多相关内容,欢迎关注创新互联行业资讯频道,感谢各位的阅读!
网站栏目:MySQL中ICP的特性是什么
文章位置:http://pwwzsj.com/article/poscdo.html