SqlServer教程

sql server 为千万级数据量的表建立分区效果如何

本文主要是介绍sql server 为千万级数据量的表建立分区效果如何,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

1.数据库所在服务器信息

2.表数据量截图

declare @table_spaceused table (name nvarchar(100) ,rows int ,reserved nvarchar(100) ,data nvarchar(100) ,index_size nvarchar(100) ,unused nvarchar(100) ) 
insert into @table_spaceused (name,rows,reserved,data,index_size,unused ) exec sp_MSforeachtable @command1='exec sp_spaceused ''?''' 
select * from @table_spaceused order  by  rows desc

3.表索引截图

4.建立分区前查询数据所用时间

 4.1准备查询语句

查询3月26日至4月1日前的数据:查询3月份内的数据

select * from t_dd_qm_quality_collection_final where collection_time >='2022-03-26' and collection_time<'2022-04-01'

查询3月26日至4月11日前的数据:查询3、4月份内的数据

select * from t_dd_qm_quality_collection_final where collection_time >='2022-03-26' and collection_time<'2022-04-11'

查询3月26日至5月11日前的数据:查询3、4、5月份内的数据

select * from t_dd_qm_quality_collection_final where collection_time >='2022-03-26' and collection_time<'2022-05-11'

查询3月26日至6月11日前的数据:查询3、4、5、6月份内的数据

select * from t_dd_qm_quality_collection_final where collection_time >='2022-03-26' and collection_time<'2022-06-11'

4.2清除所有缓存

DBCC DROPCLEANBUFFERS

4.3重启sqlserver服务

4.4执行查询语句记录时间(毫秒),每次查询前清除缓存,重启服务器

  初期清除缓存重启服务后的CPU30%,内存47%。

  查询3月份内的数据 查询3、4月份内的数据 查询3、4、5月份内的数据(未测试) 查询3、4、5、6月份内的数据(未测试)
查询结果数据量 时间 峰值CPU 峰值内存 查询结果数据量 时间 峰值CPU 峰值内存 查询结果数据量 时间 峰值CPU 峰值内存 查询结果数据量 时间 峰值CPU 峰值内存
第一次 856150 209190 67 77 2160486 249617  65  78                
第二次 856150 229190 67 77 2160486 250946  89  78                
第三次 856150 228963 68 77 2160486 250230  77  77                
平均值 856150 222448 67.5 77 2160486 250264  77  77.7                
    22653 67 51   56160   80  59                
    22664 73 52    56230   89  59                
    22910 73 53   55890  71  58                
平均值                                

 

5.为表创建分区

5.1经查询表中最早的时间是2021-09月,最晚的时间是2022-06月。本表只留近90天内的数据,其余符合历史化条件的均已历史化。2022-03月前的数据有10万+条,故可以分4个区,3月份前一个区,3月份数据一个区,4月份数据一个区,5月份数据一个区,6月份及以后一个区。

5.2创建数据库文件组:选择数据库进入属性页面,选择文件组点击添加文件组,依次录入5个文件组名称,点击确定。

 

 5.3创建数据库文件:选择数据库进入属性页面,选择文件点击添加文件,依次录入5个文件名称并选择相应的文件组,点击确定。

 5.4创建分区:选择要创建分区的表,右键选择存储-->创建分区,进入创建分区向导。

5.4.1选择分区列:选择采集时间collection_time

 

5.4.2创建分区函数

5.4.3创建分区方案

 

 5.4.4映射分区范围选择左边界,文件组中依次选择原先创建的文件组,点击设置边界,开始日期输入20220301,结束日期输入20220601,日期范围选择每月,确定后会按月依次填充到边界一列中。之后点击下一步

预计存储控件按钮不要随便点,若表内数据量小还可以,计算出来不耗时,若表内数据量大会执行很长时间。

 

 5.4.5选择输出模板,表内数据量小可以立即执行,表内数据量大选择创建脚本,之后再编辑器内执行。我这里选择创建脚本。之后点击完成。

 

 5.4.6编辑器内点击执行,创建分区。1387万的数据,脚本方式执行创建分区耗时40分钟。

 6.建立分区后查询数据所用时间

 

这篇关于sql server 为千万级数据量的表建立分区效果如何的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!