SQL Server 强大的分区技术优化执行计划索引实例详解(使用语句检测和优化数据库 （MSSQL个人笔记之数据库优化之路 四）
--SQL Server 强大的分区技术(使用语句检测和优化数据库 （MSSQL个人笔记之数据库优化之路 三） /******************************************************************************** *主题：SQL Server 强大的分区技术 *说明：本文是个人学习的一些笔记和个人愚见 * 有很多地方你可能觉得有异议，欢迎一起讨论 *作者：Stephenzhou(阿蒙) *日期: 2012.07.26 *Mail:szstephenzhou@163.com *另外：转载请著名出处。 **********************************************************************************/ /*昨天已经在900W的测试数据上做了分区 并且查询出分区的内容说到优化肯定是要设计到索引了，索引在第一章已经说了很多，在这里不做详解 今天就随便说下分区 索引和执行计划如果事件允许的话我想再把追踪一起结合放到这个案例上来说说，以完成自己对MSSQL的资料的整理和感悟吧。*/--继续上昨天的测试数据表如下：use Erp_Systemgoif OBJECT_ID('consume') is not nulldrop table consumegocreate table consume (id varchar(50) , Shopid int, GoodsId int, Amount float, ConsumeDate datetime, mark nvarchar(100) constraint [pk_cludered_id_date] primary key clustered (id asc,ConsumeDate asc,mark) ) declare @myid uniqueidentifier declare @i intset @i=0;declare @id varchar(50);begin while @i<3000000 begin set @myid=cast(newid() as nvarchar(100));set @id='10010xxxxxxx1'insert into consume values(@id,cast(rand()*10 as int),cast(rand()*50 as int),cast(rand()*1000 as int) ,GETDATE(),@myid); set @i=@i+1 end; set @i=0 while @i<3000000 begin set @id='10010xxxxxxx2'set @myid=cast(newid() as nvarchar(100));insert into consume values(@id,cast(rand()*10 as int),cast(rand()*50 as int),cast(rand()*1000 as int) ,GETDATE(),@myid); set @i=@i+1 end; set @i=0 while @i<3000000 begin set @id='10010xxxxxxx3'set @myid=cast(newid() as nvarchar(100));insert into consume values(@id,cast(rand()*10 as int),cast(rand()*50 as int),cast(rand()*1000 as int) ,GETDATE(),@myid); set @i=@i+1 end; end; --以上在表中插入随机数据和日期一共900w条数据 方便大家做海量数据的测试，我的笔记本估计跑的很慢 所以要边做别写这个案例 --时间有点稍长了点。 呵呵 继续 ---有了数据后我在后面的写的时候就不做过多的解释 ,如果有童鞋不明白的地方可以翻看之前笔记二和笔记一 use Erp_System goalter database Erp_System add filegroup GF_System01alter database Erp_System add filegroup GF_System02alter database Erp_System add filegroup GF_System03alter database Erp_System add filegroup GF_System04 go alter database Erp_System add file ( name =N'System_01_02', filename='E:\GroupFileData\System_01_02.ndf', size =5mb,maxsize=2gb ,filegrowth=5mb ) to filegroup GF_System01 alter database Erp_System add file ( name =N'System_03_05', filename='E:\GroupFileData\System_03_05.ndf', size =5mb,maxsize=2gb ,filegrowth=5mb ) to filegroup GF_System02 alter database Erp_System add file ( name =N'System_06_08', filename='E:\GroupFileData\System_06_08.ndf', size =5mb,maxsize=2gb ,filegrowth=5mb ) to filegroup GF_System03 alter database Erp_System add file ( name =N'System_09_10', filename='E:\GroupFileData\System_09_10.ndf', size =5mb,maxsize=2gb ,filegrowth=5mb ) to filegroup GF_System04 -- select top 1 * from consume create partition function Par_Shopid_Fuc(int) as range right for values(2,5,8); create partition scheme Shopid_Sec as partition Par_Shopid_Fuc to ( GF_System01,GF_System02,GF_System03,GF_System04); go if OBJECT_ID('consume_Shopid_Range') is not nulldrop table consume_Shopid_Rangegocreate table consume_Shopid_Range ( id varchar(50), Shopid int, GoodsId int, Amount float, ConsumeDate datetime, mark nvarchar(100) )on Shopid_Sec(Shopid) --导数据了 insert into consume_Shopid_Range select * from consume --好了数据完全导入到consume_Shopid_Range表中了 SET STATISTICS IO ON -- 显示有关由Transact-SQL 语句生成的磁盘活动量的信息 select * from consume_Shopid_Range where Shopid=2 select * from consume where Shopid=2 /*(899690 行受影响)表 'consume_Shopid_Range'。扫描计数 1，逻辑读取 41528 次，物理读取 1 次，预读 30970 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。(899690 行受影响)表 'consume'。扫描计数 3，逻辑读取 268352 次，物理读取 3681 次，预读 260463 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。*/
分析上面的查询消息 其中表 'consume_Shopid_Range'
执行的扫描1次;
从数据缓存读取的41528页;
从磁盘读取的1页;
为进行查询而放入缓存的30970页数
而原表consume同样的查询
执行的扫描3次;
从数据缓存读取的268352页;
从磁盘读取的3681页;
为进行查询而放入缓存的260463页数
这样分析下对比哪个的数据快就不言而喻了
具体的查询时间如下
dbcc freeproccache --清空所有数据库的高速缓存select * from consume_Shopid_Range where Shopid=2
dbcc freeproccache --清空所有数据库的高速缓存select * from consume where Shopid=2
效果出来了做了分区后查询时间为' 00:00:35' 而没有做分区的原来那张表的查询时间为 '00:01:21'
其中前面的操作都很明确了 现在再次详细的说下，原表consume 中有索引，而在consume_Shopid_Range 中没有索引 数据是从表consumecopy过来的，值是在这个表里应用了分区方案 Shopid_Sec，分区方案的详细介绍 看上一篇笔记，看到以上的查询消息就知道差距了，按住选择以上的两个查询 Ctrl +L 如下图
如上图可以看出差距了吧。做了分区的查询开销 12% ，原表consume的查询却是88%
看下图具体分析下查询计划
选择 select * from consume_Shopid_Range where Shopid=2 按住 Ctrl+L
如果对于一个SQL查询有多种写法，那么这四个值中的逻辑读(logical reads)决定了哪个是最优化的。
--查询2是par_shopid分区函数的第几个分区select $partition.Par_Shopid_Fuc(2)-------------2--(1 行受影响)--查询每个分区对应的记录数。select $partition.Par_Shopid_Fuc(Shopid) as partitionnumber,COUNT(1) recordcount from consume_Shopid_Rangegroup by $partition.Par_Shopid_Fuc(Shopid) /*partitionnumber recordcount--------------- -----------1 17995262 26992803 27015694 1799625*//**为什么记录书不是一样的呢？？看下之前的分区函数* * create partition function Par_Shopid_Fuc(int)* as range right for values(2,5,8);*分区的点为 2，5，8。 通过select distinct shopid from consume 可以得到shopid-----------0123456789(10 行受影响)select $partition.Par_Shopid_Fuc(4)也就是说shopid的记录是0-9一共十个.其中 分区函数 as range right for values(2,5,8);已经说的很明白了数据的分割点是小于2的shopid放在第一个分区中0,1大于等于2小于 5的shopid放在第二个分区中也就是3,4 大于等于5小于 8的shopid记录放在第三个分区中 也就是5,6,7 大于等于8的shopid记录放在第四个分区中 也就是 8,9忘记了 因为不相等的记录也不是他们的分配的决定的 也就是说他们之前的分配是随机的.这样做就是说以后分区不能按照随机不能确定他的大小来分区现在说下索引吧.在分区表consume_Shopid_Range的Amount上建索引如上的两个查询 */create index IX_Amount ON consume_Shopid_Range(Amount) --分区表名ON Shopid_Sec(Shopid) --分区表方案
鼠标点击表和新建的IX_Amount属性如下图
为了形成对比 把分区表和原表对比下 把原表也一样建下这个索引然后看下计划如下
create index IX_Amount ON consume(Amount)
然后看下这两条查询的计划
select * from consume_Shopid_Range where Amount=667
select * from consume where Amount=667
如图可以看出来两个居然差不多了是为什么呢？？
具体分析下这两个执行计划：
两个索引关联查询。虽然如图所说的IX_Amount索引开销显示0%但是还是有所开销的 执行了一次 具体看下面 ：
上面显示执行次数为1次也就是通过我们刚建的这个IX_Amount索引一次定位到了，然后通过下面
我们给上边的查询在 列上指定过滤条件（分区表），那么就得到了一个使用RID查找的执行计划。
--拆分和合并区 --一般用的很少但是还是要了解下 alter partition function Par_Shopid_Fuc() split range(9) --合并分区 alter partition function Par_Shopid_Fuc() merge range (9) --分区表中的数据迁移 --1。从分区表赋值到数据到普通表 create table consume_part01 (id varchar(50) , Shopid int, GoodsId int, Amount float, ConsumeDate datetime, mark nvarchar(100) ) on GF_System01 create table consume_part02 (id varchar(50) , Shopid int, GoodsId int, Amount float, ConsumeDate datetime, mark nvarchar(100) ) on GF_System02 --把第一个分区放到表 consume_part01中 alter table consume_Shopid_Range switch partition 1 to consume_part01 go alter table consume_Shopid_Range switch partition 2 to consume_part02 go --2.把表中的数据拷贝到分区表中 要把分区表的索引要先删除不然的话就会错误提示。。说创建了相同的索引 alter table consume_part01 drop constraint ck_Amount alter table consume_part01 add constraint ck_Amount check(Amount in (0,1,2)) alter table consume_part01 switch to consume_Shopid_Range partition 1 go /* 分区表中的字段有索引所提提示错误。。 消息 4947，级别 16，状态 1，第 1 行ALTER TABLE SWITCH 语句失败。对于目标表 'Erp_System.dbo.consume_Shopid_Range' 中的索引 'IX_Amount'，在源表 'Erp_System.dbo.consume_part01' 中没有完全相同的索引。*/
分区视图
分区视图和分区表的区别：分区表是一个独立的表，值是他的不通分区的数据保存在不通的文件组中，而分区视图实际上包括多个物理表，每个表包含一部分的数据 由
分区视图把这些表组合在一起，形成完整的数据。废话不多说 上测试数据
create table consume_vewt01 (id varchar(50) , Shopid int, GoodsId int, Amount float check(Amount in (0,1)), ConsumeDate datetime, mark nvarchar(100) ) create table consume_vewt02 (id varchar(50) , Shopid int, GoodsId int, Amount float check(Amount in (2,3,4)), ConsumeDate datetime, mark nvarchar(100) ) create table consume_vewt03 (id varchar(50) , Shopid int, GoodsId int, Amount float check(Amount in (5,6,7)), ConsumeDate datetime, mark nvarchar(100) ) create table consume_vewt04 (id varchar(50) , Shopid int, GoodsId int, Amount float check(Amount in (8,9)), ConsumeDate datetime, mark nvarchar(100) )
创建分区视图
--创建分区视图 use Erp_System go create view consumeview as select * from consume_vewt01 union all select * from consume_vewt02 union all select * from consume_vewt03 union all select * from consume_vewt04 go
值得说明的一点是
1.分区所有成员表必须定义主键而且一致 ，所有的数据类型和字段必须一致。
2.在插入数据的时候 ，必须包含所有列 即使有的为null或者defual值了 还是显示的写出来。
3.在定义表的时候需要写约束check 这样才操作分区视图的时候就会自动的关联到成员表中去了
在分区视图中增加数据 insert into consumeview values('10010xxxxxxx3',2,13,2,'2012-07-26 11:55:40.170',' 264234B3-76E8-4833-9A9B-1B913815335C') insert into consumeview values('10010xxxxxxx3',2,13,2,'2012-07-26 11:55:40.170',' 我是中国人') insert into consumeview values('10010xxxxxxx3',2,13,2,'2012-07-26 11:55:40.170',' stephenzhou')select * from consumeview /*id Shopid GoodsId Amount ConsumeDate mark-------------------------------- ----------- ----------- ---------------------- ----------------------- ---------------------------------------10010xxxxxxx3 2 13 2 2012-07-26 11:55:40.170 264234B3-76E8-4833-9A9B-1B913815335C10010xxxxxxx3 2 13 2 2012-07-26 11:55:40.170 stephenzhou10010xxxxxxx3 2 13 2 2012-07-26 11:55:40.170 我是中国人10010xxxxxxx3 2 21 4 2012-07-26 11:55:40.170 stephenzhou10010xxxxxxx3 2 13 7 2012-07-26 11:55:40.170 我是中国人(5 行受影响)表 'consume_vewt04'。扫描计数 1，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'consume_vewt03'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'consume_vewt02'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'consume_vewt01'。扫描计数 1，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。*/update consumeview set GoodsId=21 where ConsumeDate='2012-07-26 11:55:40.170' and Amount=4/*表 'consume_vewt04'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'consume_vewt03'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'consume_vewt02'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'consume_vewt01'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。(1 行受影响)*/delete from consumeview where Amount =2 and mark=' stephenzhou'/*表 'consume_vewt04'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'consume_vewt03'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'consume_vewt02'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'consume_vewt01'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。(1 行受影响)*/
可以看到的时候每次在操作这个视图的都会去扫描四个成员表。。
好了 今天的实例应该也差不多了。可能有很多东西没有说的很全面 希望大家一起探讨。。。。