数据库分区分表（sql、mysql）
blog.csdn/lgb934/article/details/8662956
www.2cto/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
sql数据库迁移另一个硬盘
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 3create table range(
  id int(11),
  money int(11) unsigned not null,
4 5 6 7 8 9 10  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 7create 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 5create table hash(
  a int(11),
  b datetime
  )partition by hash (YEAR(b)  partitions 4;
Key：
1 2 3 4 5create table t_key(
  a int(11),
  b datetime)
  partition by key(b)  partitions 4;
分区管理新增分区
1 2ALTER 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 9ALTER 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 3Range（范围）–这种模式允许将数据划分不同范围。例如可以将⼀个表通过年份划分成若⼲个分区。
Hash（哈希）–这中模式允许通过对表的⼀个或多个列的Hash Key进⾏计算，最后通过这个Hash码不同数值对应的数据区域进⾏分区。例如可以建⽴⼀个对表主键进⾏分区的表。
Key（键值）-上⾯Hash模式的⼀种延伸，这⾥的Hash Key是MySQL系统产⽣的。
4 5List（预定义列表）–这种模式允许系统通过预定义的列表的值来对数据进⾏分割。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 21var 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 14CREATE 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 5CREATE 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,
6 7 8 9 10 11 12 13 14`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 13CREATE 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中的最后⼀个表。
通过主表查询的时候，相当于将所有⼦表合在⼀起查询。这样并不能体现分表的优势，建议还是查询⼦表。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。