Mysql --分表、分库、分区（横向纵向、分区列）的区别与详解

一、为什么要分？

答案很简单：数据库出现性能瓶颈。用大白话来说就是数据库快扛不住了。

数据库出现性能瓶颈，对外表现有几个方面：

• 大量请求阻塞，并发性能差
  在高并发场景下，大量请求都需要操作数据库，导致连接数不够了，请求处于阻塞状态。
• SQL 操作变慢，未命中索引导致全表扫描
  如果数据库中存在一张上亿数据量的表，一条 SQL 没有命中索引会全表扫描，这个查询耗时会非常久。（一般小于1千万较好）
• 存储出现问题
  业务量剧增，单库数据量越来越大，给存储造成巨大压力。（一般1、2T就极限了）
  
  这就说明 MySQL 单表的最大尺寸不能超过 2TB，我们简单来算一下，假设一个表的平均行长度为32KB（InnoDB最大行长度限制65536字节，64KB），那么他最大能存储多少行数据？4 x 1024 x 1024 x 1024 / 32 = 134217728大约 1.4 亿不到。
  对于饿了么，美团那外卖种交易系统的订单表 1.4 亿是很容易达到的，一天平均 2000W 订单，一周就到 1.4 亿了，没法玩了，一般都会采用异地多活的方案，根据用户的位置将数据写到相应的 IDC 数据中心，这其实也是一种高大上的分表方案，不在我们今天讨论范围啦。

从机器的角度看，性能瓶颈无非就是CPU、内存、磁盘、网络这些，要解决性能瓶颈最简单粗暴的办法就是提升机器性能，但是通过这种方法成本和收益投入比往往又太高了，不划算，所以重点还是要从软件角度入手。

二、分表分库带来的复杂性

分库分表带来的复杂性
既然分库分表这么好，那我们是不是在项目初期就应该采用这种方案呢？不要激动，冷静一下，分库分表的确解决了很多问题，但是也给系统带来了很多复杂性，下面简要说一说。

（1）跨库关联查询

mysql支持多个库中不同表的关联查询。但是毕竟是跨数据库，可能性能等方面存在各种影响?

在单库未拆分表之前，我们可以很方便使用 join 操作关联多张表查询数据，但是经过分库分表后两张表可能都不在一个数据库中，如何使用 join 呢？

有几种方案可以解决：

字段冗余：把需要关联的字段放入主表中，避免 join 操作；
数据抽象：通过ETL等将数据汇合聚集，生成新的表；
全局表：比如一些基础表可以在每个数据库中都放一份；
应用层组装：将基础数据查出来，通过应用程序计算组装；

（2）分布式事务

单数据库可以用本地事务搞定，使用多数据库就只能通过分布式事务解决了。

常用解决方案有：基于可靠消息（MQ）的解决方案、两阶段事务提交、柔性事务等。

（3）排序、分页、函数计算问题

在使用 SQL 时 order by， limit 等关键字需要特殊处理，一般来说采用分片的思路：

先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终得到结果。

（4）分布式 ID

如果使用 Mysql 数据库在单库单表可以使用 id 自增作为主键，分库分表了之后就不行了，会出现id 重复。

常用的分布式 ID 解决方案有：

UUID
基于数据库自增单独维护一张 ID表
号段模式
Redis 缓存
雪花算法（Snowflake）
百度uid-generator
美团Leaf
滴滴Tinyid
这些方案后面会写文章专门介绍，这里不再展开。

（5）多数据源

分库分表之后可能会面临从多个数据库或多个子表中获取数据，一般的解决思路有：客户端适配和代理层适配。

业界常用的中间件有：

shardingsphere（前身 sharding-jdbc）
Mycat

三、直观感受—什么是分表、分库、分区

概念解释

• 分表：单表过大容易产生查询缓慢或存储的问题，所以把一张表分成N多个小表，比如user_1，user_2，user_3。具体分为水平分区和垂直分区。似乎也能mysql自动分 表现和分区一样 只用查总表即可

• 分区：把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在机器的不同的磁盘上，但不能分配到不同服务器上。一张大表进行分区后，他还是一张表，名称上不会变成二张表，但是他存放数据的区块变多了

• 分库：不同的分库在不同服务器上，一台机器的性能终究是有限制的，用分库可以解决单台服务器性能不够，或者成本过高问题。也有水平拆分和垂直拆分的区别

不同之处

侧重点

• 分区：如何突破磁盘的读写能力，从而达到提高mysql性能的目的。
  所有的数据还在一个表中，但物理存储数据根据一定的规则存放在不同的文件中，文件也可以放到另外磁盘上
  优点：代码维护量小，基本不用改动，提高IO吞吐量
  缺点：表的并发程度没有增加

• 分表：重点是存取数据时，如何提高mysql并发能力上；
  即把一个很大的表达数据分到几个表中，这样每个表数据都不多。
  优点：提高并发量，减小锁的粒度
  缺点：代码维护成本高，相关sql都需要改动

分区只是一张表中的数据的存储位置发生改变，分表是将一张表分成多张表。
当访问量大，且表数据比较大时，分区与分表两种方式可以互相配合使用。
当访问量不大，但表数据比较多时，可以只进行分区。
但是分区好像有些限制得考虑

