[MySQL] PK 컬럼 순서와 Index

목차

 

 

 

1. PK 컬럼 순서의 중요성

• 인덱스 구성에서 의도하지 않은 순서의 Primary Key Unique Index가 생성됩니다.
• 많은 인덱스가 생성되므로 입력/수정/삭제 시 불필요한 내부 작업이 증가해 성능에 악영향을 미칠 수 있습니다.

 

2. PK 구성과 Index 이용

• 스키마를 생성하기 이전에 데이터 모델의 PK 순서를 조절하지 않은 채 테이블을 생성하면 인덱스를 이용하지 못해 테이블 Full Scan 현상이 발생할 경우가 있습니다.

예시 테이블

create table multi_pk_table
(
    student_id varchar(255) not null,
    year      datetime     not null,
    semester       int          not null,
    PRIMARY KEY (student_id, year, semester)
);

 

예시 쿼리

explain
select *
from multi_pk_table
where semester = 1;

• multi_pk_table 인덱스 (student_id, year, semester) 중에 student_id에 대한 값이 where 절에 들어오지 않아 full scan이 일어납니다.

 

create table multi_pk_table
(
    student_id varchar(255) not null,
    year      datetime     not null,
    semester       int          not null,
    PRIMARY KEY (semester, student_id, year)
);

• 조회를 할 때 semester, student_id 에 대한 값이 빈번하게 들어간다면 위처럼 순서를 변경하여 인덱스를 이용하게 만듭니다.

 

3. Index 이용 최적화

• 예시 테이블 A

create table test_A
(
    student_id varchar(255) not null,
    year      datetime     not null,
    semester       int          not null,
    PRIMARY KEY (student_id, year)
);

 

• 예시 테이블 B

create table test_B
(
    student_id varchar(255) not null,
    year      datetime     not null,
    semester       int          not null,
    PRIMARY KEY (year, student_id)
);

 

• 예시 쿼리

explain
select *
from test_A
where student_id = '111111' 
and DATE(year) between '2024-01-01' and '2024-12-31';

 

• student_id 은 ‘=’으로 조건을 걸어올 것입니다.
• year 은 between으로 조건을 걸어온다고 가정합니다.
• ⇒ 인덱스를 이용했지만 최적화가 됐는지 확인을 해야됩니다.

 

• test_A 테이블은 (pk: student_id + year)  student_id를 먼저 검색하고 날짜 범위를 검색합니다. → 이게 더 효율적!!
• test_B 테이블은 (pk: year + student_id) 날짜 범위를 먼저 검색을 하고 student_id를 조회합니다.
• PK 구성을 조정하여 PK 인덱스 조회 시 범위를 줄임으로써 성능 향상을 유도할 수 있습니다.

 

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

1. 인덱스를 잘 사용하는가?
2. 인덱스 범위를 효율적으로 사용하는가?

 

 

---

<참고자료>

https://clairdelunes.tistory.com/48

https://k9e4h.tistory.com/396

https://balldev.tistory.com/93