Bootstrap
登录 注册

在MySQL上进行相同的查询需要不到一秒钟或数十分钟的时间（InnoDB引擎）

mysqlsqlquery-optimization
发布时间：2020-07-07 15:48

当数据库中没有其他操作时，我用相同的参数在相同的用户名下多次执行相同的查询；服务器大部分处于空闲状态。具有InnoDB引擎的Windows服务器（Azure框）上的MySQL版本是8.15.0。

大多数情况下，查询执行将永远耗时-这意味着相应的线程在“发送数据”状态下保持30分钟以上，然后我将其杀死。

但有时它会在0,002秒内产生结果（据我所知）

领先的表存储着约10万条记录，其中一个涉及的表将近一百万条。

原始选择是：

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

该选择在具有MERGE算法的视图下运行，其基础选择如下：

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时，它表明它计划使用PRIMARY或外键索引。 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

感谢更新。需要添加表定义和调用视图的主查询。现在，我们看到const引用来自哪里，它们是主查询的WHERE子句中的术语。

我在EXPLAIN中注意到，它在file表之前对jrf表进行排序，这很奇怪，因为它需要jrf中的文件名才能选择{{ 1}}。由于它在获得相关文件名之前已加入file，因此被迫进行表扫描（file）并检查type:ALL表中的104,300行。这可能是表现不佳的原因。

您看到非常快的性能的情况可能是例外情况，其中优化程序以适当的顺序进行连接，并在连接到file之后再连接到file。

您可以尝试使用STRAIGHT_JOIN强制执行表连接顺序。

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连接方法转换为file（即主键查找），并将type:eq_ref转换为{{ 1}}。

我不知道为什么大多数时候它以错误的顺序进行联接。也许有些奇怪的VIEW行为。

mysql 相关推荐
sql 相关推荐
query-optimization 相关推荐