初談邏輯讀、物理讀、預讀

前言：

該文并不全是本人原創，里面的某些原理來自于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
BEGININSERT TestCountSELECT NEWID(),NEWID(),NEWID(),NEWID(),NEWID(),@COUNTSET @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、有時候出現邏輯讀取次數大于物理讀取次數加上預讀次數，這是因為在預讀之前緩存中已經存在部分需要的數據。