mysql - 带有 6 LEFT JOIN 的 Maria DB 查询非常慢

问题描述

我有以下运行缓慢的查询：

SELECT SQL_CALC_FOUND_ROWS * 
FROM   (SELECT d.*, 
               IF(Isnull(r.areaid), 0, r.areaid) AreaId, 
               r.routekey, 
               de1.id                            RecordId, 
               de1.packagecount                  RecordPackageCount, 
               de1.contactname                   RecordContactName, 
               de1.remarks                       RecordRemarks, 
               de1.email                         RecordEmail, 
               de1.signaturefile                 RecordSignatureFile, 
               Count(dep.photofile)              RecordPhotoCount, 
               de1.geolatitude                   RecordGeoLatitude, 
               de1.geolongitude                  RecordGeoLongitude, 
               de1.geoaccuracy                   RecordGeoAccuracy, 
               de1.createuserid                  RecordCreateUserId, 
               de1.updateuserid                  RecordUpdateUserId, 
               de1.sourcecreatedatetimelocal     RecordLocalDateTime, 
               de1.failed                        RecordFailed, 
               de1.reasoncode                    RecordReasonCode, 
               u.displayname                     RecordCreateUser, 
               v.weightcapacity                  VehicleWeightCapacity, 
               v.volumecapacity                  VehicleVolumeCapacity 
        FROM   document_not_deleted d 
               LEFT JOIN route_not_deleted r 
                      ON d.routeid = r.id 
               LEFT JOIN area_not_deleted a 
                      ON r.areaid = a.id 
               LEFT JOIN vehicle_not_deleted v 
                      ON r.vehicleid = v.id 
               LEFT JOIN document_record_max_per_document de1 
                      ON d.id = de1.documentid 
               LEFT JOIN document_record_photo_not_deleted dep 
                      ON de1.id = dep.documentrecordid 
               LEFT JOIN user u 
                      ON de1.createuserid = u.id 
        WHERE  d.organisationid = 160 
        GROUP  BY d.id) T0 
ORDER  BY id DESC

我有索引，我无法更改任何左连接。谁能帮我改进这个查询？提前致谢。

请看下面的解释：解释上述查询

