148-索引条件下推(ICP)

好，同学们，咱们接着来看下一个内容，叫做索引下推，严格说呢，它的全称叫索引条件下推，对应的英文呢叫index condition，不知道啊，简称呢叫ICP，平时呢，我们叫的话呢，就叫做索引下推了，这个呢是麦X和5.6当中的一个新特性，是一种在存储引擎层使用索引过滤数据的优化方式，他猛一看大家可能也不太明白是吧？我们下边呢就来讲一讲它这个原理啊，讲这个原理之前呢，我们先扯两句，这个索引下推呢，在面试当中呢，其实也常没问到啊，问你呢说这个缩阴下对呢是怎么回事儿，然后呢，他具体的原理是什么啊？它的好处呢是什么啊，让你呢进行一个刻画，前段时间有个同学面试就被问到了啊，没有说清楚是吧？然后呢，过来就问我，我给大家讲了讲，呃，发现呢，其实也不是特别难，诶，那他也知道我在录制这个MYSQL这样的一套课程啊，希望呢也是早点录完是吧？诶大家看到的时候呢，其实都已经录完了，OK，那么这个索引下推呢，诶那我们接下来就来看一看它到底是怎么样一个原理，其实呢，不是特别难啊，它就跟我们前面讲的这个覆盖索引一样啊这个。

你当你不了解的时候呢，总觉得很高大上，了解以后呢，发现其实也就那么回事啊，嗯，所以下堆话呢，是查询液化器呢，也是优化过程当中一个具体的策略，那我们来学习一下这个概念呢，其实对于大家来讲也不是完全陌生的，我们在上一章讲到这个性能分析工具的时候呢，我们讲plan plan里边呢，有一个字段呢，是不是叫act啊。这个里边呢，有一个值就叫做这个using in condition，它代表的呢，就是索引条件下推。哎，索引条件下推，但是呢，我们讲的这时候呢，是其实呢，简单说了一下，那有同学呢，可能没有理解透彻，我们下边呢，再哎把它讲一讲，这个讲的过程呢，实际上呢，就在讲这个索引条件下推的一个原理，好那这块呢，我们来走一下啊，回过来首先在我们这儿啊，第七个点啊索引。啊条件啊，下推对应的英文啊，所以I条件condition下推不知道是吧，就是我们这样一个词啊，首先的话呢，我们调取到咱们当时的这个表对应的这个数据库啊，咱去use一下叫艾特硅谷DB1。

那使用一下我们这样的一个数据库，然后在这个数据库下呢，哎，我们去explain一下啊，把这个呢粘过来。好，选中。执行可以了是吧，这呢表示的就是索引条件下推这样的一个场景的使用，那产景优化器呢，是怎么考虑的呢？那我们下面来讲解一下这个讲解的过程啊，哎，其实呢，就是我们要说的这样一个原理。那我们看到了，在这个K1这个字段上呢，它实际上是有个索引的，那这其实也是一个前提，得有索引，好，那么有这个索引呢，然后接着呢，我们怎么去考虑呢？哎，我们以为啊，注意这是我们以为的啊，哎，是这样子的，我们这块呢，是不是K1大于，呃，这个大于Z，然后后边这块呢，相当于本身这个K1上的，你看它是一个已模糊开头的，它也用不上这个索引了，对吧？那我们只考虑前面这个字段，当我们通过K1所在的这个索引呢，去查询数据的时候，满足大于Z的呢？假设呢，我们找到了100条这个对应的数据。啊，就点点点了那么100条数据，我们找到的每一条的话呢，诶，我们这是一个二级索引，相应的我们是不是都要回表操作呀。

那回表到我们这个句子索引上，我们看第一条数据呢，对应的这个数据在这个句子索引当中是哪个位置找到这个，然后第二个也同样的如此，点点点是不是就100个啊，一个一个的都过来这样去找，然后你找到这，哎这个句子索引当中，叶子键中每一个数据的时候呢，你再去判断啊，这里边的这个K1这个字段呢，是不是满足这样的特征呢？如果满足呢，我们就要不满足了，就不要了，那100次的这个回表操作，然后呢，再经过这样的一个条件的过滤，假设呢，我们诶只找到这个十条，那这个最终的结果呢，相当于我们只要这十条数据就可以了。这个呢，是我们以为这样的一个过程对吧？好，那么真实的这个长液化器它是怎么做的呢？他是这样做的，注意看，那我们首先呢，查出来这一盘100条记录，这个呢是没问题的，但是呢，并不是我们这时候呢，100条记录都要进行回报操作，而是呀，我们在这100条数据的查询之后，咱就开始进行后一个条件的判判别了。

