UNIQUE 제약으로 중복 검사해도 될까?

이슈 내용

unique 제약을 통해 중복을 검사하면 안 될까?

결론

중복 검사 방법에는 두 가지 방법이 있습니다.

 

1. 직접 select 해서 체크하는 방법

이 방법은 원하는 대로 예외를 터트릴 수 있고 훨씬 명확한 코드를 제공할 수 있습니다. 하지만 동시성 이슈가 발생할 수 있습니다.

2. DB UNIQUE 제약에 의존하는 방법

사실 DB 관련 예외가 터지면 이걸 일일이 잡기가 애매합니다. 따라서 공통 예외로 처리하여 사용자에게 서버 쪽에 문제가 있다 정도로만 알릴 수 있습니다.

3. 뭐가 더 좋을까

1번 방법은 동시성 이슈가 발생한다는 단점이 있습니다. 하지만 실제 상황에서 동시성 이슈가 발생할 일은 극히 드뭅니다. 따라서 우선은 1번 방법을 사용해서 원하는 로직으로 깔끔하고 명확하게 구현하고 DB에는 UNIQUE 제약을 걸어둡니다. 만약 동시성 이슈로 인해 DB Exception이 발생하면 공통 예외를 던져서 사용자에게 서버에 문제가 있다 정도만 알리고 추후 디버깅을 위해 자세하게 로그를 남겨두는 방식이 좋을 것 같습니다.

4. title 칼럼에 index 적용

1번 방식으로 진행하게 되면 이름 중복 검사를 실시할 때 full-scan이 발생합니다. 추후 데이터가 증가하게 되면 탐색 성능이 좋지 않기 때문에 index를 적용해서 이를 개선하는 것이 좋을 것으로 보입니다. index는 B+Tree 구조를 가지기 때문에 탐색에 O(log2n)의 성능을 가집니다. 따라서 탐색 성능을 개선할 수 있으며 index에도 unique 옵션이 있기 때문에 함께 적용하도록 합니다. 결론적으로 중복 검사가 필요한 칼럼에는 unique index를 설정하도록 합니다.

개선하기

초기

현재 프로젝트에서 아이디와 모임명의 중복 검사에서 단순 select 문과 where 조건으로 이름 중복을 검사하고 있습니다. 이는 full-scan이 발생할 것으로 예상되는데 비슷한 table을 구성해서 쿼리의 실행 계획을 확인해보겠습니다.

 

아래는 Intellij에서 DB를 연결하고 콘솔 창에서 쿼리의 실행 계획을 확인한 결과입니다. (테스트용 ROW는 10만 개를 넣어뒀습니다) (MySQL은 EXPLAIN 키워드를 사용하면 실행 계획을 보여줍니다) (SQL_NO_CACHE를 사용한 이유는 검색 결과의 캐싱을 막기 위해서입니다)

EXPLAIN select SQL_NO_CACHE * from member where name = "member_name-9981";

그림이 잘 보이지 않는데요,,, type은 ALL이 나왔고 rows는 10만 개가 나왔습니다. 네.. type이 ALL이라는 것은 Full-scan을 의미하며 DB에 많은 부담을 줄 수 있으므로 개선해보도록 합니다.

index 적용

이번에는 name 컬럼에 index를 적용해서 다시 쿼리를 수행하고 실행 계획을 확인한 결과입니다.

좋네요. type에는 인덱스로 지정된 칼럼끼리의 '=' 과 같은 조건문 검색 방식인 ref가 나왔고 검색에 사용된 칼럼은 4개로 양호합니다. 제가 직접 name 칼럼으로 생성한 i_name 인덱스가 key로 쓰였다고도 나오네요.

그리고 index를 적용하다가 "max key length is 1000 bytes" 에러를 만났습니다. 아무래도 칼럼 사이즈가 너무 크면 index 지정에 문제가 있는 것 같습니다. 이 부분에 대해서는 index에 대해서 자세히 다룰 때 다시 다뤄보도록 하겠습니다. 일단 칼럼의 사이즈를 줄여주면 문제가 해결되므로 참고하시면 되겠습니다.

alter table 테이블명 modify 컬럼명 varchar(사이즈);

unique index 적용

unique index에 대해서 하나만 짚고 넘어가자면 unique index나 일반 index나 다를 건 없습니다. unique index는 단지 unqiue 제약이 추가되었을 뿐입니다.

 

그렇기 때문에 사실 unique index는 성능 상 다를 것이 없습니다. 왜냐하면 unique index나 일반 index나 구조적인 차이가 없기 때문에 여기서 발생하는 검색 속도의 차이는 구조적인 차이가 아닌, 중복되는 칼럼의 개수 차이입니다. 이 점을 고려하면 사실 두 방식의 성능을 비교하는 건 무의미하다고 보입니다.

 

