Mysql 테이블 인덱스 설정 - 야호 나도 드디어 인덱싱 해봤다!!!!

개요

코드 잘 짜야하는 이유에 대한 글을 먼저 작성하고 있었는데 지금 너무 신나고 빨리 블로그에 공유하고 싶어서 점심시간에 글을 작성한다.

오늘은 금요일, 주간회의가 있는 날이여서 회의를 하던 중 로그인이나 vpn 접속 및 기타 조회등에서 너무 느려진다라는 이슈가 있었다.

듣자마자 내 머리속에서는 본능적으로 이건 회사에서 디비 튜닝을 할 수 있는 절호의 기회다 라고 생각하여 무조건 제가 보겠습니다 라고 말했다. 그리고 멋지게 10분도 안되어 해결하였다! 처음 해보는 디비 튜닝이여서 너무 신났고 내가 봐도 너무 쉽고 간단하지만 공유해본다.

슬로우 쿼리 로그 설정

아직까지는 느려지는 원인이 디비에 있다고 확신하기엔 근거들이 부족한 상태지만 합리적인 의심을 했다.

데이터가 백만건 넘게 쌓였을때 로그인, 조회 등이 느려진다면 mysql 문제 아니겠는가, 결국 로그인도 하나의 조회이다.

그래서 근거들을 확실하게 모으기 위해 슬로우 쿼리 로그를 기록하는 옵션을 활성화했다.

위와 같은 옵션을 mysql 설정 파일중 [mysqld] 부분에 설정해줬다.

그리고 무조건 chown 명령어를 이용해서 저 폴더와 파일의 소유주를 mysql로 설정해줘야한다!! 안 그러면 mysql이 기록을 못한다 ㅠㅠ

 

long_query_time은 몇초 이상 걸리는 쿼리들을 기록할지 설정하는 옵션인데 나는 1초로 설정했다.

1초면 너무 빡빡한거 아니오!라고 할 수도 있는데 사실 단순 디비단에서 쿼리가 1초 이상 걸린다는것은 사용자 경험에 큰 영향을 주게 된다.

 

그렇게 슬로우 쿼리 로그를 설정하고 tail -f 를 이용하여 터미널에 슬로우 쿼리 로그를 띄운 채로 느려진다는 시나리오대로 재현해봤다. 

역시나 아래와 같은 정보를 알 수 있었다.

# Time: 240216 10:53:09
# User@Host: -----[-----] @ ----- [-----]
# Thread_id: 43  Schema: -----  QC_hit: No
# Query_time: 1.026230  Lock_time: 0.000067  Rows_sent: 0  Rows_examined: 2021892
# Rows_affected: 0  Bytes_sent: 244
use -----;
SET timestamp=1708048389;
SELECT * FROM ----- WHERE loginName = '-----';

여러 느린 쿼리들이 난무했지만 제일 간단한 예시 하나를 가져왔다. 회사정보이기 때문에 예민한 부분은 -----로 마스킹 처리했다.

 

일단 1.02초 걸리는 쿼리라는것을 알 수 있고 Lock_time이 짧은것으로 봤을때 교착상태 때문에 느려진것이 아닌 단순 조회가 느린것을 알 수 있다. 물론 쿼리를 봐서도 단순 조회기 때문에 교착 상태에 걸릴 이유는 전혀 없기도 하다.

 

그렇다면 우리는 단순히 데이터가 많은곳에서 조회를 하기 때문에 느려졌다는 근거가 확실히 생겼다. 그렇다면 문제가 되는 테이블의 구조를 한번 뜯어보도록 하겠다.

테이블 뜯어보기 

문제가 되는 테이블을 열어보게 된 나는 경악을 금치 못하였다. 테이블 구조 자체는 사용자명과 IP를 저장하는 몇개의 컬럼이 있었다.

그렇다, 그게 끝이다. PK, Unique 등 제약조건이 하나도 걸려있지 않았다. 그래서 데이터가 몇개인지 궁금해서 Count를 쳐보니..

꿈인가?

음..? 요즘 피파를 즐겨하면서 팀 가치가 2조씩 되다보니 내 숫자 감각이 좀 얼었었다. 생각보다 적네? 싶었다 그래서 몇개인지 세어봤더니

