深入理解MySQL分区表原理与企业级实战

墨蓝 2023-09-23 14:55 89阅读 0赞

# **本文导读** #

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

# **一、什么是分区表** #

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

分区的代码实际上是底层表的一组处理程序对象的封装。分区表的请求将通过句柄对象（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中在某些情况下，列可以直接用于分区。

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

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