目录
MySQL集群读写分离方案D-(MySQL+GTID+MyCat2+Keepalive)之6/6:MyCat2单表模式迁移数据到分表分库
一、问题
1、现有问题
现有数据库,其中表operation_workorder为1000多万,查询速度慢,约40-50秒钟才可以返回结构。
当前结构进行主从模式,没有进行分库分表,根据上节搭建好了分库分表库。
现有数据需要迁移过来,并实现自动分库分表。
二、解决思路
1、先确认需要分库分表的单表是哪个,一般为数据量巨大的表。 2、导出原来的数据库所有数据 3、传输至Mycat2库 4、在Mycat2数据库导入所有数据-(mycat2执行) 5、在Mycat2中删除表operation_workorder(mycat2执行) 6、修改operationproject.operation_workorder.bak的内容【分库分表模式创建】-(mycat2执行) 7、导入编辑好的bak文件(mycat2执行) 8、查看各数据库节点的分库情况(mycat2执行) 9、查看表的总数据量(mycat2执行) 10、查询对比单表和分库分表对数据的查询。
三、实施(针对其中一个表进行分库分表)
1、确认需要分库分表的单表
此库目前大约有1000万数据。
operationproject.operation_workorder
2、从源库(主从或单机)导出数据
(1)导出operationproject库的所有数据
mysqldump -uroot -prootroot --set-gtid-purged=OFF operationproject > operationproject.bak
(2)导出operationproject库中表operation_workorder的数据
mysqldump -uroot -prootroot --set-gtid-purged=OFF operationproject operation_workorder > operationproject.operation_workorder.bak
(3)查看表operation_workorder的数据量:
3、传输至Mycat2库
scp operationproject.operation_workorder.bak root@192.168.21.190:/mysql/backup scp operationproject.bak root@192.168.21.190:/mysql/backup
4、在Mycat2数据库导入所有生产数据-(mycat2执行)
mysql -uroot -p123456 -P 8066 -h192.168.21.190 operationproject < operationproject.bak
5、在Mycat2中删除operation_workorder(mycat2执行)
6、修改operationproject.operation_workorder.bak的内容-(mycat2执行)
即把表的创建模式改为分库分表。执行这一步的目的是手动修改创建表未分库分表,且可以把所有自动导入,人工不需要做特殊处理。
vi operationproject.operation_workorder.bak (因为有1000多万数据,打开表时较慢)
CREATE TABLE `operation_workorder` ( `Id` char(36) NOT NULL, `Code` varchar(50) NOT NULL, `Type` varchar(50) NOT NULL, ... ... ... KEY `idx_status` (`Status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC dbpartition BY mod_hash(Id) tbpartition BY mod_hash(Id) tbpartitions 3 dbpartitions 4;
7、导入编辑好的bak文件(mycat2执行)
mysql -uroot -p123456 -P 8066 -h192.168.21.190 operationproject < operationproject.operation_workorder.bak
8、查看各数据库节点的分库情况(mycat2执行)
9、查看表的总数据量(mycat2执行)
四、数据查询对比
1、查询语句
SELECT `T1`.`Id` AS `Id`, `T1`.`Code` AS `Code`, `T1`.`Type` AS `Type`, `T1`.`System` AS `System`, `T1`.`Name` AS `Name`, `T1`.`Attachment` AS `Attachment`, `T1`.`Status` AS `Status`, `T1`.`SubmitDate` AS `SubmitDate`, `T1`.`AssignDate` AS `AssignDate`, `T1`.`AssignCode` AS `AssignCode`, `T1`.`AssignName` AS `AssignName`, `T1`.`SolveDate` AS `SolveDate`, `T1`.`IsToKnowledgeBase` AS `IsToKnowledgeBase`, `T1`.`CreatedTime` AS `CreatedTime`, `T2`.`Name` AS `Creator`, `T3`.`Name` AS `TypeName`, `T4`.`Name` AS `SystemName`, `T5`.`Name` AS `StatusName`, `T6`.`Name` AS `Grade`, `T7`.`Name` AS `QuestionType`, `T8`.`cFullName` AS `CompanyName` FROM `operationproject`.`Operation_WorkOrder` AS `T1` LEFT JOIN `operationproject`.`Admin_Account` AS `T2` ON ( `T1`.`CreatedBy` = `T2`.`Id` ) AND `T2`.`Deleted` = 0 AND `T2`.`TenantId` IS NULL LEFT JOIN `operationproject`.`Common_Dict_Item` AS `T3` ON ((((`T3`.`GroupCode` = 'OperateProject' ) AND ( `T3`.`DictCode` = 'WorkOrderType' )) AND ( `T3`.`Extend` = 'zh' )) AND ( `T1`.`Type` = `T3`.`Value` )) LEFT JOIN `operationproject`.`Common_Dict_Item` AS `T4` ON ((((`T4`.`GroupCode` = 'OperateProject' ) AND ( `T4`.`DictCode` = 'System' )) AND ( `T4`.`Extend` = 'zh' )) AND ( `T1`.`System` = `T4`.`Value` )) LEFT JOIN `operationproject`.`Common_Dict_Item` AS `T5` ON ((((`T5`.`GroupCode` = 'OperateProject' ) AND ( `T5`.`DictCode` = 'WorkOrderStatus' )) AND ( `T5`.`Extend` = 'zh' )) AND ( `T1`.`Status` = `T5`.`Value` )) LEFT JOIN `operationproject`.`Common_Dict_Item` AS `T6` ON ((((`T6`.`GroupCode` = 'OperateProject' ) AND ( `T6`.`DictCode` = 'Grade' )) AND ( `T6`.`Extend` = 'zh' )) AND ( `T1`.`Grade` = `T6`.`Value` )) LEFT JOIN `operationproject`.`Common_Dict_Item` AS `T7` ON ((((`T7`.`GroupCode` = 'OperateProject' ) AND ( `T7`.`DictCode` = 'QuestionType' )) AND ( `T7`.`Extend` = 'zh' )) AND ( `T1`.`QuestionType` = `T7`.`Value` )) LEFT JOIN `operationproject`.`Master_Company` AS `T8` ON ( `T1`.`CompanyID` = `T8`.`Id` ) AND `T8`.`Deleted` = 0 LEFT JOIN `operationproject`.`Admin_Account_Role` AS `T9` ON ( `T1`.`CreatedBy` = `T9`.`AccountId` ) WHERE ( `T1`.`Status` <> '1' ) AND ( `T1`.`CreatedTime` >= TIMESTAMP ( '2024-01-01 00:00:00' )) AND ( `T1`.`CreatedTime` <= TIMESTAMP ( '2024-01-20 00:00:00' )) AND `T1`.`Deleted` = 0 ORDER BY `T1`.`Id` DESC;-- LIMIT 0,50;
2、原库(单表)查询
耗时:42.967s
3、分库分表查询
耗时:8-9s