• 分库的侧重点是：单机性能瓶颈！是最终解决方案

实现方式

• 分表：mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完正的一张表，都对应三个文件，一个.MYD数据文件，.MYI索引文件，.frm表结构文件。分表呢是利用了merge存储引擎（分表的一种方式），alluser是总表，下面有二个分表，user1，user2。他们二个都是独立的表，取数据的时候，我们可以通过总表来取。这里总表是没有.MYD,.MYI这二个文件的，也就是说，总表他不是一张表，没有数据，数据都放在分表里面。alluser.MRG里面就存了一些分表的关系，以及插入数据的方式。可以把总表理解成一个外壳，或者是联接池。
• 分区：分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据的区块变多了。按照时间分区。大部分只查询最近的订单数据，那么大部分只访问一个分区，比整个表小多了，数据库可以更加好的缓存，性能也提高了。这个是数据库分的，应用程序透明，无需修改。

数据处理上

• （1），分表后，数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表里面。看下面的例子：
  select * from alluser where id='12’表面上看，是对表alluser进行操作的，其实不是的。是对alluser里面的分表进行了操作。
• （2），分区呢，不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表呢，还是一张表。数据处理还是由自己来完成。

提高性能上

• 分表后，单表的并发能力提高了，磁盘I/O性能也提高了。并发能力为什么提高了呢，因为查寻一次所花的时间变短了，如果出现高并发的话，总表可以根据不同 的查询，将并发压力分到不同的小表里面。磁盘I/O性能怎么搞高了呢，本来一个非常大的.MYD文件现在也分摊到各个小表的.MYD中去了。

• mysql提出了分区的概念，我觉得就想突破磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。
  在这一点上，分区和分表的测重点不同，分表重点是存取数据时，如何提高mysql并发能力上；而分区呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。

实现的难易度上

• 分表的方法有很多，用merge来分表，是最简单的一种方式（要改表引擎）。这种方式根分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。
• 分区实现是比较简单的，建立分区表，根建平常的表没什么区别，并且对开代码端来说是透明的。

四、分表详解

mysql的存储结构

mysql数据库中的数据是以文件的形式存储在磁盘介质上，具体存储目录，请查阅“my.cnf”中的“datadir”属性。一张数据表主要对应着三个文件，一个是FRM存放表结构的，一个是MYD存放表数据的，一个是MYI存表索引的。如果一张表的数据量太大的话，那么MYD、MYI就会变的很大，查找数据就会变得很慢

垂直分表

以订单表 orders 为例，按照字段进行拆分，这里面需要考虑一个问题，如何拆分字段才能表上的DML性能最大化，常规的方案是冷热分离（将使用频率高字段放到一张表里，剩下使用频繁低的字段放到另一张表里）。

orders 表通过拆分之后，就变成了 orders01 和 orders02 两张表，在磁盘上就会存储两个数据文件 orders01.ibd 和 orders02.ibd，orders 表最大尺寸就是 4TB 了，拆分完之后，该怎么查询呢？举个例子：

分析下上面的 SQL，select 后面的列分别位于两张表中（order_id,order_sn在orders01中，source在orders02中），上面的SQL可以查询重写为如下形式。

如果用了数据库中间件就会自动实现查询重写，例如 mycat，sharding-sphere，不用中间件的话，也可以实现的，就是稍微比较麻烦点，可以搞一个 route 表（主键ID, 原表名，字段名，子表名），每次解析SQL时都需要根据原表名 + 字段名去获取需要的子表，然后再改写 SQL，执行 SQL 返回结果，这种代码改造量太大，而且容易出错，故这种垂直拆分在实际业务中用的不多。

如果业务表中有必须的 Text 类型来存储数据，这时可以利用垂直拆分来减少表大小，将 text 字段拆分到子表中。

再比如，在设计用户表的时候，开始的时候没有考虑好，而把个人的所有信息都放到了一张表里面去，这样这个表里面就会有比较大的字段，如个人简介，而这些简介呢，也许不会有好多人去看，所以等到有人要看的时候，在去查找，分表的时候，可以把这样的大字段，分开来。
缺点：当数据量持续增大的时候，垂直分的方式依然不能满足需求，还是需要考虑水平分，

水平分表

水平拆分表就是按照表中的记录进行分片，举个例子，目前订单表 orders 有 2000w 数据，根据业务的增长，估算一年之后会达到1亿，同时参考阿里云 RDS for MySQL 的最佳实践，单表不建议超过 500w，1亿数据分20个子表就够了。

均匀分表

问题来了，按照什么来拆分呢？主键id还是用户的user_id，按主键ID拆分数据很均匀，通过ID查询 orders 的场景几乎没有，业务访问 orders 大部分场景都是根据 user_id来过滤的，而且 user_id 的唯一性又很高（一个 user_id 对应的 orders 表记录不多，选择性很好），按照 user_id 来作为 Sharding key能满足大部分业务场景，拆分之后每个子表数据也比较均匀。

这样就将 orders 表拆分成20个子表，对应到InnoDB的存储上就是20个数据文件（orders_0.ibd，orders_1.ibd等），这时候执行SQL语句select order_id, order_sn, source from **orders** where user_id = 1001;就能很快的定位到要查找记录的位置是在orders_1，然后做查询重写，转化为SQL语句select order_id, order_sn, source from **orders_01** where user_id = 1001，这种查询重写功能很多中间件都已经实现了，常用的就是 sharding-sphere 或者 sharding-jdbc 都可以实现。

