• 明明在某个字段上加了索引，但实际上并没有生效。

• 索引有时候生效了，有时候没有生效。

为了方便给大家做演示，我特意向user表中插入了3条数据：

INSERT INTO sue.user (id, code, age, name, height) VALUES (1, '101', 21, '周星驰', 175,'香港');INSERT INTO sue.user (id, code, age, name, height) VALUES (2, '102', 18, '周杰伦', 173,'台湾');INSERT INTO sue.user (id, code, age, name, height) VALUES (3, '103', 23, '苏三', 174,'成都');

周星驰和周杰伦是我偶像，在这里自恋了一次，把他们和我放到一起了。哈哈哈。

为了防止以后出现不必要的误会，在这里有必要查一下当前数据库的版本。不说版本就直接给结论，是耍流氓，哈哈哈。

select version();

查出当前的MySQL版本号为：8.0.21

在MySQL中，如果你想查看某条SQL语句是否使用了索引，或者已建好的索引是否失效，可以通过explain关键字，查看该SQL语句的执行计划，来判断索引使用情况。

例如：

explain select * from user where id=1;

执行结果：

从图中可以看出，由于id字段是主键，该SQL语句用到了主键索引。

当然，如果你想更深入的了解explain关键字的用法，可以看看我的另一篇文章《explain | 索引优化的这把绝世好剑，你真的会用吗？》，里面更为详细的介绍。

之前我已经给code、age和name这3个字段建好联合索引：idx_code_age_name。

该索引字段的顺序是：

如果在使用联合索引时，没注意最左前缀原则，很有可能导致索引失效喔，不信我们一起往下看。

先看看哪些情况下，能走索引。

explain select * from userwhere code='101';

explain select * from userwhere code='101' and age=21 

explain select * from userwhere code='101' and age=21 and name='周星驰';

执行结果：

上面三种情况，SQL都能正常走索引。

其实还有一种比较特殊的场景：

explain select * from userwhere code = '101'  and name='周星驰';

执行结果：

查询条件原本的顺序是：code、age、name，但这里只有code和name中间断层了，掉了age字段，这种情况也能走code字段上的索引。

看到这里，不知道聪明的你，有没有发现这样一个规律：这4条SQL中都有code字段，它是索引字段中的第一个字段，也就是最左边的字段。只要有这个字段在，该SQL已经就能走索引。

这就是我们所说的最左匹配原则。

前面我已经介绍过，建立了联合索引后，在查询条件中有哪些情况索引是有效的。

接下来，我们重点看看哪些情况下索引会失效。

explain select * from userwhere age=21;

explain select * from userwhere name='周星驰';

explain select * from userwhere age=21 and name='周星驰';

执行结果：

从图中看出这3种情况下索引确实失效了。

说明以上3种情况不满足最左匹配原则，说白了是因为查询条件中，没有包含给定字段最左边的索引字段，即字段code。

在《阿里巴巴开发手册》中明确说过，查询SQL中禁止使用select * 。

那么，你知道为什么吗？

废话不多说，按照国际惯例先上一条SQL：

explain select * from user where name='苏三';

执行结果：

在该SQL中用了select *，从执行结果看，走了全表扫描，没有用到任何索引，查询效率是非常低的。

如果查询的时候，只查我们真正需要的列，而不查所有列，结果会怎么样？

非常快速的将上面的SQL改成只查了code和name列，太easy了：

explain select code,name from user where name='苏三';

执行结果：

从图中执行结果不难看出，该SQL语句这次走了全索引扫描，比全表扫描效率更高。

其实这里用到了：覆盖索引。

如果select语句中的查询列，都是索引列，那么这些列被称为覆盖索引。这种情况下，查询的相关字段都能走索引，索引查询效率相对来说更高一些。

而使用select *查询所有列的数据，大概率会查询非索引列的数据，非索引列不会走索引，查询效率非常低。

介绍本章节内容前，先跟大家一起回顾一下，根据id查询数据的SQL语句：

explain select * from user where id=1;

执行结果：

从图中可以看出，由于id字段是主键，该SQL语句用到了主键索引。

但如果id列上面有计算，比如：

explain select * from user where id+1=2;

执行结果：

从上图中的执行结果，能够非常清楚的看出，该id字段的主键索引，在有计算的情况下失效了。

有时候我们在某条SQL语句的查询条件中，需要使用函数，比如：截取某个字段的长度。

假如现在有个需求：想查出所有身高是17开头的人，如果SQL句写成这样：

