题目描述

字节面试中，多次问到这个问题，例如：

Mysql的like语句会影响索引吗？还有哪些情况无法使用索引？联合索引，a=1 and b=1、a=1 or b=1是否会触发索引？相关背景

1、MyISAM和InnoDB都使用B+树作为索引数据结构。

MyISAM：主索引和辅助索引一样，叶子节点是数据在磁盘的物理地址，区别只是主索引要求key唯一。InnoDB：主索引是聚集索引，叶子节点就是具体的数据行。辅助索引叶子节点存储主键。

2、普通索引如何进行范围查询？

根据索引，找到叶子节点；然后利用叶子节点间的指针，顺序地拿到区间内的主键，回表查询数据。

3、联合索引的结构？

我们通常会建立某些字段的辅助索引，称为“联合索引”。联合索引的索引key是全部的字段，会先根据第一个索引项排序，第一个索引项相同，才去看第二个索引项，以此类推。

like会影响索引吗？

如果模糊查询符号%在最前面，会使索引失效；如果在最后面，不会使索引失效。

可以使用覆盖索引解决模糊查询符号%在前面的情况。

哪些情况会使索引失效

1、联合索引没有最左侧的条件，失效。

因为联合索引，是根据建立时的字段顺序去依次索引的，如果第一个字段都没有，没法往下查询索引的B+树。也就是常说的“最左索引匹配”。同样的，如果中间有“跳跃”，也是不行的，必须顺序。

例如联合索引：（name、age、address）

name=“元面试” AND age=20 AND address=“中国”：最佳查询，用到3个字段address=“中国” AND name=“小明” AND age=20：最佳查询，用到3个字段。mysql优化器会自动优化成上面的顺序，不用担心人写的顺序。name=“元面试” AND age=20：也可以，用到2个字段name=“元面试” AND address=“中国”：也可以，但是存在跳跃，只能用1个字段，也就是nameage=20 AND address=“中国”：索引失效，因为没有第1个字段name

2、在索引列上做一些函数、类型转换、表达式计算等操作，失效。

很容易理解，判断的值变为了“转换后的值”，并不一定按照索引顺序排列，那么索引就失去意义了。

例如单值索引：name、age

LEFT(name, 1) = “元”：失效，使用了字符串函数LEFTage/2=10：失效，表达式计算。注意如果是age=10*2，是OK的。name= 1：失效，name是字符串，使用数字，默认隐含将int转为varchar的类型转换

3、不等号（!=或<>）、NOT IN、NOT EXISTS、IS NULL/IS NOT NULL，开销大时失效

这种关键字，通常会涵盖大量数据，开销太大时，会直接全表扫描。

例如联合索引：（name、age、address）或者单值索引（name）

name != “元面试”

4、LIKE通配符开头会失效，但末尾不会失效。

如果是“%xxx”会失效，如果是“xxx%”则不会失效。如果一定要使用通配符开头的，可以利用覆盖索引来避免索引失效。

覆盖索引（Covering index）：想要查询的字段，已经全部包含于索引key中了，这样就不用回到主键索引查数据了。

例如联合索引：（name、age、address）或者单值索引（name）

SELECT * FROM xxx WHERE name LIKE “%元面试”：失效SELECT * FROM xxx WHERE name LIKE “元面试%”：可以索引SELECT * FROM xxx WHERE name LIKE “%元面试%”：失效SELECT name, age, address FROM xxx WHERE name LIKE “%元面试%”：覆盖索引

5、OR条件导致失效

只要有1个条件没有索引，其他条件即使有索引，也失效。因为从成本上来看，没有索引的字段一定要进行1次全表扫描，加上其他字段的开销，那还不如直接1次全表扫描搞定。

例如单值索引（name）、联合索引（name、age、address）

name=“元面试” OR age=20：age没有独立的索引，失效

6、范围查询右侧的条件，索引失效。

只有前一个条件是等值条件，后面的字段值才是顺序排列的，才能用索引。

例如联合索引：（name、age、address）

name=“元面试” AND age>20 AND address=“中国”：name、age用到了索引，但是address失效。

7、ORDER BY 不满足最左匹配、排序顺序不同，索引失效。

（1）不满足最左匹配（或者没有索引的字段、WHERE是范围条件等失效情况），失效

正常情况下，联合索引的字段，只要前面确定了，后面都是顺序的。如果有跳跃，顺序就没法确定了，就得用文件排序filesort，索引失效。

例如联合索引：（name、age、address）

name = “元面试” ORDER BY age：都用了索引，name确定后，age是顺序排序的。name = “元面试” ORDER BY address：name用了索引，但是address是第3个，跳过了age，无法使用索引。age = 20 ORDER BY name：相当于没有索引，失效。

（2）排序顺序不同，失效

索引默认是升序，所以如果顺序相同，都升序或都降序，只需要从最小数据开始，或最大数据开始，顺序、逆序读出一定数量的数据即可；如果两个字段排序顺序不同，没法使用索引，会全表扫描。

例如联合索引：（name、age、address）

ORDER BY name DESC, age DESC：正常索引ORDER BY name ASC, age DESC：失效，排序顺序不同

8、GROUP BY 不满足最左匹配，索引失效

同样的，GROUP BY 也是满足最左匹配就行。如果不满足，会产生临时表，索引失效。

例如联合索引：（name、age、address）

name=“元面试” GROUP BY age：正常索引name=“元面试” GROUP BY address：失效，存在跳跃。name=“元面试” GROUP BY avatar：失效，没有索引的列。结束语

能看到这里的你，一定是个热爱编程的同学。如果想要有计划地准备大厂面试，欢迎关注。

关注我，带你像准备高考一样有计划地准备大厂面试！（当前：2022年第6周）

周一：新闻动态——了解岗位要求、薪资，找到目标

周二：编程刷题——高频算法面试题

周三：专业真题——高频连环炮提问

周四：面试提问——HR面的问题如何回答

周五：热门推荐——高效工具