条件下推，下推到哪儿，就是先不要着急回本，而是下推到我们后边这个条件当中啊，推到这儿是吧，不值档到这儿，但是你看啊，我们这个K1所在的这个索引当中，是不是也有后边这个字段啊，因为你看我这个呢，举的例子比较特别，因为前面是K1，后边恰好也是K1啊，咱们就先说这种场景，那我这个呃，这个索引当中是不是有这个K1字段，有这K字段呢？你说我这100条数据当中，是不是就可以在这个位置呢去判断啊，你现在这100条记录当中是不是都有满足这样的条件的呀。对吧，诶我们就不用着急先去回表了，现在这100个里边先过滤一遍，假设呢，我们过滤一般完以后呢，就剩十条了，那么接下来我们再去回表操作，但是你想这个时候呢，我们是不是只需要回表十次就可以了，那我们这种场景呢，其实就是ICP。那么我们回表这十次呢，只需要到这个句子索引当中只找这十个十个就行，性能呢，比之前要好很多，相当于呢，我们减少了回表的次数。同时还少什么量，大家你想，如果你原来呢，这块呢，直接我们回去呢，是有100啊是吧，啊100个这个数据，100个的话，你对应的每一个数据呢，在相应的数据页当中，这个数据的话呢，是不是它可能也不是连续的，那我们要把它在加载的内存当中的话呢，你这一条数据呢，所在页都要加载，是不是又是随机IO，而我们现在呢，是十个数据，那我随机IO可能只需要呢十个是吧，十次就可以了。

啊，注意我这块呢是100这个十，那假设的话呢，我们现在呢，我这块查到的假设是1万条数据，而我们第二条件过滤完以后呢，假设就只有一条数据，我觉得稍微夸张一点，甚至说呢就没有了，那你想想如果说呢，你要是没有用这个ICP的话呢，直接这1万条数据呢，都这个回表，然后呢，再去随机IO加载我们这个数据，这个呢，性能跟我们先过滤完以后呢，就剩一条数据了，然后我们再回表一下，就只把这一条数据所在数据呢加载一下，你想想这个性能能够差别有多大呢。哎，是不是很大呀，OK，那这呢，就是我们这个缩阴下垂的一个原理。啊，那这个例子的话呢，应该说不是特别的典型啊，为什么不是特别典型啊，因为这时候呢，大家你会看到我这是K1，这也是K1，就是不太能够展示更一般的一个场景，那下边我再给大家举个例子啊，所以这呢，我们写成了叫举例一。

好，把这两个例子呢，举完之后呢，我们再来看它的一些呃相关的一些说明情况，呃举例二，好，那么这个举例二的话呢，大家直接打开我们这个课件，然后在下边呢，又举了一个例子啊，我们把直接这个直接这个代码呢，我们就粘过来啊，直接拿过来一个啊这呢是我们创建这张表，然后呢，往这个表里边呢，添加几条数据。嗯，没问题是吧，好添加角度数据以后呢，我们相应的去做一个select的一个查询操作，好我们这块呢，把它也粘过来写。来我们先这样呢去执行，我呢把这个表呢先创建一下，那不妨我就还在这个艾特硅谷DB这样一个数据库下吧。嗯，可以啦，然后呢，我们去添加这样的几条记录。嗯，好完事了，那这块我们先这样查询一下看看啊，还有这个数据，那在前面呢，我们去加上一个叫。

