mysql分区分表

mysql分区分表

分区

一.分区就是把一张表的数据分成N多个区块 小表，这些区块可以在同一个磁盘上，也可以在不同的磁盘上

二.分区的一些优点包括：

1.与单个磁盘或文件系统分区相比，可以存储更多的数据

2.对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易的删除那些数据。相反的在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便的实现。

3.一些查询可以得到极大的优化，这主要是借助于满足一个给定where语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

4.涉及到例如sum()和count()这样聚合函数的查询，可以很容易的进行并行处理。这种查询的一个简单例子如"select salesperson_id ,count(orders) as order_total from sales group by salesperson_id";通过并行，这意味着该查询可以在每个分区上同时进行，最终结果只需要通过总计所有分区得到的结果。

5.通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

• 与单个磁盘或文件系统分区相比，可以存储更多的数据
• 很容易就能删除不用或者过时的数据
• 一些查询可以得到极大的优化
• 涉及到 SUM()/COUNT() 等聚合函数时，可以并行进行
• IO吞吐量更大

分区应该注意的事项：

1.做分区时，要么不定义主键，要么把分区字段加入到主键中

2.分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL

三.分区类型

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

list分区：类似于按range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择

hash分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含mysql中有效的 产生非负整数值的任何表达式

key分区：类似于按hash分区，区别在于key分区只支持计算一列或多列，且mysql服务器提供其自身的哈希函数。必须有一列或多列包含整数值

RANGE分区

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

这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作来进行定义

除了可以根据商店编号分割表数据外，你还可以使用一个基于两个DATE （日期）中的一个的表达式来分割表数据。例如，假定你想基于每个雇员离开公司的年份来分割表，也就是说，YEAR(separated)的值。实现这种分区模式的CREATE TABLE 语句的一个例子如下所示：

[sql] view plaincopy

1. CREATE TABLE employees (     
2.     id INT NOT NULL,     
3.     fname VARCHAR(30),     
4.     lname VARCHAR(30),     
5.     hired DATE NOT NULL DEFAULT '1970-01-01',     
6.     separated DATE NOT NULL DEFAULT '9999-12-31',     
7.     job_code INT,     
8.     store_id INT    
9. )     
10.     
11. PARTITION BY RANGE (YEAR(separated)) (     
12.     PARTITION p0 VALUES LESS THAN (1991),     
13.     PARTITION p1 VALUES LESS THAN (1996),     
14.     PARTITION p2 VALUES LESS THAN (2001),     
15.     PARTITION p3 VALUES LESS THAN MAXVALUE     
16. )；    

List分区

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择

LIST分区通过使用"PARTITION BY LIST(expr)" 来实现，其中expr是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过VALUES IN(value_list)的方式来定义每个分区，其中value_list是一个通过逗号分隔的整数列表

假定有20个音像店，分布在4个有经销权的地区，如下表所示：

====================
地区      商店ID 号

------------------------------------

北区      3, 5, 6, 9, 17
东区      1, 2, 10, 11, 19, 20
西区      4, 12, 13, 14, 18
中心区   7, 8, 15, 16

====================
要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，可以使用下面的“CREATE TABLE”语句：

[sql] view plaincopy

1. CREATE TABLE employees (     
2.     id INT NOT NULL,     
3.     fname VARCHAR(30),     
4.     lname VARCHAR(30),     
5.     hired DATE NOT NULL DEFAULT '1970-01-01',     
6.     separated DATE NOT NULL DEFAULT '9999-12-31',     
7.     job_code INT,     
8.     store_id INT    
9. )     
10.     
11. PARTITION BY LIST(store_id)     
12.     PARTITION pNorth VALUES IN (3,5,6,9,17),     
13.     PARTITION pEast VALUES IN (1,2,10,11,19,20),     
14.     PARTITION pWest VALUES IN (4,12,13,14,18),     
15.     PARTITION pCentral VALUES IN (7,8,15,16)     
16. )；    

