SQL 调优

        在使用 DBMS 时经常对系统的性能有非常高的要求：不能占用过多的系统内存和CPU 资源、要尽可能快的完成的数据库操作、要有尽可能高的系统吞吐量。如果系统开发出来不能满足要求的所有性能指标，则必须对系统进行调整，这个工作被称为调优。绝对 DBMS 的性能的因素有两个因素：硬件和软件。使用频率的的 CPU、使用多处理器、加大内存容量、增加 Cache、提高网络速度等这些都是非常有效的硬件调优方式，不过对硬件进行调优对系统性能的提高是有限的，如果有非常好的硬件条件但是如果编写的 SQL 质量非常差的话系统的性能并不会有明显的改善，而如果能对 SQL 语句进行充分的优化的话即使硬件条件稍差的话，系统性能的变化也是非常惊人的。硬件的调优涉及到非常多的内容，不是本书所能覆盖的，因此本节将主要讲解 SQL 的调优。10.2.1 SQL 调优的基本原则。

        “二八原理”是一个普遍的真理，特别是在计算机的世界中表现的更加明显，那就是 20%的代码的资源消耗占用了 80%的总资源消耗。SQL 语句也是一种代码，因此它也符合这个原理。在进行 SQL 调优的时候应该把主要精力放到这 20%的最消耗系统资源的 SQL 语句中，不要想把所有的 SQL 语句都调整到最优状态。很多 DBMS 都提供了非常好的工具用来分析系统中所有 SQL 语句资源消耗的工具，借助于这些工具发现占用系统资源排在前面的 SQL 语句，然后尝试对它们进行优化，优化后再次执行分析，迭代这一过程，直到系统中没有明显的系统资源消耗异常的SQL 语句为止。

         索引是数据库调优的最根本的优化方法，很多优化手法都是围绕索引展开的，可以说索引是一切优化手法的“内功”，而所有的优化手法都是由索引衍化出来的招式而已。根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型：聚簇索引，数据表的物理顺序与索引顺序相同；非聚簇索引，数据表的物理顺序与索引顺序不相同。下面，我们举例来说明一下聚集索引和非聚集索引的区别：字典的目录就是一种索引，因为通过目录我们可以很快的定位到要检索的内容，而不用从头到尾把字典翻一遍。汉语字典一般都至少提供两种目录，一种是拼音目录，一种是偏旁部首目录。汉语字典是按照拼音的顺序排列的，因此拼音目录就是聚集索引，而偏旁部首目录则是非聚集索引。应该在表中经常搜索的列或者按照顺序访问的列上创建聚簇索引。当创建聚簇索引时要需要每一个表只能有一个聚簇索引，因为表中数据的物理顺序只能有一个，而非聚集索引则可以创建多个。由于索引需要占据一定的存储空间，而且索引也会降低数据插入、更新和删除的速度，所以应该只创建必要的索引，一般是在检索的时候用的字段中创建索引。索引还会造成存储碎片的问题。当删除一条记录时将会导致对应的索引中的该记录的对应项为空，由于索引是采用 B 树结构存储的，所以对应的索引项并不会被删除，经过一段时间的增删改操作后，数据库中就会出现大量的存储碎片，这和磁盘碎片、内存碎片产生原理是类似的，这些存储碎片不仅占用了存储空间，而且降低了数据库运行的速度。如果发现索引中存在过多的存储碎片的话就要进行“碎片整理”了，最方便的“碎片整理”手段就是重建索引，重建索引会将先前创建的索引删除然后重新创建索引，主流数据库管理系统都提供了重建索引的功能，比如 REINDEX、REBUILD 等，如果使用的数据库管理系统没有提供重建索引的功能，可以首先用 DROP INDEX 语句删除索引，然后用 ALTER TABLE 语句重新创建索引。

        一般地，系统访问数据库中的数据，可以使用两种方法：全表扫描和索引查找。全表扫描，就是指系统必须在数据表中逐条检索表中的每条记录，以检查该记录是否匹配检索条件。全表扫描有可能会造成巨大的性能损失，当然也有可能不会影响性能，这取决于表中的数据量，如果表中有上千万条甚至上亿条记录的话，全表扫描的速度会非常慢，而如果表中只有几条、几十条记录的话表扫描的性能消耗就可以忽略不计了。当表中数据量比较小的时候，使用全表扫描非常有用。但是随着表中数据量的增加，全表扫描会导致系统性能严重下降。如果表中有索引并且待匹配条件符合索引的要求的话，DBMS 就不会执行全表扫
