[Database/Mysql] primary key를 변경했더니 정렬 순서가 바뀌었다(index에 대하여)

 

나는 데이터베이스에 대해 아직 잘 모른다. 할 수 있는건...정말 단순 CRUD정도.. DB에 대해서 잘 몰라서 생겼던 문제점에 대해서 작성해보려고 한다.

 

채팅 내용을 저장하는 테이블을 설계 할 때 방 이름과 등록일을 primary key로 잡았었다.

(해당 테이블은 이해를 돕기 위해 임의로 생성한 테이블이며, 실제로 사용 된 테이블이 아닙니다)

CREATE TABLE `MESSAGE` (
  `ROOM_ID` varchar(20) NOT NULL,
  `USER_ID` varchar(20) NOT NULL,
  `MESSAGE` varchar(100) NOT NULL,
  `REGIST_DATE` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`ROOM_ID`,`REGIST_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 

 

테이블에 데이터를 생성하고 정렬 조건을 걸지 않고 조회를 한 경우 방 room1에서 일어난 대화내용이 순서대로 조회가 되는 것을 확인 할 수 있었다.

 

그런데, 다른 시스템과 연동을 하다 보니 절대 겹칠 수 없을 것 같았던 REGIST_DATE가 겹치게 되며, duplicate key exception이 발생해서 데이터가 저장되지 않는 현상이 발생했다.

 

해당 에러를 보고 가장 먼저 든 생각은 그럼 USER_ID 도 함께 primary key로 잡자!

ALTER TABLE MESSAGE DROP PRIMARY KEY;
ALTER TABLE MESSAGE ADD PRIMARY KEY (ROOM_ID, USER_ID, REGIST_DATE);

 

과연 해결이 되었을까?

중복에러는 해결이 되었지만, 사용자의 화면에는 원하는 순서가 아니라, a가 말한 대화내용이 먼저 나온 다음에 b 대화내용이 나타났다.

 

왜 그런걸까?

이유는 primary 키를 생성 할 때 자동으로 생성되는 index 때문이다.

 

mysql의 인덱스는 두가지 종류의 인덱스가 있는데, PRIMARY KEY 혹은 UNIQUE NOT NULL 로 지정한 열은 클러스터형 인덱스가 생성된다. 

 

1. 클러스터형 인덱스

2. 보조인덱스

 

비유를 해보자면 클러스터형 인덱스는 영어사전, 보조인덱스는 책갈피로 할 수 있다.

클러스터형 인덱스는 PRIMARY KEY로 지정한 열을 기준으로 오름차순으로 데이터가 정렬되게 된다.

 

따라서 PRIMARY KEY가 ROOM_ID, REGIST_DATE 일 때 데이터를 조회 한 경우에는 ROOM_ID -> REGIST_DATE순으로 데이터가 정렬되어, 우리가 원하던 데로 데이터가 출려되었지만

PRIMARY KEY 에 USER_ID를 추가 하여 경우에는  ROOM_ID, USER_ID, REGIST_DATE 일 때는 ROOM_ID -> USER_ID -> REGIST_DATE 순으로 정렬되어 원하는데로 데이터가 표시되지 않았다.

 

그러면 순서를 올바르게 출력하고 싶은 경우에는 어떻게 해야할까?

 

1. 조회 조건에 정렬 조건 추가하기

2. PRIMARY KEY 순서 조절하기

 

나는 당장 소스를 고쳐서 배포 할 상황이 되지 않아, 우선적으로 PRIMARY KEY순서를 조절하는 방법을 사용했다.

ALTER TABLE MESSAGE DROP PRIMARY KEY;
ALTER TABLE MESSAGE ADD PRIMARY KEY (ROOM_ID, REGIST_DATE, USER_ID);

 

변경 후 다시 조회를 해보면 아래와 같이 원하던 데로 결과가 출력 된 것을 확인 할 수 있다.

 

결합 인덱스의 경우에는 결합하는 컬럼들의 순서가 중요한데, where절에서 equal 조건으로 많이 쓰이는 컬럼이 오는게 효율적이다. 또한, 분별력이 낮은 컬럼이 선두에 위치하는것이 index scip scan 측면에서 유리하다고 한다.

 

이번 경험을 통해서, 그냥 간단해보이는 변경도 신중히 해야한다는 것을 깨달았다..!🥹