不懂事的小男孩

数据库分区分表（sql、mysql）

http://blog.csdn.net/lgb934/article/details/8662956

http://www.2cto.com/database/201503/380348.html

什么是分表？

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它

什么是分区？

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

表分区分为水平分区和垂直分区。水平分区将表分为多个表。每个表包含的列数相同，但是行更少。例如，可以将一个包含十亿行的表水平分区成 12 个表，每个小表表示特定年份内一个月或几个月的数据。任何需要特定月份数据的查询只需引用相应月份的表。而垂直分区则是将原始表分成多个只包含较少列的表。水平分区是最常用分区方式，后面我们以水平分区来介绍具体实现方法。

 简单一点说，分区表就是将一个大表分成若干个小表。

分区步骤:

创建分区表必须要经过下面五个步骤。

1）创建文件组

2）创建文件

3）创建分区函数

4）创建分区方案

5）创建分区表

（1）创建文件组，有两种方案，一种是通过手动添加，另外一种就是通过SQL脚本进行添加。下面以两种方案来说明：

       方案一：创建文件组，虽然这一步我们可以省略，因为我们可以直接使用Primary文件（也就是系统主文件）。但是为了方便管理，我们还是要创建几个文件组，这样可以将不同的小表（不同时间段，或者不同数据表）放在不同的文件组里，既便于理解又可以提高运行速度。

       打开SQL Server Management Studio，找到分区表所在的数据库，右键单击选择“属性”，选择“文件组”选项，单击下面的“添加”按钮，添加X个文件组

 方案二：通过查询分析器SQL脚本执行

              ALTER DATABASE CXFunSche ADD FILEGROUP CXFG2010

ALTER DATABASE CXFunSche ADD FILEGROUP CXFG2011

ALTER DATABASE CXFunSche ADD FILEGROUP CXFG2012

ALTER DATABASE CXFunSche ADD FILEGROUP CXFG2013

（2）创建数据库文件

    方案一：创建了文件组之后，还要再创建几个数据库文件。为什么要创建数据库文件，这很好理解，因为分区的小表必须要放在硬盘上，而放在硬盘上的什么地方呢？当然是文件里啦。再说了，文件组中没有文件，文件组还要来有啥用呢？还是在上图的那个界面，选择“文件”选项，然后添加几个文件。在添加文件的时候要注意以下几点：

    1、不要忘记将不同的文件放在文件组中。当然一个文件组中也可以包含多个不同的文件。

