【MySQL】深入理解MySQL分区表原理与企业级实战（MySQL专栏启动）

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新大数据全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip204888 （备注大数据）

正文

MySQL在创建表时使用 PARTITION BY 子句定义存储在每个分区中的数据（在第四节详细说明）。

在执行查询时，优化器将根据分区定义筛选没有所需数据的分区。这样，查询不需要扫描所有分区——只需找到包含我们需要的数据的分区。

分区的主要目的之一是以更粗的粒度将数据划分为不同的表。通过这种方式，可以将相关数据存储在一起。此外，可以方便地批量删除整个分区的数据。

二、分区表的工作原理

1、分区表增删改查原理

分区表上的操作遵循以下操作逻辑：

SELECT

查询分区表时，分区层首先打开并锁定所有底层表。优化器首先确定是否可以过滤部分分区，然后调用相应的存储引擎接口来访问每个分区的数据。

INSERT

写入记录时，分区层首先打开并锁定所有底层表，然后确定哪个分区接收记录，然后将记录写入相应的底层表。

DELETE

删除记录时，分区层首先打开并锁定所有底层表，然后确定与数据对应的分区，最后删除相应的底层表。

UPDATE

更新记录时，分区层首先打开并锁定所有底层表。MySQL首先确定要更新的记录的分区，然后取出数据并对其进行更新，然后确定更新后的数据应该放在哪个分区中，最后写入基础表并删除原始数据所在的基础表。

2、分区表工作原理

分区表由多个相关的底层表实现，这些底层表也由处理程序对象表示，因此我们也可以直接访问每个分区。

存储引擎管理分区的所有基础表，就像管理公共表一样（所有基础表必须使用相同的存储引擎）。分区表的索引仅向每个基础表添加相同的索引。

从存储引擎的角度来看，基础表与公共表没有区别，存储引擎不需要知道它是公共表还是分区表的一部分。

尽管每个操作都将”“首先打开并锁定所有基础表”，但这并不意味着分区表将在处理期间锁定整个表。

如果存储引擎可以自己实现行级锁，例如InnoDB，它将在分区层释放相应的表锁。这个锁定和解锁过程类似于普通InnoDB上的查询。

我们在第四节详细说明，使用一些示例来了解在访问分区表时打开和锁定所有基础表的成本和后果。

三、分区表使用实战

1、分区表企业级实战

MySQL支持多个分区表。我们看到的最常见的分区是基于范围的。每个分区存储一个范围内的记录。分区表达式可以是列或包含列的表达式。

例如，下表 按年创建分区表 # 存储在不同的分区中：

CREATE TABLE `***` (
	`ID` BIGINT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT '主键id',
	`LOG_ID` VARCHAR ( 32 ) NOT NULL COMMENT '交易流水号',
	`ODR_ID` VARCHAR ( 32 ) NOT NULL COMMENT '父单号',
	`SUB_ODR_ID` VARCHAR ( 32 ) NOT NULL COMMENT '子单号',
	`CREATE_TIME` datetime ( 0 ) NOT NULL COMMENT '创建时间',
	`CREATE_BY` VARCHAR ( 32 ) NOT NULL COMMENT ' 创建人',
	`UPDATE_TIME` datetime ( 0 ) NOT NULL DEFAULT CURRENT_TIMESTAMP ( 0 ) ON UPDATE CURRENT_TIMESTAMP ( 0 ) COMMENT '更新时间',
	`UPDATE_BY` VARCHAR ( 32 ) NOT NULL COMMENT '更新人',

	PRIMARY KEY ( `ID`, `CREATE_TIME` ) USING BTREE,
	UNIQUE INDEX `UNQ_LOG_SUBODR_ID` ( `LOG_ID`, `SUB_ODR_ID`, `CREATE_TIME` ) USING BTREE,

	INDEX `IDX_ODR_ID` ( `ODR_ID` ) USING BTREE,
	INDEX `IDX_SUB_ID` ( `SUB_ODR_ID` ) USING BTREE,
	INDEX `IDX_CREATE_TIME` ( `CREATE_TIME` ) USING BTREE,
	INDEX `IDX_UPDATE_TIME` ( `UPDATE_TIME` ) USING BTREE 
	) ENGINE = INNODB COMMENT = '***业务明细表' 

