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

📫作者简介：小明java问道之路，专注于研究 Java/ Liunx内核/ C++及汇编/计算机底层原理/源码，就职于大型金融公司后端高级工程师，擅长交易领域的高安全/可用/并发/性能的架构设计与演进、系统优化与稳定性建设。

 

📫 热衷分享，喜欢原创~ 关注我会给你带来一些不一样的认知和成长。

 

🏆 InfoQ签约作者、CSDN专家博主/后端领域优质创作者/内容合伙人、阿里云专家/签约博主、51CTO专家 🏆

 

🔥如果此文还不错的话，还请👍关注、点赞、收藏三连支持👍一下博主~ 

本文目录

本文目录

本文导读

一、什么是分区表

二、分区表的工作原理

1、分区表增删改查原理

2、分区表工作原理

三、分区表使用实战

1、分区表企业级实战

2、分区表的使用场景

3、分区表自身限制

4、分区表的误区

4.1 性能提升

4.2 null值会使分区过滤无效

4.3 分区列和索引列不匹配

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

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

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

总结

---

本文导读

本文详细讲解什么是分区表，分区表增删改查的工作原理以及分区表的实战，分区表的场景有哪些，哪些场景不建议用分区表，并列举出六点使用分区表的误区。

一、什么是分区表

分区表是一个独立的逻辑表，底层由多个物理子表组成。

分区的代码实际上是底层表的一组处理程序对象的封装。分区表的请求将通过句柄对象（Handler Object）转换为对存储引擎的接口调用。

因此，分区完全封装了SQL层的底层实现，对应用程序是透明的，对SQL层是黑盒的。然而，从底层文件系统的角度来看，很容易发现每个分区表都有一个用#分隔的表文件。

MySQL实现分区表的方式——封装底层表——意味着索引也是根据分区子表定义的，并且没有全局索引。

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 维护分区的成本可能更高

分区重组的原理类似于ALTER，首先，创建一个临时分区，然后将数据复制到其中，最后删除原始分区。这样会使维护分区的成本可能更高

总结

本文详细讲解什么是分区表，分区表增删改查的工作原理以及分区表的实战，分区表的场景有哪些，哪些场景不建议用分区表，并列举出六点使用分区表的误区。