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

发表于 | 分类于 |
学习mysql水平分区和实践笔记

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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';
1
2
[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:

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

主键需要包含分区的字段

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

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

删除主键失败

依次执行下列的sql

1
2
3
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