淺談SQL Server邏輯讀、物理讀和預讀

標籤：資料庫   空間   統計   資訊   

【摘要】

對SQL Server資料庫進行效能最佳化時，有一個重要的工作是最佳化IO開銷。通過開啟Statistics IO，我們可以瞭解到語句執行的IO開銷，包含物理讀、邏輯讀、預讀等。而我們是否瞭解這三者的區別和聯絡呢，相信很多人都不是很理解，下面我們一起來探索這三者究竟分別代表著什麼。

【本文】

一  、SQLServer資料存放區方式

SQL Server的資料庫包括資料檔案和記錄檔，一個資料庫可以有一個或多個資料檔案或記錄檔。

所有的資料都儲存在資料檔案中，而資料檔案可以劃分為再小的單元，我們稱為頁。頁是最小的操作單元，也就是說從磁碟讀取資料庫的時候最少讀取一頁，每一頁的大小是8KB。另一個概念是區，區是8個連續的頁組成的，區是最小的配置單位，當需要空間時最少分配一個區的空間。

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/4C/52/wKioL1Q7aeLiUvbpAAC01XY4PEY362.jpg" title="1.jpg" alt="wKioL1Q7aeLiUvbpAAC01XY4PEY362.jpg" />

二  、剖析SQLServer IO統計資訊2.1      初識邏輯讀、物理讀和預讀

我們先舉一個例子。

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/4C/51/wKiom1Q7abrSlJRyAAF_waI4tYM244.jpg" title="2.jpg" alt="wKiom1Q7abrSlJRyAAF_waI4tYM244.jpg" />

第一次查詢資料庫AdventureWorks下的表Sales.SalesOrderDetail時，顯示“邏輯讀取1240次，物理讀取5次，預讀1250次”。

無論是邏輯讀、物理讀還是預讀的單位都是頁，上次我們已經介紹過了。SQL Server對於頁的讀取是原子性的，要麼讀完一頁，要麼完全不讀，並且每一頁的大小是8KB。

預讀：在查詢計劃產生過程中，用估計資訊，去硬碟讀取資料到緩衝中。預讀1250次，也就是估計將要從硬碟中讀取了1250頁資料並存放到緩衝中。

物理讀：查詢計劃產生好以後，如果緩衝缺少所需要的資料，再從硬碟中讀取缺少的資料到緩衝中。

邏輯讀：從緩衝中取出所有資料。邏輯讀1240次，也就是從緩衝裡取到1240頁資料。

2.2      語句執行過程剖析

我們再來回顧上面的查詢經曆的過程：

1.     首先在產生執行計畫過程中，SQL Server首先從硬碟預計了1250次，也就是讀取了1250*8KB=10,000KB的資料並且存入緩衝中（也就是預讀的過程）

2.     當執行查詢計劃時，發現緩衝的資料還不夠。則繼續從硬碟讀取額外需求的資料並存入緩衝中（也就是物理讀的過程）

3.     接著SQL Server再從緩衝讀取全部需要的資料並返回到用戶端。（也就是邏輯讀的過程）

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/4C/52/wKioL1Q7ag7xISh4AAConbBVTNo351.jpg" title="3.jpg" alt="wKioL1Q7ag7xISh4AAConbBVTNo351.jpg" />

2.3      邏輯讀、物理讀和預讀的關係

當我們再次運行上面的查詢語句時，得到的資訊如下：

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/4C/52/wKioL1Q7ah7Q7L6WAAGG7igNQVQ071.jpg" title="4.jpg" alt="wKioL1Q7ah7Q7L6WAAGG7igNQVQ071.jpg" />

我們可以發現，這次沒有物理讀取和預讀，只有邏輯讀，這是為什麼呢？我們前面已經剖析過原理了，因為第二次查詢時直接從緩衝就可以讀取到所需要的資料。

按照我們介紹的理論，貌似邏輯讀取的次數=物理讀取的次數+預讀的次數。但細心的讀者會發現，第一次查詢時“邏輯讀取1240次，物理讀取5次，預讀1250次”，我們發現邏輯讀取的次數不等於物理讀取的次數與預讀的次數之和。這又是為什麼呢？

1.     首先需要說明的是，邏輯讀取的次數並不一定等於物理讀取的次數與預讀的次數之和。一個很簡單例子是第二次查詢時物理讀取和預讀都是0。實際上，預讀是按照估計的資訊去讀取資料，因此讀取的頁數並不一定準確，可能多於實際的頁數也可能少於實際的頁數。

2.     如果預讀的頁數包含了全部的資料，那麼就不會有物理讀取

3.     有時候會出現邏輯讀取的次數大於物理讀取的次數與預讀的次數之和。這是因為在預讀之前緩衝中已經存在部分需要的資料。

三  、小結

理解邏輯讀、物理讀和預讀這三個概念，關鍵是理解語句的查詢過程以及哪一個步驟是從緩衝讀取資料、哪一個步驟是從硬碟讀取資料、哪一個步驟又是根據估計的資訊去讀取資料，這樣也就能理解這三個概念了。

邏輯讀、物理讀和預讀這三個的次數並不存在絕對的數量關係，關鍵還是要理論語句的查詢過程。

在對語句進行最佳化時，重點是最佳化邏輯讀取的次數。通過最佳化語句來減少邏輯讀取的次數，從而也就減少IO開銷。

本文出自 “嘉為IT培訓學院” 部落格，請務必保留此出處http://canway.blog.51cto.com/8964890/1563208

淺談SQL Server邏輯讀、物理讀和預讀