[RDB] PK 컬럼 순서

여러 개의 컬럼으로 구성된 PK 구성 테이블에서 있는 그대로 테이블을 생성해 버리면 다음과 같은 문제가 발생할 수 있습니다.

 

- 인덱스 구성에서 의도하지 않은 순서의 Primary Key Unique Index가 생성됩니다.

- 그에 따라 조회 SQL 실행 시 성능 저하 현상이 나타날 수 있습니다.

- 많은 인덱스가 생성되므로 입력/수정/삭제 시 불필요한 내부 작업이 증가해 성능에 악영향을 미칩니다.

 

Q)

단순히 테이블을 있는 그대로 생성했을 뿐인데, PK 컬럼 구성에 대해 순서를 고려하지 않으면 의도하지 않은 성능 저하 현상이 벌어지는 이유가 무엇일까요?

A) 

- 데이터 모델링을 종료하였으나 스키마를 생성하기 전에 데이터 모델의 PK 순서를 조정하지 않은 채 테이블을 생성하면 인덱스를 이용하지 못해 테이블 FULL Scan 현상이 발생하는 경우가 있습니다.

 

예시)

아래와 같이 스키마를 생성합니다.

CREATE TABLE admission_master_v1 (
    exam_number INT,
    year YEAR,
    semester VARCHAR(10),
    graduate_division_code VARCHAR(10),
    degree_type_code VARCHAR(10),
    PRIMARY KEY (exam_number, year, semester)
);

CREATE TABLE selection_subject_performance_v1 (
    exam_number INT,
    year YEAR,
    semester VARCHAR(10),
    selection_subject_code VARCHAR(10),
    score DECIMAL(5, 2),
    absent_status BOOLEAN,
    fail_status BOOLEAN,
    PRIMARY KEY (exam_number, year, semester, selection_subject_code),
    FOREIGN KEY (exam_number, year, semester) REFERENCES admission_master(exam_number, year, semester)
);

 

그리고 아래 쿼리의 실행 계획을 살펴봅니다.

EXPLAIN
select count(exam_number)
from admission_master_v1
where year = '2008'
and semester = '1';

-- 실행 계획 결과
{
    "id": 1,
    "select_type": "SIMPLE",
    "table": "admission_master_v1",
    "type": "index",
    "possible_keys": null,
    "key": "PRIMARY",
    "key_len": "47",
    "ref": null,
    "rows": "1",
    "Extra": "Using where; Using index"
}

 

책에서는 PK의 순서를 올바르게 지키지 않은 조건절로 인해 FULL TABLE SCAN이 발생할 수 있다고 나왔지만 실제로 최적화기가 인덱스를 사용하고 있습니다.

GPT에 물어보니 이런 답변을 받았습니다.

 

`admission_master_v1` 테이블의 기본 키(Primary Key, PK)는 `exam_number`, `year`, `semester` 순서로 구성되어 있습니다. 제공된 쿼리에서는 `year`와 `semester` 조건만 사용하고 있습니다. 이 경우, 실행 계획이 전체 테이블 스캔(FULL TABLE SCAN)으로 나올 수 있는지에 대한 질문에 대한 답변은 다음과 같습니다:

1. 인덱스 사용: MySQL은 복합 인덱스의 왼쪽에서 오른쪽으로 인덱싱을 수행합니다. 즉, 복합 인덱스의 첫 번째 컬럼(`exam_number`)을 조건에 포함하지 않으면, MySQL은 이 복합 인덱스를 효율적으로 사용할 수 없습니다.
2. 쿼리 최적화: MySQL 쿼리 최적화기는 여러 요인을 고려하여 실행 계획을 결정합니다. 인덱스의 일부만 사용하는 경우, 즉 `year`와 `semester`만 사용하는 경우, MySQL 최적화기는 여전히 인덱스를 부분적으로 사용할 수 있지만, 이는 전체 인덱스를 사용하는 것보다 효율이 낮을 수 있습니다.
3. FULL TABLE SCAN 가능성: 복합 인덱스의 첫 번째 필드(`exam_number`)를 조건에 포함하지 않았기 때문에, MySQL이 전체 테이블 스캔을 선택할 수 있습니다. 특히 테이블에 데이터가 많고, `year`와 `semester` 조건에 해당하는 데이터가 테이블의 상당 부분을 차지한다면, 전체 테이블 스캔이 더 효율적일 수 있습니다.

