MySQL分表和分区技术

MySQL分表和分区技术

1. 为什么要分表和分区？

日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

2. 什么是分表？

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件(MYISAM引擎)，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。分表很容易，然而由此所带来的应用程序甚至是架构方面的改动工作却不容小觑，还包括将来的扩展性等。

3. 什么是分区？

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

4. 分表与分区的比较

分表一种解决方案就是使用 MERGE引擎，架构和程序基本上不用做改动，不过，它的缺点是显见的：

只能在相同结构的 MyISAM 表上使用
无法享受到 MyISAM 的全部功能，例如无法在 MERGE 类型上执行 FULLTEXT 搜索
它需要使用更多的文件描述符
读取索引更慢

这个时候，MySQL 5.1 中新增的分区(Partition)功能的优势也就很明显了：

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

分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。

5. 分表的几种方式：

1、mysql集群

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

2、自定义规则分表

大表可以按照业务的规则来分解为多个子表。通常为以下几种类型，也可自己定义规则。
分表规则与分区规则一样，在分区模块详细介绍。
下面以Range简单介绍下如何分表（按照年份表）。

假设表结构有4个字段：自增id，姓名，存款金额，存款日期
把存款日期作为规则分表，分别创建几个表
2011年：account_2011
2012年：account_2012
……
2015年：account_2015
app在读写的时候根据日期来查找对应的表名，需要手动来判定。

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存储引擎:MERGE存储引擎把一组MyISAM数据表当做一个逻辑单元来对待，让我们可以同时对他们进行查询。

应用场景：如果需要把日志纪录不停的录入MySQL数据库，并且每天、每周或者每个月都创建一个单一的表，而且要时常进行来自多个表的合计查询，MERGE表这时会非常简单有效。

举例:
假设有如下两表

CREATE TABLE `t1` (     
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,     
`log` varchar(45) ,     
PRIMARY KEY (`id`)   
) ENGINE=MyISAM;  

CREATE TABLE `t2`(     
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,     
`log` varchar(45) ,     
PRIMARY KEY (`id`)   
) ENGINE=MyISAM; 

假设t1，t2中都有如下记录
+—-+——-+
| id | log |
+—-+——-+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
+—-+——-+
建立MERGE表

CREATE TABLE `t` (     
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,     
`log` varchar(45) NOT NULL,     
PRIMARY KEY (`id`)   
) ENGINE=MERGE UNION=(t1, t2) INSERT_METHOD=LAST;  

执行select * from t;将会得到如下结果
+—-+——-+
| id | log |
+—-+——-+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 1 | test1 |
| 2 | test2 |
| 3 | test3|
+—-+——-+
从效果上看，t1,t2的记录如同在一张表里一样被罗列了出来。现在我们主要来解释一下上面MERGE表的建表语句。
1）ENGINE=MERGE
指明使用MERGE引擎，有些同学可能见到过ENGINE=MRG_MyISAM的例子，也是对的，它们是一回事。
2）UNION=(t1, t2)
指明了MERGE表中挂接了些哪表，可以通过alter table的方式修改UNION的值，以实现增删MERGE表子表的功能。
3）INSERT_METHOD=LAST
INSERT_METHOD指明插入方式，取值可以是：0 不允许插入；FIRST 插入到UNION中的第一个表； LAST 插入到UNION中的最后一个表。
4）MERGE表及构成MERGE数据表结构的各成员数据表必须具有完全一样的结构。每一个成员数据表的数据列必须按照同样的顺序定义同样的名字和类型，索引也必须按照同样的顺序和同样的方式定义。

Cookie问答:
1）建表时UNION指明的子表如果存在相同主键的记录会怎么样？
相同主键的记录会同时存在于MERGE中，就像第三节中的例子所示。但如果继续向MERGE表中插入数据，若数据主键已存在则无法插入(这里指主键存在于INSERT_METHOD指定的表中)。换言之，MERGE表只对建表之后的操作负责。

2）若MREGE后存在重复主键，按主键查询会是什么结果？
顺序查询，只出现一条查询记录即停止。比如第三节中的例子，如果执行

