欢迎光临
我们一直在努力

SQL Server中STATISTICS IO物理读和逻辑读的误区

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

 

 

本文版权归作者所有,未经作者同意不得转载。

转载本站文章请注明,转载自:IT技术资讯 [https://www.ithothub.com]

本文链接:https://www.ithothub.com/ihh/202004101406856.html

赞(0) 打赏

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