初谈逻辑读、物理读、预读

时间：2022-08-20 00:17:27

前言：

该文并不全是本人原创，里面的某些原理来自于CareySon。

SQL SERVER数据存储的形式

要理解逻辑读、物理读、预读这三个概念，先要搞懂SQL Server的数据存储方式。

       SQL Server数据库包括数据文件和日志文件，一个数据库可以有一个或多少数据文件、日志文件。所有的数据存储在数据文件中，数据文件可以划分为再小的单元，我们称为“页”。每页大小8k。8个页面构成一个区。SQL Server对于页的读取是最原子性，要么读完一页，要么完全不读。页之间的数据组织结构为B树。 所以SQL Server对于逻辑读、物理读、预读的单位都是页。

创建如下测试表：

BEGIN TRAN
--CREATE TABLE
CREATE TABLE [dbo].[TestCount](
    [C1] [varchar](400) NULL,
    [C2] [varchar](300) NULL,
    [C3] [varchar](150) NOT NULL,
    [C4] [varchar](80) NULL,
    [C5] [varchar](38) NOT NULL,
    [C6] [int] NOT NULL,
 CONSTRAINT [PK_TestCount] PRIMARY KEY CLUSTERED 
(
    [C6] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------
DECLARE @COUNT INT =1
SET NOCOUNT ON
WHILE @COUNT<=15000
BEGIN
    INSERT TestCount
        SELECT NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),@COUNT
    SET @COUNT=@COUNT+1
END
commit

由于对页的读取是原子性的，以下读取了多少次就是读了多少页

初谈逻辑读、物理读、预读

上面查询逻辑读 387，该表是3.008M，每一页存储的数据是:8K=8192字节-96字节(页头)-36字节(行偏移）= 8060字节。

3.008*1024*1024 / 8060 ≈ 391

另外表中还有一些非数据占用的空间，因此上式的结果约等于逻辑读次数。

第二次执行上面相同的语句：

初谈逻辑读、物理读、预读

可以看到，本次就只有逻辑读了，没有上面的预读了，这是因为第一次读取的时候引擎就把读到的资料放在了缓存中，方便后面的读取（缓存的读取速度约是机械硬盘速度的20倍），而第二次读的时候只需要读缓存就行了。

从执行顺序上理解各种读

 初谈逻辑读、物理读、预读

      下面我解释一下图。当遇到一个查询语句时，SQL SERVER会走第一步，分别为生成执行计划(占用CPU和内存资源)和用估计的数据去磁盘中取得需要取的数据(占用IO资源，这就是预读),注意，两个第一步是并行的，SQL SERVER通过这种方式来提高查询性能.

然后查询计划生成好了以后去缓存读取数据.当发现缓存缺少所需要的数据后让缓存再次去读硬盘（物理读）

最后从缓存中取出所有数据（逻辑读）。

查询计划在生成，但未交给查询执行器执行之前，SQL SERVER 并不产生‘预读(但在生成执行计划时，查询处理器需要读取各个表的定义及表上各个索引的统计信息)。

当查询计划生成后，真正交给查询执行器执行时，SQL server 才会使用另外一个线程将查询“可能需要的数据”从磁盘读取的缓冲区中（前提是数据不在缓存中），这就是预读。同时这也意味着查询执行时进行逻辑运算的线程与“预读”所使用的线程并不是相同的线程。

   这个预读的页数数据可以通过这个DMV看到:

SELECT 
*
FROM sys.dm_db_index_physical_stats
(DB_ID('Test'),OBJECT_ID('TestCount'),NULL,NULL,'sampled')

 小结：

     1、首先要说明，逻辑读取次数并不绝对等于物理读取次数和预读次数之和。第二次查询物理读取次数和预读次数都是0。预读是按照估计的信息去读取信息，因此读取的页数并不一定准确，可能多于也可能少于实际的页数。
     2、如果预读的页数包括了全部数据，那么就不会有物理读取次数。
     3、有时候出现逻辑读取次数大于物理读取次数加上预读次数，这是因为在预读之前缓存中已经存在部分需要的数据。
标签：

相关文章