MySQL 数据库分表分区

博主QQ：819594300

博客地址：http://zpf666.blog.51cto.com/

有什么疑问的朋友可以联系博主，博主会帮你们解答，谢谢支持！一、分表

为什么要分表？

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

什么是分表？

分表是将一个达标按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三个文件，.MYD数据文件、.MYI索引文件、.frm表结构文件。这些表可以分布在同一块磁盘上，也可以在不同主机的不同的磁盘上。

App读写的时候根据事先定义好的规则得到对应的表名，然后去操作它。

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

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

垂直切分是指数据表列的拆分，把一张列比较多的表拆分为多张表。我们常常把常用的几个列单独放在一个表，不常用的单独放在另外一个表。

水平拆分是指数据表行的拆分，把一张表的数据拆分成多张表来存放。通常情况下，我们使用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存储引擎来实现分表有局限性，只针对myisam存储引擎表。

Merge分表，分为一个主表和若干个子表，主表就是一个壳子，在逻辑上是包含子表的，但是主表不存放任何的数据，真正的数据存放在子表中。

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

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

1）创建一个完整表存储着所有的成员信息

2）加入实验数据（我们加入32行实验数据）

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

4）创建主表tb_member

注：INSERT_METHOD,此参数

INSERT_METHOD= NO 表示该表不能做任何写入操作只作为查询使用，即只读状态；

INSERT_METHOD= LAST表示插入到最后的一张表里面；

INSERT_METHOD= first表示插入到第一张表里面。

Insert_method是指以后我们插入新的数据，则要遵守该配置项后面的参数执行。这也正是merge分表的缺陷，对于新插入的数据有局限性，而innodb分表则没有这个局限性。

5）接下来，我们把原表中的数据分到两个子表中去

6）查看两个子表的数据

7）查看主表的数据

总结：主表只是一个外壳，存取数据发生在一个一个的子表里面，每个子表都有自已独立的相关表文件，而主表只是一个壳，并没有完整的相关表文件。我们看到的主表的数据，都是给用户的一个假象，这也说明，分表对于客户来说是透明的。主表和子表的关系就很类似与我们学过的lvs的调度器和所有的节点服务器。

8）删除原表，修改主表表名（因为此时原表已经没有用处了，把主表改成原表表名，这样做的好处是不用修改APP了，这是merge的一大优势）

说明：主表也包含有.frm文件，但是他有个特殊的文件是.mrg文件，这个文件不存放任何表的真实数据，而是存放的是所有额子表表名和插入方式。

本实验总结：merge分表的顺序是：

①先创建所需的所有子表→②再创建主表→③把原表的数据导入到新建的不同子表中→④删除原表，把主表改名字为原表表名（这样做的好处不用修改APP代码，这是merge的优点）

其实innodb表也可以分表，步骤如下：

①先创建所需的所有子表→②把原表的数据导入到新建的不同子表中→③修改APP代码，把客户的请求对应到各个子表。

两种不同点是，innodb不需要创建主表，但是需要修改APP代码，比较麻烦，而merge不需要修改app，但是merge需要建主表，而innodb不需要。

二、分区

什么是分区？

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，分区后，表还是一张表，但数据散列到多个位置了。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及以采用后，则采用如下方式进行查看

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

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

1)创建range分区表

2)插入些数据（说明一下：处于分解点上额数据会被分配到下一个分区中，比如数据3不会放p0表，而是放p1分区）

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

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

5)从某个分区中查询数据

6)新增分区

mysql>alter table 库名.表名 add partition (partition 新增的分区名 values less than (n));

注意：n为条件是为具体的数字或者是maxvalues

7）删除分区

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

8)分区的合并

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

1)创建一个未分区的表

2)创建分区表,按日期的年份拆分

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

创建存储过程：

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

执行存储过程load_part_tab向bdqn.tab1表插入数据:

退出去mysql的交互式模式，然后在进入交互式模式，执行下面的命令

4）向test2.tab2表中插入数据

5)测试SQL性能

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

6）通过explain语句来分析执行情况

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

7)创建索引后情况测试

总结：创建索引后分区表比未分区表相差不大（但是数据量越大差别会明显些）

Mysql分区的类型

1、range分区（范围分区）

作用：把一个连续的列值中的多行分配给分区，列区间连续并且不重叠。

例子：

总结：p0到p3分区都是按顺序进行定义，从最低到最高，不允许从最高到最低，处于分界点的值自动放入下一个分区，比如store_id是16的自动放入p3分区，但是如果插入store_id是21的数据，就会报错，因为没有包含21的分区，为了避免这种错误，我们一般都把range分区的最后一个分区设置为maxvalue分区，把前面所有分区都不包括的该值分到maxvalue分区，避免报错。

如果创表的时候没有创建maxvalue分区，则用如下命令添加即可：

2、list分区（列表分区）

作用：基于列值匹配一个离散值集合中某个值来进行选择

例子：

总结：list分区依据地区把数据很容易的按地区划分开，比如公司打算把西部的店面全部出售，则就只需要把pwest删除即可，非常方便。注意的地方是如果视图插入的store_id列值不属于任何一个分区，mysql会报错，插入失败。list分区没有range分区类型的maxvalue(最大值)分区，要匹配的列值必须是创建表时几个分区已经有的值。

使用下面的语句删除pwest分区，它与具有同样作用的DELETE （删除）查询

“deletefrom employees2 where store_id in (7,8,15,16)；”比起来，要有效得多。

用另一种删除方法删除pnorch分区

3、hash分区

作用：对表的一个或多个列的hash key进行计算，最后通过这个hash 码不同数值对应的数据区域进行分区。

例子：

总结：hash分区不需要指定分区的集合，mysql会自动完成分区工作，用户只需要定一个列值或表达式，以及分区的数量，默认分区的数量是1（即不划分分区）。++

上图中的那个例子说明：b列的数值取年份÷分区数量，然后去余数。比如分区的数量是4，则余数则只能有0、1、2、3，自动把余数为0的放p0分区，余数为1的放p1分区等等。其中mysql会自动创建p0、p1、p2、p3分区，名字就叫p0、p1、p2、p3。

查一个表中某个指定分区中的所有数据：

从information_schema库中的partitions表里面查询bdqn.employess3表的每个分区的具体情况：

4、key分区

作用：与hash分区很相似，不同的是hash分区是用户自定义函数进行分区。而key使用mysql数据库提供的函数进行分区。NDB cluster使用md5函数来分区，对于其他存储引擎mysql使用内部的hash函数。

例子：

总结：range、list、hash、key四种分区中，分区的条件必须是整数，如果不是整数需要通过函数将其转换成整数。

5、columns分区

说明：mysql5.5版本开始支持columns分区，可视为range和list分区的进化，columns分区可以直接使用非整数数据进行分区。

Columns分区支持以下数据类型：

• 所有×××，如INT SMALLINTTINYINT BIGINT。FLOAT和DECIMAL则不支持。

• 日期类型，如DATE和DATETIME。其余日期类型不支持。

• 字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不支持。

• COLUMNS可以使用多个列进行分区。

分区时，将不同分区放到不同存储位置：

①建表前，提前创建好存储目录，并授权给mysql:

②创建表

注：使用mysql默认的存储引擎inodb时候，只需要指定data directory 就可以，因为inodb的数据和索引在一个文件中。但是创建表格时指定engine=myisam时，修改分区的存储位置，需要同时指定datadirectory 和 index directory。

总结：把一个表的所有数据通过分区划分到不同目录（目录在不同磁盘上），可以提高I/O性能，提高磁读写能力，让几个磁盘都能同时工作，提高mysql的性能。