千万级SQLServer数据库表分区的实现

最近使用SQL SERVER一个的缓存，数据量一天100w的速度增长，同时接受客户查询，速度由于数据量越来越大越来越慢，这里感谢 KillKill 和邀约, 最近读了一套书不错，感兴趣的同学可以读读<<活法>>

三水网站制作公司哪家好，找创新互联！从网页设计、网站建设、微信开发、APP开发、响应式网站等网站项目制作，到程序开发，运营维护。创新互联2013年至今到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选创新互联。

回顾下经常使用的索引

一 .聚集索引

聚集索引的页级别包含了索引键，还包含数据页，因此，关于除了键值以外聚集索引的叶级别还存放了什么的答案就是一切，也就是说，每行的所有字段都在叶级别种。
另一种说话是:数据本身也是聚集索引的一部分，聚集索引基于键值保持表中的数据有序。

SQL SERVER 中，所有的聚集索引都是唯一的，如果在创建聚集索引时没有指定UNIQUE 关键字，SQL SERVER 会在需要时通过往记录中添加一个唯一标识符(Uniqueifier)在内部保证索引的唯一性，该唯一标识符是一个4字节的值，作为附加在聚集索引键的字段添加到数据中，只有那些声明为索引键字段并拥有重复值的行才会被添加。

二 .非聚集索引

对于非聚集索引，叶级别不包含全部的数据。除了键值以外，每个叶级别(树的最低层)中的索引行包含了一个书签(bookmark),告诉SQL Server 可以在哪里找到与索引键相应的数据行。一个书签课能有两种格式。如果表上存在聚集索引，书签就是相应的数据行的聚集索引键。如果表是堆(heap)结构，就是没有聚集索引的情况下，书签就是一个行标识符 row identifier,rid ，以文件号页号槽号的格式来定位实际的行。

非聚集索引的存在与否并不影响数据分页的组织，因此每张表上并不像聚集索引那样只局限于拥有一个非聚集索引，SQL Server 2005 每张表能够包含249 个非聚集索引 SQL Server 2008 每张表能够包含999 个非聚集索引，但是实际上所用到的比这个数要少的多。

三 .包含索引

索引键字段数量限制是16个，总共900个字节大小，包含性列只在叶级别中出现而且不以任何方式控制索引行的排序。它们的目的是使叶级别能够包含更多的信息从而更大地发挥覆盖索引(Covering index)的索引调优能力.覆盖索引是一种非聚集索引，在其叶级别就可以找到满足查询的全部信息，这样sql server就根本没有必要访问数据分页了，在一些情况下 sql serer 会悄悄的为索引添加一个包含性列。这可能发生在索引建立于分区表也就是我今天是发的博客 O(∩_∩)O (partitioned table )上没有指定 on filegroup 或者 no partition_scheme 的情况下。

一 .SQL SERVER 表分区介绍：

SQL Server 引入的表分区技术，让用户能够把数据分散存放到不同的物理磁盘中，提高这些磁盘的并行处理性能以优化查询性能……

二 .SQL SERVER 数据库表分区由三个步骤来完成:

1.创建分区函数

2.创建分区架构

3.对表进行分区

基于缓存更新机制，我使用时间来进行分区，这里大家根据业务的要求使用合适的字段来作为分区

