MySql分表、分库、分片和分区知识

本文深入解析数据库扩展策略,包括分片、分库、分表、分区的概念与应用场景,以及它们在提升系统性能、解决数据库扩展性问题中的作用。探讨了ScaleOut与ScaleUp、垂直切分与水平切分的差异,以及分表与分区、分表与分库的具体实现与优劣对比。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一、前言

    数据库的数据量达到一定程度之后,为避免带来系统性能上的瓶颈。需要进行数据的处理,采用的手段是分区、分片、分库、分表。

二、分片(类似分库)

     分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题。Shard这个词的意思是“碎片”。如果将一个数据库当作一块大玻璃,将这块玻璃打碎,那么每一小块都称为数据库的碎片(DatabaseShard)。将整个数据库打碎的过程就叫做分片,可以翻译为分片。

     形式上,分片可以简单定义为将大数据库分布到多个物理节点上的一个分区方案。每一个分区包含数据库的某一部分,称为一个片,分区方式可以是任意的,并不局限于传统的水平分区和垂直分区。一个分片可以包含多个表的内容甚至可以包含多个数据库实例中的内容。每个分片被放置在一个数据库服务器上。一个数据库服务器可以处理一个或多个分片的数据。系统中需要有服务器进行查询路由转发,负责将查询转发到包含该查询所访问数据的分片或分片集合节点上去执行。

三、Scale Out/Scale Up 和 垂直切分/水平拆分

Mysql的扩展方案包括Scale Out和Scale Up两种。

Scale Out(横向扩展)是指Application可以在水平方向上扩展。一般对数据中心的应用而言,Scale out指的是当添加更多的机器时,应用仍然可以很好的利用这些机器的资源来提升自己的效率从而达到很好的扩展性。

Scale Up(纵向扩展)是指Application可以在垂直方向上扩展。一般对单台机器而言,Scale Up值得是当某个计算节点(机器)添加更多的CPU Cores,存储设备,使用更大的内存时,应用可以很充分的利用这些资源来提升自己的效率从而达到很好的扩展性。

MySql的Sharding策略包括垂直切分和水平切分两种。

垂直(纵向)拆分:是指按功能模块拆分,以解决表与表之间的io竞争。比如分为订单库、商品库、用户库...这种方式多个数据库之间的表结构不同。

水平(横向)拆分:将同一个表的数据进行分块保存到不同的数据库中,来解决单表中数据量增长出现的压力。这些数据库中的表结构完全相同。

表结构设计垂直切分。常见的一些场景包括

a).大字段的垂直切分。单独将大字段建在另外的表中,提高基础表的访问性能,原则上在性能关键的应用中应当避免数据库的大字段

b). 按照使用用途垂直切分。例如企业物料属性,可以按照基本属性、销售属性、采购属性、生产制造属性、财务会计属性等用途垂直切分

c). 按照访问频率垂直切分。例如电子商务、Web 2.0系统中,如果用户属性设置非常多,可以将基本、使用频繁的属性和不常用的属性垂直切分开

表结构设计水平切分。常见的一些场景包括

a). 比如在线电子商务网站,订单表数据量过大,按照年度、月度水平切分

b). Web 2.0网站注册用户、在线活跃用户过多,按照用户ID范围等方式,将相关用户以及该用户紧密关联的表做水平切分

c). 例如论坛的置顶帖子,因为涉及到分页问题,每页都需要显示置顶贴,这种情况可以把置顶贴水平切分开来,避免取置顶帖子时从所有帖子的表中读取

四、分表和分区

分表从表面意思说就是把一张表分成多个小表,分区则是把一张表的数据分成N多个区块,这些区块可以在同一个磁盘上,也可以在不同的磁盘上。

分表和分区的区别

1,实现方式上

mysql的分表是真正的分表,一张表分成很多表后,每一个小表都是完正的一张表,都对应三个文件(MyISAM引擎:一个.MYD数据文件,.MYI索引文件,.frm表结构文件)。

2,数据处理上

分表后数据都是存放在分表里,总表只是一个外壳,存取数据发生在一个一个的分表里面。分区则不存在分表的概念,分区只不过把存放数据的文件分成了许多小块,分区后的表还是一张表,数据处理还是由自己来完成。

3,提高性能上

分表后,单表的并发能力提高了,磁盘I/O性能也提高了。分区突破了磁盘I/O瓶颈,想提高磁盘的读写能力,来增加mysql性能。

在这一点上,分区和分表的测重点不同,分表重点是存取数据时,如何提高mysql并发能力上;而分区呢,如何突破磁盘的读写能力,从而达到提高mysql性能的目的。

4,实现的难易度上

分表的方法有很多,用merge来分表,是最简单的一种方式。这种方式和分区难易度差不多,并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。分区实现是比较简单的,建立分区表,跟建平常的表没什么区别,并且对代码端来说是透明的。

