sql 性能优化 项目实战篇

数据库结构
1、需要有配套的文档描述表、表字段、视图、函数和存储过程的含义与作用。
【缘由】一字顶千言
2、应该有自动化工具能根据文档自动生成建表、建索引的脚本
【缘由】维护人员修改文档，利用工具保证脚本和文档是同步的。就等于注释与代码的关系。只是由于数据库安全原因，不能暴露字段解释。
3、名称长度不要超过30字符，名称中只使用大小写字母、数字和下划线，名称第一个字符是字母。
【缘由】SQL-99和MS SQL限制是128，但oracle是30，因此取30。方便在不同数据库中移植+
【讨论】是否应该针对类型取前缀？
表和视图不应该增加前缀，因为视图和表会相互转换
函数和存储过程应该增加前缀，用于告知开发人员是一个逻辑操作而不是一个实体。
4、将列按照逻辑顺序排列。
【顺序】
常用查询的键
变化很少的非变长列
变化很少的变长列
经常更新的列
5、慎用主键。
【缘由】主键是同时具有唯一索引与唯一约束的限制，而且建立表后不能删除。大数据量变动时，维护主键也是一件很耗时的事情。
【讨论】什么时候使用主键，应该使用什么做为主键？
6、慎用索引
【缘由】使用正确的索引固然能提高查询效率，但也带来数据维护的代价。
        大数据量批量增加与删除时，建议先让索引不可用，导入或删除数据后再重建索引。
【讨论】什么时候使用索引，应该使用什么类型的索引？

SQL语句
1、SQL语句的关键字、表、视图名称采用大写字母编写，非加密字段名称采用小写。
【缘由】oracle解释SQL时，先把小写字母转换为大写字母再执行。
        小写字母容易阅读，字段名称容易阅读。
        使用编辑器看代码时，关键字高亮，一眼就看到了；表等大写，也容易识别；小写更方便理解字段含义。
2、采用一致的缩进与换行；
   1)SELECT、FROM、WHERE、GROUP BY、ORDER BY、[INNER|LEFT|RIGHT|OUTER] JOIN等子句另起一行编写，首字母列对齐;
   2)SQL语句字符数少于80时，可以写在同一行；
   3)SELECT子句多于一项时，每一项单独占一行，在对应FROM的基础上向右缩进8个空格；
   4)FROM子句若是表或视图，则与FROM同行写；否则是查询子句，应该另起一行，向右缩进4个空格；
   5)WHERE子句的条件如果有多项，每一个条件占一行，以AND开头，且无缩进；
   6)SQL内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔
   7)使用连接时，需要使用小写字母的别名，且字段应该使用别名说明是哪个表的字段
  一个SQL语句中间不允许出现空行和注释
【缘由】方便查看sql语句关系。一行一个字段方便定位那个字段有问题。
【示例】
SELECT  a.c0001,
        a.c0002,
        a.c0003,
        SUM(b.c0004) AS SUMVALUE,
        MAX(b.c0005) AS MAXVALUE
FROM
(
    SELECT C0001, C0002, C0003, COUNT(C0004) AS COUNTC0004
    FROM TABLEA
    GROUP BY C0001, C0002, C0003
) AS a
INNER JOIN TABLEB AS b ON a.c0001 = b.c0001 AND a.c0002 = b.c0002 AND a.c0003 = b.c0003
WHERE COUNTC0004 > 10
GROUP BY a.c0001, a.c0002, a.c0003
ORDER BY a.c0001, a.c0002, a.c0003;
3、语句末尾以";"结束。
【注】C++代码中SQL不能加";"
【缘由】oracle以";"结束，SQL Server以";"结束对语句没有影响。
4、代码拼接SQL语句时，首个字符为空格，防止拼接SQL错误
【示例】
srting sSQL = "SELECT c0001, c0002, c0003";
sSQL += " FROM TABLEA";
5、不等号统一使用"<>"
【缘由】统一格式。虽然某些DBMS可以使用"!="，但建议使用一个通用的方法。
6、使用显示的连接，不要使用WHERE条件的连接
【示例】
--correct
SELECT a.col1, a.col2, b.col1, b.col2
FROM TABLEA AS a
INNER JOIN TABLEB AS b ON a.col1 = b.col1
WHERE a.col1 = 1 AND b.col2 = 2
--avoid
SELECT a.col1, a.col2, b.col1, b.col2
FROM TABLEA AS a, TABLEB AS b
WHERE a.col1 = b.col1 AND a.col1 = 1 AND b.col2 = 2
7、使用左连接，避免使用右连接
--correct
SELECT a.col1, a.col2, b.col1, b.col2
FROM TABLEA AS a
LEFT JOIN TABLEB AS b ON a.col1 = b.col1
WHERE a.col1 = 1 AND b.col2 = 2
--avoid
SELECT a.col1, a.col2, b.col1, b.col2
FROM TABLEB AS b
RIGHT JOIN TABLEA AS a ON a.col1 = b.col1
WHERE a.col1 = 1 AND b.col2 = 2
8、避免使用SELECT *
【缘由】DBMS在解析的过程中，会将 '*' 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间
9、避免在WHERE子句的左侧条件使用计算
【缘由】若没有设置函数索引，SQL将不使用索引
10、避免使用隐式类型转换
【缘由】使用显示转换既避免特殊数据转换出错又指明两者关系
当比较不同数据类型的数据时，ORACLE自动对列进行简单的类型转换。
假设EMPNO是一个数值类型的索引列 .
SELECT … FROM EMP WHERE EMPNO = '123'
实际上，经过ORACLE类型转换，语句转化为
SELECT … FROM EMP WHERE EMPNO = TO_NUMBER('123')
幸运的是，类型转换没有发生在索引列上 , 索引的用途没有被改变。
现在 , 假设 EMP_TYPE 是一个字符类型的索引列。
SELECT … FROM EMP WHERE EMP_TYPE = 123
这个语句被 ORACLE 转换为 :
SELECT … FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123
11、避免一次性删除过多数据
【缘由】删除需要占用UNDO表空间，删除大数据会长时间锁定表，并且需要更长的时间。
【示例】
--recommend SQL Server方法
--loop 100 time
DELETE BigTable WHERE ID BETWEEN 1 AND @LoopVariable (100,200,...,10000)
--recommend Oracle方法

