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

MySQL은 복합 인덱스(composite index)

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

복합 인덱스 활용

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

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

복합 인덱스는 내부적으로 정렬된 배열로 간주됩니다. 각 행은 인덱싱된 열 값들을 연결(concatenate)하여 생성된 값을 포함합니다.

예제

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

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)
);

name 인덱스는 last_name과 first_name 열 조합에 대한 복합 인덱스입니다.
첫 번째 열(last_name)이 왼쪽 접두(prefix) 컬럼이기 때문에, 단순히 last_name만 지정한 쿼리에도 사용 가능합니다.

INDEX name 적용 (O) 예

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) 예

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를 시도하거나
  더 선택적인 인덱스를 사용해 조회합니다.

테이블에 다중 열 인덱스 (col1, col2, col3)가 있는 경우, 다음과 같은 왼쪽 접두(leftmost prefix)가 사용 가능합니다:

• (col1)
• (col1, col2)
• (col1, col2, col3)

반면, (col2) 또는 (col2, col3)만 사용하는 경우에는 인덱스를 활용할 수 없습니다.

예시 쿼리 비교

-- 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번 쿼리만 인덱스를 사용합니다.
• 3번과 4번 쿼리는 왼쪽 접두가 아니므로 인덱스를 사용하지 않습니다.