@[toc]宋哥之前写过一篇文章，给大家介绍了使用MyCat实现MySQL分库分表。不知道有没有朋友研究过。MySQL其实有自己的分区功能。有没有任何外部工具的分区表。今天我们就来看看吧。1、什么是表分区？朋友们都知道MySQL数据库中的数据是以文件的形式存储在磁盘上的。默认情况下，它位于/var/lib/mysql/目录下。我们可以使用像'%datadir%'这样的显示变量；命令查看：我们进入这个目录，可以看到我们定义的所有数据库，一个数据库就是一个文件夹，一个库，里面有对应表的信息，如下：在MySQL中，如果存储的引擎是MyISAM，那么您将在数据目录中看到三种类型的文件：.frm、.myi和.myd。作用如下：*.frm：这是表定义，是描述表结构的文件。*.myd：这是数据信息文件，也就是表格的数据文件。*.myi：这是索引信息文件。如果存储引擎是InnoDB，你会在数据目录下看到两类文件：.frm和.ibd，它们的作用如下：*.frm：表结构文件。*.ibd：表数据和索引的文件。不管是什么存储引擎，只要一张表的数据量太大，*.myd、*.myi、*.ibd文件就会过大，数据查找就会变得很慢。为了解决这个问题，我们可以利用MySQL的分区功能，将这张表对应的文件在物理上分成很多小块，这样我们在查找一条数据的时候，就不需要一次遍历整个文件了某个文件现在，我们只需要知道这个数据在哪个数据块，然后在那个数据块上搜索；另一方面，如果一个表中的数据量太大，一个磁盘可能放不下。这时候，通过Withtablepartitioning，我们可以将数据分配到不同的磁盘上。MySQL从5.1开始增加了对分区的支持，将一个表或索引分区为多个更小、更易于管理的部分的过程。对于开发者来说，分区表的用法和非分区表基本一样，只是在物理存储上，表本来只有一个数据文件，现在有多个数据文件，每个分区是一个独立的对象。,可以单独处理或作为更大对象的一部分处理。需要注意的是，分区功能并不是在存储引擎层完成的。InnoDB、MyISAM、NDB等常见的存储引擎都支持分区。但并不是所有的存储引擎都支持它。例如CSV、FEDORATED、MERGE等不支持分区。因此，在使用该分区功能之前，您应该先了解所选存储引擎对分区的支持情况。2.这两种分区方式与MyCat不同，可以垂直拆分也可以水平拆分。MySQL数据库支持的分区类型是水平分区，不支持垂直分区。2.1水平切分首先，我们先来一个简单的示意图。让我们感受一下水平分片是什么：假设我的数据库中有三个表：table-1、table-2和table-3。M大刀，瞄准黑线，砍一剑或砍N剑！切完之后，把切下来的部分放到另一个数据库实例中，就变成了这样：这样，原来放在一个DB中的表，现在放到了两个DB中。经过观察，我们发现：两个DB中的表表的个数是完整的，即原来DB中有几张表，现在还有几张表。每个表的数据不完整，数据被拆分到不同的DB中。这是数据库的横向切分，也可以理解为按数据行切分，即表数据按照表中某个字段的一定规则分布到多个库中，每个表包含数据的一部分。即水平拆分不改变表结构。2.2垂直分割首先，我们先来一个简单的示意图。感受下垂直分割：所谓垂直分割，就是拿着我的40米大砍刀，对准黑线进行切割。切开后，将不同的表放到不同的数据库实例中，看起来是这样的：这时我们发现有以下特点：每个数据库实例中的表数量不完整。每个数据库实例中的表中的数据都是完整的。这是垂直拆分。一般来说，垂直切分可以按照业务来划分，不同业务的表放在不同的数据库实例中。MySQL数据库支持的分区类型是水平分区。另外，MySQL数据库的分区是本地分区索引，即一个分区既存储数据又存储索引。目前，MySQL数据库不支持全局分区（数据存储在每个分区中，但所有数据索引都放在一个对象中间）。3、为什么要分表，让单表存储更多的数据。分区表的数据更容易维护。可以通过清空整个分区来批量删除大量数据，也可以添加新的分区来支持新插入的数据。此外，还可以对独立的分区进行优化、检查、修复等操作。部分查询可以根据查询条件确定只落在少数分区上，查询速度会很快。分区表的数据也可以分布在不同的物理设备上，以高效利用多个硬件设备。分区表可以用来避免一些特殊的瓶颈，比如InnoDB中对单个索引的独占访问，ext3文件系统中的inode锁争用。可以备份和恢复单个分区。分区的限制和缺点：一张表最多只能有1024个分区。如果分区字段中有主键列或唯一索引列，则必须包括所有主键列和唯一索引列。分区表不能使用外键约束。NULL值禁用分区过滤。所有分区必须使用相同的存储引擎。4.分区实践说了这么多，我们来看一个例子。首先我们看一下目前的MySQL是否支持分区。在MySQL5.6.1之前，可以使用命令showvariableslike'%have_partitioning%'查看MySQL是否支持分区。如果have_partitioning为YES，则支持分区。从MySQL5.6.1开始，have_partitioning参数被去掉，取而代之的是SHOWPLUGINS。如果有分区行，STATUS列的值为ACTIVE，说明支持分区，如下图：确认我们的MySQL支持分区后，我们就可以开始分区了！接下来，让我们看看几种不同的分区策略。4.1RANGE分区RANGE分区比较简单，是按照某个字段的值进行分区。但是这个字段有个要求，必须是主键或者是联合主键中的字段。比如按照user表的id进行分区：当id小于100时，将数据插入到p0分区；当id大于等于100且小于200时，插入p1分区；如果id大于等于200，则插入p2分区。上述规则涵盖所有范围的id。如果没有第三条规则，插入id为300的记录会报错。建表SQL如下：createtableuser(idintprimarykey,usernamevarchar(255))engine=innodbpartitionbyrange(id)(partitionp0valueslessthan(100),partitionp1values小于（200），分区p2值小于maxvalue）；表创建成功后，我们进入/var/lib/mysql/test08文件夹可以看到刚才创建的表文件：可以看到，此时的数据文件分为几个。在information_schema.partitions表中，我们可以查看分区的详细信息：我们也可以写一个SQL查询：select*frominformation_schema.partitionswheretable_schema='test08'??andtable_name='user'\G每行显示一个分区信息，包括分区的方式、分区的范围、分区的字段、分区中当前有多少条记录等。RANGE分区一个典型的使用场景就是按照日期对表进行分区。例如，将同一年注册的用户放在一个分区中，如下：innodbpartitionbyrange(year(createDate))(partitionp2022valueslessthan(2023),partitionp2023valueslessthan(2024),partitionp2024valueslessthan(2025));请注意，createDate是复合主键的成员。如果createDate不是主键而是普通字段，那么创建时会抛出如下错误：现在，如果我们要查询2022年注册的用户，系统只会搜索p2022分区，可以通过explain执行计划思路：如果想删除2022注册的用户，只需要删除分区：altertableuserdroppartitionp2022；从上图可以看出，删除后，数据就没有了。4.2LIST分区LIST分区类似于RANGE分区，不同的是LIST分区是根据与离散值集中某个值匹配的列值来选择的，而不是连续的。我们看一个例子来理解：假设我有一个用户表，用户有性别，现在我想把用户按照性别分开存储。雄性存放一格，雌性存放一格。SQL如下：createtableuser(idint,usernamevarchar(255),passwordvarchar(255),genderint,primarykey(id,gender))engine=innodbpartitionbylist(gender)(partitionmanvaluesin(1),partitionwomanvaluesin(0));这张表以后会有两个partition，分别存放male和female。gender的值为1或0，如果gender取其他值，执行会出错。最终执行结果如下：经过这个分区，以后查询男性或者查询女性的效率比较高，删除某个性别的用户时删除效率也比较高。4.3HASH分区HASH分区的目的是将数据均匀分布到预先定义的分区中，保证每个分区的数据量大致相同。在RANGE和LIST分区中，需要显式指定给定的列值或列值集应该存储在哪个分区中；在HASH分区中，MySQL自动完成这些任务，而用户所要做的只是根据要执行的哈希分区的列指定一个表达式，以及分区的个数。使用HASH分区拆分表，在CREATETABLE语句中加入PARTITIONBYHASH(expr)，其中expr是一个返回整数的字段或表达式；另外，通过PARTITIONS属性指定分区数，如果不指定，则分区数默认为1。另外，HASH分区不能删除分区，所以不能使用DROPPARTITION操作来删除分区。createtableuser(idint,usernamevarchar(255),passwordvarchar(255),genderint,primarykey(id,gender))engine=innodbpartitionbyhash(id)partitions4;4.4KEY分区KEY分区和HASH分区类似，但KEY分区支持除text和BLOB以外的所有数据类型的分区，而HASH分区只支持数字分区。KEY分区不允许使用用户自定义表达式进行分区，KEY分区使用系统提供的HASH函数进行分区。当表中有主键或唯一索引时，如果创建KEY分区时没有指定字段，系统会默认主键列作为分区字段，如果没有主键列，则非空的唯一索引列将被选为分区字段。例如：createtableuser(idint,usernamevarchar(255),passwordvarchar(255),genderint,primarykey(id,gender))engine=innodbpartitionbykey(id)partitions4;4.5COLUMNSpartitionCOLUMNPartitioning是5.5引入的分区函数。只有两个分区：RANGECOLUMN和LISTCOLUMN；它支持整数、日期和字符串；这种分区方法与RANGE和LIST非常相似。COLUMNSVsRANGEVsLIST分区：日期字段的分区不再需要使用函数进行转换。COLUMN分区支持多个字段作为分区键，但不支持表达式作为分区键。COLUMNS支持的类型：tinyint、smallint、mediumint、int、bigint；不支持小数和浮点数。时间类型支持：日期、日期时间。字符类型支持：char、varchar、binary、varbinary；不支持文本和blob。例如，查看：createtableuser(idint,usernamevarchar(255),passwordvarchar(255),genderint,createDatedate,primarykey(id,createDate))engine=innodbPARTITIONBYRANGECOLUMNS(createDate)(分区p0值小于（'1990-01-01'），分区p1值小于（'2000-01-01'），分区p2值小于（'2010-01-01'），分区p3值小于('2020-01-01'),分区p4值小于最大值);这是RANGECOLUMNS，分区值是连续的。再看LISTCOLUMNS分区，类似枚举：createtableuser(idint,usernamevarchar(255),passwordvarchar(255),genderint,createDatedate,primarykey(id,createDate))engine=innodbPARTITIONBYLISTCOLUMNS(createDate)(PARTITIONp0VALUESIN('1990-01-01'),PARTITIONp1VALUESIN('2000-01-01'),PARTITIONp2VALUESIN('2010-01-01'),分区p3值在('2020-01-01'));5、添加分区的常用分区命令：altertableuseraddpartition(partitionp3valueslessthan(4000));--rangepartitionaltertableuseraddpartition(partitionp3valuesin(40));--listspartitiondeletetablepartition（数据会被删除）：altertableuserdroppartitionp30;删除表的所有分区（不丢失数据）：altertableuserremovepartitioning；重新定义范围分区表（无数据丢失）：altertableuserpartitionbyrange(salary)(partitionp1valueslessthan(2000),partitionp2valueslessthan(4000));重新定义散列分区表（无数据丢失）：altertableuserpartitionbyhash(salary)partitions7;合并分区：在不丢失数据的情况下将2个分区合并为一个：altertableuserreorganizepartitionp1,p2into（分区p1值小于（1000））；6.总结不知道大家还记得2019年宋大哥写的MyCat教程吗（公众号江南一电鱼后台回复2019有文章索引），这些分区策略是不是和MyCat里面的策略很像？有兴趣的小伙伴赶紧试试吧~参考资料：https://www.cnblogs.com/dw330...
