10.3.6 Multiple-Column Indexes

10.3.6 Multiple-Column Indexes

MySQL은 복합 인덱스(즉, 여러 컬럼에 대한 인덱스)를 생성할 수 있습니다. 하나의 인덱스는 최대 16개의 컬럼으로 구성될 수 있습니다. 특정 데이터 타입의 경우, 컬럼의 프리픽스에 대해 인덱스를 생성할 수 있습니다( Section 10.3.5, “Column Indexes” 참조).

MySQL은 인덱스 내의 모든 컬럼을 조건으로 사용하는 쿼리뿐 아니라, 첫 번째 컬럼만, 첫 두 개의 컬럼, 첫 세 개의 컬럼 등과 같이 사용하는 쿼리에 대해서도 multiple-column 인덱스를 사용할 수 있습니다. 인덱스 정의에서 컬럼을 올바른 순서로 지정하면, 하나의 복합 인덱스로 동일한 테이블에 대한 여러 종류의 쿼리를 빠르게 만들 수 있습니다.

multiple-column 인덱스는 정렬된 배열로 간주할 수 있으며, 그 각 로우에는 인덱스가 지정된 컬럼 값들을 이어 붙여(concatenating) 생성된 값이 들어 있습니다.

참고

복합 인덱스의 대안으로, 다른 컬럼의 정보를 기반으로 “해시된(hashed)” 컬럼을 도입할 수 있습니다. 이 컬럼이 짧고, 적절히(유일하게) 유일하며, 인덱스가 생성되어 있다면, 많은 컬럼에 걸친 “넓은(wide)” 인덱스보다 더 빠를 수 있습니다. MySQL에서는 이 추가 컬럼을 사용하는 것이 매우 쉽습니다:

1SELECT * FROM tbl_name
2  WHERE hash_col=MD5(CONCAT(val1,val2))
3  AND col1=val1 AND col2=val2;

다음과 같은 정의를 가진 테이블이 있다고 가정해 봅시다:

1CREATE TABLE test (
2    id         INT NOT NULL,
3    last_name  CHAR(30) NOT NULL,
4    first_name CHAR(30) NOT NULL,
5    PRIMARY KEY (id),
6    INDEX name (last_name,first_name)
7);

name 인덱스는 last_name과 first_name 컬럼에 대한 인덱스입니다. 이 인덱스는 last_name과 first_name 값의 조합에 대해 알려진 범위의 값을 지정하는 쿼리에서 룩업에 사용할 수 있습니다. 또한, last_name 컬럼이 이 인덱스의 leftmost prefix이기 때문에(이 절의 뒷부분에서 설명합니다) last_name 값만을 지정하는 쿼리에도 사용할 수 있습니다. 따라서 name 인덱스는 다음 쿼리들의 룩업에 사용됩니다:

1SELECT * FROM test WHERE last_name='Jones';
2
3SELECT * FROM test
4  WHERE last_name='Jones' AND first_name='John';
5
6SELECT * FROM test
7  WHERE last_name='Jones'
8  AND (first_name='John' OR first_name='Jon');
9
10SELECT * FROM test
11  WHERE last_name='Jones'
12  AND first_name >='M' AND first_name < 'N';

그러나 name 인덱스는 다음 쿼리들의 룩업에는 사용되지 않습니다:

1SELECT * FROM test WHERE first_name='John';
2
3SELECT * FROM test
4  WHERE last_name='Jones' OR first_name='John';

다음과 같은 SELECT 스테이트먼트를 실행한다고 가정해 봅시다:

1SELECT * FROM tbl_name
2  WHERE col1=val1 AND col2=val2;

col1과 col2에 대한 multiple-column 인덱스가 존재한다면, 해당 로우들을 직접 읽어 올 수 있습니다. col1과 col2 각각에 대해 별도의 single-column 인덱스만 존재하는 경우, 옵티마이저는 Index Merge 최적화를 사용하려 시도합니다( Section 10.2.1.3, “Index Merge Optimization” 참조). 또는 어떤 인덱스가 더 많은 로우를 제외하는지를 결정하여, 가장 제한적인(restrictive) 인덱스를 사용해 로우를 읽어 오려 시도합니다.

테이블에 multiple-column 인덱스가 있는 경우, 그 어떤 leftmost prefix라도 옵티마이저가 로우 룩업에 사용할 수 있습니다. 예를 들어, (col1, col2, col3)에 대한 three-column 인덱스가 있다면, (col1), (col1, col2), (col1, col2, col3)에 대해 인덱스된 검색 기능을 갖게 됩니다.

컬럼들이 인덱스의 leftmost prefix를 형성하지 않으면, MySQL은 인덱스를 사용해 룩업을 수행할 수 없습니다. 다음과 같은 SELECT 스테이트먼트가 있다고 가정해 봅시다:

1SELECT * FROM tbl_name WHERE col1=val1;
2SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
3
4SELECT * FROM tbl_name WHERE col2=val2;
5SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

(col1, col2, col3)에 대한 인덱스가 존재할 경우, 첫 번째와 두 번째 쿼리만 이 인덱스를 사용합니다. 세 번째와 네 번째 쿼리도 인덱스가 지정된 컬럼을 포함하지만, (col2)와 (col2, col3)은 (col1, col2, col3)의 leftmost prefix가 아니기 때문에, 룩업을 수행하기 위해 인덱스를 사용하지 않습니다.