分区的适用场景

1. 一张表的查询速度已经慢到影响使用的时候。

2.表中的数据是分段的

3.对数据的操作往往只涉及一部分数据,而不是所有的数据

CREATE TABLE sales (

    id INT AUTO_INCREMENT,

    amount DOUBLE NOT NULL,

    order_day DATETIME NOT NULL,

    PRIMARY KEY(id, order_day)

) ENGINE=Innodb

PARTITION BY RANGE(YEAR(order_day)) (

    PARTITION p_2010 VALUES LESS THAN (2010),

    PARTITION p_2011 VALUES LESS THAN (2011),

    PARTITION p_2012 VALUES LESS THAN (2012),

PARTITION p_catchall VALUES LESS THAN MAXVALUE);

分表的适用场景

1. 一张表的查询速度已经慢到影响使用的时候。

2.当频繁插入或者联合查询时,速度变慢。

分表的实现需要业务结合实现和迁移,较为复杂。

五、分表与分库

分表能够解决单表数据量过大带来的查询效率下降的问题,但是,却无法给数据库的并发处理能力带来质的提升。面对高并发的读写访问,当数据库master服务器无法承载写操作压力时,不管如何扩展slave服务器,此时都没有意义了。因此,我们必须换一种思路,对数据库进行拆分,从而提高数据库写入能力,这就是所谓的分库。

与分表策略相似,分库可以采用通过一个关键字取模的方式,来对数据访问进行路由,如下图所示

\

六、分区与分片区别

\

### MySQL 分区分库分表的概念及最佳实践 #### 一、MySQL 分区 (Partitioning) MySQL分区功能允许将一张大表的数据按照一定的规则划分为多个较小的逻辑部分。这些部分可以分布在同一个数据库的不同文件中,从而提高查询性能并简化维护工作。 - **分区的优点**: 提高查询效率,减少 I/O 开销,优化备份恢复操作[^3]。 - **常见分区类型**: - `RANGE` 分区: 基于某个字段范围划分数据,适用于日期或数值类型的列。 ```sql CREATE TABLE sales ( id INT NOT NULL, sale_date DATE NOT NULL ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p0 VALUES LESS THAN (2010), PARTITION p1 VALUES LESS THAN (2015), PARTITION p2 VALUES LESS THAN MAXVALUE ); ``` - `LIST` 分区: 类似於 RANGE,但用于离散值集合。 - `HASH` 分区: 使用哈希函数分配记录到不同的分区。 ```sql CREATE TABLE employees ( emp_id INT NOT NULL, emp_name VARCHAR(50) ) PARTITION BY HASH(emp_id) PARTITIONS 4; ``` #### 二、MySQL 分库 (Sharding) 分库是一种水平扩展技术,通过将整个数据库拆分成若干个小规模的独立数据库实例来实现负载均衡。这种策略通常称为数据库分片(Database Sharding),它能够显著提升系统的可伸缩性并发处理能力。 - **分库的核心原则**: - 数据分布均匀以避免热点问题[^1]。 - 尽量减少跨节点事务的数量以便降低复杂度延迟时间。 - **具体实施方案**: - 利用中间件工具如 MyCat 或者 ProxySQL 来完成透明化的路由转发服务。 - 自定义业务逻辑层中的 sharding key 定义机制确保一致性的映射关系建立起来。 #### 三、MySQL 分表 (Table Splitting) 当单张表内的数据量过大时,则可以通过垂直或者水平方向上的切割方式来进行分表操作。前者指的是把不同属性组分开存放到各自的实体当中去;后者则是依据某些特定条件比如时间段等标准再细分成更多子集形式存在于此之上继续运作下去而已啦! - **水平分表例子**: ```sql -- 创建按月份分割的历史订单表结构模板 CREATE TABLE orders_YYYYMM( order_id BIGINT PRIMARY KEY AUTO_INCREMENT , customer_id INT UNSIGNED NOT NULL , product_info JSON DEFAULT '{}' COMMENT '商品详情' )ENGINE=InnoDB; -- 动态生成对应年月的实际存储对象名称 SET @current_month := DATE_FORMAT(NOW(), '%Y%m'); PREPARE stmt FROM CONCAT('CREATE TABLE IF NOT EXISTS orders_',@current_month,' LIKE orders_template;'); EXECUTE stmt ; DEALLOCATE PREPARE stmt ; INSERT INTO orders_202310(...)VALUES(...); ``` --- ### 总结说明 对于大规模应用而言,在设计初期就应该充分考虑到未来可能面临的挑战,并采取合适的措施加以应对。例如采用合理的索引策略配合高效的缓存体系共同作用下才能真正意义上达到理想效果。而针对具体的场景需求则需灵活调整上述提到的各种手段组合运用才最为有效果[^2]。
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值