不会like优化欠下的一顿海底捞

hello大家好 我是大家的学习成长小伙伴Captain

昨天呢，下班之后在和表妹一起吃饭的时候，表妹突然脑子抽抽的问了我一句：表哥，今天我们leader要求我优化我写的like语句，我差点就反手一个大嘴巴子了，这玩意怎么优化，真是的

此时的我一脸无奈的看着表妹，默默地摇了摇头

表妹看我状态之后觉得事情并不是那么简单，于是质问我怎么回事，我不太想告诉我，于是最终以一顿海底捞的报酬成交了

给表妹分享了之后，顺便啊我也把这个知识点整理了出来，给大家分享一波也

联合索引

也就是复合索引，我们平时可能使用的最多的是单独索引，也就是一列作为索引，因为很多种情况下一列的重复度就足够高了

那么什么时候使用联合索引呢，也就是使用场景

当需要用到多个字段进行索引的时候，但是每个索引的区分度不是非常高，这时候使用联合索引的效率就会变得很高了

如果单个字段索引就很高了，重复率就很低，那就没必要使用联合索引了，只需要把那个区分度高的字段拎出来作为索引就行了

你想啊，比如要找一个学校的学生，学号或者是姓名的区分度应该都很高，重复率都比较低，所以只需要根据这些作为索引就行了，使用姓名或者学号就可以直接找到相应的学生了

但是如果我们要统计全省的学生，如果多个学校之间的姓名重复度比较高，那只使用姓名就是一个糟糕的事情了，你想啊，如果按照姓名='张三'来搜索，搜索出几十个甚至几百个张三来，那剩下的还得根据别的字段才能区分出真正想要的那个张三

此时，联合索引便派上用场了

我们可以让学校编号+姓名作为联合索引来使用，这样通过这两个就可以使区分度变得很高咯，一个学校的叫张三的可能就只有一两个

只通过学校找可能需要找出很多学生，然后再通过张三来遍历。或者直接通过张三来找到几百个张三，然后再根据别的特性找到我们要找的张三

有人可能会问，可以对这两个字段分别建立索引啊，当然也是可以的，但是这样的效率远不如联合索引，而且也会比较浪费空间，两个索引就需要简历两个B+树，所以是比较浪费空间的

优先级

在联合索引中，有优先级这一说，也就是建立联合索引的顺序是很关键的，并不是说随便的顺序

一句话，区分度高的需要放到联合索引的前面

其实就是前面的区分度越高，后面的符合数据的也就越少了，这样后面就只需要判断更少的数据，就可以得到结果了

结构

联合索引的结构同样也是B+树，等同于是B+树排序的时候会按照联合索引的优先级来排序，联合索引学校+姓名，也就是先按照学校进行排序，学校相同的才会对姓名进行第二次的排序

联合索引只需要构建一颗B+树，使用多个单独索引就需要构建多个B+树，B+树的叶子节点存储的都是主键

最左匹配

这个也是属于联合索引的一大特点，也就是使用联合索引必须符合最左匹配原则，这个玩意是个什么呢，给大家解释下

上面的联合索引学校+姓名，我们一般使用

select * from student where school = '育才' and name = '张三'

使用上面这种是可以使用联合索引的，因为符合最左匹配原则了，也就是我们使用SQL查询的时候必须按照这个顺序使用才可以

如果我们把SQL语句换成

select * from student where name = '张三'

这种情况下，是不会使用索引的

因为我们上面使用的是先按照学校作为第一比较顺序构建的B+树，我们直接使用name是无法根据联合索引构建的B+树进行查询的

所以这就导致了无法使用索引

举个形象的例子，我们把索引理解成字典中的拼音目录，如果我们想要查找其中的"中"字，我们可以根据首字母是z来进行查询

但是如果我们不知道首字母z，只知道尾部是ong，那这就没法走索引了啊，这种拼音目录就没法起作用了，因为带有ong的分布在整本字典的各个角落

第一页可能就有，最后一页可能也有

索引下推ICP

mysql5.6开始支持ICP，index condition pushdown，不支持ICP之前，当进行索引查询的时候，首先根据索引来查询大量的数据，然后根据剩余的where条件来进行过滤，这样扫描了大量的没必要的数据，造成了资源的浪费 

默认参数

index_condition_pushdown：索引条件下推默认开启，设置为off关闭ICP特性。

在学校+姓名作为复合索引之后idx_school_name(school,name)，我们看SQL语句

select * from student where school = '育才' and name like '%张三%'

在ICP关闭的情况下，上面的SQL语句会首先按照走复合索引中的school，查出复合条件的数据，然后在server端再过滤掉所有不符合name like '%张三%'的数据，剩下复合条件的数据

这样的话，其实造成了在server端大量的无效的数据扫描

开启索引下推ICP之后，上面的SQL走了第一部分的SQL索引之后，不会直接将数据全部读取到server端，而是直接根据联合索引来执行第二部分的判断，最后只会读取这个联合索引，然后第二个条件直接判断即可，不需要将复合school条件的数据全部读取出来之后在比较

只需要在server端筛选出想要的记录即可，极大的减少了不必要的IO的开销