DB - 복합키 순서

데이터베이스에서 키를 생성할 때 기본키를 구성하는 컬럼을 복수로 지정하거나, 여러 필드를 조합하여 기본키 역할을 할 수 있게하는등의 개념을 복합키라고한다.

그리고, 여러 개의 컬럼으로 구성할 때는 당연히 컬럼 순서가 자연스럽게 정의가 될 것인데, 이 순서를 설계 시점부터 고민해야할 것이다.

복합키를 어떤 기준으로 정하면 좋을까?

일반적으로는 카디널리티가 큰(중복도가 낮은) 순서대로 구성하되, 조회/입력 방법에 따라서 적절히 순서를 바꿔서 구성하거나 새로운 인덱스를 추가하는 방향으로 기준을 잡아보면 좋다.

CREATE TABLE `ys-lab`.`student` (
  `school_code` INT(11) NOT NULL COMMENT '학교코드',
  `student_grade` SMALLINT(3) NOT NULL COMMENT '학년',
  `student_class` SMALLINT(3) NOT NULL COMMENT '반',
  `name` VARCHAR(255) NULL COMMENT '이름',
  `create_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일',
  CONSTRAINT pk_student_no_grade_class PRIMARY KEY(`school_code`, `student_grade`, `student_class`))
COMMENT = '학생';

학생 테이블을 예제로 만들어보자. 그리고 24개의 데이터를 임의로 넣었다. (실제는 이렇게 설계하면 안된다..)

학교코드 + 학년 + 반 정보를 복합키로 작성하였다. 이때 아래의 컬럼 조합으로 조회를 하면 PK index가 적용된다.

1. [school_code, student_grade, student_class]
2. [school_code, student_grade]
3. [school_code]

 

 

-- INDEX를 이용하는 경우
EXPLAIN SELECT * FROM student WHERE school_code = 1 AND student_grade = 6 AND student_class = 1;
EXPLAIN SELECT * FROM student WHERE school_code = 1 AND student_grade = 6;
EXPLAIN SELECT * FROM student WHERE school_code = 1;

-- INDEX를 이용하지 않는경우
EXPLAIN SELECT * FROM student WHERE student_grade = 6;
EXPLAIN SELECT * FROM student WHERE student_class = 1;
EXPLAIN SELECT * FROM student WHERE student_grade = 6 AND student_class = 1;

[INDEX를 이용했을 때와 이용하지 않았을 경우 옵티마이징 실행계획]

INDEX 사용

INDEX 미사용

인덱스의 비효율적인 이용 개선예제

SELECT
    *
FROM
    student
WHERE
    school_code BETWEEN 1 AND 2
    AND student_grade = 5;

위와같은 쿼리문이 있다고 해보자. school_class에서 조건절에 BETWEEN절이 사용되었다. 이떄 실행계획은

type이 range로 나오게되는데, 학교코드(school_code)에서 INDEX를 사용하긴하지만, 범위가 넓어 데이터 조회량이 많아진다면, SQL 성능이 저하될 수 있다. 컬럼마다 다르겠지만, 위의 예제 경우에는 더 단순한 조건의 student_grade 와 순서를 바꾸어서 복합키를 적용한다면 성능이 개선될 수 있을 것이다. (이때, 3개의 PK 복합키를 건드리지말고, 2개를 가지고 새로 INDEX를 만드는 방법을 사용할 수 있다)

 

 

ALTER TABLE `ys-lab`.`student`
ADD INDEX `idx_student_grade_school_code` (`student_grade` ASC, `school_code` ASC);

possible_keys에 PRIMARY와 방금 만든 index_student_grade_school_code 복합키가 후보군으로 보이고, 실제 사용한건 새로 만든 복합키가 사용되는 모습이다.

참고자료

= https://prohannah.tistory.com/99

= https://clairdelunes.tistory.com/48