由索引未被使用，看SQL开发规范落地

《由索引未被使用，看SQL开发规范落地》要点：
本文介绍了由索引未被使用，看SQL开发规范落地，希望对您有用。如果有疑问，可以联系我们。

作者介绍

王科,新炬网络架构师.Oracle OCM,10年以上运维管理经验,擅长运维服务与运维工具的融合应用,在数据资产管理、云计算、大数据相关领域也均有一定的研究及实践.

上次丁俊大师在社群上做了CBO优化器和坑爹案例的分享后,反响不是一般的强烈,但其中也有一部分同学表示太高大上了(我也是这样觉得的),消化起来相当有难度,于是便有了本文.绕开复杂的CBO优化器不说,本文将帮你理清那些因为SQL语句编写规范问题导致没有充分利用索引来大幅提升效率的使用场景.

一、SQL无法走索引的情况及解决思路

因为数据库优化器不够智能,或者一些逻辑原因,导致SQL在比较适合走索引的情况下却无法正确利用索引.这时候,除了给数据库需要的统计信息之外,SQL语句本身还必须要给优化器足够多的额外有效信息,帮助优化器能够选择更好的执行计划.要让优化器正确选择需要的索引,要考虑两点：

• 如何避免优化器的限制
• 根据业务数据特点改写SQL语句

说明：这里说的走不了索引,是指走不了正常的RANGE SCAN,非(FAST) FULL INDEX SCAN.

SQL无法走索引常见的有如下8种情况：

1. 统计信息不准确
2. 索引列的值允许为NULL
3. 谓词使用了不等于(<>,!=)
4. LIKE前通配或全通配的查询
5. 索引列使用了函数、数学运算、其它表达式等
6. 使用了隐式类型转换
7. 查询转换失败
8. 其它语句逻辑原因

第一、二种情况在现实中比较常见,解决办法也相对比较简单,下面就不再作详细展开了.

谓词使用了不等于(<>,!=),走不了索引

解决方法：

1. 如果不等条件之外的值不多,而且是确定的,可以改为等值或IN查询,比如status状态字段一般值类别很少；
2. 如果不等条件之外的值很多,可以改为“> OR <”的形式,当然第2种方法包含了方法1.

举个例子,先构建测试场景：

谓词使用<>,无法利用索引：

将<>改写为OR连接后,能够正确使用索引,走OR扩展：

如果业务允许,改写为下列语句也是走索引的,不再演示.

SELECT * FROM t?WHERE t.NAME IN (‘ORADB1′,’ORADB2′,’ORADB3’);

LIKE前通配或全通配的查询,走不了索引

解决方法,有如下三种：

(1)根据业务需求,是否可以把前通配去掉

原来全通配,无法走索引：

把前通配去掉,改为后通配,可以正常使用索引：

(2)和此LIKE一样的前通配或全通配的SQL有很多,此谓词的LIKE变化不大?如果是,考虑建立函数索引,否则对于全通配问题最好办法就是全文索引.

创建instr函数索引：

(3)如果只是前通配,可以使用reverse函数索引(不是翻转键索引)

原始语句：
SELECT ?* FROM t WHERE t.NAME LIKE ‘%ORADB1’;

创建reverse函数索引,并改写语句,注意查找值要倒序：

注意：如果通配查询的是中文,要注意使用REVERSE翻转条件值,因为REVERSE内部会按字节翻转的,正确写法如：

SELECT * FROM t WHERE REVERSE(t.name) LIKE REVERSE(‘数据’)||’%’;

否则查询出来的数据不对,将可能影响到业务的正常运行.

索引列使用了函数、数学运算、其他表达式等,走不了索引

解决方法：去掉对索引列的相关运算,保持索引列纯净.

目前优化器对一些数学运算,还无法做很好的消除动作,所以对于索引列应该尽量保持纯净,否则可能无法用上正确的索引.

举例：

把语句的条件改写一下,将运算去掉：

以上例子只是简单的数学运算,可能的运算还有和其他列运算,比如where ID+ext_col…

记住一个原则：尽量保持索引列纯净.

使用了隐式类型转换,走不了索引

解决方法：必须避免隐式类型转换,全部要求显式类型转换(非索引列),且避免对索引列进行类型转换(有函数索引除外).如果类型不一致,不管是否发生自动类型转换,谓词的右值应该显式转换为与索引列保持一致(对于非索引列的运算也应该如此).

举例：

（编辑：ASP站长网）