SQL编写规范(数据库操作规范)

8.用执行计划分析SQL性能 　　 　　EXPLAIN PLAN是一个很好的分析SQL语句的工具，它可以在不执行SQL的情况下分析语句 　　 　　通过分析，我们就可以知道ORACLE是怎样连接表，使用什么方式扫描表（索引扫描或全表扫描），以及使用到的索引名称 　　 　　按照从里到外，从上到下的次序解读分析的结果 　　 　　EXPLAIN PLAN的分析结果是用缩进的格式排列的，最内部的操作将最先被解读，如果两个操作处于同一层中，带有最小操作号的将首先被执行 　　 　　目前许多第三方的工具如PLSQL Developer和TOAD等都提供了极其方便的EXPLAIN PLAN工具 　　 　　PG需要将自己添加的查询SQL文记入log，然后在EXPLAIN PLAN中进行分析，尽量减少全表扫描 　　 　　ORACLE SQL性能优化系列 　　 　　1.选择最有效率的表名顺序(只在基于规则的优化器中有效) 　　 　　ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表driving table）将被最先处理 　　 　　在FROM子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表 　　 　　当ORACLE处理多个表时，会运用排序及合并的方式连接它们 　　 　　首先，扫描第一个表（FROM子句中最后的那个表)并对记录进行排序； 　　 　　然后扫描第二个表（FROM子句中最后第二个表)； 　　 　　最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并 　　 　　例如: 　　 　　表 TAB1 16,384 条记录 　　 　　表 TAB2 5 条记录 　　 　　选择TAB2作为基础表 (最好的方法) 　　 　　select count(*) from tab1,tab2 执行时间0.96秒 　　 　　选择TAB2作为基础表 (不佳的方法) 　　 　　select count(*) from tab2,tab1 执行时间26.09秒 　　 　　如果有3个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表 　　 　　例如: 　　EMP表描述了LOCATION表和CATEGORY表的交集 　　SELECT * 　　FROM LOCATION L, 　　CATEGORY C, 　　EMP E 　　WHERE E.EMP_NO BETWEEN 1000 AND 2000 　　AND E.CAT_NO = C.CAT_NO 　　AND E.LOCN = L.LOCN 　　 　　将比下列SQL更有效率 　　SELECT * 　　FROM EMP E , 　　LOCATION L , 　　CATEGORY C 　　WHERE E.CAT_NO = C.CAT_NO 　　AND E.LOCN = L.LOCN 　　AND E.EMP_NO BETWEEN 1000 AND 2000 　　 　　2.WHERE子句中的连接顺序 　　 　　ORACLE采用自下而上的顺序解析WHERE子句 　　 　　根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾 　　 　　例如： 　　(低效,执行时间156.3秒) 　　SELECT * 　　FROM EMP E 　　WHERE SAL > 50000 　　AND JOB = 'MANAGER' 　　AND 25 < (SELECT COUNT(*) FROM EMP WHERE 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子句中避免使用'*' 　　 　　当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用'*'是一个方便的方法，不幸的是，这是一个非常低效的方法 　　 　　实际上，ORACLE在解析的过程中，会将'*'依次转换成所有的列名 　　 　　这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间 　　 　　4.减少访问数据库的次数 SQL编写规范是数据库操作中至关重要的一环，遵循良好的SQL编写规范可以提高查询效率，优化数据库性能，降低资源消耗。以下是一些关键知识点： 1. **执行计划分析SQL性能**： 使用`EXPLAIN PLAN`工具可以帮助分析SQL语句的执行策略，包括表的连接顺序、扫描方式（如索引扫描或全表扫描）以及所使用的索引。在解读分析结果时，应从内到外，从上到下理解各个操作步骤。现代数据库管理工具如PLSQL Developer和TOAD等提供了更直观的EXPLAIN PLAN功能，便于开发人员理解和优化SQL。 2. **选择表的处理顺序**： 在FROM子句中，表的顺序影响查询性能。Oracle通常按照从右到左的顺序处理，因此应将记录条数较少的表放在以减少全表扫描。对于多表连接，基础表（驱动表）的选择尤为重要，通常是与其他表关联最多的表，即交叉表。 3. **WHERE子句的连接顺序**： WHERE子句中的条件解析遵循自下而上的顺序。为了优化性能，应先处理能过滤大量记录的条件，再进行表之间的连接。这样可以减少不必要的数据处理。 4. **避免在SELECT子句中使用'*'**： 使用`*`表示所有列虽然方便，但效率低下。ORACLE需要解析数据字典获取所有列名，增加了额外的时间开销。因此，最好明确列出所需的列。 5. **减少对数据库的访问次数**： 减少数据库访问次数可以显著提升性能。这可以通过合并SQL查询，使用存储过程，或者批量处理来实现。 6. **SQL书写格式规范**： - **缩进**：存储过程的缩进通常为8个空格，Java源代码中的SQL字符串不应有缩进。 - **换行**：遵循特定的格式规则，如SELECT、FROM、WHERE等子句单独一行，多个条件或表项分别占行，并保持适当的缩进。Java源码中的SQL字符串同样需要整洁，但不需要缩进。 遵循这些规范，不仅有助于编写清晰易读的SQL代码，还能确保代码的执行效率，从而提升整体系统性能。在实际开发中，应不断学习并实践这些最佳实践，以优化数据库操作，提高应用程序的运行速度。