Mysql 分库分区分表

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: Mysql 分库分区分表

搞懂MySQL分区 - GrimMjx - 博客园 (cnblogs.com)腾讯后台开发暑期实习一面|讲解|0312_牛客网 (nowcoder.com)MySQL的分区/分库/分表总结 - 知乎 (zhihu.com)

概念

  1. 分区:把一张表的数据分成多个区块,在逻辑上看最终只是一张表,但底层是由多个物理区块组成的
  2. 分表:把一张表按一定的规则分解成多个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明,然后操作它。
  3. 分库:把一个库拆成多个库,突破库级别的数据库操作I/O瓶颈。

InnoDB逻辑存储结构

MySQL原理(一):逻辑存储结构 - 知乎 (zhihu.com)

  • : 段不对应表空间中某一个连续的物理区域,而是一个逻辑上的概念,由若干个零散的页面(碎片区)和一系列完整的区组成。
    一个表空间里面会有很多个段组成。常见的段有数据段、索引段、回滚段等。
  • : 区是段的组成部分,它是一个连续的磁盘空间,用于存储数据页。在 InnoDB 存储引擎中,一个区通常包含多个数据页,这些页可以存储表数据、索引数据或其他数据。
  • : 页是 MySQL 中数据存储的最小单位,它代表了一个固定大小的数据块,通常为 16KB。每个页可以存储一定数量的数据行、索引键和其他数据。在 InnoDB 存储引擎中,数据页用于存储表数据、索引数据和事务日志等。

分区

  • 概念: 分区是将一个大表在物理上分割成多个较小的、更易于管理的片段,但从逻辑上来看,它们仍然被视为同一个表。
  • 类比理解: 你可以想象一个大书架,上面摆满了书,很难管理。如果我们将这些书按照类别(比如小说、历史、科学等)分到不同的小书架上,每个小书架就更容易管理了。这就是分区的基本思想。
  • 示例: 假设有一个销售表,记录了多年的销售数据。我们可以按照年份进行分区,这样查询某一年的数据时,只需要扫描该年份的分区即可。
  • 实现:在分区策略中,尽管表在物理存储上被分成了多个部分(每个部分有自己的.ibd文件),但在逻辑上它们仍然被视为一个整体,并且只与一个.frm文件相关联(1个.frm文件对应多个.ibd文件

.frm 文件用于存储表的结构定义,包括列名、数据类型等 .ibd 文件用于存储表的数据和索引

分区类型

  1. RANGE分区:基于一个给定区间边界,得到若干个连续区间范围,按照分区键的落点,把数据分配到不同的分区;
  2. LIST分区:类似RANGE分区,区别在于LIST分区是基于枚举出的值列表分区,RANGE是基于给定连续区间范围分区;
  3. HASH分区:基于用户自定义的表达式的返回值,对其根据分区数来取模,从而进行记录在分区间的分配的模式。这个用户自定义的表达式,就是MySQL希望用户填入的哈希函数。
  4. KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且使用MySQL 服务器提供的自身的哈希函数。

RANGE 类型

sql

复制代码

-- 插入下面的数据,使用 RANGE 分区
create database `m_test_db`;
CREATE TABLE `m_test_db`.`Order` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `partition_key` INT NOT NULL,
  `amt` DECIMAL(5) NULL,
  PRIMARY KEY (`id`, `partition_key`)) PARTITION BY RANGE(partition_key) PARTITIONS 5( PARTITION part0 VALUES LESS THAN (201901),  PARTITION part1 VALUES LESS THAN (201902),  PARTITION part2 VALUES LESS THAN (201903),  PARTITION part3 VALUES LESS THAN (201904),  PARTITION part4 VALUES LESS THAN (201905)) ;
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');
-- 查看这张表所有的分区
SELECT * FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'm_test_db' AND TABLE_NAME = 'Order';
-- 查看这一个查询用到的分区
SELECT * FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME = 'Order' 
AND TABLE_SCHEMA = 'm_test_db' 
AND PARTITION_DESCRIPTION = '201902';
-- PARTITION_ORDINAL_POSITION 2

LIST分区

  LIST分区和RANGE分区很相似,只是分区列的值是离散的,不是连续的。LIST分区使用VALUES IN,因为每个分区的值是离散的,因此只能定义值。

HASH分区

  说到哈希,那么目的很明显了,将数据均匀的分布到预先定义的各个分区中,保证每个分区的数量大致相同。

