SQL Server中STATISTICS IO物理讀和邏輯讀的誤區
大家知道，SQL Server中可以利用下面命令查看某個陳述句讀寫IO的情況
SET STATISTICS IO ON
那么這個命令的結果顯示的物理讀、邏輯讀的IO單位大小是多少，比如結果顯示有
物理讀取 1 次
是代表 對硬碟做了1次物理IO嗎？
在回答這個問題之前，需要先普及幾個常識
在一般默認情況下
Windows的記憶體分頁大小單位是4KB
資料庫的最小讀寫單位是 8K頁面
Windows作業系統的NTFS檔案系統最小讀寫單位（分配單元/簇）是 4KB
機械硬碟的邏輯扇區：512位元組，整個硬碟暴露給作業系統的扇區，一般跟物理扇區的大小是一樣的，保持對齊，作業系統將分配單元的讀寫請求劃分為多個512位元組大小，為了適應邏輯扇區的大小
機械硬碟的物理扇區：512位元組 ，舊磁盤是512位元組，也就是硬碟里面每個磁碟真正的讀寫扇區，新磁盤（固態硬碟的物理扇區和邏輯扇區都是為了兼容機械硬碟模擬出來的，固態硬碟實際用的是塊/頁）是4KB
機械硬碟的原生512 native對齊：邏輯扇區、物理扇區的大小都是一樣的，也叫512n，如果邏輯扇區和物理扇區是不一樣的，那么就是512e，如果是固態硬碟的native對齊就是4Kn
高級格式化：作業系統對檔案系統盤符進行格式化，規劃每分配單元/簇大小，默認4KB
低級格式化：存盤廠家對物理存盤硬體做的低級格式化，例如機械硬碟，規劃每扇區大小，通常512位元組
前提：本文闡述的所有場景都是針對《機械硬碟的原生512 native對齊》的前提下，并且是非虛擬化環境下，虛擬化環境會多了一層VMFS bloc更加復雜，敬請注意！
為什么存在磁盤塊/簇/分配單元？
讀取方便：由于扇區的數量比較小，數目眾多在尋址時比較困難，所以作業系統就將相鄰的扇區組合在一起，形成一個塊，再對塊進行整體的操作，
分離對底層的依賴，作業系統忽略對底層物理存盤結構的設計，通過虛擬出來磁盤塊的概念，檔案系統就是作業系統的一部分，所以檔案系統操作檔案的最小單位是塊/簇/分配單元
這個磁盤塊在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校驗機制
注意：即使是用固態硬碟，也請不要關閉頁面完整性檢測功能！
只有在資料庫頁面、檔案系統分配單元、機械硬碟扇區的大小一致的情況下
就是說，資料庫、檔案系統、存盤設備的最小讀寫單位大小一樣的情況下，也就是所謂的【對齊】，更嚴謹的應該是資料庫、檔案系統、（存盤設備的邏輯扇區、存盤設備的物理扇區，512n或4Kn）的最小讀寫單位大小一樣
才能關閉頁面完整性檢測功能，這個時候可以獲得最大性能
某些檔案系統、存盤設備所謂的聲稱支持【原子寫】，請各位擦亮眼睛^_^，檢查是否真的完整支持，對于某些情況，確實是支持真正原子寫，例如
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
本文著作權歸作者所有，未經作者同意不得轉載，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/4128.html
標籤：SQL Server
上一篇：記錄一下無聊的資料庫作業
下一篇：mysql 基礎操作