MySQL集群读写分离方案D-(MySQL+GTID+MyCat2+Keepalive)之6/6：MyCat2单表模式迁移数据到分表分库（查询时间对比）

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