创建数据库分区文件数量，这里存储一年的数据分成十二个分区，需要现在D盘建立好Data 的文件夹里面包含Primary 文件夹和 FG1 FG2 FG3 FG4............

 
 
 
 
  
  
  
  IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'AirAvCache')  
  
  
  
  DROP DATABASE [AirAvCache]  
  
  
  
  GO  
  
  
  
  CREATE DATABASE [AirAvCache]  
  
  
  
  ON PRIMARY 
  
  
  
  (NAME='Data Partition DB Primary FG',  
  
  
  
  FILENAME=  
  
  
  
  'D:\Data\Primary\AirAvCache Primary FG.mdf',  
  
  
  
  SIZE=5,  
  
  
  
  MAXSIZE=500,  
  
  
  
  FILEGROWTH=1 ),  
  
  
  
  FILEGROUP [AirAvCache FG1]  
  
  
  
  (NAME = 'AirAvCache FG1',  
  
  
  
  FILENAME =  
  
  
  
  'D:\Data\FG1\AirAvCache FG1.ndf',  
  
  
  
  SIZE = 5MB,  
  
  
  
  MAXSIZE=500,  
  
  
  
  FILEGROWTH=1 ),  
  
  
  
  FILEGROUP [AirAvCache FG2]  
  
  
  
  (NAME = 'AirAvCache FG2',  
  
  
  
  FILENAME =  
  
  
  
  'D:\Data\FG2\AirAvCache FG2.ndf',  
  
  
  
  SIZE = 5MB,  
  
  
  
  MAXSIZE=500,  
  
  
  
  FILEGROWTH=1 ),  
  
  
  
  FILEGROUP [AirAvCache FG3]  
  
  
  
  (NAME = 'AirAvCache FG3',  
  
  
  
  FILENAME =  
  
  
  
  'D:\Data\FG3\AirAvCache FG3.ndf',  
  
  
  
  SIZE = 5MB,  
  
  
  
  MAXSIZE=500,  
  
  
  
  FILEGROWTH=1 ),  
  
  
  
  FILEGROUP [AirAvCache FG4]  
  
  
  
  (NAME = 'AirAvCache FG4',  
  
  
  
  FILENAME =  
  
  
  
  'D:\Data\FG4\AirAvCache FG4.ndf',  
  
  
  
  SIZE = 5MB,  
  
  
  
  MAXSIZE=500,  
  
  
  
  FILEGROWTH=1 ),  
  
  
  
  FILEGROUP [AirAvCache FG5]  
  
  
  
  (NAME = 'AirAvCache FG5',  
  
  
  
  FILENAME =  
  
  
  
  'D:\Data\FG5\AirAvCache FG5.ndf',  
  
  
  
  SIZE = 5MB,  
  
  
  
  MAXSIZE=500,  
  
  
  
  FILEGROWTH=1 ),  
  
  
  
   
  
  
  
  FILEGROUP [AirAvCache FG6]  
  
  
  
  (NAME = 'AirAvCache FG6',  
  
  
  
  FILENAME =  
  
  
  
  'D:\Data\FG6\AirAvCache FG6.ndf',  
  
  
  
  SIZE = 5MB,  
  
  
  
  MAXSIZE=500,  
  
  
  
  FILEGROWTH=1 ),  
  
  
  
   
  
  
  
   
  
  
  
  FILEGROUP [AirAvCache FG7]  
  
  
  
  (NAME = 'AirAvCache FG7',  
  
  
  
  FILENAME =  
  
  
  
  'D:\Data\FG7\AirAvCache FG7.ndf',  
  
  
  
  SIZE = 5MB,  
  
  
  
  MAXSIZE=500,  
  
  
  
  FILEGROWTH=1 ),  
  
  
  
   
  
  
  
  FILEGROUP [AirAvCache FG8]  
  
  
  
  (NAME = 'AirAvCache FG8',  
  
  
  
  FILENAME =  
  
  
  
  'D:\Data\FG8\AirAvCache FG8.ndf',  
  
  
  
  SIZE = 5MB,  
  
  
  
  MAXSIZE=500,  
  
  
  
  FILEGROWTH=1 ),  
  
  
  
   
  
  
  
  FILEGROUP [AirAvCache FG9]  
  
  
  
  (NAME = 'AirAvCache FG9',  
  
  
  
  FILENAME =  
  
  
  
  'D:\Data\FG9\AirAvCache FG9.ndf',  
  
  
  
  SIZE = 5MB,  
  
  
  
  MAXSIZE=500,  
  
  
  
  FILEGROWTH=1 ),  
  
  
  
   
  
  
  
  FILEGROUP [AirAvCache FG10]  
  
  
  
  (NAME = 'AirAvCache FG10',  
  
  
  
  FILENAME =  
  
  
  
  'D:\Data\FG10\AirAvCache FG10.ndf',  
  
  
  
  SIZE = 5MB,  
  
  
  
  MAXSIZE=500,  
  
  
  
  FILEGROWTH=1 ),  
  
  
  
   
  
  
  
  FILEGROUP [AirAvCache FG11]  
  
  
  
  (NAME = 'AirAvCache FG11',  
  
  
  
  FILENAME =  
  
  
  
  'D:\Data\FG11\AirAvCache FG11.ndf',  
  
  
  
  SIZE = 5MB,  
  
  
  
  MAXSIZE=500,  
  
  
  
  FILEGROWTH=1 ),  
  
  
  
   
  
  
  
  FILEGROUP [AirAvCache FG12]  
  
  
  
  (NAME = 'AirAvCache FG12',  
  
  
  
  FILENAME =  
  
  
  
  'D:\Data\FG12\AirAvCache FG12.ndf',  
  
  
  
  SIZE = 5MB,  
  
  
  
  MAXSIZE=500,  
  
  
  
  FILEGROWTH=1 )

