[글또 9기] 다섯 번째 글
얼마 전 내가 만들었던 쿼리에서 DB 부하와 데드락이 발생했다.
만든 지 거의 1년이 넘어가는 기능이었고 그 이후에 별 문제없이 잘 돌아가고 있어서 괜찮다고 생각했으나... 최근 DB 스펙 조정 + 트래픽 몰림을 겪으면서 이 쿼리에서 DB 부하와 데드락이 발생했다.
(사실 괜찮았던 게 아니라 얼렁뚱땅 돌아가고 있었던 것...)
이 기능은 유저들이 버튼을 누르면 유저가 버튼을 눌렀다는 값을 insert하는데,
그 전에 해당 테이블에 이미 그 유저가 버튼을 눌렀다는 데이터가 있는지 확인하는 select를 먼저 한다.
그런데 트래픽이 몰리는 시간대에 select에서는 리더 DB 부하가 발생했고, insert에서는 라이터 DB에서 데드락이 발생했다. (하하)
그래서 DB 부하와 데드락의 원인에 대해 공부했던 것을 기록하려고 하는데,
이번 포스팅은 DB 부하가 발생한 건에 대해 먼저 간단히 적어보겠다.
실행 계획에서 type 컬럼의 'INDEX'는 '인덱스 풀스캔'이다.
우선 DB 부하가 있었던 select 쿼리에서 발생했는데, 내가 만든 쿼리를 점검하며 실행계획을 다시 보았다.
그런데... type 컬럼에 INDEX가 있었다.
어 저 INDEX... 인덱스 풀스캔이었던 것 같은데...?
index 접근 방법은 많은 사람이 자주 오해하는 접근 방법이다. 접근 방법의 이름이 index라서 MySQL 서버에 익숙하지 않은 사람이 “효율적으로 인덱스를 사용하는구나”라고 생각하게 만드는 것 같다.
하지만 index 접근 방법은 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다. range 접근 방법과 같이 효율적으로 인덱스의 필요한 부분만 읽는 것을 의미하는 것은 아니라는 점을 잊지 말자.
(...)
일반적으로 index와 ALL 접근 방법은 작업 범위를 제한하는 조건이 아니므로 빠른 응답을 사용자에게 보내야 하는 웹 서비스 등과 같은 온라인 트랜잭션 처리 환경에는 적합하지 않다. 테이블이 매우 작지 않다면 실제로 테이블에 데이터를 어느 정도 저장한 상태에서 쿼리의 성능을 확인해 보고 적용하는 것이 좋다.
<Real MySQL 8.0 (1권)>, pp.445-446
아마 이 기능을 만들고 약 지난 1년간은 '리드 DB 스펙이 높았음 + 해당 테이블의 데이터양이 엄청 많지는 않음 (하지만 점점 쌓여감)' 이었기 때문에 트래픽이 몰려도 이 인덱스 풀스캔을 감당하지 않았을까 싶다.
사실 리드 DB 스펙을 내린 게 부하 발생의 시작이라고 볼 수 있겠지만... 일단 이 select 쿼리가 효율적인 쿼리가 아님은 분명했다.
하지만 아무리 쿼리 튜닝을 해도 계속 실행계획을 보면 인덱스 풀스캔이 떴다.
왜냐하면 이 테이블에 걸려있는 인덱스가 member_id, date, board_id 다중 컬럼 인덱스였는데,
date와 board_id는 where절에 조건으로 있었지만, member_id는 where 절에 없어서 인덱스 풀스캔을 타는 것 같았다.
(쿼리를 올릴 수 없어 설명을 하기 어렵긴 한데...
여튼 이 쿼리는 member_id를 where 절에 넣을 수 없는 쿼리였다.)
index 접근 방법은 다음 조건 가운데(첫 번째 + 두 번째) 조건을 충족하거나
(첫 번째 + 세 번째) 조건을 충족하는 쿼리에서 사용되는 읽기 방식이다.(1) range나 const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
(2) 인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우 (즉, 데이터 파일을 읽지 않아도 되는 경우)
(3) 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우 (즉, 별도의 정렬 작업을 피할 수 있는 경우)
<Real MySQL 8.0 (1권)>, p.445
책에 따르면 위와 같은 경우에 인덱스 풀스캔을 사용한다고 하는데
나의 경우엔 (1), (2)가 해당되었다.
그럼 (1)에서 이런 의문이 들 수 있다.
range, const, ref 과 같은 접근 방법은 어떤 경우에 해당하는가?
- const :
- 조인의 순서와 관계없이 프라이머리 키나 유니크 키의 모든 칼럼을 이용하는 WHERE 조건절을 가지고 있으며 동등(Equal) 조건으로 검색 (반드시 1건의 레코드만 반환)
- 다중 칼럼으로 구성된 프라이머리 키나 유니크 키 중에서 인덱스의 일부 칼럼만 조건으로 사용할 때는 const 타입의 접근 방법을 사용할 수 없다. 이 경우에는 실제 레코드가 1건만 저장돼 있더라도 MySQL 엔진이 데이터를 읽어보지 않고서는 레코드가 1건이라는 것을 확신할 수 없기 때문
- eq_req :
- eq_ref 접근 방법은 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다.
- 조인에서 첫 번째 읽은 테이블의 칼럼값을 이용해 두 번째 테이블을 프라이머리 키나 유니크 키로 동등(Equal) 조건 검색(두 번째 테이블은 반드시 1건의 레코드만 반환)
- 다중 칼럼으로 만들어진 프라이머리 키나 유니크 인덱스라면 인덱스의 모든 칼럼이 비교 조건에 사용돼야만 eq_ref 접근 방법이 사용될 수 있다.
- ref:
- 조인의 순서와 인덱스의 종류에 관계없이 동등(Equal) 조건으로 검색(1건의 레코드만 반환된다는 보장이 없어도 됨)
- 프라이머리 키를 구성하는 칼럼 중에서 일부만 동등(Equal) 조건으로 WHERE 절에 명시되었을 경우, 조건에 일치하는 레코드가 1건이라는 보장이 없어 const가 아닌 ref 접근 방법이 사용됨
→ 이 세 가지 접근 방법 모두 WHERE 조건절에 사용하는 비교 연산자는 ‘동등 비교 연산자’여야 한다는 공통점이 있다. 동등 비교 연산자는 ‘=’ 또는 ‘<=>’을 의미한다. ‘<=>’ 연산자는 NULL에 대한 비교 방식만 조금 다를 뿐 ‘=’ 연산자와 같은 연산자다.
<Real MySQL 8.0 (1권)>, p.436-439
일단 문제가 된 내 select 쿼리는 다중 컬럼 유니크 인덱스(member_id, date, board_id)에서 member_id가 where 절에 없으므로 const, eq_req 모두 탈락이었다.
(ref는 왜 탈락인지 약간 아리송...)
여튼 이러한 이유로 인덱스 풀스캔을 타는 것으로 확인...!!!
- 그래서 해결 방법은...?
그래서 나는 인덱스 설계가 잘못된 것 같아 인덱스를 바꿔야겠다고 생각했는데,
그때 의견을 들었던 게 코드단에서 쿼리를 분리 해서 지금 있는 인덱스를 타게 하라는 거였다.
(문제가 된 select 쿼리가 Inner join으로 두 테이블 정도 물려있었다)
DB 접근은 최소화하는 게 좋다고 생각해서 조인을 했는데,
쿼리를 분리하게 되면 1번 접근하던 걸 3번으로 늘어나긴 하지만
결과적으로 simple 쿼리 3개가 되었고, 인덱스를 타고 풀스캔은 돌지 않게 되면서 일단 해결이 된 것 같다.
당시엔 코드도 더러워지고 DB 접근 횟수도 많아지는데 이게 맞나? 싶었지만,
지금 돌이켜보니 이것도 또 하나의 방법이고, 어쩌면 나의 방법보다 더 효율적이고 맞는 방법일수도 있겠다는 생각이 들었다.
이건 이후에도 모니터링하면서 더 공부하도록 해봐야겠다.
인덱스 풀스캔이 꼭 나쁜 것은 아니다.
덧붙이자면, 쿼리를 튜닝한다는 것이 무조건 인덱스 풀 스캔과 테이블 풀 스캔을 사용하지 못하게 하는 것은 아니라고 한다.
옵티마이저가 판단하기를 풀스캔이 낫다고 판단할 때는 풀스캔을 돈다고 한다.
예를 들면 데이터양이 적거나 그럴 경우엔 풀스캔이 종종 뜰 때가 있다.
그래서 적절하게 써야할 때는 쓰는 것이 맞는 것 같다.
예를 들면 <MySQL 8.0 (1권)>에서는 다음과 같은 예시를 들었다.
다음 쿼리는 아무런 WHERE 조건이 없으므로 range나 const, 또는 ref 접근 방법을 사용할 수 없다. 하지만 정렬하려는 칼럼은 인덱스(ux_deptname)가 있으므로 별도의 정렬 처리를 피하려고 index 접근 방법을 사용했다.
EXPLAIN SELECT * FROM departments ORDER BY dept_name DESC LIMIT 10;이 예제의 실행 계획은 테이블의 인덱스를 처음부터 끝까지 읽는 index 접근 방법이지만 LIMIT 조건이 있기 때문에 상당히 효율적이다. 단순히 인덱스를 거꾸로(역순으로) 읽어서 10개만 가져오면 되기 때문이다. 하지만 LIMIT 조건이 없거나 가져와야 레코드 건수가 많아지면 상당히 느린 처리를 수행한다.
- (참고) 풀 테이블 스캔과 인덱스 풀스캔의 차이
참고로 type 'ALL' (풀 테이블 스캔)과 type 'INDEX'의 차이는 다음과 같다고 한다.
index 접근 방법은 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔 방식과 비교했을 때 비교하는 레코드 건수는 같다. 하지만 인덱스는 일반적으로 데이터 파일 전체보다 크기가 작으므로 인덱스 풀 스캔 시 풀 테이블 스캔보다 빠르게 처리되며, 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 훨씬 효율적이라 할 수 있다.
다음 포스팅엔 insert 쿼리에서 발생한 데드락에 대해 써보겠다.
끝~
