MySQL 千万级数据迁移

今天药忘吃喽~ 2024-03-30 14:34 74阅读 0赞

背景介绍

项目中表数据存在超过50%的逻辑删除数据，影响查询性能，本次计划将逻辑有效的数据迁移到新表，使得表数据量减少，索引文件减少，提高查询效率。  
大体思路

通过存储过程实现表数据迁移，迁移过程中涉及动态删除索引、动态创建索引等操作，导入时通过分页的方式来导入逻辑有效数据。

计算表数据量大小  
创建备份表（只保留表结构）  
删除备份表索引（保留主键索引）  
导入有效数据到备份表  
给备份表创建索引（索引与原表一致）  
这里使用视图先准备好需要创建的索引数据  
再通过存储过程从视图中读取出数据继续创建  
重命名备份表，重命名新表

步骤详情

Step-1：计算表数据量大小

CREATE DEFINER=\`root\`@\`localhost\` PROCEDURE \`Step\_01\_CountUsefulData\`(  
in schemaName varchar(50), -- 输入参数，schema名称  
in tableName varchar(50), -- 输入参数，表名称  
in pageSize INT, -- 输入参数，每页大小  
OUT totalPages INT, -- 输出参数，总页数  
OUT minId INT, -- 输出参数，最新id  
OUT maxId INT -- 输出参数，最大id  
)  
BEGIN  
SET @begin\_date = now();  
SET @pageSize = pageSize;  
  
SET @stmt = CONCAT('SELECT count(1), min(id), max(id) FROM ', tableName, ' where is\_deleted = false into @totalRecords, @minId, @maxId');  
prepare stmt from @stmt;  
execute stmt;  
deallocate prepare stmt;  
  
SET totalPages = CEILING(@totalRecords/pageSize);  
SET minId = @minId;  
SET maxId = @maxId;  
SET @ct = TIMESTAMPDIFF(SECOND, @begin\_date, now());  
select '计算表数据量' as '任务名称', tableName as '表名', @totalRecords as '总条数', @pageSize as '每页大小', @ct as '用时/秒';  
END

Step-2：创建备份表（只保留表结构）

CREATE DEFINER=\`root\`@\`localhost\` PROCEDURE \`Step\_02\_CreateBakTable\`(  
in schemaName varchar(50), -- 输入参数，schema名称  
in originalTableName varchar(50), -- 输入参数，原表名称  
in newTableName varchar(50) -- 输入参数，新表名称  
)  
BEGIN  
set @stmt = CONCAT('create table ', newTableName, ' like ', originalTableName);  
select '创建备份表' as '任务名称', newTableName as '备份表', originalTableName as '原表', @stmt as '备份SQL';  
  
prepare stmt from @stmt;  
execute stmt;  
deallocate prepare stmt;  
END

Step-3：删除备份表索引（保留主键索引）

根据传入的schema名称和表名称动态删除索引（保留主键索引不删）

CREATE DEFINER=\`root\`@\`localhost\` PROCEDURE \`Step\_03\_RemoveTableIndex\`(  
in tableSchema varchar(50), -- 输入参数  
in newTableName varchar(50) -- 输入参数  
)  
BEGIN  
select '删除表索引开始' as '任务名称', tableSchema as 'TableSchema', newTableName as '表名';  
SET @begin\_time = now();  
  
\-- 查询表索引，并拼接成drop语句  
set @stmt\_idx = CONCAT("SELECT GROUP\_CONCAT(concat('drop index ',INDEX\_NAME)) as idx FROM ",  
"(select DISTINCT(INDEX\_NAME) FROM INFORMATION\_SCHEMA.STATISTICS T WHERE T.TABLE\_SCHEMA = '", tableSchema, "'"  
" AND T.TABLE\_NAME = '", newTableName, "' AND T.INDEX\_NAME <> 'PRIMARY') t1 into @dropsql");  
  
prepare stmt from @stmt\_idx;  
execute stmt;  
deallocate prepare stmt;  
  
IF @dropsql is not NULL THEN  
\-- 拼接删除语句  
set @stmt = CONCAT('ALTER TABLE ', newTableName, ' ', @dropsql, ';');  
select '删除表索引' as '任务名称', @stmt as '删除SQL';  
  
prepare stmt from @stmt;  
execute stmt;  
deallocate prepare stmt;  
ELSE  
select '删除表索引失败' as '任务名称', newTableName as '该表没有索引，无需删除';  
END IF;  
  
SET @cost = TIMESTAMPDIFF(SECOND, @begin\_time, now());  
select '删除表索引结束' as '任务名称', newTableName as '表名', @cost as '耗时(秒)';  
END

Step-4：导入逻辑有效数据到备份表

CREATE DEFINER=\`root\`@\`localhost\` PROCEDURE \`Step\_04\_ImportTableData\`(  
in tableSchema VARCHAR(50),  
in originalTableName VARCHAR(50),  
in newTableName VARCHAR(50),  
in pageSize int,  
in minId int,  
in maxId int  
)  
BEGIN  
DECLARE fromId int;  
SELECT '导入数据开始' as '任务名称', tableSchema 'Schema Name', originalTableName '原表名', newTableName as '新表名', pageSize as '每页数据大小', minId as '最小id', maxId as '最大Id';  
SET @pageSize = pageSize;  
SET @newTableName = newTableName;  
SET @originalTableName = originalTableName;  
SET @minId = minId;  
SET @maxId = maxId;  
set @fromId = @minId;  
  
SET @query\_begin\_date = now();  
WHILE @minId <= @maxId DO  
  
SET @stmt = CONCAT('INSERT INTO ', @newTableName, ' SELECT \* FROM ', @originalTableName, ' where is\_deleted = false and id BETWEEN ? and ?;');  
  
SET @minId = @minId;  
SET @nextId = @minId + @pageSize;  
  
\-- select @stmt as '备份SQL', @minId as 'MinId', @maxId as 'MaxId';  
\-- 开启事务，执行一批数据后就提交事务  
BEGIN  
START TRANSACTION;  
prepare stmt from @stmt;  
execute stmt USING @minId, @nextId;  
deallocate prepare stmt;  
COMMIT;  
END;  
  
\-- select @stmt as '备份SQL', @minId as 'MinId', @nextId as 'NextId', @ct as '导入数据用时/秒';  
SET @minId = @nextId + 1;  
  
END WHILE;  
SET @ct = TIMESTAMPDIFF(SECOND, @query\_begin\_date, now());  
select '导入数据结束' as '任务名称', @ct as '耗时(秒)';  
  
END

Step-5：给备份表创建索引（索引与原表一致）

包括唯一索引、联合索引的创建

CREATE DEFINER=\`root\`@\`localhost\` PROCEDURE \`Step\_05\_CreateTableIndex\`(  
in schemaName VARCHAR(50),  
in originalTableName varchar(50),  
in newTableName varchar(50)  
)  
BEGIN  
  
select '创建索引开始' as '任务名称', newTableName as '表名';  
SET @begin\_time = now();  
  
\-- 根据schemaName, tableName 查询表索引信息，并创建视图 （动态游标的处理方案）  
call Step\_05\_01\_CreateTableIndex(schemaName, originalTableName);  
  
\-- 从上一步的视图中读取数据  
call Step\_05\_02\_CreateTableIndex(schemaName, newTableName);  
  
SET @cost = TIMESTAMPDIFF(SECOND, @begin\_time, now());  
select '创建索引结束' as '任务名称', newTableName as '表名', @cost as '耗时(秒)';  
  
END

CREATE DEFINER=\`root\`@\`localhost\` PROCEDURE \`Step\_05\_01\_CreateTableIndex\`(  
in tableSchema varchar(50),  
in newTableName varchar(50)  
)  
BEGIN  
DROP VIEW IF EXISTS tmp\_table\_idx\_view;  
SET @sqlstr = "CREATE VIEW tmp\_table\_idx\_view as ";  
SET @sqlstr = CONCAT(@sqlstr , "SELECT non\_unique, index\_name, seq\_in\_index, column\_name, index\_type, index\_comment FROM INFORMATION\_SCHEMA.STATISTICS t WHERE t.TABLE\_SCHEMA = '",  
tableSchema, "' AND t.TABLE\_NAME ='", newTableName, "' AND t.INDEX\_NAME <> 'PRIMARY'");  
  
PREPARE stmt FROM @sqlstr;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;  
END

重点：索引结构动态SQL的拼接如下：

CREATE DEFINER=\`root\`@\`localhost\` PROCEDURE \`Step\_05\_02\_CreateTableIndex\`(  
in tableSchema varchar(50),  
in tableName varchar(50)  
)  
BEGIN  
DECLARE is\_finished int;  
DECLARE non\_unique int; -- 非主键索引 0:是主键索引 1:非主键索引  
DECLARE idxname VARCHAR(50); -- 索引名称  
DECLARE idxseq int; -- 索引序号  
DECLARE clmname VARCHAR(50); -- 索引列名称  
DECLARE idxtype VARCHAR(50); -- 索引类型  
DECLARE idxcmt VARCHAR(50); -- 索引备注  
DECLARE pre\_sql VARCHAR(500) DEFAULT 'ADD $unique INDEX \`$idxname\`($idxcln) USING $idxtype $cmt';  
DECLARE result\_sql VARCHAR(3500) DEFAULT '';  
  
DECLARE cursor\_query CURSOR FOR SELECT \* FROM tmp\_table\_idx\_view; -- 从视图中读取数据  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is\_finished = 1;  
  
SET @begin\_time = now();  
select '创建表索引step2开始' as '任务名称', tableSchema as 'TableSchema', tableName as '表名';  
  
OPEN cursor\_query;  
get\_idx: LOOP  
  
FETCH cursor\_query into non\_unique, idxname, idxseq, clmname, idxtype, idxcmt;  
IF is\_finished=1 THEN  
LEAVE get\_idx;  
END IF;  
  
if idxseq = 1 then  
\-- 新创建  
set result\_sql = CONCAT(result\_sql, ',', pre\_sql);  
set result\_sql = replace(result\_sql,'$clmns', '');  
set result\_sql = replace(result\_sql, '$idxname', idxname);  
set result\_sql = replace(result\_sql, '$idxcln', CONCAT('\`', clmname, '\`', '$clmns'));  
set result\_sql = replace(result\_sql, '$idxtype', idxtype);  
  
if non\_unique = 0 THEN  
set result\_sql = replace(result\_sql, '$unique', 'UNIQUE');  
else  
set result\_sql = replace(result\_sql, '$unique', '');  
end if;  
  
if idxcmt is NULL THEN  
set result\_sql = replace(result\_sql, '$cmt', '');  
else  
set result\_sql = replace(result\_sql, '$cmt', CONCAT(' COMMENT \\'', idxcmt, '\\''));  
end if;  
  
elseif idxseq > 1 then  
\-- 替换  
set result\_sql = replace(result\_sql, '$clmns', CONCAT(',\`', clmname, '\`', '$clmns'));  
end if;  
  
END LOOP get\_idx;  
CLOSE cursor\_query;  
  
set result\_sql = replace(result\_sql , '$clmns', '');  
set result\_sql = replace(result\_sql, '$idxcln', '');  
set result\_sql = CONCAT(replace(result\_sql ,'$clmns',''), ';');  
  
set result\_sql = right(result\_sql, length(result\_sql) - 1);  
set result\_sql = CONCAT('ALTER TABLE \`', tableName,'\` ', result\_sql);  
  
select '创建表索引step2中...' as '任务名称', result\_sql as 'SQL';  
  
set @stmt = result\_sql;  
PREPARE stmt FROM @stmt;  
EXECUTE stmt;  
DEALLOCATE PREPARE stmt;  
SET @cost = TIMESTAMPDIFF(SECOND, @begin\_time, now());  
select '创建表索引step2结束' as '任务名称', result\_sql as 'SQL', @cost as '耗时(秒)';  
  
set @stmt = NULL;  
set result\_sql = NULL;  
DROP VIEW IF EXISTS tmp\_table\_idx\_view;  
  
END

Step-6：重命名备份表，重命名新表

CREATE DEFINER=\`root\`@\`localhost\` PROCEDURE \`Step\_06\_RenameTableName\`(  
in schemaName VARCHAR(50),  
in originalTableName VARCHAR(50),  
in newTableName VARCHAR(50)  
)  
BEGIN  
/\*  
原表名: t\_user  
备份表: bak\_t\_user\_年月日时分秒  
重命名后:  
旧表: t\_user --> bak\_bak\_t\_user\_年月日时分秒  
新表: bak\_t\_user\_年月日时分秒 --> t\_user  
\*/  
  
SET @begin\_time = now();  
select '重命名表名开始' as '任务名称', schemaName as 'SchemaName', newTableName as '表名称', originalTableName as '原表名称';  
  
set @stmt = CONCAT('RENAME TABLE ', originalTableName, ' to bak\_', newTableName, ', ', newTableName, ' to ', originalTableName);  
  
prepare stmt from @stmt;  
execute stmt;  
deallocate prepare stmt;  
  
SET @cost = TIMESTAMPDIFF(SECOND, @begin\_time, now());  
select '重命名表名结束' as '任务名称', @stmt as '重命名表名SQL语句', @cost as '耗时(秒)';  
END

其他：

删除表索引的方式有：

方式一：

ALTER TABLE t\_user  
drop index idx\_name,  
drop index idx\_age;

方式二

drop index idx\_name on t\_user;  
drop index idx\_age on t\_user;