数据库SQL性能调优

      系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍；可见，对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。

一、合理创建、使用索引，提高SQL性能

      在多数情况下，数据库(Oracle/DB2/MySql等)使用索引来更快地遍历表，优化器/工具主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器不使用索引而使用全表扫描(劣质SQL)。一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。

(一)、数据结构及索引利弊

1、通常，尤其是大型数据库(Oracle/DB2等)的数据结构都是采用平衡树(Balanced-tree)结构。如Oracle数据库采用复杂的自平衡结构B-tree；

2、B-tree亦称B-树/B树，它的变体有B+-tree/ B*-tree (B~Tree)等。B-tree是一种多路搜索树，不仅限于二叉；它区别于二叉查找树(Binary Search Tree)/平衡二叉查找树(Balanced Binary Search Tree)/红黑树 (Red-Black Tree )，后三种是典型的二叉查找树。

3、索引利弊：     

3.1、良好的索引即选择性高的索引。索引的选择性可以帮助基于成本的优化器来决定执行路径。索引的选择性越高，针对每个不同值平均返回的行数也越少。对于组合索引，在索引中添加额外的列不会显著改善选择性，额外列增加的成本可能会超出收益。           

3.2、滥加索引的弊端：                         

3.2.1、存储在磁盘空间中，需要定期维护；                         

3.2.2、每当增减记录或则索引列被修改时，索引也会被修改；这样一个数据库增删改操作会导致额外的磁盘I/O访问(4-5次)；                     3.2.3、索引定期的重构也是必要的(Oracle语法)：ALTER  INDEX <INDEX_NAME> REBUILD TABLESPACE <TABLESPACE_NAME>;

3.3、扩展

3.3.1、集群因子：它记录在扫描索引时需要读取的数据块数量。如果使用的索引具有较大的集群因子，就必须访问更多的表数据块才可以获得每个索引块中对应的数据行(因为邻近行位于不同的数据块中)。如果集群因子接近于表中的数据块数量，就表示索引对应数据行的排序情况良好；反之，如果集群因子接近于表中的数据行数量，就表示索引对应的数据块排序情况不佳。表中数据的集群因子，可以用来提高范围扫描类型操作语句的性能。通过决定在语句中列是如何使用的，对这些列进行索引可以带来很大益处。

(二)、合理利用索引

    4.1、null列：创建索引时，索引列中有某列包含null值，这样的列将不会包含在索引中；亦即该索引不会对该列起作用。

    4.2、where条件中带is null/is not null的子句，优化器不会走索引；因为数据库中没有值等于NULL，甚至NULL 也不等于NULL；该列在索引中不会被创建条目。

    4.3、where条件的谓词列不能带有修饰，如调用了函数、进行了运算、连接了其它列或字符等；否则索引不起作用(除非使用基于函数的索引,不过这样的索引有很大局限性)，需单独放在条件表达式一侧。

    4.4、通配符%出现在词首时，索引无效，如：xx like '%hello%'; 反之xx like 'hello%' 则是索引有效的。

    4.5、Order by子句中非索引列，尤其是含运算表达式或函数的列，会降低查询速度。可以对非索引列添加索引或者重写子句，用索引列排序；同时避免函数表达式在子句中的运用；

    4.6、不等于运算符(<>、!=)：索引只能用于查找表中已存在的数据；每当在WHERE子句中使用不等于运算符时，其中对应列上的索引都将无法使用。不过这并不等于性能被降低，而是优化器基于(性能)成本的考量做的决定：在WHERE 子句中对一些值使用索引，而对部分值(不等式等)不使用。因为通常大型数据库分析表的同时都收集了表中数据分布的相关统计信息。

4.6.1、不等式的逻辑运算本身需返回绝大多数数据行，因此仍会产生一次全表扫描；    4.6.2、在应用程序开发、测试期间，应使用有代表性的测试数据，从而可以模拟产品环境中实际的数据值分布情况。    4.6.3、Oracle创建带分析索引：4.6.3.1、后跟COMPUTE STATISTICS子句：CREATE INDEX index_name ON table_name(column_name)COMPUTE STATISTICS;4.6.3.2、10g不立即起作用，需执行：ANALYZE TABLE table_name(column_name) COMPUTE STATISTICS;

二、改进sql语句，提高SQL性能

了解数据库特性，养成良好的书写sql习惯。一个优质的sql可以很大程度提高sql检索效率，而且有些场景下这些问题往往隐藏的很深，难以发现，需要在平时的工作中逐步摸索、慢慢养成。

1、比较不匹配的数据类型   

1.1、当Where条件中谓词列与比较值的数据类型不同时，数据库查询优化器(如Oracle)不但不会报错，反而会进行隐式数据转换，从而去匹配比较值的数据类型。比如：acco列为VARCHAR2类型，执行子句where acco= 99011 会转换成 where to_number(acco) = 99011。自然索引被抑制，产生全表扫描。

1.2、改进做法：传递对应数据类型的比较值以匹配谓词列(推崇)；结合整体业务需要，考虑重新定义合适的谓词列数据类型以匹配比较值。

2、select子句避免用*，否则会查询数据字典带来额外开销；sql语句用大写；尽量用Where子句替换Having子句，因为Having子句是在检索出来的结果集上再次过滤，过程中需要排序、总计，产生额外开销；多表查询时给表起别名可避免列歧义减少解析时间。

3、用(NOT)EXISTS代替(NOT)IN: 

有时候查询会包含子查询，用(NOT)IN相比用(NOT)EXISTS，查询的数据范围更宽；因为(NOT)EXISTS子句中可以带上主查询的关联条件，而(NOT) IN没有。如： 高效：SELECT * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  EXISTS (SELECT ‘X'  FROM DEPT  WHERE  DEPT.DEPTNO = EMP.DEPTNO  AND  LOC = ‘MELB'); 
低效：SELECT  * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = ‘MELB');

4、用EXISTS代替DISTINCT去重: 

在多表连接，尤其是一对多的表连接中，如果只需要‘一’表的数据，可以采用EXISTS子句替换主查询的多表连接，这样避免了主查询中出现重复的数据。因为DISTINCT会在查询出来的结果集基础上进行排序、去重，增加了额外的工作。如：低效：SELECT  DISTINCT  D.DEPT_NO,D.DEPT_NAME  FROM  DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO;
高效：SELECT  DEPT_NO,DEPT_NAME  FROM  DEPT D  WHERE  EXISTS ( SELECT ‘X'FROM  EMP E  WHERE E.DEPT_NO = D.DEPT_NO);

5、高效的删除重复记录sql
 DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID) FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO); 

6、用TRUNCATE替代DELETE以释放空间DELETE：DML操作，数据存放在回滚段(rollback segments)中，执行COMMIT则真正删除，反之回滚。TRUNCATE：DDL操作，会彻底删除数据，不存回滚段；资源调用少、执行快。彻底不用的表可以采用此命令。

7、在查询数据无差别的情况下，采用group by比distinct快，不论查询列是否添加索引。

三、sql语句优化--深层次

1、sql优化中，一个重要的基本原则就是“永远用小结果集驱动大的结果集”。

为什么？因为驱动结果集越大，意味着需要循环的次数越多，也就是说在被驱动结果集上面所需要执行的查询检索次数会越多。当然，此优化的前提条件是通过Join条件对各个表的每次访问的资源消耗差别不是太大。如果访问存在较大的差别的时候（一般都是因为索引的区别），我们就不能简单的通过结果集的大小来判断需要Join语句的驱动顺序，而是要通过比较循环次数和每次循环所需要的消耗的乘积的大小来得到如何驱动更优化。