MYSQL分区分表

为什么要分表和分区？

我们的数据库数据越来越大，随之而来的是单个表中数据太多。以至于查询速度变慢，而且由于表的锁机制导致应用操作也受到严重影响，出现了数据库性能瓶颈。
mysql中有一种机制是表锁定和行锁定，是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作，必须等我对表操作完才行。行锁定也一样，别的sql必须等我对这条数据操作完了，才能对这条数据进行操作。当出现这种情况时，我们可以考虑分表或分区。

分表

什么是分表？

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三个文件，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] 然后查询,更新,删除也是通过取模的方法来查询 部分业务逻辑也可以通过地区，年份等字段来进行归档拆分; 进行拆分后的表，这时我们就要约束用户查询行为。比如我们是按年来进行拆分的,这个时候在页面设计上就约束用户必须要先选择年,然后才能进行查询。

分表的几种方式：

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

2）预先估计会出现大数据量并且访问频繁的表，将其分为若干个表
根据一定的算法（如用hash的方式，也可以用求余（取模）的方式）让用户访问不同的表。 例如论坛里面发表帖子的表，时间长了这张表肯定很大，几十万，几百万都有可能。聊天室里面信息表，几十个人在一起一聊一个晚上，时间长了，这张表的数据肯定很大。像这样的情况很多。所以这种能预估出来的大数据量表，我们就事先分出个N个表，这个N是多少，根据实际情况而定。以聊天信息表为例：我们事先建100个这样的表，message_00,message_01,message_02…message_98,message_99.然后根据用户的ID来判断这个用户的聊天信息放到哪张表里面，可以用hash的方式来获得，也可以用求余的方式来获得，方法很多。 或者可以设计每张表容纳的数据量是N条，那么如何判断某张表的数据是否容量已满呢？可以在程序段对于要新增数据的表，在插入前先做统计表记录数量的操作，当<N条数据，就直接插入，当已经到达阀值，可以在程序段新创建数据库表（或者已经事先创建好），再执行插入操作）。

3）利用merge存储引擎来实现分表
如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码，因为程序里面的sql语句已经写好了，用merge存储引擎来实现分表, 这种方法比较适合。 merge分表，分为主表和子表，主表类似于一个壳子，逻辑上封装了子表，实际上数据都是存储在子表中的。 我们可以通过主表插入和查询数据，如果清楚分表规律，也可以直接操作子表。 下面我们来实现一个简单的利用merge存储引擎来实现分表的演示： 创建一个完整表存储着所有的成员信息（表名为member）

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed

mysql> create table member
    -> (
    -> id bigint primary key auto_increment,
    -> 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);
Query OK, 1 row affected (0.03 sec)

mysql> insert into member(name,sex) select name,sex from member;
mysql> insert into member(name,sex) select name,sex from member;
mysql> insert into member(name,sex) select name,sex from member;
mysql> insert into member(name,sex) select name,sex from member;

第二条语句多执行几次就有了很多数据

mysql> select * from member;
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  1 | tom1 |   1 |
|  2 | tom1 |   1 |
|  3 | tom1 |   1 |
|  4 | tom1 |   1 |
|  5 | tom1 |   1 |
|  6 | tom1 |   1 |
|  7 | tom1 |   1 |
|  8 | tom1 |   1 |
|  9 | tom1 |   1 |
| 10 | tom1 |   1 |
| 11 | tom1 |   1 |
| 12 | tom1 |   1 |
| 13 | tom1 |   1 |
| 14 | tom1 |   1 |
| 15 | tom1 |   1 |
| 16 | tom1 |   1 |
+----+------+-----+
16 rows in set (0.00 sec)

mysql> select count(*) from member;
+----------+
| count(*) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)

下面我们进行分表，这里我们把mevmber分两个表tb_member1,tb_member2

mysql> create table tb_member1
    -> (
    -> id bigint primary key,
    -> name varchar(20),
    -> sex tinyint not null default '0'
    -> )engine=myisam default charset=utf8;
 
mysql> create table tb_member1
    -> (
    -> id bigint primary key,
    -> name varchar(20),
    -> sex tinyint not null default '0'
    -> )engine=myisam default charset=utf8; 

//创建tb_member2也可以用下面的语句 create table tb_member2 like tb_member1;

创建主表tb_member

mysql> create table tb_member
    -> (
    -> id bigint primary key,
    -> name varchar(20),
    -> sex tinyint not null default '0'
    -> )engine=merge union=(tb_member1,tb_member2) insert_method=last charset=utf8;

注：INSERT_METHOD,此参数
INSERT_METHOD = NO 表示该表不能做任何写入操作只作为查询使用。
INSERT_METHOD = LAST表示插入到最后的一张表里面。
INSERT_METHOD = first表示插入到第一张表里面。

查看一下tb_member表的结构

mysql> desc tb_member;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint(20)  | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | tinyint(4)  | NO   |     | 0       |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

注：查看子表与主表的字段定义要一致

查看所有表：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| member         |      member：可以理解为父表（总表）
| tb_member      |      tb_member:两个子表结合的主表，操作都在此表
| tb_member1     |      tb_member1:子表1
| tb_member2     |      tb_member2:子表2
+----------------+
4 rows in set (0.00 sec)

接下来，我们把数据分到两个分表中去：

mysql> insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0;
mysql> insert into tb_member2(id,name,sex) select id,name,sex from membber where id%2=1;

查看两个子表的数据：

mysql> select * from tb_member1;
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  2 | tom1 |   1 |
|  4 | tom1 |   1 |
|  6 | tom1 |   1 |
|  8 | tom1 |   1 |
| 10 | tom1 |   1 |
| 12 | tom1 |   1 |
| 14 | tom1 |   1 |
| 16 | tom1 |   1 |
+----+------+-----+
8 rows in set (0.00 sec)

mysql