멀티컬럼 PK는 왜?

어느 날, 친구에게 이런 카톡을 받았다.

이런 상황에서는 학생_수업 테이블에는 PK(primary key)를 만들어야 하는가에 대한 질문이었다.

나는 항상 테이블을 만들 때 PK를 기본으로 만들고 시작했었는데 (생각 없이)

생각해보자니 저 상황일 때도 만들어야 하는가? 에 대한 고찰 없이 무지성으로 만들었었다.

그럼 질문이 생긴다.

1. PK는 모든 테이블에 꼭 필요한가?
2. 학생-수업 테이블에서 FK 두 개를 묶어서 PK로 만들어 사용해도 되는가?

PK를 구글링 해보면 잘 정리된 블로그가 많으니 해당 블로그를 통해 자세히 살펴보면 좋을 것이다.

위키 백과의 PK에 대한 설명 부분을 가져왔다.

• 테이블에서 정보 값(각 행)을 식별하기 위해 만들어진
• null값이 허용되지 않고
• 중복이 허용되지 않는 키라고 볼 수 있다.

위 위키피디아의 서술에 따르면 그렇다면 중복이 없고(유일한 값), 빈칸을 허용하지 않고!(NOT NULL),

식별이 가능한 칼럼의 경우, PK의 역할로 전혀 문제가 없다고 나온다. (=후보 키)

그럼 테이블의 데이터들의 구분을 위해서는 후보 키만 사용해도 되지 않을까? PK가 반드시 필요할까?

https://stackoverflow.com/questions/840162/should-each-and-every-table-have-a-primary-key

Should each and every table have a primary key?

해당 글에 대해 대답을 정리해보자

• 다른 테이블과 Join 연산 (관계를 표현) 하기 위해 필요
• 테이블을 클러스터링* 하기 위해서는 필요
  클러스터링이란? 디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 테이블의 데이터를
  디스크의 같은 위치에 저장시키는 방법

• MySQL에서 InnoDB 스토리지 엔진은 명시적으로 지정하지 않은 경우 항상 기본 키를 생성(접근 불가한 PK)

하지만 덧붙인 말에

If you have a many-to-many link table, you create the primary key on all fields involved in the link. Thus you ensure that you don't have two or more records describing one link.

“다대다 연결 테이블이 있는 경우 링크와 관련된 모든 필드에 기본 키를 작성합니다. 따라서 하나의 링크를 설명하는 레코드가 둘 이상 존재하지 않습니다.”

 

또 다른 의견으로는

https://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=qna_db&wr_id=212062 

WWW.PHPSCHOOL.COM

• 단순 로그 DB ( INSERT를 제외한 나머지 DML 작업이 극히 드물고, 로그 기록에만 사용하는 DB )의 경우라면, PK를 사용하는 것 자체도 성능에 악영향
  • INSERT 시 PK에 대한 무결성 체크 비용과 인덱싱에 드는 비용들이 그 원인

하지만 굉장히 옛날 글이기 때문에 버전 up 된 DB 엔진에서는 PK가 존재함으로써, 성능에 큰 이슈가 될지는 잘 모르겠다.

 

PK를 추가하면 인덱싱이라는 작업에 비용이 든다… 그럼 인덱싱이 뭔데?

이럴 땐 역시 공식 document를…(Mysql)

https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html

MySQL :: MySQL 8.0 Reference Manual :: 8.3.1 How MySQL Uses Indexes

인덱스는 뭔가요? 왜 쓰나요?

• Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.
  (인덱스는 특정 칼럼 값을 통해 행을 찾을 때 사용!, 만약 인덱스가 없다면 처음부터 끝까지 전부 뒤져 봅니다!)

인덱스란 각 행을 빠르게 찾을 수 있는 정보 값(또는 값들)이고, 그러니 정보를 빠르게 찾기 위해서는 인덱스 설정이 필수라는 거네~

인덱스는 어떤 게 있나요?

• Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees.
  (PK, UNIQUE, INDEX, FULLTEXT 등이 있다… B-tree 자료구조로 저장된다.)

그 말인즉, 정보를 빠르게 찾기 위해서 인덱스(ex. PK)를 설정한다는 건데. PK 말고도 다른 옵션이 존재한다.

오호 그럼 인덱스를 추가하는 작업 시 해당 칼럼을 토대로 정렬 정보 저장(B-tree 등)이 일어나고,

다음 삽입시 해당 정보의 수정 후 삽입되기 때문에 작업 시 느려지겠구먼?

그럼 인덱싱을 하는 칼럼이 늘어날 때는 얼마나 느려질까?

 

인덱스를 추가하는 것에 관련된 질문과 그에 대한 답변들

https://stackoverflow.com/questions/418744/what-is-the-cost-of-indexing-multiple-db-columns

What is the cost of indexing multiple db columns?

이 질문자는 마치 내가 궁금한 것처럼 멀티 칼럼의 인덱싱이 소요되는 성능 상의 비용에 대해 물어보고 있다

• More indexes tend to slow down inserts and speed up queries. So it's always a balancing act. That's why you only add indexes in specific response to a problem. Anything else is premature optimization and should be avoided.
  (인덱싱을 하면 삽입 속도는 느려지고, 쿼리 속도는 빨라짐. 특정 문제에 도달했을 때만 인덱싱을 추가하기 바람)
  
  
