优化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中变为了空。

如果需要切换回来执行

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)运行工作负载来提供是否分区的建议。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏PHP在线

MYSQL 优化常用方法

1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得...

35540
来自专栏跟着阿笨一起玩NET

SQLServer中使用索引视图(物化视图)

物化视图:以前用的普通的视图,普通视图就是一段逻辑语句,对性能没有任何的提升,也不能创建索引,而物化视图会把视图里查询出来的数据在数据库上建立快照,它和物理表一...

24620
来自专栏.net core新时代

数据字典生成工具之旅(8):SQL查询表的约束默认值等信息

      上一篇代码生成工具里面已经用到了读取表结构的SQL,这篇将更加详细的介绍SQL SERVER常用的几张系统表和视图! 阅读目录 系统表视图介绍 实际...

21870
来自专栏我的博客

NodeJS操作Mysql示例

//config.js module.exports={ mysql:{ host:'localhost', port:3306, user:...

55180
来自专栏乐沙弥的世界

PL/SQL --> DML 触发器

何谓触发器?简言之,是一段命名的PL/SQL代码块,只不过该代码块在特定的条件下被触发并且执行。对于这样的代码我们称之为触发器

11030
来自专栏黑泽君的专栏

在命令行下,Mysql显示各个端所使用的字符集命令

9420
来自专栏守望轩

VS 2010 Database tool 查询分析器的小困扰

昨天用Visual Studio 2010 做一个程序,使用的是sql server 2005的数据库,使用Visual Studio 2010 的Datab...

19190
来自专栏SpringBoot

mybatis 批量更新问题

41110
来自专栏乐沙弥的世界

批量生成sqlldr文件,高速卸载数据

      SQL*Loader 是用于将外部数据进行批量高速加载的数据库的最高效工具,可用于将多种平面格式文件加载到Oracle数据库。SQL*Loader支...

21020
来自专栏跟着阿笨一起玩NET

SQL Server通过整理索引碎片和重建索引提高速度

本文章转载:http://database.51cto.com/art/201108/282408.htm

53210

扫码关注云+社区

领取腾讯云代金券