MySQL面试：哪些情况下数据库索引会失效

拼搏现实的明天。 2022-09-16 12:24 174阅读 0赞

# 列与列对比 #

某个表中，有两列（id和c\_id）都建了单独索引，下面这种查询条件不会走索引

select * from test where id=c_id;

这种情况会被认为还不如走全表扫描。

# 存在NULL值条件(is null/ is not null) #

*  **索引中无法存储NULL值，所以where条件判断如果对字段进行了NULL值判断（is NULL/ is not null），则数据库放弃索引而进行全表查询**
 *  **我们在设计数据库表时，应该尽力避免NULL值出现**，如果非要不可避免的要出现NULL值，也要给一个DEFAULT值，数值型可以给0、-1之类的， 字符串有时候给空串有问题，就给一个空格或其他

select * from test where id is not null;

> 为什么索引列无法存储Null值？

索引是有序的，NULL值进入索引时，无法确定其应该放在哪里（将索引值进行建树，其中必然涉及到诸多的比较操作，null值是不确定值无法比较，无法确定NULL出现在索引树的叶子节点位置）

> 如果需要把空值存入索引，方法有二

*  其一，把NULL值转为一个特定的值，在WHERE中检索时，用该特定值查找。（推荐）
 *  其二，建立一个复合索引。例如`create index ind_a on table(col1,1);`通过在复合索引中指定一个非空常量值，而使构成索引的列的组合中，不可能出现全空值。

[浅谈索引序列之是否可以存储NULL值？][NULL]

# like查询以%开头 #

当使用模糊搜索时，尽量采用后置的通配符，

例如：name||’%’，因为走索引时，其会从前去匹配索引列，这时候是可以找到的，如果采用前匹配，那么查索引就会很麻烦，比如查询所有姓张的人，就可以去搜索’张%’。相反如果你查询所有叫‘明’的人，那么只能是%明。这时候索引如何定位呢？

前匹配的情况下，执行计划会更倾向于选择全表扫描。后匹配可以走INDEX RANGE SCAN。

所以业务设计的时候，尽量考虑到模糊搜索的问题，要更多的使用后置通配符。  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_14_color_FFFFFF_t_70_g_se_x_16]

# 条件上包括函数 #

查询条件上尽量不要对索引列使用函数，比如下面这个SQL

select * from test where upper(name)='SUNYANG';

这样是不会走索引的，因为索引在建立时会和计算后可能不同，无法定位到索引。但如果查询条件不是对索引列进行计算，那么依然可以走索引。比如

select * from test where name=upper('sunyang');
    --INDEX RANGE SCAN

这样的函数还有：to\_char、to\_date、to\_number、trunc等

# 数据类型出现隐式转换 #

比如：

*  如果类型是字符串，那一定要在条件中将数据使用引号括起来，否则不使用索引  
    ![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_14_color_FFFFFF_t_70_g_se_x_16 1]

# 如果条件中有or，即使其中有条件带索引也不会使用（这也是为什么尽量少用or的原因） #

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_15_color_FFFFFF_t_70_g_se_x_16]  
要想使用or，又想让索引失效，只能将or条件中的每个列都加上索引

# 如果mysql估计使用全表扫描比使用索引块，就不会使用索引 #

查看索引的使用情况：

show status like ‘Handler\_read%’;

注意：  
handler\_read\_key:这个值越高越好，越高表示使用索引查询到的次数  
handler\_read\_rnd\_next:这个值越高，说明查询低效

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_19_color_FFFFFF_t_70_g_se_x_16]

# 组合索引，不是使用第一列索引，索引失效（最左匹配原则） #

[NULL]: https://blog.csdn.net/cuiwei2936/article/details/100340224
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_14_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/68165ec7415c4d87b0964e0b4e78c743.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_14_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/affff041a9cf4e71bd96279f14a4cdfa.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_15_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/6e1d9bed7cda419a9320c22ca60edcaf.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_19_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/4868dc8a3d604ca9b3a36b77492d9ab6.png