MySQL:需要提高 SQL 查询性能帮助

Question

需要 MySQL 专家的帮助来改善以下查询的执行时间。目前，每晚大约需要 1 小时 25 分钟，并且随着更多数据添加到 rsdata 和 trdata 表中，每天都会增加几分钟。其他 3 个表是静态的。

非常感谢任何帮助。

表格尺寸为:

rsdata - 480,566 rows / 130.4 MiB
trdata - 522,506 rows / 64.3 MiB
module - 1,458 rows / 164 KiB
variantgates - 87 rows / 8 KiB
testatussequence - 8 rows / 2.3 KiB

查询:

select distinct
rsdata.rs_variant,
rsdata.weeknumber,
rsdata.rs_moduleid,
rsdata.rs_objectidentifier,
trdata.tr_objectidentifier,
trdata.tr_testversion,
trdata.tr_plannedgate,
trdata.tr_vnvmethod,
testatussequence.tesequencenr,
trdata.tr_testexecutionstatus
from rsdata,
trdata,
module,
variantgates,
testatussequence
where rsdata.weeknumber='1408'
and rsdata.rs_moduleid = module.moduleid
and rsdata.rs_reviewstatus != 'Obsolete'
and rsdata.rs_reviewstatus != 'Rejected'
and rsdata.rs_introbjectidentifieralllevels != ''
and rsdata.rs_introbjectidentifieralllevels != 'Unknown'
and trdata.weeknumber='1408'
and trdata.tr_plannedgate != ''
and trdata.tr_plannedgate != 'Unknown'
and trdata.tr_plannedgate = variantgates.gate
and trdata.tr_variant = variantgates.variant
and trdata.tr_testexecutionstatus = testatussequence.testatus
and find_in_set(trdata.tr_objectidentifier, (select rsdata.rs_introbjectidentifieralllevels))
order by
rs_variant ASC,
weeknumber ASC,
rs_moduleid ASC,
rs_objectidentifier ASC,
tr_testversion ASC,
tr_plannedgate ASC,
tr_vnvmethod ASC,
tesequencenr DESC;

解释查询的输出:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY variantgates ALL Variant_2,Variant NULL NULL NULL 87 Using where; Using temporary; Using filesort
1 PRIMARY trdata ref TR_Variant,WeekNumber TR_Variant 23 remets.variantgates.Variant 5226 Using where
1 PRIMARY testatussequence eq_ref PRIMARY,TEStatus PRIMARY 22 remets.trdata.TR_TestExecutionStatus 1
1 PRIMARY rsdata ref RSDataSI1,RSDataUI2,WeekNumber RSDataUI2 7 const 75658 Using where
1 PRIMARY module ref ModuleUI1 ModuleUI1 11 remets.rsdata.RS_ModuleID 10 Using where; Using index
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used

表格的详细信息:

CREATE TABLE `rsdata` (
`RS_Variant` varchar(20) default 'Unknown',
`RS_ModuleID` varchar(8) default 'Unknown',
`RS_ObjectIdentifier` varchar(50) default 'Unknown',
`RS_ObjectType` varchar(20) default 'Unknown',
`WeekNumber` varchar(4) default '9999',
`RS_ReviewStatus` varchar(12) default 'Unknown',
`RS_Priority` varchar(20) default 'Unknown',
`RS_PlannedGate` varchar(10) default 'Unknown',
`RS_VnVMethod` varchar(50) default 'Unknown',
`RS_Datasheet` varchar(1) default NULL,
`RS_RSLinks` int(11) default '0',
`RS_TSLinks` int(11) default '0',
`RS_ReqLinked` varchar(10) default 'Unknown',
`RS_VnVLinked` varchar(10) default 'Unknown',
`RS_InRSObjectIdentifierOneLevel` varchar(500) default 'Unknown',
`RS_InTSObjectIdentifierOneLevel` varchar(500) default 'Unknown',
`RS_InTSObjectIdentifierAllLevels` varchar(750) default 'Unknown',
`RS_InTRObjectIdentifierAllLevels` varchar(750) default 'Unknown',
UNIQUE KEY `RSDataUI1` (`RS_Variant`,`RS_ModuleID`,`RS_ObjectIdentifier`,`WeekNumber`),
KEY `RSDataSI1` (`RS_ModuleID`,`WeekNumber`),
KEY `RSDataUI2` (`WeekNumber`,`RS_Variant`,`RS_ModuleID`,`RS_ObjectIdentifier`),
KEY `WeekNumber` (`WeekNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `trdata` (
`TR_Variant` varchar(20) default NULL,
`TR_ModuleID` varchar(8) default NULL,
`TR_ObjectIdentifier` varchar(50) default NULL,
`TR_ObjectType` varchar(20) default 'Unknown',
`WeekNumber` varchar(4) default '9999',
`TR_ReviewStatus` varchar(12) default 'Unknown',
`TR_Priority` varchar(20) default 'Unknown',
`TR_PlannedGate` varchar(10) default 'Unknown',
`TR_VnVMethod` varchar(50) default 'Unknown',
`TR_TestVersion` varchar(12) default 'Unknown',
`TR_TestExecutionStatus` varchar(20) default 'Unknown',
UNIQUE KEY `TR_Variant` (`TR_Variant`,`TR_ModuleID`,`TR_ObjectIdentifier`,`WeekNumber`),
KEY `TRDataSI1` (`TR_ModuleID`,`WeekNumber`),
KEY `WeekNumber` (`WeekNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `module` (
`ModuleID` varchar(8) default NULL,
`ModuleType` varchar(5) default NULL,
`ModuleName` varchar(50) default NULL,
`ModuleNameLng` varchar(250) default NULL,
UNIQUE KEY `ModuleUI1` (`ModuleID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `variantgates` (
`Variant` varchar(20) default NULL,
`GateGroup` varchar(20) default NULL,
`GateSequenceNr` int(11) default NULL,
`Gate` varchar(10) default NULL,
UNIQUE KEY `Variant_2` (`Variant`,`GateGroup`,`GateSequenceNr`),
KEY `Variant` (`Variant`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `testatussequence` (
`TESequenceNr` int(11) NOT NULL,
`TEStatus` varchar(20) NOT NULL default '',
PRIMARY KEY (`TEStatus`),
UNIQUE KEY `TEStatus` (`TEStatus`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Answer 1

我想到了一些事情:

• DISTINCT 通常表明 select 语句写得不好。为什么要组合很多很多记录，然后必须在其中删除它们结束？
• 为什么加入表格模块？只是为了保证这样的匹配记录存在吗？那为什么不使用 exits 子句呢？
• 为什么要加入表variantgates？同样的问题。
• trdata 和 rsdata 的 weeknumber='1408'。这似乎是一个真正有助于缩小数据范围的标准。两个表的该列上是否都有索引？如果没有，请创建它们。
• 由于 trdata 和 rsdata 都应具有 weeknumber='1408'，因此您应该暗示这是链接表的内容:并且 trdata.weeknumber = rsdata.weeknumber。 (我知道这已经是隐式的了，但是将其显式化可以帮助 dbms 构建连接。)
• 能否为 rs_reviewstatus、rs_introbjectidentifieralllevels 和 tr_plannedgate 提供肯定列表而不是否定列表？ IE。类似于('打开'，'已接受')中的 rsdata.rs_reviewstatus 之类的东西？然后您也可以在这里使用索引，以加快查询速度。
• 最后回到 DISTINCT:为什么在这里使用它？为什么会出现重复的情况呢？是因为表模块和variantgates的原因吗？还是还有其他原因？