浅谈SQL Server逻辑读、物理读和预读

2022-10-18 14:52:39

【摘要】

对SQL Server数据库进行性能优化时，有一个重要的工作是优化IO开销。通过开启Statistics IO，我们可以了解到语句执行的IO开销，包含物理读、逻辑读、预读等。而我们是否了解这三者的区别和联系呢，相信很多人都不是很理解，下面我们一起来探索这三者究竟分别代表着什么。

【正文】

一、SQLServer数据存储方式

SQL Server的数据库包括数据文件和日志文件，一个数据库可以有一个或多个数据文件或日志文件。

所有的数据都存储在数据文件中，而数据文件可以划分为再小的单元，我们称为页。页是最小的操作单元，也就是说从磁盘读取数据库的时候最少读取一页，每一页的大小是8KB。另一个概念是区，区是8个连续的页组成的，区是最小的分配单元，当需要空间时最少分配一个区的空间。

我们先举一个例子。

第一次查询数据库AdventureWorks下的表Sales.SalesOrderDetail时，显示“逻辑读取1240次，物理读取5次，预读1250次”。

无论是逻辑读、物理读还是预读的单位都是页，上次我们已经介绍过了。SQL Server对于页的读取是原子性的，要么读完一页，要么完全不读，并且每一页的大小是8KB。

预读：在查询计划生成过程中，用估计信息，去硬盘读取数据到缓存中。预读1250次，也就是估计将要从硬盘中读取了1250页数据并存放到缓存中。

物理读：查询计划生成好以后，如果缓存缺少所需要的数据，再从硬盘中读取缺少的数据到缓存中。

逻辑读：从缓存中取出所有数据。逻辑读1240次，也就是从缓存里取到1240页数据。

我们再来回顾上面的查询经历的过程：

1. 首先在生成执行计划过程中，SQL Server首先从硬盘预计了1250次，也就是读取了1250*8KB=10,000KB的数据并且存入缓存中（也就是预读的过程）

2. 当执行查询计划时，发现缓存的数据还不够。则继续从硬盘读取额外需求的数据并存入缓存中（也就是物理读的过程）

3. 接着SQL Server再从缓存读取全部需要的数据并返回到客户端。（也就是逻辑读的过程）

当我们再次运行上面的查询语句时，得到的信息如下：

我们可以发现，这次没有物理读取和预读，只有逻辑读，这是为什么呢？我们前面已经剖析过原理了，因为第二次查询时直接从缓存就可以读取到所需要的数据。

按照我们介绍的理论，貌似逻辑读取的次数=物理读取的次数+预读的次数。但细心的读者会发现，第一次查询时“逻辑读取1240次，物理读取5次，预读1250次”，我们发现逻辑读取的次数不等于物理读取的次数与预读的次数之和。这又是为什么呢？

1. 首先需要说明的是，逻辑读取的次数并不一定等于物理读取的次数与预读的次数之和。一个很简单例子是第二次查询时物理读取和预读都是0。实际上，预读是按照估计的信息去读取数据，因此读取的页数并不一定准确，可能多于实际的页数也可能少于实际的页数。

2. 如果预读的页数包含了全部的数据，那么就不会有物理读取

3. 有时候会出现逻辑读取的次数大于物理读取的次数与预读的次数之和。这是因为在预读之前缓存中已经存在部分需要的数据。

理解逻辑读、物理读和预读这三个概念，关键是理解语句的查询过程以及哪一个步骤是从缓存读取数据、哪一个步骤是从硬盘读取数据、哪一个步骤又是根据估计的信息去读取数据，这样也就能理解这三个概念了。

逻辑读、物理读和预读这三个的次数并不存在绝对的数量关系，关键还是要理论语句的查询过程。

在对语句进行优化时，重点是优化逻辑读取的次数。通过优化语句来减少逻辑读取的次数，从而也就减少IO开销。