浅谈mysql分区、分表、分库

黄啊码

发布于 2021-09-26 10:16:41

1.3K0

发布于 2021-09-26 10:16:41

文章被收录于专栏：黄啊码【CSDN同名】

分区

分区就是把一个数据表的文件和索引分散存储在不同的物理文件中。

mysql支持的分区类型包括Range、List、Hash、Key，其中Range比较常用：

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

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

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

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

Composite（复合模式）：以上模式的组合使用　

分区的限制(截止5.1.44版)

• 只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列

• 最大分区数目不能超过1024

• 如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内

• 不支持外键

• 不支持全文索引（fulltext）

按日期进行分区很非常适合，因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多

案例：

建立一个user 表以id进行分区 id 小于10的在user_1分区id小于20的在user_2分区

create table user_info (
    id int not null auto_increment,
    username varchar(10),
    primary key(id)
)engine = innodb charset=utf8
partition by range (id)(
    partition user_1 values less than (10),
    partition user_2 values less than (20)
);

这样的表数据只能写入到19行，因为区块不够了

建立后添加分区：

maxvalue 表示最大值这样大于等于20的id 都出存储在user_3分区

alter table user_info add partition(
    partition user_3 values less than maxvalue
);

继续执行添加的数据，结果如下，数据正常写入

如果表使用的存储引擎是MyISAM类型，就是：

user#P#user_1.MYD,user#P#user_1.MYI和user#P#user_2.MYD,user#P#user_2.MYI

由此可见，mysql通过分区把数据保存到不同的文件里，同时索引也是分区的。相对于未分区的表来说，分区后单独的数据库文件索引文件的大小都明显降低，效率则明显的提示了。

现在我们截断整个表，然后插入一条数据然后分析查询语句验证一下：

insert into user_info values(null,'测试');

explain partitions select * from user_info where id =1;

可以看见仅仅在user_1分区执行了这条查询。

具体分区的效率是多少还需要看数据量。在分区时可以通过 DATA DIRECTORY 和　　INDEX DIRECTORY 选项吧不同的分区放到不同的磁盘上进一步提高系统的I/O吞吐量。

【DATA DIRECTORY和INDEX DIRECTORY知识点】 指定INDEX DIRECTORY 和 DATA DIRECTORY 操作，在当磁盘分区不足的时候，可以将数据文件放在其它的分区上。注： INNODB 表要是独立表空间（innodb_file_per_table=1）。注：目前只有5.6才支持单表指定目录，且目录是mysql:mysql。注：在Windows中将忽略DATA DIRECTORY和INDEX DIRECTORY选项。注：对于Myisam表要启用符号链接：（--symbolic-links,--skip-symbolic-links 分别对应启用和禁用符号链接支持。启用符号链接表示可以使用create table的index directory或data directory选项将MyISAM索引文件或数据文件链接到另一个目录，如果删除或重命名表，符号链接指向的文件也将被删除或重命名。参见create table语法。在windows中，启用符号链接，可以通过创建包含目标目录路径的directory.sym文件来建立数据库目录的符号链接。将忽略data directory和index directory，因为windows并不支持符号链接，在非功能realpath()调用的系统中，该功能也将被忽略。）注： NO_DIR_IN_CREATE 创建表时，将忽略所有的data directory和index directory，并且对从服务器有用。注：如果在主服务器上的CREATE TABLE 语句中使用了DATA DIRECTORY或INDEX DIRECTORY 子句，子句也可以在从服务器上使用。如果在从服务器主机文件系统中不存在一致的目录或虽然存在但不能被从服务器访问，则会带来问题。MySQL 5.1 支持一个称为NO_DIR_IN_CREATE的sql_mode选项。如果从服务器运行时将SQL 模式设置为包括该选项，复制CREATE TABLE 语句时将忽略这些子句。结果是在表的数据库目录中创建了MyISAM数据和索引文件。注： CREATE TABLE...LIKE 不会复制对原表或外键定义指定的DATA DIRECTORY或INDEX DIRECTORY 表选项。注：（1）当修改表引擎时： INNODB 表转为 MYISAM时，只会指定DATA DIRECTORY = '/var/lib/mysqldata'，没有指定INDEX DIRECTORY= '/var/lib/mysqldata'，则索引文件就在默认目录下。 MYISAM 表转为 INNODB时，指定DATA DIRECTORY = '/var/lib/mysqldata' （2）在不同的库下，指定MYISAM表的路径到同一位置时，不能有重复的表。因为 MYISAM表直接在/var/lib/mysqldata 目录下，而INNODB的表在/var/lib/mysqldata 目录下有库目录。 例子： create table user_info ( id int not null auto_increment, username varchar(10), primary key(id) )ENGINE=MYISAM DATA DIRECTORY = '/ibddir' INDEX DIRECTORY= '/ibddir';

