[MySQL] 복합 인덱스(Multiple-Column Indexes)

MySQL은 복합 인덱스(composite index)

• 즉 여러 열에 대한 인덱스를 생성할 수 있습니다.
• 하나의 인덱스는 최대 16개의 열로 구성될 수 있습니다.
• 특정 데이터 유형의 경우, 열의 접두사(prefix)만 인덱싱할 수도 있습니다
  • (자세한 내용은 섹션 10.3.5, "열 인덱스"를 참조하세요).

복합 인덱스 활용

• 인덱스에 포함된 모든 열을 조건으로 사용하는 쿼리
• 첫 번째 열만 조건으로 사용하는 쿼리
• 첫 번째와 두 번째 열을 조건으로 사용하는 쿼리
• 첫 번째, 두 번째, 세 번째 열을 조건으로 사용하는 쿼리
• 그리고 이러한 순서를 따르는 쿼리들.

인덱스 정의에서 열의 순서를 올바르게 지정하면, 단일 복합 인덱스로도 동일한 테이블에 대한 다양한 종류의 쿼리 성능을 개선할 수 있습니다.

복합 인덱스는 정렬된 배열로 간주할 수 있습니다. 이 배열의 각 행은 인덱싱된 열 값들을 연결(concatenate)하여 생성된 값을 포함합니다.

EX)

테이블에 다음과 같은 사양이 있다고 가정해 보자.

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

INDEX name은 last_name과 first_name 열에 대한 인덱스입니다.
이 인덱스는 last_name과 first_name 값의 조합에 대해 알려진 범위 내의 값을 지정하는 쿼리를 조회하는 데 사용할 수 있습니다.
또한, last_name 열이 인덱스의 왼쪽 접두(prefix) 컬럼이기 때문에(이 섹션의 뒷부분에서 설명) 단순히 last_name 값만 지정한 쿼리에도 사용할 수 있습니다.

INDEX name 적용 ( O ) EX)

SELECT * 
FROM test 
WHERE last_name='Jones';

SELECT * 
FROM test
WHERE last_name='Jones' 
AND first_name='John';

SELECT * 
FROM test
WHERE last_name='Jones'
AND (first_name='John' OR first_name='Jon');

SELECT * 
FROM test
WHERE last_name='Jones'
AND first_name >='M' 
AND first_name < 'N';

 

INDEX name 적용 ( X ) EX)

SELECT * 
FROM test 
WHERE first_name='John';

SELECT * 
FROM test
WHERE last_name='Jones' 
OR first_name='John';

 

다음의 SELECT 문을 실행한다고 가정해 보자.

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

 

col1과 col2에 대해 복합 인덱스가 존재하는 경우 적절한 행을 직접 가져올 수 있습니다.
col1과 col2에 각각 별도의 단일 인덱스가 존재하는 경우, 옵티마이저는 Index Merge 최적화를 시도하거나
(섹션 10.2.1.3, "Index Merge Optimization" 참조), 더 많은 행을 제외하는 가장 제한적인 인덱스를 찾아 해당 인덱스를 사용해 행을 가져오려고 시도합니다.

테이블에 다중 열 인덱스가 있는 경우,
옵티마이저는 인덱스의 왼쪽 접두(prefix)인 모든 열을 사용하여 행을 조회할 수 있습니다.
EX) INDEX (col1, col2, col3)에 대해 세 개의 열로 구성된 인덱스가 있는 경우
> (col1), (col1, col2), (col1, col2, col3)에 대해 인덱스 검색 기능을 사용 가능

MySQL은 열이 인덱스의 왼쪽 접두를 형성하지 않는 경우 인덱스를 사용해 조회를 수행할 수 없습니다.

 

 다음과 같은 SELECT 문이 있는 상황을 가정해봅니다

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

(col1, col2, col3)에 인덱스가 있는 경우,
1번, 2번 Query만 인덱스를 사용합니다.

3번, 4번 Query는 인덱스가 있는 열을 포함하지만 (col2)와 (col2, col3)이 (col1, col2, col3)의 가장 왼쪽 접두사가 아니기 때문에 인덱스를 사용하여 조회를 수행하지 않습니다.