你的位置:首页 > 数据库

[数据库]理解SQL SERVER的逻辑读,预读和物理读以及索引


主要参考了博文:宋沄剑 -理解SQL SERVER中的逻辑读,预读和物理读 和 宋沄剑 - T-SQL查询进阶--理解SQL Server中索引的概念,原理以及其他,以下内容为对其博文的读书笔记。

 

1. 数据库的数据存储形式

 

在谈到几种不同的读取方式之前,首先要理解SQL SERVER数据存储的方式.SQL SERVER存储的最小单位为页(Page).每一页大小为8k,SQL SERVER对于页的读取是原子性,要么读完一页,要么完全不读,不会有中间状态。而页之间的数据组织结构为B树。所以SQL SERVER对于逻辑读,预读,和物理读的单位是页.

原子性,指不可再分。页是数据库读取数据的最小单位,所以上图中那种显示,读取了多少次,都是指的是读取了多少页数据。

 

2. SQL SERVER数据读取顺序

当SQL Server执行一个查询语句时,SQL Serer会开始第一步,生成查询计划,同时用估计的数据去磁盘读取数据(预读),这两个第一步是并行的。SQL Server通过这种方式来提高查询性能。

查询计划生成好了以后去缓存读取数据,当发现缓存缺少所需要的数据后让缓存再次去读硬盘(物理读)然后从缓存中取出所有数据(逻辑读)

也即,一般生产环境中,开启io查询后,可能不太容易看到物理读取的操作,因为执行过1次后系统会将其缓存下来,所以这种情况下,物理读的优化不用考虑,只要想办法让逻辑读的次数尽可能少就可以大幅度提高查询性能了。

 

3. 索引的原理

在数据库检索来说,对于磁盘IO扫描是最消耗时间的.因为磁盘扫描涉及很多物理特性,这些是相当消耗时间的。所以B树设计的初衷是为了减少对于磁盘的扫描次数。如果一个表或索引没有使用B树(对于没有聚集索引的表是使用堆heap存储),那么查找一个数据,需要在整个表包含的数据库页中全盘扫描。这无疑会大大加重IO负担.而在SQL SERVER中使用B树进行存储,则仅仅需要将B树的根节点存入内存,经过几次查找后就可以找到存放所需数据的被叶子节点包含的页!进而避免的全盘扫描从而提高了性能.

也即,使用索引以后,数据的存储会由无序的堆变成b树,完成从无序到有序的转变。

 

4. 聚集索引

为了提高某个属性(或属性组)的查询速度,把这个或这些属性(称为聚集码)上具有相同值的元组集中存放在连续的物理块称为聚集。

 

3

在SQL SERVER中,聚集的作用就是将某一列(或是多列)的物理顺序改变为和逻辑顺序相一致,比如,我从adventureworks数据库的employee中抽取5条数据:

4

    当我在ContactID上建立聚集索引时,再次查询:

5

 

所以,核心是:聚集索引改变的是其所在表的物理存储顺序,所以每个表只能有一个聚集索引.

由于有主键的表,系统会默认将其建成聚集索引,所以个人感觉,如果主键是用自增id做的话,不如不要将其设为主键,因为自增id一般在查询中不会太经常使用,反而是【时间】、【日期】等字段在查询时很方便缩小数据集的范围,因此将之设定成聚集索引,个人觉得应该会对查询速度有明显提高。

 

5. 非聚合索引

一个简单的非聚集索引概念如下:

6

     可以看到,非聚集索引需要额外的空间进行存储,按照被索引列进行聚集索引,并在B树的叶子节点包含指向非聚集索引所在表的指针.

如果用目录来比喻索引的话,个人感觉,应该是酱紫的:

聚集索引:

就是标准的目录样式:    页码(聚集索引列)  ||   页码对应的内容

非聚集索引:

是一种嵌套的目录,类似:       页码(非聚集索引列)   ||  页码对应的聚集索引列

也即,非聚集索引查询时,应该是先通过非聚集索引查到对应的聚集索引列,而后再通过聚集索引查询到目标内容。

显然,非聚集索引多了一些操作,因此查询速度上必然比聚集索引要慢,但好处在于,聚集索引只能有一个,可非聚集索引可以有多个,因此给了更多的查询方式。