10.3.1 How MySQL Uses Indexes

10.3.1 How MySQL Uses Indexes

Indexes는 특정 컬럼 값들을 가진 row를 빠르게 찾는 데 사용됩니다. Index가 없으면, MySQL은 첫 번째 row부터 시작하여 관련 row들을 찾기 위해 전체 테이블을 읽어야 합니다. 테이블이 클수록 비용이 더 많이 듭니다. 해당 컬럼들에 대해 테이블에 index가 있는 경우, MySQL은 모든 데이터를 보지 않고도 데이터 파일 중간에서 찾아야 할 위치를 빠르게 결정할 수 있습니다. 이는 모든 row를 순차적으로 읽는 것보다 훨씬 빠릅니다.

대부분의 MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, FULLTEXT)는 B-trees에 저장됩니다. 예외: 공간 데이터 타입에 대한 indexes는 R-trees를 사용합니다. MEMORY 테이블은 hash indexes도 지원합니다. InnoDB는 FULLTEXT indexes에 역색인(inverted list)을 사용합니다.

일반적으로, indexes는 다음 설명과 같이 사용됩니다. MEMORY 테이블에서 사용되는 hash indexes에 특화된 특성은 Section 10.3.9, “Comparison of B-Tree and Hash Indexes”에 설명되어 있습니다.

MySQL은 다음 작업에 indexes를 사용합니다:

• WHERE 절과 일치하는 rows를 빠르게 찾기 위해.

• 고려 대상에서 rows를 제거하기 위해. 여러 indexes 중 선택해야 하는 경우, MySQL은 일반적으로 가장 적은 수의 row를 찾는 index(가장 selective인 index)를 사용합니다.

• 테이블에 다중 컬럼 index가 있는 경우, 옵티마이저는 index의 어떤 leftmost prefix든 사용하여 row를 조회할 수 있습니다. 예를 들어, (col1, col2, col3)에 대한 3컬럼 index가 있다면, (col1), (col1, col2), (col1, col2, col3)에 대해 인덱스된 검색 기능을 가지게 됩니다. 자세한 내용은 Section 10.3.6, “Multiple-Column Indexes”를 참조하십시오.

• 조인을 수행할 때 다른 테이블에서 rows를 가져오기 위해. 컬럼들이 동일한 타입과 크기로 선언되어 있으면 MySQL은 indexes를 더 효율적으로 사용할 수 있습니다. 이 문맥에서, VARCHAR와 CHAR는 동일한 크기로 선언된 경우 동일하다고 간주됩니다. 예를 들어, VARCHAR(10)과 CHAR(10)은 같은 크기이지만, VARCHAR(10)과 CHAR(15)은 그렇지 않습니다.

Nonbinary 문자열 컬럼 간 비교의 경우, 두 컬럼 모두 동일한 문자 집합을 사용해야 합니다. 예를 들어, utf8mb4 컬럼과 latin1 컬럼을 비교하면 index 사용이 불가능해집니다.

서로 다른 컬럼(예를 들어, 문자열 컬럼과 시간 또는 숫자 컬럼)을 비교하는 경우, 값들을 변환 없이 직접 비교할 수 없다면 index 사용이 불가능할 수 있습니다. 숫자 컬럼의 값 1에 대해, 문자열 컬럼의 '1', ' 1', '00001', '01.e1' 등 많은 값과 동일하게 비교될 수 있습니다. 이는 문자열 컬럼에 대한 어떤 index도 사용할 수 없게 만듭니다.

• 특정 index된 컬럼 _key_col_에 대해 MIN() 또는 MAX() 값을 찾기 위해. 이는 프리프로세서에 의해 최적화되는데, 프리프로세서는 index에서 key_col 앞에 나오는 모든 key part에 대해 WHERE key_part_N = constant를 사용하는지 여부를 확인합니다. 이 경우, MySQL은 각 MIN() 또는 MAX() expression에 대해 단일 key lookup을 수행하고 이를 상수로 대체합니다. 모든 expression이 상수로 대체되면, query는 즉시 반환됩니다. 예:

1SELECT MIN(key_part2),MAX(key_part2)
2    FROM tbl_name WHERE key_part1=10;

• 정렬 또는 그룹화가 사용 가능한 index의 leftmost prefix에 대해 수행되는 경우 (예: ORDER BY key_part1, key_part2) 테이블을 정렬하거나 그룹화하기 위해. 모든 key part 뒤에 DESC가 오는 경우, key는 역순으로 읽힙니다. (또는 index가 내림차순 index이면, key는 정방향으로 읽힙니다.) 자세한 내용은 Section 10.2.1.16, “ORDER BY Optimization”, Section 10.2.1.17, “GROUP BY Optimization”, Section 10.3.13, “Descending Indexes”를 참조하십시오.

• 어떤 경우에는, 데이터 row를 참조하지 않고도 값을 가져오도록 query를 최적화할 수 있습니다. (Query에 필요한 모든 결과를 제공하는 index를 covering index라고 합니다.) 어떤 query가 테이블로부터 특정 index에 포함된 컬럼들만 사용하는 경우, 선택된 값은 더 높은 속도를 위해 index 트리에서 가져올 수 있습니다:

1SELECT key_part3 FROM tbl_name
2    WHERE key_part1=1

Indexes는 작은 테이블에 대한 query나, 리포트 query가 대부분 또는 모든 row를 처리하는 큰 테이블에 대한 query에서는 덜 중요합니다. Query가 대부분의 row에 접근해야 할 때에는, index를 통해 작업하는 것보다 순차적으로 읽는 것이 더 빠릅니다.

Query에 모든 row가 필요하지 않더라도, 순차적 읽기는 디스크 탐색을 최소화합니다. 자세한 내용은 Section 10.2.1.23, “Avoiding Full Table Scans”을 참조하십시오.