Sql server 性能优化

最新推荐文章于 2024-07-28 18:23:28 发布

小笨蛋的忧伤

最新推荐文章于 2024-07-28 18:23:28 发布

阅读量675

点赞数 1

刚开始时，注册用户较少，网站性能表现不错，但随着注册用户的增多，访问速度开始变慢，当一个基于数据库的应用程序运行起来很慢时，90%的可能都是由于数据访问程序的问题，要么是没有优化，要么是没有按最佳方法编写代码，因此你需要审查和优化你的数据访问/处理程序。

数据库优化包含以下三部分，数据库自身的优化，数据库表优化，程序操作优化。

第一部分：数据库自身的优化

分为粗略数据分区和精确数据分区两部分。

优化1：增加次数据文件，设置文件自动增长（粗略数据分区）

1.增加次数据文件
由于CPU和内存的速度远大于硬盘的读写速度，所以可以把不同的数据文件放在不同的物理硬盘里，这样执行查询的时候，就可以让多个硬盘同时进行查询，以充分利用CPU和内存的性能，提高查询速度。在这里详细介绍一下其写入的原理，数据文件（MDF、NDF）和日志文件（LDF）的写入方式是不一样的：
　　
　　数据文件：SQL Server按照同一个文件组里面的所有文件现有空闲空间的大小，按这个比例把新的数据分布到所有有空间的数据文件里，如果有三个数据文件A.MDF，B.NDF，C.NDF，空闲大小分别为200mb，100mb，和50mb，那么写入一个70mb的东西，他就会向ABC三个文件中一次写入40、20、10的数据，如果某个日志文件已满，就不会向其写入。
　　
　　日志文件：日志文件是按照顺序写入的，一个写满，才会写入另外一个。

由上可见，如果能增加其数据文件NDF,有利于大数据量的查询速度，但是增加日志文件却没什么用处。
2.设置文件自动增长（大数据量，小数据量无需设置）
当数据文件写满，进行某些操作时，SQL Server会让操作等待，直到文件自动增长结束了，原先的那个操作才能继续进行。
对于一个比较繁忙的数据库，推荐的设置是开启数据库自动增长选项，以防数据库空间用尽导致应用程序失败，但是要严格避免自动增长的发生。同时，尽量不要使用自动收缩功能。

优化2：表分区，索引分区（精确数据分区）

为什么要表分区？
　　当一个表的数据量太大的时候，我们最想做的一件事是什么？将这个表一分为二或者更多分，但是表还是这个表，只是将其内容存储分开，这样读取就快了N倍了。
　　原理：表数据是无法放在文件中的，但是文件组可以放在文件中，表可以放在文件组中，这样就间接实现了表数据存放在不同的文件中。能分区存储的还有：表、索引和大型对象数据。
　　SQL SERVER 2005中，引入了表分区的概念，当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区,当一个表里的数据很多时，可以将其分拆到多个的表里,因为要扫描的数据变得更少 ,查询可以更快地运行,这样操作大大提高了性能,表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表

什么时候使用分区表?
1、表的大小超过2GB。
2、表中包含历史数据，新的数据被增加到新的分区中。

表分区的操作三步走:
1 .创建分区函数

CREATE PARTITION FUNCTION xx1(int)

AS RANGE LEFT FOR VALUES (10000, 20000);

注释：创建分区函数：myRangePF2，以INT类型分区，分三个区间，10000以内在A 区，1W-2W在B区，2W以上在C区.

2.创建分区架构

CREATE PARTITION SCHEME myRangePS2

AS PARTITION xx1

TO (a, b, c);

注释：在分区函数XX1上创建分区架构：myRangePS2，分别为A,B,C三个区间,A,B,C分别为三个文件组的名称，
	 而且必须三个NDF隶属于这三个组，文件所属文件组一旦创建就不能修改

3 .对表进行分区

常用数据规范–数据空间类型修改为：分区方案，然后选择分区方案名称和分区列列表，结果如图所示：

CREATE TABLE [dbo].[AvCache]( 

[AVNote] [varchar](300) NULL,

[bb] [int] IDENTITY(1,1)

) ON [myRangePS2](bb);        

 注意:这里使用[myRangePS2]架构，根据bb分区,也可以用sql语句生成

4.查询表分区

SELECT *, $PARTITION.[myRangePF2](bb)  FROM dbo.AVCache

5.索引分区

ALTER PARTITION FUNCTION 语法如下：
ALTER PARTITION FUNCTION partition_function_name()
{
    SPLIT RANGE ( boundary_value )
  | MERGE RANGE ( boundary_value )
} [ ; ]

