평소와 같이 평화롭게 회사생활을 하던중 위기가 찾아왔다…
학급 모든 학생의 과제 이력을 엑셀파일로 출력하는 기능을 개발하게 되어 처음으로 많은 양의 데이터를 다룰수 있는 경험을 하게 되었는데
(과제 이력 테이블의 로우 수는 약 100만건 정도…
)
문제는 과제 이력 테이블 뿐 아니라 조인해야할 테이블도 굉장히 많았다는 것이다.
처음에는 복잡한 비지니스 로직까지 쿼리로 전부 처리하려고 복잡한 조인 로직에 함수까지 잔뜩 사용했던게 문제인줄 알고 쿼리부터 다시 짜기 시작했고 좀처럼 나아지지 않는 성능과 이대로는 운영이 불가능하다고 생각할때쯤 인덱싱에 대해서 찾아보기 시작했다.
MySQL을 잘 알아서 정리를 한것이 아니라, 잘 알고 다음에 봤을때 회고하려 작성한 글이기에.. 다소 오류가 있을수 있다.
인덱스란?
인덱스(Index)는 데이터베이스에서 검색 속도를 향상시키기 위해 사용되는 자료 구조다. 마치 책의 뒤편에 있는 목차처럼, 인덱스는 데이터를 빠르게 찾을 수 있도록 도와준다.
update, delete의 성능을 희생하는 대신 select의 성능을 향상시킨다.
단순히 update, delete가 느린것이 아니라, 인덱스가 없는 컬럼을 조건으로 update, delete를 하게 되면 굉장히 느려 많은 양의 데이터를 삭제 해야하는 상황에선 인덱스로 지정된 컬럼을 기준으로 진행하는게 좋다.
인덱스 키의 크기
InnoDB는 디스크에 데이터를 저장하는 가장 기본 단위를 페이지라고 하며, 인덱스 역시 페이지 단위로 관리가 된다.
페이지 크기는 16KB로 고정되어 있지만, 실제로 인덱스 키의 크기와 페이지 내 항목 수 계산에서 중요한 점은 인덱스 구조에 따라 다를 수 있다고 한다.
예를 들어, 내가 설정한 인덱스 키의 크기가 16바이트이고, 각 자식 노드의 주소 크기가 12바이트인 경우, 하나의 페이지에는 다음과 같이 16KB (페이지 크기) / (16바이트 + 12바이트) = 585 585개의 항목이 저장된다.
이 경우, 인덱스 키가 32바이트로 커지면 한 페이지에 저장할 수 있는 항목 수는 다음과 같이 16KB (페이지 크기) / (32바이트 + 12바이트) = 372 372개로 줄어든다.
위에 예시로 500개의 row를 읽을때 16바이트일때는 1개의 페이지에서 다 조회가 되지만, 32바이트일때는 2개의 페이지를 읽어야 하므로 성능 저하가 발행하게 되는것이다.
위 사진에서보면 chat_id bigint(20)이랑 chat_name varchar(255) 두 컬럼을 본다면 bigint 8바이트의 크기가 varchar(255) 255바이트 크기보다 인덱스 효율이 좋다고 이해하면 될거 같다.
인덱스 생성
다음 쿼리를 사용해서 인덱스를 추가 할 수 있다. (사실 사용중인 워크벤치에서 추가하는게 쿼리보다는 간단하지만 편안함에 익숙해지지는 말자..)
1 |
|
다음 쿼리를 사용해서 테이블에 인덱스를 조회해볼수 있다.
1 |
|
조회하는 컬럼에 인덱스를 추가하여 해당 컬럼으로 조회 및 조인하는 쿼리로 수정했는데 18s를 소요하던 퀄리가 1s까지 줄어들었다.
개발 서버에서 테스트를 진행하고 적용하기까지 책임님께서 도움을 주셨고 추가적으로 쿼리에 사용했던 복잡한 함수 및 비지니스 로직을 service 계층으로 옮기는것으로 0.01ms라는 말도 안되는 성능 개선을 해냈다. 인덱스를 무분별하게 사용하면 반대로 성능 저하도 일으킬수도 있다는 조금 더 딥하게 공부할 필요가 있을거 같다.
마무리
부족한 글솜씨와 정확한 정보를 작성했는지 모를 불안감을 가지며 첫 번째 글을 작성해봤다. (부끄러움은 미래 나의 몫이겠지)
노션에 정리하였던 내용을 블로그 포스팅으로 옮기느라 직접 개선한 사진을 첨부 못한게 아쉽지만, 인덱스를 사용하여 말도 안되는 성능 향상 결과를 볼 수 있어 빠른 요구사항에만 대처하기 바빠 성능을 뒤로 미뤄두었던 자신을 돌아보게 되었다.
추후에 테스트 데이터를 통해 성능 확인 사진을 추가 첨부하도록 노력하겠다.