切身实际总结的Sql性能总结.doc

SQL的编写技巧16目 录1.SQL语句要统一成大写32.在进行多个表连接时，FROM中的表的顺序要按照记录数由多到少的顺序来排列(Oracle)33.可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。（Oracle）34.涉及到多表检索时，明确地为每个字段指定表名35.对于经常使用的SQL语句（循环处理中使用的SQL等），可以通过预编译、绑定变量来提高性能46.对于索引列不要执行NULL值的检索47.对于索引列，不要使用NOT、!=、比较运算48.对于索引列不要使用函数和计算式49.尽可能将操作移至等号右边510.对于多键值索引，要按照索引的定义顺序来使用511.不要通过LIKE运算来执行中间一致或后方一致的检索512.去掉没有意义的GROUP BY、ORDER BY子语513.WHERE语句中不要使用NOT IN或者HAVING614.尽量避免较多地使用子查询615.避免不同类型的查询条件616.有使用IN或者EXISTS的语句吗？617.如果DBMS能够产生执行计划，验证一下是否是最优的SQL？618.避免全表扫描的查询方式719.当有多个索引可供选择时，使用的是DB设计者所希望的索引吗720.调整SQL后执行代价变得比原来更低了吗？721.在循环处理中，是否存在执行大量SQL语句的情形722.SELECT子句中避免使用*（Oracle）71. SQL语句要统一成大写原因：SQL语句转换成大写，可缩短些SQL的解析时间。通过统一成大写，可提高SQL的再利用率，缩短SQL解析时间。select * from a_tableSELECT * FROM A_TABLE2. 在进行多个表连接时，FROM中的表的顺序要按照记录数由多到少的顺序来排列(Oracle)原因：ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名3. 可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。（Oracle）原因：ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。SELECT FROM EMP E WHERE SAL 50000 AND JOB = MANAGER AND 25 (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);SELECT FROM EMP E WHERE 25 50000 AND JOB = MANAGER;4. 涉及到多表检索时，明确地为每个字段指定表名原因：通过在A_TABLE、B_TABLE中指定别名A、B,就可不再需要调查A_ITEM、B_ITEM、A_KEY、B_KEY是哪儿个表中的项目,从而缩短SQL解析时间。（为方便编码可以为表名指定名）SELECT A_ITEM FROM A_TABLE, B_TABLEWHERE A_KEY = B_KEY;SELECT A.A_ITEM, B.B_ITEM FROM A_TABLE A, B_TABLE BWHERE A.A_KEY = B.B_KEY;5. 对于经常使用的SQL语句（循环处理中使用的SQL等），可以通过预编译、绑定变量来提高性能原因：由于SQL可以被再利用，所以可缩短SQL解析时间。SELECT COUNT(*) FROM A_TABLE A WHERE A.KEY = 0;SELECT COUNT(*) FROM A_TABLE A WHERE A.KEY = 1;:VAR = 0;SELECT COUNT(*) FROM A_TABLE A WHERE A.KEY = :VAR;:VAR = 1;SELECT COUNT(*) FROM A_TABLE A WHERE A.KEY = :VAR;6. 对于索引列不要执行NULL值的检索原因：NULL检索是指查找没有的东西，所以如果不全部都调查的话无法判断出是有还是没有。索引只做成有的数据。调整成不执行NULL检索的SQL，或者修改表的定义SELECT * FROM A_TABLE AWHERE A.KEY IS NULL;7. 对于索引列，不要使用NOT、!=、比较运算原因：同5SELECT * FROM A_TABLE AWHERE A.KEY != 1;SELECT * FROM A_TABLE AWHERE A.KEY 1;SELECT * FROM A_TABLE AWHERE NOT EXIST ( SELECT * FROM B_TABLE B WHERE B.KEY = A.KEY);8. 对于索引列不要使用函数和计算式原因：索引将不能发挥索引的作用。SELECT * FROM A_TABLE A WHERE TO_CHAR(A.KEY, YYYYMMDD) = 20030101SELECT * FROM A_TABLE AWHERE A.KEY = TO_DATE(20030101,YYYYMMDD);SELECT * FROM A_TABLE AWHERE A.KEY BETWEEN TO_DATE(20030101000000, YYYYMMDDHH24MISS) AND TO_DATE(20030101235959, YYYYMMDDHH24MISS);9. 尽可能将操作移至等号右边原因：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。10. 对于多键值索引，要按照索引的定义顺序来使用原因：如果索引是建立在多个列上,只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。提示：需要修改索引的结合顺序或者重新设定复合索引。可能会对其他业务有影响，所以需要经过充分讨论后再作决定。 新追加的索引对更新/检索方面的性能都可能会有极大影响，更需要十分慎重。在按照A.KEY1 + A.KEY2 + A.KEY3来定义索引的情况下SELECT * FROM A_TABLE AWHERE A.KEY2 = KEY2 AND A.KEY3 = KEY3;SELECT * FROM A_TABLE AWHERE A.KEY1 = KEY1 AND A.KEY2 = KEY2;11. 不要通过LIKE运算来执行中间一致或后方一致的检索原因：中间或后方一致性检索时，索引是无效的。提示：重新分析一下看是否真的需要中间一致或后方一致检索，如果真的需要的话，可能需要重新规划一下业务流程。SELECT * FROM A_TABLE AWHERE A.KEY LIKE %XYZ;SELECT * FROM A_TABLE AWHERE A.KEY LIKE %EFG%;SELECT * FROM A_TABLE AWHERE A.KEY LIKE ABC%;12. 去掉没有意义的GROUP BY、ORDER BY子语原因：对于数据库来说ORDER BY、GROUP BY执行起来最耗费资源的处理。提示：使用ORDER BY时，需要确认真的需要进行排序处理吗？SELECT COUNT(*), A.ITEM FROM A_TABLE AWHERE A.ITEM = ABCGROUP BY A.ITEM;# A.ITEM只抽取1条数据，所以GROUP BY结果也是1行，没有意义SELECT COUNT(*) FROM A_TABLE AWHERE A.ITEM = ABC;13. WHERE语句中不要使用NOT IN或者HAVING原因：提示：考虑使用NOT EXISTS14. 尽量避免较多地使用子查询原因：提示：可用连接实现的场合，就尽量避免使用子查询。15. 避免不同类型的查询条件原因：默认的类型转会导致索引变成无效。SELECT * FROM A_TABLE A WHERE A.NUMBER_COL = 123;SELECT * FROM A_TABLE A WHERE A.NUMBER_COL = 123;16. 有使用IN或者EXISTS的语句吗？原因：提示：使用本身是没有问题的，但是需要确认。如果这样的SQL耗时的话，能否用EXISTS代替IN，或者用IN代替EXISTS，通过改写或许可以使SQL变快，视具体情况来定17. 如果DBMS能够产生执行计划，验证一下是否是最优的SQL？原因：提示：检查/调整的方法：在返回相同结果的SQL中，I/O代价小的才是好的SQL；同一SQL的首次执行和以后的执行，因为缓冲的原因会有差异，所以在同一条件下的比较很重要。18. 避免全表扫描的查询方式原因：如果样本很少的话（几件或几十件，一个DISK I/O就可以取得的那种程度），应该是没有问题的。但是一般来说要严禁整表扫描的。提示：仔细确认一下：SQL语句的写法没有问题吗？是否忘记了使用索引？索引的使用方法没有问题吗？19. 当有多个索引可供选择时，使用的是DB设计者所希望的索引吗原因：使用能最大限度（效率高）地提取到数据的索引。一般来说，能够抽取原始数据的1/50以内的索引，才是效率高的。如果不具备这样的条件的话，或许全表扫描也许会很快。20. 调整SQL后执行代价变得比原来更低了吗？原因：对于执行代价的绝对值是没有意义；变更前后的差才是最重要的。提示：能否考虑循环处理之外的方法，比如是否能通过批处理来实现相同的功能。21. 在循环处理中，是否存在执行大量SQL语句的情形原因：即使单个SQL语句的处理时间短，但这个SQL语句因循环处理而被发行了几万次，几十万次的话 循环处理中的SQL语句，通常要注意循环次数（即使不准确但能够大致估算出来也是重要的）。22. SELECT子句中避免使用*（Oracle）原因：ORACLE在解析的过程中, 会将* 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。SQL使用技巧23. 高效的sql语句Sql优化方法RBO(rule-based optimizer)CBO驱动表1）2张行数不一致的表连接 表TAB1行数：16,384行 表TAB2行数：1行SELECT COUNT(*) FROM TAB2, TAB1;SELECT COUNT(*) FROM TAB1, TAB2;2）3张表连接 SELECT * FROM EMP E, LOC L, CAT C WHERE E.emp_noBETWEEN 1000 AND 2000 AND E.cat_no= C.cat_no AND E.locn= L.locn;SELECT * FROM LOC L, CAT C, EMP E WHERE E.emp_noBETWEEN 1000 AND 2000 AND E.cat_no= C.cat_no AND E.locn= L.locn;Where语句顺序的效率1) 使用索引引起的where语句效率使用AND语句时行数多的放在前面SELECT * FROM emp E WHERE emp_sal 50000 AND emp_type = MANAGER AND 25 (SELECT COUNT(*) FROM EMP WHERE emp_mgr = E.emp_no);SELECT * FROM emp E WHERE 25 50000 AND emp_type = MANAGER;使用OR语句时，行数多的放在后面SELECT * FROM emp E WHERE 25 50000 AND emp_type = MANAGER);SELECT * FROM emp E WHERE (emp_sal 50000 AND emp_type = MANAGER) OR 25 0; （3）|SELECT acc_name, trans_date, amount FROM transaction WHERE acc_name | acc_type = AMEXA; SELECT acc_name, trans_date, amount FROM transaction WHERE acc_name = AMEX AND acc_type = A;（4）运算SELECT acc_name, trans_date, amount FROM transaction WHERE amount + 3000 5000; SELECT acc_name, trans_date, amount FROM transaction WHERE amount ( SELECT MIN(X.rowid) FROM emp X WHERE X.emp_no = E.emp_no );表的行计数SELECT COUNT(有索引的列) FROM TRANS;SELECT COUNT(*) FROM TRANS;SELECT COUNT(1) FROM TRANS;用WHERE语句替换HAVING语句的使用SELECT region, AVG(loc_size) FROM location GROUP BY region HAVING region != SYDNEY AND region != PERTH;SELECT region, AVG(loc_size) FROM location WHERE region != SYDNEY AND region != PERTH; GROUP BY region使用表连接替代EXISTS使用SELECT emp_name FROM emp WHERE (emp_cat, sal_range) = (SELECT MAX(category), MAX(sal_range) FROM emp_categories) AND emp_dept = 0020;SELECT emp_name FROM emp WHERE emp_cat = (SELECT MAX(category) FROM emp_categories) AND sal_range = (SELECT MAX(sal_range) FROM emp_categories) AND emp_dept = 0020;使用EXISTS替代表连接SELECT . FROM emp E WHERE EXISTS ( SELECT X FROM deptWHERE dept_no = E.dept_no AND dept_cat = A) AND E.emp_type = MANAGER;使用EXISTS代替DISTINCT语句SELECT DISTINCT dept_no, 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);使用NOT EXISTS代替NOT IN语句 SELECT . FROM empWHERE dept_no NOT IN ( SELECT dept_no FROM dept WHERE dept_cat = A);SELECT . FROM emp E WHERE NOT EXISTS ( SELECT X FROM dept WHERE dept_no = E.dept_noA