数据库性能优化专题一：通过减少iO次数，优化查询的原理浅谈

背景：面试中遇到数据库性能优化，回答不太理想，并且实际开发中数据库性能优化，的确是一个非常重要且常见的需求，决心好好研究

关于sql server数据库是由一个或是多个数据文件，和日志文件构成如下

如下新建一张表格，并且插入十万条数据，本电脑性能耗时大约9s,

CREATE TABLE [dbo].[PersonTenThousand](
	[Id] [int] NULL,
	[PId] [int] NULL,
	[Name] [nvarchar](50) NULL,
	[Gender] [nvarchar](50) NULL,
	[Age] [int] NULL,
	[Birthday] [datetime] NULL,
	[Height] [int] NULL,
	[Weight] [int] NULL
)

declare @i int,@r int,@g char(2);
set @i=1
while(@i<=100000)
begin
set @r=rand()*10000;
set @g='女'
if(@r>50000)
begin
set @g='男'
end
insert into PersonTenThousand(Id,PId,Name,Gender,Age,Birthday,Height,Weight) 
values(@i,@r,'随机姓名'+convert(varchar(50),@i),@g,rand()*100,getdate(),100+rand()*100,50+rand()*100)
set @i=@i+1
end
truncate table PersonTenThousand

 dbcc dropcleanbuffers --清空缓存
set statistics io on --开启IO统计
select * from PersonTenThousand where id=16000
set statistics io on 
select * from PersonTenThousand where name='张飞'

执行以下语句，得到结果

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

(1 行受影响)
表 'PersonTenThousand'。扫描计数 1，逻辑读取 751 次，物理读取 0 次，预读 745 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

(0 行受影响)
表 'PersonTenThousand'。扫描计数 1，逻辑读取 751 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

到底预读是什么意思？逻辑读取又是什么意思，物理读取又是什么意思呢？

看一下查询过程图：
 

其实sql语句执行过程中，一边生成执行，一边预估出这次查询需要加载的数据页数，（如何预估，博主暂时也不清楚）如图所示，可以看到数据文件是5.867MB

而SQLSERVER存储的最小单位是页，每一页大小为8K，即8*1024=8192字节，SQLSERVER对页的读取是原子性的，即要么读完一页，要么完全不读。

即使仅仅要获得一条数据，也要读完该页，而页之间的数据组织结构为B树结构。

每一页存储的数据是8K=8192字节-96字节（页头）-36字节（行偏移）=8060字节。（关于这里页头，行偏移的知识，建议看一下msdn官网一节关于页的描述）

5.867*1024*1024=6151995.39
/99999=61.52字节，平均每条记录
/8060约等于763,表中有一些非存储数据本身占用的空间，

预读数据，可以通过如下方式得到，即系统内置表函数，

select page_count from sys.dm_db_index_physical_stats(DB_ID('MyRedisTest'),OBJECT_ID('PersonTenThousand'),null,null,'sampled')
就是要读取页总数，

 

 语句优化，重点是优化逻辑读取次数，减少iO次数，提升性能，至于具体如何优化，减少逻辑读次数，博主后面再慢慢分享