门樘柱:SQL Server 优化

一个系统的性能的提高，不单单是试运行或者维护阶段的性能调优的任务，也不单单是开发阶段的事情，而是在整个软件生命周期都需要注意，进行有效工作才能达到的。应该从软件生命周期的不同阶段来总结数据库性能优化相关的注意事项：

一 分析阶段 一般来说，在系统分析阶段往往有太多需要关注的地方，系统各种功能性、可用性、可靠性、安全性需求往往吸引了我们大部分注意力，但是我们必须和足以，性能是很重要的非功能性需求，必须根据系统的特定确定其实时性需求、响应时间的需求、硬件的配置等。最好能有各种需求的量化的指标。另一方面，在分析阶段应该根据各种需求区分出系统的类型，大的方面，区分是OLTp（联机事务处理系统）和OLAP（联机分析处理系统）。

二 设计阶段 可以说是以后系统性能的关键阶段，在这个阶段，有一个关系到以后几乎所有性能调优的过程—数据库 设计

在数据库设计完成后，可以进行初步的索引设计，好的索引可以知道编码阶段写出高效率的代码，为整个系统的性能打下良好的基础。

以下是性能要求设计阶段需要注意的：

1 数据库逻辑设计的规范化 第一范式 没有重复的组或多值的列，这是数据库设计的最低要求；第二范式每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖；第三范式 一个给关键字段不能依赖于另一个非关键的字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求

2 合理的冗余 完全按照规范话设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。冗余可以是冗余的数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力。冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

3 主键的设计 主键是必要的，SQL Server 的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的。在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键比较快，同时小的键可以使主键的B树结构的层次更少。主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

4 外键的设计 外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：外件事最高效的一致性维护方法，数据库的一致性要求，一次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。谨慎使用级联删除和级联更新，级联删除和级联更新有些突破了传统的关于外键的定义，功能有点过于强大使用前必须确定自己已经把握好其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能上级联删除和级联更新是比其他方法更高效的方法。

5 字段的设计 自断时数据库最基本的单位，其设计对性能的影响是很大的需要注意：A 数据类型尽量使用数字型，数字型的比较比字符型的快很多；B 数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的 C 尽量不要允许NULL ，除非必要，可以使用Not Null-Default 代替 D 少用Text 和Image，二进制字段读写是比较慢的，而且读取的方法也不多 E 自增字段慎用，不利于数据迁移

6 数据库物存储和环境的设计 在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使得我们的系统在将来能适应比较多的用户并发和比较大的数据量。

这里需要注意的是文件组的作用，使用文件组可以有效把I/O操作分散道不同的物理硬盘，提高并发能力。

7 系统设计 整个系统的设计特别是系统结构设计对性能是由很大影响的。对于一般的OLTP系统，可以选择C/S、三层的C/S结构等，不同的系统结构其性能的关键也有所不同。系统设计阶段你应该归纳一些业务逻辑放在数据库编程实现。数据库编程包括数据库存储过程、触发器和函数。用数据库编程实现业务逻辑的好处是减少网络流量并可充分利用数据库的预编译和缓存功能。

8 索引的设计 在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候有所区别：

A 根据数据量决定那些表需要添加索引，数据量小的可以只有主键；B根据使用频率决定那些字段需要建立索引，选择京城作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段；C 把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面；D一个表不要加太多索引，因为索引影响插入和更新的速度。

三 编码阶段 编码阶段是本文的重点，因为在设计确定的情况下，编码的质量几乎决定了整个系统的质量。编码阶段首先是需要所有程序员有性能意识，也就是在实现功能同事有考虑性能的思想，数据库是能进行集合运算的工具，我们应该尽量的利用这个工具，所谓集合运算实际是批量运算，就是尽量很少在客户端进行大数据量的循环操作，而用SQL语句或者存储过程代替。

需要注意的有：

1 只返回需要的数据 返回数据到客户端至少需要数据库提取数据、网络传输数据、客户管接收数据以及客户端处理数据等环节，如果返沪不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：A 横向来看不要写Select * 的语句，而是选择你需要的字段；B 纵向来看合理写Where子句，不要写没有Where的SQL语句 C 注意Select Into 后的Where子句，因为Select Into把数据插入到临时表，这个过程会锁定一些系统表，如果这个Where子句返回的数据过多或者速度太慢，会造成系统表长期索引，阻塞其他进程；D 对于聚合查询，可以用Having 子句进一步限定返回的行。

