범위 쿼리가 포함된 복합 인덱스의 높은 카디널리티 열 배치

범위 조건과 관련된 복합 인덱스가 있는 테이블을 쿼리할 때 인덱스 내의 열은 성능에 큰 영향을 미칠 수 있습니다.

기본 키(did, 파일 이름)와 두 개의 복합 키가 있는 테이블 파일을 고려하세요. 인덱스: INDEX(filetime, ext) 및 INDEX(ext, filetime). 두 인덱스 모두 ext보다 카디널리티가 높은 파일 시간 열을 포함합니다.

쿼리:

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

는 ext와 filetime을 모두 기반으로 데이터에 액세스해야 합니다. 질문이 생깁니다. 이러한 쿼리에 어떤 인덱스가 최적입니까?

분석

최적의 인덱스를 결정하기 위해 FORCE INDEX를 사용하고 실행 계획을 검토할 수 있습니다.

-- Force range on filetime first
FORCE INDEX(fe) SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime = '2015-01-01'
  AND filetime 
출력에 따르면 INDEX(ext, filetime) (ef)의 행 수가 훨씬 적어 스캔 효율성이 더 우수함을 나타냅니다.
옵티마이저 추적
옵티마이저의 동작을 더 자세히 분석하기 위해 옵티마이저를 사용할 수 있습니다. 추적:
SELECT explain_format = 'JSON';

SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime 
추적에서는 최적화 프로그램이 인덱스의 두 열을 모두 사용하여 데이터를 필터링하고 가져올 수 있기 때문에 INDEX(ext, filetime)를 선택한다는 것을 보여줍니다. 반면 INDEX(filetime, ext)는 첫 번째 열(filetime)만 필터링에 사용할 수 있습니다.
결론
분석을 바탕으로 다음과 같은 결론을 내릴 수 있습니다. draw:

• 범위 쿼리에 사용되는 복합 인덱스의 경우 등호 조건자(이 경우 ext)와 관련된 열이 먼저 배치되어야 합니다.
• WHERE 절에서 사용된 순서에 따라 인덱스의 열을 정렬하면 쿼리 성능이 향상됩니다.
• 카디널리티만으로는 결정적인 요소가 아닙니다. 최적의 지수를 결정할 때 범위 열의 카디널리티가 더 높지만 같음 열이 같음 조건부에 포함되어 있는 시나리오에서는 같음 열을 먼저 배치하면 성능이 더 좋아집니다.