java八股文面试[数据库]——索引哪些情况下会失效

淩亂°似流年 2024-03-26 23:18 73阅读 0赞

1.  查询条件包含 **or**，会导致索引失效。
2.  隐式**类型转换**，会导致索引失效，例如 id字段类型是**varchar**，我们 where id = 1，这样就会触发隐式类型转换
3.  **like** 通配符会导致索引失效，注意:”ABC%” 不会失效，会走 range 索引，”% ABC” 索引会失效
4.  **联合索引**，查询时的条件列不是联合索引中的**第一个列**，索引失效。
5.  对索引字段进行**函数运算**。
6.  对索引列**运算**（如，+、-、\*、/），索引失效。
7.  索引字段上使用（!= 或者 < >，not in）时，会导致索引失效。
8.  索引字段上使用 **is null**， is not null，not exist **可能导致**索引失效。
9.  相 join 的两个表的**字符编码**不同，不能命中索引，会导致笛卡尔积的循环计算
10. mysql 估计使用**全表扫描要比使用索引快**，则不使用索引。

知识速记：or like null 不等于 联合最左 类型 函数 运算 编码 全表

###### 类型转换 ######

**MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较**。

select * from t_user where phone = 1300000001;

这是因为 phone 字段为字符串，所以 MySQL 要会自动把字符串转为数字，所以这条语句相当于：

select * from t_user where CAST(phone AS signed int) = 1300000001;

可以看到，**CAST 函数是作用在了 phone 字段，而 phone 字段是索引，也就是对索引使用了函数！而前面我们也说了，对索引使用函数是会导致索引失效的**。

例子二中的查询语句，我跟大家说了是会走索引扫描：

//例子二的查询语句
    select * from t_user where id = "1";

这时因为字符串部分是输入参数，也就需要将字符串转为数字，所以这条语句相当于：

select * from t_user where id = CAST("1" AS signed int);

可以看到，索引字段并没有用任何函数，CAST 函数是用在了输入参数，因此是**可以走索引扫描的**。

###### **联合索引使用了select \*** ######