다만 말했듯이 중복되는 데이터가 없기 때문에 속도 자체는 unique index가 더 빠를 수 있겠습니다. 그런데 인덱스 쓰기 과정은 unique index가 index 보다 느립니다. unique index는 키값을 쓸 때 중복된 값이 있는지 체크해야 하기 때문입니다. 문제는 MySQL이 유니크 인덱스에서 중복된 값을 체크할 때 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번하게 발생합니다.

 

따라서 꼭 필요한 경우가 아니라면 unique index를 적용할 필요는 없습니다. 저의 경우에는 위에서 이야기한 대로 name 컬럼에 unqiue 제약이 필요하기 때문에 unqiue index를 프로젝트에 적용할 계획입니다.

인덱스는 막 써도 될까?

우선 인덱스는 WHERE 절을 사용하는 경우에 효과적입니다. 그리고 인덱스는 DB의 메모리를 사용하여 테이블 형태로 저장되기 때문에 남용하는 것은 좋지 않습니다. 또한 조회 시 WHERE 절에 자주 사용하고 고유한 값들을 가지는 칼럼 위주로 설정하는 것이 좋습니다.

 

인덱스를 설정하기 위해 여러 후보 칼럼이 있을텐데 아래 네 가지 기준을 사용하면 효율적으로 인덱스 설정이 가능합니다.

1. 카디널리티

카디널리티는 칼럼의 중복도와 반비례하는 개념입니다. 중복도가 높으면 카디널리티가 낮고 중복도가 낮으면 카디널리티가 높습니다. 즉 카디널리티가 높을수록 인덱스로 설정하기 좋은 칼럼입니다.

2. 선택도

선택도도 중복도와 관련 있는 개념입니다. 선택도는 한 테이블에서 어떤 지표(칼럼 조건)로 검색했을 때 선택되는 레코드의 수를 %로 나타낸 것이며 당연하게도 선택되는 것이 적을수록 인덱스에 적절한 칼럼이라고 볼 수 있습니다. 결국 칼럼의 카디널리티가 높을수록 선택도는 당연히 낮아지게 되고 낮을수록 인덱스에 적합하다고 볼 수 있습니다.

3. 활용도

가장 중요하다고 생각합니다. 활용도는 실제 작업에서 얼마나 잘 활용되는지에 대한 값입니다. WHERE 절에 자주 활용될수록 좋습니다.

4. 중복도

이 중복도는 하나의 칼럼에 인덱스가 몇 번 설정되었는지를 의미합니다. 하나의 칼럼에 여러 개의 인덱스가 설정될 수 있는데 어차피 DB가 더 빠른 인덱스로 조회를 수행합니다. 따라서 불필요한 인덱스는 줄이는 것이 좋고 인덱스가 적게 설정된 칼럼을 인덱스로 설정하는 것이 효율적이라고 볼 수 있습니다. 

5. +

• 인덱스 키의 크기는 되도록 작게 설계해야 성능에 유리하다.
• 분포도가 좋은 칼럼(좁은 범위), 기본 키, 조인의 연결 고리가 되는 칼럼을 인덱스로 구성한다.
• 단일 인덱스 여러 개보다 다중 칼럼 인덱스의 생성을 고려한다.
• 업데이트가 빈번하지 않은 칼럼으로 인덱스를 구성한다.
• JOIN 시 자주 사용하는 칼럼은 인덱스로 등록한다.
• 되도록 동등 비교(=)를 사용한다.
• WHERE 절에서 자주 사용하는 칼럼에는 인덱스 추가를 고려한다.
• 인덱스를 많이 생성하는 것은 INSERT/UPDATE/DELETE의 성능 저하의 원인이 될 수 있다.
• 인덱스 스캔이 테이블 순차 스캔보다 항상 빠르지는 않다. 보통 선택도(selectivity)가 5~10% 이내인 경우에 인덱스 스캔이 우수하다.

 

참고사항

https://www.inflearn.com/questions/59250

안녕하세요. Unique Index 데이터 저장에 대해서 질문드립니다. - 인프런 | 질문 & 답변

https://blog.naver.com/PostView.nhn?isHttpsRedirect=true&blogId=realuv&logNo=10184319823 

[MySQL] MySQL Query Plan 보는 법

https://kmhan.tistory.com/325

[Mysql] 테이블 생성시 'ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes' 문구가 떴을 때

https://d2.naver.com/helloworld/1155

https://jojoldu.tistory.com/243