当前位置：首页 > news >正文

千万大表分区办法

news 2025/11/4 22:16:38

第一步:创建影子表

CREATE TABLE xxx_record_new LIKE psb_opendoor_record;

第二步: 将影子表的分区字段-加入到主键中

ALTER TABLE xxx_record_new DROP PRIMARY KEY, ADD PRIMARY KEY (LockRecordMKId, OpenTime);

第三步: 给影子表创建分区

ALTER TABLE xxx_record_new
PARTITION BY RANGE (OpenTime) (PARTITION p202505 VALUES LESS THAN (20250601000000),PARTITION p202506 VALUES LESS THAN (20250701000000),PARTITION p202507 VALUES LESS THAN (20250801000000),PARTITION p202508 VALUES LESS THAN (20250901000000),PARTITION p202509 VALUES LESS THAN (20251001000000),PARTITION p202510 VALUES LESS THAN (20251101000000),PARTITION p202511 VALUES LESS THAN (20251201000000),PARTITION p202512 VALUES LESS THAN (20260101000000),PARTITION p202601 VALUES LESS THAN (20260201000000)
);

第四步：创建业务表触发器

DELIMITER $$CREATE TRIGGER trg_ins_fwd
AFTER INSERT ON xxx_record
FOR EACH ROW
BEGININSERT INTO xxx_record_new VALUES (NEW.LockRecordMKId, NEW.LockLogId, NEW.HotelId,NEW.RoomId, NEW.BillNo, NEW.CRowId, NEW.OpenTime, NEW.LockAddr,NEW.OpenState, NEW.RecordType, NEW.OpenType, NEW.Password,NEW.Telphone, NEW.PlatRole, NEW.ShopRole, NEW.CreateId,NEW.CreateDate, NEW.ModifyId, NEW.ModifyDate, NEW.Remark);
END$$DELIMITER ;

第五步：建立快照文件，加载快照数据

SHOW VARIABLES LIKE 'secure_file_priv';   //C:\ProgramData\MySQL\MySQL Server 8.4\Uploads\USE bscback;
SELECT *
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.4/Uploads/psb_old.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
FROM psb_opendoor_record
WHERE OpenTime < 20250801000000
ORDER BY LockRecordMKId;-- 批量导入影子表
SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.4/Uploads/psb_old.csv'
INTO TABLE xxx_record_new
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;   -- 如果 CSV 带表头就加 IGNORE 1 LINES，不带就去掉COMMIT;
SET unique_checks = 1;
SET foreign_key_checks = 1;

第六步：核对-检测-切换

-- 1. 确认两边行数一致
SELECT COUNT(*) FROM xxx_record;      -- 原表（触发器仍在写）
SELECT COUNT(*) FROM xxx_record_new;  -- 影子表（已含旧数据 + 新数据）-- 2. 在一个事务里交换表名（InnoDB 毫秒级锁）
RENAME TABLE xxx_record      TO xxx_record_old,xxx_record_new  TO xxx_record;-- 3. 删除触发器（可选：留几天当保险）
DROP TRIGGER IF EXISTS trg_ins_fwd ON xxx_record_old;-- 4. 确认业务正常后，再 drop 旧表
-- DROP TABLE xxx_record_old;

相关测试脚本

--  清空测试表
TRUNCATE TABLE xxx_record_new;

查询提交状态

SELECT @@autocommit;          -- 看是否 0
SELECT trx_id, trx_state FROM information_schema.innodb_trx\G

批量插入数据

DELIMITER $$ CREATE PROCEDURE insert_test_500() 
BEGIN DECLARE i INT DEFAULT 1; WHILE i<=500 DO 
INSERT INTO xxx_record VALUES(UUID(),CONCAT('LOG',LPAD(i,5,0)),'HT001',CONCAT('R',LPAD(i%100,3,0)),CONCAT('B',LPAD(i,6,0)),CONCAT('C',LPAD(i,6,0)),20250808125036,'ADDR01',0,4,2,'1234','13800000000',1,0,'admin',UNIX_TIMESTAMP()*1000,'admin',UNIX_TIMESTAMP()*1000,'测试');
SET i=i+1;
END WHILE;
END$$ DELIMITER ;--  执行存储过程
CALL insert_test_500();