一、分区的原理
表的分区的原理理解起来比较简单，其实就是把一张大数据量的表，根据分区策略进行分区，分区设置完成之后，由数据库自身的储存引擎来实现分发数据到指定的分区中去，一张数据表被分成了n个分区，并且分区被放入到不同的介子disk中，每个disk中包含自少一个分区，这就实现了数据的均衡以及通过跨分区介子检索提高了整体的数据操作IO吞吐率。
二、表分区的策略
目前在MySql中支持四种表分区的方式，分别为HASH、RANGE、LIST及KEY，当然在其它的类型数据库中，分区的实现方式略有不同，但是分区的思想原理是相同，具体如下：
1、HASH
HASH分区主要用来确保数据在预先确定数目的分区中平均分布，而在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中，而在HASH分区中，MySQL自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。比如：
CREATE TABLE t_product_item (
id int(7) not null,
title varchar(40) not null,
subtitle varchar(60) null,
price double not null,
imgurl varchar(70) not null,
producttype int(2) not null,
createtime datetime not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(YEAR(createtime))
PARTITIONS 10
;
上面的例子，使用HASH函数对createtime日期进行HASH运算，并根据这个日期来分区数据，这里共分为10个分区。
NOTE：
可以通过在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回整数的表达式。它可以是字段类型为MySQL 整型的一列的名字，也可以是返回非负数的表达式。另外，可能需要在后面再添加一个“PARTITIONS num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。
2、RANGE
基于属于一个给定连续区间的列值，把多行分配给同一个分区，这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。比如：
CREATE TABLE t_product_item (
id int(7) not null,
title varchar(40) not null,
subtitle varchar(60) null,
price double not null,
imgurl varchar(70) not null,
producttype int(2) not null,
createtime datetime not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(producttype) (
PARTITIONP0 VALUES LESS THAN(2),
PARTITIONP1 VALUES LESS THAN(4),
PARTITIONp2 VALUES LESS THAN(6),
PARTITIONp3 VALUES LESS THAN MAXVALUE
);
上面的例子，使用了范围RANGE函数对产品类型进行分区，共分为4个分区，产品类别为0,1的对应在分区P0中，2,3类别在分区P1中，依次类推即可。那么类别编号大于6的怎么分区呢？我们可以使用MAXVALUE来将大于6的数据统一存放在分区P3中即可。
3、LIST
类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择分区的。LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。比如：
DROP TABLE IF EXISTS t_product_item;
CREATE TABLE t_product_item (
id int(7) not null,
title varchar(40) not null,
subtitle varchar(60) null,
price double not null,
imgurl varchar(70) not null,
producttype int(2) not null,
createtime datetime not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST(producttype) (
PARTITIONP0 VALUES IN (0,1),
PARTITIONP1 VALUES IN (2,3),
PARTITIONP2 VALUES IN (4,5),
PARTITIONP3 VALUES IN (6,7,8,9,10,11,12)
)
上面的例子，使用了列表匹配LIST函数对产品类型进行分区，共分为4个分区，产品类别为0,1的对应在分区P0中，2,3类别在分区P1中，依次类推即可。那么类别编号大于12的怎么分区呢？这里不同于RANGE，LIST分区的数据必须匹配列表中的产品类别才能进行分区。
4、KEY
类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。比如：
CREATE TABLE t_product_item (
id int(7) not null,
title varchar(40) not null,
subtitle varchar(60) null,
price double not null,
imgurl varchar(70) not null,
producttype int(2) not null,
createtime datetime not null
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY(producttype)
PARTITIONS 10;
NOTE：
此种分区算法目前使用的比较少，大家知道其存在和怎么使用即可。
三、表分区的实施
这里我以RANGE分区算法为例，进行数据表分区的实现，具体如下：
1、建分区表
sql：
DROP TABLE IF EXISTS t_product_item;
CREATE TABLE t_product_item (
id int(7) not null,
title varchar(40) not null,
subtitle varchar(60) null,
price double not null,
imgurl varchar(70) not null,
producttype int(2) not null,
createtime datetime not null
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(producttype) (
PARTITIONP0 VALUES LESS THAN(2),
PARTITIONP1 VALUES LESS THAN(4),
PARTITIONp2 VALUES LESS THAN(6),
PARTITIONp3 VALUES LESS THAN MAXVALUE
)
NOTE：
这里使用了range算法分区，目前分为4个分区，分区是按照产品的类别进行划分，具体说明请查看上面的range讲解说明。
2、插入数据
A、插入产品类型为0,1 共1条
insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
values(0,'A','A-title',99.99,'http://null',0);
B、插入产品类型为2,3 共1条
insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
values(1,'A','A-title',99.99,'http://null',3);
C、插入产品类型为4,5 共1条
insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
values(2,'A','A-title',99.99,'http://null',5);
D、插入产品类型为6,7，8 共3条
insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
values(3,'A','A-title',99.99,'http://null',6);
insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
values(4,'A','A-title',99.99,'http://null',7);
insert into t_product_item(id,title,subtitle,price,imgurl,producttype)
values(5,'A','A-title',99.99,'http://null',8);
3、验证分区
首先，查看下各个分区信息及数据是否正确：
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
andtable_name='t_product_item';
执行结果：
从上图知道，我们根据RNAGE算法，通过产品类型producttype进行RNAGE运算，结果插入数据的结果都已经正确的插入到对应的分区位置。
其次，使用EXPLAIN分析查询sql：
explain select * from t_product_item where producttype=3;
执行结果：
从上图知道，这次的查询数据是从partions=P0中直接查询的，而不是全表查询，所以如果数据量很大时会明显提高检索效率。
四、踩过的坑
一、背景
mysql 表中已有 4 亿数据，为提高查询效率，需创建分区，一开始计划是创建 HASH 分区，结果报错：
ERROR 1659 (HY000): Field 'partno' is of a not allowed type for this type of partitioning
查询得知报错原因，HASH 分区只支持数字分区，而我要分区的字段是 varchar 类型，故改用 KEY 分区
二、解决
KEY 分区语句：
alter table TABLENAME PARTITION BY key(COLUMN) PARTITIONS NUM;
TABLENAME ：表名
COLUMN：列名
NUM：分区数量
检查分区是否创建成功
EXPLAIN PARTITIONS SELECT * FROM `cars`
若成功，结果如下：
踩坑
拿小表测试 100 个分区发现，分区不均匀，一半分区有数据，一半没有数据，网上查询得知：key 分区，只有指定分区数目为质数，才能保证每个分区都有数据。
小表测试分区 97 个，分布均匀。
KEY 分区和 HASH 分区的区别
KEY 分区和 HASH 分区相似，但是 KEY 分区支持除 text 和 BLOB 之外的所有数据类型的分区，而 HASH 分区只支持数字分区，KEY 分区不允许使用用户自定义的表达式进行分区，KEY 分区使用系统提供的 HASH 函数进行分区。
当表中存在主键或者唯一键时，如果创建 key 分区时没有指定字段系统默认会首选主键列作为分区字列，如果不存在主键列会选择非空唯一键列作为分区列，注意唯一列作为分区列唯一列不能为null。
查看已分区表的详情
SELECT *
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'TABLE_NAME';
————————————————
版权声明：本文为CSDN博主「persistenceヾ(◍°∇°◍)ﾉ」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/persistencegoing/article/details/94437806
6818
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
