간단한 조인으로 MySQL/MariaDB COUNT(*) 행 속도가 매우 느림

간단한 조인으로 MySQL/MariaDB COUNT(*) 행 속도가 매우 느림

나는 두 개의 테이블을 결합하고 간단한 MySQL 쿼리로 반환된 행을 세고 있습니다.

SELECT SQL_NO_CACHE count(parc2.id) 
FROM SHIP__shipments AS ship 
JOIN SHIP__shipments_parcels AS parc2 ON ship.shipmentId = parc2.shipmentId 

결과를 제공하는 데 약 2초가 소요되며, 이는 약 800k 행입니다.기본 테이블에 cca가 있습니다.700k 행, 결합된 테이블에는 cca가 있습니다.80만 줄.

두 테이블 모두 인덱스가 있습니다.셀 수 없이 가입하는 것은 매우 빠릅니다, cca. 0.005초.테이블 하나만 세는 것도 0.01초 정도로 매우 빠릅니다.카운트와 조인이 동일한 쿼리에 포함되면 프로파일러의 "데이터 전송"에 99%의 시간을 할애하여 2초로 떨어집니다.

설명의 출력:

1   SIMPLE  ship    index   PRIMARY senderId    4   NULL    738700  Using index 
1   SIMPLE  parc2   ref shippmentId,shipmentId  shippmentId 4   ship.shipmentId 1   Using index 

저는 테스트하는 동안 많은 시도를 했습니다.결합된 키 사용, count(*) 사용, 인덱스 사용 강제..또한 하위 쿼리 사용 등과 같은 더 이국적인 방법도 있습니다.도움이 되는 건 아무것도 없어요. 항상 그렇게 느리거든요.

