优化SQLServer——表和分区索引(二)

优化SQLServer——表和分区索引(二)

简介

    之前一篇简单的介绍了语法和一些基本的概念,隔了一段时间,觉得有必要细致的通过实例来总结一下这部分内容。如之前所说,分区就是讲大型的对象(表)分成更小的块来管理,基本单位是行。这也就产生了很大优势, 比如在数据库维护备份还原操作的时候,比如在大量用户访问能导致死锁的时候等等。

接下来我们通过大量实例从分区到展示分区的效果以及一些实际案例来提高对这部分知识的理解。

–******************
–1.创建分区函数
–******************

–Create the partition function: dailyPF
DECLARE @StartDay DATE=DATEADD(dd,-3,CAST(SYSDATETIME() AS DATE));
CREATE PARTITION FUNCTION DailyPF (DATETIME2(0))
    AS RANGE RIGHT FOR VALUES
    (@StartDay, DATEADD(dd,1,@StartDay), DATEADD(dd,2,@StartDay), 
        DATEADD(dd,3,@StartDay), DATEADD(dd,4,@StartDay) );
GO

范围分区函数指定范围的边界,left和right关键字指定当数据库引擎按照剩余从左到右对区间值进行排序是,边界值属于那一边,默认为left。分区范围不能有间隔。

–******************
–2. 创建文件组

–******************

ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG1
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG2
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG3
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG4
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG5
GO
ALTER DATABASE PartitionThis ADD FILEGROUP DailyFG6
GO

这里我们建立6个文件组,同时也可以为文件组创建文件,

创建好的文件和文件组文件

接下来我为文件组创建分区方案:

 

–******************
–3. 创建分区架构
–******************


CREATE PARTITION SCHEME DailyPS
    AS PARTITION DailyPF
    TO (DailyFG1, DailyFG2, DailyFG3, DailyFG4, DailyFG5, DailyFG6);

 

–******************
–4. 在分区架构上建表
–******************

if OBJECT_ID(‘OrdersDaily’,’U’) is null
CREATE TABLE OrdersDaily (
    OrderDate DATETIME2(0) NOT NULL,
    OrderId int IDENTITY NOT NULL,
    OrderName nvarchar(256) NOT NULL
) on DailyPS(OrderDate)
GO

这里我们将分区函数映射到单个文件组里面,调用我们之前建立的分区函数即可。然后接着创建表在分区文件上,同时应用分区函数在

OrderDate时间上。这里我们还需要插入一部分测试数据便于观察,同时创建一个架构便于查询分区

–*******************************
–创建架构
–*******************************

–Create a schema for “partition helper” objects
CREATE SCHEMA [ph] AUTHORIZATION dbo;
GO