KEY \`union\_idx\` (\`id\_no\`,\`username\`,\`age\`)

比如，在上面的联合索引中，如果查询条件是age或username，当使用了`select *` ，肯定是不会走索引的。

但如果希望根据username查询出id\_no、username、age这三个结果（均为索引字段），明确查询结果字段，是可以走**`覆盖索引`**的：

explain select id_no, username, age from t_user where username = 'Tom2';
    explain select id_no, username, age from t_user where age = 12;

explain结果：

![2f0b4a6ff3f21f937d3dec2ff28adf78.png][]

无论查询条件是`username`还是`age`，都走了索引，根据key\_len可以看出使用了索引的所有列。

第二种索引失效场景：**在联合索引下，尽量使用明确的查询列来趋向于走覆盖索引**；

###### 如果**or两边同时使用“>”和“<”**，则索引也会失效： ######

explain select * from t_user where id  > 1 or id  < 80;

explain结果：

![eaefbf8b3a23d2af53ff55b015076929.png][]

查询条件使用or关键字，**其中一个字段没有创建索引**，则会导致整个查询语句索引失效；

or两边为“>”和“<”范围查询时，索引失效。

###### **两列做比较** ######

如果两个列数据**都有索引**，但在查询条件中对两列数据进行了对比操作，则会导致索引失效。

这里举个不恰当的示例，比如age小于id这样的两列（真实场景可能是两列同维度的数据比较，这里迁就现有表结构）：

explain select * from t_user where id > age;

explain结果：

![format_png][]

这里虽然id有索引，age也可以创建索引，但当两列做比较时，索引还是会失效的。

**两列数据做比较，即便两列都创建了索引，索引也会失效**。

###### **不等于比较** ######

示例：

explain select * from t_user where id_no <> '1002';

explain结果：

![e729bfd88a7eaced6bee91ab29bf3233.png][]

当查询条件为字符串时，使用”<>“或”!=“作为条件查询，有可能不走索引，**但也不全是**。

explain select * from t_user where create_time != '2022-02-27 09:56:42';

上述SQL中，由于“2022-02-27 09:56:42”是存储过程在同一秒生成的，大量数据是这个时间。执行之后会发现，当查询结果集占比比较小时，会走索引，占比比较大时不会走索引。此处与结果集与总体的占比有关。

需要注意的是：上述语句如果是**`id`进行不等操作**，则正常走索引。

explain select * from t_user where id != 2;

explain结果：

![03c8e7da77ec240ff8d4a93e76cd832b.png][]

**查询条件使用不等进行比较时，需要慎重，普通索引会查询结果集占比较大时索引会失效**。

###### **is not null** ######

示例：

explain select * from t_user where id_no is not null;

explain结果：

![fe5f4eda3de6796e687cd2400f3bd47a.png][]

查询条件使用**is null时正常走索引**，使用is not null时，不走索引。

###### **not in和not exists** ######

在日常中使用比较多的范围查询有in、exists、not in、not exists、between and等。

explain select * from t_user where id in (2,3);
    
    explain select * from t_user where id_no in ('1001','1002');
    
    explain select * from t_user u1 where exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);
    
    explain select * from t_user where id_no between '1002' and '1003';

上述四种语句执行时都会正常走索引，具体的explain结果就不再展示。主要看不走索引的情况：

explain select * from t_user where id_no not in('1002' , '1003');

explain结果：

![b149ea9188a73b35edcf207c64c4c95f.png][]

当使用`not in`时，不走索引？把条件列换成主键试试：

explain select * from t_user where id not in (2,3);

explain结果：

![ffabc5dddfffcc141722cdc5f495d46b.png][]

如果是主键，则正常走索引。

**查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效**。

再来看看**`not exists`**：

explain select * from t_user u1 where not exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);

explain结果：

![d5c7df4e2d986eeb41c1f6daf2aca622.png][]

当查询条件使用**`not exists`时**，不走索引。

**查询条件使用not exists时，索引失效**。

###### **order by导致索引失效** ######

示例：

explain select * from t_user order by id_no ;

explain结果：

![495b2423bc649b788b3ba814f3cb276b.png][]

其实这种情况的索引失效很容易理解，毕竟需要对**全表数据进行排序处理**。

那么，添加删limit关键字是否就走索引了呢？

explain select * from t_user order by id_no limit 10;

explain结果：

![7cbcdf93f939136e147b6035bb00d43d.png][]

结果依旧不走索引。在网络上看到有说如果`order by`条件满足最左匹配则会正常走索引， 在当前8.0.18版本中并未出现。所以，在基于`order by`和`limit`进行使用时，要特别留意。是否走索引不仅涉及到**数据库版本**，还要看Mysql**优化器**是如何处理的。

这里还有一个特例，就是**主键使用`order by`时，可以正常走索引**。

explain select * from t_user order by id desc;

explain结果：

![d723b02d070b43e0ffc7a996afa802be.png][]

可以看出针对主键，还是`order by`可以正常走索引。

另外，笔者测试如下SQL语句：

explain select id from t_user order by age;
    explain select id , username from t_user order by age;
    explain select id_no from t_user order by id_no;

上述三条SQL语句都是走索引的，也就是说**覆盖索引的场景**也是可以正常走索引的。

现在将`id`和`id_no`组合起来进行`order by`：

explain select * from t_user order by id,id_no desc;
    explain select * from t_user order by id,id_no desc limit 10;
    explain select * from t_user order by id_no desc,username desc;

explain结果：

![77fdf7fbede600dc34c0b87c4ac6ac2d.png][]

上述两个SQL语句，都未走索引。

当查询条件涉及到**order by、limit等条件时**，是否走索引情况比较复杂，而且与**Mysql版本**有关，通常普通索引，如果**未使用limit，则不会走索引**。order by**多个索引字段时**，可能不会走索引。其他情况，建议在使用时进行expain验证。

知识来源：马士兵教育

[盘一盘常见的6种索引失效情况 - 知乎][6_ -]

[15个必知的Mysql索引失效场景，别再踩坑了！ - 知乎][15_Mysql_ -]

[2f0b4a6ff3f21f937d3dec2ff28adf78.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/11/463e180b44eb429a8a832952837018c7.png
[eaefbf8b3a23d2af53ff55b015076929.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/11/e0009b1c8ac946358e5968f6f09dc2a9.png
[format_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/11/e6d2c803e6bf499986a4086dcb9a1742.png
[e729bfd88a7eaced6bee91ab29bf3233.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/11/a756100d9d704623830c055e97a0ef97.png
[03c8e7da77ec240ff8d4a93e76cd832b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/11/c4ac8893009842a0a09aad7a89d1058e.png
[fe5f4eda3de6796e687cd2400f3bd47a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/11/29065a17abc54459a50b3ae482090f64.png
[b149ea9188a73b35edcf207c64c4c95f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/11/7523e3c3b15b45a386f9643bd9e24fff.png
[ffabc5dddfffcc141722cdc5f495d46b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/11/58a2bdce64a243bf8e40e11513962f87.png
[d5c7df4e2d986eeb41c1f6daf2aca622.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/11/873ad5fee64d44b08741e442e3001e43.png
[495b2423bc649b788b3ba814f3cb276b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/11/82a5d251be8b4964bb36374693f2aa22.png
[7cbcdf93f939136e147b6035bb00d43d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/11/e3ebb7c7d18548edabd7d1502a5e4f70.png
[d723b02d070b43e0ffc7a996afa802be.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/11/ed7c3fb51f6140a097a7dab3b217769c.png
[77fdf7fbede600dc34c0b87c4ac6ac2d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/11/7ca5af410e294194af976838adaba190.png
[6_ -]: https://zhuanlan.zhihu.com/p/471209432?utm_id=0
[15_Mysql_ -]: https://zhuanlan.zhihu.com/p/473413370