描，而是直接到索引中查找，这将大大加快检索的速度。DBMS 中都有查询优化器，它会根据分布的统计信息生成该查询语句的优化执行规划，以提高访问数据的效率为目标，确定是使用全表扫描还是使用索引查找。注意并不是表中存在索引在进行检索的时候就会使用索引查找，如果使用不当检索的过程仍然会是采用全表扫描，这样索引就起不到效果了。关于如何才能避免全表扫描的发生我们会在下一节中介绍。

        下面将会列出了一些常用的优化手法，注意这些优化手法只是一些常规条件下的优化手法，具体的优化效果是与使用的 DBMS 以及数据的特点密切相关的，需要根据具体情况来使用不同的优化手法，如果使用不当的话有可能会适得其反:

        1、在经常需要进行检索的字段上创建索引，比如经常要按照图书名称进行检索，那么就应该在图书名称字段上创建索引，如果经常要按照员工部门和员工岗位级别进行检索，那么就应该在员工部门和员工岗位级别这两个字段上创建索引。创建索引给检索带来的性能提升往往是巨大的，因此在发现检索速度过慢的时候应该首先想到的就是创建索引。

        2、程序中通常是根据用户的输入来动态执行 SQL 语句，这时应该尽量使用参数化SQL，这样不仅可以避免 SQL 注入漏洞攻击，最重要数据库会对这些参数化 SQL 执行预编译，这样第一次执行的时候 DBMS 会为这个 SQL 语句进行查询优化并且执行预编译，这样以后再执行这个 SQL 的时候就直接使用预编译的结果，这样可以大大提高执行的速度。

        3、调整 WHERE 子句中的连接顺序
DBMS 一般采用自下而上的顺序解析 WHERE 子句，根据这个原理,表连接最好写
在其他 WHERE 条件之前，那些可以过滤掉最大数量记录。
比如下面的 SQL 语句性能较差：
SELECT *
FROM T_Person
WHERE FSalary > 50000
AND FPosition= ‘MANAGER’
AND 25 < (SELECT COUNT(*) FROM T_Manager
WHERE FManagerId=2);
我们将子查询的条件放到最前面，下面的 SQL 语句性能比较好：
SELECT *
FROM T_Person
WHERE
25 < (SELECT COUNT(*) FROM T_Manager
WHERE FManagerId=2)
AND FSalary > 50000
AND FPosition= ‘MANAGER’ ;

        4、SELECT 语句中避免使用'*'
SELECT *比较简单，但是除非确实需要检索所有的列，否则将会检索出不需要的列，这回增加网络的负载和服务器的资源消耗；即使确实需要检索所有列，也不要使用SELECT *，因为这是一个非常低效的方法，DBMS 在解析的过程中，会将*依次转换成所有的列名，这意味着将耗费更多的时间。

        5、尽量将多条 SQL 语句压缩到一句 SQL 中每次执行 SQL 的时候都要建立网络连接、进行权限校验、进行 SQL 语句的查询优化、发送执行结果，这个过程是非常耗时的，因此应该尽量避免过多的执行 SQL 语句，能够压缩到一句 SQL 执行的语句就不要用多条来执行。

        6、用 Where 子句替换 HAVING 子句避免使用 HAVING 子句，因为 HAVING 只会在检索出所有记录之后才对结果集进行过滤。如果能通过 WHERE 子句限制记录的数目，那就能减少这方面的开销。HAVING 中的条件一般用于聚合函数的过滤，除此而外，应该将条件写在 WHERE 子句中。

        7、使用表的别名当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少那些由列名歧义引起的语法错误。
        8、用 EXISTS 替代 IN
        在查询中，为了满足一个条件，往往需要对另一个表进行联接，在这种情况下，使用 EXISTS 而不是 IN 通常将提高查询的效率，因为 IN 子句将执行一个子查询内部的排序和合并。下面的语句 2 就比语句 1 效率更加高。
