mysql上的同一查询需要不到一秒或几十分钟(innodb引擎)

cdmah0mi  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(255)

当数据库中没有其他操作时，我在同一用户名下用完全相同的参数执行同一查询几次；服务器大部分空闲。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

查询有什么问题？

sqlmysqlquery-optimization

1条答案

按热度按时间
ryoqjall

ryoqjall1#

谢谢你的更新。需要添加表定义和调用视图的主查询。现在我们看到 const 引用来自，它们是主查询的where子句中的术语。
我注意到在解释中，它命令 file 前桌 jrf 表，这很奇怪，因为它需要 jrf 选择中匹配的行 file . 因为它加入了 file 在获取相关文件名之前，它必须执行表扫描( type:ALL )并检查 file table。这可能是表现不佳的根源。
您看到的性能非常快的情况可能是例外情况，优化器按照正确的顺序进行连接，连接到 file 在它加入到 jrf .
您可以尝试使用直接联接来强制表联接顺序。

...
FROM
`peps.JobRunFiles` as jrf
STRAIGHT_JOIN `peps.ImportSession` AS job 
  ON job.`UUID` = jrf.`SessionUUID`
STRAIGHT_JOIN `peps.Files` AS file 
  ON file.`Name` = jrf.`FileName`
  AND file.`SessionUUID` = jrf.`SessionUUID`

我希望这能扭转局面 type:ALL 的join方法 file 将表格放入 type:eq_ref （即主键查找），这将使 rows:104300 进入 rows:1 .
我不知道为什么大部分时间都是按错误的顺序加入。也许是一些奇怪的行为。

赞(0）分享  回复(0）举报  2021-07-24
我来回答

相关问题

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com