MySQL常见的水平切分方案一般分为：分库分表、分区表。

但是互联网公司一般不用MySQL分区表，看完这篇文章你就会多少有些了解了。

分区

分区概念

所有数据，逻辑上还在一个表中，但物理上，把数据表的文件和索引分散存储在不同的物理文件中。这是MySQL5.1之后支持的功能，业务代码无需改动。

分区类型

分区具有如下4种类型：

• Range分区：是对一个连续性的行值，按范围进行分区；比如：id小于100；id大于100小于200；
• List分区：跟range分区类似，不过它存放的是一个离散值的集合。
• Hash分区：对用户定义的表达式所返回的值来进行分区。可以写partitions (分区数目)，或直接使用分区语句,比如partition p0 values in…..。
• Key分区：与hash分区类似，只不过分区支持一列或多列，并且MySQL服务器自身提供hash函数。

range范围进行分区

create table user(
    id int auto_increment primary key,
    name varchar (30)
)  engine=InnoDB
partition by range(id)(
    partition p0 values less than(5),
    partition p1 values less than(10),
    partition p3 values less than(15),
    partition p4 values less than(maxvalue)
);

其实上面的分区创建，我们可知道，它的表类型为InnoDB，而每个分区的引擎也是InnoDB，这个可以通过show create table tablename查看。
将用户表分成4个分区，以每5条记录为界限，每个分区都有自己独立的数据、索引文件的存放目录。

insert into user values(null,'测试');
explain partitions select * from user where id =1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | p0         | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+-------+

可以看见仅仅在p0分区执行了这条查询，这样就非常快速了。
还可以将这些分区所在的物理磁盘分开完全独立，可以提高磁盘IO吞吐量。

CREATE TABLE users (  
    id int auto_increment primary key,
    name varchar (30)
)partition BY range (id) (  
       PARTITION p0 VALUES LESS THAN (3000000)
       DATA DIRECTORY = '/data0/data'
       INDEX DIRECTORY = '/data0/index',

       PARTITION p1 VALUES LESS THAN (6000000)
       DATA DIRECTORY = '/data1/data'
       INDEX DIRECTORY = '/data1/index',

       PARTITION p2 VALUES LESS THAN (9000000)
       DATA DIRECTORY = '/data2/data'
       INDEX DIRECTORY = '/data2/index',

       PARTITION p3 VALUES LESS THAN MAXVALUE
       DATA DIRECTORY = '/data3/data'
       INDEX DIRECTORY = '/data3/index'
);

注意：上面的具体4个分布，在windows系统上目前还不支持。

list进行分区

create table user(
    id int auto_increment primary key,
    name varchar(30),
    index id(id)
) engine=InnoDB  partition by list(id)(
    partition p0 values in(1,3),
    partition p1 values in(2,4,6),
    partition p3 values in(10)
);

分成3个区,同样可以将分区设置的独立的磁盘中。
list 分区只能把你插入的值放在某个已定的分区里，若没有那个值，就显示不能插入。

[Err] 1526 - Table has no partition for value 5

hash进行分区

create table user(
    id int auto_increment primary key,
    name varchar(30)
) engine=InnoDB  partition by hash(id) partitions 4(
    partition p0,
    partition p1,
    partition p2,
    partition p3
);

如果分为4个分区，那当我插入数据时，哪些数据是放在哪些分区里呢？ 当我对某个id值进行检索时，它明确说放到哪个分区里？或者说是有什么内部机制？
使用hash分区，最主要就是确保数据的分配，它是基于create table时提供的表达式。不必定义单独的分区，只要使用partitions关键字和所需要分多少个区的数字。语句如上所述。

key进行分区

create table user(
    id int auto_increment primary key,
    name varchar(30),
    index_id(id)
) engine=InnoDB  partition by key(id) partitions 4(
    partition p0,
    partition p1,
    partition p2,
    partition p3
);

这个分区类似于hash分区，除了MySQL服务器使用它本身的hash表达式，不像其他类型的分区，不必要求使用一个int或null的表达式。

子分区进行分区

create table user(
    id int auto_increment primary key,
    name varchar(30),
    store_id int
) engine=InnoDB  partition by range(id)
subpartition by hash(store_id) subpartitions 2(
    partition p0 values less than(5),
    partition p1 values less than(10),
    partition p3 values less than(15)
);

分区管理

MySQL可以通过如下方式来获取分区表的信息:

show create tabe table;  //表详细结构
show table status;       //表的各种参数状态
select * from information_schema.partitions；//通过数据字典来查看表的分区信息
explain partitions select * from table;   // 通过此语句来显示扫描哪些分区，及他们是如何使用的.

修改部分分区

由于我们平常使用的数据库大都是动态运行的，所以只对某个表分区进行修改就OK了。
可以对range或list表分区进行add或drop，也可以对hash或key分区表进行合并或分解。这些动作都在alter table语句里进行。