哈希取模：hash(key)%N

按日期分表

mysql分区表（似乎就是分区）

直接看后面那章吧

merge分表

要改引擎为另一个不支持事务的 暂且不讲

分表坏处

1、自增ID问题
2、数据关联查询问题
3、数据同步问题

a 设置数据库的字长
缺点: 步长确定后，无法扩展新的mysql，不然生成步长的规则可能会发生变化

查询自增的步长
SHOW VARIABLES LIKE auto_inc%
修改自增的步长
SET @@auto increment increment=10;
修改起始值
0
SET @@auto increment offset=5;
假设有两台mysq1数据库服务器
节点e自增1 3 5 7 9 11 
节点。自增2 4 6 8 10 12.

b、UUID形式，缺点是不能排序
c、使用雪花算法或redis解决

数据分区(分区表)

mysql数据库中的数据是以文件的形式存储在磁盘介质上，具体存储目录，请查阅“my.cnf”中的“datadir”属性。一张数据表主要对应着三个文件，一个是FRM存放表结构的，一个是MYD存放表数据的，一个是MYI存表索引的。如果一张表的数据量太大的话，那么MYD、MYI就会变的很大，查找数据就会变得很慢，这个时候可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样做的话，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

mysql提供的分区属于第一种，横向分区，并且细分成很多种方式

分区的好处

• 与单个磁盘或文件系统分区相比，可以存储更多的数据。
• 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。
• 可以自动删除，比如月底报表下下个月就没用了
• 一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
• 涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
  通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

分区的限制

分区表的限制因素

1）一个表最多只能有1024个分区 2） MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。 3）如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。 4）分区表中无法使用外键约束 5）MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

判断是否支持分区

MySQL实现用户定义的分区，您可以通过变量查询语句来判断MySQL服务器是否支持分区：

mysql> SHOW VARIABLES LIKE '%partition%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)
Note
The have_partitioning variable is deprecated, and removed in MySQL 5.6.1.

您还可以检查显示插件语句的输出，如下所示：

mysql> SHOW PLUGINS;
+------------+----------+----------------+---------+---------+
| Name       | Status   | Type           | Library | License |
+------------+----------+----------------+---------+---------+
| binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| FEDERATED  | DISABLED | STORAGE ENGINE | NULL    | GPL     |
| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| InnoDB     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ndbcluster | DISABLED | STORAGE ENGINE | NULL    | GPL     |
+------------+----------+----------------+---------+---------+
11 rows in set (0.00 sec)

您也可以通过类似于此的查询来检查INFORMATION_SCHEMA.PLUGINS表：

mysql> SELECT
    ->     PLUGIN_NAME as Name,
    ->     PLUGIN_VERSION as Version,
    ->     PLUGIN_STATUS as Status
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+--------+
| Name               | Version | Status |
+--------------------+---------+--------+
| binlog             | 1.0     | ACTIVE |
| CSV                | 1.0     | ACTIVE |
| MEMORY             | 1.0     | ACTIVE |
| MRG_MYISAM         | 1.0     | ACTIVE |
| MyISAM             | 1.0     | ACTIVE |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE |
| BLACKHOLE          | 1.0     | ACTIVE |
| ARCHIVE            | 3.0     | ACTIVE |
| InnoDB             | 5.6     | ACTIVE |
| partition          | 1.0     | ACTIVE |
+--------------------+---------+--------+
10 rows in set (0.00 sec)

mysql的数据分区类型（资料1 有具体实践）

1.range分区

按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行:

//创建range分区表
mysql> CREATE TABLE IF NOT EXISTS `user` (
-> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘用户ID’,
-> `name` varchar(50) NOT NULL DEFAULT ” COMMENT ‘名称’,
-> `sex` int(1) NOT NULL DEFAULT ‘0’ COMMENT ‘0为男，1为女’,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
-> PARTITION BY RANGE (id) (
-> PARTITION p0 VALUES LESS THAN (3),
-> PARTITION p1 VALUES LESS THAN (6),
-> PARTITION p2 VALUES LESS THAN (9),
-> PARTITION p3 VALUES LESS THAN (12),
-> PARTITION p4 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.13 sec)

//插入一些数据
mysql> INSERT INTO `test`.`user` (`name` ,`sex`)VALUES (‘tank’, ‘0’)
-> ,(‘zhang’,1),(‘ying’,1),(‘张’,1),(‘映’,0),(‘test1′,1),(‘tank2′,1)
-> ,(‘tank1′,1),(‘test2′,1),(‘test3′,1),(‘test4′,1),(‘test5′,1),(‘tank3′,1)
-> ,(‘tank4′,1),(‘tank5′,1),(‘tank6′,1),(‘tank7′,1),(‘tank8′,1),(‘tank9′,1)
-> ,(‘tank10′,1),(‘tank11′,1),(‘tank12′,1),(‘tank13′,1),(‘tank21′,1),(‘tank42′,1);
Query OK, 25 rows affected (0.05 sec)
Records: 25 Duplicates: 0 Warnings: 0

//到存放数据库表文件的地方看一下，my.cnf里面有配置，datadir后面就是
[root@BlackGhost test]# ls |grep user |xargs du -sh
4.0K user#P#p0.MYD
4.0K user#P#p0.MYI
4.0K user#P#p1.MYD
4.0K user#P#p1.MYI
4.0K user#P#p2.MYD
4.0K user#P#p2.MYI
4.0K user#P#p3.MYD
4.0K user#P#p3.MYI
4.0K user#P#p4.MYD
4.0K user#P#p4.MYI
12K user.frm
4.0K user.par

//取出数据
mysql> select count(id) as count from user;
+——-+
| count |
+——-+
| 25 |
+——-+
1 row in set (0.00 sec)

//删除第四个分区
mysql> alter table user drop partition p4;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

/**存放在分区里面的数据丢失了，第四个分区里面有14条数据，剩下的3个分区
只有11条数据，但是统计出来的文件大小都是4.0K，从这儿我们可以看出分区的
最小区块是4K
*/
mysql> select count(id) as count from user;
+——-+
| count |
+——-+
| 11 |
+——-+
1 row in set (0.00 sec)

//第四个区块已删除
[root@BlackGhost test]# ls |grep user |xargs du -sh
4.0K user#P#p0.MYD
4.0K user#P#p0.MYI
4.0K user#P#p1.MYD
4.0K user#P#p1.MYI
4.0K user#P#p2.MYD
4.0K user#P#p2.MYI
4.0K user#P#p3.MYD
4.0K user#P#p3.MYI
12K user.frm
4.0K user.par

/*可以对现有表进行分区,并且会按規则自动的将表中的数据分配相应的分区
中，这样就比较好了，可以省去很多事情，看下面的操作*/
mysql> alter table aa partition by RANGE(id)
-> (PARTITION p1 VALUES less than (1),
-> PARTITION p2 VALUES less than (5),
-> PARTITION p3 VALUES less than MAXVALUE);
Query OK, 15 rows affected (0.21 sec) //对15数据进行分区
Records: 15 Duplicates: 0 Warnings: 0

//总共有15条
mysql> select count(*) from aa;
+———-+
| count(*) |
+———-+
| 15 |
+———-+
1 row in set (0.00 sec)

//删除一个分区
mysql> alter table aa drop partition p2;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0

//只有11条了，说明对现有的表分区成功了
mysql> select count(*) from aa;
+———-+
| count(*) |
+———-+
| 11 |
+———-+
1 row in set (0.00 sec)

2.list分区

LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分 区是从属于一个连续区间值的集合:

//这种方式失败
mysql> CREATE TABLE IF NOT EXISTS `list_part` (
->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘用户ID’,
->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT ‘省’,
->   `name` varchar(50) NOT NULL DEFAULT ” COMMENT ‘名称’,
->   `sex` int(1) NOT NULL DEFAULT ‘0’ COMMENT ‘0为男，1为女’,
->   PRIMARY KEY (`id`)
-> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
-> PARTITION BY LIST (province_id) (
->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),
->     PARTITION p1 VALUES IN (9,10,11,12,16,21),
->     PARTITION p2 VALUES IN (13,14,15,19),
->     PARTITION p3 VALUES IN (17,18,20,22,23,24)
-> );
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function
//这种方式成功
mysql> CREATE TABLE IF NOT EXISTS `list_part` (
->   `id` int(11) NOT NULL  COMMENT ‘用户ID’,
->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT ‘省’,
->   `name` varchar(50) NOT NULL DEFAULT ” COMMENT ‘名称’,
->   `sex` int(1) NOT NULL DEFAULT ‘0’ COMMENT ‘0为男，1为女’
-> ) ENGINE=INNODB  DEFAULT CHARSET=utf8
-> PARTITION BY LIST (province_id) (
->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),
->     PARTITION p1 VALUES IN (9,10,11,12,16,21),
->     PARTITION p2 VALUES IN (13,14,15,19),
->     PARTITION p3 VALUES IN (17,18,20,22,23,24)
-> );
Query OK, 0 rows affected (0.33 sec)

上面的这个创建list分区时，如果有主键的话，分区时主键必须在其中，不然就会报错。如果我不用主键，分区就创建成功了，一般情况下，一个张表肯定会有一个主键，这算是一个分区的局限性吧。

如果对数据进行测试，请参考range分区的测试来操作

3.hash分区

HASH分区主要用来确保数据在预先确定数目的分区中平均分布，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以 及指定被分区的表将要被分割成的分区数量：