好，我们选中。哎，执行那可以了，当然你会以发现呢，这时候呢，是不是也有这个叫using inex condition是吧，那我们把这个例子呢，也再解释一下，它呢，其实是更具备这个叫普遍的这样一个特性，也就是呢，我们这个索引条件下推啊，更多的时候呢，其实针对的是联合索引啊，就是我们这里边这个情况，但是你看我这里边呢，针对这个表有一个索引叫，诶针对我们叫zip code last name和这个first name，我这是建来了，是不是一个叫联合索引啊，诶三个字段行，然后呢，咱们在这块查询的时候，我呢查询的这叫zip code是不是跟它对应了，然后下边的这个last name呢，是不是跟这个对应了，后边这个呢，就没对应上，OK，没关系，行，这是我刻意这样设计的啊，然后呢，我们在这个查询的时候呢，咱们是不是使用上了这样的一个索引吧。啊，显然是没有问题的，那么使用这个索引的时候呢，大家你会发现我们第一个字段用上了，这是很好的，第二字段的话呢，我这用的like是不是百分号开头的，这个我们就用不上了，相当于我们这个索引的话呢，是不是只用了这一部分啊，大家通过这个Les这块呢，也能够看得到，对吧，没问题。

好，那么我们以为的这个查询的过程是什么啊，当然我说的这个以为的其实就是咱们认为就没有ICP是什么样个场景，对吧？那就是我们针对这样的一个值有效到这儿了，咱们就相当于是查询了满足这个条件的是不是，诶从你这个索引当中，我们找到了一波数据，然后这波数据呢，我们每一条是不是都开始叫回表了，回表找我们这个，呃，主键索引，或者叫居出索引，然后呢，我们诶再找，比如我们这儿呢，假设有这个，咱这个表里边数据少啊，假如我们这个呢，有这个1000条吧。但是这有1000条这个数据满足我们回去呢，回报1000次，然后呢，这个在这1000个对应的你这个数据项，其实呢，你这个每一项还都得以数据热的方式呢，加载到这个内存当中才行，对吧，然后的话呢，我们在这个里边呢，再去找满足第二个条件，满足第三个条件呢，做一个过滤，过滤完以后呢，有可能就只有十条数据。是不是这样个场景了，OK，好，那我们呃说有ICP呢，是什么样子呢？有ICP的话呢，咱们会发现在我们这个索引当中，除了我们这个zip code之外呢，第二个条件呢，虽然说我们用不上索引了，但是呃，咱们现在呢，有叫索引条件下推，我们在这个索引当中呢，它包含这个字段，相当于我们能不能在这块呢，去判断一下拉斯内like克有章的这样个情况呀，诶是可以的，对吧，诶同学说老师这个可不可以，这个就不可以了，因为咱们这个联合索引当中是不是就没有这个字段了。

诶，我可以这样设计的，就是体现更一般的一个场景，好，那就相当于我们除了这个Zippo呢，过滤好之后呢，虽然说下边这个呢，你没办法去使用这个索引这一部分了，但是我们可以呢，去做个过滤，我就只找出来有章的，那这时候一过滤发现呢，我们假设啊，就只剩100条数据了。对吧，那100条数据，那跟这个还能不能再去用一下，用不了了，我们只能拿着这100条数据，咱们去做一个汇报操作，这个呢，就是咱们说的叫ICP。那么回去之后呢，我们在这100个里边，呃，你一个呢，都加载一下，然后呢，再去看一下这100个里边呢，哪个是不是满足这个条件啊，哎，然后你再过滤一下，发现诶只剩十条了，那这呢就相当于是使用ICP呢，是不是减少了我们这个回表的次数，那对应的话呢，你回表以后呢，我们减这个，把这一条数据呢，你再加载到内存中的时候呢，是不是也减少了这个随机IO的一个次数啊。

啊，这是它的一个性能的好处。好，那么这呢是我们说的这样个场景，那咱们在我要是再举个例子的话呢，比如说我就具体呢写明一个，这呢是更具咱们更具备咱们以前讲的这样个情况，比如说咱们这个last呢，假设它就是啊，咱叫like也行，假设呢就张开头的是吧，然后再写一个，比如说这个叫first name。啊，我这边再加一下。啊，比如for them like啊，这个我们叫，比如叫在三开头的。啊，这么着是吧，哎，大家呢，应该能明白我这里边想要刻画是什么一个意思吧，啊就相当于我们前面呢讲过联合索引，说这个呢，呃这个缩引条件下推，你看这它也写有了是吧？但是这个时候呢，其实我们呃真正意义上来讲的话呢，还呃没有起到多大的作用啊为什么这么说呢？我们对于这种场景来讲的话呢，你看我们这儿呢，是一个确定的，这呢也算是我们能够应用这个呃索引的地方是吧？这呢是不是也是能够应用索引的地方呀。

