数据库调优

SQL语句的调整原则

1、1、尽量使用索引。试比较下面两条SQL语句： 
语句A：SELECT dname, deptno FROM dept WHERE deptno NOT IN  
(SELECT deptno FROM emp); 
语句B：SELECT dname, deptno FROM dept WHERE NOT EXISTS 
(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno); 
这两条查询语句实现的结果是相同的，但是执行语句A的时候，ORACLE会对整个emp表进行扫描，没有使用建立在emp表上的deptno索引，执行语句B的时候，由于在子查询中使用了联合查询，ORACLE只是对emp表进行的部分数据扫描，并利用了deptno列的索引，所以语句B的效率要比语句A的效率高一些。 

2、2、选择联合查询的联合次序。考虑下面的例子： 
SELECT stuff FROM taba a, tabb b, tabc c 
WHERE a.acol between :alow and :ahigh 
AND b.bcol between :blow and :bhigh 
AND c.ccol between :clow and :chigh 
AND a.key1 = b.key1 
AMD a.key2 = c.key2;

3、在子查询中慎重使用IN或者NOT IN语句，使用where (NOT) exists的效果要好的多。

  　select num from a where num in(select num from b) 

　　用下面的语句替换： 

　　select num from a where exists(select 1 from b where num=a.num)

4、慎重使用视图的联合查询，尤其是比较复杂的视图之间的联合查询。一般对视图的查询最好都分解为对数据表的直接查询效果要好一些。

5、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： 

　　select id from t where num is null 

　　可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： 

　　select id from t where num=0
6、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

7、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： 

　　select id from t where num=10 or num=20 

　　可以这样查询： 

　　select id from t where num=10 

　　union all 

　　select id from t where num=20
8、下面的查询也将导致全表扫描： 

　　select id from t where name like '%abc%' 

　　若要提高效率，可以考虑全文检索。 （mysql全文检索）

9、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： 

　　select id from t where num/2=100 

　　应改为: 

　　select id from t where num=100*2

10、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

11、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

数据库管理员还可以通过下述语句： 
SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA;   oracle
查看低效率的SQL语句，优化这些语句也有助于提高CPU的利用率。
12、使用INNER JOIN 而不是WHERE来创建连接

一些SQL开发人员更喜欢使用WHERE来做join，比如：

SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDateFROM Customers, SalesWHERE Customers.CustomerID = Sales.CustomerID

这个类型join实际上创建时笛卡尔连接，也被称为笛卡尔积或 CROSS JOIN。 在笛卡尔连接中，所有可能的组合都会被创建出来。在上面的例子中，如果有1000顾客和1000条销售记录，这个查询会先产生1000000个结果，然后通过正确的 CustomerID过滤出1000条记录。 这是一种低效利用数据库资源，数据库多做100倍的工作。 在大型数据库中，笛卡尔连接是一个大问题，对两个大表的笛卡尔积会创建数10亿或万亿的记录。

为了避免创建笛卡尔积，应该使用INNER JOIN ：

SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDateFROM Customers   INNER JOIN Sales   ON Customers.CustomerID = Sales.CustomerID

这样数据库就只产生等于CustomerID 的1000条目标结果。

有些数据库系统会识别出 WHERE连接并自动转换为 INNER JOIN。在这些数据库系统中，WHERE 连接与INNER JOIN 就没有性能差异。但是， INNER JOIN 是所有数据库都能识别的，因此DBA会建议在你的环境中使用它。

13、使用WHERE而不是使用 HAVING 来筛选

类似于上面提到的概念， 高效查询的目的就是只从数据库得到需要的记录。 每一个SQL 指令，HAVING 语句是放在WHERE 语句后面来运算的。 如果想基于条件来过滤记录，无疑WHERE 会更佳高效。

例如， 我们假定在2016年有200销售记录，我们想查询出2016年的客户销售数。

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)FROM Customers   INNER JOIN Sales   ON Customers.CustomerID = Sales.CustomerIDGROUP BY Customers.CustomerID, Customers.NameHAVING Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#

这个查询会从销售表中拉取1000条记录，然后过滤得到2016年的200条记录，最后才是对这个数据集计数。

相比而言, WHERE 就直接限制了拉取的数据记录数：

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)FROM Customers  INNER JOIN Sales  ON Customers.CustomerID = Sales.CustomerIDWHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#GROUP BY Customers.CustomerID, Customers.Name

本查询只拉取2016年的200条记录，然后对这个数据集计数。这样就不必使用 HAVING 。

HAVING 只能应用在聚合字段上。在上面的查询中，我们可以添加一个显示消费记录大于5的客户的条件。

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)FROM Customers   INNER JOIN Sales   ON Customers.CustomerID = Sales.CustomerIDWHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#GROUP BY Customers.CustomerID, Customers.NameHAVING Count(Sales.SalesID) > 5

续MySQL 性能优化的经验分享