표:

   CREATE TABLE `SHIP__shipments` (
  `shipmentId` int(11) NOT NULL COMMENT 'generated ID',
  `externalId` varchar(255) DEFAULT NULL COMMENT 'spedition number',
  `senderId` int(11) NOT NULL COMMENT 'FK - sender address',
  `recipientId` int(11) DEFAULT NULL COMMENT 'Fk - recipient address',
  `customerId` int(11) NOT NULL COMMENT 'FK - custromer',
  `packageCount` int(11) NOT NULL COMMENT 'number of parcels',
  `shipmentPickupDate` datetime NOT NULL COMMENT 'when to pickup shipent',
  `shipmenmtDescription` varchar(255) DEFAULT NULL COMMENT 'free description',
  `codAmount` double DEFAULT NULL COMMENT 'COD to take',
  `codReference` varchar(255) DEFAULT NULL COMMENT 'customer''s COD refference',
  `codCurrencyCode` varchar(50) DEFAULT NULL COMMENT 'FK - currency',
  `codConfirmed` tinyint(1) NOT NULL COMMENT 'COD confirmed by spedition',
  `codSent` tinyint(1) NOT NULL COMMENT 'COD paid to customer? 1/0',
  `trackingCountryCode` varchar(50) NOT NULL COMMENT 'FK - country of shippment tracking',
  `subscriptionDate` datetime NOT NULL COMMENT 'when to enter to the sped. system',
  `speditionCode` varchar(50) NOT NULL COMMENT 'FK - spedition',
  `shipmentType` enum('DIRECT','WAREHOUSE') NOT NULL DEFAULT 'WAREHOUSE' COMMENT 'internal OLZA flag',
  `weight` decimal(10,3) NOT NULL COMMENT 'sum weight of parcells',
  `billingPrice` decimal(10,2) NOT NULL COMMENT 'stored price of delivery',
  `billingCurrencyCode` varchar(50) NOT NULL COMMENT 'storred currency of delivery price',
  `invoiceCreated` tinyint(1) NOT NULL COMMENT 'invoicing has been done? 1/0',
  `invoicingDate` datetime NOT NULL COMMENT 'date of creating invoice',
  `pickupPlaceId` varchar(100) DEFAULT NULL COMMENT 'pickup place ID, if applicable for shipment',
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `lastCheckDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'last date of status check'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='shippment details';

ALTER TABLE `SHIP__shipments`
  ADD PRIMARY KEY (`shipmentId`),
  ADD UNIQUE KEY `senderId` (`senderId`) USING BTREE,
  ADD UNIQUE KEY `externalId` (`externalId`,`trackingCountryCode`,`speditionCode`),
  ADD UNIQUE KEY `recipientId_2` (`recipientId`),
  ADD KEY `recipientId` (`recipientId`),
  ADD KEY `customerId` (`customerId`),
  ADD KEY `codCurrencyCode` (`codCurrencyCode`),
  ADD KEY `trackingCountryCode` (`trackingCountryCode`),
  ADD KEY `speditionCode` (`speditionCode`);

ALTER TABLE `SHIP__shipments`
  MODIFY `shipmentId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'generated ID';


ALTER TABLE `SHIP__shipments`
  ADD CONSTRAINT `SHIP__shipments_ibfk_3` FOREIGN KEY (`recipientId`) REFERENCES `SHIP__recipient_list` (`recipientId`),
  ADD CONSTRAINT `SHIP__shipments_ibfk_4` FOREIGN KEY (`customerId`) REFERENCES `CUST__customer_list` (`customerId`),
  ADD CONSTRAINT `SHIP__shipments_ibfk_5` FOREIGN KEY (`codCurrencyCode`) REFERENCES `SYS__currencies` (`code`),
  ADD CONSTRAINT `SHIP__shipments_ibfk_6` FOREIGN KEY (`trackingCountryCode`) REFERENCES `SYS__countries` (`code`),
  ADD CONSTRAINT `SHIP__shipments_ibfk_7` FOREIGN KEY (`speditionCode`) REFERENCES `SYS__speditions` (`code`),
  ADD CONSTRAINT `SHIP__shipments_ibfk_8` FOREIGN KEY (`senderId`) REFERENCES `SHIP__sender_list` (`senderId`);

CREATE TABLE `SHIP__shipments_parcels` (
  `id` int(11) NOT NULL COMMENT 'generated ID',
  `shipmentId` int(11) NOT NULL COMMENT 'FK - shippment',
  `externalNumber` varchar(255) DEFAULT NULL COMMENT 'number from spedition',
  `externalBarcode` varchar(255) DEFAULT NULL COMMENT 'Barcode ID - external reference',
  `status` varchar(100) DEFAULT NULL COMMENT 'FK - current status',
  `weigth` decimal(10,3) NOT NULL COMMENT 'weight of parcel',
  `weightConfirmed` tinyint(1) NOT NULL COMMENT 'provided weight has been confirmed/updated by measuring',
  `parcelType` varchar(255) NOT NULL COMMENT 'foreign key',
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='data and relations between shippment and it''s parcels';

ALTER TABLE `SHIP__shipments_parcels`
  ADD PRIMARY KEY (`id`),
  ADD KEY `shippmentId` (`shipmentId`,`status`),
  ADD KEY `status` (`status`),
  ADD KEY `parcelType` (`parcelType`),
  ADD KEY `externalBarcode` (`externalBarcode`),
  ADD KEY `weightConfirmed` (`weightConfirmed`),
  ADD KEY `externalNumber` (`externalNumber`),
  ADD KEY `shipmentId` (`shipmentId`);


ALTER TABLE `SHIP__shipments_parcels`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'generated ID';

ALTER TABLE `SHIP__shipments_parcels`
  ADD CONSTRAINT `SHIP__shipments_parcels_ibfk_2` FOREIGN KEY (`status`) REFERENCES `SHIP__statuses` (`statusCode`),
  ADD CONSTRAINT `SHIP__shipments_parcels_ibfk_3` FOREIGN KEY (`shipmentId`) REFERENCES `SHIP__shipments` (`shipmentId`),
  ADD CONSTRAINT `SHIP__shipments_parcels_ibfk_4` FOREIGN KEY (`parcelType`) REFERENCES `SHIP__parcel_types` (`parcelType`);

서버는 SSD 디스크에서 실행되고 있으며 여기에서는 많은 데이터에 대해 언급하고 있지 않습니다.내가 뭘 빠트렸나요?아니면 2초가 실시간 행 계산입니까?

카운트 결과를 0.01초와 같은 "정상" 시간으로 얻을 수 있습니까?

우리는 MariaDB 10을 운영하고 있습니다.

분석.

몇 개의 기둥을 해부해 보겠습니다.EXPLAIN:

`shipmentId` int(11)  (*3)  NOT NULL COMMENT 'generated ID',
`senderId`   int(11)  (*3)  NOT NULL COMMENT 'FK - sender address',


1   SIMPLE  ship    index   PRIMARY 
  senderId (*2)    4   NULL        738700  Using index (*1)
1   SIMPLE  parc2   ref shippmentId,shipmentId
  shippmentId (*4) 4   ship.shipmentId 1   Using index (*1) 

SELECT ... count(parc2.id)  (*5)  ...  STRAIGHT_JOIN (*6)  ...

주의:

*1 -- 둘 다Using index이것은 많은 도움이 될 것 같습니다.
*2 --INDEX(senderId)아마도 "위험" 지수일 것입니다.InnoDB를 사용하고 있습니다.PK는 데이터와 함께 "클러스터"되므로 "소규모"가 아닙니다.모든 보조 인덱스에는 PK가 암시적으로 고정되어 있으므로 효과적입니다.(senderId, shipmentId)이것은 옵티마이저가 신비롭게 선택한 이유를 설명합니다.INDEX(senderId).
*3 --INT최대 +/- 20억 개의 숫자를 허용하는 4바이트를 사용합니다.그렇게 많은 발송인과 발송물이 있을 것으로 예상하십니까?데이터 유형 축소(및 생성)UNSIGNED공간과 I/O를 절약하여 작업 속도를 약간 높일 수 있습니다.
*4 --INDEX(shipmentId)실제로는INDEX(shipmentId, id)다시 2개의 INT.
*5 --COUNT(x)수표x일단은NOT NULL이것은 아마도 당신의 애플리케이션에서 불필요할 것입니다.로 변경COUNT(*)Null 검사가 필요하지 않은 경우. (성능 차이는 경미합니다.)
*6 - 사용 가능한 색인을 제외하고, 어떤 테이블을 먼저 선택하는지는 중요하지 않습니다.이런 이유로,STRAIGHT_JOIN도움이 되지 않았습니다.

이제는 어떻게 그것이JOIN효과가 있습니다. 사실상 모든.JOINsMySQL에는 "NLJ"(내포 루프 조인)가 있습니다.이것은 코드가 테이블 중 하나(실제로는 테이블 하나에 대한 인덱스일 뿐)를 통과한 다음 발견된 각 행에 대해 다른 테이블(인덱스일 뿐)에 도달하는 방식입니다.

를 것COUNT(*)행의 존재만 확인하면 됩니다.

그래서, 그것은 2개의 열을 통과했습니다.INDEX(senderId, shipmentId) shipmentIds1】그하는 데 하지 않았습니다.그 목록을 정렬하거나 중복제거하는 데 시간을 낭비하지 않았습니다.그리고 그 이후로.shipmentId는 PK, 즉 PK입니다.UNIQUE것입니다.), 더는없것입니다을프다▁),니.