mysql> CREATE TABLE IF NOT EXISTS `hash_part` (
->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘评论ID’,
->   `comment` varchar(1000) NOT NULL DEFAULT ” COMMENT ‘评论’,
->   `ip` varchar(25) NOT NULL DEFAULT ” COMMENT ‘来源IP’,
->   PRIMARY KEY (`id`)
-> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
-> PARTITION BY HASH(id)
-> PARTITIONS 3;
Query OK, 0 rows affected (0.06 sec)

4.key分区

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用 户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供：

mysql> CREATE TABLE IF NOT EXISTS `key_part` (
->   `news_id` int(11) NOT NULL  COMMENT ‘新闻ID’,
->   `content` varchar(1000) NOT NULL DEFAULT ” COMMENT ‘新闻内容’,
->   `u_id` varchar(25) NOT NULL DEFAULT ” COMMENT ‘来源IP’,
->   `create_time` DATE NOT NULL DEFAULT ‘0000-00-00 00:00:00′ COMMENT ‘时间’
-> ) ENGINE=INNODB  DEFAULT CHARSET=utf8
-> PARTITION BY LINEAR HASH(YEAR(create_time))
-> PARTITIONS 3;
Query OK, 0 rows affected (0.07 sec)

5.子分区

子分区是分区表中每个分区的再次分割，子分区既可以使用HASH希分区，也可以使用KEY分区。这 也被称为复合分区（composite partitioning）。

如果一个分区中创建了子分区，其他分区也要有子分区
如果创建了了分区，每个分区中的子分区数必有相同
同一分区内的子分区，名字不相同，不同分区内的子分区名子可以相同（5.1.50不适用）

mysql> CREATE TABLE IF NOT EXISTS `sub_part` (
->   `news_id` int(11) NOT NULL  COMMENT ‘新闻ID’,
->   `content` varchar(1000) NOT NULL DEFAULT ” COMMENT ‘新闻内容’,
->   `u_id` int(11) NOT NULL DEFAULT 0s COMMENT ‘来源IP’,
->   `create_time` DATE NOT NULL DEFAULT ‘0000-00-00 00:00:00′ COMMENT ‘时间’
-> ) ENGINE=INNODB  DEFAULT CHARSET=utf8
-> PARTITION BY RANGE(YEAR(create_time))
-> SUBPARTITION BY HASH(TO_DAYS(create_time))(
-> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2),
-> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good),
-> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3)
-> );
Query OK, 0 rows affected (0.07 sec)

官方网站说不同分区内的子分区可以有相同的名字，但是mysql5.1.50却不行会提示以下错误

ERROR 1517 (HY000): Duplicate partition name s1

二、Mysql的数据分区类型（资料2 讲解更细致）

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

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

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

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

RANGE分区

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

这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。以下是实例。

Sql代码：

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 NOT NULL,
    store_id INT NOT NULL
)

partition BY RANGE (store_id) (
    partition p0 VALUES LESS THAN (6),
    partition p1 VALUES LESS THAN (11),
    partition p2 VALUES LESS THAN (16),
    partition p3 VALUES LESS THAN (21)
);

按照这种分区方案，在商店1到5工作的雇员相对应的所有行被保存在分区P0中，商店6到10的雇员保存在P1中，依次类推。注意，每个分区都是按顺序进行定义，从最低到最高。这是PARTITION BY RANGE 语法的要求；在这点上，它类似于C或Java中的“switch … case”语句。对于包含数据(72, ‘Michael’, ‘Widenius’, ’1998-06-25′, NULL, 13)的一个新行，可以很容易地确定它将插入到p2分区中，但是如果增加了一个编号为第21的商店，将会发生什么呢？在这种方案下，由于没有规则把store_id大于20的商店包含在内，服务器将不知道把该行保存在何处，将会导致错误。 要避免这种错误，可以通过在CREATE TABLE语句中使用一个“catchall” VALUES LESS THAN子句，该子句提供给所有大于明确指定的最高值的值：

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 NOT NULL,
    store_id INT NOT NULL
)

PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

MAXVALUE 表示最大的可能的整数值。现在，store_id 列值大于或等于16（定义了的最高值）的所有行都将保存在分区p3中。在将来的某个时候，当商店数已经增长到25, 30, 或更多 ，可以使用ALTER TABLE语句为商店21-25, 26-30,等等增加新的分区。在几乎一样的结构中，你还可以基于雇员的工作代码来分割表，也就是说，基于job_code 列值的连续区间。例如——假定2位数字的工作代码用来表示普通（店内的）工人，三个数字代码表示办公室和支持人员，四个数字代码表示管理层，你可以使用下面的语句创建该分区表：

Sql代码：

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 NOT NULL,
    store_id INT NOT NULL
)

PARTITION BY RANGE (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
);

在这个例子中, 店内工人相关的所有行将保存在分区p0中，办公室和支持人员相关的所有行保存在分区p1中，管理层相关的所有行保存在分区p2中。在VALUES LESS THAN 子句中使用一个表达式也是可能的。这里最值得注意的限制是MySQL 必须能够计算表达式的返回值作为LESS THAN (<)比较的一部分；因此，表达式的值不能为NULL 。由于这个原因，雇员表的hired, separated, job_code,和store_id列已经被定义为非空（NOT NULL）。除了可以根据商店编号分割表数据外，你还可以使用一个基于两个DATE （日期）中的一个的表达式来分割表数据。例如，假定你想基于每个雇员离开公司的年份来分割表，也就是说，YEAR(separated)的值。实现这种分区模式的CREATE TABLE 语句的一个例子如下所示：

Sql代码：

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 RANGE (YEAR(separated)) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

在这个方案中，在1991年前雇佣的所有雇员的记录保存在分区p0中，1991年到1995年期间雇佣的所有雇员的记录保存在分区p1中， 1996年到2000年期间雇佣的所有雇员的记录保存在分区p2中，2000年后雇佣的所有工人的信息保存在p3中。