–插入测试数据
INSERT OrdersDaily(OrderDate, OrderName)
SELECT DATEADD(ss, t.N, DATEADD(dd,-3,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,
    CASE WHEN t.N % 3 = 0 THEN ‘Robot’ WHEN t.N % 4 = 0 THEN ‘Badger’  ELSE ‘Pen’ END AS OrderName
FROM ph.tally AS t–tally是一个1到10万自增长的表,只有一个字段 N

WHERE N < = 1000;
   

INSERT OrdersDaily(OrderDate, OrderName)
SELECT DATEADD(ss, t.N, DATEADD(dd,-2,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,
    CASE WHEN t.N % 3 = 0 THEN ‘Flying Monkey’ WHEN t.N % 4 = 0 THEN ‘Junebug’  ELSE ‘Pen’ END AS OrderName
FROM ph.tally AS t
WHERE N < = 2000;

INSERT OrdersDaily(OrderDate, OrderName)
SELECT DATEADD(ss, t.N, DATEADD(dd,-1,CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0)))) AS OrderDate,
    CASE WHEN t.N % 2 = 0 THEN ‘Turtle’ WHEN t.N % 5 = 0 THEN ‘Eraser’  ELSE ‘Pen’ END AS OrderName
FROM ph.tally AS t
WHERE N < = 3000;

INSERT OrdersDaily(OrderDate, OrderName)
SELECT DATEADD(ss, t.N, CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME2(0))) AS OrderDate,
    CASE WHEN t.N % 3 = 0 THEN ‘Lasso’ WHEN t.N % 2 = 0 THEN ‘Cattle Prod’  ELSE ‘Pen’ END AS OrderName
FROM ph.tally AS t
WHERE N < = 4000;
GO

随即在创建相关的索引

–******************
–7. 创建索引
–******************
–添加聚集索引
ALTER TABLE OrdersDaily
ADD CONSTRAINT PKOrdersDaily
    PRIMARY KEY CLUSTERED(OrderDate,OrderId)
GO

–对齐索引

CREATE NONCLUSTERED INDEX NCOrderIdOrdersDaily
    ON OrdersDaily(OrderId)
GO

–非对齐索引
CREATE NONCLUSTERED INDEX NCOrderNameOrdersDailyNonAligned
    ON OrdersDaily(OrderName) ON [PRIMARY]
GO

此时建立分区文件数据等条件后,我们可以看一下相应的文件及数据的情况,可以同过如下DMV来查看

SELECT  SCHEMA_NAME(so.schema_id) AS schema_name ,
        OBJECT_NAME(p.object_id) AS object_name ,
        p.partition_number ,
        p.data_compression_desc ,
        dbps.row_count ,
        dbps.reserved_page_count * 8 / 1024. AS reserved_mb ,
        si.index_id ,
        CASE WHEN si.index_id = 0 THEN ‘(heap!)’
                ELSE si.name
        END AS index_name ,
        si.is_unique ,
        si.data_space_id ,
        mappedto.name AS mapped_to_name ,
        mappedto.type_desc AS mapped_to_type_desc ,
        partitionds.name AS partition_filegroup ,
        pf.name AS pf_name ,
        pf.type_desc AS pf_type_desc ,
        pf.fanout AS pf_fanout ,
        pf.boundary_value_on_right ,
        ps.name AS partition_scheme_name ,
        rv.value AS range_value
FROM    sys.partitions p
JOIN    sys.objects so
        ON p.object_id = so.object_id
            AND so.is_ms_shipped = 0
LEFT JOIN sys.dm_db_partition_stats AS dbps
        ON p.object_id = dbps.object_id
            AND p.partition_id = dbps.partition_id
JOIN    sys.indexes si
        ON p.object_id = si.object_id
            AND p.index_id = si.index_id
LEFT JOIN sys.data_spaces mappedto
        ON si.data_space_id = mappedto.data_space_id
LEFT JOIN sys.destination_data_spaces dds
        ON si.data_space_id = dds.partition_scheme_id
            AND p.partition_number = dds.destination_id
LEFT JOIN sys.data_spaces partitionds
        ON dds.data_space_id = partitionds.data_space_id
LEFT JOIN sys.partition_schemes AS ps
        ON dds.partition_scheme_id = ps.data_space_id
LEFT JOIN sys.partition_functions AS pf
        ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS rv
        ON pf.function_id = rv.function_id
            AND dds.destination_id = CASE pf.boundary_value_on_right
                                        WHEN 0 THEN rv.boundary_id
                                        ELSE rv.boundary_id + 1
                                    END

查询结果如图:

分区表

可以发现按照日期的分布产生了不同文件组的数据插入到了不同的文件里面和索引里面了。

接下来我们通过分区切换来更好的理解分区的意义,首先要建立新的文件组(DailyF7)来切换分区,同时创建一个分区表OrdersDailyLoad,并向这个表里面插入5000条数据创建索引等以上的操作单独对此表进行一遍重复操作,来实现对新分区的新标的对齐。注意5000条数据一定要在指定范围内,比如使用check约束使数据在11.30-12.1日内的数据。

 

代码:

在切换之前我们一定要禁用或者删除掉这个分区的对其的索引
ALTER INDEX NCOrderNameOrdersDailyNonAligned ON OrdersDaily DISABLE;
GO
ALTER TABLE OrdersDailyLoad
SWITCH TO OrdersDaily PARTITION 6;
GO

如图,分区切换后文件组6中变为了5000条数据,而7中变为了空。

image

如果需要切换回来执行

ALTER TABLE PARTITION 6

SWITCH TO OrdersDaily OrdersDailyLoad ;

GO

如果需要合并分区

ALTER PARTITION FUNCTION DailyPF ()
        MERGE RANGE (‘2015-11-27 00:00:00.000’)

结果:此界点两个分区将合并为一个

 

 

 

总结:

           通过以上代码和实例的展示,我们能了解如何使用分区。同时我们要知道分区的意义。但是要知道分区也是一把双刃剑,它可以看做是一个性能选项、管理选项、可扩展工具,在提高数据查询、维护性能的同时也对数据库的备份还原策略、索引的维护、并发性以及变分区锁等有副作用,所以具体是否选用表分区要根据实际情况来判断,然后推荐一个工具(DataBase Tuning Adcisor)运行工作负载来提供是否分区的建议。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请联系我们举报,一经查实,本站将立刻删除。

发布者:全栈程序员-站长,转载请注明出处:https://javaforall.net/119737.html原文链接:https://javaforall.net

(0)
全栈程序员-站长的头像全栈程序员-站长


相关推荐

  • c语言入门教程–-4运算符

    c语言入门教程–-4运算符

    2021年3月12日
    222
  • Keil系列教程(汇总)「建议收藏」

    Keil系列教程(汇总)「建议收藏」推荐分享一个大神的人工智能教程。零基础!通俗易懂!风趣幽默!还带黄段子!希望你也加入到人工智能的队伍中来!http://www.captainbed.net/strongerhuang我的网站:https://www.strongerhuang.com我的知乎:https://www.zhihu.com/people/strongerHuang.com推荐在我公众…

    2022年5月24日
    37
  • int转long类型[通俗易懂]

    int转long类型[通俗易懂]一.将long型转化为int型,这里的long型是基础类型:longa=10;intb=(int)a;二.将Long型转换为int型的,这里的Long型是包装类型:Longa=10;intb=a.intValue();三.将int型转化为long型,这里的int型是基础类型:inta=10;longb=(int)a;四.将Integer…

    2022年6月6日
    298
  • HD地址批量生成 java

    HD地址批量生成 java前言:HD钱包全称为是分层确定性(HierarchicalDeterministic)钱包的缩写HDWallets。首次创建HD钱包或者备份钱包时,会产生一个助记词,助记词是一连串的英⽂单词,这一串单词序列就可以创建种子,种子又可以创建所有的私钥。单词顺序也是钱包的备份,可以恢复钱包。而种⼦对应的就是所确定性钱包的随机数。HD钱包的优点在于只需要主公钥,…

    2022年7月8日
    42
  • 风控模型评估

    风控模型评估  本文总结了一下评分卡建模过程中常用的模型评估方法,并结合代码展示,理论结合实际,方便初学者对模型评估的方法有深入的理解。之前写过一篇模型评估的指标,偏于理论,详情见风控模型指标详解。1.AUC  AUC值指的是ROC曲线下面积大小,该值能够量化反映基于ROC曲线衡量的模型性能。所以,需要了解ROC曲线的绘制方法。  首先,需要了解TPR(真阳性率)和FPR(假阳性率)。TPR就是P个正…

    2022年4月30日
    146
  • emc测试标准有哪些_emc标准体系

    emc测试标准有哪些_emc标准体系电波暗室,用于模拟开阔场,同时用于辐射无线电骚扰(EMI)和辐射敏感度(EMS)测量的密闭屏蔽室。来源:http://gememc.com/upload/201712/20171201093022763.jpg文章目录0.前言1.什么是EMC测试2.EMC标准的发布和管理者3.GB/T17626.X系列国家标准4.EMC试验的选择5.静电放电抗扰度试验6.浪涌(冲击)抗扰度试验7.电压…

    2025年9月19日
    5

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

关注全栈程序员社区公众号