각에 shipmentId그리고 나서 그것은 두 번째 테이블의 모든 행을 찾아보았습니다.은 그은효이니다습 때문에 이었습니다.INDEX(shipmentId, id).

I/O(또는 없음)

다른 문제로 넘어가죠.I/O가 있었습니까?? RAM에 캐시되었습니까?의 가치는 무엇입니까?innodb_buffer_pool_size?

InnoDB가 테이블 또는 인덱스에서 행을 가져오는 방법은 먼저 행이 "버퍼 풀"에 있는지 확인하는 것입니다.이 블록이 없으면 버퍼 풀에서 무언가를 꺼내어 원하는 16KB 블록을 버퍼 풀로 읽어들여야 합니다.

한 극단에서는 버퍼 풀에 아무것도 없으며 디스크에서 모든 블록을 읽어야 합니다.다른 극단적인 경우에는 모든 것이 캐시되므로 I/O가 필요하지 않습니다.당신이 여러 가지 시도를 해봤기 때문에, 관련 블록(그 두 인덱스)은 모두 RAM에 있었던 것 같습니다.

INT 2개 * (800K + 700K 행) + 일부 오버헤드 = 50MB일 수 있습니다.을 가정하여innodb_buffer_pool_size그 이상이며 스왑이 발생하지 않은 경우 I/O가 없는 것이 합리적입니다.

따라서 완전히 캐시된 150만 개의 행을 한 번에 터치하는 데 얼마나 시간이 걸릴까요?JOIN아아, 2초가 적당해 보입니다.

사용자 기대치

수백만 단위의 정확한 최신 카운트가 필요한 경우는 거의 없습니다.사용자 요구 사항을 다시 생각합니다.또는 가치를 사전 계산하는 방법에 대해 논의할 수 있습니다.아니면 그냥 무시해도 됩니다.

사이드 노트

(이것들은 당면한 질문에 영향을 미치지 않습니다.)

• 맹목적으로 사용하지 않음255모든 조건에 대하여
• UNIQUE(x)입니다.INDEX그러니 또한 가지고 있지 마세요.INDEX(x).
• 이상 의 보유를 가지는 것.PRIMARY또는UNIQUE인덱스는 일반적으로 스키마의 설계 오류입니다.
• 일부 열은 정규화될 수 있습니다. 예:parcelType?
• 사용하지 FLOAT또는DOUBLE가치를 ; 적가를위여하치사용금전; 용사▁for;를 사용합니다.DECIMAL. (weight떠 있을 수 있습니다.)

언급URL : https://stackoverflow.com/questions/59363317/simple-join-makes-mysql-mariadb-count-rows-very-slow