我们在对表进行分区设计时，需要明白的是，它是一种对数据进行物理分区的数据管理方式。
什么场景下应该使用分区表？这有时像是一个哲学问题，因为不同的业务场景使用的数据库肯定技术肯定是不同的，另外对表进行分区的同时，要配合着数据库的表结构设计、索引设计等不同技术一同来做到最优实现。
这篇文章，将会结合 MySQL 数据库介绍分区表的使用、注意事项以及有哪些误区，希望对大家学习分区表的相关知识能提供一点帮助，从而在自己的业务实践表分区的最优设计。
如何进行表分区？
我们可以从一个算法来熟悉它，就是“分区函数”，分区表就是把N张具有相同物理表结构的表通过分区函数组成一张逻辑大表。那么 MySQL 数据库有RANGE、LIST、HASH、KEY、COLUMNS 这些分区函数。
有了分区函数，还要指定在哪些表列上应用分区函数，也就是所谓的“分区列”。
需要特别注意的是，对 MySQL 表进行分区设计时，进行分区计算的列必须是联合主键的一部分，否则我们创建分区表时会报错，参见下面的SQL示例：
CREATE TABLE t (
a INT,
b INT,
c DATETIME(6),
d VARCHAR(32),
e INT,
PRIMARY KEY (a,b)
)
partition by range columns(c) (
PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
PARTITION p9999 VALUES LESS THAN (MAXVALUE)
);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).在创建表 t 时，给列 a、b 设置了联合主键，然后根据列 c（时间列）进行拆分数据，这样MySQL就会根据时间范围把不同数据放到不同分区中。
理论上这样没问题，但我们可以看到出错了，报错信息的意思是：分区表的主键一定要包含分区函数的列。
基于该错误提示，我们需要稍微改下创建表的SQL，让主键也包含列 c，参见如下SQL语句：
CREATE TABLE t (
a INT,
b INT,
c DATETIME,
d VARCHAR(32),
e INT,
PRIMARY KEY (a,b,c),
KEY idx_e (e)
)
partition by range columns(c) (
PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
PARTITION p9999 VALUES LESS THAN (MAXVALUE)
);这回可以正常建表了，执行完SQL后，我们可以在MySQL物理存储硬盘上看到p0000、p2019、p2020、p9999这四个分区对应的 ibd 文件：
t#p#p0000.ibd t#p#p2019.ibd t#p#p2020.ibd t#p#p9999.ibd当我们往表 t insert 不同时间c列的数据时，这些数据就会落在不同的分区里，最终也就是放在不同的 ibd 文件里了。
因此我们可以这样理解：MySQL 中的分区表是把一张表拆成了多张独立的小表，并且这些表有自己的索引，逻辑上看还是一张表，但物理上它们的数据是存储在不同文件中的。
由此，又出现一个新问题，分区表和普通表的索引有区别吗？分区表是如何实现唯一索引的？
分区表的唯一索引有什么要注意的地方？
当我们对MySQL数据库中的表进行分区后，就会出现“局部”和“全局”的概念，对于分区表的索引，都是局部索引，没有全局。
这就代表，每个分区里的索引都是独立的。
所以我们要在分区表上建唯一索引时，需要把所有分区列加上，否则唯一索引会添加失败。
还是拿表 t 举例，在列 d 上增加一个唯一索引：
ALTER TABLE t ADD UNIQUE KEY idx_d(d);
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).毫无疑问，出错了，因为我们的分区列是 c，结合分区表的唯一索引必须包含分区函数中所有列的准则。我们修改下创建唯一索引的SQL：
ALTER TABLE t ADD UNIQUE KEY idx_d(d,c);这回没问题了。
分区表一定能带来性能提升吗？
先说答案：NO！
一般根据业务场景，有两类数据库应用，一类是OLTP(在线事务处理)，如博客、电子商务、网游等；另一类是OLAP(在线分析处理)，如数据仓库、数据集市。
分区只能提升OLAP应用的查询性能，为什么？
因为OLAP应用的特征是需要频繁查询扫描全表，如果分区了，只需要扫描对应分区的数据即可。
相反OLTP应用大多是通过索引返回表里若干记录，绝大部分场景不会从一张大表中拿取超过10%的数据。
我们可以结合MySQL的B+索引特性，来分析下表分区的查询效率。
假设我们给上表 t insert 1000w条数据，那此时B+树的高度为3，若将表 t 分为10个分区，则每张小表100W条数据的B+树高度则是2。
若查询用到的索引在分区列上，则从原表2到3次IO可以提升到2次IO。
但若查询用到的索引在其它列上，则最坏的情况可能需要10 * 2 = 20次IO。
所以，分区能不能带来查询性能提升，得看不同的业务系统需求和表设计。
苏公网安备32021302001419号