MariaDB: 내부 쿼리 결과에 PK 사용

MariaDB: 내부 쿼리 결과에 PK 사용

세 개의 테이블(MariaDB 10.6.5)이 있습니다. 사람, 개인_사람 및 기업_사람입니다.사용자에게는 ID가 저장되어 있고, 다른 테이블에는 이름이 저장되어 있으며, 둘 다 FK당 사용자에게 연결되어 있습니다.

CREATE TABLE `person` (
    `Id` INT(11) NOT NULL AUTO_INCREMENT,
    `TypeOfPerson` ENUM('PRIVATE','CORPORATE') NOT NULL,
    PRIMARY KEY (`Id`)
) ENGINE=InnoDB;

CREATE TABLE `private_person` (
    `PersonId` INT(11) NOT NULL,
    `FirstName` VARCHAR(255) NULL DEFAULT NULL,
    `LastName` VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`PersonId`),
    INDEX `IX_private_person_FirstName` (`FirstName`),
    INDEX `IX_private_person_LastName` (`LastName`),
    CONSTRAINT `FK_private_person_person_PersonId` FOREIGN KEY (`PersonId`) REFERENCES `person` (`Id`) ON UPDATE RESTRICT ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE `corporate_person` (
    `PersonId` INT(11) NOT NULL,
    `Name` VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`PersonId`),
    INDEX `IX_corporate_person_Name` (`Name`),
    CONSTRAINT `FK_corporate_person_person_PersonId` FOREIGN KEY (`PersonId`) REFERENCES `person` (`Id`) ON UPDATE RESTRICT ON DELETE RESTRICT
) ENGINE=InnoDB;

이제 private_person 및 corporate_person 테이블에서 이름을 모두 검색해야 합니다.

SELECT `p`.Id
  FROM `test`.`person` AS `p`
  LEFT JOIN `test`.`private_person` AS `p0` ON `p`.`Id` = `p0`.`PersonId`
  LEFT JOIN `test`.`corporate_person` AS `c0` ON `p`.`Id` = `c0`.`PersonId`
  WHERE `p0`.`FirstName` = 'Test' OR p0.LastName = 'Test' OR `c0`.`Name` = 'Test';

하지만 실제로 많은 행이 있기 때문에 쿼리가 조금 느립니다.

쿼리를 다음에서 변경했습니다.

SELECT Id FROM `test`.`person` WHERE Id IN (
SELECT p.Id
  FROM `test`.`person` AS `p`
  INNER JOIN `test`.`private_person` AS `p0` ON `p`.`Id` = `p0`.`PersonId`
  WHERE `p0`.`FirstName` = 'Test' OR `p0`.`LastName` = 'Test'
UNION SELECT p.Id
  FROM `test`.`person` AS `p`
  INNER JOIN `test`.`corporate_person` AS `c0` ON `p`.`Id` = `c0`.`PersonId`
  WHERE `c0`.`Name` = 'Test' ORDER BY Id);

내부 쿼리(UNION)는 매우 빠르지만 전체 문장도 느립니다.

그리고 나는 왜 그런지 이해할 수 없습니다.내부 쿼리는 ID의 양만 제공하며, 옵티마이저는 이러한 단순한 ID의 양에 대해 기본 인덱스를 사용하지 않는 이유는 무엇입니까?내부 쿼리 대신 ID를 지정할 때

SELECT Id FROM `test`.`person` WHERE Id IN (25251, 47413, 99851 ...);

물론 진술도 빠릅니다.

지수를 해도 (1차 지수)SELECT Id FROM test.person FORCE INDEX (PRIMARY) WHERE ...) 아무것도 변경하지 않습니다. 쿼리 최적화 프로그램에 따르면 주 인덱스가 현재 사용되지만 문이 더 빠르지는 않습니다.

하위 쿼리에서 ID의 양만 가져오는 경우 옵티마이저가 주 인덱스를 빠른(더 빠른) 방식으로 사용하지 않는 이유는 무엇입니까?

편집: 오해를 해서 죄송합니다.쿼리를 더 빨리 만들고 싶지는 않습니다. 사실 이전에 구체적인 문제(여기서 설명한 것보다 더 복잡한 시나리오에서 느린 쿼리)에 대한 해결책이 있었습니다. 아마도 이 문제를 좀 더 명확하게 쓰는 것을 놓쳤을 수도 있습니다.하지만 진술서를 작성하는 동안, 저는 mysql과 최적화기에 대한 제 지식을 사용하려고 노력했습니다. 여기서 저는 매우 놀랐습니다. mariadb의 문제가 어디에 있는지 이해할 수 없습니다.OUTER 문은 ID 집합만 가져오고 PK를 적절한 방식으로 사용할 수 없습니다.SELECT Id FROM tabA WHERE Id IN (123, 456, 789) PK는 사용됩니다.SELECT Id FROM tabA WHERE Id IN (SELECT Id FROM tabB WHERE ...)PK가 적절한 방식으로 사용되지 않는 경우, 최적화 도구가 전체 테이블 탭 A를 크롤링합니다.왜 그럴까요?그게 제가 하려던 질문입니다.

그은입니다.OR당신의 질문을 망치는 작전.

  WHERE `p0`.`FirstName` = 'Test' OR p0.LastName = 'Test' OR `c0`.`Name` = 'Test';

MySQL이 테이블 참조당 하나의 인덱스만 사용한다는 점에서 쿼리 최적화와 관련된 일반적인 문제입니다(인덱스 병합 최적화가 존재하더라도 생각만큼 자주 실행되지는 않습니다).

문제는 최적화 도구가 단일 B-트리 인덱스를 사용하여 이와 같은 여러 열을 검색할 수 없다는 것입니다.

