mysql union 分区_MySQL 之分区分表

1、为什么要分表？

数据库数据越来越大，随之而来的是单个表中数据太多。以至于查询速度变慢，而且由于表的锁机制导致应用操作也搜到严重影响，出现了数据库性能瓶颈。

mysql中有一种机制是表锁定和行锁定，是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作，必须等我对表操作完才行。行锁定也一样，别的sql必须等我对这条数据操作完了，才能对这条数据进行操作。当出现这种情况时，我们可以考虑分表或分区。

2、MySQL分表

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的表名，然后去操作它。

将单个数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，根据一定的算法(如用hash的方式，也可以用求余(取模)的方式)，让用户访问不同的表，这样数据分散到多个数据表中，减少了单个数据表的访问压力。提升了数据库访问性能。分表的目的就在于此，减小数据库的负担，缩短查询时间。

Mysql分表分为垂直切分和水平切分，具体区别如下：

垂直切分是指数据表列的拆分，把一张列比较多的表拆分为多张表 通常我们按以下原则进行垂直拆分: 把不常用的字段单独放在一张表; 把text，blob(binary large object，二进制大对象)等大字段拆分出来放在附表中;

经常组合查询的列放在一张表中; 垂直拆分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用join关键起来即可。

水平拆分是指数据表行的拆分，把一张的表的数据拆成多张表来存放。 水平拆分原则，通常情况下，我们使用hash、取模等方式来进行表的拆分 比如一张有400W的用户表users，为提高其查询效率我们把其分成4张表users1，users2，users3，users4 通过用ID取模的方法把数据分散到四张表内Id%4= [0,1,2,3] 然后查询,更新,删除也是通过取模的方法来查询 部分业务逻辑也可以通过地区，年份等字段来进行归档拆分; 进行拆分后的表，这时我们就要约束用户查询行为。比如我们是按年来进行拆分的,这个时候在页面设计上就约束用户必须要先选择年,然后才能进行查询。

3、利用merge存储引擎实现分表

注：只有myisam引擎的原表才可以利用merge存储引擎实现分表。

merge分表，分为主表和子表，主表类似于一个壳子，逻辑上封装了子表，实际上数据都是存储在子表中的。 我们可以通过主表插入和查询数据，如果清楚分表规律，也可以直接操作子表。

例：

1)创建一个完整表

mysql> create database test1;

mysql> use test1;

mysql> create table member

-> (

-> id bigint auto_increment primary key,

-> name varchar(20),

-> sex tinyint not null default '0'

-> )engine=myisam default charset=utf8 auto_increment=1;

#插入数据

mysql> insert into member(name,sex) values('tom1',1);

mysql> insert into member(name,sex) select name,sex from member; # 插入语句多执行几次，即可插入大量的数据

mysql> select count(*) from member; # 手贱了，这里我插入了16384条数据

+----------+

| count(*) |

+----------+

| 16384 |

+----------+

1 row in set (0.00 sec)

2)对上面完整的表进行分表

分表注意事项：

子表和主表的字段定义需要一致，包括数据类型，数据长度等；

当分