学习mysql水平分区和实践笔记

SHOW PLUGINS; sql 可以查看partition的Status 是否是ACTIVE的

使用mydatetime 进行水平分区案例:

CREATE TABLE test_users (
	`id` INT (10) NOT NULL AUTO_INCREMENT,
	`mydatetime` datetime NOT NULL,
	`email` VARCHAR (255) NOT NULL,
	UNIQUE INDEX (`email`),
	PRIMARY KEY (`id`)
);

-- 如果表已创建时的操作

-- 修改主键的类型
ALTER TABLE test_users CHANGE COLUMN `id` `id` INT (10) UNSIGNED NOT NULL;
-- 删除主键
ALTER TABLE test_users DROP PRIMARY KEY;
-- 将 主键id和分区字段mydatetime 同作为主键
ALTER TABLE test_users ADD PRIMARY KEY (id, mydatetime);
-- 给主机 id 加上 自动增长
ALTER TABLE test_users CHANGE COLUMN `id` `id` INT (10) UNSIGNED NOT NULL AUTO_INCREMENT;
-- 删除索引 email
ALTER TABLE test_users DROP INDEX email;
-- 将 email和mydatetime字段 同作为名为 email的唯一索引
ALTER TABLE test_users ADD UNIQUE KEY `email` (email, mydatetime);
-- 根据 mydatetime的日期值，将小于 2018-12-31放在 p1,小于2019-06-30放在p2,小于最大值的放在p8分区,名字是自己定义的
alter table test_users PARTITION by range(TO_DAYS(mydatetime))(
	PARTITION p1 VALUES LESS THAN (TO_DAYS('2018-12-31')),
	PARTITION p2 VALUES LESS THAN (TO_DAYS('2019-06-30')),
	PARTITION p8 VALUES LESS THAN MAXVALUE 
);
-- 分析查询语句，测试分区是否有用
EXPLAIN SELECT * FROM test_users WHERE mydatetime <= '2019-07-01';

---

[Err]1503 - A UNIQUE INDEX must include all columns in the table's partitioning function
[Err] 1486 - Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

尝试1:

[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function

主键需要包含分区的字段

尝试了将原来的主键删除掉，然后再重新创建一个组合主键

[Err]1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

删除主键失败

依次执行下列的sql

ALTER TABLE test_table CHANGE COLUMN `id` `id` int(10) unsigned NOT NULL ;
ALTER TABLE test_table DROP PRIMARY KEY; #删除主键
ALTER TABLE test_table add PRIMARY KEY(id); #添加主键

References

1. 故障案例--mysql5.5分区表的一个坑
2. mysql 自动分区实践
3. MySQL 分区表探索
4. MySQL 表分区 A PRIMARY KEY must include all columns in the table's partitioning function
5. A primary must include all columns in the table's partitioning location error? 需要和主键，分区的字段作为一个unique keyi进行处理
6. [MySQL] AUTO_INCREMENTカラム変更がめんどくさい・・ mysql [err] 1075
7. MySQL添加/删除主键、外键、唯一键、索引、自增
8. 错误代码：1503 A UNIQUE INDEX must include all columns in the table's partitioning function