• My results are: adding average index (1-3 columns in an index) to a table - makes inserts slower by 2.1%. So, if you add 20 indexes, your inserts will be slower by 40-50%. But your selects will be 10-100 times faster.
  (MySql에서 실제로 수행 결과 1~3개의 칼럼 인덱스로 추가 지정 시 삽입 속도는 2.1% 정도 내려갔다고 한다. 20개의 인덱스 추가 시엔 삽입 속도가 최대 50% 정도 내려갈 수 있지만 select 작업의 경우 최대 100배 빨라질 수 있을 것이다.)

그렇다면 위 학생-수업 테이블의 경우, 겨우 1개의 인덱스(PK)가 지정되니, 성능 상의 이슈로 엄청 영향을 끼칠 것 같아 보이진 않는다. (친구한테 해당 테이블에 어느 정도 양이 들어오는지 까지는 듣지 못했지만! )

 

다시 공식 document를 보자면

• If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3). For more information, see Section 8.3.6, “Multiple-Column Indexes”
  

-> 여러 개의 칼럼을 index로 지정 가능하다.

-> index를 지정하면 지정한 순서로 가장 왼쪽의 (left-most) 칼럼 값으로도 인덱싱이 된다. (=빠르게 접근 가능)

 

예를 들어,

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

이라고 지정했다면 (last_name, first_name index 지정)

SELECT * FROM test WHERE last_name='Jones';

SELECT * FROM test
  WHERE last_name='Jones' AND first_name='John';

SELECT * FROM test
  WHERE last_name='Jones'
  AND (first_name='John' OR first_name='Jon');

SELECT * FROM test
  WHERE last_name='Jones'
  AND first_name >='M' AND first_name < 'N';

해당 쿼리에 대해서는 index 가 걸린 연산이기 때문에 빠르게 조회 가능하다는 것이다.

정리해보자면

• PK는 행을 구분하기 위해, 다른 테이블과의 연관 관계를 알기 위해 (PK-FK) 사용한다.
• PK 지정 시, INDEX가 걸려 해당 칼럼으로 조회 시 굉장히 빠르게 접근 가능하다. 대신 INDEX 정보 값이 추가되고, 삽입시마다 해당 정보 값을 갱신!
• Index 값(PK 값)으로 여러 개의 칼럼을 지정 가능하다.
• 꼭 지정될 필요는 없다… 하지만, 지정하는 걸 추천한다!

다시 맨 처음 질문으로 돌아와 보자.

https://stackoverflow.com/questions/2190272/sql-many-to-many-table-primary-key

SQL - many-to-many table primary key

나와 동일한 궁금증에 대해 답변으로 나온 걸 내 질문에 대입하자면

 

학생-수업 테이블에서는

각 테이블의 PK 값들을 모아서 하나의 테이블을 구성했기 때문에

{FK(학생 PK) - FK(수업 PK) }를 하나의 PK로 지정해도 전혀 문제가 없다.

1. PK의 조건을 만족하기 때문에 (1. unique, 2. not null )
2. PK로 지정 시, index 가 걸리는데 멀티 칼럼이라고 해서 속도에 문제가 생길 정도로 느려지지도 않는다.

하지만 쿼리 지원을 위해서라면 INDEX로

{FK(수업 PK) - FK(학생 PK) } 걸어 주면 좋을 것 같다.

 

그럼 멀티 칼럼 pk를 어떻게 지정하는지 보자. (Mysql 기준)

CREATE TABLE table1(
	`id` BIGINT(20) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`email` VARCHAR(20),
	PRIMARY KEY(`id`, `name`)
);

(+멀티 칼럼일 때는 left-most로 인덱스를 타니)

CREATE TABLE table1(
	`id` BIGINT(20) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`email` VARCHAR(20),
	PRIMARY KEY(`id`, `name`),
	INDEX(`name`, `id`)
);

인덱스로 추가해주면 빠르게 접근 가능할 것이다.

 

 

그렇다고 한다. 고맙다 덕분에 나도 하나 찾아봤다.

 

 

출처

• 기본키란? -> https://ko.wikipedia.org/wiki/%EA%B8%B0%EB%B3%B8_%ED%82%A4
• PK가 반드시 필요할까? -> https://stackoverflow.com/questions/840162/should-each-and-every-table-have-a-primary-key
• PK가 반드시 필요할까? 2 -> https://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=qna_db&wr_id=212062
• 멀티 칼럼을 PK로 지정 시 비용 소모? -> https://stackoverflow.com/questions/418744/what-is-the-cost-of-indexing-multiple-db-columns
• MySql 공식 document -> https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html
• many-to-many 테이블에서의 PK 지정 -> https://stackoverflow.com/questions/2190272/sql-many-to-many-table-primary-key
• 기본키에 대한 이해 -> https://kimsyoung.tistory.com/entry/SQL-기본-키에-대한-이해
• 멀티 칼럼 PK의 장단점 -> https://stackoverflow.com/questions/2204381/what-are-the-pros-and-cons-of-using-multi-column-primary-keys