语句 1：
SELECT * FROM T_Employee
WHERE FNumber> 0
AND FDEPTNO IN (SELECT FNumber
FROM T_Department
WHERE FMangerName = 'Tome')
语句 2：
SELECT * FROM T_Employee
WHERE FNumber > 0
AND EXISTS (SELECT 1
FROM T_Department
WHERE T_Department. FDEPTNO = EMP.FNumber
AND FMangerName = ‘MELB’)

        9、用表连接替换 EXISTS
        通常来说，表连接的方式比 EXISTS 更有效率，因此如果可能的话尽量使用表连接替换 EXISTS。下面的语句 2 就比语句 1 效率更加高。
语句 1：
SELECT FName FROM T_Employee
WHERE EXISTS
(
SELECT 1 FROM T_Department
WHERE T_Employee.FDepartNo= FNumber
AND FKind='A'
);
语句 2：
SELECT FName FROM T_Department, T_Employee
WHERE T_Employee. FDepartNo = T_Departmen. FNumber
AND FKind = ‘A’ 

        10、避免在索引列上使用计算
        在 WHERE 子句中，如果索引列是计算或者函数的一部分，DBMS 的优化器将不会使用索引而使用全表扫描。
例如下面的 SQL 语句用于检索月薪的 12 倍大于两万五千元的员工：
SELECT *FROM T_Employee
WHERE FSalary * 12 >25000;
由于在大于号左边的是 FSalary 与 12 的成绩表达式，这样 DBMS 的优化器将不会
使用字段 FSalary 的索引，因为 DBMS 必须对 T_Employee 表进行全表扫描，从而计算
FSalary * 12 的值，然后与 25000 进行比较。将上面的 SQL 语句修改为下面的等价写法
后 DBMS 将会使用索引查找，从而大大提高了效率：
SELECT *FROM T_Employee
WHERE FSalary >25000/12;
同样的，不能在索引列上使用函数，因为函数也是一种计算，会造成全表扫描。下
面的语句 2 就比语句 1 效率更加高。
语句 1：
SELECT * FROM T_Example
WHERE ABS(FAmount)=300
语句 2：
SELECT * FROM T_Example
WHERE FAmount=300 OR FAmount=-300

        11、用 UNION ALL 替换 UNION
当 SQL 语句需要 UNION 两个查询结果集合时，即使检索结果中不会有重复的记
录，如果使用 UNION 这两个结果集同样会尝试进行合并，然后在输出最终结果前进行
排序。
因此，如果检索结果中不会有重复的记录的话，应该用 UNION ALL 替代 UNION，这
样效率就会因此得到提高。下面的语句 2 就比语句 1 效率更加高。
语句 1：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS1
WHERE TRAN_DATE = '20010101'
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS2
WHERE TRAN_DATE ='20010102'
语句 2：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS1
WHERE TRAN_DATE ='20010101'
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS2
WHERE TRAN_DATE = '20010102'

        12、防止检索范围过宽
如果 DBMS 优化器认为检索范围过宽，那么它将放弃索引查找而使用全表扫描。下面是几种可能造成检索范围过宽的情况：
使用 IS NOT NULL 或者不等于判断，可能造成优化器假设匹配的记录数太多。使用 LIKE 运算符的时候，"a%"将会使用索引，而"a%c"和"%c"则会使用全表扫描，因此"a%c"和"%c"不能被有效的评估匹配的数量。