参数说明：partition_function_name，要修改的分区函数的名称。

在分区函数中添加一个分区。boundary_value 确定新分区的范围，因此它必须不同于分区函数的现有边界范围。根据 boundary_value，Microsoft SQL Server 2005 数据库引擎将某个现有范围拆分为两个范围。在这两个范围中，新 boundary_value 所在的范围被视为是新分区。

优化3.分布式数据库设计

分布式数据库系统是在集中式数据库系统的基础上发展起来的，理解起来也很简单，就是将整体的数据库分开，分布到各个地方，就其本质而言，分布式数据库系统分为两种：
1.数据在逻辑上是统一的，而在物理上却是分散的，一个分布式数据库在逻辑上是一个统一的整体，在物理上则是分别存储在不同的物理节点上，我们通常说的分布式数据库都是这种。
2.逻辑是分布的，物理上也是分布的，这种也成联邦式分布数据库，由于组成联邦的各个子数据库系统是相对“自治”的，这种系统可以容纳多种不同用途的、差异较大的数据库，比较适宜于大范围内数据库的集成。

分布式数据库较为复杂，具体的使用方式就不做详细的介绍了。

第二部分：数据库表的优化

优化1：设计规范化表，消除数据冗余

范式：检测和纠正非规范设计
1NF：属性只能包含单值，即无重复的属性；
2NF: 非主键属性完全依赖于主键，不能只依赖于主键的一部分；
3NF：非主键属性不依赖于其他任何非主键属性。

优化2：适当的冗余，增加计算列

满足范式的表一定是规范化的表，但不一定是最佳的设计。很多情况下会为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。合理的冗余可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。
数据库设计的实用原则是：在数据冗余和处理速度之间找到合适的平衡点。

优化3：字段设计原则

字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：
A、数据类型尽量用数字型，数字型的比较比字符型的快很多。
B、数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。
C、尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。
D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。
E、自增字段要慎用，不利于数据迁移。

优化4：分割你的表，减小表尺寸

如果你发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键的某个值为界线，将该表的记录水平分割为两个表。
如果你若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表。

第三部分：程序操作优化

分为调整索引和优化T-SQL代码两部分。

一.调整索引

分为创建适当的覆盖索引和整理索引碎片两部分。

1.创建适当的覆盖索引

 为了更好地理解覆盖索引，在正式介绍覆盖索引之前，首先稍微来谈一谈有关索引的一些基础知识。

数据页和索引页
　　在SQLServer中，数据存储的基本单位是页，一页的大小为8KB，分别由页首，数据行和行偏移量组成。
　　页首固定占用96个字节，用来存储相关的页面系统信息，例如所属的数据库表对象Id等。数据行是真实数据的存储区域，每一行的大小是不固定的。行偏移量是一个数组，数组的每个位置占2个字节，用来存储数据行距离开头的位置偏移量，主要是用来做快速定位，例如想要查找第N行，只要访问行偏移量数组的第N项，就能快速找到数据行所在的位置。索引页和数据页的结构类似，所不同的是索引页的数据行存储的是和索引相关的信息。

聚集索引和非聚集索引
　　聚集索引定义了表中数据存储的真实物理位置，它是按照指定列的顺序来存储数据的，类比于新华字典中的汉字是按照拼音顺序排列的，所以每张表只能建立一个聚集索引。聚集索引是一棵B+树结构，包含索引页和数据页。
　　非聚集索引是独立于数据真实存储顺序逻辑而存在的，类比于新华字典中按偏旁部首查找汉字的方式。与聚集索引对比，非聚集索引也是B+树的数据结构，但却只包含索引页，而且在一张表中可以建立多个非聚集索引。

什么是覆盖索引
　　覆盖索引是在SQLServer2005中引入的概念，只能建立在非聚集索引的基础上，通常情况下，非聚集索引的索引页是不包含真实数据的，只存储着指向数据页中数据行的指针，而覆盖索引则是通过将数据存储在索引页上，从而在查找对应数据的时候，只要找到索引页就可以访问到数据，无需再去查询数据页，所以说这个索引是数据“覆盖”的。也就是给非聚集索引添加了聚集索引的功能。
　　
创建覆盖索引

CREATE INDEX IDX_name                      --覆盖索引名
　　ON  tbl_name(IX_name)                  --表名及非聚集索引名
　　INCLUDE(column_name,column_name)       --列名

