[MySQL] PK, unique key 컬럼 선정 방법 (aka. 복제 지연 예방 방법)

 

인덱스가 있어도

복제 방식으로 인해 unique key가 필요한 경우가 있다.

특히 binlog 기반으로 복제하는 MySQL의 경우 대량의 데이터 변경을 한 쿼리에서 할 경우 복제 지연이 발생할 수 있다.

 

예를 들면,

ROW-BASED 복제 환경에서 unique key나 primary key가 없는 테이블에

Primary에서 100만건 데이터를 삭제한다고 가정해보자.

(ROW-BASED 복제는 binary log를 쿼리 기준으로 기록하는 것이 아니라 쿼리에서 변경된 데이터 row 기준으로 기록하는 것을 말한다.)

Primary에서는 1회의 index/table full scan으로 처리되지만, row-based 복제에서는 binary log가 쿼리 단위가 아닌 변경된 데이터 기준으로 쌓이게 된다. 따라서 100만건의 데이터 변경이 binlog에 기록되게 되고, Secondary DB는 이를 적용하게 되어 100만번의 index/table full scan으로 처리된다.(O-O) 

단순한 쿼리 하나로 인해 secondary DB에서 몇 십시간의 복제 지연이 발생할 수 있다니...놀랍다!!

 

이전에 아래 포스팅에서 자세하게 기록했다.

https://june-coder.tistory.com/61?category=1089434

---

위 포스팅에서 적었듯이

데이터 변경량을 줄이는것이 해결 방법 중 하나다.

100만건의 데이터를 한 쿼리에서 처리하지 않고, 몇 개의 쿼리에서 나눠 처리하도록 하는 것이다.

하지만 때에 따라서는 인덱스나 PK의 부재나 어플리케이션 로직상의 문제로 몇 건의 데이터로 나눠 처리하는 것 자체가 불가능할 수가 있다.

 

더 근본적인 해결방법은 복제할 때 100만번의 full scan이 일어나지 않도록 unique한 인덱스나 primary key를 만드는 것이다. 그러면 full scan이 일어나지 않고 1건만 찾고 종료하게 된다. 그러면 대량의 데이터를 변경하더라도 복제 DB에서 따라잡는 부담이 크지 않다.

(Unique index는 동등 조건으로 검색할때 1건만 찾으면 종료하기 때문에, ROW-BASED로 기록된 binlog의 한 줄 한 줄을 unique한 인덱스나 PK로 바로 찾을 수가 있다.)

---

Unique key 선정 방법?

unique한 칼럼이 하나도 없다면

기존 칼럼들의 조합으로 unique함을 보장할 수 있는지 확인해서 unique 컬럼을 추가하면 된다.

 

기존 데이터를 바탕으로 쿼리로 간단하게 확인할 수 있다.

모든 칼럼을 나열하여 group by를 해서 1건 이상 나오는지 보면 된다.

(1건도 안 나온다면 해당 조합이 unique한 조합이 됨을 보장하기 떄문)

따라서 이 조합으로 unique key를 생성하면 된다.

 

쿼리 예시)

Select count(*) from (select col1, col2, col3, count(*) from table1 group by col1, col2, col3 having count(*) > 1) a;