总结sqlserver数据库性能优化相关的注意事项

迷南。 2022-03-31 03:23 336阅读 0赞

**一、分析阶段**  
一般来说，在系统分析阶段往往有太多需要关注的地方，系统各种功能性、可用性、可靠性、安全性需求往往吸引了我们大部分的注意力，但是，我们必须注意，性能是很重要的非功能性需求，必须根据系统的特点确定其实时性需求、响应时间的需求、硬件的配置等。最好能有各种需求的量化的指标。  
另一方面，在分析阶段应该根据各种需求区分出系统的类型，大的方面，区分是OLTP（联机事务处理系统）和OLAP（联机分析处理系统）。  
**二、设计阶段**  
设计阶段可以说是以后系统性能的关键阶段，在这个阶段，有一个关系到以后几乎所有性能调优的过程—数据库设计。  
在数据库设计完成后，可以进行初步的索引设计，好的索引设计可以指导编码阶段写出高效率的代码，为整个系统的性能打下良好的基础。  
以下是性能要求设计阶段需要注意的：

**1、**   
**数据库逻辑设计的规范化**  
数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式：  
第1规范：表的列具有原子性，不可再分割，即列的信息，不能分割。只要是关系型数据库都自动满足。

![345474-20160905152638035-655412631.png][]

第2规范: 要有主键**。**为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。

第3规范:

表中不要有冗余数据，就是说如果表中的数据可以推导出来，就不应该设置该字段

![345474-20160905161603707-1158951858.png][]

**2、**

**合理的冗余**  
完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。  
冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。  
冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。  
  
**3、**   
**字段的设计**  
字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：  
A、数据类型尽量用数字型，数字型的比较比字符型的快很多。  
B、数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。

C、自增字段要慎用，不利于数据迁移。

**4、**

**使用存储过程 \[模块化编程，可以提高速度\]**

**5、**   
**索引的设计**  
在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。  
关于索引的选择，应改主意：  
  
A、  
根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。  
  
B、  
根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。  
  
C、把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。  
D、唯一性太差的不适合单独创建索引，比如性别

E、更新频繁的字段不适合创建索引

F、 一个表不要加太多索引，因为索引影响插入和更新的速度。

G、 什么创建索引后，速度就会变快?

原理示意图:

![345474-20160906160330582-1367775958.png][]

**6.使用索引的注意事项**

A、对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。  
B、 对于使用like 的查询，查询如果是 ‘ %aaa ’ 不会使用到索引，‘aaa%’ 会使用到索引。  
C、 如果条件中有or ，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引, 我们建议大家尽量避免使用 or  关键字  
**7、水平分割**

就是将数据分布在不同的相同字段的表中，比如qq登陆表

我们可以指定一个规则分布存储在不同的表中，例如根据qq号%3=（0-2之间的数字），创建三张表分别为user0，user1，user2

**8、垂直分割**

![345474-20160906161159988-105686015.png][]

一句话: 如果一张表某个字段，信息量大，但是我们很少查询，则可以考虑把这些字段，单独的放入到一张表中，这种方式称为垂直分割.

**三、编码阶段**  
编码阶段是本文的重点，因为在设计确定的情况下，编码的质量几乎决定了整个系统的质量。  
编码阶段首先是需要所有程序员有性能意识，也就是在实现功能同时有考虑性能的思想，数据库是能进行集合运算的工具，我们应该尽量的利用这个工具，所谓集合运算实际是批量运算，就是尽量减少在客户端进行大数据量的循环操作，而用SQL语句或者存储过程代替。关于思想和意识，很难说得很清楚，需要在编程过程中来体会。  
下面罗列一些编程阶段需要注意的事项：  
**1、**   
**只返回需要的数据**  
返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的无效劳动，其害处是显而易见的，避免这类事件需要注意：  
  
A、横向来看，不要写SELECT \*的语句，而是选择你需要的字段。  
  
B、  
纵向来看，合理写WHERE子句，不要写没有WHERE的SQL语句。  
C、注意SELECT  
INTO后的WHERE子句，因为SELECT  
INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。  
  
D、对于聚合查询，可以用HAVING子句进一步限定返回的行。

**2、**   
**注意事务和锁**  
事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事务来保证数据的正确性。

在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意：  
  
A、事务操作过程要尽量小，能拆分的事务要拆分开来。  
  
B、

事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。  
  
C、 事务操作过程要按同一顺序访问对象。  
  
D、提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。  
  
E、 尽量不要指定锁类型和索引，SQL SERVER允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQL  
SERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更有，但是数据量和数据分布在将来是会变化的。  
  
F、 查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。  
**3、**   
**注意临时表和表变量的用法**  
在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：  
  
A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。  
B、如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。  
  
C、  
如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。  
  
D、其他情况下，应该控制临时表和表变量的使用。  
E、关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。  
  
