멀티 PK일 경우 순서가 중요하다..

1. PK 컬럼 순서가 중요하다.

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

1.1. PK 구성과 인덱스 이용

pk 순서를 잘못했을 경우 index가 먹지 않아 full scan을 하게 된다.

create table multi_pk_test2
(
    student_number varchar(255) not null,
    test_year      datetime     not null,
    semester       int          not null,
    test_a         varchar(255) null,
    test_b         varchar(255) null,
    PRIMARY KEY (student_number, test_year, semester)
)
;

explain
select *
from multi_pk_test2
where semester = 1
;

• multi_pk_test2 인덱스 (student_number, test_year, semester) 중에 student_number에 대한 값이 where 절에 들어오지 않아 full scan이 일어남

-- auto-generated definition
create table multi_pk_test
(
    student_number varchar(255) not null,
    test_year      datetime     not null,
    semester       int          not null,
    test_a         varchar(255) null,
    test_b         varchar(255) null,
    PRIMARY KEY (semester, student_number, test_year)
)
;

explain
select *
from multi_pk_test
where semester = 1
;

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

1.2 인덱스의 비효율적인 이용

// 최적화 잘되는 테이블
create table multi_pk_test
(
    student_number varchar(255) not null,
    test_year      datetime     not null,
    semester       int          not null,
    test_a         varchar(255) null,
    test_b         varchar(255) null,
    PRIMARY KEY (student_number, test_year)
)
;

// 최적화 안되는 테이블
create table multi_pk_test2
(

    student_number varchar(255) not null,
    test_year      datetime     not null,
    semester       int          not null,
    test_a         varchar(255) null,
    test_b         varchar(255) null,
    PRIMARY KEY (test_year, student_number)
)
;

위처럼 테이블이 있을 경우

explain
select *
from multi_pk_test
where student_number = '111111' and DATE(test_year) between '2022-11-01' and '2022-11-31';
;

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

• test_year + student_number 테이블은 날짜 범위를 먼저 검색을 하고 student_number을 조회한다.
• student_number + test_year 테이블은 student_number을 검색하고 날짜 범위를 검색한다. ⇒ 이게 더 효율적이다.
• PK 구성을 조정하여 PK 인덱스 조회시 범위를 줄임으로써 성능 향상을 유도할 수 있다.

결론

1. 설계 단계를 마치기 전 데이터 모델링을 수행할 때 PK 컬럼 순서를 반드시 검토하여 조정해야 한다.
   1. 인덱스를 잘 사용하는가?
   2. 인덱스 범위를 효율적으로 사용하는가?

 

출처: https://clairdelunes.tistory.com/48