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

时间: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、有时候出现逻辑读取次数大于物理读取次数加上预读次数,这是因为在预读之前缓存中已经存在部分需要的数据。