分区类型的选择，通常使用Range类型，不过有些情况，比如主从结构中，主服务器很少使用‘select’查询，在主服务器上使用 Range类型分区通常没有太大的意义，此时使用Hash类型分区更好例如：

partition by hash(id) partitions 10;当插入数据时，根据id吧数据平均散到各个分区上，由于文件小，效率高，更新操作变得更快。

create table user_info (
    id int not null auto_increment,
    username varchar(10),
    primary key(id)
)engine = innodb charset=utf8
partition by hash(id) partitions 10;

执行结果如下，id不会自动按顺序排列

分布的区域也会有所不同

在分区时可以通过 DATA DIRECTORY 和　　INDEX DIRECTORY 选项吧不同的分区放到不同的磁盘上进一步提高系统的I/O吞吐量。

使用Hash分区，当插入数据时，根据id吧数据平均散到各个分区上，由于文件小，效率高，更新操作变得更快。

分区的限制：

1.主键或者唯一索引必须包含分区字段，如primary key (id,username),不过innoDB的大组建性能不好。

2.很多时候，使用分区就不要在使用主键了，否则可能影响性能。

      因为建主键的同时会建一个唯一性的全局索引，在drop分区表时如果不指定update global indexes则会使索引失效，导致数据无法入库。

       如果非要建主键，要2种方法：

       1.应用上drop 分区表时显示指定update global indexes，

       2.将主键上的索引建成本地索引

       上述2种方法虽然可以实现，但效果都不好。因为当数据量超大时维护索引也是很大的开销。将主键建成本地索引的方法也比较受限。

3.只能通过int类型的字段或者返回int类型的表达式来分区，通常使用year或者to_days等函数（mysql 5.6 对限制开始放开了）。

4.每个表最多1024个分区，而且多分区会大量消耗内存。

5.分区的表不支持外键，相关的逻辑约束需要使用程序来实现。

6.分区后，可能会造成索引失效，需要验证分区可行性。

分表 ---->分表实现这里用单独一篇文章讲解https://cloud.tencent.com/developer/article/1882462

水平分表【按业务】

概念：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
结果：
每个表的结构都一样；
每个表的数据都不一样，没有交集；
所有表的并集是全量数据；
场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。
分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。

垂直分表【按字段的活跃度】

概念：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。
结果：
每个表的结构都不一样；
每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
所有表的并集是全量数据；
场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。
分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

在一个流量监控系统中，由于网络流量巨大，统计数据很庞大，需要按天分表。先要得到任意日，周，月的数据。

1.需要任意一天的数据。直接查询当天的数据表即可。

2.需要几天的数据。分爱查询这几天的数据，然后进行汇总。

3.需要查询一周的数据。对一周的数据定期汇总到一个week表，从这个表里面查询。这个汇总过程可以由一个外部程序完成，也可以由定期的脚本完成。

4.查询一个月的数据。汇总本月所有的数据到month表，在此表查询。

5.查询5个月内的详细数据。不支持。仅支持最多3个月的详细数据。数据没3个月已归档一次。在大数据的处理中，必须做出一些牺牲。对于超出3个月的数据，仅提供统计数据，详细数据需要查看归档。90天或者180天，给数据保存设个界限，也是大部分这类系统的常规做法，超出90天的数据就不再提供数据详单了。比如，移动的通话记录最多保存半年，即180天，超过这个范围的数据不在提供查询。如果你实在需要，可能就要联系移动的工程师了。

分库

水平分库

概念：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
结果：
每个库的结构都一样；
每个库的数据都不一样，没有交集；
所有库的并集是全量数据；
场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。
分析：库多了，io和cpu的压力自然可以成倍缓解。

垂直分库

概念：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
结果：
每个库的结构都不一样；
每个库的数据也不一样，没有交集；
所有库的并集是全量数据；
场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。
分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。