제가 자주 사용하는 비유는 전화번호부입니다.사용자를 성으로 검색하려면 전화번호부의 순서를 사용하여 전화번호부를 효율적으로 검색할 수 있습니다. 전화번호부는 성으로 먼저 알파벳화되어 있기 때문입니다.그러나 이름으로 사람을 검색하려는 경우 항목이 이름으로 정렬되지 않기 때문에 이 책은 도움이 되지 않습니다.성이나 이름으로 사람을 찾으려면 이름으로 일치하는 사람을 찾기 위해 책 전체를 스캔해야 합니다.

이름별로 정렬된 두 번째 전화번호부가 있다고 가정합니다.도움이 될 수도 있지만, 두 책 모두가 아닌 한 권 또는 다른 책만을 사용하여 검색하도록 강요하는 규칙에 의해 제한을 받는다면, 당신은 꼼짝 못하게 될 것입니다.어떤 책을 선택하든 다른 이름을 검색하려면 전체 책을 스캔해야 합니다.

가 테이블 참조당 하나의 인덱스만 허용하는 합니다.OR조건을 입력합니다.

많은 사람들이 사용하는 해결 방법은 여러 가지 쿼리를 수행하는 것입니다.UNION그들의 결과

SELECT `p`.Id
FROM `test`.`person` AS `p`
INNER JOIN `test`.`private_person` AS `p0` ON `p`.`Id` = `p0`.`PersonId`
WHERE `p0`.`FirstName` = 'Test'
UNION
SELECT `p`.Id
FROM `test`.`person` AS `p`
INNER JOIN `test`.`private_person` AS `p0` ON `p`.`Id` = `p0`.`PersonId`
WHERE p0.LastName = 'Test'
UNION
SELECT `p`.Id
FROM `test`.`person` AS `p`
INNER JOIN `test`.`corporate_person` AS `c0` ON `p`.`Id` = `c0`.`PersonId`
WHERE `c0`.`Name` = 'Test';

이 경우 각 테이블 참조는 서로 다른 색인을 사용할 수 있으며, 원하는 이름을 보다 효율적으로 검색할 수 있습니다.일치하는 행의 작은 집합을 찾으면 해당 행을 다시 연결합니다.person테이블의 기본 키를 사용한 테이블(MySQL은 쿼리에 나열된 테이블과 다른 순서로 테이블에 액세스하는 방법을 알고 있으므로 조인에서 어떤 테이블의 이름이 먼저 지정되는지 걱정할 필요가 없습니다.)

---

댓글:

때때로 옵티마이저는 비용 추정 모델을 기반으로 어떤 테이블에 먼저 액세스하는 것이 더 나은지에 대해 테이블을 주문하는 방법에 대해 이상한 결정을 내립니다.다음과 같이 재정의할 수 있습니다.

SELECT `p`.Id
FROM `private_person` AS `p0` 
STRAIGHT_JOIN `person` AS `p` ON `p`.`Id` = `p0`.`PersonId`
WHERE `p0`.`FirstName` = 'Test'
UNION
SELECT `p`.Id
FROM `private_person` AS `p0`
STRAIGHT_JOIN `person` AS `p` ON `p`.`Id` = `p0`.`PersonId`
WHERE p0.LastName = 'Test'
UNION
SELECT `p`.Id
FROM `corporate_person` AS `c0` 
STRAIGHT_JOIN `person` AS `p` ON `p`.`Id` = `c0`.`PersonId`
WHERE `c0`.`Name` = 'Test';

그STRAIGHT_JOINSQL 조회에 나타나는 대로 테이블 순서를 사용합니다.

이를 통해 EXPLE 출력을 테스트하고 다음과 같은 결과를 얻었습니다.

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: p0
   partitions: NULL
         type: ref
possible_keys: PRIMARY,IX_private_person_FirstName
          key: IX_private_person_FirstName
      key_len: 1023
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: p
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test2.p0.PersonId
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 2
  select_type: UNION
        table: p0
   partitions: NULL
         type: ref
possible_keys: PRIMARY,IX_private_person_LastName
          key: IX_private_person_LastName
      key_len: 1023
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 4. row ***************************
           id: 2
  select_type: UNION
        table: p
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test2.p0.PersonId
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 5. row ***************************
           id: 3
  select_type: UNION
        table: c0
   partitions: NULL
         type: ref
possible_keys: PRIMARY,IX_corporate_person_Name
          key: IX_corporate_person_Name
      key_len: 1023
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 6. row ***************************
           id: 3
  select_type: UNION
        table: p
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test2.c0.PersonId
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 7. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2,3>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using temporary

이렇게 하면 인덱스를 더 잘 사용하고 테이블에 더 좋은 순서로 액세스할 수 있습니다.

제가 방금 빈 테이블로 테스트했다는 경고와 함께, 때때로 최적화 도구가 비용을 계산하는 방법에 대해 혼동을 일으킵니다.사용자 환경에서 사용해 보십시오.

당신의 유니온 사건에서SELECT Id FROM 을 하다. WHERE Id IN불필요합니다.비효율성의 원인일 수 있습니다.

단순히 필요한 것 이상의 것이 필요합니다.Id대신 결과에서 다음을 수행합니다.

SELECT ...  -- (more than just Id)
    FROM  ( (SELECT ...)
            UNION ALL
            (SELECT ...)
          ) AS x;

그것은 본질적으로 외부 선택에 대해 생각하기 전에 연합을 하도록 강요합니다.

참고:UNION ALL(상세 정보에 따라) 오류가 발생할 수 있지만 보다 빠릅니다.UNION(즉,UNION DISTINCT) 즉, 업이 없는 것을 알고 있는 경우에는ALL.

언급URL : https://stackoverflow.com/questions/70784575/mariadb-using-pk-for-inner-query-result