높은 카디널리티와 낮은 카디널리티: 범위 인덱스에서 어떤 열이 먼저 와야 합니까?

범위 인덱스의 높은 카디널리티 열 순서

데이터베이스 설계에서 여러 열에 인덱스를 생성할 때 해당 열의 순서는 다음과 같습니다. 범위 쿼리 성능에 큰 영향을 미칩니다. 특히, 높은 카디널리티 열과 낮은 카디널리티 열을 모두 포함하는 쿼리의 경우 인덱스의 최적 열 순서는 직관적이지 않을 수 있습니다.

시나리오:

다음을 고려하세요. 다음 스키마가 있는 테이블:

CREATE TABLE `files` (
  `did` int(10) unsigned NOT NULL DEFAULT '0',
  `filename` varbinary(200) NOT NULL,
  `ext` varbinary(5) DEFAULT NULL,
  `fsize` double DEFAULT NULL,
  `filetime` datetime DEFAULT NULL,
  PRIMARY KEY (`did`,`filename`),
  KEY `fe` (`filetime`,`ext`),          -- This?
  KEY `ef` (`ext`,`filetime`)           -- or This?
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

이 테이블은 약 100만 행의 파일 메타데이터를 저장합니다. filetime 열에는 대부분 고유한 값이 있어 높은 카디널리티를 나타내는 반면, ext 열에는 한정된 수의 값이 있어 낮은 카디널리티를 나타냅니다.

쿼리:

다음 쿼리 ext 및 filetime을 기반으로 파일 정보를 검색하는 데 사용됩니다.

WHERE ext = '...'
  AND filetime BETWEEN ... AND ...

Best Index 선택:

fe 또는 ef 중 어느 인덱스가 이 쿼리에 더 좋을지에 대한 의문이 생깁니다. 직관적으로 카디널리티가 높은 열(파일 시간)이 먼저 있는 인덱스가 더 효율적이라고 가정할 수 있습니다. 그러나 분석에 따르면 그 반대가 사실입니다.

설명:

EXPLAIN 명령을 사용하면 다양한 인덱스 선택에 대한 쿼리 계획을 검사할 수 있습니다.

-- Force index on fe (filetime first)
EXPLAIN SELECT COUNT(*), AVG(fsize)
FROM files FORCE INDEX(fe)
WHERE ext = 'gif' AND filetime >= '2015-01-01' AND filetime < '2015-01-01' + INTERVAL 1 MONTH;

-- Force index on ef (ext first)
EXPLAIN SELECT COUNT(*), AVG(fsize)
FROM files FORCE INDEX(ef)
WHERE ext = 'gif' AND filetime >= '2015-01-01' AND filetime < '2015-01-01' + INTERVAL 1 MONTH;

결과에 따르면 카디널리티가 낮은 ext 열을 먼저 사용하는 인덱스 ef가 fe.

Optimizer 추적을 사용한 추가 분석에서는 fe 인덱스를 사용하여 최적화 프로그램이 ext 값을 필터링하기 위해 많은 수의 행(16684)을 스캔하는 것으로 추정했습니다. ef 인덱스를 사용하면 두 인덱스 열을 모두 효율적으로 사용할 수 있고 관련 행(538)만 검색할 수 있습니다.

결론:

범위를 지원하기 위해 복합 인덱스를 생성하는 경우 쿼리의 경우 카디널리티에 관계없이 같음 테스트에 관련된 열(이 경우 ext)을 먼저 배치하는 것이 좋습니다. 이를 통해 쿼리 실행 계획에서 인덱스를 보다 효과적으로 사용할 수 있어 성능이 향상됩니다.

위 내용은 높은 카디널리티와 낮은 카디널리티: 범위 인덱스에서 어떤 열이 먼저 와야 합니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!