啊，我也就是说呢，我们这个时候呢，你在使用我们这个索引的时候呢，它本身呢，就会考虑第一个字段，第二个字段，第三个字段，咱们就都考虑上了，然后呢，我们这时候再回表，其实呢，你是体现不出来说我们这个索引条件下推它到底这个好处啊是什么对吧？哎，我们体现这个好处呢，主要就是针对呢，在你这个索引当中有这个字段。但是这个字段呢，它又失效了，你没办法去使用这个索引的这个部分，那这时候的话呢，我们就使用索引条件下推啊，在这个回表之前进行一个数据的过滤啊，这就是他的一个作用啊，大家呢，去理解一下我说的这样的一个点。好，那么接着的话呢，我们再来说这个点啊，大家呢，你去执行这个条件的时候呢，你会发现呢，除了叫using index condition之外呢，是不是还有一个呢，叫using where对吧？这个呢，就是因为我们后边这个呢，不在咱们这个索引当中，所以呢它造成的，你要说呢，不想有这个U的话呢，我们只需要呢，是不是这样操作一下。

哎，我们再执行是不是就只有它了，那就不包括我们这样的一个条件了啊这个比较简单啊，好理解，行，那这儿呢，我们就把这个事呢，就说清楚了，那大家也会发现呢，就是我们默认情况呢，是不是就开启了这个叫ICP了，那下一个问题呢，就涉及到我们的ICP呢，如何开启和关闭的一个场景啊呃，如何开启和关闭呢，这块的话呢，我们提到上面这个点。默认情况下呢，我们这个参数呢，它是一个开启的状态，那如果呢，你想关闭的话呢，我们就这样做一个set的操作，把这个参数里边的这个index condition down啊，我们改成是一个off，那它就关闭掉了。哎，这个是打开，我写错了啊，这应该是关闭啊，这个呢是打开。啊，这个一看呢，就知道写错了是吧？好然后这块呢，CTRLC咱们呢也过来做一个演示，你比如说呢，我们现在呢，就把这个操作呢，咱们改成是一个off了。关掉了，关掉以后的话呢，你看我们这时候再去做一个预算啊，咱们执行你会发现呢，这时候呢，是不是就没有索引条件下退了啊，那没有的话呢，其实这个性能呢，它就会降低，OK，然后呢，咱们这块呢，再给它比如说改回去啊，改成是一个on的状态啊走起啊这块你再去选中做一个执行，那这样呢，是不是就有了，好这呢就相当于我们这个叫索引条件下推的一个开启和关闭啊这呢就说完了，好，那我们从理论上呢，说清楚这个ICP之后呢，我们下边从一个实际例子呢来讲一下，说它的性能呢，到底跟开启和关闭呢，有没有呃多大的区别，诶咱们这块呢，就做个演示，诶我们就往这个P这个表里边呢，去批量的添加这个这么多的数据啊，100万条数据，咱们呢，去做一个前后的对比，好线下数据这事呢，我们就通过这个存储过程来做了啊，首先呢，我们这块呢，去这样来操作一下啊。

创建。啊，这呢，我写一下这个信息啊，存储过程啊，这个像。在咱们这个。People are？这个表中我们去添加啊，100万条数据。啊，然后呢，去测试咱们这个ICP这个开启和关闭状态下的。

这个性能OK，好把它呢，我们粘过来选中执行。啊，这都比较简单，然后接下来的话呢，我们是不是就靠进行一个调用就可以了，那我们调用下我们这样的一个。哎，存储过程啊，100万。来选中，那我们把它呢，做一个直行走起。好，这个呢，相对来说花的时间会多一点，那我暂时呢，先把这个视频呢，做做一个暂停。嗯，好了，已经可以了啊，那接下来话呢，我们就来测试一下这个前后的一个区别，来我们打开这个代码，首先的话呢，我们去这个呃，选择这两个操作，这个操作话呢，表示我们就是使用一下这个索引下推是吧，当然使用的话呢，我们想看前后区别的话呢，咱们可以呢，是不是记录一下相应这个语句执行时间，包括它的一些细节，我们前面讲过这个相应的一些工具是不是profile是吧？OK，所以这块我们首先呢，嗯，这个操作的话呢，咱们在在这写吧，啊在这的话呢，我们可能更这个容易的去查看一下啊爱硅谷DB啊，是不是在这啊啊首先我们查看一下啊，这个select count。

