前言

如同前一篇提到，假设今天接到一个DB有一张表裏面资料量蛮大的，每天大概有20万笔，且这张表要保留至少半年的资料，平常搜寻大多是以一天为条件，所以我们希望搜寻的时候可以只用到某个 partition 区块就好。所以就打算在既有的 TABLE 上建立 partition。

流程

先建立测试资料

从2020-01-01 ~ 2020-04-01 每天塞3笔资料进去PK: uid 且有自增长

-- 建立表格drop table if exists tb1;CREATE TABLE tb1 (    uid int NOT NULL AUTO_INCREMENT,    uname varchar(50),    create_time timestamp not null default current_timestamp,    PRIMARY KEY(uid),    KEY (uname));-- 塞入2020-01-01 到 2020-04-01  每天3笔资料CREATE TEMPORARY TABLE IF NOT EXISTS `name_list` (    `name` varchar(10) NOT NULL) ENGINE = InnoDB;INSERT INTO `name_list` VALUES ('test1'),('test2'),('test3');INSERT INTO tb1(uname,create_time)WITH RECURSIVEcte  AS (      SELECT "2020-01-01 05:00:00" as `expiration`      UNION ALL      SELECT          date_add(`expiration`, INTERVAL 1 DAY) as `expiration`      FROM cte      WHERE `expiration` < "2020-04-01 05:00:00"    ),cte1AS    (    SELECT * FROM cte    CROSS JOIN `name_list`    )SELECT         `name`    ,`expiration`FROM cte1

★ 假设我觉得这张表很大，想要在这张表切 partition ，且以日来切，步骤如下

因为切 partition ，切的条件必须放在 PK中，这里我们想利用 create_time 这个栏位来切，所以PK就要修改。所以第一步要先 drop PK

ALTER TABLE tb1 DROP PRIMARY KEY;

上面这个步骤执行就发现错误
1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
所以我们需要先把自增长拿掉

ALTER TABLE tb1 modify column uid INT NOT NULL; 

接着就可以 drop PK重新建立 PK，并把要切 partition 的栏位放进去

ALTER TABLE tb1 ADD PRIMARY KEY(`uid`,`create_time`);

把原本的自增长栏位加回去

ALTER TABLE tb1 MODIFY uid INT  NOT NULL AUTO_INCREMENT;  

这里因文章範围有限，我只切从2020-03-23 ~ 2020-04-02

ALTER TABLE tb1 partition by range(unix_timestamp(create_time))( partition ym_200322 values less than (unix_timestamp('2020-03-23 00:00:00')),  partition ym_200323 values less than (unix_timestamp('2020-03-24 00:00:00')),  partition ym_200324 values less than (unix_timestamp('2020-03-25 00:00:00')),  partition ym_200325 values less than (unix_timestamp('2020-03-26 00:00:00')),  partition ym_200326 values less than (unix_timestamp('2020-03-27 00:00:00')),  partition ym_200327 values less than (unix_timestamp('2020-03-28 00:00:00')),  partition ym_200328 values less than (unix_timestamp('2020-03-29 00:00:00')),  partition ym_200329 values less than (unix_timestamp('2020-03-30 00:00:00')),  partition ym_200330 values less than (unix_timestamp('2020-03-31 00:00:00')),  partition ym_200331 values less than (unix_timestamp('2020-04-01 00:00:00')), partition ym_200401 values less than (unix_timestamp('2020-04-02 00:00:00')));  

检查是否有进到 partition：利用下面的语法可以观察每个 partition 里面的数量如下图1

select table_schema, table_name, partition_name,partition_ordinal_position,partition_method, partition_description, table_rowsfrom information_schema.partitionswhere table_schema = 'test' and table_name = 'tb1'

从下图可以看到

因为我只从2020-03-23 开始有切 partition ，所以小于此日期的都会在这里。后面就是每天有3笔资料。

图1

小结

以上为如何在原本既有的 TABLE 上建立 partition。几个重点整理一下

由于要拿来切 partition 的栏位必须在PK中，所以PK势必得重建如果要 drop PK 就要先把自增长栏位先拿掉可以透过系统表来观察资料是否有写入到对应的 partition，如图1，藉此检查是否有符合自己的期望

下一篇再分享关于切的 partition 数量跟效能有什么关係，可以无限制的切下去吗?

资料库知识相当广泛，文中若有不正确的地方，也烦请各位大神不吝指教，谢谢