mysql表分区，mysql分区表
【0】分区介绍
（0.1）概念及其分区后性质
修改主键：alter table jygoldlog.log_appstart drop primary key,add primary key (logid,logtime);
mysql分区后每个分区成了独立的文件，虽然从逻辑上还是一张表其实已经分成了多张独立的表；
但 从“information_schema.INNODB_SYS_TABLES”系统表可以看到每个分区都存在独立的TABLE_ID；
由于Innodb数据和索引都是保存在".ibd"文件当中（从INNODB_SYS_INDEXES系统表中也可以得到每个索引都是对应各自的分区(primary key和unique也不例外）），所以分区表的索引也是随着各个分区单独存储。
在INNODB_SYS_INDEXES系统表中type代表索引的类型;0:一般的索引,
1:(GEN_CLUST_INDEX)不存在主键索引的表,会自动生成一个6个字节的标示值
2:unique索引,3:primary索引;所以当我们在分区表中创建索引时其实也是在每个分区中创建索引，每个分区维护各自的索引（其实也就是local index）；
对于一般的索引(非主键或者唯一)没什么问题由于索引树中只保留了索引key和主键key(如果存在主键则是主键的key否则就是系统自动生成的6个的key)不受分区的影响；
但是如果表中存在主键就不一样了，虽然在每个分区文件中都存在主键索引但是主键索引需要保证全局的唯一性就是所有分区中的主键的值都必须唯一（唯一键也是一样的道理）；
所以在创建分区时如果表中存在主键或者唯一键那么分区列必须包含主键或者唯一键的部分或者全部列（全部列还好理解，部分列也可以个人猜测是为了各个分区和主键建立关系）；
由于需要保证全局性又要保证插入数据更新数据到具体的分区所以就需要将分区和主键建立关系,由于通过一般的索引进行查找其它非索引字段需要通过主键如果主键不能保证全局唯一性的话那么就需要去每个分区查找了，这样性能可想而知。
To enforce the uniqueness we only allow mapping of each unique/primary key value to one partition.If we removed this limitation it would mean that for every insert/update we need to check in every partition to verify that it is unique. Also PK-only lookups would need to look into every partition.（0.2）索引方式
1.主键分区
主键分区即字段是主键同时也是分区字段，性能最好
2. 部分主键+分区索引
使用组合主键里面的部分字段作为分区字段，同时将分区字段建索引
3.分区索引
没有主键，只有分区字段且分区字段建索引
4.分区+分区字段没有索引
只建了分区，但是分区字段没有建索引
分区系列文章：
RANGE分区：http://www.cnblogs.com/chenmh/p/5627912.html
LIST分区：http://www.cnblogs.com/chenmh/p/5643174.html
COLUMN分区：http://www.cnblogs.com/chenmh/p/5630834.html
HASH分区：http://www.cnblogs.com/chenmh/p/5644496.html
KEY分区：http://www.cnblogs.com/chenmh/p/5647210.html
子分区：http://www.cnblogs.com/chenmh/p/5649447.html
指定各分区路径：http://www.cnblogs.com/chenmh/p/5644713.html
分区介绍总结：http://www.cnblogs.com/chenmh/p/5623474.html
（0.3）查阅表分区信息
SELECT table_schema,table_name,PARTITION_NAME,PARTITION_METHOD, TABLE_ROWS,SUBPARTITION_NAME
FROM information_schema.PARTITIONS where partition_name is not null;
【1】表分区的场景和类型
（1.0）使用前必看注意事项
0、分区列必须包含在第一个 主键/唯一索引中
0.1、比如主键是 logid，分区列是 logtime ，那么得 alter table drop primary key,add primary key (logid,logtime) ，把logtime 加入主键后，才能作为分区列
但注意，如果主键相关值 经常被更新，那么容易死锁
0.2、如果现表有一个主键索引，有一个唯一索引，那么执行 0.1后，并没有用，删除主键后默认的唯一索引就顶上去变成聚集索引了，而新主键只是一个约束和二级索引，依旧不满足第一个 主键/唯一索引的定义；
对此，也没有什么特别好的办法，只有继续删除唯一索引，直到可以为止（直到只剩下主键索引而无任何唯一索引），然后再重建唯一索引
1、在5.7版本之前，对于 data 和 datatime 类型的列，要实现分区，只能使用 year() 和 to_days 函数，在5.7及之后，新增了 to_seconds() 函数
2、timestamp 类型的列，只能基于 unix_timestamp 函数分区
该部分到文末转自：转自：https://blog.csdn.net/dayi_123/article/details/84242656
1、对表分区的原因
数据库数据越来越大，导致单个表中数据太多。以至于增删改查询速度变慢，而且由于表的锁机制导致应用操作也受到严重影响，出现了数据库性能瓶颈。
2、表分区
表分区就是将一个表的数据按照一定的规则水平划分为不同的逻辑块，并分别进行物理存储，这个规则就叫做分区函数，可以有不同的分区规则，通过”show plugins”语句可以查看当前MySQL是否支持表分区功能，mysql5.7社区版默认开启了表分区功能。
3、进行表分区的优势
（1）可以允许在一个表里存储更多的数据，突破磁盘限制或者文件系统限制
（2）对于从表里将过期或历史的数据移除在表分区很容易实现，只要将对应的分区移除即可
（3）对某些查询和修改语句来说，可以自动将数据范围缩小到一个或几个表分区上，优化语句执行效率。而且可以通过显示指定表分区来执行语句
4、表分区的类型
（1）RANGE表分区：范围表分区，按照一定的范围值来确定每个分区包含的数据
（2）LIST表分区：列表表分区，按照一个确定的值来确定每个分区包含的数据
（3）HASH表分区：哈希表分区，按照一个自定义的函数返回值来确定每个分区包含的数据
（4）KEY表分区 ：key表分区，与哈希表分区类似，只是用MySQL自己的HASH函数来确定每个分区包含的数据
【2】各种类型表分区的使用
2.0 进行表分区时需要注意的事项
当表中含有主键或唯一键时，每个被用作分区函数的字段必须是表中唯一键和主键的全部或一部分，否则就无法创建分区表。
有主键时必须用主键，无主键时可以用唯一键
# 由于唯一键和主键没有相同的字段，所以无法创建表分区
mysql> create table pattern_test01( -> id int primary key, -> name varchar(12) unique, -> age int) partition by range (id) -> (partition p0 values less than(10), -> partition p1 values less than(20)); ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
# 不使用唯一键可创建成功
mysql> create table pattern_test02( -> id int primary key, -> name varchar(12), -> age int) partition by range (id) -> (partition p0 values less than(10), -> partition p1 values less than(20)); Query OK, 0 rows affected (0.16 sec)
# 同时将主键设置为唯一键
mysql> create table pattern_test03( -> id int,name varchar(12),age int, -> primary key (id,name), -> unique key (id)) partition by range (id) -> (partition p0 values less than(10), -> partition p1 values less than(20)); Query OK, 0 rows affected (0.14 sec)
2.1、RANGE表分区的使用
RANG表分区(范围表分区)，按照一定的范围值来确定每个分区包含的数据，分区函数使用的字段必须只能是整数类型，分区的定义范围必须是连续的，且不能有重叠部分，通过使用VALUES LESS THAN来定义分区范围，表分区的范围定义是从小到大定义的。
mysql> create table range_test(id int not null, name1 varchar(30),name2 varchar(30), hired date not null default '1970-01-01', separated date not null default '2999-12-31', job_code int not null, store_id int not null)
partition by range (store_id) (partition p0 values less than(6),partition p1 values less than(12), partition p2 values less than(18),partition p3 values less than(24));
# 插入数据
mysql> insert into range_test values(1,'dayi','dayi123',now(),now(),2,6),(2,'dy','liu',now(),now(),5,10);
# 查询时可以指定具体的分区表查询
mysql> select * from range_test partition (p1); +----+-------+---------+------------+------------+----------+----------+ | id | name1 | name2 | hired | separated | job_code | store_id | +----+-------+---------+------------+------------+----------+----------+ | 1 | dayi | dayi123 | 2018-09-30 | 2018-09-30 | 2 | 6 | | 2 | dy | liu | 2018-09-30 | 2018-09-30 | 5 | 10 | +----+-------+---------+------------+------------+----------+----------+
# 由于分区表p0中没有数据所以查不到
mysql> select * from range_test partition (p0); Empty set (0.00 sec)
# 查看数据文件时每个分区表都会生成一个独立的数据文件
]# ls /data/mysql/data/test/range_test* range_test.frm
range_test#P#p0.ibd
range_test#P#p1.ibd
range_test#P#p2.ibd
range_test#P#p3.ibd
当插入的数据的分区字段的值不在分区表指定的范围时会报错。这是需要修改下表的定义，可以使用MAXVALUE关键词表示可能的最大值。
对timestamp字段类型可以使用的表达式目前仅有unix_timestamp（作用是将时间转化为时间戳），其他的表达式都不允许。对于date及datetime类型的数据可以将”year”提取出来。
# 依据timestamp类型字段表分区时将该字段转化成时间戳
mysql> CREATE TABLE range_test02 ( id INT NOT NULL,name VARCHAR(20) NOT NULL,update_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )PARTITION BY RANGE ( UNIX_TIMESTAMP(update_date) ) ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00') ), PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2018-01-01 00:00:00') ), PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-01-01 00:00:00') ), PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2020-10-01 00:00:00') ), PARTITION p9 VALUES LESS THAN (MAXVALUE) );
mysql> insert into range_test02 values(1,'dayi123',now()),(2,'testname','2030-01-02 12:00:00'); Query OK, 2 rows affected (2.34 sec)
# 使用date类型的数据表分区时可将”year”提取出来利用”year”进行表分区
mysql> create table range_test04(tstamp date) partition by range(year(tstamp))( partition p0 values less than(2018));
2.2、list表分区的使用
使用list表分区时，PARTITION BY LIST(expr)分区函数表达式必须返回整数，取值范围通过VALUES IN (value_list)定义。
# 创建基于list表分区的表
mysql> create table list_test01(id int not null, name varchar(30),hired date not null default '1970-01-01', separated date not null default '9999-12-31', job_code int,store_id int) partition by list(store_id) (partition p_north values in (1,4,7), partition p_east values in (2,5,8), partition p_west values in (3,6,9));
基于list表分区的表创建注意项：
（1）对List表分区来说，没有MAXVALUE特殊值，所有的可能取值都需要再VALUES IN中包含，如果有定义的取值则会报错.
（2）当有主键或者唯一键存在的情况下，分区函数字段需要包含在主键或唯一键中。
2.3、hash表分区的使用
哈希表分区是按照一个自定义的函数返回值来确定每个分区包含的数据，这个自定义函数也可以仅仅是一个字段名字
通过PARTITION BY HASH (expr)子句来表达哈希表分区，其中的expr表达式必须返回一个整数，基于分区个数的取模（%）运算。
根据余数插入到指定的分区，对哈希表分区来说只需要通过” PARTITIONS”定义分区的个数，其他的事情由内部完成；如果没有写明PARTITIONS字段，则默认为1。
# 创建基于hash表分区的表
mysql> create table hash_test01(id int not null,name varchar(30), hired date not null default '1970-01-01', separated date not null default '2999-12-31', job_code int,store_id INT) partition by hash(store_id) partitions 5;
# 查看创建的hash表分区的分区表
mysql> select table_name,partition_name from information_schema.partitions where table_name='hash_test01';
+-------------+----------------+
| table_name | partition_name |
+-------------+----------------+
| hash_test01 | p0 |
| hash_test01 | p1 |
| hash_test01 | p2 |
| hash_test01 | p3 |
| hash_test01 | p4 |
+-------------+----------------+
2.4、key表分区的使用
key表分区与哈希表分区类似，只不过哈希表分区依赖于自定义的函数，而key表分区的哈希算法是依赖MySQL本身。
Key表分区通过CREATE TABLE ... PARTITION BY KEY ()语句创建，括号里面可以包含0个或者多个字段，所引用的字段必须是主键或者主键的一部分;
如果括号里面没有字段，则代表使用主键，如果表中没有主键但有唯一键，则使用唯一键，但唯一键字段必须定义为not null，否则报错。Key表分区所引用的字段未必是整数类型，其他的类型也可以使用。
# 没有指定字段，则使用主键
mysql> CREATE TABLE key_test01 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) ) PARTITION BY KEY() PARTITIONS 2;
# 指定字符串作为key表分区的字段
mysql> create table key_test02(id int,tname char(30) primary key) partition by key(tname) partitions 10;
2.5、创建子表分区
子表分区，是在表分区的基础上再创建表分区的概念，每个表分区下的子表分区个数必须一致，在MySQL5.7版本中，子表分区必须是范围/列表分区+哈希/key子表分区的组合。
# 创建字表分区
mysql> create table test_zb01(id int,purchased date) partition by range(year(purchased)) subpartition by hash(to_days(purchased)) subpartitions 2 (partition p0 values less than(2000), partition p1 values less than(2020), partition p2 values less than maxvalue);
# 查看字表的表名
mysql> select table_name,partition_name,SUBPARTITION_NAME from information_schema.partitions where table_name='test_zb01';
+------------+----------------+-------------------+
| table_name | partition_name | SUBPARTITION_NAME |
+------------+----------------+-------------------+
| test_zb01 | p0 | p0sp0 |
| test_zb01 | p0 | p0sp1 |
| test_zb01 | p1 | p1sp0 |
| test_zb01 | p1 | p1sp1 |
| test_zb01 | p2 | p2sp0 |
| test_zb01 | p2 | p2sp1 |
+------------+----------------+-------------------+
字表在不指定表名时，表名时系统默认分配的。子表分区也可以显示的指定子表分区的名字。
# 创建字表时指定子分区表名
mysql> create table test_zb02(id int,purchased date) partition by range(year(purchased)) subpartition by hash(to_days(purchased)) subpartitions 2 (partition p0 values less than(2000) (subpartition s1,subpartition s2), partition p1 values less than(2020) (subpartition s3,subpartition s4), partition p2 values less than maxvalue (subpartition s5,subpartition s6));
【3】表分区的管理
表分区创建完成后可以通过alter table命令可以执行增加，删除，重新定义，合并或者拆分表分区的管理动作。对不同类型的表分区管理方法也有不同。
3.1、表分区的删除，表分区数据的删除
（1）表分区删除：
对范围表分区和列表表分区来说，删除一个表分区时使用命令
” ALTER TABLE table_name DROP PARTITION partition_name”删除；
多个分区用逗号分隔：alter table tab drop partition p1,p2;
删除表分区的动作不光会把分区删掉，也会把表分区里原来的数据给删除掉。
删除所有分区：alter table tea_prop_rich_log remove partitioning; （不会删除数据）
# 查看创建表的语句
mysql> show create table range_test; +------------+----------------------------------------------------+ |Tabl|CreateTable +------------+-----------------------------------------+ | range_test | CREATE TABLE `range_test` ( `id` int(11) NOT NULL, `name1` varchar(30) DEFAULT NULL, `name2` varchar(30) DEFAULT NULL, `hired` date NOT NULL DEFAULT '1970-01-01', `separated` date NOT NULL DEFAULT '2999-12-31', `job_code` int(11) NOT NULL, `store_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (12) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (18) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (24) ENGINE = InnoDB) */ | +------------+----------------------------------------------------------+
# 删除分区表p3
mysql> alter table range_test drop partition p3;
（2）表分区数据的删除：
要从 partition 删除所有行p0，请使用以下语句：
ALTER TABLE t1 TRUNCATE PARTITION p0;
3.2、增加表分区
（1）范围表分区(range)上增加及重新组织表分区
在原分区上增加一个表分区可以通过alter table … add partition语句来完成。
对范围表分区来说，增加的表分区必须在尾部增加，在头部或者在中间增加都会失败，对此可以通过REORGANIZ增加表分区及重组表分区。
# 增加表分区 mysql> alter table range_test add partition (partition p3 values less than maxvalue);
# 使用REORGANIZ命令创建表分区，相当于将p0分成两个表分区
mysql> alter table range_test REORGANIZE PARTITION p0 into (partition n0 values less than (3),partition n1 values less than (6));
#使用REORGANIZ命令也可将拆分的表分区合并
mysql> alter table range_test REORGANIZE PARTITION n0,n1 into (partition p0 values less than(6));
（2）列表表分区（list）上增加及重新组织表分区
对列表表分区来说，只要新增加的分区对应的值在之前的表分区中没有出现过，就可以通过alter table… add partition来增加。也可以通过REORGANIZE命令将之前的多个分区合并成一个或几个分区，但要保持分区值一致；对列表分区重组时，重新组织的分区必须是相邻的分区。
# 增加表分区 mysql> alter table list_test01 add partition (partition pCentral values in(10,11,12));
# 重新组织表分区 mysql>
alter table list_test01
REORGANIZE partition p_north,p_east,p_west
into (
partition p0 values in (1,2,3),
partition p1 values in (4,5,6)
,partition p2 values in (7,8,9)
);
如果表里已有的数据在新重组的分区中没有指定的值，则数据会丢失
3.3、哈希表分区和key表分区的管理
对哈希表分区和KEY表分区的管理手段与范围和列表表分区完全不同，比如不能删除表分区；
但可以通过ALTER TABLE ... COALESCE PARTITION语句合并表分区，其partition后面的数字代表缩减的个数，而不是缩减到的个数；如果是增加表分区，则可以使用add partition语句。
# 将表hash_test01的表分区缩减到三个 mysql> alter table hash_test01 COALESCE PARTITION 3;
# 对表has_test01再增加10个表分区 mysql> alter table hash_test01 ADD PARTITION PARTITIONS 10;
3.4、分解与合并分区
分解分区：
Reorganizepartition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。分解前后分区的整体范围应该一致。
alter table te reorganize partition p1 into ( partition p1 values less than (100), partition p3 values less than (1000) ); ----不会丢失数据
合并分区:
Merge分区：把2个分区合并为一个。
alter table te reorganize partition p1,p3 into (partition p1 values less than (1000));
----不会丢失数据
3.5、重新定义 hash和range 分区表
#重新定义hash分区表: Alter table emp partition by hash(salary) partitions 7; ----不会丢失数据 #重新定义range分区表: Alter table emp partition by range(salary) ( partition p1 values less than (2000), partition p2 values less than (4000) ); ----不会丢失数据
3.6、重建/优化/分析/修补/检查 分区
重建分区：
这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。
ALTER TABLE emp rebuild partitionp1,p2;
优化分区：
如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。
ALTER TABLE emp optimize partition p1,p2;
分析分区：
读取并保存分区的键分布。
ALTER TABLE emp analyze partition p1,p2;
修补分区：
修补被破坏的分区。
ALTER TABLE emp repairpartition p1,p2;
检查分区：
可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。
ALTER TABLE emp CHECK partition p1,p2;
这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。
3.7、查询指定分区的数据
select count(1) from t1 partition(p9);
【4】表分区的其他管理操作
4.1、创建range和list表分区时，分区函数可以包含多个字段
分区多字段函数所涉及的字段类型可以包括 TINYINT、SMALLINT、MEDIUMINT、INT (INTEGER)、 BIGINT、DATE、DATETIME、CHAR、VARCHAR、BINARY、 VARBINARY。
# 创建多字段表分区的rang分区表
mysql> create table columns_test(id int not null,name varchar(30), tname varchar(30),store_id int) partition by range columns(id,tname,store_id) (partition p0 values less than (100,'dayi123',3), partition p1 values less than (200,'dy',6), partition p2 values less than (MAXVALUE,MAXVALUE,MAXVALUE));
4.2、对null值得处理
（1）在范围表分区中，如果插入的是NULL值，则将数据放到最小的分区表里
（2）在list表分区中，支持NULL值的唯一情况就是某个分区的允许值中包含NULL
（3）对哈希表分区和Key表分区来说，NULL值会被当成0值对待
4.3、表分区数据的交换
对分区表可以通过 ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt 命令将一个分区或者是子分区的数据与普通的表的数据相互交换，其本身的表结构不会变化；
交换的分区表和目标表必须结构完全相同，包括字段，类型，索引，存储引擎必须完全一样。
# 创建用于数据交换分区的表 mysql> create table ext_test01 like range_test;
# 数据交换 mysql> alter table ext_test01 remove partitioning; mysql> alter table range_test exchange partition p1 with table ext_test01;
# 查看数据交换后各表的数据 mysql> select * from ext_test01; +----+-------+---------+------------+------------+----------+----------+ | id | name1 | name2 | hired | separated | job_code | store_id | +----+-------+---------+------------+------------+----------+----------+ | 1 | dayi | dayi123 | 2018-09-30 | 2018-09-30 | 2 | 6 | | 2 | dy | liu | 2018-09-30 | 2018-09-30 | 5 | 10 | +----+-------+---------+------------+------------+----------+----------+ mysql> select * from range_test partition (p1); Empty set (0.00 sec)
4.4、表分区的其他管理命令
（1）使用rebuild命令除去分区碎片 mysql> alter table range_test rebuild partition p0,p1; （2）OPTIMIZE命令可以回收分区中未使用的空间和重新获取统计资料 mysql> alter table range_test optimize partition p0,p1,p2; （3）Analyzing partitions命令用于重新收集分区统计资料 mysql> alter table range_test analyze partition p3; （4）Repairing partitions命令用于修复异常的分区 mysql> alter table range_test repair partition p0,p1; （5）Checking partitions命令检查分区中数据或者索引数据是否损坏 mysql> alter table range_test check partition p0; （6）ALTER TABLE ... TRUNCATE PARTITION命令用来删除分区中的所有数据 mysql> alter table range_test truncate partition p3;
4.5、表分区的修剪和选择
表分区修剪是MySQL优化的一种，其核心就是只扫描需要的分区。
表分区选择和表分区修剪类似，只不过修剪是自动实现的，而表分区选择是现实的指定分区范围。
【pt实现】
参考：https://www.cnblogs.com/gered/p/15272660.html#_label4
（0）忽略一些检查
很多时候老是要我检查 很烦的；
#（1）添加唯一索引时，忽略检查被添加列中数据是否值是唯一： --nocheck-unique-key-change #（2）删除主键时，忽略检查是否有其他唯一索引级键（唯一key是为了保证delete触发器可以删掉对应行）
--no-check-alter
#（3）当有复制过滤时，pt-online-schema-change会不让使用，可以选择忽略
--no-check-replication-filters
（1）修改主键
-- (1 )tea_prop_rich_log: 原主键（logid） 修改主键为(logid,logtime) -- 分区列： logtime (datetime) pt-online-schema-change --host="localhost" --port='3306' --user="root" --password="xx" \ D="test1",t="tea_prop_rich_log" --alter="drop primary key,add primary key (logid,logtime)" --no-check-alter --nocheck-unique-key-change --execute
（2）添加索引
nohup pt-online-schema-change --host="localhost" --port='3306' --user="root" --password="xx" \ D="weixin_agent",t="agent_money_stock_hourly" --alter="add unique index uix_bid(bid)" --no-check-alter --nocheck-unique-key-change --execute &
（3）现有表修改为分区表
pt-online-schema-change --host="localhost" --port='3306' --user="root" --password="xx" \
D="test1",t="tea_prop_rich_log" --alter="partition by range(to_days(logtime)) ( \
partition p202102 values less than (to_days('2021-03-01') ), \
partition p202103 values less than (to_days('2021-04-01') ), \
partition p202104 values less than (to_days('2021-05-01') ), \
partition p202105 values less than (to_days('2021-06-01') ), \
partition p202106 values less than (to_days('2021-07-01') ), \
partition p202107 values less than (to_days('2021-08-01') ), \
partition p202108 values less than (to_days('2021-09-01') ), \
partition p202109 values less than (to_days('2021-10-01') ), \
partition pmax values less than (maxvalue) \
);" --nocheck-unique-key-change --execute
【最佳实践】
（0）range 分区的时间分区与时间戳分区
按月分区：
得出来的值，也可以 from_days() 函数查看其 日期值是多少
-- 构建唯一键，以便可以使用online ddl
create index UIX_logid on tea_prop_rich_log(logid);
-- 用 pt-osc 执行，修改主键 alter table tea_prop_rich_log drop primary key,add primary key(logid,logtime);
-- 用 pt-osc 执行，构建分区表 alter table tea_prop_rich_log partition by range(to_days(logtime)) ( partition p202102 values less than (to_days('2021-03-01') ), partition p202103 values less than (to_days('2021-04-01') ), partition p202104 values less than (to_days('2021-05-01') ), partition p202105 values less than (to_days('2021-06-01') ), partition p202106 values less than (to_days('2021-07-01') ), partition p202107 values less than (to_days('2021-08-01') ), partition p202108 values less than (to_days('2021-09-01') ), partition p202109 values less than (to_days('2021-10-01') ), partition pmax values less than (maxvalue) );
时间戳字段，按月分区：
得出来的值，也可以 from_unixtime() 函数查看其 日期值是多少
CREATE TABLE range_test02 ( id INT NOT NULL,name VARCHAR(20) NOT NULL,update_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )
PARTITION BY RANGE ( UNIX_TIMESTAMP(update_date) ) ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00') ), PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-02-01 00:00:00') ), PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-03-01 00:00:00') ), PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-04-01 00:00:00') ), PARTITION p9 VALUES LESS THAN (MAXVALUE) )
mysql自带的 online ddl 对其支持，对目标表 DML 的影响：
mysql8.0：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-partitioning
简述：增加删除分区 TRUNCATE PARTITION 分区数据，不影响不阻塞该表的DML
mysql5.7：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html#online-ddl-partitioning
5.7：均阻塞DML，需要用PT 等online ddl 操作来不阻塞执行
（1）自动化添加删除分区 8.0
用的8.0版本 直接用的自带online ddl；
8.0以下官网说操作期间不允许DML，但我们线上在用，或者可以很快操作完？闲时也不太影响？
#!/bin/bash log_file=/data/dba/partition_auto.log if [ ! -d `dirname ${log_file}` ];then mkdir -p `dirname ${log_file}` fi exec 1>>${log_file} exec 2>>${log_file} source /etc/profile del_partition=$(date --date='7 month ago' +%Y%m) new_partition=$(date --date='1 month' +%Y%m) next_month=$(date --date='2 month' +%Y%m) db_name=test1 exec_sql=`mysql -pbfgame20 -N -e" select concat('alter table ',table_name,' drop partition p${del_partition};' ,'alter table ',table_name,' reorganize partition pmax into (partition p${new_partition} values less than (to_days(''${next_month}01'')) ,partition pmax values less than (maxvalue) );' ) from information_schema.tables where table_schema='${db_name}' and table_name in ('tea_prop_rich_log','tea_house_bills','round_bills','agent_money_stock_hourly','agent_cost_game_cnt','agent_cost'); "` echo -e "--------------------------------------------\n' begin exec sql:`date +%F-%T`\n${exec_sql}\n -----------------------------" #exec sql mysql -uroot -pbfgame20 -D"${db_name}" -e"${exec_sql}"
二次参考：
#!/bin/bash source /etc/profile exec 1>> /soft/sp_alter_log.log exec 2>&1 set -e NOW_MONTHS=$(date --date='2 month' +%Y-%m) PARTNAME=$(date --date='3 month ago' +%m) NOWNAME=$(date --date='1 month' +%m) mysql -pbfgame20 -Bse "select tablename from test.Ptablelist" >/root/soft/table1.txt FILE1=/root/soft/table1.txt logfile=/root/soft/alter.log while read line do table_name=`echo $line | awk '{print $1}'` echo $table_name echo "Start running SQL script for DB "$table_name" at "$(date +%Y-%m-%d%t%A%t%T) >> $logfile mysql -pbfgame20 <<EOF use log_db; alter table log_db.$table_name drop partition p$PARTNAME,p99; alter table log_db.$table_name add partition(partition p$NOWNAME VALUES LESS THAN (to_days('${NOW_MONTHS}-01')) ENGINE = InnoDB); alter table log_db.$table_name add partition(partition p99 VALUES LESS THAN MAXVALUE ENGINE = InnoDB); EOF done<$FILE1
（2）根据 datetime，timestamp 类型区分自动化分区
注意修改，我这里的 p05 其实是 <to_days('2022-05-01') 的，也就是4月的数据
至于文中的 test.partition_tableslist(table_schema,table_name,datatype) ，可以根据 information_schema.tables columns partitions 等系统表获取
#!/bin/bash source /etc/profile dir_cur=`dirname $0` exec 1>> ${dir_cur}/auto_partition.log exec 2>&1 set -e echo "----------------------------------------------------------------" NOW_MONTHS=$(date --date='2 month' +%Y-%m) PARTNAME=$(date --date='3 month ago' +%m) NOWNAME=$(date --date='2 month' +%m) FILE1=${dir_cur}/tmp_partition_tablelist.txt mysql -N -Bse "select * from test.partition_tablelist" >${FILE1} while read line do table_schema=`echo $line | awk '{print $1}'` table_name=`echo $line | awk '{print $2}'` column_datatype=`echo $line | awk '{print $3}'` echo "@@@@@ Start running SQL script for DB ${table_schema}.${table_name} at $(date +%Y-%m-%d%t%A%t%T) @@@@@@@" if [ $column_datatype = 'datetime' ]; then mysql <<EOF use ${table_schema}; alter table ${table_schema}.$table_name drop partition p$PARTNAME,p99; alter table ${table_schema}.$table_name add partition(partition p$NOWNAME VALUES LESS THAN (to_days('${NOW_MONTHS}-01')) ENGINE = InnoDB); alter table ${table_schema}.$table_name add partition(partition p99 VALUES LESS THAN MAXVALUE ENGINE = InnoDB); EOF elif [ $column_datatype = 'timestamp' ]; then mysql <<EOF use ${table_schema}; alter table ${table_schema}.$table_name drop partition p$PARTNAME,p99; alter table ${table_schema}.$table_name add partition(partition p$NOWNAME VALUES LESS THAN (unix_timestamp('${NOW_MONTHS}-01')) ENGINE = InnoDB); alter table ${table_schema}.$table_name add partition(partition p99 VALUES LESS THAN MAXVALUE ENGINE = InnoDB); EOF fi done<$FILE1
【故障处理】
（1）ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
alter table jygoldlog.log_event partition by range(to_days(logtime)) ( -> partition p01 values less than (to_days('2022-01-01') ), -> partition p02 values less than (to_days('2022-02-01') ), -> partition p03 values less than (to_days('2022-03-01') ), -> partition p04 values less than (to_days('2022-04-01') ), -> partition p05 values less than (to_days('2022-05-01') ),partition p99 values less than (maxvalue)); ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
原因，我的 logtime 是 timestamp 类型字段
解决：使用 unix_timestamp 把数据转成时间戳，以此分区
alter table jygoldlog.log_event partition by range(UNIX_TIMESTAMP(logtime)) ( partition p01 values less than (UNIX_TIMESTAMP('2022-01-01') ), partition p02 values less than (UNIX_TIMESTAMP('2022-02-01') ), partition p03 values less than (UNIX_TIMESTAMP('2022-03-01') ), partition p04 values less than (UNIX_TIMESTAMP('2022-04-01') ), partition p05 values less than (UNIX_TIMESTAMP('2022-05-01') ),partition p99 values less than (maxvalue)); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0
（2）ERROR 1503 (HY000) at line 23: A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
0、分区列必须包含在第一个 主键/唯一索引中
0.1、比如主键是 logid，分区列是 logtime ，那么得 alter table drop primary key,add primary key (logid,logtime) ，把logtime 加入主键后，才能作为分区列
但注意，如果主键相关值 经常被更新，那么容易死锁
这个报错就是：
如果现表有一个主键索引，有一个唯一索引，那么执行 0.1后，并没有用，删除主键后默认的唯一索引就顶上去变成聚集索引了，而新主键只是一个约束和二级索引，依旧不满足第一个 主键/唯一索引的定义；
所以报错，一个 unique index must include all columns in table's partitioning function（唯一索引必须包括表的分区函数中的所有列（不考虑带前缀的列））
对此，也没有什么特别好的办法，只有继续删除唯一索引，直到可以为止（直到只身下主键索引而无任何唯一索引），然后再重建唯一索引
【参考文档】
https://www.cnblogs.com/pejsidney/p/10074980.html
https://blog.csdn.net/dayi_123/article/details/84242656