请参阅下面的 SHOW CREATE TABLE 文档：

  CREATE TABLE `document` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `OrganisationId` int(10) unsigned NOT NULL,
  `DocumentKey` varchar(100) NOT NULL,
  `DocumentType` char(1) NOT NULL COMMENT '"D" or "C" (drop or collection)',
  `RouteId` int(11) DEFAULT NULL,
  `DeliveryDate` date NOT NULL COMMENT 'Required delivery date',
  `PlannedDeliveryDate` date DEFAULT NULL COMMENT 'Planned delivery date from route',
  `Stop` smallint(6) DEFAULT NULL,
  `Weight` double NOT NULL DEFAULT 0,
  `Volume` double NOT NULL DEFAULT 0,
  `PackageCount` smallint(6) DEFAULT NULL,
  `CustomerCode` varchar(50) DEFAULT NULL,
  `CustomerName` varchar(100) DEFAULT NULL,
  `ContactName` varchar(100) DEFAULT NULL,
  `Address1` varchar(100) DEFAULT NULL,
  `Address2` varchar(100) DEFAULT NULL,
  `Address3` varchar(100) DEFAULT NULL,
  `City` varchar(100) DEFAULT NULL,
  `PostalCode` varchar(100) DEFAULT NULL,
  `Country` varchar(100) DEFAULT NULL,
  `AddressLatitude` double DEFAULT NULL,
  `AddressLongitude` double DEFAULT NULL,
  `AddressLocationAccuracy` int(11) DEFAULT NULL,
  `Phone` varchar(100) DEFAULT NULL,
  `Email` varchar(100) DEFAULT NULL,
  `Instructions` varchar(1000) DEFAULT NULL,
  `PlanningInstructions` varchar(1000) DEFAULT NULL,
  `CommittedETA` datetime DEFAULT NULL,
  `TimeAtStop` time NOT NULL DEFAULT '00:00:01',
  `CreateUserId` int(10) unsigned NOT NULL DEFAULT 0,
  `UpdateUserId` int(10) unsigned NOT NULL DEFAULT 0,
  `SourceCreateDatetimeUTC` datetime NOT NULL,
  `SourceCreateDatetimeLocal` datetime NOT NULL,
  `SourceCreateTimezone` varchar(50) NOT NULL,
  `CreateDatetimeUTC` datetime NOT NULL,
  `CreateDatetimeLocal` datetime NOT NULL,
  `CreateTimezone` varchar(50) NOT NULL,
  `UpdateDatetimeUTC` datetime NOT NULL,
  `UpdateDatetimeLocal` datetime NOT NULL,
  `UpdateTimezone` varchar(50) NOT NULL,
  `Completed` bit(1) NOT NULL DEFAULT b'0',
  `Export` bit(1) NOT NULL DEFAULT b'0',
  `RecordExport` bit(1) NOT NULL DEFAULT b'0',
  `Deleted` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`Id`),
  KEY `CreateUserId` (`CreateUserId`),
  KEY `UpdateUserId` (`UpdateUserId`),
  KEY `CustomerCode` (`CustomerCode`),
  KEY `PostalCode` (`PostalCode`),
  KEY `Country` (`Country`),
  KEY `RouteId` (`RouteId`),
  KEY `Deleted` (`Deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=651196 DEFAULT CHARSET=utf8

显示创建表路线：

  CREATE TABLE `route` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `OrganisationId` int(11) unsigned NOT NULL COMMENT 'Foreign key to Organisation.Id',
  `RouteKey` varchar(50) NOT NULL,
  `AreaId` int(11) unsigned NOT NULL,
  `DepotId` int(11) unsigned DEFAULT NULL,
  `Trip` int(11) NOT NULL COMMENT 'Trip name',
  `UserId` int(11) DEFAULT NULL COMMENT 'Foreign key to User.Id',
  `VehicleId` int(11) DEFAULT NULL COMMENT 'Foreign key to Vehicle.Id',
  `StartDate` datetime NOT NULL COMMENT 'Trip start date',
  `EndDate` datetime NOT NULL,
  `TotalDuration` int(10) unsigned DEFAULT NULL,
  `TotalDistance` int(10) unsigned DEFAULT NULL,
  `CreateUserId` int(11) unsigned NOT NULL DEFAULT 0,
  `UpdateUserId` int(11) unsigned NOT NULL DEFAULT 0,
  `CreateDatetimeUTC` datetime NOT NULL,
  `CreateDatetimeLocal` datetime NOT NULL,
  `CreateTimezone` varchar(50) NOT NULL,
  `UpdateDatetimeUTC` datetime NOT NULL,
  `UpdateDatetimeLocal` datetime NOT NULL,
  `UpdateTimezone` varchar(50) NOT NULL,
  `Published` bit(1) NOT NULL DEFAULT b'0',
  `Completed` bit(1) NOT NULL DEFAULT b'0',
  `Deleted` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`Id`),
  KEY `OrganisationId` (`OrganisationId`),
  KEY `VehicleId` (`VehicleId`),
  KEY `CreateUserId` (`CreateUserId`),
  KEY `UpdateUserId` (`UpdateUserId`),
  KEY `UserId` (`UserId`),
  KEY `Completed` (`Completed`),
  KEY `Deleted` (`Deleted`),
  KEY `AreaId` (`AreaId`),
  KEY `Published` (`Published`)
) ENGINE=InnoDB AUTO_INCREMENT=16173 DEFAULT CHARSET=utf8

显示创建表车辆：

  CREATE TABLE `vehicle` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `OrganisationId` int(11) unsigned NOT NULL COMMENT 'Foreign key to Organisation.Id',
  `VehicleKey` varchar(20) NOT NULL COMMENT 'User-visible vehicle identifier',
  `Registration` varchar(20) DEFAULT NULL COMMENT 'E.g. number plate',
  `WeightCapacity` double NOT NULL DEFAULT 0,
  `VolumeCapacity` double NOT NULL DEFAULT 0,
  `SpeedFactor` double NOT NULL DEFAULT 1,
  `Description` varchar(250) DEFAULT NULL,
  `CreateUserId` int(11) unsigned NOT NULL DEFAULT 0,
  `UpdateUserId` int(11) unsigned NOT NULL DEFAULT 0,
  `CreateDatetimeUTC` datetime NOT NULL,
  `CreateDatetimeLocal` datetime NOT NULL,
  `CreateTimezone` varchar(50) NOT NULL,
  `UpdateDatetimeUTC` datetime NOT NULL,
  `UpdateDatetimeLocal` datetime NOT NULL,
  `UpdateTimezone` varchar(50) NOT NULL,
  `Deleted` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`Id`),
  KEY `OrganisationId` (`OrganisationId`),
  KEY `CreateUserId` (`CreateUserId`),
  KEY `UpdateUserId` (`UpdateUserId`),
  KEY `Deleted` (`Deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=173 DEFAULT CHARSET=utf8

显示创建表 document_record_photo：

    CREATE TABLE `document_record_photo` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `DocumentRecordId` int(11) unsigned DEFAULT NULL,
  `PhotoFile` varchar(250) NOT NULL,
  `Deleted` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`Id`),
  KEY `DocumentRecordId` (`DocumentRecordId`),
  KEY `Deleted` (`Deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=158887 DEFAULT CHARSET=utf8

document_record_max_per_document：

Select dr2.Id Id
     , dr2.DocumentId DocumentId
     , dr2.PackageCount PackageCount
     , dr2.ContactName ContactName
     , dr2.Remarks Remarks
     , dr2.Email Email
     , dr2.ReasonCode ReasonCode
     , dr2.SignatureFile SignatureFile
     , dr2.PhotoFile PhotoFile
     , dr2.GeoLatitude GeoLatitude
     , dr2.GeoLongitude GeoLongitude
     , dr2.GeoAccuracy GeoAccuracy
     , dr2.CreateUserId CreateUserId
     , dr2.UpdateUserId UpdateUserId
     , dr2.SourceCreateDatetimeUTC SourceCreateDatetimeUTC
     , dr2.SourceCreateDatetimeLocal SourceCreateDatetimeLocal
     , dr2.SourceCreateTimezone SourceCreateTimezone
     , dr2.CreateDatetimeUTC CreateDatetimeUTC
     , dr2.CreateDatetimeLocal CreateDatetimeLocal
     , dr2.CreateTimezone CreateTimezone
     , dr2.UpdateDatetimeUTC UpdateDatetimeUTC
     , dr2.UpdateDatetimeLocal UpdateDatetimeLocal
     , dr2.UpdateTimezone UpdateTimezone
     , dr2.Failed Failed
     , dr2.Deleted Deleted 
  from document_record_max_id_per_document dr1 
  join document_record dr2 
    on dr1.Id = dr2.Id

document_record_max_id_per_document：

SELECT x.DocumentId 
     , MAX(x.Id) Id 
  FROM document_record_not_deleted 
 GROUP 
    BY x.DocumentId

更新了 1 个查询

SELECT SQL_CALC_FOUND_ROWS * FROM
          (
           SELECT d.*, COALESCE(r.AreaId,0) AreaId, r.RouteKey, de1.Id RecordId, 
           de1.PackageCount RecordPackageCount, de1.ContactName RecordContactName, de1.Remarks RecordRemarks, 
           de1.Email RecordEmail, de1.SignatureFile RecordSignatureFile, COUNT(dep.PhotoFile) RecordPhotoCount, 
           de1.GeoLatitude RecordGeoLatitude, de1.GeoLongitude RecordGeoLongitude, de1.GeoAccuracy RecordGeoAccuracy, 
           de1.CreateUserId RecordCreateUserId, de1.UpdateUserId RecordUpdateUserId, 
           de1.SourceCreateDatetimeLocal RecordLocalDateTime, de1.Failed RecordFailed, de1.ReasonCode RecordReasonCode, 
           u.DisplayName RecordCreateUser, v.WeightCapacity VehicleWeightCapacity, v.VolumeCapacity VehicleVolumeCapacity
           FROM document d
           LEFT JOIN (SELECT * FROM route WHERE Deleted = 0) r ON d.RouteId = r.Id
           LEFT JOIN (SELECT * FROM vehicle WHERE Deleted = 0) v ON r.VehicleId = v.Id
           LEFT JOIN (SELECT dr2.* FROM (SELECT DocumentId, max(Id) Id FROM document_record WHERE Deleted = 0 GROUP BY DocumentId) dr1
                JOIN document_record dr2 ON dr1.Id = dr2.Id) de1 ON d.Id = de1.DocumentId
           LEFT JOIN (SELECT * FROM document_record_photo WHERE Deleted = 0) dep ON de1.Id = dep.DocumentRecordId
           LEFT JOIN user u ON de1.CreateUserId = u.Id
           WHERE d.OrganisationId = 160
           AND d.Deleted = 0
                GROUP BY d.Id ) T0  
                ORDER BY Id DESC

更新 1 解释：

所有这些更改仅节省了 1 秒

更新了 2 个查询：

SELECT SQL_CALC_FOUND_ROWS * FROM
          (
            SELECT d.*, COALESCE(r.AreaId,0) AreaId, r.RouteKey, de1.Id RecordId,
                  de1.PackageCount RecordPackageCount, de1.ContactName RecordContactName, de1.Remarks RecordRemarks,
                  de1.Email RecordEmail, de1.SignatureFile RecordSignatureFile, COUNT(dep.PhotoFile) RecordPhotoCount,
                  de1.GeoLatitude RecordGeoLatitude, de1.GeoLongitude RecordGeoLongitude, de1.GeoAccuracy RecordGeoAccuracy,
                  de1.CreateUserId RecordCreateUserId, de1.UpdateUserId RecordUpdateUserId,
                  de1.SourceCreateDatetimeLocal RecordLocalDateTime, de1.Failed RecordFailed, de1.ReasonCode RecordReasonCode,
                  u.DisplayName RecordCreateUser, v.WeightCapacity VehicleWeightCapacity, v.VolumeCapacity VehicleVolumeCapacity
                 FROM document d
                 LEFT JOIN route r ON r.Id = d.RouteId 
                    AND r.Deleted = 0
                 LEFT JOIN vehicle v ON v.Id = r.VehicleId 
                    AND v.Deleted = 0
                 LEFT JOIN ( SELECT dr2.* FROM document_record dr2 JOIN ( SELECT DocumentId, max(Id) Id FROM document_record WHERE Deleted = 0 
            GROUP BY DocumentId ) dr1 ON dr1.Id = dr2.Id ) de1 ON de1.DocumentId = d.Id 
                 LEFT JOIN document_record_photo dep ON dep.DocumentRecordId = de1.Id 
                    AND dep.Deleted = 0 
                 LEFT JOIN user u ON u.Id = de1.CreateUserId WHERE d.OrganisationId = 16 
                    AND d.Deleted = 0
                 GROUP BY d.Id 
               ) T0  
                ORDER BY Id DESC

更新 2 解释：

标签： mysqlmariadb

解决方案

EXPLAIN 中的表名与查询中的表不一致。请修复。
document_not_deleted需要INDEX(organisationid)

不要这样做。

SELECT SQL_CALC_FOUND_ROWS * 
          FROM   (SELECT ... )

相反，做
```
SELECT SQL_CALC_FOUND_ROWS ...
```
SQL_CALC_FOUND_ROWS但是，除非您有LIMIT子句，否则不要使用。
de1需要INDEX(documentid)
使用COUNT(*)而不是Count(dep.photofile). 已知是NOT NULL，因此无需检查。
不要SELECT *在子查询中使用；由于拖着未使用的笨重的柱子，它可能会显着减慢速度。

mysql - 带有 6 LEFT JOIN 的 Maria DB 查询非常慢

问题描述

解决方案

推荐阅读