索引失效的10个场景

1. 不满足最左匹配原则

2. 使用了select *

3. 索引列上有计算

4. 索引列用了函数

5. 字段类型不同

6. like 左边包含 %

7. 列对比

8. 使用or 关键字

9. not in 和 not exists

10. order by 的坑

建表

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT '0',
  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `height` int DEFAULT '0',
  `address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code_age_name` (`code`,`age`,`name`),
  KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

查看执行计划

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

 explain select * from user where id=1;

由于id字段是主键，该sql语句用到了主键索引。

 

1.不满足最左匹配原则：

如果在使用联合索引时，没注意最左前缀原则，很有可能导致索引失效

根据上面的建表语句，我们使用索引进行查询时必须按照code ,age, name 这几个字段的顺序进行查询，如果直接从age进行则索引失效

可以使用code,name; code, age 这两个，但如果没包含code，直接从age或者name开始则索引失效

因为查询条件中，没有包含给定字段最左边的索引字段，即字段code。

 

2.使用了select * :

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

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

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

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

 

3.索引列上有计算:

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

有计算，索引将失效

 

4.索引列用了函数:

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

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

索引失效

 

5.字段类型不同:

表中的code字段，它是varchar字符类型的。

 explain select * from user where code=101;

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

 

int类型的参数，不管在查询时加没加引号，都能走索引。

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

 

 select 1 + '1';

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

但如果你确实想拼接字符串该怎么办？ 可以使用concat关键字。

 select concat(1,'1');

 

6. like左边包含%：

 

当like语句中的%，出现在查询条件的左边时，索引会失效。

 -- 索引都会失效
 like '%1%'; 
 like '%k';
 -- 索引不失效
 like 'j%';

索引就像字典中的目录。一般目录是按字母或者拼音从小到大，从左到右排序，是有顺序的。

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

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

但如果你硬要跟目录的设计反着来，先从字典目录右边匹配第一个字母，困难程度增加

 

7. 列对比：

 select * from user where id=height

索引失效

id字段本身是有主键索引的，同时height字段也建了普通索引的，并且两个字段都是int类型，类型是一样的。

但如果把两个单独建了索引的列，用来做列对比时索引会失效。

 

8. 使用or关键字：

 select * from user where id=1 or height='175' or address='成都';

id是主键索引，height是普通索引，address没有建立索引。

address字段没有加索引，从而导致其他字段的索引都失效了。

注意：如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效，这是一个大坑。

 

9. not in和not exists：

 

sql语句中用in关键字是走了索引的。

 

用exists关键字同样走了索引。

 

主键字段中使用not in关键字查询数据范围，任然可以走索引。而普通索引字段使用了not in关键字查询数据范围，索引会失效。

 

使用not exists关键后，t1表走了全表扫描，并没有走索引。

 

10. order by的坑

但我始终觉得order by挺难用的，它跟where或者limit关键字有很多千丝万缕的联系，一不小心就会出问题。

order by后面的条件，也要遵循联合索引的最左匹配原则。

除了遵循最左匹配原则之外，有个非常关键的地方是，后面还是加了limit关键字，如果不加它索引会失效。

order by还能配合where一起遵循最左匹配原则。

虽说name是联合索引的第三个字段，但根据最左匹配原则，该sql语句依然能走索引，因为最左边的第一个字段code，在where中使用了。只不过order by的时候，排序效率比较低，需要走一次filesort排序罢了。

order by后面如果包含了联合索引的多个排序字段，只要它们的排序规律是相同的（要么同时升序，要么同时降序），也可以走索引。

code字段在where和order by中都有，对于这种情况，还是能走了索引的。

 

哪些情况不走索引？

如果order by语句中没有加where或limit关键字，该sql语句将不会走索引。

如果对多个索引进行order by，索引也失效了。

不满足最左匹配原则的情况：如果order by不满足最左匹配原则，确实不会走索引。

尽管order by后面的code和age字段遵循了最左匹配原则，但由于一个字段是用的升序，另一个字段用的降序，最终会导致索引失效。