总结：模 型 数 空 运 最 快
1、模：
模糊查询时，like查询如果以%开头，则索引失效
eg: select name from test where name like ‘%dd’
2、型：
表示数据类型错误，比如数据库字段类型是varchar，但你的查询where 后边加的是一个number类型的，索引也会失效
eg: select name from test where height =180; heigth是varchar类型的所有会索引失效(这种情况可以建立基于函数的索引，就不会失效了)
3、数：
表示函数，对索引的字段使用内部函数，索引也会失效
eg: select name from test where DATE(create_time)=‘2020-01-01’
4、空：
表示Null，索引是不存储空值的，如果不限制索引列默认都是not null，如果对字段进行判空，数据库会认为这个索引列可能有空值，所以不会按照索引列进行计算（设计数据库字段的时候如果没有要求字段必须为null，最后给这个字段设置一个默认值字符串）
eg:select age from test where name is null
select age from test where name is not null
5、运：
表示运算，对索引列进行计算（+ - * / ！ ！= <>）等运算，会导致索引失效
eg:select name from test where age-1=20;
6、最：
表示最左原则，在复合索引中索引列的顺序至关重要，如果不是按照索引的最左列开始查找，则无法使用索引。
7、快：
全表扫描更快的意思，如果数据库预计使用全表扫描要比使用索引快，则不适用索引。
（什么情况下全表扫描比索引扫描效率更高）
查询结果的记录数量小于表中记录一定比例的时候。这个主要是由于索引扫描后要利用索引中的指针去逐一访问记录，假设每个记录都使用索引访问，则读取磁盘的次数是查询包含的记录数T，而如果表扫描则读取磁盘的次数是存储记录的块数B，如果T>B 的话索引就没有优势了。对于大多数数据库来说，这个比例是10%（oracle，postgresql等），即先对结果数量估算，如果小于这个比例用索引，大于的话即直接表扫描

目录

一、概述

二、索引失效的场景

一、概述

为了提高数据库SQL语句执行效率，我们第一时间想到的就是，通过在表上建立一些合适的索引。但是有时候我们明明在列上面创建了索引，查询条件也用到了该列，但是通过查询执行计划的时候，却发现没有走索引查询。本篇文章我们将总结一些常见的索引的失效场景。

二、索引失效的场景

【a】存在NULL值条件

在设计数据库时，我们应该尽量避免某个列的值为空，如果非要不可避免的要出现NULL值，我们可以给它一个DEFAULT值，比如-1等。如果索引列是可空的，是不会给其建立索引的，这种情况下，索引值是少于表的count(*)总记录数的，所以这种情况下可能导致索引用不上，而是进行全表扫描。如下示例：

【b】索引使用不满足最左前缀匹配原则

• 如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；
• 如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引

假如表建立了一个联合索引(a、b、c)三列，如果在查询条件中没有使用到最左边的索引列a,那么可能导致索引失效。如下示例：

--能利用索引
select * from test where a = 1 and b = 2 and c = 3;

--不能利用索引
select * from test where b = 2 and c = 3 

注意：索引一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整

【c】使用前置LIKE模糊查询

模糊搜索时，尽量采用后置%，因为走索引时，其会从头开始去匹配索引列，这时候是可以找到的，如果采用前%，那么查索引就会比较麻烦。这时候索引如何定位呢？前%的情况下，执行计划会更倾向于选择全表扫描，使用后%的话执行计划可以走INDEX RANGE SCAN索引范围扫描，能利用上索引。
所以业务设计的时候，尽量考虑到模糊搜索的问题，要尽可能使用后置%。如下示例：

--oracle下
select * from zhxg_zhcp_cpmb t1 where t1.cpxmc like '德育' || '%';
--尽量不使用前%
select * from zhxg_zhcp_cpmb t1 where t1.cpxmc like '%' || '德育';

--mysql下
SELECT * FROM `account_info` t WHERE t.`account_name` LIKE '张%'
--尽量不使用前%
SELECT * FROM `account_info` t WHERE t.`account_name` LIKE '%张'

【d】隐式类型转换

当查询条件存在隐式转换时，索引可能会失效。如下示例： tx_no在数据库是varchar类型，但是我们传入的参数却使用了int或者bigint其他类型。

--tx_no VARCHAR(64)  
SELECT * FROM `account_trans_record` t WHERE t.`tx_no` = 123

【e】where条件进行函数运算[to_char、to_date、upper、lower、to_number、trunc等函数]

如果我们的查询条件中包含对索引列的函数运算的话，可能导致执行计划进行全表扫描。如下示例：

select * from zhxg_zhcp_cpmb t1 where upper(t1.cpxmc) = 'ZHANGSAN';

相反，如果查询条件不是对索引列进行计算，那么依然可以走索引。比如我们将函数运算移到等于号右边，这样是可以利用上索引的。

select * from zhxg_zhcp_cpmb t1 where t1.cpxmc = upper('zhangsan')

【f】not in、not exists等

我们知道建立索引时，给每一个索引列建立一个条目，如果查询条件为等值或范围查询时，索引可以根据查询条件去找对应的条目。反过来当查询条件为非时，索引定位就相对困难，执行计划此时可能更倾向于全表扫描，这类的查询条件有：<>、NOT、in、not exists。如下示例：

--<>
select *
  from zhxg_zhcp_cpmb t1
 where t1.cpxid <> '6d4c1af7-3fb4-45a1-b555-3db8246e2ad5';
 
--in 
select *
  from zhxg_zhcp_cpmb t1
 where t1.cpxid in ('6d4c1af7-3fb4-45a1-b555-3db8246e2ad5',
                    '5fbea6c3-a367-4c51-beeb-9dbeb7d9b308');
                    
--not in                     
select *
  from zhxg_zhcp_cpmb t1
 where t1.cpxid not in
       ('6d4c1af7-3fb4-45a1-b555-3db8246e2ad5',
        '5fbea6c3-a367-4c51-beeb-9dbeb7d9b308');
        
--not exists           
select *
  from zhxg_zhcp_cpmb t1
 where not exists
 (select 1 from zhxg_zhcp_hjbd t2 where t1.cpxid = t2.cpxid);

【g】查询条件进行谓词运算
这种情况，跟前面说到的函数运算差不多，如果我们确实需要对索引列进行谓词运算，我们尽量把计算移到=右边，即尽量不要对索引列进行谓词运算。如下示例：

SELECT * FROM `account_info` t WHERE id / 2 = 2;
--优化成下面：
SELECT * FROM `account_info` t WHERE id = 4;

本文只是总结了常见的一些索引失效的场景，当然肯定还有其他很多种情况，欢迎大家一起补充其他索引失效的场景。由于笔者水平有限，如上文有不对之处，还请指正。

1.不符合最左前缀法则（查询从索引的最左列开始不跳过中间的列）
2.范围查询右边的列不走索引
3.索引列上进行运算操作索引失效
4.字符串不加单引号，索引失效（底层进行运算操作）
5.使用or分隔开的条件，如果or前的条件有索引而or后面列没有索引，那么都不会走索引
6.以%号开头的Like模糊查询，索引失效（尾部模糊匹配索引不会失效）。
7. 如果走全表扫描比走索引快则走全表扫描。 （比如我以address列作为索引，sql底层发现大部分都是北京市，那还不如走全表扫描）

8.is null和is not null 可能会走索引可能不会走。比如我这一列里面基本上没有null，那么is null就走索引，is not null走全表扫描。
9.in走索引，not不走索引