虚谷数据库索引失效的场景

前言

       我们都知道在执行查询的时候，执行计划走了索引和没走索引cost代价可能差别会很大的。往往创建好的索引是查询SQL调优的最关键的一环，然而有时候创建了索引可能有的SQL不走索引，就是索引失效。在体验虚谷数据库的时候，有的查询SQL同样会导致索引失效，下面举一些常见的索引失效例子。

一、创建测试表

create table CLUS_TB (id int , name varchar, c2 varchar);

DECLARE
begin
for i in 1..100000
loop
    INSERT INTO CLUS_TB (id, name,c2 ) VALUES (i, CONCAT('NAME_DATA', i), CONCAT('CLUS_DATA', i));
END loop;
end;
/

使用左右模糊匹配

       一般情况下，我们使用左右模糊查询像是 like %aaa 或者 like aaa% 这两种方式都会导致索引失效。

create index idx_CLUS_TB_name on CLUS_TB(name);
--收集全部索引列统计信息
exec dbms_stat.analyze_table('SYSDBA.CLUS_TB','all index columns',1,null);
explain select * from SYSDBA.CLUS_TB where name like 'NAME%';

       查询NAME前缀的数据不会走索引扫描。

explain select * from SYSDBA.CLUS_TB where name like '%DATA2023';

       查询DATA2023后缀的数据不会走索引扫描。

对索引列使用函数

       有时候查询语句中想用函数来转换得到想要的值，查询条件中如果是索引列用到函数转换，那么就会导致索引失效。

EXPLAIN select * from SYSDBA.CLUS_TB where LENGTH(NAME) = 9;

       导致不走索引的原因是索引是按照原值创建的，查询的值是使用函数转换的，所以值不同了只有走顺序扫描。如果创建索引的时候使用函数索引，那么语句执行计划就显示走的索引。

create index idx_CLUS_TB_name_length on SYSDBA.CLUS_TB(length(name));
exec dbms_stat.analyze_table('SYSDBA.CLUS_TB','all index columns',1,null);
EXPLAIN select * from SYSDBA.CLUS_TB where LENGTH(NAME) = 9;

       索引创建有函数索引的功能。

对索引列表达式计算

       在查询条件中对索引进行表达式计算，也是无法走索引的。

EXPLAIN SELECT * FROM CLUS_TB WHERE id*2 = 1122;

       其实和索引列使用函数表达式类似，如果条件修改为 where id = 1122/2 ，不对索引列进行表达式计算，就可以索引扫描了。

EXPLAIN SELECT * FROM CLUS_TB WHERE id = 1122/2;

索引类型转换

       上次有位用户遇到一个问题，between ... and ... 查询不出数据，询问后得知原因是 between ... and ... 中数据类型为 varchar ，需要做数据类型转换，存的是小数所以使用 cast(id as numeric(10,1)) 类型转换后查询出结果。在索引列上做转换类型，类型变化后就不会走索引了。

explain select * from t_varchar  where cast(id as numeric(10,1)) between -6.2 and -0.2;

联合索引非最左匹配

       联合索引是指我们在创建的索引可以关联多个列 (a,b,c) ，SQL为 select a,b,c from table where a='',b='',c='' 的时候，同时查询这三列数据不用回表做解析，这种类型的SQL创建联合索引效率更高。在创建之后的使用也需要注意先后顺序，(a,b,c) 和 (b,a,c) 使用的时候是有区别的，在虚谷数据库中，正确使用联合索引要遵循最左匹配原则，按照最左优先的方式进行索引的匹配。

explain select * from SYSDBA.CLUS_TB where id = 112 and name = 'aa' and c1 = 'asd';
explain select * from SYSDBA.CLUS_TB where id = 112 and c1 = 'asd';
explain select * from SYSDBA.CLUS_TB where c1 = 'asd' and id = 112;

       不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效。

explain select * from SYSDBA.CLUS_TB where name = 'aa' and c1 = 'asd';

       为什么不遵循最左匹配原则联合索引会失效呢？

       原因是在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。

总结

       在使用虚谷数据库的时候需要注意以上5中索引失效的情况。like %aaa% , like %aaa ,like aaa% 这三种方式都会造成索引失效，索引列使用函数会造成索引失效，索引列进行表达式计算索引失效，索引列发生类型转换会索引失效，没有遵循最左匹配原则索引失效。

参考文章：https://www.cnblogs.com/xiaolincoding/p/15839040.html