当数据库中没有其他操作时,我在同一用户名下用完全相同的参数执行同一查询几次;服务器大部分空闲。mysql版本是8.15.0,在带有innodb引擎的windows server(azure box)上。
查询执行的大部分时间是永远的——这意味着相应的线程保持“发送数据”状态超过30分钟,然后我就终止了它。
但有时它会在0002秒内产生结果(我的理解应该是这样的)
领先的表存储了大约10万条记录,其中一个涉及的表有将近100万条记录。
最初的选择是:
SELECT part_1.*
FROM `peps.VSessionRunFileList` as part_1
WHERE 1 = 1 and
part_1.`SessionUUID` = 0xD565EC62BDFC11EAB5571736CB554B78 AND
part_1.`RunId` = 138
ORDER BY 1
LIMIT 151 OFFSET 0
select使用具有合并算法的视图进行操作,基础select如下所示:
SELECT
jrf.`FileName` as `Name`,
jrf.`SessionUUID`,
jrf.`RunId`,
file.`StoragePath`,
coalesce(fip.`lines`, 0) as `Lines`,
file.`Size`,
file.`ImportedSize`,
file.`Format`,
file.`CreatedOn`,
fup.`StartedOn` as `UploadStartedOn`,
fup.`EndedOn` as `UploadedOn`,
fip.`StartedOn` as `ImportStartedOn`,
fip.`EndedOn` as `CompletedOn`,
file.`FileCreatedOn`,
CAST(fup.`Ended` as UNSIGNED) AS `Uploaded`,
CAST(fip.`Ended` as UNSIGNED) AS `Complete`,
CAST(file.`Aborted` as UNSIGNED) AS `Aborted`,
TIMESTAMPDIFF(SECOND, fup.`StartedOn`, fup.`EndedOn`) as `UploadDuration`,
TIMESTAMPDIFF(SECOND, fip.`StartedOn`, fip.`EndedOn`) as `ImportDuration`,
file.`DigestAlgorithm`,
file.`OriginalDigest`,
file.`Digest`,
(select count(*) from `peps.ImportSessionLog`
where `SessionUUID` = jrf.`SessionUUID`
AND `FileName` = jrf.`FileName`
AND `RunId` = jrf.`RunId`
AND `Level` = 0) as `ErrorCount`,
(select count(*) from `peps.ImportSessionLog`
where `SessionUUID` = jrf.`SessionUUID`
AND `FileName` = jrf.`FileName`
AND `RunId` = jrf.`RunId`
AND `Level` = 1) as `WarningCount`,
coalesce(fup.`percentage`, 0) AS `UploadPercentage`,
coalesce(fip.`percentage`, 0) AS `ImportPercentage`,
CASE
WHEN file.`Digest` = file.`OriginalDigest` THEN 1
ELSE 0
END AS `ValidCheckSum`,
job.`SystemUUID`,
file.`DataCategory`,
job.`DataCategory` as `JobDataCategory`,
job.`SubCategory`,
job.`SessionType`
FROM
`peps.JobRunFiles` as jrf
INNER JOIN `peps.ImportSession` AS job ON job.`UUID` = jrf.`SessionUUID`
INNER JOIN `peps.Files` AS file ON file.`Name` = jrf.`FileName`
AND file.`SessionUUID` = jrf.`SessionUUID`
LEFT JOIN `peps.FileProgress` AS fip ON fip.`SessionUUID` = jrf.`SessionUUID`
AND fip.`FileName` = jrf.`FileName`
AND fip.`RunId` = jrf.`RunId`
AND fip.`Operation` = 'IMPORT'
LEFT JOIN `peps.FileProgress` AS fup ON fup.`SessionUUID` = jrf.`SessionUUID`
AND fup.`FileName` = jrf.`FileName`
AND fup.`RunId` = jrf.`RunId`
AND fup.`Operation` = 'UPLOAD'
当我执行explain时,它显示它计划使用主键或外键索引。where条件包含前导表的所有主键,所有连接条件都使用eq操作。
# id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY job const PRIMARY PRIMARY 18 const 1 100.00 Using temporary; Using filesort
1 PRIMARY file ALL PRIMARY 104300 10.00 Using where
1 PRIMARY jrf eq_ref PRIMARY,JobRunFilesDel PRIMARY 405 const,const,elsa_dev.file.Name 1 100.00 Using where; Using index
1 PRIMARY fip eq_ref PRIMARY PRIMARY 467 elsa_dev.file.Name,const,const,const 1 100.00 Using where
1 PRIMARY fup eq_ref PRIMARY PRIMARY 467 elsa_dev.file.Name,const,const,const 1 100.00 Using where
3 DEPENDENT SUBQUERY peps.importsessionlog ref ImportSessionLogDel ImportSessionLogDel 18 elsa_dev.jrf.SessionUUID 42733 0.10 Using index condition; Using where
4 DEPENDENT SUBQUERY peps.importsessionlog ref ImportSessionLogDel ImportSessionLogDel 18 elsa_dev.jrf.SessionUUID 42733 0.10 Using index condition; Using where
涉及的表格:
CREATE TABLE `peps.jobrunfiles` (
`SessionUUID` varbinary(16) NOT NULL,
`RunId` int(11) NOT NULL,
`FileName` varchar(127) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`SessionUUID`,`RunId`,`FileName`),
KEY `JobRunFilesDel` (`FileName`,`SessionUUID`),
CONSTRAINT `JobRunFilesDel` FOREIGN KEY (`FileName`, `SessionUUID`) REFERENCES `peps.files` (`Name`, `SessionUUID`) ON DELETE CASCADE ON UPDATE RESTRICT
) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `peps.importsession` (
`UUID` varbinary(16) NOT NULL,
`SystemUUID` varbinary(16) DEFAULT NULL,
`CurrentRunId` int(11) DEFAULT NULL,
`StartedOn` timestamp NULL DEFAULT NULL,
`CreatedOn` timestamp NULL DEFAULT NULL,
`CreatedBy` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`Completed` tinyint(1) DEFAULT '0',
`Started` tinyint(1) DEFAULT '0',
`StartedBy` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`CompletedOn` timestamp NULL DEFAULT NULL,
`CompletedBy` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`Aborted` tinyint(1) DEFAULT '0',
`AbortedOn` timestamp NULL DEFAULT NULL,
`AbortedBy` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`FromDesktop` tinyint(1) DEFAULT '0',
`FilePath` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`DataCategory` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`SubCategory` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`SessionType` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`CurrentRunListId` int(11) DEFAULT NULL,
`StartParams` text,
PRIMARY KEY (`UUID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `peps.files` (
`Name` varchar(127) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`SessionUUID` varbinary(16) NOT NULL,
`ID` int(11) DEFAULT NULL,
`Format` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`FileCreatedOn` timestamp NULL DEFAULT NULL,
`Size` int(11) DEFAULT '0',
`ImportedSize` int(11) DEFAULT '0',
`Complete` tinyint(1) DEFAULT '0',
`DataCategory` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`Lines` int(11) DEFAULT '0',
`CreatedOn` timestamp NULL DEFAULT NULL,
`CreatedBy` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`ImportStartedOn` timestamp NULL DEFAULT NULL,
`CompletedOn` timestamp NULL DEFAULT NULL,
`CompletedBy` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`Aborted` tinyint(1) DEFAULT '0',
`AbortedOn` timestamp NULL DEFAULT NULL,
`AbortedBy` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`DigestAlgorithm` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`OriginalDigest` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`Digest` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`Uploaded` tinyint(1) DEFAULT '0',
`UploadStartedOn` timestamp NULL DEFAULT NULL,
`UploadedOn` timestamp NULL DEFAULT NULL,
`UploadedBy` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`HeaderFileName` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`StoragePath` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`Inconsistencies` int(11) DEFAULT NULL,
`ACTUALSIZE` int(11) DEFAULT NULL,
PRIMARY KEY (`Name`,`SessionUUID`)
) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `peps.fileprogress` (
`FileName` varchar(127) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`SessionUUID` varbinary(16) NOT NULL,
`RunId` int(11) NOT NULL,
`Operation` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`StartedOn` timestamp NULL DEFAULT NULL,
`EndedOn` timestamp NULL DEFAULT NULL,
`Ended` int(11) DEFAULT '0',
`time` timestamp NULL DEFAULT NULL,
`percentage` double DEFAULT '0',
`transferred` int(11) DEFAULT '0',
`length` int(11) DEFAULT '0',
`remaining` int(11) DEFAULT '0',
`eta` int(11) DEFAULT '0',
`runtime` int(11) DEFAULT '0',
`delta` int(11) DEFAULT '0',
`speed` int(11) DEFAULT '0',
`lines` int(11) DEFAULT '0',
PRIMARY KEY (`FileName`,`SessionUUID`,`RunId`,`Operation`),
CONSTRAINT `FileProgressDel` FOREIGN KEY (`FileName`, `SessionUUID`) REFERENCES `peps.files` (`Name`, `SessionUUID`) ON DELETE CASCADE ON UPDATE RESTRICT
) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
查询有什么问题?
1条答案
按热度按时间ryoqjall1#
谢谢你的更新。需要添加表定义和调用视图的主查询。现在我们看到
const
引用来自,它们是主查询的where子句中的术语。我注意到在解释中,它命令
file
前桌jrf
表,这很奇怪,因为它需要jrf
选择中匹配的行file
. 因为它加入了file
在获取相关文件名之前,它必须执行表扫描(type:ALL
)并检查file
table。这可能是表现不佳的根源。您看到的性能非常快的情况可能是例外情况,优化器按照正确的顺序进行连接,连接到
file
在它加入到jrf
.您可以尝试使用直接联接来强制表联接顺序。
我希望这能扭转局面
type:ALL
的join方法file
将表格放入type:eq_ref
(即主键查找),这将使rows:104300
进入rows:1
.我不知道为什么大部分时间都是按错误的顺序加入。也许是一些奇怪的行为。