码农之家 - 索引
type: channel
link: http://www.liyx.fun/index.php/tag/索引/
items: oracle查询不走索引的一些情况（索引失效）

oracle查询不走索引的一些情况（索引失效）
type: item
date: 2021-09-02T10:50:37+08:00
description: Oracle建立索引的目的是为了避免全表扫描，提高查询的效率。但是有些情况下，即使建立了索引，但是执行写出来的查询还是很慢，然后通过执行计划会发现是索引失效导致的（不走索引，走全表扫描）。所以需要了解一下有哪些些情况会导致索引失效，即查询不走索引的原因。在写SQL的层面上一些骚操作会导致索引失效没有写WHERE子句或查询条件没有建立索引既然没有WHERE子句，那么就是查询全部数据了，相当于全表扫描，当然不走索引了。而查询条件上没有建立索引的话，索引都没有还走个毛索引啊。WHERE子句上没有使用索引中的引导列要使用索引，则查询条件中必须包含索引中的引导列。比如一个复合索引包含A，B，C，D四列，则A为引导列（排在第一位置的列）。如果WHERE子句中所包含的列是BCD或者BD等情况，则只能使用非匹配索引扫描。-- 创建包含A，B，C，D四列的复合索引 CREATE INDEX INDEX_ABCD ON LETTERS(A, B, C, D); -- 下列语句不会使用复合索引 SELECT * FROM LETTERS WHERE B = 'b' AND C = 'c'; SELECT * FROM LETTERS WHERE B = 'b' AND D = 'd'; SELECT * FROM LETTERS WHERE C = 'c' AND D = 'd'; SELECT * FROM LETTERS WHERE B = 'b' AND C = 'c' AND D = 'd'; 另外，单独引用复合索引里排第一位置的索引列也会导致索引失效，复合索引必须复合使用才能生效。-- 单独使用复合索引中的引导列也不会触发复合索引 SELECT * FROM LETTERS WHERE A = 'a' WHERE子句中使用IS NULL或IS NOT NULL 使用判断空或非空的条件会导致该索引列失效。-- COMM列的索引会失效 SELECT * FROM EMP WHERE COMM IS NULL; WHERE子句中使用函数 如果没有使用基于函数的索引，那么WHERE子句中对存在索引的列使用函数时，会使优化器忽略这些索引。-- 在没有建立基于函数索引的字段上使用了函数，会导致索引失效 SELECT * FROM STUFF WHERE TRUNC(BIRTHDAY) = '2019-04-01' 非要这样的话可以通过在索引列上建立基于函数的索引来解决问题。 -- 给BIRTHDAY列创建TRUNC函数的函数索引 CREATE INDEX STUFF_BIRTHDAY_FBI_IDX ON STUFF(TRUNC(BIRTHDAY)); 这样的话函数索引就起作用了，也就能在索引列上使用函数了，而且这时候你想不用函数都不行。 但是对于MIN，MAX函数等，Oracle仍然会使用索引，因为Oracle对这类聚合分析函数做了相应的优化。对索引列进行运算导致索引失效和使用函数同样的，如果使用运算，比如加减乘除非等，也会导致索引失效。-- 对索引列进行了减法运算，导致不走索引 SELECT * FROM USERS WHERE AGE - 1 = 23 -- 正确的写法（去除运算） SELECT * FROM USERS WHERE AGE = 24 使用前导模糊查询前导模糊查询的写法是LIKE '%T'，即通配符【%】写在要匹配的字符前面，这样的情况下不走索引。-- 前导模糊查询不走索引 SELECT * FROM LOVERS WHERE NAME LIKE '%静'; WHERE子句中使用不等于条件不等于操作包括：<>!=NOT COL >= ?NOT COL <= ?为什么不等于条件会不走索引，我们可以用常规的思维去推断一下。首先【不等于】这个概念可以推断成选取结果集中的一个比较大的部分，比如我们要找出中国人口中不是姓杨的，要找出资深程序员中不会秃头的，都可以认为是要返回结果集中一个很大的部分。因为Oracle的CBO是闭源的，我们只能推断Oracle会认为既然要返回结果集中很大的一个部分，不如直接使用全表扫描而不考虑索引。而且Oracle是建议，对于这些限制条件可以使用OR代替，例如COL <> 0可以替换成COL > 0 OR COL < 0。WHERE子句中使用NOT IN或NOT EXISTS使用NOT IN或NOT EXISTS也会导致不走索引。事实上NOT IN和NOT EXISTS也可以看做是不等于条件，不走索引的原因和上面的不等于条件相同。另外有一些人说使用IN是不走索引的，这是不对的，IN是可以走索引的，只是可能效率会比EXISTS低。等于和范围索引不会被合并使用SELECT * FROM CARS WHERE COL…