결론적으로, 제공된 쿼리에서 전체 테이블 스캔이 일어날 가능성은 있습니다. 그러나 이는 MySQL의 버전, 데이터의 분포, 쿼리 최적화기의 특정 동작 방식 등에 따라 달라질 수 있습니다. 정확한 실행 계획을 알기 위해서는 실제 환경에서 `EXPLAIN` 명령어를 사용하여 쿼리의 실행 계획을 확인하는 것이 가장 정확합니다.

 

 

year, semester, exam_number 순으로 PK을 재구성한 테이블을 생성하고 실행 계획을 다시 살펴 보겠습니다.

CREATE TABLE admission_master_v2 (
    year YEAR,
    semester VARCHAR(10),
    exam_number INT,
    graduate_division_code VARCHAR(10),
    degree_type_code VARCHAR(10),
    PRIMARY KEY (year, semester, exam_number)
);

CREATE TABLE selection_subject_performance_v2 (
    year YEAR,
    semester VARCHAR(10),
    exam_number INT,
    selection_subject_code VARCHAR(10),
    score DECIMAL(5, 2),
    absent_status BOOLEAN,
    fail_status BOOLEAN,
    PRIMARY KEY (year, semester, exam_number, selection_subject_code),
    FOREIGN KEY (year, semester, exam_number) REFERENCES admission_master(year, semester, exam_number)
);

EXPLAIN
select count(exam_number)
from admission_master_v2
where year = '2008'
and semester = '1';


{
    "id": 1,
    "select_type": "SIMPLE",
    "table": "admission_master_v2",
    "type": "ref",
    "possible_keys": "PRIMARY",
    "key": "PRIMARY",
    "key_len": "43",
    "ref": "const,const",
    "rows": "1",
    "Extra": "Using where; Using index"
}

 

위 2개의 실행 계획을 비교한 결과에 대한 GPT 답변입니다.

2번 실행 계획이 더 좋습니다.

이유는 'ref' 타입이 'index' 타입보다 일반적으로 더 효율적이기 때문입니다. 'ref' 타입은 인덱스를 사용하여 특정 값에 해당하는 행들을 빠르게 찾는 반면, 'index' 타입은 인덱스의 모든 항목을 스캔합니다.

또한, 2번 실행 계획에서는 const를 사용하여 인덱스를 참조하고 있는데, 이는 매우 효율적인 데이터 검색을 의미합니다. 즉, 상수 값에 딱 맞는 행을 빠르게 찾을 수 있습니다.

따라서, 2번 실행 계획이 데이터베이스 서버에 더 적은 부담을 주고, 쿼리를 더 빠르고 효율적으로 수행할 가능성이 높습니다.

 

 

---

 

PK 컬럼 순서를 효율적으로 만드려면, 

설계 단계를 마치기 전 데이터 모델링을 수행할 때 PK 컬럼 순서를 반드시 검토하여 조정해야 합니다.

 

PK 순서가 잘못되어 SQL 문장의 성능이 저하되는 경우는 크게 두 가지가 있습니다.

- 컬럼 순서를 준수하여 조건절을 명시하지 않아 FULL TABLE SCAN이 발생할 수 있는 경우

- 인덱스는 사용하는데 범위가 넓어져 성능이 저하되는 경우(책과는 다르게 최적화기가 해결해주는 것 같습니다.)

ex) 문자열(10), 문자열(5)의 인덱스 순서보다 문자열(5), 문자열(10)의 순서의 인덱스 구성이 범위를 조회 범위를 줄여주어 더 좋은 구성입니다. (MySQL 8.x 버전에서는 동일한 실행계획이 나와 따로 스키마를 작성하지 않았습니다.)

 

인덱스의 정렬(SORT) 구조를 이해한 상태에서 트랜잭션의 특성에 따른 PK 구성을 하여 인덱스 범위를 최소화하는 방향으로 데이터 모델에 반영해야 합니다.

데이터 모델의 PK 순서가 아무런 의미가 없는 것 같지만, 실전 프로젝트에서는 의미 없는 PK 순서 때문에 성능이 저하되는 경우가 아주 빈번하게 발생합니다.

PK 순서 이외에 다른 엔티티타입으로부터 상속받아 발생하는 PK 순서까지 항상 주의하여 표시하도록 해야합니다.

PK 순서를 결정할 때에는 인덱스 정렬 구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 해야 합니다.

 

---

 

참고 도서

아는 만큼 보이는 데이터베이스 설계와 구축