数据库优化之SQL语句性能调整原则

首先，提出的问题

在应用系统开发初期，由于数据库中数据的缺乏，对于查询SQL语句和编写复杂视图，无法得知SQL语句的各种编写方法的性能。但是，如果将应用系统提交到实际应用中，随着数据库中数据的增加，系统的响应速度将成为系统目前需要解决的最重要的问题之一。系统优化的一个重要方面是SQL语句的优化。对于海量数据，差的SQL语句和高质量的SQL语句的速度差可以达到上百倍。可见，对于一个系统来说，并不是简单的实现其功能，而是要写出高质量的SQL语句来提高系统的可用性。

在大多数情况下，数据库使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果SQL语句的where子句中写的SQL代码不合理，就会导致优化器删除索引，使用全表扫描。一般这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句的时候，要知道优化器是用什么原理来删除索引的，这有助于编写高性能的SQL语句。

二、SQL语句的编写问题

详细介绍了一些SQL语句的where子句编写时应该注意的问题。在这些where子句中，即使有些列有索引，系统在运行SQL语句时也因为SQL不好而无法使用索引，但也使用全表扫描，导致响应速度大大降低。

1.为空且不为空

不能使用null作为索引，任何包含null值的列都不会包含在索引中。即使索引有多个列，只要其中一列包含null，该列就会被排除在索引之外。也就是说，如果一个列的值为空，即使对该列进行索引也不会提高性能。

任何在where子句中使用is null或not null的语句优化器都不允许使用索引。

2.连接列

对于具有连接的列，即使最后一个连接值是静态值，优化器也不会使用索引。让我们看一个例子。假设有一个员工列表。雇员的姓和名存储在两列中(名和姓)。现在，我们必须询问一位名叫比尔·克立顿的雇员。

以下是使用联接查询的SQL语句。

select * from employess
where
first _ name | | ' ' | | last _ name = ' Beill Cliton '；
上面的语句完全可以查出Bill Cliton是否是员工，但是这里需要注意的是，系统优化器并没有使用基于last_name创建的索引。

当编写以下SQL语句时，Oracle系统可以采用基于姓氏创建的索引。

select * from employee
其中
first _ name = ' Beill ' and last _ name = ' Cliton '；
以下情况如何处理？如果Bill Cliton的名字存储在一个变量(name)中，如何避免遍历整个过程，在这种情况下使用索引？可以使用函数将变量名中的姓和名分开，但需要注意的是，该函数不能作用于索引列。以下是SQL查询脚本:

select * from employee
其中
first _ name = SUBSTR(' & & name '，1，INSTR('&&name '，' ')-1)
last _ name = SUBSTR(' & & name '，INSTR('&&name '，' ')+1)

3.带通配符的like语句(%)

用上面同样的例子来看这种情况。目前需求如下:要求在员工列表中查询姓名中包含克立顿的人。您可以使用以下查询SQL语句:

select * from employee where last _ name like“% cliton %”；

这里，通配符(%)出现在搜索词的开头，所以不使用姓氏的索引。在许多情况下，这种情况可能无法避免，但是您必须意识到使用通配符会降低查询速度。但是，当通配符出现在字符串的其他地方时，优化器可以利用索引。以下查询中使用了该索引:

select * from employee where last _ name like ' c % '；

4.按语句排序

Order语句确定Oracle如何对返回的查询结果进行排序。Order语句对要排序的列没有特殊的限制，并且还可以向列添加函数(如join或append等)。).Order by语句中的任何非索引项或计算表达式都会降低查询速度。

仔细检查order by语句，找出非索引项或表达式，这将降低性能。这个问题的解决方案是重写order by语句以使用索引，或者为used列构建另一个索引，并且绝对避免在order by子句中使用表达式。
5。不

我们在查询时经常会在where子句中使用一些逻辑表达式，比如大于、小于、等于和不等于等。我们也可以用AND (AND)，or (OR)和NOT (NOT)。NOT不能用来否定任何逻辑运算符号。以下是NOT子句的一个示例:

...如果不是(状态= '有效')

如果要用NOT，就要在倒装句前加括号，在词组前加NOT运算符。NOT运算符包含在另一个逻辑运算符中，该运算符不等于()运算符。换句话说，即使您没有在查询where子句中显式添加NOT单词，NOT仍然在运算符中，如下例所示:

...其中状态为“无效”；

请看下面的例子:

select * from工资3000的员工；

对于此查询，可以将其重写为不使用NOT:

select * from工资3000的员工；

虽然两个查询的结果相同，但第二个查询方案比第一个查询方案快。第二个查询允许数据库对salary列使用索引，而第一个查询不能使用索引。

6.存在于

有时一列与一系列值进行比较。最简单的方法是在where子句中使用子查询。where子句中可以使用两种格式的子查询。

第一种格式是使用IN运算符:

...where列在(select * from...在哪里...);

第二种格式是使用EXIST运算符:

...存在位置(从中选择“X”...在哪里...);

我相信大部分人都会用第一种格式，因为更容易写，但实际上第二种格式远比第一种格式效率高。几乎所有的IN运算符子查询都可以使用数据库中的EXISTS重写为子查询。

在第二种格式中，子查询以' select' x '开头。使用EXISTS子句。无论子查询从表中提取什么数据，它都只查看where子句。这样优化器就不用遍历整个表，只需要根据索引就可以完成工作(这里假设where语句中使用的列有索引)。与IN子句相比，EXISTS使用连接的子查询，这比IN子查询更难构造。

使用EXIST，数据库系统将首先检查主查询，然后运行子查询，直到找到第一个匹配项，这样可以节省时间。当Oracle在子查询中执行时，它首先执行子查询，并将获得的结果列表存储在一个带索引的临时表中。在执行子查询之前，系统挂起主查询，并在子查询完成并存储在临时表中之后执行主查询。这就是为什么使用EXISTS通常比使用in要快。

同时，应尽可能使用NOT EXISTS而不是NOT IN。虽然两者都使用NOT(索引不能用来降低速度)，但在查询中NOT EXISTS比NOT更高效。