F、 关于临时表产生使用SELECT INTO和CREATE TABLE + INSERT INTO的选择，我们做过测试，一般情况下，SELECT  
INTO会比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT  
INTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用CREATE  
TABLE + INSERT INTO，而大数据量的单个语句使用中，使用SELECT  
INTO。  
G、  
注意排序规则，用CREATE  
TABLE建立的临时表，如果不指定字段的排序规则，会选择TEMPDB的默认排序规则，而不是当前数据库的排序规则。如果当前数据库的排序规则和TEMPDB的排序规则不同，连接的时候就会出现排序规则的冲突错误。一般可以在CREATE  
TABLE建立临时表时指定字段的排序规则为DATABASE\_DEFAULT来避免上述问题。  
**4、**   
**子查询的用法**  
子查询是一个 SELECT 查询，它嵌套在  
SELECT、INSERT、UPDATE、DELETE  
语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。  
  
子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。  
  
如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以用IN、NOT IN、EXISTS、NOT  
EXISTS引入。  
关于相关子查询，应该注意：  
A、NOT IN、NOT EXISTS的相关子查询可以改用LEFT   
JOIN代替写法。比如：  
1.SELECT PUB\_NAME FROM PUBLISHERS WHERE PUB\_ID NOT IN (SELECT PUB\_ID FROM TITLES WHERE TYPE = 'BUSINESS')

可以改写为

1.SELECT A.PUB\_NAME FROM PUBLISHERS A LEFT JOIN TITLES B ON A.PUB\_ID=B.PUB\_ID AND B.TYPE = 'BUSINESS' WHERE B.PUB\_ID IS NULL

1.SELECT TITLE FROM TITLES WHERE NOT EXISTS (SELECT TITLE\_ID FROM SALES WHERE TITLE\_ID = TITLES.TITLE\_ID)  
可以改写成：  
1.SELECT TITLE FROM TITLES LEFT JOIN SALES ON SALES.TITLE\_ID = TITLES.TITLE\_ID WHERE SALES.TITLE\_ID IS NULL

B、 如果保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN

1.SELECT PUB\_NAME FROM PUBLISHERS WHERE PUB\_ID IN (SELECT PUB\_ID FROM TITLES WHERE TYPE = 'BUSINESS')

可以改写为

1.SELECT A.PUB\_NAME FROM PUBLISHERS A INNER JOIN TITLES B ON A.PUB\_ID=B.PUB\_ID AND B.TYPE = 'BUSINESS'

1.SELECT TITLE FROM TITLES WHERE EXISTS (SELECT TITLE\_ID FROM SALES WHERE TITLE\_ID = TITLES.TITLE\_ID)  
可以改写成：  
1.SELECT TITLE FROM TITLES INNER JOIN SALES ON SALES.TITLE\_ID = TITLES.TITLE\_ID

C、区别：in是关系运算符，exists是相关子查询。  
exists的效率比in高的说法不正确，要看具体情况，尽可能利用索引。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。

例：设有两个表td（大表）和tx（小表），看下面的查询：  
1、select \* from td where col in (select col from tx); --利用了td的索引  
select \* from td where exists (select col from tx where col=td.col); --利用tab2的索引  
结果：第一句效率高。  
2、select \* from tx where col in (select col from td); --利用了tx的索引  
select \* from tx where exists (select col from td where col=tx.col); --利用了td的索引  
结果：第二句效率高。

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

A、不要对索引字段进行运算，而要想办法做变换，比如  
SELECT ID FROM T WHERE NUM/2=100  
应改为:  
SELECT ID FROM T WHERE NUM=100\*2

SELECT ID FROM T WHERE NUM/2=NUM1  
如果NUM有索引应改为:  
SELECT ID FROM T WHERE NUM=NUM1\*2  
如果NUM1有索引则不应该改。  
发现过这样的语句：  
1.  
SELECT 年,月,金额 FROM 结余表 WHERE 100\*年+月=2007\*100+10  
应该改为：  
SELECT 年,月,金额 FROM 结余表 WHERE 年=2007 AND 月=10  
B、  
不要对索引字段进行格式转换  
日期字段的例子：  
WHERE CONVERT(VARCHAR(10), 日期字段,120)=’2008-08-15’  
应该改为  
WHERE日期字段〉=’2008-08-15’AND日期字段<’2008-08-16’

C、 不要对索引字段使用函数  
WHERE LEFT(NAME, 3)='ABC' 或者WHERE SUBSTRING(NAME,1, 3)='ABC'  
应改为:  
WHERE NAME LIKE 'ABC%'  
日期查询的例子：  
WHERE DATEDIFF(DAY, 日期,'2005-11-30')=0 应改为:WHERE 日期 >='2005-11-30' AND 日期 <'2005-12-1'  
WHERE DATEDIFF(DAY, 日期,'2005-11-30')>0 应改为:WHERE 日期 <'2005-11-30'  
WHERE DATEDIFF(DAY, 日期,'2005-11-30')>=0应改为:WHERE 日期 <'2005-12-01'  
WHERE DATEDIFF(DAY, 日期,'2005-11-30')<0应改为:WHERE 日期>='2005-12-01'  
WHERE DATEDIFF(DAY, 日期,'2005-11-30')<=0应改为:WHERE 日期>='2005-11-30'  
D、不要对索引字段进行多字段连接  
比如：  
WHERE FAME+ ’.’+LNAME=‘HAIWEI.YANG’  
应改为:WHERE FNAME=‘HAIWEI’ AND LNAME=‘YANG’