--avoid
DELETE BigTable WHERE ID BETWEEN 1 AND 10000
12、删除全表时，使用TRUNCATE TABLE而不是DELETE FROM
【缘由】TRUNCATE TABLE不使用UNDO表空间。
13、减少访问数据库的次数
【示例】
--correct
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
--avoid
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = (SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604)
AND DB_VER = (SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
14、使用EXISTS代替IN，使用NOT EXISTS代替NOT IN
【缘由】EXISTS找符合条件的记录就返回，而IN需要获取所有记录才能返回。使用EXISTS时可用上col1列上的索引。
【示例】
--correct
SELECT col1
FROM TABLEA AS a
WHERE col2 > 0
AND EXISTS (SELECT col1 FROM TABLEB AS b WHERE a.col1 = b.col1 AND b.col2 = 2011)
--avoid
SELECT col1
FROM TABLEA
WHERE col2 > 0
AND col1 IN (SELECT col1 FROM TABLEB WHERE col2 = 2011)
【注】当IN中条件为常量时，使用IN是很高效的。IN (1, 2)
15、确认联合的记录没有重复时，使用UNION ALL代替UNION
【缘由】UNION比UNION ALL多了一个删除重复记录操作，会对两个结果集分别排序。
16、需要频繁操作的类似SELECT语句，使用绑定变量方式提高效率。
【缘由】
在oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.
一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行）， 而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。
而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。
【示例】
普通sql语句：
SELECT fname, lname, pcode FROM TABLEA WHERE id = 674;
SELECT fname, lname, pcode FROM TABLEA WHERE id = 234;
SELECT fname, lname, pcode FROM TABLEA WHERE id = 332;
含绑定变量的sql 语句：
SELECT fname, lname, pcode FROM TABLEA WHERE id = :TABLEA_ID;
Sql*plus 中使用绑定变量：
sql> VARIABLE x NUMBER;
sql> EXEC := 123;
sql> SELECT fname, lname, pcode FROM TABLEA WHERE id =:x;

函数
1、函数中不应该出现INSERT、UPDATE、DELETE等影响表数据的语句
2、函数中最多只有一个SELECT语句。
【讨论】什么时候使用函数？
我一般不使用函数，感觉效率低。若函数能像C++的inline函数一样展开，我就使用。

存储过程
1、若调用的接口程序能检测存储过程执行是否成功，出错时能反馈数据库提供的错误信息，该系统的存储过程可以不需要出参
   若外部调用者未能知道存储过程执行是否成功，则需要两个参数，Result参数表明存储过程执行结果，LogErrDesc是出错时数据库反馈的信息
【缘由】一切操作都应该知道执行结果
【示例】
-- Oracle例子，参数名兼容NetMAX-GU已有实现
CREATE OR REPLACE PROCEDURE P_TEST(Result OUT NUMBER, LogErrDesc OUT VARCHAR2) IS
BEGIN
    Result := -1;

    -- 实际操作语句

    Result := 0;
    COMMIT;

    -- 异常处理
    EXCEPTION WHEN OTHERS THEN
        ROLLBACK;
        Result := -1;
        LogErrDesc := substr(dbms_utility.format_error_stack,1,500);
        COMMIT;
        RETURN;
END P_TEST;
/
2、一个存储过程只完成一个事务过程
【缘由】方便利用DBA工具或外部程序及时记录每个操作的过程，这样也很好地做进度监控，出错定位也比较容易
3、避免使用游标。
【缘由】效率慢。详细见以SQL方式思考
4、当oracle DBI程序没有设置存储过程提交时，存储过程内需要至少有一个"COMMIT;"语句
【缘由】Oracle不提倡自动提交语句，因此，操作结束后需要提交
5、SQL语句使用并行时，需要立刻提交，否则查询出错。

以SQL方式思考
1、以集合和逻辑的方式思考问题，而不是顺序和过程化的方式思考。
   使用集合图而不是方框和箭头
【缘由】实现是否高效，七分靠逻辑，三分靠SQL写法。
2、SQL代码编写是否高效，在于查看查询计划和实际测试效果。
【缘由】目前大部分数据库使用CBO方式，SQL语句好坏取决于数据库如何根据统计信息选择合适的查询计划。