mysql 分区表新增字段方案由于分区表数据量太大，接近1亿条

2024-07-16 18:01| 来源: 网络整理| 查看: 265

背景：在工作中遇到MySQL分区表需要新增多个字段的情况，尝试用一下语法，但是半个小时过去了仍然没有反应

alter table test_20220115 add column clk_pv bigint(20) comment '次数' AFTER icon_clk_dev_cnt_1d;

找了专业的DBA问了一下，给出一下解决方案

1、mysql创建一样的表结构 CREATE TABLE test_20220115 AS SELECT * FROM ads_d_cpcdw_innereye_gsearch_log_tab_1d WHERE 1=0; 2、mysql创建一样的表结构并且复制全部数据， CREATE TABLE test_20220115 AS SELECT * FROM tab_1d 3、考虑到数据量比较到且有一年的数据，分区看看插入一天的数据看看需要的时间 insert into 新表 select * from 旧表 where dayno=‘2022-01-15’ 查询时间如下，1天数据条数：633987 Query OK, 633987 rows affected (15.91 sec) Records: 633987 Duplicates: 0 Warnings: 0

测试后发现这样子确实能够实验快速增加表字段并实现数据的恢复，但是建表的数据的分区变了

原表： CREATE TABLE tab_1d( `old_clk_pv` bigint(20) DEFAULT NULL COMMENT '曝光数', `dayno` date NOT NULL COMMENT '日期', `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `auto_inc_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增长id', PRIMARY KEY (`auto_inc_id`,`dayno`), KEY `idx_dayno_grouping_type_enter_id_page_id_card_id_tab_name` (`dayno`,`grouping_type`,`enter_id`,`page_id`,`card_id`,`tab_name`) ) ENGINE=InnoDB AUTO_INCREMENT=114004994 DEFAULT CHARSET=utf8mb4 COMMENT='数据汇总' /*!50500 PARTITION BY RANGE COLUMNS(dayno) (PARTITION p20210227 VALUES LESS THAN ('2021-02-28') ENGINE = InnoDB, PARTITION p20210228 VALUES LESS THAN ('2021-03-01') ENGINE = InnoDB, PARTITION p20210301 VALUES LESS THAN ('2021-03-02') ENGINE = InnoDB, PARTITION p20210302 VALUES LESS THAN ('2021-03-03') ENGINE = InnoDB, 新表： CREATE TABLE test_20220115 ( `clk_pv` bigint(20) DEFAULT NULL COMMENT '点击数', `old_clk_pv` bigint(20) DEFAULT NULL COMMENT '曝光数', `dayno` date NOT NULL COMMENT '日期', `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `auto_inc_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '自增长id' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

可能是分区表的建表语句不能这么用

1、mysql分区表建表语句不能这么用户 CREATE TABLE test_20220115 AS SELECT * FROM ads_d_cpcdw_innereye_gsearch_log_tab_1d WHERE 1=0; 2、使用mysqltee命令获取旧表的建表语句 mysql>tee /home/a.txt mysql>select * from 旧表; mysql>exit 3、复制一模一样的建表语句 CREATE TABLE test_20220115 ( enter_id varchar(100) DEFAULT NULL COMMENT '入口', app_version varchar(100) DEFAULT NULL COMMENT '版本', model_sale varchar(100) DEFAULT NULL COMMENT '机型', clk_pv bigint(20) DEFAULT NULL COMMENT '点击数', res_clk_cnt_1d bigint(20) DEFAULT NULL COMMENT '有点pv', dayno date NOT NULL COMMENT '日期', gmt_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', gmt_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', auto_inc_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增长id', PRIMARY KEY (auto_inc_id,dayno), KEY idx1 (dayno,enter_id,grouping_type,page_id,card_id), KEY idx2 (dayno,enter_id,grouping_type,app_version) ) ENGINE=InnoDB AUTO_INCREMENT=102570243 DEFAULT CHARSET=utf8mb4 COMMENT='数据汇总' PARTITION BY RANGE COLUMNS(dayno) (PARTITION p20210610 VALUES LESS THAN ('2021-06-11') ENGINE = InnoDB, PARTITION p20210611 VALUES LESS THAN ('2021-06-12') ENGINE = InnoDB) 4、新增字段 alter table test_20220115 add column icon_install_clk_pv_1d bigint(20) comment '控件安装点击次数' AFTER res_clk_cnt_1d ; alter table test_20220115 add column icon_seconds_open_clk_pv_1d bigint(20) comment '控件秒开点击次数' AFTER icon_install_clk_pv_1d ; 5、将分区数据导入 15天数据，每天70万条，耗时5分钟 insert into ads_d_cpcdw_innereye_gsearch_log_card_1d_test_20220115 select enter_id ,app_version ,model_sale ,clk_pv ,res_clk_cnt_1d ,icon_install_clk_pv_1d ,icon_seconds_open_clk_pv_1d ,dayno ,gmt_create ,gmt_modified ,auto_inc_id from old_table where dayno>='2021-10-01' and dayno

【本文地址】

公司简介

联系我们

mysql 分区表新增字段方案 由于分区表数据量太大，接近1亿条

mysql 分区表新增字段方案由于分区表数据量太大，接近1亿条