RANGE分区在如下场合特别有用：
1）、当需要删除一个分区上的“旧的”数据时,只删除分区即可。如果你使用上面最近的那个例子给出的分区方案，你只需简单地使用”ALTER TABLE employees DROP PARTITION p0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表，这比运行一个如”DELETE FROM employees WHERE YEAR (separated) <= 1990;”这样的一个DELETE查询要有效得多。
2）、想要使用一个包含有日期或时间值，或包含有从一些其他级数开始增长的值的列。
3）、经常运行直接依赖于用于分割表的列的查询。例如，当执行一个如”SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;”这样的查询时，MySQL可以很迅速地确定只有分区p2需要扫描，这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

注释：这种优化还没有在MySQL 5.1源程序中启用，但是，有关工作正在进行中。

LIST分区

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

LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。 注释：在MySQL 5.1中，当使用LIST分区时，有可能只能匹配整数列表。

Sql代码：

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
)；

假定有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代码：

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 LIST(store_id)
    PARTITION pNorth VALUES IN (3,5,6,9,17), //不同之处 用的in
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如，假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录（行）可以使用查询“ALTER TABLE employees DROP PARTITION pWest；”来进行删除，它与具有同样作用的DELETE（删除）查询“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；”比起来，要有效得多。【要点】：如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。例如，假定LIST分区的采用上面的方案，下面的查询将失败：

Sql代码：

INSERT INTO employees VALUES
(224, 'Linus', 'Torvalds', '2002-05-01', 
'2004-10-12', 42, 21);

这是因为“store_id”列值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。要重点注意的是，LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。
LIST分区除了能和RANGE分区结合起来生成一个复合的子分区，与HASH和KEY分区结合起来生成复合的子分区也是可能的。

HASH分区

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

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

Sql代码：

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;

如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。例外：对于NDB Cluster（簇）表，默认的分区数量将与簇数据节点的数量相同，这种修正可能是考虑任何MAX_ROWS设置，以便确保所有的行都能合适地插入到分区中。

LINER HASH

MySQL还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY”子句中添加“LINEAR”关键字。

Sql代码：

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 LINEAR HASH(YEAR(hired))
PARTITIONS 4;

假设一个表达式expr，当使用线性哈希功能时，记录将要保存到的分区是num 个分区中的分区N，其中N是根据下面的算法得到： 1. 找到下一个大于num.的、2的幂，我们把这个值称为V ，它可以通过下面的公式得到： 2. V = POWER(2, CEILING(LOG(2, num))) （例如，假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4，则V = POWER(2,4), 即等于16）。 3. 设置 N = F(column_list) & (V – 1). 4. 当 N >= num: · 设置 V = CEIL(V / 2) · 设置 N = N & (V – 1) 例如，假设表t1，使用线性哈希分区且有4个分区，是通过下面的语句创建的： CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR HASH( YEAR(col3) ) PARTITIONS 6; 现在假设要插入两行记录到表t1中，其中一条记录col3列值为’2003-04-14′，另一条记录col3列值为’1998-10-19′。第一条记录将要保存到的分区确定如下： V = POWER(2, CEILING(LOG(2,7))) = 8 N = YEAR(’2003-04-14′) & (8 – 1) = 2003 & 7 = 3 (3 >= 6 为假（FALSE）: 记录将被保存到#3号分区中) 第二条记录将要保存到的分区序号计算如下： V = 8 N = YEAR(’1998-10-19′) & (8-1) = 1998 & 7 = 6 (6 >= 4 为真（TRUE）: 还需要附加的步骤) N = 6 & CEILING(5 / 2) = 6 & 3 = 2 (2 >= 4 为假（FALSE）: 记录将被保存到#2分区中) 按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000吉）数据的表。它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

KSY分区

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

Sql代码：

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

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。

三.分区管理

1.删除分区：

mysql> alter table user drop partition p4;

alter table 表名 drop partition 分区名;

2.新增分区：

//range添加新分区
mysql> alter table user add partition(partition p4 values less than MAXVALUE);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

//list添加新分区
mysql> alter table list_part add partition(partition p4 values in (25,26,28));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

//hash重新分区
mysql> alter table hash_part add partition partitions 4;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

//key重新分区
mysql> alter table key_part add partition partitions 4;
Query OK, 1 row affected (0.06 sec)    //有数据也会被重新分配
Records: 1  Duplicates: 0  Warnings: 0

//子分区添加新分区，虽然我没有指定子分区，但是系统会给子分区命名的
mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table sub1_part\G;
*************************** 1. row ***************************
Table: sub1_part
Create Table: CREATE TABLE `sub1_part` (
`news_id` int(11) NOT NULL COMMENT ‘新闻ID’,
`content` varchar(1000) NOT NULL DEFAULT ” COMMENT ‘新闻内容’,
`u_id` varchar(25) NOT NULL DEFAULT ” COMMENT ‘来源IP’,
`create_time` date NOT NULL DEFAULT ‘0000-00-00′ COMMENT ‘时间’
) ENGINE=InnoDB DEFAULT CHARSET=utf8
!50100 PARTITION BY RANGE (YEAR(create_time))
SUBPARTITION BY HASH (TO_DAYS(create_time))
(PARTITION p0 VALUES LESS THAN (1990)
(SUBPARTITION s0 ENGINE = InnoDB,
SUBPARTITION s1 ENGINE = InnoDB,
SUBPARTITION s2 ENGINE = InnoDB),
PARTITION p1 VALUES LESS THAN (2000)
(SUBPARTITION s3 ENGINE = InnoDB,
SUBPARTITION s4 ENGINE = InnoDB,
SUBPARTITION good ENGINE = InnoDB),
PARTITION p2 VALUES LESS THAN (3000)
(SUBPARTITION tank0 ENGINE = InnoDB,
SUBPARTITION tank1 ENGINE = InnoDB,
SUBPARTITION tank3 ENGINE = InnoDB),
PARTITION p3 VALUES LESS THAN MAXVALUE
(SUBPARTITION p3sp0 ENGINE = InnoDB,    //子分区的名子是自动生成的
SUBPARTITION p3sp1 ENGINE = InnoDB,
SUBPARTITION p3sp2 ENGINE = InnoDB))
1 row in set (0.00 sec)

3.重新分区

//range重新分区
mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);
Query OK, 11 rows affected (0.08 sec)
Records: 11  Duplicates: 0  Warnings: 0

