SQL Server中STATISTICS IO物理读和逻辑读的误区
SQL Server中STATISTICS IO物理读和逻辑读的误区
背景
SQL Server 作为运行在 Windows 操作系统上的数据库软件，其 IO 统计功能对于数据库性能优化非常重要。
为了更好地理解 SQL Server 的 SET STATISTICS IO ON 输出结果，尤其是物理读、逻辑读的统计单位，我们需要首先了解操作系统、文件系统以及存储设备的基本概念和它们之间的交互机制。
本文将重点解释这些基础概念，并剖析 SQL Server 如何与文件系统和硬件交互，以帮助读者更清晰地理解数据库 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 block更加复杂，敬请注意！
为什么存在磁盘块/簇/分配单元？
读取方便：由于扇区的数量比较小，数目众多在寻址时比较困难，所以操作系统就将相邻的扇区组合在一起，形成一个块，再对块进行整体的操作，
分离对底层的依赖，操作系统忽略对底层物理存储结构的设计，通过虚拟出来磁盘块的概念，文件系统就是操作系统的一部分，所以文件系统操作文件的最小单位是块/簇/分配单元
这个磁盘块在Linux的ext4文件系统中称为block，在Windows的NTFS文件系统中称为分配单元或簇
什么是内存分页？
操作系统经常与内存和硬盘这两种存储设备进行通信，类似于“块”的概念，都需要一种虚拟的基本单位。所以，与内存操作，是虚拟一个页的概念来作为最小单位。与硬盘打交道，就是以块为最小单位
固态硬盘因为没有扇区概念，用的是块/页，一个块/页一般是4KB，所以固态硬盘暂且不讨论
先说结论，实际上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
SQL Server的PAGE校验机制
注意：即使是用固态硬盘，也请不要关闭页面完整性检测功能！
只有在数据库页面、文件系统分配单元、机械硬盘扇区的大小一致的情况下
就是说，数据库、文件系统、存储设备的最小读写单位大小一样的情况下，也就是所谓的【对齐】，更严谨的应该是数据库、文件系统、（存储设备的逻辑扇区、存储设备的物理扇区，512n或4Kn）的最小读写单位大小一样
才能关闭页面完整性检测功能，这个时候可以获得最大性能
某些文件系统、存储设备所谓的声称支持【原子写】，请各位擦亮眼睛，检查是否真的完整支持，对于某些情况，确实是支持真正原子写，例如
1、数据库使用裸设备，这样就不需要文件系统
2、以宝存PCIE闪存为例子，其Nand Flash的最小写单位是page，目前Nand Flash 的page大小是32kb，这个基本上都是大于大部分数据库通用的block size或page size，32kb可以存放4个SQL Server页面
对于逻辑读情况
Windows的内存分页大小单位是4KB，一个数据库页面8KB，那么读写一个内存中的数据库页面实际上需要读写2个内存分页
在内存里，读写一个数据库8KB页面需要读写2个内存分页， 也就是2个内存IO
然后内存中，8KB数据库页跟文件系统中的8KB数据库页是一一对应的，不然的话，利用B+树索引结构和二分查找法查找数据也无从谈起
总结
对于文件系统，读写一个数据库8KB页面需要读写2个分配单元 也就是2个文件系统IO，另外，磁盘是一个块设备，固态硬盘和机械硬盘都一样，每次刷盘都需要刷一定大小的块 (block)才高效，所以文件系统为了刷盘经济，一般达到一定的block 大小才会刷盘
固态硬盘中的页是最小读写单位，页大小通常为4KB或8KB，最小的擦除单位称为“块”，所以NTFS文件系统默认4KB分配单元设定是比较合理的
对于机械硬盘，磁盘/硬盘是一个块设备，读写一个数据库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
本文版权归作者所有，未经作者同意不得转载。
