logo资料库

sql server 性能优化大全.pdf

发布时间：2022-06-24 发布人：admin 分类：说明书 资料大小：4.25M 资料格式：pdf 举报 版权申诉
第1页 / 共169页
第2页 / 共169页
第3页 / 共169页
第4页 / 共169页
第5页 / 共169页
第6页 / 共169页
第7页 / 共169页
第8页 / 共169页
资料共169页，剩余部分请下载后查看
    天善智能致全体BI同仁的公开信
    数据库优化之实战
    1 优化SQLServer数据库的经验总结
    1.1 数据库优化终极目标
    1.2 数据库优化经验
    2 SQL2005数据库引擎结构
    2.1 SQL2005数据库引擎结构（一）
    2.1.1 协议层
    2.2 SQL2005数据库引擎结构（二）
    2.2.1 关系引擎　relational engine
    2.3 SQL2005数据库引擎结构（三）
    2.3.1 存储引擎
    3 SQLServer性能优化综述
    4 索引的基本原理，以及数据是如何被访问的
    4.1 （一）SQLS如何访问没有建立索引的数据表
    4.2 （二）SQLS如何访问建立了非聚集索引的数据表
    4.3 （三）SQLS如何访问建立聚集索引的数据表
    4.4 （四）SQLS如何访问既有聚集索引、又有非聚集索引的数据表
    4.5 索引的优点和不足
    4.6 MSSQL优化之————探索MSSQL执行计划 .
    5 SQLServer中提高查询性能的方法
    5.1 MS SQLServer查询优化方法
    5.2 第一部分 SQL SERVER数据库优化方案
    5.3 存储过程编写经验和优化措施
    5.4 50种方法巧妙优化你的SQL Server数据库
    6 数据库的查询优化技术
    6.1 查询优化技术一
    6.2 数据库查询优化技术二
    7 微软官方查询优化建议
    7.1 查询优化
    7.2 查询优化建议
    7.3 数据库优化策略
    8 SQL Server数据库开发的二十一条军规(SQL收藏)
    8.1 一、了解你用的工具
    8.2 二、不要使用游标
    8.3 三、规范化你的数据表
    8.4 四、不要使用SELECT *
    8.5 五、了解你将要对数据进行的操作
    8.6 六、不要给“性别”列创建索引
    8.7 七、使用事务
    8.8 八、小心死锁
    8.9 九、不要打开大的数据集
    8.10 十、不要使用服务器端游标
    8.11 十一、使用参数查询
    8.12 十二、在程序编码时使用大数据量的数据库
    8.13 十三、不要使用INSERT导入大批的数据
    8.14 十四、注意超时问题
    8.15 十五、不要忽略同时修改同一记录的问题
    8.16 十六、在细节表中插入纪录时，不要在主表执行SELECT MAX(ID)
    8.17 十七、避免将列设为NULLable
    8.18 十八、尽量不要使用TEXT数据类型
    8.19 十九、尽量不要使用临时表
    8.20 二十、学会分析查询
    8.21 二十一、使用参照完整性
    9 SQL 查询慢的48个原因分析
    9.1 sql 查询慢的48个原因分析。
    10 本地电脑和局域网电脑的SQLServer之间-自动-备份数据库
    10.1 以下代码放在作业里做调度，自动备份、自动删除天前备份
    10.2 远程数据库可以备份本地
    11 数据库优化设计方案
    11.1 1 引言
    11.2 2 基于第三范式的基本表设计
    11.3 3 基本表扩展设计
    12 如何让你的SQL运行得更快
    12.1 一、不合理的索引设计----
    12.1.1 ---- 1.在date上建有一非个群集索引
    12.1.2 ---- 2.在date上的一个群集索引
    12.1.3 ---- 3.在place，date，amount上的组合索引
    12.1.4 ---- 4.在date，place，amount上的组合索引
    12.1.5 ---- 5.总结：----
    12.2 二、不充份的连接条件：
    12.3 三、不可优化的where子句
    12.4 ---- 总结：---- 可见，所谓优化即where子句利用了索引，不可优化即发生了表扫描或额外开销。
    13 优化SQL Server索引的技巧 .
    13.1 关于索引的常识
    13.2 数据页
    13.3 填充因子
    14 大型数据库的设计原则与开发技巧 .
    14.1 一、成立数据小组
    14.2 二、设计原则
    14.3 三、设计技巧
    15 SQL数据库优化
    15.1 sql数据库优化之一：就是合理的数据库的设计。
    15.2 sql数据库优化之二：查询的优化
    15.3 sql数据库优化之三：就是where条件
    16 程序员必须知道的SQLSERVER数据库优化技巧
    16.1 （1）超大量记录数据库的优化技巧
    16.2 （2）中等量级数据库的优化技巧
    16.3 （3）避免使用视图(viewport)与关联
    16.4 （4）不要忘记索引(index)也不要滥用索引(index)
    16.5 （5）二进制的 text image 等字段应该单独设置别的表中
    16.6 （6）不要使用文本类型的 id
    16.7 （7）数据库表table的字段field不要太多
    16.8 （8）将字符串(varchar)比较变成数字型(int)比较
    16.9 （9）为每个数据库表(table)设置 datetime 字段
    16.10 （10）适当使用存储过程(Stored Processing)
    17 数据库优化理论
    17.1 数据库优化理论 - 优化概述
    18 尖端数据管理技术，优化企业数据宝库
    19 优化硬件，磁盘，操作系统
    19.1 一.优化硬件
    19.2 三、优化磁盘
    19.3 四、优化操作系统
    20 数据库优化之SQLServerProfiler使用
    20.1 数据库优化方案一
    20.2 数据库优化方案二:
    20.3 数据库优化方案三:
    21 SQLSERVER数据库优化教程系列
    21.1 MSSQL优化之存储架构之文件和文件组
    21.1.1 Sql server存储架构之一文件和文件组
    21.1.2 数据库的文件和文件组
    21.1.3 关于文件大小
    21.2 MSSQL优化之存储架构之 页
    21.2.1 页
    21.2.2 页头
    21.2.3 PAGEID：
    21.2.4 Nextpage：
    21.3 最详细的临时表，表变量的对比
    22 大型网站数据库优化和故障跟踪与排查
    22.1 上篇
    22.2 下篇
    23 数据库优化查询原则
    24 sql server 数据库优化--显示执行计划
    25 你真的了解SQL的索引吗？
    26 如何使用数据库引擎优化顾问优化数据库
    27 数据库水平切分的实现原理解析
    27.1 什么是数据切分
    27.2 为什么要数据切分
    27.3 怎么做到数据切分
    28 数据库优化之总结
    29 参考文献:
    数据库优化之实战 天善智能—专注 BI 商业智能和数据库性能优化 想学习 BI 商业智能？想掌握真正的数据库技术?赶紧加入天善智能，获取天善多年来商业智能的实战文档、视频等资料。 天善智能致全体 BI 同仁的公开信 各位 BI 同仁们： 大家好！如果有一天，您有机会看到这封信，说明我们很有缘。天善智能是一个小团队，是由 几位 BI 技术实战者组织建立的草根组织，或者某一天我们会变得很强大，如果您支持和同意的话。 天善智能专注于商业智能和数据库性能优化，2011 年 9 月由梁勇（Robay）带头组织成立，前期 以接外包项目和顾问培训服务为主，以及一些小范围的独家知识传播。在 2012 年 9 月，天善开始通 过网络来传播天善多年来在商业智能和数据库方面的实战经验。目前天善智能总共已经制作了 23 份 技术文档，录制了 25 部视频教程（陆续增加中...），开展了六期 BI 技术公开课，并且把这资料放在 天善智能的官方博客，优酷，各种网络媒介上免费让大家下载，观看和学习。最难得的是，这些资料 是网络上最独有，最全，最有含金量的，并且整理的井井有序，免费开放了给大家。这样做的目的， 完全是因为天善体谅一些大学生、初学者想学习 BI 技术，但是又苦于学习无门的心情，因为天善的 所有成员也经历过一段这样痛苦的时间，因此想通过这些小小的行为帮助到那些 BI 的初学者。 俗话说：“授之以鱼不如授之以渔”，天善智能虽然提供了这么多的资料供大家免费学习，但是天 善认为仅仅做到这一点是不够的，更好的方式应该是让大家掌握一种无形的本领。这种本领可以描述 成——“如何思考、如何学习、如何解决问题、如果沉淀、如何成长”等。也许您会觉得有点虚无缥 缈，但是请相信我，关注天善智能，和天善一起前行成长，终有一天会实现各位心中的理想。 最后，天善智能成立时间短，也还年轻，更急需成长，因此天善智能诚恳的希望您能提出具有建 设性的建议，助天善团队壮大，提升天善团队影响力，使天善迈向成熟的阶段。 写于 2012 年 12 月 22 日末日重生后的第一天 如何找到天善？ 官方博客：http://blog.tianshansoft.com (订阅本博客随时掌握天善动态，下载最新文档工具。目前已经 600 人订阅，还不订阅更待何时？) 官方 QA 论坛：http://qa.tianshansoft.com (任何技术问题，只要您认真提，天善一定认真答。) 官方 QQ 群——5 群：23689966 6 群：237979203 前 4 群基本满员，多达 2000 人，加入交流群也是必须的。（加入时请注明：天善智能） 天善优酷：http://i.youku.com/tianshansoft 天善智能官方博客： http://blog.tianshansoft.com 第 1 页 共 169 页
    数据库优化之实战 天善智能—专注 BI 商业智能和数据库性能优化 想学习 BI 商业智能？想掌握真正的数据库技术?赶紧加入天善智能，获取天善多年来商业智能的实战文档、视频等资料。 数据库优化之实战 1 优化 SQLServer 数据库的经验总结 1.1 数据库优化终极目标 1.避免磁盘 I/O 瓶颈、 2.减少 CPU 利用率 3.减少资源竞争。 角度： 基本表设计、扩展设计和数据库表对象放置 方法： 1.数据库设计 2.索引 3.避免长事务 4.存储过程 5.sql 优化。 1.2 数据库优化经验 缺省情况下建立的索引是非群集索引，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页上。 优化数据库的注意事项： 1、关键字段建立索引。 2、使用存储过程，它使SQL变得更加灵活和高效。 3、备份数据库和清除垃圾数据。 4、SQL语句语法的优化。（可以用Sybase的SQL Expert，可惜我没找到unexpired的序列号） 5、清理删除日志。 SQL语句优化的基本原则： 1、使用索引来更快地遍历表。 缺省情况下建立的索引是非群集索引，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页 上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说：①.有大量重复值、且经常有范围查询 （between, >,< ，>=,< =）和order by、group by发生的列，可考虑建立群集索引；②.经常同时存取多列，且每列都含有 重复值可考虑建立组合索引；③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。 2、IS NULL 与 IS NOT NULL 不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中 有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where 子句中使用is null或is not null的语句优化器是不允许使用索引的。 3、IN和EXISTS EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用 EXISTS的子查询。 4、在海量查询时尽量少用格式转换。 5、当在SQL SERVER 2000中，如果存储过程只有一个参数，并且是OUTPUT类型的，必须在调用这个存储过程的时候给这 个参数一个初始的值，否则会出现调用错误。 6、ORDER BY和GROPU BY 使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，Optimizer 将无法优化。 7、任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。 8、IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含 索引。 天善智能官方博客： http://blog.tianshansoft.com 第 2 页 共 169 页
    数据库优化之实战 天善智能—专注 BI 商业智能和数据库性能优化 想学习 BI 商业智能？想掌握真正的数据库技术?赶紧加入天善智能，获取天善多年来商业智能的实战文档、视频等资料。 9、SET SHOWPLAN_ALL ON 查看执行方案。DBCC检查数据库数据完整性。DBCC(DataBase Consistency Checker）是一组 用于验证SQL Server数据库完整性的程序。 10、谨慎使用游标 在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，这样可使性能 得到明显提高。 注释：所谓的优化就是WHERE子句利用了索引，不可优化即发生了表扫描或额外开销。经验显示，SQL Server性能的最 大改进得益于逻辑的数据库设计、索引设计和查询设计方面。反过来说，最大的性能问题常常是由其中这些相同方面中的不 足引起的。其实SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数， 尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及 数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计 2 SQL2005 数据库引擎结构 Microsoft SQL Server 2005 数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限 并快速处理事务，从而满足企业内要求极高而且需要处理大量数据的应用需要。数据库引擎还在保持高可用性方面提供了有 力的支持。 2.1 SQL2005 数据库引擎结构（一）  今天重点从上层来看了一下 SQL2005 的数据库引擎组件，并大体上说说如何协同工作，理解这些是有助更好的学 习数据库。联机帮助一下，发现一个很重点的组件：SQLOS-SQL Operating System.先用图来说明一下总体组成： 天善智能官方博客： http://blog.tianshansoft.com 第 3 页 共 169 页
    数据库优化之实战 天善智能—专注 BI 商业智能和数据库性能优化 想学习 BI 商业智能？想掌握真正的数据库技术?赶紧加入天善智能，获取天善多年来商业智能的实战文档、视频等资料。 2.1.1 协议层  首先我们必须知道 SQLSERVER 逻辑都是客户/服务器，不管是你在一台单机上，还是前端应用分散在多台不同操 作系统的机器上，甚至 COM+结构都是。接着我们简单的来理解访问架构，理解 SQLSERVER 的我们不如先来说说 HTTP，不管我们是什么平台下的浏览器，或是 MSXML 对象，或是.NET 的类我们都可以轻松访问 IIS，简单的说也 就是我们只要遵守标准的 HTTP 协议就可以访问 IIS。那我们的 SQLSERVER 也是这样做的，只不过不 HTTP 协议而 是表格格式数据流 DTS(tabular data stream),对 IIS 和 SQLSERVER 来说 HTTP 与 DTS 两者的差别仅是前者是公开 协议而后者是 MS 非公开的内部协议(每一次数据库升级 DTS 也会作相应的升级，同时确保向下的兼容性)，所以 不管是 ODBC 还是 OLEDB、SQL Client、JDBC 只是把数据请求转换成 DTS 然后打包封装成标准的通信协议(如 TCP/IP) 信息包最后送到服务器端。服务器端把通讯信息包解开送给 ODS(Open Data Services)，然后还原成标准的 SQL 语句并转交给关系引擎。 SQLSERVER2005 支持的通讯协议有四种：     1.共享内存 这是最简单的数据库在本地的一种协议。同时不需要配置就可以使用。如果是 MDAC2.8 以前的版本 是不支持这个协议的，会自动切换到命名管道。 2.命名管道 局域网内使用的一种协议，互为对方进程提供数据，这些进程可以在本地，也可以远程。 3.TCP/IP 使用是广泛的协议，可以在不同硬件和操作系统间互相通讯。 4.虚拟接口 VIA 与专用 VIA 硬件有关。 最后说一下 TDS 端点，2005 已经使用 TDS 端点代表服务器连接，初始化安装时，一共初始化 5 个端点，四个各 一个，另一个给 DAC 使用。对于激动的 DTS 端点才可以使用，处于非激活态的端点仍然存在，只是不能使用而已。 2.2 SQL2005 数据库引擎结构（二）  图例参考 天善智能官方博客： http://blog.tianshansoft.com 第 4 页 共 169 页
    数据库优化之实战 天善智能—专注 BI 商业智能和数据库性能优化 想学习 BI 商业智能？想掌握真正的数据库技术?赶紧加入天善智能，获取天善多年来商业智能的实战文档、视频等资料。 2.2.1 关系引擎 relational engine  这个可以说是数据库最重要的引擎之一，有的书也称查询处理器，简单的说它完成对某个查询进行分析并协调引 擎内的相关组件以最优的方式进行工作，最终以 OLE DB 行集或非 OLE DB 行集与存储引擎(storage engine)通讯 进行存取数据。  在关系引擎内按其功能大体上可以分几个主要的组件，重点说说命令解析及查询优化器的工作。下面我们以图来 说明： 天善智能官方博客： http://blog.tianshansoft.com 第 5 页 共 169 页
    数据库优化之实战 天善智能—专注 BI 商业智能和数据库性能优化 想学习 BI 商业智能？想掌握真正的数据库技术?赶紧加入天善智能，获取天善多年来商业智能的实战文档、视频等资料。  注：图中的蓝色箭头是我根据自己的理解画出来的，事实这些组件之间的工作是互相牵连、错综复杂的，如有不 当希望指正  命令解析器：很显然它是负责检查 T-SQL 的语法正确性并转译成内部使用的查询树，在这时如果解析器无法识别 时会立马报错并指出错误出处。我平时在使用的过程中经常会发现 SQL 抛出没有准确标识的错误，这是因为 T-SQL 本身没有语法错误，命令解析器把它生成查询树，不再保存源码格式的语句，在后续的执行中如果有错误发生自 然无法定位源码行信息。  查询优化器：这个话题感兴趣的人就多了，但是我得告诉大家的是 SQL 采用的取样分布统计数据评估成本的方式 进行优化的，这势必会带来一些让人不尽如意的时候。优化器所能做的是使用各种存取方法及策略解析查询并生 成相对成本最经济的计划。这个最经济的计划就有意思了，SQLSERVER 并没有采用彻底优化的方式，因为对于一 个复杂的查询如果优化器检查并评估每一个可能的计划比选择一个相对经济的计划并执行它耗时耗成本。最后 SQL 优化器采用了启发式的修剪方式回避彻底优化生成执行计划，其实我们想呀，基于成本的优化本身这个过程 就是耗时，如果优化都彻底实现最优化，对于一个查询无论你如何实现它在性能上没有了差异，嘿嘿，DBA 失业 也就不远了。  查询优化器首先从解析器获取到查询树，首先判断是否要优化，如果不需要优化的查询树直接生成内格式，比如 控制流、DLL 命令等。另外的被标识并进入实际执行优化阶段，这部分被标识的语句大部分为 DML 语句。优化过 程大体如下： 天善智能官方博客： http://blog.tianshansoft.com 第 6 页 共 169 页
    数据库优化之实战 天善智能—专注 BI 商业智能和数据库性能优化 想学习 BI 商业智能？想掌握真正的数据库技术?赶紧加入天善智能，获取天善多年来商业智能的实战文档、视频等资料。  Pseudo 系统过程：SQL6.5 以后的版本在关系引擎中加入了系统过程的二进代码，如游标运行机制的过程： sp_cursor、sp_cursoropen 等以交互的方式解决难以标准的 SQL 语句，调用方式类似于存储过程。 2.3 SQL2005 数据库引擎结构（三）  图例参考 天善智能官方博客： http://blog.tianshansoft.com 第 7 页 共 169 页
    数据库优化之实战 天善智能—专注 BI 商业智能和数据库性能优化 想学习 BI 商业智能？想掌握真正的数据库技术?赶紧加入天善智能，获取天善多年来商业智能的实战文档、视频等资料。 2.3.1 存储引擎  存储引擎我们可以简单的理解成与数据有关的所有组件。存储引擎的主要任务包括：管理数据库实例文件、磁盘 数据分页、缓冲区及 I/O 操作、数据库事务及并发和锁、大容量装载、备份恢复、DBCC 命令等等。  根据相关资料我们可以把这些功能简单的分成五个单元：数据存取、事务、其它操作、缓冲区管理器、SQLOS。 这五部分又派生出许多组件，下面先简单的以图来说明： 天善智能官方博客： http://blog.tianshansoft.com 第 8 页 共 169 页
    分享到：
    赞 收藏

    相关推荐

    资料库

    数据库

    共收录1218份资料，累计14个分类，关注成员有19位，主要包括：NoSQL,MongoDB,Redis,SQLite,Memcached,DB2,Sybase,Informix,PostgreSQL,MySQL,SQLServer,Access,Oracle,其它

    热门标签

    NoSQL MongoDB Redis SQLite Memcached DB2 Sybase Informix PostgreSQL MySQL SQLServer Access Oracle 其它

    最新资料