//list重新分区
mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

//hash和key分区不能用REORGANIZE，官方网站说的很清楚
mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PARTITION 9′ at line 1

子分区

在分区的基础上 再分区

指定不同的盘符

CREATE TABLE `T_part` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) 
PARTITION BY RANGE (f_id)
SUBPARTITION BY HASH(F_ID)
(
    PARTITION p0     VALUES less THAN (10)
    (
        SUBPARTITION s0 
            DATA DIRECTORY = '/disk0/data' 
            INDEX DIRECTORY = '/disk0/idx',
        SUBPARTITION s1 
            DATA DIRECTORY = '/disk1/data' 
            INDEX DIRECTORY = '/disk1/idx'
    ),
    PARTITION p1    VALUES less THAN (20)
    (
        SUBPARTITION s2
            DATA DIRECTORY = '/disk0/data' 
            INDEX DIRECTORY = '/disk0/idx',
        SUBPARTITION s3 
            DATA DIRECTORY = '/disk1/data' 
            INDEX DIRECTORY = '/disk1/idx'
    )
)

分区支持的数据类型

• 所有的整型：TINYINT, SMALLINT, MEDUMINT, INT, BIGINT。其它的数字类型如DECIMAL, FLOAT都不支持。
• DATE, DATETIME。其它时间类型不支持。
• CHAR, VARCHAR, BINARY, VARBINARY等字符串类型。TEXT, BLOB等不支持

备注：TIMESTAMP不能用于分区列的原因，因为这种数据类型受时区限制，会受数据库时区的影响。

定时任务 & 时间戳似乎也能分区啊

-- 普通添加第一个表分区语句
ALTER TABLE iot_divice_info PARTITION by range(UNIX_TIMESTAMP(createtime))(partition par0 values less than (UNIX_TIMESTAMP('2016-02-01 00:00:00')));
-- 普通添加表分区语句
ALTER TABLE iot_divice_info add partition (partition par1 values less than (UNIX_TIMESTAMP('2016-03-01 00:00:00')));