• 新增分区，使用add partition 关键字来对已有分区表进行添加。

# 新增 RANGE 分区
Alter table user add partition(
    Partition p5 values less than(maxvalue)
)
# 新增 LIST 分区
ALTER table user add partition (
    partition p4 VALUES IN (16,17,18,19)
);
# 新增 HASH/KEY 分区 将分区总数扩展到8个
ALTER table user add partition PARTITIONS 8;

• 分解分区，使用reorganize partition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。

Alter table user reorganize partition p0 into(
    partition n0 values less than(5000),
    partition n1 values less than(10000)
);

• 合并分区：像上面把p0分成n0和n1，现在在把2个合并为一个。

# RANGE
Alter table user reorganize partition n0,n1 into(
    Partition p0 values less than(10000)
);
# LIST
Alter table user reorganize partition p0,p1 INTO (
    partition p0 VALUES IN(0,1,4,5,8,9,12,13)
);  
# HASH/KEY 分区重建 
Alter table user reorganize partition COALESCE PARTITION 2;
#用 REORGANIZE 方式重建分区的数量变成2，在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。

修改所有的分区

• 在into关键字之前或之后都指定多个分区

Alter table user reorganize partition p0,p1,p2,p3,p4,p5 into(
    Partition r0 values less than(25000),
    Partition r1 values less than(50000),
    Partition r2 values less than(maxvalue)
);

• Coalesce 合并分区

Merge分区的另一种方法就是alter table….coalesce partition语句，你不能对hash或key分区进行删除

Alter table user coalesce partition1;

• Redefine重定义分区

Alter table user partition by hash(id) partitions 4;

删除部分分区

• Drop 分区：

可以对range或list类型的分区通过drop partition 关键字进行删除

Alter table user drop partition p0;

注意：

1. 对这个分区进行删除时，你会把这个分区的所有数据进行删除，与delete语句相等；
2. 在做alter table..drop partition时，必须有drop权限；
3. 运行这个删除命令，它不会返回删除了的行，可以通过select count()语句查看。如果想对多个分区进行删除，可以使用如下命令语句：Alter table orders_range drop partition p1,p2;

删除所有分区

通过如下命令语句删除表中所有分区，最后是一个正规表.

Alter table user remove partitioning;

分区的优缺点

优点

1. 可以提高数据库的性能，查找只查找相应的分区不用全部查找了；
2. 对大表(行较多)的维护更快、更容易，因为数据分布在不同的逻辑文件上；
3. 删除分区或它的数据是容易的，因为它不影响其他表。
4. 进行大数据搜索时可以进行并行处理。

插入到分区表的速度更快。随着InnoDB表变大，它们的基于PK的B-Trees可以得到非常“深”，意味着大容量INSERT可以逐渐变慢。分区减慢了这个过程。
如果您能够在查询中使用分区键，那么锚定在PK +分区键上的搜索将比未分区的表快得多。

缺点

分区表的一些缺点，是大数据量，高并发量的业务难以接受的：

1. 如果SQL不走分区键，很容易出现全表锁；
2. 在分区表实施关联查询，就是一个灾难；
3. 分库分表，自己掌控业务场景与访问模式，可控；分区表，工程师写了一个SQL，自己无法确定MySQL是怎么玩的，不可控；
   类似于，不要把业务逻辑实现在存储过程，用户自定义函数，触发器里，而要实现在业务代码里一样。
4. DBA给OP埋坑，容易大打出手，造成同事矛盾；

如果你有一个表上有很多次级索引，分区不会提高性能，实际上可能会伤害它，特别是如果你做搜索，最终需要访问许多或有时所有的分区通过次级索引。
设计较差的分区可以混乱缓存局部性很多，创建缓存未命中。

当然，在数据量和并发量不太大，或者按照时间来存储冷热数据或归档数据的一些特定场景下，分区表还是有上场机会的。

分区的限制

1. 主键或者唯一索引必须包含分区字段，如primary key (id,username),不过innoDB的大组建性能不好。
2. 很多时候，使用分区就不要在使用主键了，否则可能影响性能。
3. 只能通过int类型的字段或者返回int类型的表达式来分区，通常使用year或者to_days等函数（mysql 5.6 对限制开始放开了）。
4. 每个表最多1024个分区，而且多分区会大量消耗内存。
   当你建立分区表包含很多分区但没有超过1024限制的时候，如果报错 Got error 24 from storage engine，那意味着你需要增大open_files_limit参数。
5. 分区的表不支持外键，相关的逻辑约束需要使用程序来实现。

6. 分区后，可能会造成索引失效，需要验证分区可行性。

7. 不支持FULLTEXT indexes（全文索引）

8. 分区表不支持Key caches。
9. 所有的分区必须使用同种引擎；
10. 不支持三维数据类型(GIS); 不能对临时表进行分区；
11. 分区表不支持INSERT DELAYED.

