SQL优化总结 博客分类： 工作 SQL

1.使用where子句过滤行
这个是最最基础的优化了。where子句中应该避免使用函数，否则会增加执行时间。
SELECT * FROM USER;
SELECT * FROM USER WHERE USER_ID = 10000；


2.使用表连接而不是多个查询
通常执行一个查询的效率会比执行两个查询的效率高。查询中应避免复杂的视图，因为这样会导致查询中首先执行的是视图，然后才是实际查询。相反地，使用表而不是试图编写查询。
SELECT USER_ID FROM USER WHERE USER_TYPE = 2;
==> 1
SELECT TEACHER_ID FROM TEACHER WHERE USER_ID = 1;

SELECT T.TEACHER_ID FROM TEACHER T,USER U WHERE U.USER_TYPE = 2 AND T.USER_ID = U.USER_ID;


3.执行连接时使用完全限定列引用
在查询中包含表别名，并为查询所引用的每列显式指定合适的别名。因为对列的所有引用包含了表别名，数据库不需要浪费时间去搜索所有表，从而减少了执行时间。
SELECT U.USER_ID,TEACHER_ID FROM TEACHER T,USER U WHERE T.USER_ID = U.USER_ID;

SELECT U.USER_ID,T.TEACHER_ID FROM TEACHER T,USER U WHERE T.USER_ID = U.USER_ID;


4使用case查询而不是多个查询
当需要对一个表的相同行执行许多计算时使用CASE表达式而不是多个查询。
SELECT COUNT(*) FROM USER WHERE AGE < 10;
==>30
SELECT COUNT(*) FROM USER WHERE AGE BETWEEN 10 AND 13;
==>20
SELECT COUNT(*) FROM USER WHERE AGE > 13;
==>10

SELECT
COUNT(CASE WHEN AGE < 10 THEN 1 ELSE NULL END) LOW,
COUNT(CASE WHEN AGE BETWEEN 10 AND 13 THEN 1 ELSE NULL END) MID,
COUNT(CASE WHERN WHERE AGE > 13 THEN 1 ELSE NULL) HIGH
FROM USER;

CASE表达式中可以使用重叠的范围和不同的函数。


5.添加表索引
索引的缺点是当向表中添加一行时，需要附加时间来更改新行的索引。一般地，一个包含许多行的表检索少数行时，只需要为一列建立一个索引。一条成功的经验是，当单个查询检索的行数不大于表总行数的10%时，建立索引是有用的。


6.使用WHERE而不是HAVING
WHERE子句用户过滤行；HAVING子句用于过滤行组。因为行被分组之后，HAVING才可以过滤行组，这需要一定的时间，所以应该经尽量使用WHERE子句过滤行。
SELECT PRODUCT_TYPE_ID,AVG(PRICE) FROM PRODUCTS GOURP BY PRODUCT_TYPE_ID HAVING PRODUCT_TYPE_ID IN (1,2);

SELECT PRODUCT_TYPE_ID,AVG(PRICE) FROM PRODUCTS WHERE PRODUCT_TYPE IN (1,2) GROUP BY PRODUCT_TYPE_ID;


7.使用UNION ALL而不是UNION
UNION ALL用于获得两个查询检索到的所有行，包括重复行；UNION用于获得查询检索到的所有不重复行。应为UNION删除了重复行，这需要一定时间，所以尽量使用UNION ALL
SELECT PRODUCT_ID,PRODUCT_TYPE_ID,NAME FROM PRODUCTS UNION SELECT PRD_ID,PRD_TYPE_ID,NAME FORM MORE_PRODUCTS;

SELECT PRODUCT_ID,PRODUCT_TYPE_ID,NAME FROM PRODUCTS UNION ALL SELECT PRD_ID,PRD_TYPE_ID,NAME FROM MORE+PRODUCTS;


8.使用EXISTS而不是IN
IN用于检查一个值是否包含在列表中。EXISTS与IN不同：EXISTS只检查行的存在性，而IN检查实际的值。在子查询中，EXISTS提供的性能通常比IN提供的性能要好。

9.使用EXISTS而不是DISTINCT
DISTINCT用于禁止重复行的现实；EXISTS用于检查子查询返回的行的存在性。因为DISTINCT在禁止重复行之前要排序检索到的行。