星from一下这个people。哎，这呢是我们本身的有的数据，这是我们后来又加的数据，对吧，没问题，首先呢，我们去算一下叫啊Ling。啊，因为默认情况呢，它是关闭的，然后把它呢打开，打开以后的话呢，首先我们呢，去做这样的一个查询，这个查询的话呢，我们会去使用这个索引条件下推，因为刚才呢，我在这块的时候呢，把它是不是又恢复成一个on的状态了，对的是吧，来我们这块粘过来走起。好抄完了这呢是花费的这个时间数，然后的话呢，我把它关闭，关闭的话呢，两种方式啊，其实呢是有两种方式，一种方式呢，就是大家呢，使用刚才我们讲到的，咱们把这个呃参数呢给它改成是一个off的状态啊，那你一会儿呢，要想用还得再还得再打开是吧，那另外一种方式呢，就是我们直接呢，在写这个circle的时候呢，我们就写上了叫no IP。那表示呢，就是我们不使用这个索引条件下，推好CTRLC一下，我们回过来，这是我们粘过来来走起。

啊，其实大家呢，已经感受到我们现在这个时间执行的是不是就挺长的呀，你看差别还是挺大的是吧，好，那是我们跟呢可以呢去show profile。诶查看一下，诶大家你会发现呢，诶刚才我们执行的时候呢，其实这个时间呢，在这块呢，也有一个显示，好，那这两个的差别你看还是非常明显的，主要区别就在于我们使没有没有使用这个叫ICP，当数据量比较大的时候呢，你看这个差别还是很大的。啊，这个高下立判对吧，那你也可以呢，去查看一下这个具体的细节，说会不会因为一些其他原因影响的呀，啊，那你愿意看你就可以看看啊收profile啊，咱们说呢叫哎QUERY1是吧，这是它的一个细节，然后我们在for query2查看一下，你会发现呢，主要这个时间的区别呢，就是在我们这个执行过程当中，跟其他呢就没有关系了。啊，所以说呢，这个执行过程中差别呢，主要就是我们这个ICP使用使用与否的这个问题。好，这呢我们就解释清楚了，然后接下来的话呢，我们看一下这个IP的一个使用条件，首先的话呢，它针对于我们访问这个表的这个类型啊，就是我们这个讲这个type的时候呢，针对这个啊re EQ啊ref和ref no才可以使用这个ICP啊，这个了解一下，然后的话呢，我们这ICP呢，针对于我们这两种存储引擎呢，都是有效的，下边呢，提到说印度D表ICP呢，仅用于二级索引索引，诶这块呢，其实我们想强调就是这个CP啊，它在使用的一个前提呢，就是它一定得回表，在回表之前呢，我们多呢，是不是做了一个比较啊，做了一个条件的判断啊，因为它在这个索引当中出现这个字段了，那如果说本身没有回表这个行为，那我们这呢，是不是就没有意义了？

啊，那什么时候没有汇标行为呢？那一种情况就我们现在用的这个查询呢，是不是用的就是句索引是吧？诶那他就没有汇标了，所以说呢，这个ICP呢，你不能够是用在这个句速索引当中啊，这是一个点，那另外一个场景说什么时候我们是二级索引，但是也没有回表了，是不是我们前面讲到过覆盖索引啊，覆盖缩引就不用回表了，那所以说呢，我们如果你要是用了覆盖索引了，那我们ICP呢，就也用不上了。

啊，就不支持了，那这里有点像这个Java当中，比如说我们这个final关键字和这个abstra这个关键字呢，他俩都不能同时来修饰一个类一样，对吧，有点水火不容这样一个意思。好，下一点的话呢，提到叫相关子查询的这个条件呢，不能使用IP啊，因为涉及到这个里外条件的一个相关性的问题啊，这个大家注意一下就行，好，那么关于这个索引条件下推啊，咱们就说到这儿。