12. 在分区表使用ALTER TABLE … ORDER BY，只能在每个分区内进行order by。

13. 子分区方面： 只允许对range和list类型的分区再进行分区；

14. 子分区的类型只允许是hash或key. 分区表达式方面： Range，list, hash分区必须是int类型；
15. Key分区不可以有text，blob类型；
16. 不允许使用UDF,存储函数，变量，操作符(|,,^,<<,>>,~)和一些内置的函数；
17. 在表创建之后sql mode不可以改变；
18. 在分区表达式中，不允许子查询；

由此可见，分区的限制和弊端，让互联网公司都不得采取分区的方式。

MySQL 分区(Partition)脚本

MySQL 5.1 中新特性分区(partition) shell 脚本。注意 MySQL 只支持小于等于 1024 个分区

#!/bin/sh

# Set these values
PART=0
ORI=5000
STEP=5000
MAX=3000000

for NUM in `seq -f %f $ORI $STEP $MAX | cut -d. -f1`
do
    echo "PARTITION $PART VALUES LESS THAN ($NUM)," >> /tmp/partition.sql
    part=`expr $PART + 1`
done
echo "PARTITION $PART VALUES LESS THAN MAXVALUE" >> /tmp/partition.sql

分表

分表的概念

分库分表：顾名思义。是把一个很大的库（表）的数据分到几个库（表）中，每个库（表）的结构都相同，但他们可以分布在不同的MySQL实例，甚至不同的物理机器上，以达到降低单库（表）数据量，提高读写性能的目的。

分表和分区类似，区别是，分区是把一个逻辑表文件分成几个物理文件后进行存储，而分表则是把原先的一个表分成几个表。进行分表查询时可以通过union或者视图。

为什么分表

• 如果一个表的每条记录的内容很大，那么就需要更多的IO操作，如果字段值比较大，而使用频率相对比较低，可以将大字段移到另一张表中，当查询不查大字段的时候，这样就减少了I/O操作
• 如果一个表的数据量很少，那么查询就很快；如果表的数据量非常非常大，那么查询就变的比较慢；也就是表的数据量影响这查询的性能。
• 表中的数据本来就有独立性，例如分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，而另外一些数据不常用。

分割方式

分表有两种分割方式，一种垂直分割另一种水平分割。

垂直分割

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 垂直分割一般用于拆分大字段和访问频率低的字段，分离冷热数据。
垂直分割适用于记录不是非常多的，但是字段却很多，这样占用空间比较大，检索时需要执行大量的I/O，严重降低了性能，这个时候需要把大的自读那拆分到另一个表中，并且该表与源表时一对一关系。

垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂。

水平分割

水平拆分是指数据表行的拆分，表的行数超过500万行或者单表容量超过10GB时，查询就会变慢，这时可以把一张的表的数据拆成多张表来存放。水平分表尽可能使每张表的数据量相当，比较均匀。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水品拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水品拆分最好分库。

水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决，跨界点Join性能较差，逻辑复杂。

水平拆分会给应用增加复杂度，它通常在查询是需要多个表名，查询所有数据需要union操作。在许多数据库应用中，这种复杂性会超过它带来的优点，因为只要索引关键字不大，则在索引用于查询时，表中增加2-3倍数据量，查询时也就增加读一个索引层的磁盘次数，所以水平拆分要考虑数据量的增长速度，根据实际情况决定是否需要对表进行水平拆分。

例如日志记录就可以采取水平分割，用时间进行分表。
对于一个会员表，按对3的模进行分割 : table = id%5
如果id%5=0 则将用户数据放入到user_0表中，如id%5=1 就放入user_1表中，依次类推。

对于一些流量统计系统，其数据量比较大，并且对过往数据的关注度不高，这时按年、月、日进行分表，将每日统计信息放到一个以日期命名的表中；或者按照增量进行分表，如每个表100万数据，超过100万就放入第二个表。还可以按Hash进行分表，但是按日期和取模余数分表最为常见，也容易扩展。
分表后可能会遇到新的问题，那就是查询，分页和统计。通用的方法是在程序中进行处理，辅助视图。

分表案例

<?php
$i = $uid % 10;
$table = sprintf("user_info_%s", $i);
$check_sql = "SELECT COUNT(1) exist from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND table_name ='{$table}' ";
$result = $this->link->fetchOne($check_sql);
$sql = <<<EOD
CREATE TABLE IF NOT EXISTS `{$table}` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) COMMENT '用户姓名'
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
EOD;
if ($result['exist'] == 0) {
    $this->link->execute($sql);
}
return $table;

参考文献：
MYSQL之水平分区——MySQL partition分区I（5.1）

更多文献：
mysql的分区和分表
mysql 批量创建表及自动分区
Mysql分区表及自动创建分区Partition
php实现mysql分表
MySQL性能优化(六)：分区