mysql--表分区，分表技术

表分区，分表

mysql中表分区与分表是两个不同的概念，不能混为一谈
分表：指的是通过一定的规则，将一张表分解成多张不同的表。
表分区：是指按照一定的规则，将数据库中的一张表分解成多个更小的，容易管理的部分

分表与表分区的区别从逻辑上看，分区只有一张表，而分表有多张表

表分区

分区一般是两种 横向分区和纵向分区

横向分区：

什么是横向分区呢？就是横着来分区了，举例来说明一下，假如有100W条数据，分成十份，前10W条数据放到第一个分区，第二个10W条数据放到第二个分区，依此类推。也就是把表分成了十分，根用merge来分表，有点像哦。取出一条数据的时候，这条数据包含了表结构中的所有字段，也就是说横向分区，并没有改变表的结构。

纵向分区：

什么是纵向分区呢？就是竖来分区了，举例来说明，在设计用户表的时候，开始的时候没有考虑好，而把个人的所有信息都放到了一张表里面去，这样这个表里面就会有比较大的字段，如个人简介，而这些简介呢，也许不会有好多人去看，所以等到有人要看的时候，在去查找，分表的时候，可以把这样的大字段，分开来。

mysql中提供的是第一种分区方式，横向分区，并且细分了很多种，我们一一介绍下

横向分区种类

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  #MAXVALUE 表示最大的可能的整数值
　　)；

这种分区的用处

1 你可以根据需求一次删除一个分区或者多个分区的数据，例如”ALTER TABLE employees DROP PARTITION p0;”或者”DELETE FROM employees WHERE YEAR (separated) <= 2010;”，不需要全表扫描删除

2 想要使用一个包含有日期或时间值，或包含有从一些其他级数开始增长的值的列。
3 经常运行直接依赖于用于分割表的列的查询。
　　例如，当执行一个如”SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;”这样的查询时，
　　MySQL可以很迅速地确定只有分区p2需要扫描，这是因为余下的分区不可能包含有符合该WHERE子句的任何记录

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) 
);
LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到

hash 分区

这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

key分区

上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

分区的管理

增加一个分区

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

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 分区表的数据可以分布在不同的物理设备上，从而高效利用多个硬件设备，和单个磁盘相比，可以储存更多的数据
2 优化查询，在where语句中，包含多个分区条件时，可以只扫表一个分区或者多个分区来提高查询速率；涉及到sum和count操作时，可以同时在多个分区进行，在进行合并
3 分区表更容易维护，例如删除大量数据时，只需要删除一个或多个分区表
4 可以使用分区表避免某些特殊瓶颈

分区的限制因素

a：一个表中只能有1024个分区
b:mysql5.1中分区表达式必须是整数，或者返回整数的表达式，mysql5.5中提供了非整数表达式分区
c:如果分区字段中有主键或者唯一键，那么全部的主键和唯一键都要包括进来
d:分区表中无法使用外键约束
e:mysql的分区适用于一个表中的所有的数据索引，不能分开

你可以使用 show variables like ‘%partition%’ 命令查看mysql是否支持分区

分表

分表一共有三种形式

mysql集群

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

自定义规则分表

如果我们预先估计给会产生大数据，就可以在建表时，直接创建多几个表，用于分担存储数据，这个分表规则类似于上面的分区方法
在执行插入语句前，现根据条件计算出要插入的表名

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

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

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

mysql> CREATE TABLE IF NOT EXISTS `user1` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT,  
 ->   `name` varchar(50) DEFAULT NULL,  
 ->   `sex` int(1) NOT NULL DEFAULT '0',  
 ->   PRIMARY KEY (`id`)  
 -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  
Query OK, 0 rows affected (0.05 sec)  
  
mysql> CREATE TABLE IF NOT EXISTS `user2` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT,  
 ->   `name` varchar(50) DEFAULT NULL,  
 ->   `sex` int(1) NOT NULL DEFAULT '0',  
 ->   PRIMARY KEY (`id`)  
 -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  
Query OK, 0 rows affected (0.01 sec)  
  
mysql> INSERT INTO `user1` (`name`, `sex`) VALUES('张映', 0);  
Query OK, 1 row affected (0.00 sec)  
  
mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1);  
Query OK, 1 row affected (0.00 sec)  
  
mysql> CREATE TABLE IF NOT EXISTS `alluser` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT,  
 ->   `name` varchar(50) DEFAULT NULL,  
 ->   `sex` int(1) NOT NULL DEFAULT '0',  
 ->   INDEX(id)  
 -> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

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