SQL语句查询优化

数据库优化包括查询优化，索引优化，库表结构优化

一，获取有性能问题SQL的三种方法

通过用户反馈获取存在性能问题的SQL，通过慢查询日志获取存在性能问题的SQL，实时获取存在性能问题的SQL.

二，如何优化Not in 和 <> 查询

使用汇总表优化查询

汇总表就是提前把要统计的数据进行汇总并记录到表中以备后续的查询使用

下面以显示每一个商品的评论数为例：

三，数据库优化

1，检查索引：在SQL语句中WHERE和JOIN部分中用到的所有字段上，都应该加上索引。

2，限制工作数据集的大小：检查SELECT语句中用到的表，查看是否可以应用WHERE子句进行过滤，当你的查询语句带有子查询时，注意在子查询的内部语句上使用过滤，而不是在外部语句上。

3，只选择你需要的字段：额外的字段通常会增加返回数据的纹理，从而导致更多的数据被返回到SQL客户端

4，移除不必要的表

5，移除外部连接查询

6，删除WHERE和JOIN子句中的计算部分：

四，优化数据库的思想

1，关键字段建立索引  2，使用存储过程，使SQL变得灵活高效  3，备份数据库和清理垃圾数据  4，SQL语句语法的优化  5，清理删除日志

五，SQL语句优化的原则

1，选择最有效率的表名顺序：在from子句中包含多个表的情况下，你必须选择记录条数最少的表做为基础表。

基础表是指被最先访问的表（通常以全表扫描的方式被访问）

如果有三个以上的表连接查询，那就需要选择交叉表作为基础表。

交叉表是指那个被其他表所引用的表。

例如:

EMP表描述了LOCATION表和CATEGORY表的交集.

SELECT * FROM LOCATION L,CATEGORYC,EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO=C.CAT_NOAND E.LOCN=L.LOCN

下列SQL更有效率

SELECT * FROM EMP E,LOCATION L,CATEGORYC WHERE E.CAT_NO=C.CAT_NOAND E.LOCN=L.LOCNAND E.EMP_NO BETWEEN 1000 AND 2000

2，WHERE子句中的连接顺序

采用自下而上的顺序解析WHERE语句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾，同时在链接的表中能过滤的就应该先过滤。

例如:(低效,执行时间156.3秒)

SELECT...FROM EMP E

WHERE SAL>50000

AND JOB=‘MANAGER’

AND 25<(SELECT COUNT(*)FROM EMPWHERE MGR=E.EMPNO);

(高效,执行时间10.6秒)

SELECT...FROM EMP E

WHERE 25<(SELECT COUNT(*)FROM EMP

WHERE MGR=E.EMPNO)

AND SAL>50000

AND JOB=‘MANAGER’;

3，SELECT子句中避免使用“ * ”

4，尽量多使用COMMIT：程序性能不仅得到提升，需求也会因为COMMIT释放的资源而减少，

COMMIT所释放的资源：回滚段上用于恢复数据的信息，被程序语句获得的锁

5，计算计数条数：和一般的观点相反，count(*)比count(1)稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的。

6，用WHERE子句替换HAVING子句：避免使用HAVING子句，HAVING只会在检索出索引记录之后才会对结果集进行过滤，这个处理需要总计，排序等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。

例如:低效:

SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION!=‘SYDNEY’AND REGION!=‘PERTH’

高效sql优化

SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION!=‘SYDNEY’AND REGION!=‘PERTH’ GROUP BY REGION；

HAVING中的条件一般用于对一些集合函数的比较,如COUNT()等等.除此而外,一般的条件应该写在WHERE子句中

7，通过内部函数提高SQL效率

8，使用表的别名（Alias）：当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上，这样一来，就可以减少解析的时间，并减少那些由Column歧义引起的语法错误。

9，用 EXISTS 代替 IN,用 NOT EXISTS 替代 NOT IN

10，用表连接替换EXISTS

11，用索引提高效率：索引可以提高检索数据的效率，但索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改，这意味着每条记录的INSERT,UPDATE,DELETE将为此多付出4，5次的磁盘IO，因为索引需要额外的存储空间来处理，那些不必要的索引反而会使查询反应时间变慢。

12，避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描举例:

低效：SELECT...FROM DEPTWHERE SAL*12>25000;

高效:   SELECT...FROM DEPTWHERE SAL>25000/12;

非常实用的规则，请务必牢记

13，自动选择索引

14，避免在索引列上使用NOT

15，用>=替代>

16，用UNION替换OR(适用于索引列)，通常对索引列使用OR会造成全表扫描，注意，以上规则只争对多个索引列有效，如果有Column没有被索引，查询效率可能会因为你没有选择OR而降低。

17，使用IN来替换OR

18，避免在索引列上使用IS NULL和IS NOT NULL（避免在索引中使用任何可以为空的列）

19，总是使用索引的第一个列：如果索引是建立在多个列上，只有在它的第一个列被WHERE子句引用时，优化器才会选择使用该索引。

20，用UNION ALL替换UNION，如果有可能的话。

当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION ALL的方式被合并，然后在输出最后结果前进行排序。如果用UNION ALL替代UNION，这种排序就不是必要了，效率就会因此得到提高。UNION将对结果集合进行排序，这个操作会使用到SORT_AREA_SIZE这块内存。

UNION：如果查询出来的结果中有重复记录，那么就去重，英文称之为‘distinct'

UNION ALL：显示所有符合条件的记录，重复也保留。

21，ORDER BY 子句只在两种严格的条件下使用索引

ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序；

ORDER BY中所有的列必须定义为非空

22，避免改变索引列的类型

当比较不同数据类型的数据时,自动对列进行简单的类型转换.假设EMPNO是一个数值类型的索引列.SELECT...FROM EMP WHERE EMPNO=‘123’实际上,经过ORACLE类型转换,语句转化为:

SELECT...FROM EMP WHERE EMPNO=TO_NUMBER(‘123’)幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.现在,假设EMP_TYPE是一个字符类型的索引列.

SELECT ...FROM EMPWHERE EMP_TYPE = 123这个语句被ORACLE转换为:

SELECT ...FROM EMPWHERE TO_NUMBER(EMP_TYPE)=123因为内部发生的类型转换,这个索引将不会被用到!为了避免ORACLE对你的SQL进行隐式的类型转换,最好把类型转换用显式表现出来.注意当字符和数值比较时,ORACLE会优先转换字符型到数字类型.

23，需要担心的WHERE子句：“!=” 将不使用索引，记住，索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中，“+” 是数字函数，就像其他数字函数那样，停用了索引。

24，避免使用耗费资源的操作：带有DISTINCT, UNION, MINUS, INTERSECT, ORDER BY 的SQL语句会启动SQL引擎，执行耗费资源的排序（SORT）功能，DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。