基于索引的SQL语句优化

基于索引的SQL语句优化

一、尽量避免非操作符的使用

通常情况下，为了对指定列建立特定的条件，需要在WHERE子句中使用诸如NOT、!=、<>、!<、!>等操作符，在索引列上使用这些非操作符，DBMS是不使用索引的，可以将查询语句转换为可以使用索引的查询。例：

SELECT * FROM ORDERS WHERE ORDERDATE<>1997－l2 转化为：SELECT * FROM ORDERS WHERE ORDERDATE l998-l-l

这样DBMS就能利用索引字段ORDERDATE，大大提高查询效率。

二、避免困难的正规表达式

MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。

例如：SELECT * FROM STUDENT WHERE STUDENT_NUM LIKE “98_ _”

即使在STUDENT_NUM字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT * FROM STUDENT WHERE STUDENT_NUM >”98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。如果一定要使用通配符也要避免通配符在搜索字段的首部出现，这种情况下DBMS的优化器不会使用索引[6]。

三、避免在索引列上使用NULL关键字

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。即使索引有多列，只要这些列中有一列含有NULL，该列就会从索引中排除，也就是说如果某列存在空值，即使对该列建索引也不会提高性能[7]。

任何在WHERE子句中使用IS NULL或IS NOT NULL的语句，优化器是不允许使用索引的。

四、避免对查询的列使用数学运算

如果在查询列使用数学运算，则DBMS优化器先要处理数学运算也会影响查询效能。例如：

(1)SELECT * FROM ORDERDETAILS WHERE QUANTITY*2<50

(2)SELECT * FROM ORDERDETAILS WHERE QUANTITY<25

虽然这两条查询的结果完全相同，但某些情况下第二个语句的执行效率远高于第一个，因此在查询前应将数学运算转化。

五、尽量去掉IN或OR

含有“IN”或“OR”的WHERE子句常会令索引失效；在不产生大量重复值的情况下，可以考虑把子句拆开，拆开的子句中应该包含索引。例：SELECT COUNT (*) FROM EMP WHERE EMP_ID IN (‘0’,’1’)；

可以将子句分开：

SELECT COUNT (*) FROM EMP WHERE EMP_ID =‘0’；

SELECT COUNT (*) FROM EMP WHERE EMP_ID =‘1’；

然后再做一个简单的加法，与原来的SQL语句相比，查询速度有了明显提高。

六、限制查询范围，减少全范围搜索

例：以下查询表RECORD中时间EMP_TIME中小于2003年6月1日的数据。

SELECT * FROM RECORD

WHERE EMP_TIME<=TO_DATE(‘20030601’,’YYYYMM’) ；

查询计划表明，上面的查询对表进行了全表扫描，如果知道表中最早的数据为2000年1月1日，那么可以增加一个最小时间，保证查询在一个完整的范围之内[8]。

SELECT * FROM RECORD

WHERE EMP_TIME<=TO_DATE(‘20030601’,’YYYYMM’) ；

AND

EMP_TIME>=TO_DATE(‘20000101’,’YYYYMM’)；

后一种SQL语句利用了EMP_TIME字段上的索引，从而可以提高查询的效率。把“20030601”换为一个变量，根据取值的机率，可以证明有5O％以上的机率提高查询效率。同理，对于大于某个值的查询，如果知道当前可能的最大值，也可以在WHERE子句中加上“AND 列名<最大值”来限制查询范围，以提高查询的效率。

七、避免使用不兼容的数据类型

数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如：

SELECT TITLE FROM TITIES WHERE PRICE > 100；

在这条语句中，“PRICE”字段是“MONEY”型的，优化器很难对其进行优化，因为100是个整型数，应当在编程时将整型转化成为货币类型，而不要等到运行时转化。

当比较不同数据类型的数据时，ORACLE自动对列进行简单的类型转换。

例如，假设EMP_TYPE是一个字符类型的索引列。

SELECT * FROM EMP WHERE EMP_TYPE=123；

这个语句被ORACLE转换为：SELECT * FROM EMP WHERE

TO_NUM(EMP_TYPE)=123；

因为内部发生了类型转换，这个索引将不会被用到。

为了避免ORACLE对SQL进行隐式的类型转换，最好把类型转换用显式形式表现出来。尤其要注意当字符和数值比较时，ORACLE会优先转换数值类型到字符类型。

八、CBO下使用更具选择性的索引

基于代价的优化器(CBO,COST-BASED OPTIMIZER)对索引的选择性进行判断来决定索引的使用是否能提高效率。我们知道选择性高的字段应该建立索引，原因是：如果索引有很高的选择性，那就是说对于每个不重复的索引键值，只对应数量很少的记录。选择性越高，通过索引键值检索出的记录就越少。如果索引的选择性很低，检索数据就需要大量的索引范围查询操作和ROWID访问表的操作，也许会比全表扫描的效率更低。

记住下面两条经验：①如果检索数据量超过30％的表中记录数，使用索引将没有显著的效率提高；②在特定情况下，用索引也许会比全表扫描慢，但这是同一个数量级上的区别。通常情况下，使用索引比全表扫描要快几倍乃至几千倍。

九、定期地重构索引是有必要的

索引需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT、DELETE、UPDATE将为此多付出4至5次的磁盘I/O，可以通过改变参数ALTERINDEXREBUILD来定期重构。