select * from t where id=1;  

只会得到结果
+—-+——–+
| id | log |
+—-+——–+
| 1 | test1 |
+—-+——–+

3）直接删除一个子表会出现什么情况，正确删除的方式是怎样的？
MERGE表会被破坏，正确方式是用alter table方式先将子表从MERGE表中去除，再删除子表。
以第三节中的例子为例，执行如下操作：

alter table t ENGINE=MRG_MyISAM UNION=(t1) INSERT_METHOD=LAST;  

可以从MERGE表中去除t2，这里你可以安全的对t2进行任何操作了。

4）误删子表时，如何恢复MERGE表？
误删子表时，MERGE表上将无法进行任何操作。
方法1，drop MERGE表，重建。重建时注意在UNION部分去掉误删的子表。
方法2，建立MERGE表时，会在数据库目录下生成一个.MRG文件，比如设表名为t，则文件名为t.MRG。
文件内容类似：

t1
t2
#INSERT_METHOD=LAST

指明了MGEGE表的子表构成及插入方式。
可以直接修改此文件，去掉误删表的表名。然后执行flush tables即可修复MERGE表。

5）MERGE的子表中之前有记录，且有自增主键，则MERGE表创建后，向其插入记录时主键以什么规则自增？
以各表中的AUTO_INCREMENT最大值做为下一次插入记录的主键值。
比如t1的自增ID至6，t2至4，则创建MERGE表后，插入的下一条记录ID将会是7

6）两个结构完全相同的但已存在数据的表，是否一定可以合成一个MEREGE表？
从实验的结果看，不是这样的，有时创建出的表，无法进行任何操作。
所以，推荐的使用方法是先有一个MERGE表，里面只包含一张表，当一个这个表的的大小增长到一定程度（比如200w）时，创建另一张空表，将其挂入MERGE表，然后继续插入记录。

7）删除MERGE表是否会对子表产生影响？
不会

8）MREGE表的子表的ENGIN是否有要求？
有的，必须是MyISAM表

6、MySQL分区

分区应该注意的事项：

1、 做分区时，要么不定义主键，要么把分区字段加入到主键中。
2、 分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL

分区的类型：

RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包>含MySQL中有效的、产生非负整数值的任何表达式。
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含>整数值。

可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区，例如：

mysql> SHOW VARIABLES LIKE ‘%partition%’;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| have_partition_engine | YES |
+———————–+——-+
1 row in set (0.00 sec)

1. range分区
   　create table t_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
   　　)；

2. list分区

   create table t_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)
   　　);
   对于innodb和myisam引擎，一条语句插入多条记录的时候，如果中间有值不能插入，innodb会全部回滚，myisam在错误值之前的数据可以插入到表中。

3. hash分区
   hash分区的目的是将数据均匀的分布到预先定义的各个分区中，保证各分区的数据量大致一致。

   create table t_hash(
   　　a int(11),
   　　b datetime
   　　)partition by hash (YEAR(b)
   　　partitions 4;
   hash的分区函数页需要返回一个整数值。partitions子句中的值是一个非负整数，不加的partitions子句的话，默认为分区数为1。

4. key分区
   key分区和hash分区相似，不同在于hash分区是用户自定义函数进行分区，key分区使用mysql数据库提供的函数进行分区，NDB cluster使用MD5函数来分区，对于其他存储引擎mysql使用内部的hash函数，这些函数基于password()一样的算法。

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

分区操作

新增分区

mysql> ALTER TABLE sale_data
    -> ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

未分区的表进行分区

 ALTER TABLE `userinfo` PARTITION BY RANGE(uid) (
 partition p0 VALUES less than (3000000),
 partition p1 VALUES less than (6000000),
 PARTITION p2 VALUES less than MAXVALUE);

删除分区
注意：当删除了一个分区，也同时删除了该分区中所有的数据。

mysql> ALTER TABLE sale_data DROP PARTITION p201010;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> 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)
    -> );
Query OK, 0 rows affected (1.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySql 分区取消

alter table table_name remove partitioning;