--通过时间戳类型的字段对表分区存储过程
create PROCEDURE pro_sys_logByMonthSJC(IN tableName VARCHAR(20),IN timeColName VARCHAR(20))
COMMENT '每月按时添加表分区的存储过程，由定时任务调用'
BEGIN
    DECLARE p_id int;
    DECLARE nextDate date;
    DECLARE lastDate LONG;
    --获取表中的现有的分区数量数量
    SELECT COUNT(partition_name) into p_id FROM INFORMATION_SCHEMA.partitions 
                                        WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=tableName;
    if p_id=0 then
        --获取下个月第一天的时间值，根据此值设置时间分区
        SELECT DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH) into nextDate from DUAL; 
        set @v_add=CONCAT('ALTER table ',tableName,' PARTITION by range(UNIX_TIMESTAMP(',timeColName,'))
                                    (partition ',CONCAT('par',p_id),' values less than (UNIX_TIMESTAMP(\'',nextDate,'\')))');
    ELSE
        --获取表中现有的最大的分区日期
        SELECT max(partition_description) des into lastDate from INFORMATION_SCHEMA.partitions 
                WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME=tableName;
        --获取下个月第一天的时间值，根据此值设置时间分区
        select DATE_ADD(FROM_UNIXTIME(lastDate),INTERVAL 1 MONTH)  into nextDate  from dual;
        set @v_add=CONCAT('alter table ',tableName,' add partition (partition ',CONCAT('par',p_id),
                        ' values less than (UNIX_TIMESTAMP(\'',nextDate,'\')))');
    END IF;
    PREPARE stmt from @v_add;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;
-- 测试存储过程
call pro_sys_logByMonthSJC('iot_divice_info','createtime');
-- 删除表分区
ALTER TABLE iot_divice_info REMOVE PARTITIONING; 

--每月创建一个分区的定时任务
create event event_sysLog on SCHEDULE EVERY 1 MONTH STARTS CURRENT_TIMESTAMP
on COMPLETION PRESERVE
ENABLE
do call pro_sys_logByMonthSJC('sys_log_storage','createtime');
————————————————
版权声明：本文为CSDN博主「奥法vx」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/aofavx/article/details/50462687

分库方案

MySQL 的高可用架构大多都是一主多从，所有写入操作都发生在 Master 上，随着业务的增长，数据量的增加，很多接口响应时间变得很长，经常出现 Timeout，而且通过升级 MySQL 实例配置已经无法解决问题了，这时候就要分库，通常有两种做法：按业务拆库和按表分库，下面就介绍这两种分库方案啦。

分库分表产生的问题

通过上面的分表和分库方案的介绍，主要会遇到下面问题：

1. MySQL单 Master 的写入性能瓶颈。
2. 分库分表后的 SQL 解析处理，服务调用链路变长，系统变得不稳定。
3. 分库分表后动态扩容不好实现，例如开始分了20个表，不影响业务的情况下扩容至50个表不好实现。
4. 跨库join问题

垂直拆分遇到的问题和解决方案

跨库Join问题
在垂直拆分之前，系统中所需的数据是可以通过表 Join 来完成的，而拆分之后，数据库可能分布式在不同 RDS 实例，Join 处理起来比较麻烦，根据 MySQL 开发规范，一般是禁止跨库 Join 的，那该怎么处理呢？

首先要考虑这种垂直拆分的合理性，如果可以调整，那就优先调整，如果无法调整，根据以往的实际经验，总结几种常见的解决思路。

全局表
用过 mycat 做分库分表的朋友都清楚，有个全局表的概念，也就是每个 DataNode 上都有一份全量数据，例如一些数据字典表，数据很少修改，可以避免跨库 Join 的性能问题。

数据同步
对于分布式系统，不同的服务的数据库是分布在不同的 RDS 实例上的，在禁止跨库 Join 的情况下，数据同步是一种解决方案。

分布式事务问题
拆分之后，数据分布在不同的 RDS 实例上，对表的 DML 操作就变成了多个子表的 DML 操作，就涉及到分布式事务，也要遵循事务 ACID 特性，同时也会提到两个重要的理论：CAP（Consistency一致性，Availability可用性，Partition tolerance分区容忍性Partitiontolerance）和BASE（Basically Available基本可用， Soft state软状态，Eventually consistent最终一致性），进而产生了解决分布式事务问题不同的方案。

MySQL XA事务

MySQL支持分布式事务（XA 事务或者 2PC 两阶段提交），分为两个阶段：Prepare 和 Commit，事务处理过程如下

本地消息表

本地消息表实现方式应该是业界使用最多的，其核心思想是将分布式事务拆分成本地事务进行处理，其基本的设计思想是将远程分布式事务拆分成一系列的本地事务。

水平拆分遇到问题的解决方案

分布式全局唯一ID
MySQL InnoDB的表都是使用自增的主键ID，分库分表之后，数据表分布不同的分片上，如果使用自增 ID 作为主键，就会出现不同分片上的主机 ID 重复现象，可以利用 Snowflake 算法生成唯一ID。

分片键选择
选择分片键时，需要先统计该表上的所有的 SQL，尽量选择使用频率且唯一值多的字段作为分片键，既能做到数据均匀分布，又能快速定位到数据位置，例如user_id，order_id等。

数据扩容
举个例子，目前交易数据库 trade 中的订单表 orders 已经做了水平分库（位于两个不同RDS实例上），这时发现两个 RDS 写入性能还是不够，需要再扩容一个RDS，同时将 orders 从原来的 20 个子表扩容到 40个（user_id % 40），这就需要迁移数据来实现数据重平衡，既要停机迁移数据，又要修改代码，有点出力不讨好的感觉啦。

跨库Join问题
跟垂直拆分中的跨库 Join 问题是一样的。

跨库排序分页
在处理order by user_id limit n场景是，当排序字段就是分片字段 user_id 的时候，通过分片键可以很容易定位到具体的分片，而当排序字段非分片字段的时候，例如order by create_time，处理起来就会变得复杂，需要在不同的分片节中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。

跨库函数处理
在使用max，min，sum，count之类的函数进行统计和计算的时候，需要先在每个分片数据源上执行相应的函数处理，然后将各个结果集进行二次处理，最终再将处理结果返回。

ER分片
在 RDBMS 系统中，表之间往往存在一些关联的关系，如果可以先确定好关联关系，并将那些存在关联关系的表记录存放在同一个分片上，就能很好地避免跨分片 join 问题。

非分片键过滤

大部分业务场景都可以根据分片键来过滤，但是有些场景没有分片键过滤，例如按照状态和时间范围来查询订单表 orders，常见的SQL 这样的。

图片
这种就很痛苦了，只能全部分片数据扫描一遍，将每个分片的数据Union之后再回复给客户端，这种场景可以考虑创建复合索引（status，create_time）让SQL走索引范围扫描，同时减少返回的数据量，如果是核心业务场景，可以考虑实时实时数仓（例如基于MPP架构的分析型数据库 ADB，分布式列式数据库 Clickhouse），将需要的表实时同步到数仓，然后再做处理，这也是实际业务中常见一种解决方案。

参考资料

前任都能看懂的分库分表方案