explain select * from user  where height=17;

该SQL语句确实用到了普通索引：

但该SQL语句肯定是有问题的，因为它只能查出身高正好等于17的，但对于174这种情况，它没办法查出来。

为了满足上面的要求，我们需要把SQL语句稍稍改造了一下：

explain select * from user  where SUBSTR(height,1,2)=17;

这时需要用到SUBSTR函数，用它截取了height字段的前面两位字符，从第一个字符开始。

执行结果：

你有没有发现，在使用该函数之后，该SQL语句竟然走了全表扫描，索引失效了。

在SQL语句中因为字段类型不同，而导致索引失效的问题，很容易遇到，可能是我们日常工作中最容易忽略的问题。

到底怎么回事呢？

请大家注意观察一下t_user表中的code字段，它是varchar字符类型的。

在sql语句中查询数据时，查询条件我们可以写成这样：

explain select * from user where code="101";

执行结果：

从上图中看到，该code字段走了索引。

但如果你在写SQL时，不小心把引号弄掉了，把SQL语句变成了：

explain select * from user where code=101;

执行结果：

你会惊奇的发现，该SQL语句竟然变成了全表扫描。因为少写了引号，这种小小的失误，竟然让code字段上的索引失效了。

这时你心里可能有一万个为什么，其中有一个肯定是：为什么索引会失效呢？

答：因为code字段的类型是varchar，而传参的类型是int，两种类型不同。

此外，还有一个有趣的现象，如果int类型的height字段，在查询时加了引号条件，却还可以走索引：

explain select * from user where height='175';

执行结果：

从图中看出该SQL语句确实走了索引。int类型的参数，不管在查询时加没加引号，都能走索引。

这是变魔术吗？这不科学呀。

答：MySQL发现如果是int类型字段作为查询条件时，它会自动将该字段的传参进行隐式转换，把字符串转换成int类型。

MySQL会把上面列子中的字符串175，转换成数字175，所以仍然能走索引。

接下来，看一个更有趣的SQL语句：

select 1 + '1';

它的执行结果是2，还是11呢？

好吧，不卖关子了，直接公布答案执行结果是2。

MySQL自动把字符串1，转换成了int类型的1，然后变成了：1+1=2。

但如果你确实想拼接字符串该怎么办？

答：可以使用concat关键字。

具体拼接SQL如下：

select concat(1,'1');

接下来，关键问题来了：为什么字符串类型的字段，传入了int类型的参数时索引会失效呢？

答：根据MySQL官网上解释，字符串'1'、' 1 '、'1a'都能转换成int类型的1，也就是说可能会出现多个字符串，对应一个int类型参数的情况。那么，MySQL怎么知道该把int类型的1转换成哪种字符串，用哪个索引快速查值?

感兴趣的小伙伴可以再看看官方文档：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

模糊查询，在我们日常的工作中，使用频率还是比较高的。

比如现在有个需求：想查询姓李的同学有哪些?

使用like语句可以很快的实现：

select * from user where name like '李%';

但如果like用的不好，就可能会出现性能问题，因为有时候它的索引会失效。

不信，我们一起往下看。

目前like查询主要有三种情况：

假如现在有个需求：想查出所有code是10开头的用户。

这个需求太简单了吧，SQL语句如下：

explain select * from userwhere code like '10%';

执行结果：

图中看出这种%在10右边时走了索引。

而如果把需求改了：想出现出所有code是1结尾的用户。

查询SQL语句改为：

explain select * from userwhere code like '%1';

执行结果：

从图中看出这种%在1左边时，code字段上索引失效了，该SQL变成了全表扫描。

此外，如果出现以下SQL：

explain select * from userwhere code like '%1%';

该SQL语句的索引也会失效。

下面用一句话总结一下规律：当like语句中的%，出现在查询条件的左边时，索引会失效。

那么，为什么会出现这种现象呢？

答：其实很好理解，索引就像字典中的目录。一般目录是按字母或者拼音从小到大，从左到右排序，是有顺序的。

我们在查目录时，通常会先从左边第一个字母进行匹对，如果相同，再匹对左边第二个字母，如果再相同匹对其他的字母，以此类推。

通过这种方式我们能快速锁定一个具体的目录，或者缩小目录的范围。

但如果你硬要跟目录的设计反着来，先从字典目录右边匹配第一个字母，这画面你可以自行脑补一下，你眼中可能只剩下绝望了，哈哈。