programing

6 LEFT JOIN을 사용하는 Maria DB 쿼리가 매우 느림

telebox 2023. 9. 4. 19:43
반응형

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

인덱스가 있어서 왼쪽 조인을 변경할 수 없습니다.누가 이 질문을 개선하는 것을 도와줄 수 있습니까?잘 부탁드립니다.

아래의 설명을 참조하십시오.EXPLAIN for above query

문서는 아래의 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

SHOW CREATE TABLE 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_message:

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_message:

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 설명:

Updated Explain

이 모든 변경 사항으로 단 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 설명:

enter image description here

  • EXPLEX의 테이블 이름이 쿼리의 테이블과 일치하지 않습니다.고쳐주세요.

  • 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 *하위 쿼리에서는 사용하지 않는 부피가 큰 열을 끌어다 놓기 때문에 속도가 크게 느려질 수 있습니다.

언급URL : https://stackoverflow.com/questions/59944405/maria-db-query-with-6-left-join-very-slow

반응형

'programing' 카테고리의 다른 글

별도의 파일에 있는 db가 있는 mysqdump  (0) 2023.09.04
jDIV에 클래스 "x"가 없는 경우 쿼리  (0) 2023.09.04
MySQL 쿼리 최적화를 실행하기 위한 대규모 샘플 데이터베이스는 어디서 얻을 수 있습니까?  (0) 2023.09.04
PLS 오류 00363, Pl/SQL에서 텍스트 파일로 쓰는 방법  (0) 2023.09.04
C#과 Powershell로 무엇을 할 수 있습니까?  (0) 2023.09.04

'programing'의 다른글

  • 현재글6 LEFT JOIN을 사용하는 Maria DB 쿼리가 매우 느림

관련글

댓글

티스토리툴바