数据库SQL优化

>>>本文主要针对Oracle

一般的SQL优化技巧

一、select语句中避免使用"*"

使用"*"替代所有的列，可以降低编写SQL语句的难度和减少SQL语句的复杂性，但是却降低了SQL语句执行的效率。

当一条SQL语句从客户端进程传递到服务器端进程后，Oracle需要执行如下步骤：

1、在共享池中搜索SQL语句是否已经存在。

2、验证SQL语句的语法是否准确。

3、执行数据字典来验证表和列的定义。

4、获取对象的分析锁，以便在语句的分析过程中对象的定义不会改变。

5、检查用户是否具有相应的操作权限。

6、确定语句的最佳执行计划。

7、将语句和执行方案保存到共享的SQL区。

使用"*"会导致Oracle系统需要通过数据字典将*转换成表的所有列。

二、使用where子句替代having子句

在select语句中，使用where子句过滤行，使用having子句过滤分组，而行被分组需要一定的时间，所以尽量先用where过滤掉尽量多的数据。

三、使用truncate替代delete

两者都可以删除表数据，区别是：truncate会一次性删除全部数据并且不会记录操作，不可回滚；delete会逐行删除数据并且使用回滚段记录操作，在commit前可以rollback。如果确定要删除表中的所有行，建议使用truncate语句。

四、在确保完整性的情况下多用commit语句

用户进行DML操作后，如果不使用commit命令进行提交，则Oracle会在回滚段中记录DML操作，以便用户使用rollback命令对数据进行恢复。实现这种数据回滚功能，需要花费相应的时间和空间资源。

五、使用表连接而不是多个查询

尽量减少表的查询次数，主要是指可以使用一次查询获得的数据，尽量不要通过两次或更多次的查询获得。

六、使用exists替代in

in操作符用于检查一个值是否包含在列表中。exists与in不同，exists只检查行的存在性，而in检查实际的值。在子查询中，exists提供的性能通常比in提供的性能要好。因此建议用exists替代in，用not exists替代not in。

七、使用exists替代distinct

在连接查询的select语句中，distinct关键字用于禁止重复行的显示；exists用于检查子查询返回的行的存在性。尽量使用exists替代distinct，因为distinct在禁止重复行显示之前要排序检索到的行。

使用exists操作符实现子查询时，推荐使用表的连接查询来替代exists的使用方式。

八、使用"<="替代"<"

使用"<"会定位到当前数，再去寻找比它小的数。

九、使用完全限定的列引用

在查询中包含多个表时，为每个表指定表别名，并且为所引用的每列都显式地指定合适的列名，这称为完全限定的列引用，这减少了解析列的时间。

表的连接方法

一、form子句中表的顺序

Oracle的解析器在处理form子句中的表时，是按照从右到左的顺序进行处理。建议在使用表的连接查询时，选择记录行数最少的表作为驱动表，也就是作为form子句中的最后一个表。

二、where子句的连接顺序

Oracle采用自右至左（自下向上）的顺序解析where子句，根据这个顺序，表之间的连接应该写在其它where条件之前，将可以过滤掉最大数量记录的条件写在where子句的末尾。

有效使用索引

优点：当Oracle查找执行select和update语句的最佳路径时，Oracle优化器将使用索引，同时在连接多个表时使用索引也可以提高效率。

缺点：索引需要额外的存储空间和处理操作。

一、创建索引的基本原则

哪些表？

1、对于经常以查询关键字为基础的表，并且该表中的数据行是均匀分布的。

2、以查询关键字为基础，表中的数据行随机排序。

3、表中包含的列数相对比较少。

4、表中的大多数查询都包含相对简单的where子句。

哪些列？

1、经常在where子句中使用的列。

2、经常在表连接查询中用于表之间连接的列。

3、不宜将经常修改的列作为索引项。

4、不宜将经常在where子句中使用，但与函数或操作符相结合的列作为索引项。

5、对于取值较少的列，应考虑建立位图索引，而不应该采用B数索引。

注意：除了所查询的表没有索引，或者需要返回表中的所有行时，Oracle会进行全表扫描以外，如果对索引列使用了函数或操作符（例如like），Oracle同样会对全表进行扫描。

二、索引列上所使用的操作符

对索引列的操作语句应该尽量避免“非”操作符的使用，例如not、!=、<>、!<、!>、not exists、not in和not like等，“非”操作符的使用会造成Oracle对表进行全表扫描。

使用like时，如果第一个字符是通配符，索引不再起作用。

三、避免对唯一索引列使用null值

在where子句中使用is null或is not null，对唯一索引项进行空值比较时，Oracle将停止使用该列上的唯一索引，导致Oracle进行全表扫描。

列中包含有null值的行都不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以设计数据库时，尽量不要让字段的默认值为null值。

四、选择复合索引主列

索引不仅可以基于单独的列，还可以基于多个列，在多个列上创建的索引叫复合索引。

创建复合索引时，应该按照如下原则：

选择经常在where子句中使用、并且由and操作符连接的列作为复合索引列。

选择where子句中使用频率相对较高的列排在最前面，或者根据需要为其它列创建单独的索引。

只有当复合索引中的第一列被where子句使用时，Oracle才会使用该复合索引。

五、监视索引是否被使用

通过v$object_usage视图，查看索引的使用状态。