PARTITION BY RANGE ( YEAR ( `CREATE_TIME` ) ) (
	PARTITION p_2021 VALUES LESS THAN ( 2021 ),
	PARTITION p_2022 VALUES LESS THAN ( 2022 ),
	PARTITION p_2023 VALUES LESS THAN ( 2023 ),
	PARTITION p_2024 VALUES LESS THAN ( 2024 ),
	PARTITION p_catchall VALUES LESS THAN MAXVALUE )  ;

PARTITION分区子句中可以使用各种函数。但是，表达式返回的值必须是一个确定的整数，而不是常数。这里我们使用函数 YEAR() 或任何其他函数。

2、分区表的使用场景

一、表太大，无法存储在内存中，或者只有表的最后一部分有热数据，其余部分是历史数据。

二、分区表数据更易于维护。例如，如果要批量删除大量数据，可以使用清除整个分区的方法。此外，可以优化、检查和修复独立的分区。

三、分区表的数据可以分布在不同的物理设备上，从而可以有效地使用多个硬件设备。

四、分区表可以用来避免一些特殊的瓶颈，例如独占访问InnoDB的单个索引和ext3文件系统的索引节点锁竞争。

五、备份和恢复独立的分区，大的数据集场景。

3、分区表自身限制

一、一个表最多只能有1024个分区。

二、在MySQL 5.1中，分区表达式必须是整数或返回整数的表达式。在MySOL 5.5中在某些情况下，列可以直接用于分区。

三、如果分区字段中有主键列或唯一索引列，则必须包括所有主键列和唯一索引列。

四、分区表中不能使用外键约

4、分区表的误区

4.1 性能提升

许多人会认为分区表将一个大表划分为多个小表，因此MySQL数据库的性能将大大提高。

这是错误的理解！分区表技术并不是用来提高MySQL数据库的性能，而是为了方便数据管理。

分区表的创建需要主键包含分区列；在分区表中唯一索引仅在当前分区文件唯一，而不是全局唯一；分区表唯一索引推荐使用类似 UUID 的全局唯一实现；

分区表不解决性能问题，如果使用非分区列查询，性能反而会更差；推荐分区表用于数据管理、速度快、日志小。

4.2 null值会使分区过滤无效

检查第一个分区，因为 YEAR() 函数在接收非法值时可能返回NULL值，因此该范围的值可能返回NULL并存储在第一个分区中。如果第一个分区非常大，特别是当使用“完全扫描数据，无索引”策略时，成本将非常高。

4.3 分区列和索引列不匹配

如果定义的索引列和分区列不匹配，查询将无法执行分区筛选。

4.4 选择分区的成本可能更高

不同类型的分区以不同的方式实现，因此它们的性能不同。当我们在行中写入大量数据时。每次将一行数据写入范围分区表时，都需要扫描分区定义列表以找到合适的目标分区。

这个问题可以通过限制分区的数量来缓解。根据实际经验，对于大多数系统，大约100个分区没有问题。

4.5 锁住所有表的成本可能更高

当查询和访问分区表时，MySQL需要打开并锁定所有底层表，这是分区表的另一个成本。

单个操作，例如使用批插入或LOAD DATA INFILE一次删除多行数据。

4.6 维护分区的成本可能更高

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip204888 （备注大数据）

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

不再深入研究，那么很难做到真正的技术提升。**

需要这份系统化的资料的朋友，可以添加V获取：vip204888 （备注大数据）
[外链图片转存中…(img-90zjrUBl-1713348115791)]

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！