SQLSERVER底层存储和性能优化总结分享

1、系统的过程

架构层、数据库的配置层、SQL语句层。

架构层：安装SQLSERVER，文件：主文件、日志文件分开磁盘放。

系统文件：tempdb  存放SQL执行缓存、执行计划。。发现tempdb增长的比较大，查询，update..安装数据库 把 tempdb单独存在一个磁盘空间中。。



2、设计与优化索引 （性能差，我们想到第一件事就是创建索引）

为什么会提高性能呢？？

a、sqlserver如何访问数据

没有索引，读取所有的页面。有索引的话，读取索引文件。

索引文件大小,也有存储过程空间。

sqlserver2008文件，只有行索引的。（以行为单位，进行除重。 压缩成一条文件）

当我们进行查询，查询的时候找到我们所用的文件。  索引文件比表文件小很多。

SQLSERVER2012 有列索引的概念   （压缩去重）



b.索引结构

数据库每张表最基本单位：页，每个页 是 8kb.

不像select 查询的有顺的排列的。数据库存储页比较凌乱。

像每本书的目录一样。。没有创建索引的表，简称数据堆存放。查询性能比较低。



什么数据堆？

数据后台存放已page，但是他的排列顺序不像我们select 是有顺序的。。数据库存储页比较凌乱，杂乱无章。，这个page用满了，在存放另外一个列的数据。不创建任何索引，就很杂乱，相当于一堆数据。。 （没有指标的概念）



堆上的操作：

INSERT  1、每个新行 可用空间的可用页上。（页，纸。填满，生成新的数据页。。新行的进来，把剩下的，换个新页）

UPDATE 2、从之前页找，如果是移除。

当前页=需要update数据存放的页。   

=update 数据容量大小 小于<=之前字段大小

[quote]=之前页当中。   （转发指针，导致性能问题，跨数据页的问题）  [/quote]



聚集索引：1、逻辑顺序  1，2，3，4，5  （一个）

Insert  每一个新行都必须在正确的 逻辑位          置。

索引碎片： 页分裂

页面没有满

外部：页面的逻辑顺序不一致。。。创建聚集索引，按照一定的顺序存放。聚集索引的列数据出现update就会出现不一致，好的聚集索引：唯一，不变的。

1、没有索引。。表扫描。

（数据堆）



2、建索引，压缩操作。



RID 每个PageID进行查找。



找到最高性能的语句。





有索引的，没有索引的，查看下

IO 开销，CPU开销。。整表查询。。

加个 where条件看看.  (比对IO开销)



想让 IO的开销变少。



查询计划参数化东西

[quote]200 和 >201  执行计划都不一样。[/quote]



select textdata,startTime,EndTime,Reads,writes,cpu

from fn_trace_gettable('c:\a.trc',default) where cpu is not null

order by cpu desc

基础表数据都存放在数据页里面，SQLSERVER存储数据都是按照“页”为单位来存放在磁盘的，SQLSERVER从磁盘读写数据也是按照 “页” 为单位 一页一页地读取到内存，所以有时候如果数据不能塞满整个数据页，那么这种情况就叫做：page split 页拆分 或者 “碎片“，，SQLSERVER有时候需要读取两页才能把用户需要找的数据读出来，如果表加了索引的话可以通过重建索引的方法解决，没有建立索引的话就没有办法了。因为建立了索引的表，表里面的数据是放在B树数据结构的索引页，而不是堆数据结构的数据页





碎片页拆分，如果有索引的话也叫“索引碎片”，那么这些碎片是怎麽造成的 以及如何避免和修复呢？



造成的原因很简单：当你删除表里一条记录的时候，SQLSERVER会去找这条记录的所在页面；然后删除，当你删除了之后，那么那个数据页面存放的数据就变成不连续的了，这时候就称为“数据碎片”。就是页面存放的数据不连续，当你删除多条记录，而这些记录都在同一个页面，那么就会造成数据塞不满整个页面，当你插入一条记录的时候，这条记录是不会插入到你上次删除的那条记录的地方的，如果SQLSERVER的最后一个数据页面（这些数据页面是用双向链表来链接的）有位置就插入去，没有位置就新开一个页面，然后插入记录



如果存在这些碎片主要两个弊端：

弊端一：SQLSERVER在查找记录的时候要查找多个页面才能找到那条记录（特别使用表扫描执行计划），SQLSERVER按一定顺序一条一条记录地去找，这样的话本来只需要读取一个页面到内存，SQLSERVER现在需要读取两个页面到内存（特别SQLSERVER使用表扫描执行计划的时候），会造成内存的使用增大，查找时间增多

