[MySQL] Unique index와 Primary key 차이

이전에 썼던 잘못된 글..

https://fladi.tistory.com/312

[DB] Unique index와 pk

 

작년에 다음과 같은 포스팅을 했었다. 

이 때는 인덱스가 뭔지도 몰랐고, Unique인덱스도 처음 들어보는 이야기였다. Primary키를 왜 써야하는지 쓰면 뭐가 좋은지도 몰랐고 그냥 쓰라고 해서 썼었던 시절이었다. 그래서 구글링을 조금 해보면서 둘의 차이점을 블로그에 올렸었다.

 

하지만 최근에 Real MySQL 책을 읽고 디비를 공부하면서 내가 알고있던 지식이 잘못됐다는 걸 알게되었고, 위 포스팅을 다시 찾아서 고쳐야겠다는 생각이 들었다. 

 

 

 

들어가기 전 사전지식

• MySQL 8.0부터 InnoDB 스토리지 엔진이 기본으로 체택되었다
• InnoDB스토리지 엔진은 클러스터링 인덱스를 지원하며, PK가 굉장히 중요하다
• MyISAM이나 MEMORY 스토리지 엔진은 클러스터링 인덱스를 지원하지 않음
  • 세컨더리 인덱스와 PK가 별로 큰 차이가 없음
• InnoDB가 아닌 스토리지 엔진 테이블(클러스터링 테이블이 아닌 곳)은 Unique Index와 Primary Key가 차이가 없다. 이 블로그에서 서술하는 Unique Index와 PK의 차이점은 InnoDB에서만 해당된다

 

 

 

InnoDB의 클러스터링 테이블

PK와 Unique 인덱스를 구분하기 위해서는 이 지식이 꼭 필요하다고 생각하여 먼저 서술한다.

InnoDB 스토리지 엔진에서는 MySQL 서버의 클러스터링 인덱스를 지원한다. 

 

클러스터링 인덱스란, 테이블의 레코드를 pk기준 비슷한 것들끼리 묶어서 저장하는 방식이다. 그냥 pk가 비슷한 레코드들끼리 모여서 저장된다고 생각하면 된다.

(클러스터링 인덱스를 사용하는 테이블을 클러스터링 테이블이라고 부르겠다)

 

클러스터링 테이블은 다음과 같이 동작한다

• pk값에 의해 레코드의 저장위치 결정 
• pk값이 변경되면 레코드의 물리적인 저장 위치가 바뀜
• pk기반 검색이 매우 빠르지만, 레코드의 저장이나 pk변경이 상대적으로 느림

 

클러스터링 테이블의 레코드 저장방식과 세컨더리 인덱스 저장방식은 비슷하지만, pk기준 B-Tree 리프노드에 레코드의 전체 정보가 저장되어있다는 차이점이 있다.

 

 

 

 

클러스터링 테이블에서 Primary Key

• 클러스터링 테이블에서는 pk를 기준으로 레코드가 묶여서 저장된다
• 그래서 PK가 변경되거나 새로운 레코드를 넣을 때 pk를 기반으로 저장위치를 결정해야하기 때문에 상대적으로 시간이 많이 걸린다
• MyISAM과 같은 클러스터링되지 않는 테이블은 한 번 저장된 레코드의 위치가 변경되지 않지만, InnoDB와 같은 클러스터링 테이블의 경우 레코드의 실제 위치가 변경되기도 한다

 

 

 

세컨더리 인덱스의 저장방식

• 클러스터링 테이블에서는 pk를 기반으로 인덱싱되어 레코드가 저장된다
• 클러스터링 테이블에서 세컨더리 인덱스는 리프노드에 레코드의 실제 주소가 아닌 pk가 저장되어 있다. 
  • 그래서 실제 레코드를 찾기 위해 pk를 기반으로 다시 조회해야한다.
• 클러스터링 테이블과 그렇지 않은 테이블에서 세컨더리 인덱스를 타고 레코드를 가져오는 과정을 비교해보면 다음과 같음
  • 클러스터링x(MyISAM): 해당 인덱스를 검색해 레코드 주소 확인, 레코드 주소를 이용해 최종 레코드 가져옴
  • 클러스터링o(InnoDB): 해당 인덱스를 검색해 레코드의 pk확인, pk 인덱스를 검색해 최종 레코드를 가져옴

 

 

Unique 인덱스란?

• Unique 인덱스는 인덱스라기보단 제약조건에 가깝다
• NULL이 저장가능하고, NULL은 중복되어 저장될 수 있다
• 유니크 인덱스와 일반 세컨더리 인덱스는 실행계획이 다르지만, 성능상 차이는 미미하다고 한다

 

 

PK대신 Unique Index를 사용할 수 없는 이유

Unique Index의 경우 NULL이 허용되기 때문에 PK로 사용할 수 없다. 스토리지 엔진이 자동으로 PK를 지정해줄 때도 NULL이 허용되지 않는 컬럼만 PK로 지정한다. 만약 Unique 인덱스에 NOT NULL 제약조건이 붙어있는 경우에는 이 컬럼이 PK처럼 사용된다고 생각하면 된다(스토리지 엔진이 지정해줄거다)

 

 

클러스터링 테이블은 PK가 없을 경우에도 PK를 대체할 컬럼을 지정하여 클러스터링 한다. 스토리지 엔진은 다음 우선순위로 PK를 대체할 컬럼을 선택한다.

1. NOT NULL 옵션의 Unique 인덱스 중 하나를 클러스터링 키로 선택
2. 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가하여 클러스터링 키로 선택

 

이렇게 어차피 지정 안해줘도 클러스터링 키가 자동으로 부여된다. 그리고 자동으로 생성된 경우에는 사용자에게 노출되지 않는 내부적 레코드의 일련번호가 생성된다. 사용자에게 노출되지 않는 것보단 웬만하면 사용자가 사용할 수 있는 값으로 설정하는 게 좋을거다. (pk를 검색에서 빈번하게 사용하면 성능이 좋아질테니까)

 

InnoDB가 아닌 테이블(MyISAM)의 경우 PK는 NULL이 허용되지 않는 유니크 인덱스와 같기때문에 둘은 동일하다.

하지만 InnoDB의 경우 PK는 클러스터링 키의 역할도 하므로 근본적으로 다르다. 클러스터링 테이블에서 Primary Key가 가지는 의미가 정말 크기때문에 무조건 지정해야한다고 생각하자. (AUTO_INCREMENT라도 꼭 지정하기!)