E、应尽量避免在 WHERE  
子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

**7、**   
**其他需要注意的地方**  
经验表明，问题发现的越早解决的成本越低，很多性能问题可以在编码阶段就发现，为了提早发现性能问题，需要注意：  
A、程序员注意、关心各表的数据量。  
B、  
编码过程和单元测试过程尽量用数据量较大的数据库测试，最好能用实际数据测试。  
C、  
每个SQL语句尽量简单  
D、  
注意数据库函数的限制以及其性能  
**8、学会分辩SQL语句的优劣**  
自己分辨SQL语句的优劣非常重要，只有自己能分辨优劣才能写出高效的语句。  
A、查看SQL语句的执行计划，可以在查询分析其使用CTRL+L图形化的显示执行计划，一般应该注意百分比最大的几个图形的属性，把鼠标移动到其上面会显示这个图形的属性，需要注意预计成本的数据，也要注意其标题，一般都是CLUSTERED INDEX SEEK 、

INDEX SEEK 、CLUSTERED INDEX SCAN 、INDEX SCAN 、TABLE SCAN等，其中出现SCAN说明语句有优化的余地。也可以用语句  
SET STATISTICS IO ON --运行set statistics io命令可以得到SQL语句的logical reads信息

B、  
用事件探查器跟踪系统的运行，可疑跟踪到执行的语句，以及所用的时间，CPU用量以及I/O数据，从而分析语句的效率。  
C、  
可以用WINDOWS的系统性能检测器，关注CPU、I/O参数  
四、测试、试运行、维护阶段  
测试的主要任务是发现并修改系统的问题，其中性能问题也是一个重要的方面。重点应该放在发现有性能问题的地方，并进行必要的优化。主要进行语句优化、索引优化等。  
试运行和维护阶段是在实际的环境下运行系统，发现的问题范围更广，可能涉及操作系统、网络以及多用户并发环境出现的问题，其优化也扩展到操作系统、网络以及数据库物理存储的优化。  
这个阶段的优花方法在这里不再展开，只说明下索引维护的方法：  
A、  
可以用DBCC DBREINDEX语句或者SQL SERVER维护计划设定定时进行索引重建，索引重建的目的是提高索引的效能。  
B、  
可以用语句UPDATE STATISTICS或者SQL SERVER维护计划设定定时进行索引统计信息的更新，其目的是使得统计信息更能反映实际情况，从而使得优化器选择更合适的索引。  
C、  
可以用DBCC CHECKDB或者DBCC CHECKTABLE语句检查数据库表和索引是否有问题，这两个语句也能修复一般的问题。

**三、  
网上资料中一些说法的个人理解**  
1、 “应尽量避免在 WHERE 子句中对字段进行 NULL  
值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：  
SELECT ID FROM T WHERE NUM IS  
NULL  
可以在NUM上设置默认值0，确保表中NUM列没有NULL值，然后这样查询：  
SELECT ID FROM T WHERE  
NUM=0”  
个人意见：经过测试，IS NULL也是可以用INDEX  
SEEK查找的，0和NULL是不同概念的，以上说法的两个查询的意义和记录数是不同的。  
2、 “应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：  
SELECT ID  
FROM T WHERE NUM=10 OR NUM=20  
可以这样查询：  
SELECT ID FROM T WHERE  
NUM=10  
UNION ALL  
SELECT ID FROM T WHERE  
NUM=20”  
个人意见：主要对全表扫描的说法不赞同。  
3、 “IN 和 NOT IN 也要慎用，否则会导致全表扫描，如：  
SELECT ID  
FROM T WHERE NUM IN(1,2,3)  
对于连续的数值，能用 BETWEEN 就不要用 IN 了：  
SELECT ID FROM T  
WHERE NUM BETWEEN 1 AND 3”  
个人意见：主要对全表扫描的说法不赞同。  
4、  
“尽可能的使用 VARCHAR/NVARCHAR 代替 CHAR/NCHAR  
，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。”  
个人意见：“在一个相对较小的字段内搜索效率显然要高些”显然是对的，但是字段的长短似乎不是由变不变长决定，而是业务本身决定。在SQLSERVER6.5或者之前版本，不定长字符串字段的比较速度比定长的字符串字段的比较速度慢很多，所以对于那些版本，我们都是推荐使用定长字段存储一些关键字段。而在2000版本，修改了不定长字符串字段的比较方法，与定长字段的比较速度差别不大了，这样为了方便，我们大量使用不定长字段。

[345474-20160905152638035-655412631.png]: /images/20220331/2adf66a49a2a4af6b9c21aa290efa43b.png
[345474-20160905161603707-1158951858.png]: /images/20220331/9b54f5f472fc476588f8e7debc29e832.png
[345474-20160906160330582-1367775958.png]: /images/20220331/d05b76d9089644128045509a0a8b2eb6.png
[345474-20160906161159988-105686015.png]: /images/20220331/3302fb9809a143eab587925c44cb7c7e.png