SQL Server中STATISTICS IO物理讀和邏輯讀的誤區 大家知道，SQL Server中可以利用下麵命令查看某個語句讀寫IO的情況 SET STATISTICS IO ON 那麼這個命令的結果顯示的物理讀、邏輯讀的IO單位大小是多少，比如結果顯示有 物理讀取 1 次 是代表 對硬碟做了 ...
SQL Server中STATISTICS IO物理讀和邏輯讀的誤區
大家知道，SQL Server中可以利用下麵命令查看某個語句讀寫IO的情況
SET STATISTICS IO ON
那麼這個命令的結果顯示的物理讀、邏輯讀的IO單位大小是多少，比如結果顯示有
物理讀取 1 次
是代表 對硬碟做了1次物理IO嗎？
在回答這個問題之前，需要先普及幾個常識
在一般預設情況下
Windows的記憶體分頁大小單位是4KB
資料庫的最小讀寫單位是 8K頁面
Windows操作系統的NTFS文件系統最小讀寫單位（分配單元/簇）是 4KB
機械硬碟的的最小讀寫單位（邏輯扇區和物理扇區）是512位元組
高級格式化：操作系統對文件系統盤符進行格式化，規劃每分配單元/簇大小，預設4KB
低級格式化：存儲廠家對物理存儲硬體做的低級格式化，例如機械硬碟，規劃每扇區大小，通常512位元組
為什麼存在磁碟塊/簇/分配單元？
讀取方便：由於扇區的數量比較小，數目眾多在定址時比較困難，所以操作系統就將相鄰的扇區組合在一起，形成一個塊，再對塊進行整體的操作，
分離對底層的依賴，操作系統忽略對底層物理存儲結構的設計，通過虛擬出來磁碟塊的概念，文件系統就是操作系統的一部分，所以文件系統操作文件的最小單位是塊/簇/分配單元
這個磁碟塊在Linux的ext4文件系統中稱為block，在Windows的NTFS文件系統中稱為分配單元或簇
什麼是記憶體分頁？
操作系統經常與記憶體和硬碟這兩種存儲設備進行通信，類似於“塊”的概念，都需要一種虛擬的基本單位。所以，與記憶體操作，是虛擬一個頁的概念來作為最小單位。與硬碟打交道，就是以塊為最小單位
固態硬碟因為沒有扇區概念，用的是塊/頁，一個塊/頁一般是4KB，so固態硬碟暫且不討論
先說結論，實際上STATISTICS IO 中物理讀和邏輯讀的統計對象自始至終都是資料庫8K頁面，比如，邏輯讀1次， 物理讀1次，實際上都是按8KB頁為單位，是SQL Server這個軟體的統計方式
這樣就會造成誤解，產生疑問
如果物理讀為1次，那麼資料庫對磁碟是做了一次讀寫操作一次IO，對嗎？
如果邏輯讀為1次，那麼資料庫在記憶體中是讀寫了一個記憶體頁一次IO，對嗎？
實際情況是怎樣的呢？
對於物理讀情況
SQL Server是運行在Windows系統上的一個軟體，那麼這個軟體在文件系統上存儲數據依然按照NTFS文件系統的規則，存儲一個8K的頁面需要占用2個分配單元
可以用winhex這個軟體，按8K大小查看資料庫的mdf文件可以查看到完整的一個資料庫頁面數據
對於文件系統，讀寫一個資料庫8KB頁面需要讀寫2個分配單元 也就是2個文件系統IO
在機械硬碟裡面，文件系統的一個4KB分配單元寫入到機械硬碟里，需要讀寫8個扇區，也就是8個硬碟IO，而1個資料庫8KB頁面寫入到機械硬碟里，就需要讀寫16個扇區，也就是實際寫入一個資料庫頁面需要16個硬碟IO
然後這裡會出現一些問題，如果系統故障或硬體故障，就有可能出現一個資料庫頁面寫入存儲硬體不完整情況，比如16個硬碟IO才能寫入完整一個8KB頁面，而如果在寫入第10個IO的時候發生系統崩潰或硬體崩潰，只寫入了5KB頁面數據到硬碟，這時候資料庫數據就已經不完整了，然後各家資料庫廠商才開發【頁面寫入完整性檢測機制】，例如
MySQL InnoDB的Double Write機制（innodb_doublewrite = 1） + page checksum
MSSQL的PAGE校驗機制
註意：即使是用固態硬碟，也請不要關閉頁面完整性檢測功能！
只有在資料庫頁面、文件系統分配單元、機械硬碟扇區的大小一致的情況下
就是說，資料庫、文件系統、存儲設備的最小讀寫單位大小一樣的情況下，也就是所謂的【對齊】，才能關閉頁面完整性檢測功能，這個時候可以獲得最大性能
某些文件系統、存儲設備所謂的聲稱支持【原子寫】，請各位擦亮眼睛^_^，檢查是否真的完整支持，對於某些情況，確實是支持真正原子寫，例如
1、資料庫使用裸設備，這樣就不需要文件系統
2、以寶存PCIE快閃記憶體為例子，其Nand Flash的最小寫單位是page，目前Nand Flash 的page大小是32kb，這個基本上都是大於大部分資料庫通用的block size或page size，32kb可以存放4個MSSQL頁面（非廣告）
對於邏輯讀情況
Windows的記憶體分頁大小單位是4KB，一個資料庫頁面8KB，那麼讀寫一個記憶體中的資料庫頁面實際上需要讀寫2個記憶體分頁
在記憶體里，讀寫一個資料庫8KB頁面需要讀寫2個記憶體分頁， 也就是2個記憶體IO
然後記憶體中8KB資料庫頁跟文件系統中的8KB資料庫頁是一一對應的，不然的話，利用B+樹索引結構和二分查找法查找數據也無從談起
總結
對於文件系統，讀寫一個資料庫8KB頁面需要讀寫2個分配單元 也就是2個文件系統IO
對於機械硬碟，讀寫一個資料庫8KB頁面需要讀寫16個硬碟扇區 也就是16個硬碟IO
對於記憶體，讀寫一個資料庫8KB頁面需要讀寫2個記憶體分頁 也就是2個記憶體IO
SQL Server只是跑在Windows操作系統上的一個軟體，它無法知道也不需知道它所在文件系統的最小讀寫單位，也無法知道也不需知道存儲設備的最小讀寫單位，
實際上操作系統從文件系統中讀取8KB頁面數據喂給資料庫，資料庫收到之後STATISTICS IO 就統計物理讀為 1，至於邏輯讀也是同理
最最後，放一張圖，做的比較醜
參考文章
http://www.dostor.com/article/111637957.html
https://blog.csdn.net/qq_34228570/article/details/80209748
本文版權歸作者所有，未經作者同意不得轉載。
