mysql千万级表加字段操作记录

链接:
https://cloud.tencent.com/developer/article/1524019

create table new_origin_order_line like origin_order_line;

## 生成删除索引语句
SELECT CONCAT('ALTER TABLE ',i.TABLE_NAME,' DROP INDEX ',i.INDEX_NAME,';') FROM INFORMATION_SCHEMA.STATISTICS i WHERE TABLE_SCHEMA = 'starpro' AND TABLE_NAME='new_origin_order_line' AND i.INDEX_NAME != 'PRIMARY';

## 生成添加索引语句
SELECT CONCAT('ALTER TABLE ',i.TABLE_NAME,' ADD INDEX ',i.INDEX_NAME,'(',i.COLUMN_NAME,');') FROM INFORMATION_SCHEMA.STATISTICS i WHERE TABLE_SCHEMA = 'starpro' AND TABLE_NAME='origin_order_line' AND i.INDEX_NAME != 'PRIMARY';

### 删除新表索引
ALTER TABLE new_origin_order_line DROP INDEX origin_order_line_5a4ff50e;
ALTER TABLE new_origin_order_line DROP INDEX origin_order_line_8f8a52e9;
ALTER TABLE new_origin_order_line DROP INDEX origin_order_line_fe275738;
ALTER TABLE new_origin_order_line DROP INDEX origin_order_line_af5f18fd;
ALTER TABLE new_origin_order_line DROP INDEX origin_order_line_d0ca2d2c;

### 新表添加字段
alter table new_origin_order_line  add discount double default null;
## copy数据 16min+
## https://www.cnblogs.com/hanwuxing/p/10367147.html
LOCK TABLES origin_order_line READ;
insert into new_origin_order_line(idorder_line,order_line_item_id,ItemID,Email,ItemTitle,order_idorder_id,product_unit,product_qty,taxamount,product_name,product_idproduct_id,p_sku,transactionID,External_Sku,ASIN,image_url,order_status) select idorder_line,order_line_item_id,ItemID,Email,ItemTitle,order_idorder_id,product_unit,product_qty,taxamount,product_name,product_idproduct_id,p_sku,transactionID,External_Sku,ASIN,image_url,order_status from origin_order_line;
UNLOCK TABLES;


## 添加索引  5min+
ALTER TABLE new_origin_order_line ADD INDEX origin_order_line_5a4ff50e(order_idorder_id);     
ALTER TABLE new_origin_order_line ADD INDEX origin_order_line_8f8a52e9(product_idproduct_id); 
ALTER TABLE new_origin_order_line ADD INDEX origin_order_line_fe275738(ItemID);               
ALTER TABLE new_origin_order_line ADD INDEX origin_order_line_af5f18fd(ASIN);                 
ALTER TABLE new_origin_order_line ADD INDEX origin_order_line_d0ca2d2c(p_sku);

## 删除旧表
drop table origin_order_line;
## rename
rename table new_origin_order_line to origin_order_line;