Mysql分区 - Key分区

1、Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980’s）的数据，90年代（1990’s）的数据以及任何在2000年（包括2000年）后的数据。
2、Hash（哈希） – 这种模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如DBA可以建立一个对表主键进行分区的表。
3、Key（键值） – Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
4、List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。
5、Composite（复合模式） – 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。

结论

• 分区和未分区占用文件空间大致相同 （数据和索引文件）
• 如果查询语句中有未建立索引字段，分区时间远远优于未分区时间
• 如果查询语句中字段建立了索引，分区和未分区的差别缩小，分区略优于未分区。
• 对于大数据量，建议使用分区功能。
• 去除不必要的字段
• 根据手册， 增加myisam_max_sort_file_size 会增加分区性能( mysql重建索引时允许使用的临时文件最大大小)

一、Key分区

1、KEY只接受零个或多个列名的列表。任何用作分区键的列都必须包含表的部分或全部主键（如果表有主键）。如果没有指定列名作为分区键，则使用表的主键（如果有）

2、KEY分区和HASH分区相似，但是KEY分区支持除text和BLOB之外的所有数据类型的分区，而HASH分区只支持数字分区，KEY分区不允许使用用户自定义的表达式进行分区，KEY分区使用系统提供的HASH函数进行分区。当表中存在主键或者唯一键时，如果创建key分区时没有指定字段系统默认会首选主键列作为分区字列,如果不存在主键列会选择非空唯一键列作为分区列,注意唯一列作为分区列唯一列不能为null。

3、KEY分区中，使用MySQL服务器的哈希函数，NDB集群使用MD5()，对于使用其他存储引擎的表，服务器使用自己的内部哈希函数，该函数基于与PASSWORD()相同的算法；

4、LINEAR KEY与KEY的区别：线性key，它与常规key的不同之处在于，线性key使用线性二次幂算法，而常规key使用模算法

1、创建分区

CREATE TABLE `tesc`.`key_test`  (
  `id` INT NOT NULL PRIMARY KEY,
  `name` varchar(255) NULL,
  `date` datetime NULL 
)
PARTITION BY KEY(id)
PARTITIONS 4;

#线性key
CREATE TABLE `tesc`.`linear_key_test`  (
  `id` INT NOT NULL PRIMARY KEY,
  `name` varchar(255) NULL,
  `date` datetime NULL 
)
PARTITION BY LINEAR KEY(id)
PARTITIONS 4;

插入测试数据

insert into key_test(id,`name`,date) values(1,'zhangsan','1990-09-02'),(2,'lisi','2000-09-02'),(3,'wangwu','2011-09-02'),(4,'liuliu','2021-09-02');

2、删除分区

ALTER TABLE key_test COALESCE PARTITION 2;

注意：通过这种删除分区的方式不会将分区中的数据删除

3、增加分区

添加3个分区，即从之前的2个分区变为5个分区

ALTER TABLE key_test ADD PARTITION PARTITIONS 3;