本文主要向大家介绍了MySQL数据库之MySQL数据库分区分表配置以及原理、特点、概念、区别详解 ，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。

一、为什么要分表和分区？

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

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

1、分表

什么是分表？

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

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

Mysql分表分为垂直切分和水平切分

垂直切分是指数据表列的拆分，把一张列比较多的表拆分为多张表

通常我们按以下原则进行垂直拆分:

把不常用的字段单独放在一张表;

把text，blob（binary large object，二进制大对象）等大字段拆分出来放在附表中;

经常组合查询的列放在一张表中;

垂直拆分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用jion关键起来即可。

水平拆分是指数据表行的拆分，把一张的表的数据拆成多张表来存放。

水平拆分原则

通常情况下，我们使用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条数据，就直接插入，当已经到达阀值，可以在程序段新创建数据库表（或者已经事先创建好），再执行插入操作）。< p="">

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

如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码，因为程序里面的sql语句已经写好了，用merge存储引擎来实现分表, 这种方法比较适合。

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

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

下面我们来实现一个简单的利用merge存储引擎来实现分表的演示：

（1.）创建一个完整表存储着所有的成员信息（表名为member）

mysql> create database test;

mysql> use test;

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;

（2.）加入点测试数据：

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

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

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

（3.）下面我们进行分表，这里我们把member完整表分成两个子表tb_member1,tb_member2

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

（4.）创建主表tb_member

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

查看一下tb_member表的结构:

mysql> desc tb_member;

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

（5.）接下来，我们把数据分到两个子表中去：

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 member where id%2=1;

查看两个子表的数据：

查看一下主表的数据：

测试查看：

注意：总表只是一个外壳，存取数据发生在一个一个的子表里面。

注意：每个子表都有自已独立的相关表文件，而主表只是一个壳，并没有完整的相关表文件

2、分区

什么是分区？

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，分区后，表还是一张表，但数据散列到多个位置了。app读写的时候操作的还是表名字，db自动去组织分区的数据。

分区主要有两种形式： 

水平分区（Horizontal Partitioning） 这种形式分区是对表的行进行分区，所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。

举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。

垂直分区（Vertical Partitioning）这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。

举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

分区技术支持

在5.6之前，使用这个参数查看当将配置是否支持分区

mysql> SHOW VARIABLES LIKE '%partition%';

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

|Variable_name          | Value |

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

| have_partition_engine | YES   |

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

如果是yes表示你当前的配置支持分区

在5.6及以采用后，则采用如下方式进行查看

mysql> show plugins;

在显示结果中，可以看到partition是ACTIVE的，表示支持分区

下面我们先演示一个按照范围(range)方式的表分区

（1.）创建range分区表

mysql> create database test2;

mysql> use test2;

mysql> create table  user (

    -> id int ,

    -> name varchar(30) ,

    -> sex int,

    -> primary key(id))default charset=utf8 

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

-> );

（2.）插入些数据

到存放数据库表文件的地方看一下

从information_schema系统库中的partitions表中查看分区信息

从某个分区中查询数据

（3.）新增分区

mysql> alter table test2.user add partition (partition partionname values less than (n));

（4.）删除分区

当删除了一个分区，也同时删除了该分区中所有的数据。

（5.）分区的合并

 

下面的SQL，将p2 – p4合并为2个分区p01 – p02

mysql> alter table test2.user

    -> reorganize partition p2,p3,p4 into

    -> (partition p01 values less than (8),

    -> partition p02 values less than (12)

    -> );

3.未分区表和分区表性能测试

（1.）创建一个未分区的表

（2.）创建一个分区表,按日期的年份拆分

mysql> CREATE TABLE test2.tab2 ( c1 int, c2 varchar(30) , c3 date )    

PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),  

PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,  

PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,  

PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,  

PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,  

PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),  

PARTITION p11 VALUES LESS THAN MAXVALUE );   

注意:最后一行，考虑到可能的最大值

通过存储过程插入100万条测试数据

（3.）创建存储过程：

mysql> delimiter $$      //指定存储过程结束符

mysql>CREATE PROCEDURE load_part_tab()  

       begin 

     declare v int default 0;  

     while v < 2000000  

     do  

         insert into test2.tab1  

         values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));  

          set v = v + 1;  

     end while;  

     end 

   $$

注意：RAND()函数在0和1之间产生一个随机数，如果一个整数参数N被指定，它被用作种子值。每个种子产生的随机数序列是不同的。

（4.）执行存储过程load_part_tab向test2.tab1表插入数据

mysql> delimiter ;

mysql> call load_part_tab();

（5.）向test2.tab2表中插入数据

mysql> insert into test2.tab2 select * from test2.tab1;

（6.）测试SQL性能

结果表明分区表比未分区表的执行时间少很多。

（7.）通过explain语句来分析执行情况

explain语句显示了SQL查询要处理的记录数目可以看出分区表比未分区表的明显扫描的记录要少很多。

（8.）创建索引后情况测试

创建索引后分区表比未分区表的统计记录要少很多。（数据量越大差别会明显些）

二、mysql分区的类型

1.RANGE分区

基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。以下是实例。

本文由职坐标整理并发布，希望对同学们学习MySQL有所帮助，更多内容请关注职坐标数据库MySQL数据库频道！