KEY分区

  KEY分区和HASH分区相似,不同之处在于HASH分区使用用户定义的函数进行分区,KEY分区使用数据库提供的函数进行分区。

分表

  • 概念: 分表是将一个大表拆分成多个具有相同结构的小表,每个小表存储不同的数据子集。
  • 类比理解: 这有点类似于将一个大班级分成多个小班,每个小班管理起来更加容易。
  • 示例: 假设有一个用户表,用户数量巨大。我们可以按照用户ID的范围进行分表,比如用户ID 1-1000在表1,用户ID 1001-2000在表2,以此类推。
  • 实现:在分表策略中,每个分表都有自己独立的.frm和.ibd文件,这些分表在逻辑和物理存储上都是完全独立的。(1个.frm文件对应1个.ibd文件

水平分表

水平分表和分区很像,或者说分区就是水平分表的数据库实现版本,它们分的都是行记录,就像用一把刀,水平的将一个表切成多张表一样。

针对数据量巨大的单张表(比如订单表),我们按照某种规则,切分到多张表里面去。

垂直分表

水平分表分的是行记录,而垂直分表,分的是列字段,它就像用一把刀,垂直的将一个表切成多张表一样。

将不常用的,或者数据量大的字段拆分到“扩展表”上。这样避免查询时,数据量太大造成的“跨页”问题。

分库

一台机器的性能是有限制的。

将一个库分成多个库,并在多个服务器上部署,就可以突破单服务器的性能瓶颈,这是分库必要性的最主要原因。

分库之后也可以进行分表,两种方法结合

分库的类型

分库同样分为水平分库和垂直分库,与水平分表和垂直分表的规则类似。

分库分表的问题

  1. 事务问题
  • 解释:由于数据存储到了不同的库上,无法依赖数据库的事务。
  • 解决方法:利用分布式事务,协调不同库之间的数据原子性,一致性。
  1. 跨库跨表的join问题
  • 解释:在不同库、表的数据被切割,原本简单的join复杂性大大增加,难以定位,查询次数也有可能增加。
  • 解决方法:尽力避免跨库join,或者查询代码整合好
  1. 因分库分表带来的额外性能开销

小结

分区一般都是放在单机里的,方便归档。

分区的类型有 RANGE、LIST、HASH、KEY分区,用的比较多的是时间范围RANGE分区

分表是指拆分一张表,变成多个表。方法有水平和垂直分表。

分库逻辑上和分表类似,是指拆分一个完整的库,分布式部署到多机上去

分库分表需要代码实现,分区则是mysql内部实现。分库分表和分区并不冲突,可以结合使用。

分库分表会带来 事务、跨库跨表join问题和额外的性能开销

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
关系型数据库 MySQL 中间件
|
2月前
|
缓存 关系型数据库 MySQL
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
115 0
|
8天前
|
消息中间件 关系型数据库 MySQL
实时计算 Flink版产品使用问题之从MySQL数据库中捕获变更数据并进行实时处理如何按天分表同步CDC数据
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1月前
|
分布式计算 DataWorks 关系型数据库
DataWorks产品使用合集之当需要将数据从ODPS同步到RDS,且ODPS表是二级分区表时,如何同步所有二级分区的数据
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
29 7
|
18天前
|
SQL 关系型数据库 MySQL
mysql面试之分库分表总结
mysql面试之分库分表总结
35 0
|
26天前
|
关系型数据库 MySQL 数据库
深入探讨MySQL分表策略与实践
深入探讨MySQL分表策略与实践
26 0
|
26天前
|
存储 缓存 关系型数据库
心得经验总结:理解MySQL——并行数据库与分区(Partion)
心得经验总结:理解MySQL——并行数据库与分区(Partion)
11 0
|
2月前
|
关系型数据库 MySQL 分布式数据库
PolarDB产品使用合集之PolarDB MySQL标准版中带有分区功能吗
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
2月前
|
存储 关系型数据库 MySQL
MySQL分区的优缺点
数据库中分区是将表或索引的数据划分成更小、更可管理的部分的一种技术。这些部分被称为分区,每个分区可以独立地进行维护和管理。
123 0
|
2月前
|
NoSQL 算法 关系型数据库
redis与mysql的数据一致性问题( 网络分区)
redis与mysql的数据一致性问题( 网络分区)
36 0