创建好后如图：

打开FG1 文件夹看到多了AirAvCacheFG1.ndf 文件

创建分区函数

 
 
 
 
  
  
  
  USE AirAvCache  
  
  
  
  GO   
  
  
  
   
  
  
  
  -- 创建函数  
  
  
  
   
  
  
  
  CREATE PARTITION FUNCTION [AirAvCache Partition  Range](DATETIME)  
  
  
  
  AS RANGE LEFT FOR VALUES ('2010-09-01','2010-10-01','2010-11-01',
  
  
  
  '2010-12-01','2011-01-01','2011-02-01','2011-03-01','2011-04-01',
  
  
  
  '2011-05-01','2011-06-01','2010-07-01');

创建分区架构

 
 
 
 
  
  
  
  CREATE PARTITION SCHEME [AirAvCache Partition Scheme]  
  
  
  
  AS PARTITION [AirAvCache Partition  Range]  
  
  
  
  TO ([AirAvCache FG1], [AirAvCache FG2], [AirAvCache FG3],[AirAvCache FG4],[AirAvCache FG5],[AirAvCache FG6],[AirAvCache FG7],[AirAvCache FG8],  
  
  
  
  [AirAvCache FG9],[AirAvCache FG10],[AirAvCache FG11],[AirAvCache FG12]);

创建一个使用AirAvCache Partitiion Scheme 架构的表

 
 
 
 
  
  
  
  CREATE TABLE [dbo].[AvCache](  
  
  
  
      [CityPair] [varchar](6)  NOT NULL,  
  
  
  
      [FlightNo] [varchar](10)  NULL,  
  
  
  
      [FlightDate] [datetime] NOT NULL,  
  
  
  
      [CacheTime] [datetime] NOT NULL   DEFAULT (getdate()),  
  
  
  
      [AVNote] [varchar](300)  NULL 
  
  
  
  )  ON [AirAvCache Partition Scheme] (FlightDate);   
  
  
  
  --注意这里使用[AirAvCache Partition Scheme]架构，根据FlightDate 分区

查询分区情况

 
 
 
 
  
  
  
  -- 查看使用情况   
  
  
  
  SELECT *, $PARTITION.[AirAvCache Partition  Range](FlightDate)  
  
  
  
  FROM dbo.AVCache

可以看到9 月和 10 月已经分开了。

当前题目：千万级SQLServer数据库表分区的实现
文章路径：http://www.gawzjz.com/qtweb2/news32/1382.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：创新互联

猜你还喜欢下面的内容