案例2—MySQL5.7-分布式-MySQL8.0.32执行关联查询对比

一、环境

1、基本情况

测试机3台，4核，8G

数据库：mysql 5.7.40

数据库升级：mysql8.0.32

2、数据导入方式

mysqldump导出，mysql导入。

3、测试数据

4、查询语句

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 `St atus`,
`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;

--create index idx_Admin_Account_AccountId on Admin_Account_Role(AccountId);

-- alter table Admin_Account_Role drop index idx_Admin_Account_AccountId;

二、测试对比

大约有9个表进行join，主要查询的表大约1000万行，其他都是100多行的小表。

除了主表有单独加的索引，其他都没后加索引，查询20天数据。

1、主表单独列加索引和所有表的主键索引—mysql5.7.40

（1）执行计划

（2）执行时间（140s）

2、小表需要的列也加索引—mysql5.7.40

创建索引：

create index idx_GroupCode_DictCode_Extend on Common_Dict_Item(GroupCode,DictCode,Extend);

（1）执行计划

（2）查询时间（40s）

3、mycat2分布式分库分表—mysql5.7.40

使用mycat2的分布式分库分表，额外的索引没有进行干预，只对这个1000万的大表进行分库分表，

同样的查询语句直接干到8s-9s。

（1）分库分表结构

mycat2本地所有表；分库分表

CREATE TABLE `operation_workorder3` (
`id` BINARY(16) DEFAULT (UUID_TO_BIN(UUID())),
`Code` varchar(50) NOT NULL,
`Type` varchar(50) NOT NULL,
`System` varchar(50) NOT NULL,
`Name` varchar(100) NOT NULL,
`Description` longtext NOT NULL,
`Attachment` varchar(2000) DEFAULT NULL,
`Status` varchar(50) NOT NULL,
`SubmitDate` datetime DEFAULT NULL,
`AssignDate` datetime DEFAULT NULL,
`AssignCode` varchar(50) DEFAULT NULL,
`AssignName` varchar(50) DEFAULT NULL,
`AssignMemo` varchar(500) DEFAULT NULL,
`ToDevelopDate` datetime DEFAULT NULL,
`ToDevelopCode` varchar(50) DEFAULT NULL,
`ToDevelopName` varchar(50) DEFAULT NULL,
`ToRequirementDate` datetime DEFAULT NULL,
`ToRequirementCode` varchar(50) DEFAULT NULL,
`ToRequirementName` varchar(50) DEFAULT NULL,
`SolvePersonCode` varchar(50) DEFAULT NULL,
`SolvePersonName` varchar(50) DEFAULT NULL,
`SolveDate` datetime DEFAULT NULL,
`Grade` varchar(50) DEFAULT NULL,
`QuestionType` varchar(50) DEFAULT NULL,
`IsToKnowledgeBase` varchar(50) DEFAULT NULL,
`ToKnowledgeBaseDate` datetime DEFAULT NULL,
`ConfirmDate` datetime DEFAULT NULL,
`EvaluateDate` datetime DEFAULT NULL,
`EvaluateScore` decimal(18,2) DEFAULT NULL,
`EvaluateMemo` varchar(200) DEFAULT NULL,
`CompanyID` int(11) DEFAULT '0',
`CreatedTime` datetime DEFAULT NULL,
`CreatedBy` char(36) NOT NULL,
`ModifiedTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ModifiedBy` char(36) NOT NULL,
`Deleted` bit(1) NOT NULL DEFAULT b'0',
`DeletedTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`DeletedBy` char(36) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `idx_id` (`Id`),
KEY `idx_createdtime` (`CreatedTime`),
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;

（2）执行计划

（3）查询语句（8.7s）

4、升级MySQL版本-8.0.32

还是上述语句，升级版本到mysql8.0.31,单表查询，表结构和1一样，小表也没有单独加索引，单表查询时间直接就是8-11s;

（1）执行计划

（2）执行语句（10s）

我主要就想问问，8.0这么猛吗。有没有大佬做过这种测试。

你这个案例很好啊，第一个这个业务表很多，mysql多表关联本来就支持不好，一般4-5张就算多了，5.7只支持nest loop，小表要建索引，8.0支持hash，所以不建索引执行计划也快了，至于用了分库分表，这是架构优化的。