HASH分区

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算，这个函数可以包含mysql中有效的 产生非负整数值的任何表达式

要使用HASH分区来分隔一个表，要在create table语句上添加一个PARTITION BY HASH(expr) 子句，其中expr是一个返回一个整数的表达式。它可以仅仅是字段类型为mysql整型的一列的名字。此外，很可能需要在后面添加一个PARTITION BY NUM 子句，其中num是一个非负的整数，它表示将要被分割成分区的数量

[sql] view plaincopy

1. CREATE TABLE employees (     
2.     id INT NOT NULL,     
3.     fname VARCHAR(30),     
4.     lname VARCHAR(30),     
5.     hired DATE NOT NULL DEFAULT '1970-01-01',     
6.     separated DATE NOT NULL DEFAULT '9999-12-31',     
7.     job_code INT,     
8.     store_id INT    
9. )     
10. PARTITION BY HASH(store_id)     
11. PARTITIONS 4；    

如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。

KEY分区

类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且mysql服务器提供其自身的哈希函数，必须有一列或多列包含整数值

columns分区

上面的RANGE、LIST、HASH、KEY四种分区中，分区的条件必须是整形，如果不是整形需要通过函数将其转换为整形。

　　mysql-5.5开始支持COLUMNS分区，可视为RANGE和LIST分区的进化，COLUMNS分区可以直接使用非整形数据进行分区。COLUMNS分区支持以下数据类型：

　　所有整形，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL则不支持。

　　日期类型，如DATE和DATETIME。其余日期类型不支持。

　　字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不支持。

　　COLUMNS可以使用多个列进行分区。

分表

将表分成几个子表

分表策略:

1. 取模分表，
2. 根据时间维度进行分表
3. 自定义的Hash

分表后的数据复制，一般采用insert + select语句将原有表的数据导入新的分表，或者直接copy原表的数据到分表中。比如根据id取模分四张表，分表后把原有数据复制示例如下。

insert into user0(id,name, extDO, hobbys, votes) select * from user where idmod 4 = 0

insert into user0(id,name, extDO, hobbys, votes) select * from user where idmod 4 = 1

insert into user0(id,name, extDO, hobbys, votes) select * from user where idmod 4 = 2

insert into user0(id,name, extDO, hobbys, votes) select * from user where idmod 4 = 3

分表有个总表 合并表

CREATE TABLE `test`.`article` (

　　`id` BIGINT( 20 ) NOT NULL ,

　　`subject` VARCHAR( 200 ) NOT NULL ,

　　`content` TEXT NOT NULL ,

　　PRIMARY KEY ( `id` )

　　) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=0 UNION=(`article_0`,`article_1`,`article_2`,`article_3`,`article_4`);

合并表必须和子表有相同的结构 类型长度 包括字段的顺序，这里的insert_method=0表示不允许对本表进行insert操作。当需要查询的时候，我们只对article这个表进行操作就可以了，这个表仅仅只能进行select操作

mysql下merge分表

1.merge简介

分表就是把N条记录的表，分成若干个分表，各个分表记录的总和仍为N

merge是mysql的一种存储引擎，它把一组MyISAM数据当做一个逻辑单元

create table `t`(

    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

    `data` varchar(45) NOT NULL,

    PRIMARY KEY(`id`)

)ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

其中ENGINE=MERGE表示，使用merge引擎。另外ENGINE=MEG_MyISAM是一样的意思

INSERT_METHOD 表示插入方式 。 0不允许插入，FIRST插入到UNION中的第一个表，LAST插入到UNION中的最后一个表

2.merge数据存储结构

mysql中MyISAM引擎下每一张表都对应三个文件 .MYD数据文件 .frm表结构文件 .MYI索引文件

但是MERGE引擎下每一张表只有一个.MRG文件 .MRG里面存放着分表的关系，以及插入数据的方式。它好像是一个外壳，或者是连接池，数据存放在分表里面