SQLSERVER 分区分表

SQLSERVER 分区分表

学习前提：要掌握文件和文件组的概念！你可以直接看大牛的博客详解：http://www.cnblogs.com/CareySon/archive/2011/12/30/2307766.html（他已经将的很详细了）,我这里只是做一点学习的总结。

1.特点：

1. 表分区这个特性只有在企业版或者开发版中才有

2. 使用它主要目的：把一个表中的数据分布不同的硬盘或其他存储介质中，会大大的提高查询速度

3. 存档，比如将销售记录中1年前的数据分到一个专门存档的服务器中

4. 便于管理，比如把一个大表分成若干个小表，则备份和恢复的时候不再需要备份整个表，可以单独备份分区

5. 提高可用性，当一个分区跪了以后，只有一个分区不可用，其它分区不受影响

6. 轻松管理大表或分区

7. 提高并发性（产生分区锁，而不是表锁）

8. 以文件组级别就行备份和还原，从而仅仅只备份和还原表的一部分（比如说只备份表中的热数据），从而减少了还原时间

9. 可以仅仅在线重建某个分区

10.对表分区后，也要对表上的非聚集索引进行索引分区

2.创建步骤

a.定义分区函数

b.定义分区构架

c.定义分区表(虽然在很多情况下都是你在发现已经表已经足够大的时候才想到要把表分区，但是分区表只能够在创建的时候指定为分区表)

3.具体的实例操作：

--定义分区函数
CREATE PARTITION FUNCTION FN_PARTITION(DATE)
AS RANGE RIGHT                              --这里有两个可选参数(right,left)
FOR VALUES('2004-01-01','2007-01-01')       --三个端   

--查看:
SELECT * FROM sys.partition_functions

--定义分区架构
CREATE PARTITION SCHEME SCH_PARTITION
AS PARTITION FN_PARTITION
TO (GROUP1,GROUP2,GROUP3)

--查看
SELECT * FROM sys.partition_schemes

--定义分区表:
CREATE TABLE ORDERINFO
(
 ID INT  IDENTITY(1,1), --这里暂时不能建立主键(索引),原因后面我再详解 PRIMARY KEY
 ORDERID VARCHAR(100),
 SALEDATE DATE                    --这个数据的类型 要和分区函数的数据类型一致滴呀
) ON SCH_PARTITION(SALEDATE)


  --测试 数据
  INSERT INTO ORDERINFO VALUES('110','2002-2-2')
  INSERT INTO ORDERINFO VALUES('111','2006-2-2')
  INSERT INTO ORDERINFO VALUES('112','2012-2-2')
  INSERT INTO ORDERINFO VALUES('112','2012-5-2')

查看数据分布的sql语句

select convert(varchar(50), ps.name) as partition_scheme,
p.partition_number, 
convert(varchar(12), ds2.name) as filegroup, 
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary, 
str(p.rows, 9) as rows
from sys.indexes i 
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id 
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id 
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id 
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id 
join sys.partition_functions pf on ps.function_id = pf.function_id 
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right 
WHERE i.object_id = object_id('ORDERINFO')
and i.index_id in (0, 1) 
order by p.partition_number

结果：

这里再分享一个；sql语句查看分区信息的存储过程。

  create procedure [dbo].[sp_show_partition_range]s
(
   @partition_table    nvarchar(255) = null
  ,@partition_function nvarchar(255) = null
)
as
begin
   set nocount on

   declare @function_id int
       set @function_id = null

   -- get @function_id base on @partition_table
   if len(@partition_table) > 0 begin
      select @function_id = s.function_id
        from sys.indexes i
                inner join sys.partition_schemes s
          on i.data_space_id = s.data_space_id
       where i.index_id < 2
         and i.object_id = object_id(@partition_table)
         
      if @function_id is null
         return 1         
   end

   -- get @function_id base on @partition_function
   if len(@partition_function) > 0 begin
      select @function_id = function_id
        from sys.partition_functions
       where name = @partition_function
       
      if @function_id is null
         return 1        
    end
   

   -- get partition range
   select partition_function = f.name
         ,t.partition
         ,t.minval
         ,value = case when f.boundary_value_on_right=1 then '<= val <' else '< val <=' end
         ,t.maxval
     from (
           select h.function_id
                 ,partition = h.boundary_id
                 ,minval    = l.value
                 ,maxval    = h.value
             from sys.partition_range_values h
                     left join sys.partition_range_values l
               on h.function_id = l.function_id and h.boundary_id = l.boundary_id + 1

           union all

           select function_id
                 ,partition = max(boundary_id) + 1
                 ,minval    = max(value)
                 ,maxval    = null
             from sys.partition_range_values
            group by function_id
          ) t
              inner join sys.partition_functions f
         on t.function_id = f.function_id
      where f.function_id = @function_id
         or @function_id is null
      order by 1, 2
end

sp_show_partition_range  'tableName'

View Code

4.分区表的分割

/*
   需要注意的：
   如果分割时，被分割的分区3内有内容需要分割到分区4，
   则这些数据需要被复制到分区4，并删除分区3上对应数据。
   这种操作非常非常消耗IO，并且在分割的过程中锁定分区三内的内容，
   造成分区三的内容不可用。不仅仅如此，
   这个操作生成的日志内容会是被转移数据的4倍！
*/
--第一步:需要知道分割后多出来的分区应当存储在那个文件组中

ALTER PARTITION SCHEME SCH_PARTITION NEXT USED 'MORE'

--第二部：添加分割点；
ALTER PARTITION FUNCTION FN_PARTITION()
SPLIT RANGE('2010-05-01')

5.分区表的合并

ALTER PARTITION FUNCTION FN_PARTITION()
SPLIT RANGE('2010-05-01')

/*
   需要注意的：
   2007 和 2010 在不同文件组中（一个在group3，一个在more）那么合并后
    归于那个文件组呢？
    这个需要看我们的分区函数定义的是left还是right.如果定义的是left.则由左边
   
*/


--只需 使用我们的 MERGE 参数；
ALTER PARTITION FUNCTION FN_PARTITION()
MERGE RANGE('2007-05-01')

关于分区分表，更多的内容，我还在总结中...........

敬请期待....