SQL Server存储引擎（1） - 数据库如何读写数据

数据库如何读写数据

 

1、数据读写流程

 

数据读写流程图

 

（0）事务开始

（0.1）所有DML语句必然是基于事务的，如果没有显式开启事务(SQL SERVER 默认不开启隐式事务，这点与ORACLE正好相反)，则事务处理的最小单位为每一条DML语句，即每条语句作为一个事务，并自动提交事务；

（0.2）除非手动开启一个事务(BEGIN TRAN)，或开启隐式事务(SET IMPLICIT_TRANSACTIONS ON)，才需要手动提交事务(COMMIT TRAN)，否则SQL SERVER自动提交事务；

 

（1）发起DML

（1.1）DML语句包括：INSERT、DELETE、UPDATE；

（1.2）DDL语句最终是被转化为对系统表的DML，在SQL SERVER中DDL语句也可以被回滚（ORACLE不可以），比如：CREATE/ALTER/DROP/TRUNCATE，另外DCL语句也可以被回滚；

 

（2）数据是否在内存

（2.1）在内存中查找数据使用HASH算法，不多说；

（2.2）如果数据页不在内存中，则需要从磁盘上的数据文件中，读取相应的数据页到内存中，即物理读，以2.1的方式查找数据页（哪怕没找到），即逻辑读。

 

（3）修改数据

（3.1）在SQL SERVER内存的数据缓冲区中将数据页修改，此时数据页称为脏页（DIRTY PAGE）；

（3.2）在SQL SERVER 内存的日志缓冲区中记录REDO LOG，姑且称为脏日志；

 

（4）事务结束

（4.1）提交（COMMIT），此时将当前事务的脏日志刷新到数据库的日志文件中，并打上事务结束标记（COMMIT），脏页有可能暂未被刷新到数据文件；

事务日志结构如下：

BEGIN TRAN

DML

COMMIT TRAN

 

（4.2）回滚（ROLLBACK），此时读REDO LOG（当中包括逻辑操作、或数据前后像）得到反向DML操作，反向修改脏页，将DML+反向DML从日志缓冲区刷新到数据库的日志文件中，并打上事务结束标记（ROLLBACK），同样，脏页有可能暂未被刷新到数据文件；

事务日志结构如下：

BEGIN TRAN

DML

反向DML

ROLLBACK TRAN

 

不难发现，SQL SERVER的日志容易成为一个瓶颈（BOTTLENECK），因为在写的同时引入了读，即引入了竞争，而ORACLE用UNDO SEGMENT很好地避免了这个问题，REDO LOG永远只是在被串行写。

 

（5）刷新数据页

（5.1）从步骤4可以看出数据库的预写日志（WAL）原则，因为关系型数据库是基于事务的，而日志正是事务ACID属性的保证，也是数据恢复的保证；

（5.2）检查点（CHECKPOINT），检查点周期性地将脏页刷新到数据文件中，在此之前，也是先将脏日志刷新到日志文件中，最终在日志文件打上检查点标记（CHECKPOINT），至此上面事务中修改的数据被正式写到磁盘上的数据文件中。

 

2、数据读写流程深入

 

数据读写体系结构图

 

（0）SQL SERVER MEMORY

（0.1）SQL SERVER占用服务器内存的一部分，非SQL SERVER占用的内存，供操作系统及服务器上其他应用程序使用；

（0.2）SQL SERVER内存结构可分为两大块，关于内存结构详见《SQL Server存储引擎（2） - 内存》，图中仅标出Buffer Pool中的数据及日志缓存；

 

（1）事务结束

（1.1）事务结束的前提是日志缓存成功写入到日志文件中，此时，数据库才会返回事务结束的响应；

（1.2）日志缓存并不是一定要等到事务结束时才刷新到日志文件的，请看下面的234；

 

（2）LOG WRITER

（2.1）当遇到长事务时，不必等到发出事务结束命令，LOG WRITER也会周期性地将脏日志刷新到日志文件，以保证用户发出COMMIT时快速响应并结束；

（2.2）微软并没有公布SQL SERVER 除去COMMIT外，LOG WRITER将脏日志刷新到日志文件的周期，这里可以参考ORACLE的：每3秒；日志缓冲区1/3满或已经包含1M的脏日志；

 

（3）LAZY WRITER

（3.1）LAZY WRITER周期性扫描缓存（默认1s），维护自由页面列表，根据LRU算法将非脏页释放；

（3.2）如果是脏页则刷新到磁盘，同样也是先将脏日志刷新到日志文件中，然后再将脏页刷新到数据文件中，最终内存页释放并加入自由页面列表；

 

（4）CHECKPOINT

（4.1）CHECKPOINT同LAZY WRITER一样也会刷新脏页到数据文件中，但不会维护内存自由页面列表；

（4.2）可以设置SP_CONFIGURE ‘RECOVERY INTERVAL’选项来改变CHECKPOINT发生的频率。