弊端二：本来使用一个页面可以存放下连续的数据，现在需要两个页面才能存放下这些数据，造成磁盘空间的浪费，占用额外的磁盘空间



如何避免和修复：

最好在表上建立一个聚集索引，然后通过重建索引或者重新组织索引的方式使数据重新按照建立索引的那个字段的顺序重新排序存储，重建索引会把这些数据页面重新排序把没有“塞满数据”的页面重新“塞满”，并有序排列。



聚集索引除了可以提高查询性能之外，还可以按需重新生成或重新组织来控制表碎片。也可以对视图创建聚集索引。

聚集索引基于数据行的键值在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储

只能按一个顺序存储是指：当你建立聚集索引或者主键的时候，你有可能在多个列上建立了聚集索引或者复合主键

SQLSERVER只会按照你创建索引的时候的最左一列的字段来排序，只是一列，不是说先按第一列排序，再按第二列排序，再按第三列排序。



如果表格上没有聚集索引那么这个问题不能解决，没有聚集索引的表都是堆数据结构的表，就是说数据本身就没有一个排列方式

除非加一个聚集索引，使数据有序排列，非聚集索引也不能解决因为非聚集索引只是在表上加了索引但是数据还是按照“堆”数据结构来排列的，因为SQLSERVER的页面类型有索引页面，数据页面，LOB页面，行溢出页面



因为数据的存储地方只有两个：“索引页”跟“堆数据结构的堆页”，如果建立了聚集索引那么数据会存储在索引页并按照建立了索引的第一个字段的顺序来排序，而没有建立聚集索引的那么数据都原封不动，照常存储在堆数据结构的数据页面，只是非聚集索引有指针指向这些页面，使查找更快

SQL Server查询分析器是基于开销的。通常来讲，查询分析器会根据谓词来确定该如何选择高效的查询路线,比如该选择哪个索引。而每次查询分析器寻找路径时，并不会每一次都去统计索引中包含的行数，值的范围等，而是根据一定条件创建和更新这些信息后保存到数据库中，这也就是所谓的统计信息。



下面我们通过一个简单的例子来看统计信息是如何影响查询分析器。我建立一个测试表，有两个INT值的列，其中id为自增，ref上建立非聚集索引，插入100条数据，从1到100，再插入9900条等于100的数据。

此时，我where后使用ref值作为查询条件，但是给定不同的值，我们可以看出根据统计信息，查询分析器做出了不同的选择，所示。

select * from teststatistic where ref=4   索引查找  和 RID查找

select * from teststatistic where ref=100  表扫描。。因为 ref=100 数据量很大，表扫描明显优于索引查找。

据不同的谓词，查询优化器做了不同的选择  其实，对于查询分析器来说，柱状图对于直接可以确定的谓词非常管用。



SqlServer有二种索引：聚集索引和非聚集索引。二者的差别在于：【聚集索引】直接决定了记录的存放位置，或者说：根据聚集索引可以直接获取到记录。【非聚集索引】保存了二个信息：1.相应索引字段的值，2.记录对应聚集索引的位置（如果表没有聚集索引则保存记录指针）。因此，如果能通过【聚集索引】来查找记录，显然也是最快的。 





既然是计划，就表示要在具体执行前就能确定下来的操作方案。那么SqlServer是如何选择一种执行计划的呢？ SqlServer怎么知道什么时候该用索引或者用哪个索引？对于

SqlServer来说，每当要执行一个查询时，都要首先检查有没有这个查询的执行计划是否存在缓存中，如果没有，则要生成一个执行计划，具体在产生执行计划时，并不是看有哪些索引可用（随机选择），而是会参考一种被称为【索引统计信息】的数据。 如果您仔细地看一下前面的执行计划或者执行过程表格，会发现SqlServer能预估每个步骤所产生的数据量，正是因为SqlServer能预估这些数据量，SqlServer才能选择一个它认为最合适的方法去执行查询过程，此时【索引统计信息】就能告诉SqlServer这些数据。



随着列中数据发生变化，索引和列的统计信息可能会过时，从而导致查询优化器选择的查询处理方法不是最佳的。例如，如果创建一个包含一个索引列和 1,000 行数据的表，每一行在索引列中的值都是唯一的，则查询优化器将把该索引列视为收集查询数据的好方法。如果更新列中的数据后存在许多重复值，则该列不再是用于查询的理想候选列。但是，查询优化器仍然根据索引的过时分布统计信息（基于更新前的数据），将其视为好的候选列。