mysql 在线修改表，改表结构，分区。PT工具

1. 使用PT工具：

pt-online-schema-change -uroot -p’xxx’ --socket=’/u01/my3306/run/mysql.sock’ --host=localhost --preserve-triggers --alter “PARTITION BY RANGE (TO_DAYS(warn_dt)) (PARTITION P20180501 VALUES LESS THAN (737181), PARTITION P20180601 VALUES LESS THAN (737212), PARTITION P20180701 VALUES LESS THAN (737242), PARTITION P20180801 LESS THAN (738519), PARTITION P20211231 VALUES LESS THAN (738520))” D=zeusdb（数据库名）,t=t_pstr_stif（表名） --print --execute

2.传统的建立中间表 然后rename
1.建立中间表：
CREATE TABLE t_pstr_stif_m (
id int(11) NOT NULL AUTO_INCREMENT,
rep_id varchar(32) DEFAULT NULL,
seq_no int(8) DEFAULT NULL,
rpd_id varchar(40) DEFAULT NULL,
stif_id varchar(32) DEFAULT NULL,
ctif_id varchar(32) DEFAULT NULL,
ctnm varchar(500) DEFAULT NULL,
smid varchar(30) DEFAULT NULL,
citp varchar(32) DEFAULT NULL,
citp_nt varchar(32) DEFAULT NULL,
ctid varchar(500) DEFAULT NULL,
cbat varchar(2) DEFAULT NULL,
cbac varchar(500) DEFAULT NULL,
cabm varchar(128) DEFAULT NULL,
ctat varchar(2) DEFAULT NULL,
ctac varchar(500) DEFAULT NULL,
cpin varchar(128) DEFAULT NULL,
cpba varchar(500) DEFAULT NULL,
cpbn varchar(128) DEFAULT NULL,
ctip varchar(15) DEFAULT NULL,
tstm varchar(14) DEFAULT NULL,
cttp varchar(4) DEFAULT NULL,
tsdr varchar(32) DEFAULT NULL,
crpp varchar(500) DEFAULT NULL,
crtp varchar(3) DEFAULT NULL,
crat decimal(22,2) DEFAULT NULL,
tcnm varchar(500) DEFAULT NULL,
tsmi varchar(30) DEFAULT NULL,
tcit varchar(32) DEFAULT NULL,
tcit_nt varchar(32) DEFAULT NULL,
tcid varchar(500) DEFAULT NULL,
tcat varchar(2) DEFAULT NULL,
tcba varchar(500) DEFAULT NULL,
tcbn varchar(128) DEFAULT NULL,
tctt varchar(2) DEFAULT NULL,
tcta varchar(500) DEFAULT NULL,
tcpn varchar(128) DEFAULT NULL,
tcpa varchar(500) DEFAULT NULL,
tpbn varchar(128) DEFAULT NULL,
tcip varchar(15) DEFAULT NULL,
tmnm varchar(64) DEFAULT NULL,
bptc varchar(64) DEFAULT NULL,
pmtc varchar(64) DEFAULT NULL,
ticd varchar(128) DEFAULT NULL,
redt datetime DEFAULT NULL,
warn_dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
spcs_st char(1) DEFAULT NULL,
check_st char(1) DEFAULT ‘0’,
mend_st char(1) DEFAULT ‘0’,
mender varchar(32) DEFAULT NULL,
mend_tm datetime DEFAULT NULL,
remark varchar(512) DEFAULT NULL,
doubutclass varchar(20) DEFAULT NULL,
gate_id varchar(32) DEFAULT NULL,
mer_prov varchar(10) DEFAULT NULL,
mer_area varchar(10) DEFAULT NULL,
pos_prov varchar(10) DEFAULT NULL,
pos_area varchar(10) DEFAULT NULL,
warn_rl varchar(512) DEFAULT NULL,
mer_unit varchar(10) DEFAULT NULL,
trans_type varchar(10) DEFAULT NULL COMMENT '交易类型 ',
pos_dev_id varchar(16) DEFAULT NULL COMMENT ‘交易终端号’,
PRIMARY KEY (id,warn_dt)
) ENGINE=InnoDB AUTO_INCREMENT=50344436 DEFAULT CHARSET=utf8 COMMENT=‘可疑交易表’ ;

2.创建分区：
ALTER TABLE t_pstr_stif_m PARTITION BY RANGE (TO_DAYS(warn_dt)) (
PARTITION P20100101 VALUES LESS THAN (734139),
PARTITION P20180101 VALUES LESS THAN (737061),
PARTITION P20211218 VALUES LESS THAN (738507),
PARTITION P20211219 VALUES LESS THAN (738508),
PARTITION P20211220 VALUES LESS THAN (738509),
PARTITION P20211221 VALUES LESS THAN (738510),
PARTITION P20211222 VALUES LESS THAN (738511),
PARTITION P20211223 VALUES LESS THAN (738512),
PARTITION P20211224 VALUES LESS THAN (738513),
PARTITION P20211225 VALUES LESS THAN (738514),
PARTITION P20211226 VALUES LESS THAN (738515),
PARTITION P20211227 VALUES LESS THAN (738516),
PARTITION P20211228 VALUES LESS THAN (738517),
PARTITION P20211229 VALUES LESS THAN (738518),
PARTITION P20211230 VALUES LESS THAN (738519),
PARTITION P20211231 VALUES LESS THAN (738520));

mysql> select count() from t_pstr_stif;
±---------+
| count() |
±---------+
| 8959017 |
±---------+
1 row in set (8.83 sec)

3.改大临时buffer ，进行插入
set session bulk_insert_buffer_size =256217728;

insert into t_pstr_stif_m select * from t_pstr_stif;

4.新表补上索引。
alter table t_pstr_stif_m add index idx_tpss_ctif_id (ctif_id) ,ALGORITHM=INPLACE, LOCK=NONE;
alter table t_pstr_stif_m add index idx_tpss_rep_id (rep_id),ALGORITHM=INPLACE, LOCK=NONE;
alter table t_pstr_stif_m add index idx_tpss_rpd_id (rpd_id) ,ALGORITHM=INPLACE, LOCK=NONE;;
alter table t_pstr_stif_m add index idx_tpss_stif_id (stif_id),ALGORITHM=INPLACE, LOCK=NONE;
alter table t_pstr_stif_m add index ind_tpss_warn_dt (warn_dt),ALGORITHM=INPLACE, LOCK=NONE;
alter table t_pstr_stif_m add index idx_tpss_check_st (check_st) ,ALGORITHM=INPLACE, LOCK=NONE;
alter table t_pstr_stif_m add index idx_tpss_mend_st (mend_st),ALGORITHM=INPLACE, LOCK=NONE;
alter table t_pstr_stif_m add index idx_tpss_redt (redt),ALGORITHM=INPLACE, LOCK=NONE;
alter table t_pstr_stif_m add index idx_tps_spcs_st (spcs_st) ,ALGORITHM=INPLACE, LOCK=NONE;
alter table t_pstr_stif_m add index idx_ticd (ticd),ALGORITHM=INPLACE, LOCK=NONE;
alter table t_pstr_stif_m add index idx_tss_pmtc (pmtc),ALGORITHM=INPLACE, LOCK=NONE;

5. 新表老表互换名字：
   Alter table t_pstr_stif rename to t_pstr_stif_old;
   Alter table t_pstr_stif_m rename to t_pstr_stif;

6. 新表analyze
   analyze table t_pstr_stif;