2 尽量少做重复的工作 这一点和上一点是一样的，就是尽量减少无效工作，侧重点在客户端程序：

A 控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的；B 减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的 C 杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销 D 合并同一表统一条件的多次Update；E Update 操作不要拆成Delete和Insert操作的形式，虽然功能相同，但是性能差别很大；F 不要写一些没有意义的查询

3 注意事务和锁 事务是数据库应用中重要的工具，它由原子性、一致性、隔离性、持久性这四个属性，很多次操作我们都需要利用事务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁，尽量减少阻塞。具体注意：

A事务操作过程要尽量下，能拆分的事务要拆分开来；B事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定的很多资源 C事务操作过程要按同一顺序访问对象 D 提高实物中每个语句的频率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间；E 尽量不要指定所类型和索引，SQL Server 允许我们指定语句使用锁类型和索引，但是一般情况下，SQL Server优化器选择的锁类型和索引是在当前数据量和查询条件下最优的 F 查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别。

4 注意临时表和表变量的用法 在复杂系统中，临时表和表变量很难避免，需要注意：

A 如果语句很复杂，连接太多，可以考虑用临时表和表变量分布完成；B 如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据 C 如果需要综合多个表的数据，行程一个结果，可以考虑用临时表变量分步会在哦该着多个表的数据；D 其他情况下 应该控制临时表和表变量的使用 E 关于临时表和表变量得选择，很多说法是表变量在内存，速度快，应该首先选择表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据较多的情况下，临时表的速度反而更快 F 关于临时表产生使用Select Into和Create Table和insert Into ，一般Select Into快的多，但是Select Into会锁定Tempdb的系统表Sysobjects、Sysindexes、SysColumns，在多用户并发开发环境下，容易阻塞进程，在并发起同种，尽量使用Create Table－Inser　into G 注意排序规则，用Create Table 建立的临时表，如果不指定字段的排序规则，会选择TempDB默认的排序规则，而不是当前数据库的排序规则。

5 子查询的用法 子查询是一个Select查询，他嵌套在Select insert Delete Update语句或其他子查询中。任何允许使用表达式的地方都可以用。

6 慎用游标 数据库一般的操作是集合操作，也就是对由Where子句和选择列确定的结果集做集合操作，游标是提供一个非集合操作的途径。一般情况下，游标实现的功能往往可以用循环实现，所以我们一般把游标操作搬到客户端。游标是吧结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的。

7 尽量使用索引建立索引后，并不是每个查询都会使用索引。在使用索引的情况下，索引的使用效率也会有很大的差别。只要我们在查询语句中没有强制指定索引，索引的选择和使用方法是SQL Server 的优化器自动做的选择，而它选择的根据是查询语句的条件以及相关表的统计信息，这就要求我们在写SQL 语句的时候尽量使得优化器可以使用索引。应该注意：

A 不要对索引字段进行计算，而应该想办法做变换；B 不要对索引字段进行格式转换 C 不要对索引字段使用函数 D 不要对索引字段进行多字段连接

8 注意连接条件的写法 多表连接的连接条件对索引的选择有着重要的意义，所以我们在写连接条件的时候需要注意：A 多表连接的时候，连接条件必须写全，宁可重复不要缺漏 B 连接条件尽量使用聚集索引 C注意ON部分条件和Where部分条件的区别

9 其他 A 程序员注意，关心各表的数据量；B编码过程和但愿测试过程尽量用数据量较大的数据库测试，最好用实际数据测试C 每个SQL语句尽量简单 D 不要频繁更新有触发器的表的数据 E 注意数据库函数的限制以及其性能

10 学会分辨SQL语句的优劣

A 查看SQL语句的执行计划，可以在查询分析器使用Control+L图形化的显示执行计划，一般应该注意百分比最大的几个图形的属性，把鼠标移动到其上面会显示这个图形的属性，需要注意预计成本的数据

四 测试 试运行和维护阶段测试的主要任务是发现并修改系统的问题，其中性能问题也是一个重要的方面。重点应该放在发现有性能问题的地方，并进行必要的优化。

A 可以用DBCCDBREINDEX语句或者SQL Server维护计划设定定时进行索引重建，索引重建的目的是提高索引的效能；可以用Update Statistics 或者SQL Server维护计划设定定时进行索引统计信息的更新，其目的是似的统计信息更能反映实际情况，从而使得优化器选择更合适的索引 C 可以用DBCC CHECKDB 或者DBCC CHECKTABLE 语句检查数据库表和索引是否有问题