2、如果可以的话，将不同的文件放在不同的硬盘分区里，最好是放在不同的独立硬盘里。要知道IQ的速度往往是影响SQL Server运行速度的重要条件之一。将不同的文件放在不同的硬盘上，可以加快SQL Server的运行速度。

 

  方案二：通过查询分析器SQL脚本执行

    ALTER DATABASE CXFunSche ADD FILE ( NAME = N'SellLogDetail2010', FILENAME = N'D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail2010.ndf' , SIZE = 3072KB ,FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2010

ALTER DATABASE CXFunSche ADD FILE ( NAME = N'SellLogDetail201102', FILENAME = N'D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail201102.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2010

ALTER DATABASE CXFunSche ADD FILE ( NAME = N'SellLogDetail201104', FILENAME = N'D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail201104.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2011

ALTER DATABASE CXFunSche ADD FILE ( NAME = N'SellLogDetail201106', FILENAME = N'D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail201106.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2011

ALTER DATABASE CXFunSche ADD FILE ( NAME = N'SellLogDetail201108', FILENAME = N'D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail201108.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2011

ALTER DATABASE CXFunSche ADD FILE ( NAME = N'SellLogDetail201110', FILENAME = N'D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail201110.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2011

ALTER DATABASE CXFunSche ADD FILE ( NAME = N'SellLogDetail201112', FILENAME = N'D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail201112.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2011

ALTER DATABASE CXFunSche ADD FILE ( NAME = N'SellLogDetail201202', FILENAME = N'D:\program files\Programming Software\SQL Server 2005\MSSQL.1\MSSQL\DATA\SellLogDetail201202.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP CXFG2012

mysql分区的几种方式

Range：

1
2
3
4
5
6
7
8
9
10
create table range(
　　id int(11),
　　money int(11) unsigned not null,
　　date datetime
　　)partition by range(year(date))(
　　partition p2007 values less than (2008),
　　partition p2008 values less than (2009),
　　partition p2009 values less than (2010)
　　partition p2010 values less than maxvalue
)；

List：

1
2
3
4
5
6
7
create table list(
　　a int(11),
　　b int(11)
　　)(partition by list (b)
　　partition p0 values in (1,3,5,7,9),
　　partition p1 values in (2,4,6,8,0)
　);

Hash：

1
2
3
4
5
create table hash(
　　a int(11),
　　b datetime
　　)partition by hash (YEAR(b)
　　partitions 4;

Key：

1
2
3
4
5
create table t_key(
　　a int(11),
　　b datetime)
　　partition by key (b)
　　partitions 4;

分区管理

新增分区

1
2
ALTER TABLE sale_data
ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

删除分区
--当删除了一个分区，也同时删除了该分区中所有的数据。
ALTER TABLE sale_data DROP PARTITION p201010;

分区的合并
下面的SQL，将p201001 - p201009 合并为3个分区p2010Q1 - p2010Q3

1
2
3
4
5
6
7
8
9
ALTER TABLE sale_data
REORGANIZE PARTITION p201001,p201002,p201003,
p201004,p201005,p201006,
p201007,p201008,p201009 INTO
(
PARTITION p2010Q1 VALUES LESS THAN (201004),
PARTITION p2010Q2 VALUES LESS THAN (201007),
PARTITION p2010Q3 VALUES LESS THAN (201010)
);

分表的几种方式：

1、mysql集群

它并不是分表，但起到了和分表相同的作用。集群可分担数据库的操作次数，将任务分担到多台数据库上。集群可以读写分离，减少读写压力。从而提升数据库性能。

2、自定义规则分表

大表可以按照业务的规则来分解为多个子表。通常为以下几种类型，也可自己定义规则。

?
1
2
3
4
5
Range（范围）–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。
Hash（哈希）–这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
List（预定义列表）–这种模式允许系统通过预定义的列表的值来对数据进行分割。
Composite（复合模式） –以上模式的组合使用　
分表规则与分区规则一样，在分区模块详细介绍。

下面以Range简单介绍下如何分表（按照年份表）。

假设表结构有4个字段：自增id，姓名，存款金额，存款日期

把存款日期作为规则分表，分别创建几个表

2011年：account_2011

2012年：account_2012

……

2015年：account_2015

app在读写的时候根据日期来查找对应的表名，需要手动来判定。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
var getTableName = function() {
    var data = {
        name: 'tom',
        money: 2800.00,
        date: '201410013059'
    };
    var tablename = 'account_';
    var year = parseInt(data.date.substring(0, 4));
    if (year < 2012) {
        tablename += 2011; // account_2011
    } else if (year < 2013) {
        tablename += 2012; // account_2012
    } else if (year < 2014) {
        tablename += 2013; // account_2013
    } else if (year < 2015) {
        tablename += 2014; // account_2014
    } else {
        tablename += 2015; // account_2015
    }
    return tablename;
}

3、利用merge存储引擎来实现分表

merge分表，分为主表和子表，主表类似于一个壳子，逻辑上封装了子表，实际上数据都是存储在子表中的。

我们可以通过主表插入和查询数据，如果清楚分表规律，也可以直接操作子表。

子表2011年

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE `account_2011` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money`  float NOT NULL ,
`tradeDate`  datetime NOT NULL
PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=2
CHECKSUM=0
ROW_FORMAT=DYNAMIC
DELAY_KEY_WRITE=0
;

子表2012年

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE `account_2012` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money`  float NOT NULL ,
`tradeDate`  datetime NOT NULL
PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=2
CHECKSUM=0
ROW_FORMAT=DYNAMIC
DELAY_KEY_WRITE=0
;

主表，所有年

?
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `account_all` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`money`  float NOT NULL ,
`tradeDate`  datetime NOT NULL
PRIMARY KEY (`id`)
)
ENGINE=MRG_MYISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
UNION=(`account_2011`,`account_2012`)
INSERT_METHOD=LAST
ROW_FORMAT=DYNAMIC
;

创建主表的时候有个INSERT_METHOD，指明插入方式，取值可以是：0 不允许插入；FIRST 插入到UNION中的第一个表； LAST 插入到UNION中的最后一个表。

通过主表查询的时候，相当于将所有子表合在一起查询。这样并不能体现分表的优势，建议还是查询子表。

posted on 2017-08-17 13:55  不懂事的小男孩  阅读(4506)  评论(1)  编辑  收藏  举报