mysql 分库分表分区总结

life1024

已于 2022-03-30 14:06:57 修改

阅读量2.9k

点赞数

分类专栏： mysql 文章标签： mysql

于 2020-06-14 16:06:40 首次发布

本文链接：https://blog.csdn.net/u013378306/article/details/106747340

版权

mysql 专栏收录该内容

26 篇文章 1 订阅

订阅专栏

Mysql目录结构

一个库一个目录

MyISAM引擎

InnoDB引擎

分库分表分区总结

对于分区分表都可以进行横向（按表字段分），纵向分（按数据行分），此文暂时值考虑横向分。

对于分库：分库

分区

说明

分区和分表类似，把原本的一个数据量很大的表，根据规则分成很多块。这些块可以跨磁盘分布。突破单磁盘IO的限制。

好处

分区为了突破IO瓶颈，可以把索引及数据文件放在不同磁盘。
可以根据自定义的分区方式优化查询，比如根据分区列查询，就会定位到某个分区只查某分区的数据。
完全在数据库层面操作，与代码解耦，不需要java代码等进行逻辑处理，后期增加，拆分，合并，删除方便。
不用关心跨分区统计查询问题，数据库层面已经处理好。

缺点

要事先预估数据量，后期可能会随着数据的增加而需要重新映射分区。
只能在一个数据库实例中，内存加载和计算只能在一台机器中。

分表

说明

分表是建立多张子表，每张表的表字段相同，根据映射算法把数据分到不同子表。数据文件只能在一个磁盘上，一个数据库实例中。

好处

可以根据自定义的表字段映射算法，把每次增删改查时的操作映射到的表，这样可以少查询数据。可以数据量很大的表表横向拆分为多个子表。

缺点

需要java代码逻辑处理映射算法。比较复杂。

（2）只能在一个数据库实例中，只能在一个磁盘中，内存加载和计算只能在一台机器中。

（3）要事先预估数据量，后期可能会随着数据的扩容重新扩展映射表。

（4）对于跨子表的统计查询，会比较复杂。

分库

好处

分库可以突破服务器单节点内存，和计算的的限制。具体可以通自定义程序逻辑实现。根据场景自己实现即可。

如：主从备份读写分离，数据库按业务进行分（纵向）。把一张很大的表横向切分并放在不同的库中（映射方法类似于分表分区）。

缺点

分库要考虑跨库多表查询的问题。

要考虑分布式事务的问题。

分区

主要可以利用多个磁盘，提高IO性能。

分区把一个表数据和索引存储到多个不同的地方，跨磁盘，（可以跨文件系统，前提是当前系统可以看到此文件系统），可以指定每个分区文件存储的位置。

分区可以分为垂直分区（对一个大表，拆字段分别放在不同分区），和水平分区（把特定的某些行分开存放）。目前mysql只支持水平分区。

指定分区所在磁盘的文件：Mysql数据库表分区跨磁盘存储表分区放到不同的磁盘表分区存储到指定磁盘路径_Alex许恒的博客-CSDN博客_mysql 不同数据库放不同目录

分区具体命令操作：MYSQL表分区,查看分区_成功不打折扣的博客-CSDN博客_mysql 分区查询

分区可以调整（如把一个分区调整为2个），可以合并（如把两个分区合并为一个），可以追加，可以删除

在做分区前，也要先预估数据量，然后分为几个区。最好是按照时间分区，比如一年一个分区，这样就不用预估多少个分区，每一年开始就追加一个分区即可

Mysql分区类型

根据所使用的不同分区规则可以分成几大分区类型。

RANGE 分区：

基于属于一个给定连续区间的列值，把多行分配给分区。

LIST 分区：

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区：

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。

KEY

分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

复合分区：

基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。

对表做range分区

create table t5 (a varchar(20) not null,b varchar(11) not null,id int not null) partition by range(id)

(

partition s1 values less than(2000000),

partition s2 values less than(4000000),

partition s3 values less than(10000000)

)

可见分为三个区后，在mysql的数据库目录下，多了三个ibd分区的文件，和一个frm的表信息文件。

不使用分区列查询，可以看到用到了三个分区

分区约束

在5.1版本中分区表对唯一约束有明确的规定，每一个唯一约束必须包含在分区表的分区键（也包括主键约束）。
还有很多约束，比如不能外键，以及innordb，MyISAM引擎上的约束等等。

分区后查询性能

注意此处测试时，我所有分区都在一个磁盘，所以不考虑多个磁盘会提升IO性能的问题。

test1是没有分区的表，t5是分为三个区的表（按照id：<2000000，<4000000，<10000000）。两个表都没有索引，数据都一样，总量都为6291456条。

两个表格式如下

条件不包含分区列

可以看到，条件如果没有分区列，则会查询三个分区。速度上两者差不多。

条件包含分区列

在一个分区内的数据查询

可以看到只使用一个分区。速度要比不分区要快的多。

数据跨两个分区

可以看到id<2500000的数据跨了两个分区。同样比不分区要快。

跨所有分区

当条件id<4500000时跨所有三个分区，速度上两者差不多。

分表

分表有多种方式，目前我们只说两种。做分表前，最好要提前预估数据量（不然后面要增加子表需要重建映射关系），并根据数据量确定使用的映射子表的算法。最好能够按年进行分表，这样就能很好的解决建多少张表的问题。

另外可以考虑一致性hash。

Merge引擎实现表合并（不推荐）

主要原理是利用MERGE引擎创建一个主表，把两张子表关联起来（方便总的统计查询）。这样只查询主表即可。但是由于主表插入的函数只有两个选项LAST或者FIRST（即插入到最后一个子表或者第一个子表）。因此需要自己再程序中创建映射子表的算法。

Sql语句

创建两张子表（必须使用MyISAM引擎），并添加数据

mysql> CREATE TABLE IF NOT EXISTS `user1` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `sex` int(1) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE IF NOT EXISTS `user2` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `sex` int(1) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO `user1` (`name`, `sex`) VALUES('张映', 0);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1);
Query OK, 1 row affected (0.00 sec)

创建主表（必须使用MRG_MYISAM引擎）

ysql> CREATE TABLE IF NOT EXISTS `alluser` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `sex` int(1) NOT NULL DEFAULT '0',
-> INDEX(id)
-> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=NO AUTO_INCREMENT=1 ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

上述sql 中TYPE=MERGE表示使用MERGE存储引擎； UNION=(user1,user2) 表示要把两张表组合一起，INSERT_METHOD=NO 表示不允许插入主表。使用first或last值使得插入被相应地做在第一或最后一个表上。如果你没有指定insert_method选项，或你用一个no值指定该选，则表示不允许插入主表。