본문 바로가기

개발/Database

[MySQL] 테이블에서 중복을 피하는 방법


Background

먼저 왜 이러한 고민을 하게 되었는지 배경을 정리하고자 합니다. 

이번에 사용한 Database는 MySQL이고, 엔진은 InnoDB 입니다. 

( * MySLQ 사용 엔진은 SELECT engine FROM information_schema.TABLES where table_name=’your_table_name’ 으로 확인할 수 있습니다)


데이터를 insert 할 때, 두 필드를 조건으로 유니크함을 보장해야 했습니다. 

단순히 두 필드를 복합키를 걸어서 유니크 키로 사용하면 해결될 문제입니다. 

하지만 복합키를 사용할 수 없는 상황이었습니다.


** Composite Key 를 허용하지 않음

저희 회사에서는 테이블에 Composite Key 복합키를 허용하지 않습니다. 

또한 모든 테이블은 Auto increment 를 Primary Key로 갖습니다. 

MySQL InnoDB 에서 Auto increment PK와 Composite Key를 같이 사용하게되면 결과적으로 인덱스를 두번 검색해야 되서 약간의 성능이슈가 있을 수 있어서 그런 것 같습니다. 

왜 Auto increment PK와 Composite Key 를 같이사용하면 InnoD 에서 인덱스 스캔이 두번발생하는지는 다음과 같습니다. 

(* 원문 : https://stackoverflow.com/questions/1460465/composite-primary-key-performance-drawback-in-mysql )



[Composite Primary Key : Auto increment PK가 아닌 경우]

Auto increment를 사용하지 않는 테이블의 경우 아래와 같이 Composite Key 인덱스만 한번 검색하면 로우포인터를 얻을 수 있습니다.


[Composite Key + Auto increment PK]

하지만 Auto increment(id)와 Composite key(col1,col2)를 같이 사용하게될 경우 select 하기 위해 결과적으로 두 번의 스캔이 일어납니다. 첫번째로 (a,b)를 인덱스에서 검색해서 auto increment 값인 id 를 얻어오고, 두번째로 id를 이용해서 로우포인터를 얻기 위해 또다른 인덱스를 스캔합니다. 하지만 이 두번째 작업은 매우 미세해서 크게 성능상 이슈가 없다고 합니다. 이 경우 insert는 당연히 auto-increment를 중복을 걱정할 일이 없습니다. 




MySQL에서 중복을 피하는 방법


1. 유니크 키 사용

저의 경우와 다르게 MySQL이 제공해주는 유니크 키 기능을 사용할 수 있다면 사용합니다. 

서버에서는 중복이 발생할 경우, 던지는 예외만 잡아서 처리하면 됩니다. 

하지만 그럴 수 없는 상황이여서, DB가 해결해야될 문제를 DB가 아닌 곳, 일반적으로는 서버 로직으로 해결해야되는 상황입니다. 



2. Select before Insert

가장 간단하게는 DB insert전에 select를 한번 해서 중복체크를 하고 insert를 실행합니다. 

물론 select, insert를 하나의 트랜잭션으로 구성합니다. 

이럴 경우 insert가 일어날때마다 중복검사의 대상이 되는 필드를 조건으로 전체 테이블을 select 해야 합니다. 

데이터양이 많지 않을 경우 문제가 크지 않겠지만, 매번 전체 select를 하는 것은 문제가 되므로 중복체크가 필요한 필드에 인덱스를 걸어주는 것이 좋아보입니다. 

이러한 방식을 MySQL에서는 ** exists 키워드로 제공합니다. 

예를들어 아래와 같이 사용하면 field에 특정 value가 없을때만 insert 하도록 합니다. 

이것도 보시는 것처럼 insert전에 한번 select를 하는 것과 같습니다.


INSERT INTO table (field)
SELECT 'value' FROM DUAL
WHERE NOT EXISTS (SELECT * FROM table WHERE field='value')



3. Select Distinct

두번째는 Select할 때 유니크함을 보장하는 것입니다. 
실제로 DB 에는 여러개가 들어갈 수 있지만, Select 할때 하나만 가져와서 사용합니다. 
저의 경우도 중복이 발생할 활률이 매우 적은 경우였습니다. 
저의 경우 웹 서비스에서 유저가 순간적으로 동시에 여러 요청을 보내지 않는 이상 발생할 수 없는 케이스 였습니다.  
하나의 DB insert 가 일어나는 짧은 시간 동안 또 다른 요청이 중복해서 올 가능성이 적기때문에 괜한 걱정일 수 있습니다. 
이럴경우 괜히 작은 확률로 일어날 중복 문제에 대해 고민하지 말고 select에서 한개만 가져와서 사용하는 것도 하나의 방법이 될 수 있습니다. 


회고

결과적으로 exist 키워드를 이용해서 해결했지만, 뭔가 마음에 들지않았습니다. 

전사적으로 Auto increment를  PK로 한다는 것은 방침이니 이해가되지만..

인덱스를 한번 더 탄다는 이유로 복합키 구성이 불가하여, 두 필드 조합의 유니크함을 보장하기 위해 서버쪽에 로직을 추가해야만 했습니다. 

복합키 구성이 성능에 이슈가 많이될까 궁금합니다.