숫자로 봤을땐 작아보이지만 200만건이였다. 엥? 보안장비에 이 정도 데이터가 쌓일 일이 있는가? 싶어서 백엔드 코드를 보니 단순히 접속할때만 임시로 부여되는 ip들을 저장하는 로직이였다. 한마디로 디비에 계속 영속되어 있을 필요가 없는 일회용 데이터였다.

 

이 데이터들을 삭제도 안하고 만료도 안 시키고 그냥 그대로 쌓이게 내버려 두니 이렇게 많은 숫자가 쌓이게 된것이다.

그래서 추후에 이런 부분들을 redis를 도입해서 사용하거나 다른 조치를 취해보고 싶다.

이걸 보다가 알았는데 일회용 데이터를 저장하는 테이블이 꽤 많다.. 왜일까

쿼리 실행계획 보기

자 이제 쿼리를 DB에서 어떻게 실행할것인지 계획을 물어보도록 하겠다, 실행 계획을 보는법은 간단하다. 

알고 싶은 쿼리문앞에 EXPLAIN을 붙여서 쿼리를 날려보면 된다! 주로 SELECT처럼 조회하는 쿼리에 많이 사용해본다고 한다.

그렇게 되면 이런식으로 쿼리를 어떻게 Innodb에서 최적화 시켜서 실행시킬지 실행계획을 보여주게 된다. 

쿼리 실행 계획을 보는 방법은 다른 글에서 자세히 다루겠다, 여기서 우리가 일단 집중해야하는 부분은 'type'컬럼이다.

대부분 제약조건이 안 걸려서 조회가 느린 경우에는 type이 ALL인 경우가 대다수이다. 위 사진은 이미 인덱스를 추가한 후여서 ref다.

 

내가 처음에 확인했을때는 type이 ALL이였고 이러면 데이터 비교를 위해 테이블에 있는 데이터를 하나하나 비교해본다는 뜻이다.

당연히 시간이 오래걸릴수 밖에 없다, 200만개중 200만번째 데이터면 200만개를 하나하나 다 비교하는셈이다, 얼마나 끔찍한가?

컬럼에 인덱스 설정하기

그래서 나는 비교 기준이 되는 컬럼에 인덱스를 설정해주기로 했다, 사실 이 상황이면 PK를 거는 판단이 더 맞다고 생각하지만 일단 눈으로 성능이 개선이 되는지 보고 싶어서 인덱스를 걸어보기로 했다. (PK는 그동안 많이 사용해봐서 굳이굳이 인덱스를 걸어보고 싶었음)

ALTER TABLE 테이블명 ADD INDEX (인덱스 설정할 컬럼명);

생각보다 간단해서 놀랐다, 물론 지금 이 케이스가 조인문도 없이 간단한 상황이여서 이렇게 간단히 해결되었던것 같다.

 

이 쿼리를 날리게 되면 많은 데이터를 인덱싱하는 시간이 잠시 걸린 뒤 성공적으로 인덱스가 다 걸리게 된다.

 

그런 다음 바로 로그인을 시도해보니 세상에나 5초 정도 걸리던 로그인이 0.5초만에 파박하고 되는것 아니겠는가!!

마무리하며

일단 너무 신났다, 저렇게 인덱스를 설정했더니 마법처럼 속도가 개선된것을 보고 놀라웠다. 나는 B2B 보안회사에서 일할동안은 디비 튜닝이나 성능 개선을 할 경험이 전혀 없다고 생각했다. 하지만 테이블에 제약조건 하나도 안 걸고 만드신 어떤분께서 나에게 정말 좋은 경험을 주셨다 (누군진 몰라요 워낙 오래된 코드라...) 이런 경험을 할 수 있어서 너무나도 행복했다, 앞으로 서비스 회사로 이직하여 할 경험들은 얼마나 더 재밌고 행복할까 기대도 된다.

 

한편으로는 너무 간단해서 불안하다..?라고 해야하나, 분명 이 케이스가 너무 단순한 케이스였기 때문에 간단히 해결했다고 믿고 싶다.

난 더 어렵고 머리아프고 이해하기 힘든 케이스들이 존재할것이라고 믿는다, 그런걸 공부해야지 더 기분이 좋지고 뿌듯하기 때문이다.

 

요즘 옷 대신 개발 서적을 사는게 행복하고 맛있는 음식보다 좋은 코드 한줄 짜는게 더 행복한 시기인것 같다.

빨리 좋은 경험을 할 수 있는곳으로 가서 많이 성장하고 싶다.