MySQL分区分表 原理详解

MySQL分区分表
为什么要分区和分表

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

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

1、分表
什么是分表
分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三个文件，MYD数据文件，MYI索引文件，frm表结构文件。这些表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的表名，然后去操作他。
用单个数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，根据一定的算法（如用hash算法的方式，也可以用求余（取模）的方式），让用户访问不同的表，这样数据分散到多个数据表中，减少了单个表的访问压力，提升了数据库的访问性能。分表的目的就在于此，减少数据库的负担，缩短查询时间。

MySQL分表分为垂直切分和水平切分
    垂直切分是指数据表列的切分，把一张列比较多的表拆分为多张表。
    通常我们按照以下原则进行垂直切分：
        把不常用的字段单独放到一张表。
        把text，blob（binary large object，二进制大对象）等大字段拆分出来放在附表中
        经常组合查询的列放在一张表中：
        垂直切分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用join关键起来即可。
    水平拆分是指数据表行的拆分，把一张表的数据拆成多张表来存放。
    水平拆分的原则
        通常情况下，我们使用hash，取模的方式来进行表的拆分。
        比如一张拥有400W的用户表users，为了提高其查询效率，我们把其分为四张表 user1，user2，user3，user4
        通过ID取模的方法把数据分散到四张表内 id%4=[0,1,2,3]
        然后查询，更新，删除也是通过取模的方式来查询
        部分业务逻辑也可以通过地区，年份等字段来进行归档拆分；
        进行拆分后的表，这是我们就要约束用户的查询行为。比如我们是按年来进行拆分的，这个时候在页面设计上就约束用户必须要先选择年，然后才能进行查询。

分表的几种方式
    1、mysql集群
        他并不是分表，但是起到了和分表相同的作用。集群可分担数据库的操作次数，将任务分担到多台数据库上。集群可以读写分离，减少读写的压力。从而提升数据库的性能。
    2、预先估计会出现大数据量并且访问频繁的表，将其分为若干个表
        根据一定的算法（如hash的方式，也可以使用求余（取模）的方式）让用户访问不同的表。
    3、利用merge存储去引擎来实现分表
        merge分表，分为主表和子表，主表类似于一个壳子，逻辑上封装了子表，实际上都是存储在子表中的。
        我们可以通过主表插入和查询数据，如果清除分表规律也可以直接操作子表。

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

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

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

MySQL的分区类型
1、RANGE分区
基于属于一个给定连续区间的列值，把多行分配给给分区。这些区间要连续且不能相互重叠，使用values less than操作符进行定义。
2、LIST分区
类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
LIST分区通过使用“PARTITION BY LIST"（expr）来实现，其中expr 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过：VALUES IN （value_list）”的方式来定义某个分区，其中“value——list“是一个通过逗号分隔的整数列表。
3、Hash分区
这种模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同的数值对应的数据区域进行分区。
hash分区的目的是将数据均匀分步到预先定义的各个分区中，保证各分区的数据的数据量大致一致。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪一个分区中；而在RANGE和LIST分区中MySQL自动完成这个动作，用户所要定一个列值或者表达式，以及指定被分区的表将要被分割成分区的数量。
4、Key分区
Key分区和Hash相似，不同在于hash分区是用户自定义函数进行分区，Key分区使用mysql数据库提供的分区进行分区，NDB clust使用MD5函数进行分区，对于其他存储引擎mysql使用内部的hash函数。

以上四种分区方式中，分区的条件必须是×××，如果不是×××，需要通过函数将其转换为整行。

5、columns 分区
    mysql5.5开始支持COLUMNS分区，可视为RANGE和LIST分区的进化，COLUMNS分区可以直接使用×××数据进行分区。COLUMNS分区支持一下数据类型。
        所有×××，如INT SMALLINT TINYINT BIGINT。FLOAT和DECIMAL则不支持。
        日期类型，如DATE和DATETIME。其余日期类型不支持。
        字符串类型，如CHAR,VARCHAR,BINARY,和VARBINSRY。BOLB和TEXT类型不支持。
        COLUMNS可以使用多个列进行分区。

MySQL分表和分区有什么区别
1、实现方式上
1、mysql的分表是真正意义上的分表，一张表分成很多表以后，每一个小表都是完整的一张表，都对应三个文件，一个是MYD数据文件，MYI索引文件，frm表结构文件
2、分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据的区块变多了。
2、在数据上
1、分表后，数据都是存放在分表里，总表只是一个外壳，存放数据发生在一个一个的分表里。
2、分区呢，不存在分表的概念，分区只不过把存放数据的文件分成许多的小块，分区后的表呢，还是一张表，数据处理还是有自己完成。
3、提高性能上
1、分表后，单表的并发能力提高了，磁盘IO性能提高了。
为什么并发提高了，因为查询一次所花的时间变短了，如果出现高并发的花，总表可以根据不同的查询，将并发压力分到不同的小表里面。
2、mysql提出了分区的概念，主要是想突破磁盘的IO瓶颈，提高磁盘的读写能力，提高磁盘的读写能力来增加mysql的性能。
在这一点上，分区和分表的侧重点不同，分表重点是存取数据时，如何提高mysql并发能力上，而分区是突破磁盘的读写能力，从而达到提高mysql性能的目的。
4、实现难易程度上
1、分表的方法有很多，用merge来分表，是最简单的一种方式。这种方式跟分区的难以程度差不多，并且对程序代码来说可以做到透明。如果是用其他分表方式就比分区麻烦了。
2、分区实现是比较简单的，建立分区表，跟建平常的表没有什么区别，并且对开代码端来说是透明的。
mysql分表和分区有什么联系
1、都是提高mysql的性能，在高并发的状态下都有一个良好的表现
2、分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表。我们可以采取分区的方式，访问量不大，但是表数据较多的表，我们可以采取分区的方式等。
3、分表技术是比较麻烦的，需要手动去创建子表，app服务端读写的时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。
4、表分区相当于分表，操作方便，不需要创建子表。

转载于:https://blog.51cto.com/13214087/2149994