MySql，Sql Server分区技术浅析-原创手记-慕课网

一.MySql

MySql在5.1以后的版本中加入了分区技术，其不同于以往的分表技术，之前的分表技术是把一张大的表水平（按照一定的逻辑）分成多张表，比如如果我们的User表中有1000万条数据，那如果放在一张表里面去查询，绝对是坑爹的行为，更别提再去进行增删改。如果在加上索引，撑爆内存是难免的。所以才有了之后的分表技术，比如把用户名是a开头的用户放入一张表里面，这样可以减少该表的数据量，但是同样这个在应用层上也需要对系统进行优化，比如当我查询“abc”这个用户时，我知道他的信息在user1这个表里，然后与之对应的select语句就要发生相应的变化。当然进行水平分表时也不一定仅仅按照用户名的首字母来匹配对应相应的存储表，应根据信息得不同建立相应的逻辑对应关系。发现自己扯得有点远……。咱们回过头来说一下MySql5.1版本之后的的分区技术，不过还要提一下之前的分表技术，多大表进行拆分后，其相应的子表（暂且这么称呼）在逻辑上是变化的，这就体现在我们查询sql语句的不同上，那有没有一种技术可以在逻辑上保持原状，仅仅在物理结构上发生变化呐？这就是我们要提到的MySql的分区技术。对应用程序而言，他还是一张表，这样可以在逻辑层上屏蔽我们之前遇到的复杂查询语句。

MySql5.1上有5种分区类型，下面就让我们一个个的来瞅瞅看：

1）RANGE分区（经常使用）：

基于属于一个给定连续区间的列值，把多个行分配给分区；

例：假定你创建了一个如下表，该表保存了20家音像店的职员记录，这20家音像店的编号从1到20，你想把不同时期离职的职员的信息分别存储，那么你可以将字段separated（即离职时间）作为一个Key，则sql语句如下：

CREATE TABLE Employees（

　　Id INT NOT NULL,

　　Fname VARCHAR(30),

　　Iname VARCHAR(30),

　　Hired DATE NOT NULL DEFAULT ‘1990-01-01’,

　　Separated DATE NOT NULL DEFAULT ‘9999-12-31’,

　　Job_CODE INT,

　　Store_ID INT

）

PARTITION BY RANGE(YEAR(Separated ))(

　　PARTITION S0 VALUESLESS THAN(1991),

　　PARTITION S1 VALUESLESS THAN(1996),

　　PARTITION S2 VALUESLESS THAN(2001),

　　PARTITION S3 VALUESLESS THAN MAXVALUE,

);

PARTITION BYRANGE(YEAR(Separated ))(

……

)

就是根据Separate进行分组，上面代码表示离职年份在1991之前数据的存储在S0表中，其余的一次类推，相信以各位的智商应该能够看得懂。

2）LIST分区（一般使用）：

类似于RANGE分区，但是属于RANGE的一个特例，是基于列值匹配一个离散集合重的某个值来进行选择的，再清楚一点就是当表中列里的值是固定值时（性别：男，女）而且是枚举类型时，此时适合使用LIST分区；

例：假定你创建了一个如下的一个表，该表保存了20家音像店的职员记录，而这20家音像店分布在4个有经销权的地方，如下表：

地区

商店ID号

北区

3,5,6,9,17

东区

1,2,10,11,19,20

西区

4,12,13,14,18

中心区

7,8,15,16

则LIST分区语句为：

CREATE TABLE Employees（

　　Id INT NOT NULL,

　　Fname VARCHAR(30),

　　Iname VARCHAR(30),

　　Hired DATE NOT NULL DEFAULT ‘1990-01-01’,

　　Separated DATE NOT NULL DEFAULT ‘9999-12-31’,

　　Job_CODE INT,

　　Store_ID INT

）

PARTITION BY LIST(Store_ID)(

　　PARTITION Snorth VALUESLESS IN(3,5,6,9,17),

　　PARTITION Seast VALUESLESS IN(1,2,10,11,19,20),

　　PARTITION Swest VALUESLESS IN(4.12.13.14.18),

　　PARTITION Scentral VALUESLESS IN (7,8.15.16),

);

相信大家都看得懂，不做过多的解释。

3）HASH分区（较少使用）

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的那些行的列值进行计算，这个函数可以包含MySql重的有效的、产生非负整数值的任何表达式。其要根据该表所处的环境来衡量是否可用于该表，也就是在预先确定数目的分区中平均分布。

例：还是上面的那种表，那么现在我想把不同时期入职的员工分别进行存储，那我可以将日期字段Hired作为一个Key，sql语句如下：

CREATE TABLE Employees（

　　Id INT NOT NULL,

　　Fname VARCHAR(30),

　　Iname VARCHAR(30),

　　Hired DATE NOT NULL DEFAULT ‘1990-01-01’,

　　Separated DATE NOT NULL DEFAULT ‘9999-12-31’,

　　Job_CODE INT,

　　Store_ID INT

）

PARTITION BY HASH(YEAR(Hired))

PARTITIONS 4

;

也就是说根据Hired这个字段把数据平均分配到4个不同分区表中。注意：HASH中的值必须是整数所以使用到了YEAR函数。

4）KEY分区（很少使用）

类似于HASH分区，区别在于KEY分区只提供计算一列或多列，且MySql服务器提供其自身的哈希函数。与HASH不同的是它的Key可以不是整数类型，可以是字符串等字段，该分区使用不多，而且效率有些折扣，在此不再举例；

二.Sql Server

Sql Server在2005之后的版本引入的特性。这个特性允许逻辑上的表在物理上分成多个部分，之前所谓的分区表仅仅是分布式视图，也就是多个表做union视图，而真正的分区表是逻辑上一个表，物理上多个表，原理跟MySql分区表的概念基本一致。有一点值得注意的是分区函数并不具体属于分区架构和分区表，他们之间仅仅属于使用关系。

1）.定义分区表首先要定义分区函数，例如：

--创建分区函数

CREATE PARTITION FUNCTION fqPartition(DATE)

AS RANGE RIGHT

FOR VALUES('2010-01-01','2012-01-01')

--查看分区函数是否创建成功

Select * FROM SYS.PARTITION_FUNCTIONSGAI

该函数把时间分成了3个区域，2010-01-01之前是一个区域，2010-01-01~2012-01-01是一个区域，剩下的是一个区域。

2）.定义分区架构

定义完分区函数仅仅是到了如何将列的值区分到不同的分区中，而每个分区的存储方式则需要分区架构来定义，分区架构负责分配每个区属于那个文件组，而分区函数是决定了如何在逻辑上分区

--基于之前的分区函数创建分区架构

CREATE PARTITION SCHEME schemeForPartition

AS PARTITION fqPartition

--因为有3个区域，所以要指定3个文件组，也可以使用all 所有的区域指向一个文件组，不过这样没有太多意义

TO (fileGroup0,fileGroup1,fileGroup2)

--查看已经建立的分区架构

select * from sys.partition_schemes

3）.定义分区表

这个就不用过多解释了，就是我们的逻辑表，只不过显示当中都是某张表够大的时候才考虑采用分区表，但是当我们在刚刚建立时需要指定相关的特性，示例代码：

CREATE TABLE PTable(

ID INT,

ORDERID INT,

SALESDATE DATE

) ON schemeForPartition(SALESDATE)

--schemeForPartition指定分区架构根据的Key为SALESDATE就创建完成了

这样Sql Server数据库的分区表就创建完成了。。。

今天先写到这里，以后在对分区表进行深入的研究。

MySqlSql Server分区技术数据库