> For the complete documentation index, see [llms.txt](https://maxwell.gitbook.io/way-to-architect/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://maxwell.gitbook.io/way-to-architect/shu-ju-ku/mysql/mysqlzhong-de-sharding/fen-qu-biao.md).

# 分区表

## 概述

分区表是MySQL从5.0版本引入的高级特性，对用户而言，分区表在逻辑上是一张表，但底层由多个物理子表组成。分区对于SQL层来说，是一个完全封装底层实现的黑盒子，对应用透明。

其中，MYISAM、InnoDB、NDB等引擎支持分区，CSV、FEDORATED、MERGE等引擎不支持分区。

## 分区表的操作原理

**select**

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。

**insert**

当写入一条记录时，分区层打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应的底层表

**delete**

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

**update**

当更新一条数据时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，然后对底层表进行写入操作，并对原数据所在的底层表进行删除操作

虽然每个操作都会打开并锁住所有的底层表，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，如：InnoDB，则会在分区层释放对应的表锁，这个加锁和解锁过程与普通Innodb上的查询类似。

## 分区表的类型

**水平分区**

根据列属性按行分，将不同行记录存储在不同的分区中。

水平分区的几种模式：

* Range（范围）
* * 允许将数据划分不同范围。
* * 例如将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在2000年（包括2000年）后的数据。 
* Hash（哈希）
* * 允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。
* * 例如建立一个对表主键进行分区的表。 
* Key（键值）
* * Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。 
* List（预定义列表）
* * 允许系统通过定义的列表的值所对应的行数据进行分割。
* * 例如：建立一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。 
* Columns：
* * 对range，list分区的补充，弥补了后两者只支持整型数分区（或者通过转换为整型数），使得支持数据类型增加很多（所有整数类型，日期时间类型，字符类型），还支持多列分区。
* * 在多列分区表上插入数据时，采用元组的比较，即多列排序，先根据field1排序，再根据field2排序，根据排序结果来来分区存储数据。
* Composite（复合模式）
* * 以上模式的组合使用，如：在初始化已经进行了Range范围分区的表上，可以对其中一个分区再进行hash哈希分区。 

**垂直分区**

按列进行分区，不同的列存储在不同的分区。比如一个包含了TEXT和BLOB列的表，这些TEXT和BLOB列又不经常被访问，可以把这些不经常使用的TEXT和BLOB划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

## 分区表的使用场景

**适用场景**

（1）表非常大以至于无法全部都放到内存，或者只在表的最后部分有热点数据，其他均为历史数据；

（2）分区表数据更容易维护（可独立对分区进行优化、检查、修复及批量删除大数据可以采用drop分区的形式等）；

（3）分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备；

（4）分区表可以避免某些特殊的瓶颈，如 InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等 ；

（5）可以备份和恢复独立的分区，非常适用于大数据集的场景；

**限制**

（1）单表最多支持1024个分区

（2）MySQL5.1只能对表的整型列进行分区，或者列可以通过分区函数转化成整型列；MySQL5.5的RANGE LIST类型可以直接使用列进行分区

（3）如果分区字段中有主键或唯一索引的列，那么所有的主键列和唯一索引列都必须包含进来

（4）分区表无法使用外键约束

（5）分区必须使用相同的Engine

**但是在实际场景中，一般不会使用MySQL提供的分区表的功能，而是选择自己分库分表来水平扩展。**

原因如下：

1）分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁

2）一旦数据量并发量上来，如果在分区表进行关联操作，性能较低

3）自己分库分表，掌控业务场景与访问模式，可控。而分区表底层是一个完全封装的黑盒子，不太可控。

## 参考

《高性能MySQL》

[MySQL 分区表原理及数据备份转移实战](http://blog.51cto.com/daisywei/1900331)

[MySQL分区表答疑  <br>](https://www.w3cschool.cn/architectroad/architectroad-mysql-partition-table.html)