创建索引能带来查询的优化，但却带来了更改数据的负担，覆盖索引也不意外。由上面的分析我们知道，覆盖索引是非聚集索引的进一步细化，在更新数据的时候，如果涉及到覆盖索引INCLUDE的列，除了更改数据页之外还要更改索引页，比单纯使用非聚集索引增添了额外的工作。所以，在设计覆盖索引的时候，要综合考虑应该覆盖的列，确保INCLUDE的列能带来最佳的性能优化。

2.整理索引碎片

什么是索引碎片?
　　由于表上有过度地插入、修改和删除操作，索引页被分成多块就形成了索引碎片，如果索引碎片严重，那扫描索引的时间就会变长，甚至导致索引不可用，因此数据检索操作就慢下来了。

有两种类型的索引碎片：内部碎片和外部碎片。

内部碎片：为了有效的利用内存，使内存产生更少的碎片，要对内存分页，内存以页为单位来使用，最后一页往往装不满，于是形成了内部碎片。

外部碎片：为了共享要分段，在段的换入换出时形成外部碎片，比如5K的段换出后，有一个4k的段进来放到原来5k的地方，于是形成1k的外部碎片。

如何知道是否发生了索引碎片?

DBCC ShowContig(tbl_name)                     --检查数据库下的某表的索引碎片情况

检查结果（示例）：
在这里插入图片描述
通过对扫描密度（过低），扫描碎片（过高）的结果分析，判定是否需要索引重建，主要看如下两个：
Scan Density [Best Count:Actual Count]-扫描密度［最佳值:实际值］：DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100％。低了则说明有外部碎片。

Logical Scan Fragmentation-逻辑扫描碎片：无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片。

解决方式：

   DBCC INDEXDEFRAG              --一是利用整理索引碎片

   DBCC DBREINDEX               --二是利用重建索引。

两者区别调用微软的原话如下：
DBCC INDEXDEFRAG 命令是联机操作，所以索引只有在该命令正在运行时才可用，而且可以在不丢失已完成工作的情况下中断该操作。这种方法的缺点是在重新组织数据方面没有聚集索引的除去/重新创建操作有效。
重新创建聚集索引将对数据进行重新组织，其结果是使数据页填满。填满程度可以使用 FILLFACTOR 选项进行配置。这种方法的缺点是索引在除去/重新创建周期内为脱机状态，并且操作属原子级。如果中断索引创建，则不会重新创建该索引。

也就是说，要想获得好的效果，还是得用重建索引，所以决定重建索引。

虽然索引可以提高查询速度，但如果你的数据库是一个事务型数据库，大多数时候都是更新操作，更新数据也就意味着要更新索引，这个时候就要兼顾查询和更新操作了，因为在OLTP数据库表上创建过多的索引会降低整体数据库性能。
　　我给大家一个建议：如果你的数据库是事务型的，平均每个表上不能超过5个索引，如果你的数据库是数据仓库型，平均每个表可以创建10个索引都没问题。

二.优化TSQL数据访问代码

1、在查询中不要使用“select * ”
　　(1)检索不必要的列会带来额外的系统开销，有句话叫做“该省的则省”；
　　(2)数据库不能利用“覆盖索引”的优点，因此查询缓慢。
　　
2、子查询中执行存在性检查使用 EXISTS，不使用 IN
　当你使用EXISTS时，SQL Server知道你要执行存在性检查，当它发现第一个匹配的值时，就会返回TRUE。
　
3、避免使用临时表：
　　(1)除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替;
　　(2)大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在TempDb数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。
　　
4、使用全文搜索搜索文本数据，取代like搜索，全文搜索始终优于like搜索：
　　(1)全文搜索让你可以实现like不能完成的复杂搜索，如搜索一个单词或一个短语，搜索一个与另一个单词或短语相近的单词或短语，或者是搜索同义词;
　　(2)实现全文搜索比实现like搜索更容易(特别是复杂的搜索)。

5、在事务中使用下列最佳实践
　　(1)SQL Server 2005之前，在BEGIN TRANSACTION之后，每个子查询修改语句时，必须检查@@ERROR的值，如果值不等于0，那么最后的语句可能会导致一个错误，如果发生任何错误，事务必须回滚。从SQL Server 2005开始，Try…Catch…代码块可以处理TSQL中的事务，因此在事务型代码中最好加上Try…Catch…;
　　(2)避免使用嵌套事务，使用@@TRANCOUNT变量检查事务是否需要启动(为了避免嵌套事务);
　　(3)尽可能晚启动事务，提交和回滚事务要尽可能快，以减少资源锁定时间。