数据库系统性能调优系列(1)_原理

2
3Hale Waihona Puke Baidu
4 OPTION(FORCE ORDER) 选择好驱动表 SQL SERVER 中表的连接顺序由查询分析器智能决定，ORACLE 中表的 连接顺序从右到左 避免 LEFT/RIGHT JOIN Not Over 5 Table Join ABCDEF BEDCFA
WHERE
1 不可缺少，重点分析，深度挖掘潜能，直接影响IO大小和Index使用效率! 2 条件顺序对大数据量表的查询会产生直接的影响！ 3 在符合业务规则的前提下，尽可能多的增加限制条件(时间段)！ 4 通过声明变量，改变表联结等形式，避免出现条件字句嵌套！静态 动态 5 任何对列的操作都将导致表扫描无法利用索引(如计算，转换，正则，函数 等)，尽可能通过优化转换将操作移至等号右边，利用索引！ 6 避免性能低下的操作符和关键字，使用高效的替代方法；如 a is not null 改为 a>0！
避免动态SQL语句，性能和安全 自动参数化查询 静态SQL语句
TIPS
1 避免将复杂的操作使用一条语句完成，多使用短事务! 2 SQL语句编写简洁，可读性强，备注信息明确！别名，大写！ 3 CLR定义的函数和过程，在大数据量环境下，处理字符串和计算的性能比 T_SQL函数倍数提升！ 4 必须不断学习掌握新的数据库技术，利用新版本关键字，函数，工具等， 达到事半功倍！ 5 随着SOA的推广，多层架构，各种框架的普及。必须学习了解编程语言， 站在开发人员的角度来分析优化语句，不简单的割裂程序和数据库，整体 优化！ 6 人工优化 VS 智能优化！LECCO SQL Expert QUEST TOAD ON 减少游标使用 WHERE HAVING 减少函数的使用 Create PROC
大家如有需要，联系我，索取电子书！ 大家如有需要，联系我，索取电子书！
The Art of SQL
谢谢观赏 ！
数据库系统性能调优系列
SQL语句原理篇 SQL语句原理篇
研发一部
Create By:
Sir.Q
SQL结构化查询语言（Structured Query Language） 结构化查询语言（
1 2 3 4 1970年，IBM公司San Jose,California研究实验室的E.J. Codd（埃德加·科德）发表将资料组 成表格的应 用原则（Codd‘s Relational Algebra） 。 1974年，同实验室的D.D.Chamberlin和R.F. Boyce在Codd‘s Relational Algebra基础上，研制 出一套规范语言SEQUEL(Structured English QUEry Language)。 1979年 Oracle 发布了商业版SQL；随后IBM公司在DB2 数据库系统中也实现了SQL商业应用。 1986年，美国ANSI采用SQL作为关系数据库管理系统的标准语言（ANSI X3. 135-1986）， 后为国际标准化组织（ISO）也采纳SQL为国际标准。到目前为止共发展经历了： SQL/86（第一个标准）； SQL/89(增加了引用完整性(referential integrity)) ； SQL/92(被数据库管理系统（DBMS）生产商广发接受) ； SQL/99 SQL/2003(包含了XML相关内容) ； SQL/2006：(包含XQuery的关联应用) SQL/2008 5 因为主流的各种数据库系统在其实践过程中都对 SQL 规范作了某些编改和扩充。所以，不同 数据库系统之间的SQL语言不能完全相互通用。目前主要分为以下几种形式： T-SQL(Transact-SQL) PL/SQL（Procedural Language/SQL） SQL PL[1] 支持 支持 支持 Sybase ASE、MS-SQL Server、MySql Oracle IBM DB2
推荐参考书籍
Learning SQL on SQL Server 2005 Microsoft.SQL.Server.2005.T-SQL.Querying Microsoft SQL Server 2005 Query Tuning and Optimization
SQL for MySQL Developers
Table Join
1 Loop join 驱动表(outer table)较小而内部表较大(inner table)且内部表需要有效的访 问方法（Index）；轻量级 等值和非等值联接 Merge join 驱动表(outer table)和内部表(inner table)都已经在合并(on)列上排序且存 在索引，避免对结果集再排序；中量级 等值联接 Hash join 驱动表(outer table)和内部表(inner table)数据相差明显，未排序，无索引 的等值联结形式；重量级 视图 等值联接
2 3 4
5
6 SQL语句的优化环境，根据数据库系统应用类型可分为：OLTP 和OLAP两种 7 SQL语句优化类型，根据优化途径可分为：基于业务 和 基于技术两种 8 SQL语句优化标准，正确 时间 负载（IO） 局部或整体
SQL语句编译过程 SQL语句编译过程
SQL语句
计
语法分析编译
关系代数表达式 统计信息 RBO CBO 句 调 优 的 关 键 SQL语 句 计 段 阶 语
SQL Tuning and Optimization Cycle
Capture
Workload
Analyze
Change
Optimiza tion
Tuning and Optimization Order
数据库架构 SQL编码 存储设计
硬件配置
Suggestion
常规情况下
磁盘负载过重 内存负载过重 处理器负载过重 网络负载过重 架构不良 代码不良 代码不良 客户端代码不良 调整架构 调整代码 调整存储 调整客户端设计
Join Monitor Where Index Analysis Order Variable Tips
数
Key Word Order
(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP BY <group_by_list> (6) WITH {CUBE | ROLLUP} (7) HAVING <having_condition> (10) ORDER BY <order_by_list>
Variable
1 避免将嵌套的条件字句，替换为通过单独语句赋值的变量，缓存计划! 2 在存储过程中 传入单一变量值很多(如股票代码)，会出现“参数嗅探”， 可通过查询提示，使存储过程重新编译RECOMPILE，提高性能！ 3 变量@A的传入值中，X所占的权重最高，可以通过OPTION (OPTIMIZE FOR (@A = ‘X’))，来节省编译时间，提高查询性能！ 4 SQL语句中，动态的库名，表名，字段名。必须使用变量，动态SQl语句！ 5 变量类型和长度的选择，比较 和 相等 运算！ 6 @Table VS #Table VS ##Table！
通过形式多样的优化转换，达到 限制IO，利用索引，提高性能的目的 没有where基本上就是要做表扫描
INDEX
1 避免无法利用索引的写法，如列计算，类型转换，函数，null，<>等操作! 2 聚簇索引，非聚簇索引，覆盖索引，组合索引，唯一索引与非唯一索引的 差异，索引视图的具体应用！ 3 统计索引使用的具体数据，查找缺失索引，掌握成本和性能比率！ 4 索引管理和维护的方法和周期！索引大小的限制（最大键列数为 16，最大 索引键大小为 900 字节）。 5 合理的索引设计是建立在对各种查询的分析和统计数据上的，只有正确地 使索引与SQL语句结合起来,才能产生最佳的优化方案！ 6 不同的应用环境，制定不同的索引策略！OLTP 和 OLAP 7 Like 查询耗时和字段值总长度成正比 VARCHAR 索引对性能的提升和维护成本的增加 是把双刃剑！ 空间换时间，避免表扫描！ B数结构 全文索引
ORDER
1 避免使用排序，尤其是对大结果集进行排序，与TOP一起出现! 2 排序字段要选择有效的索引字段，与索引排序一致，但排序必然造成索引 扫描！ 3 排序字段避免使用表达式，性能非常之差！ 4 避免排序（计算）列有NULL值！
在建立主键索引(自增列或时间列) 的时候，默认DESC。在SELECT语句 执行时默认为降序排列，避免 了再次 order by
SQL语句的监控与分析
1 Query Analyze Execution Plan 2 SQL SERVER Profiler 3 4 5 6 7 8 9 10 Activity Monitor RML Utilities SYS.DM_ Views SET SHOWPLAN_ALL/XML ON/OFF SET STATISTICS IO/TIME ON/OFF SQL Best Practices Analyzer The Database Engine Tuning Advisor EXPLAIN (MySql)
SQL语句优化和数据库性能调优的关系
1 SQL语句是应用程序访问数据的唯一途径，应用程序的任何数据库操作最终归结为SQL 语 句的执行，所以SQL语句的效率对数据库的性能起了决定性的作用！ SQL语句消耗了70-90%的数据库资源，必须高度重视，严格审核！ SQL语句独立与应用程序的逻辑，相对与优化程序，在时间成本和风险上都很低！ SQL语句的优化,是数据库可编程对象优化的前提和基础;是数据库对象结构和数 据库系统 架构调整优化的强有利支撑! 同样结果的SQL语句有不同的写法，在性能上的差异非常大。