这是我参与「第五届青训营」伴学笔记创作活动的第 7 天

索引失效

最左匹配

我原本以为自己弄清楚了，结果还是有点小问题

请看下面SQL语句进行思考是否会走索引

-- 联合索引 sname,s_code,address

1、select create_time from student where sname = "变成派大星"  -- 会走索引吗？

2、select create_time from student where s_code = 1   -- 会走索引吗？

3、select create_time from student where address = "上海"  -- 会走索引吗？

4、select create_time from student where address = "上海" and s_code = 1 -- 会走索引吗？

5、select create_time from student where address = "上海" and sname = "变成派大星"  -- 会走索引吗？

6、select create_time from student where sname = "变成派大星" and address = "上海"  -- 会走索引吗？

7、select create_time from student where sname = "变成派大星" and s_code = 1 and address = "上海"  -- 会走索引吗？

实际上，不只是 1、7 会走索引，5、6 也会走。最左匹配最重要的就是最左边的那一个！

具体原因都知道，由于 B+ 树对数据排序是先按索引第一个排，第一个相等排第二个，以此类推……

所以一旦最左边第一个属性未指定，数据库就不知道你要匹配什么索引，只能走一遍全表检查

而只要第一个有了，就能找到对应的索引，自然就不会索引失效了

前提如果创建 b,c,d 联合索引面

如果我where 后面的条件是c = 1 and d = 1为什么不能走索引呢如果没有b的话你查询的值相当于 *11 我们都知道``是所有的意思也就是我能匹配到所有的数据

如果我 where 后面是 b = 1 and d =1 为什么会走索引呢？你等于查询的数据是 1*1 我可以通过前面 1 进行索引匹配所以就可以走索引

最左缀匹配原则的最重要的就是第一个字段

索引跳跃扫描

当然，这个原则随着数据库的不断优化，也不再绝对了：

MySQL8.0版本开始增加了索引跳跃扫描的功能，当第一列索引的唯一值较少时，即使where条件没有第一列索引，查询的时候也可以用到联合索引。比如我们使用的联合索引是 bcd 但是b中字段比较少我们在使用联合索引的时候没有使用 b 但是依然可以使用联合索引 MySQL联合索引有时候遵循最左前缀匹配原则，有时候不遵循。

使用函数

就一句话，你索引中存的是原始数据，加个函数人家可就不认账了

比如你来一句

SELECT name from person where length(name) = 3

这样就破坏了原始数据

当然，最终解释权还在索引那里，假如你使用一个叫做 函数索引 的东西，那么他就会对函数计算后的值建立索引，也就可以使用函数来作为条件走索引了

计算操作

加入我这样查询：

SELECT name from person where age - 4 = 18

那么很遗憾，压根儿不行，因为这样就改变了查询的对象，如果改成：

SELECT name from person where age = 18 + 4

就完全 OK，可以走索引。诚然 MySQL 可以做相关优化实现两种都走索引，但他显然偷了个懒

Like %

%百分号通配符: 表示任何字符出现任意次数(可以是0次).
_下划线通配符: 表示只能匹配单个字符,不能多也不能少,就是一个字符.
like操作符: LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较.

注意: 如果在使用like操作符时,后面的没有使用通用匹配符效果是和=一致的,

SELECT * FROM products WHERE products.prod_name like '1000';

2.匹配包含"Li"的记录(包括记录"Li") :

SELECT* FROM products WHERE products.prod_name like '%Li%';

3.匹配以"Li"结尾的记录(包括记录"Li",不包括记录"Li ",也就是Li后面有空格的记录,这里需要注意)

SELECT * FROM products WHERE products.prod_name like '%Li';

我们可以看到 % 在哪边就模糊匹配那一边，所以如果模糊匹配左边（%target），就不太可能走索引了。模糊匹配右边，左边还有确定的值，这时候会走索引，但是范围仍旧很大，索引级别低

索引的时候和查询范围关系也很大范围过大造成索引没有意义从而失效的情况也不少

or

这个好解释吧，任意一边非索引（匹配不上索引）都会出问题

in/not in

当然前提是你寻找的字段在索引里啊！

其次，虽然 in 看起来一定会走索引，但是之前咱也说过了：索引和查询范围关系也很大，假如你的范围定的太广，就自动走全表了。

in 在结果集大于30%的时候索引失效

order by

这一个主要是Mysql 自身优化的问题我们都知道OrderBy 是排序那就代表我需要对数据进行排序如果我走索引索引是排好序的但是我需要回表消耗时间另一种我直接全表扫描排序不用回表也就是

走索引 + 回表
不走索引直接全表扫描

Mysql 认为直接全表扫面的速度比回表的速度快所以就直接走索引了在Order By 的情况下走全表扫描反而是更好的选择

回表

就是在索引树上，叶子节点只存了索引对应的主键，要想查出对应数据还得重新返回主树再次查询，这个过程就是回表

ICP 索引下推

这个是在MySQL 5.6 之后提供的特性，也就是一种优化

如果你在 5.6 之前查询时需要回表，就会找到多少个回表多少次，把回表得到的数据拿出来后再进行筛选

而 5.6 之后，其它索引树叶子节点中还存放了索引字段的数据，这也使得索引字段相关的筛选可以提前进行，最后把筛选出来的数据进行同意回表，只需要仅仅一次回表

但是记住一定要是联合索引，普通索引我记得应该是没有索引下推的

MySQL 索引失效 | 青训营笔记