前言
我们都知道在执行查询的时候,执行计划走了索引和没走索引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% 这三种方式都会造成索引失效,索引列使用函数会造成索引失效,索引列进行表达式计算索引失效,索引列发生类型转换会索引失效,没有遵循最左匹配原则索引失效。