`

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语句好坏取决于数据库如何根据统计信息选择合适的查询计划。
分享到:
评论

相关推荐

    sql性能优化及实例

    减少数据访问,返回更少数据,减少交互次数,减少服务器CPU开销,利用更多资源。注意:这个是对《sql性能优化分享》的后期修改与补充。下载这个最新的就下载老的了。别下载重复了!!!

    sql 性能优化文档下载

    提供的文档如"ORACLE性能优化.doc"、"oracle_sql性能优化.doc"、"ORACLE_SQL性能优化系列.pdf"、"Oracle_SQL性能优化.ppt"等,应包含更多具体案例和实战经验,帮助读者深入理解并掌握这些技巧。

    SQL Server性能调优实战

    SQL Server性能调优实战 带索引书签目录............

    SQL Server性能优化实战.pdf

    本文介绍SQL Server性能优化的实战经验,通过分析作者在湖北中医药大学附属襄阳医院工作时遇到的真实案例,详细阐述了SQL Server性能问题的诊断与解决方法。知识点涵盖从SQL Server性能优化目标、影响性能的因素、...

    实战国产达梦数据库SQL性能优化

    实战国产达梦数据库SQL性能优化 达梦数据库是国产数据库管理系统,具有高性能、安全、可靠等特点。然而,在实际应用中,我们经常会遇到性能瓶颈的问题,例如SQL查询效率低、CPU使用率高、IO_utilization高等问题。...

    SQL性能优化PPT.rar

    在SQL性能优化领域,理解和掌握相关知识是...每一份PPT都可能包含实例分析、最佳实践和实战经验,帮助我们更好地理解和掌握SQL性能优化的精髓。在阅读过程中,记得结合自己的项目经验,理论联系实际,这样效果会更佳。

    北大青鸟 ACCP5.0 S1 SQL Server 项目实战

    【北大青鸟ACCP5.0 S1 SQL Server 项目实战】是北大青鸟教育机构推出的针对初学者的SQL Server数据库管理系统的学习课程,旨在通过实际操作项目,提升学员对SQL Server的理解与应用能力。该课程可能包含了一系列的...

    SqlServer性能优化方面的总结归纳和实战

    在SQL Server性能优化方面,有许多关键点需要理解并掌握,以确保数据库系统高效运行。本文将深入探讨SQL Server性能优化的策略、索引的作用以及优化的阶段。 首先,我们需要了解SQL Server性能优化的重要性。数据库...

    Java 大型网站性能优化实战从前端网络 CDN 到后端大促的全链路性能优化

    本实战指南将探讨从前端网络到后端大促的全链路性能优化策略,旨在提升网站的整体效率。 前端网络性能优化主要关注减少页面加载时间。CDN(Content Delivery Network)是一个关键的工具,它通过在全球部署多个节点,...

    sql server 性能优化大全

    本篇文章将系统地介绍SQL Server性能优化的全方位技术与方案,从底层原理到实际应用。 首先,数据库优化的终极目标包括避免磁盘I/O瓶颈、减少CPU利用率以及减少资源竞争。实现这些目标可以从三个角度进行: 1. ...

    sql优化sql优化sql优化sql优化sql优化

    以上知识点在"SQL Server性能优化.doc"和"SQL Server 2000数据库优化方案参考.doc"中可能有更详细的解释和案例分析。深入理解并实践这些优化策略,将有助于提升SQL Server的性能,从而改善整个系统的运行效率。

    Java 性能优化实战 21 讲

    在Java性能优化实战的21讲中,涵盖了Java开发中至关重要的性能调优技术,旨在提升应用程序的效率、稳定性和可扩展性。以下是对这些关键知识点的详细解析: 1. **JVM内存模型**:理解Java虚拟机(JVM)的内存结构是...

    《Java程序性能优化》(葛一鸣)PDF版本下载.txt

    根据提供的文件信息,我们可以推断出这是一本关于Java程序性能优化的书籍,作者是葛一鸣,并提供了该书PDF版本的下载链接。虽然没有具体的书籍内容,但基于标题、描述以及通常这类书籍会涉及的主题,我们可以总结出...

    sql竞赛项目实战答案

    8. **性能优化**:理解如何使用索引、避免全表扫描、合理使用临时表和存储过程,以及编写高效的SQL语句,这些都是提升查询速度的关键。 9. **SQL标准与方言**:虽然SQL有标准,但每个数据库管理系统(如MySQL、...

    cc的sql优化实战

    在SQL优化实战中,"cc的sql优化实战"这一主题主要关注如何提升SQL查询的效率,这在数据库管理和系统性能优化中至关重要。SQL(结构化查询语言)是用于管理关系数据库的标准语言,优化SQL可以显著改善数据库应用的...

    oracle性能优化最佳实践

    本文旨在深入探讨Oracle数据库性能优化的最佳实践,包括数据库性能基础、调优方法论、SQL语句调优以及Oracle性能优化解决方案,帮助数据库管理员(DBA)和开发者提升数据库系统的整体效能。 ### 数据库性能基础及调优...

    收获,不止SQL优化--抓住SQL的本质1

    SQL性能问题及其原因 - **性能问题的根源**:随着系统数据量的增长和并发访问需求的增加,原本简单的SQL语句可能变得复杂,导致性能下降。 - **识别问题SQL**:在大规模系统中,找出哪些具体的SQL语句影响了系统...

    阿里巴巴Java性能调优实战(2021华山版).pdf

    阿里巴巴Java性能调优实战

    ACCP s2 sql server 项目实战

    《ACCP S2 SQL Server 项目实战》是一个深入学习SQL Server数据库管理与应用的实践教程。在这个项目中,我们将探讨SQL Server的核心功能,包括数据库设计、数据存储、查询优化、事务处理、安全性以及备份恢复等多个...

Global site tag (gtag.js) - Google Analytics