mysql中分表，分库，分区，和分片

2023-11-05 06:17| 来源: 网络整理| 查看: 265

背景

mysql的集群方案，可降低并发时的读写压力，将读写操作按照算法分布到集群中不同机器上。不管是pxc各个节点还是replication的slave和master节点，他们每个节点的数据都是相同复制的。并不是分片存储的。当数据库节点数据大于2000万条后性能就会下降，那么问题来了，当有海量数据，几千万甚至上亿时该怎么办？这时就需要使用分表、分库、分区、分片操作。

分表

分表方式：

水平分表：将一张表中的数据分成多个表且表结构不变。垂直分表：将一张表按照字段分成不同的表且表结构发送改变。

使用场景：

水平分表：一张表有海量数据，且通过索引优化后速度依然慢。这时就需要对其水平分表。举个例子，某表有2000万条数据，分成20个表，每个表100万条数据，你说在100万表数据中查询快还在在2000万条数据中查询快？水平分表可以解决数据量过大带来的查询效率降低的问题，即能提高读的速度。垂直分表：当表中有大字段如text类型的、或者是表中字段非常多就需要垂直分表。这里涉及到一个mysql的 $\color{blue}{数据页}$ 概念，mysql采用数据页的形式组织数据。你可以把一张数据表理解为一本书，索引为目录，表中所有数据为整本书的内容，而书中内容是以每页的形式展现给你的， $\color{blue}{恰巧你查询后返回的mysql数据也是以每页每页的形式给你的}$ ，也就是所说的数据页。由于每页的字书是固定不变的,也就是说如果有大字段的话，它会占用你每页中的更多空间，接着每页查询的记录数就会变少，查询的页数就会变多。你看书时一个内容明明一页可以看完，现在要翻俩页，简单点说就是读的效率降低了，也就是说 $\color{blue}{垂直分表能够提高读的速度。}$ 注意：如果一张表中有一个大字段，那么虽然查询时没有查询该字段，但是在根据id或者其他索引进行查询的时候也会把大字段一起查出来。就是这个大字段也占用数据页的空间，所以查询出来的记录数就变少了。总结：mysql底层是通过数据页存储的，如果一条记录占用空间过大,就可能造成跨页查询的问题，通过垂直分表可有效解决这个问题。

注意：在水平分表后，我们通常是对主键id取模，然后定位具体表。如果是vachar类型怎么取模？可以对varchar类型md5然后取模，因为md5是16进制的。

分库

使用场景：

几个业务完全不相关的表可以进行分库，这样可以降低单个数据库节点的压力。比如，有1000人在操作A模块，有1000人在操作B模块，这时mysql就相当2000个并发压力，而分库后，每个数据库节点承受1000并发压力。如果分库后，俩库还有业务联系，这时会涉及到分布式事务问题，所以要选择合适的分库策略。分库是为了降低节点数据库压力，提高并发而提出的策略。既然是为了降低数据库压力所以通常数据库是放到不同的节点上。每个数据库支持的连接数是有限的，每一次的数据库请求都会产生一条数据库连接，当一个库连接数超过max_connection时就会报too many connections的问题，也就是无法支持更多访问的时候，我们会把原来的单个数据库分成多个，帮助分担压力。说白了就是分散数据库压力。分库还有一个很常用的场景就是冷热分离，比如订单表，用户经常查的是一个月内的订单，我们称这部分数据为热数据，而一个月之内的订单很少查但不代表不查，我们称这部分数据为冷数据。一个几千万表数据中查询数据，可想而至速度很慢，此时如果把冷数据挪到另一个库中，而生产库只保留热数据，这样查询起来速度是不是很快了呢？冷热分离即保证了查询速度又保证了数据的完整性，所以是很值得的。分区

分区是将一张表的数据分成多个区块，存放到不同的位置，可以在同一磁盘，数据存放的位置方式改变。也可以在不同磁盘，分区不存在分表，分区后的表还是一张表。

分片

分片带来的问题

分布式事务一致性跨节点Join的问题分区和分表的区别分表后磁盘文件

比如对alluser表进行分表，分俩张表user1和user2,在磁盘中文件如下

alluser.MRG alluser.frm user1.MYD user1.MYI user1.frm user2.MYD user2.MYI user2.frm

frm文件是表结构文件，MYI是索引文件，MYD是数据文件。

可以看出user1和user2都有这3个文件, alluser表只要一个frm文件，alluser只是一个逻辑表。MRG文件是存了一些分表的对应关系。

分区后磁盘文件

比如对aa表进行分区，为p1和p3分区，磁盘文件如下

aa#P#p1.MYD aa#P#p1.MYI aa#P#p3.MYD aa#P#p3.MYI aa.frm aa.par

可以看出只有一个frm，而每个分区都有单独的MYD和MYI文件。par文件记录的是分区的对应关系。

分表和分区后，一个大的数据文件分成多个小的数据文件，这样查询性能肯定能大幅提高。也正是由于分成了多个文件，磁盘的io性能也可以大幅提高。分区是mysql天然支持的，而分表需要业务代码的支持，我理解水平分表（不包括垂直分表）和分区达到的效果是一致的，如果在水平分表和分区之间选择，最好选择分区吧

【本文地址】

公司简介

联系我们