[MySQL] 5초 -> 1초로 성능 개선하기 ( Explain / Index / JMeter )

📝 인덱스가 궁금하신 분들은

[MySQL] Index, B-Tree란 ? ( 인덱스 키 값 계산 )

[MySQL] Index가 사용되지 않는 6가지 경우 ( with 10만 건 예제 데이터를 통한 실습 )

 

 

 

회사에서 프로젝트 진행 중에 약 5만 건의 회원 데이터를 조회하는 쿼리의 속도가 이상하다는 제보를 받았다.

5만 건이면 많은 데이터도 아닌데 불구하고 5초의 시간이 소요 됐다.

재밌는 사건 발생 ! 해결하러 가보자

( 사실 10월에 해결했던 문제인데 회사일이 사진으로만 남겨 뒀다가 이제 글 쓰는 건 비밀 ... )

🐳실행계획

MySQL 은 EXPLAIN 기능을 활용해 작성한 쿼리의 실행 계획을 확인할 수 .

실행계획은 쿼리 옵티마이저가 사용자가 작성한 쿼리를 효율적으로 실행하기 위한 계획입니다.

실행계획은 인덱스, 조인 순서, 필터링 등 여러 가지 요소에 의해 영향을 받습니다 !

 

그렇다면 어떻게 실행계획을 확인할까요 ?

쿼리문 상단에 EXPLAIN 을 붙여 실행 계획을 확인하자 !

1. 쿼리를 작성한다.
2. 상단에 EXPLAIN을 작성하고 실행한다 !

굉장히 간단하죠? 실행 계획을 확인해 봅시다 !

 

문제의 쿼리문의 실행계획 (회사 정보가 포함될 수 있어 테이블 명은 모두 지웠으니 양해 부탁드립니다.)

 

이번에는 실행계획 중에 type을 위주로 볼 것입니다.

 

먼저 type이 뭘까요 ?

공식문서에 따르면 type 은 join 타입을 의미합니다.

하지만 join 타입이라고 생각하면 이해하기 어렵기 때문에 Table의 record를 읽는 방식이라고 생각하는 게 훨씬 이해가 잘 되실 겁니다 !

 

이 join type에는 여러 종류가 있습니다. 아래는 MySQL 공식 문서에서 나열한 type의 종류 입니다. 

그리고 노란색으로 색칠된 type은 위의 실행 계획에서 사용되는 type입니다.

( best type -> worst type 순으로 나열했습니다. 좋고 나쁨 또한 공식 문서에서 지정한 순서입니다 ! )

• system
• const
• eq_ref
• ref
• fulltext
• ref or null 
• index merge
• unique_subquery
• index_subquery
• range
• index
• ALL

MySQL 공식문서가 지정한 나쁜 type 1위인 ALL type이 사용되고 있네요.

심지어 공식문서에 usually very bad 라고 박아놨네요.

이때 ALL 타입을 제외한 나머지 모든 타입들은 Index를 사용해 Table을 스캔합니다.

 

그렇다면 다른 것보다 이 부분을 먼저 해결해 봅시다.

 

🐳문제 해결

실행계획의 row를 확인해 보면 289개의 데이터를 전부 (ALL) 스캔하고 있음을 알 수 있습니다.

이 문제를 해결해 봅시다.

 

먼저 이 테이블은 code 값의 정보를 저장하고 있는 테이블입니다.

( 가령 , CD_NO : TBT0001 , CD_NAME : 티스토리 블로그 타입 - 개발 )

 

이런 식으로 코드값을 지정하고 그 코드값에 이름을 부여하는 식으로 사용하고 있습니다.

 

도전 1. 이 테이블과 조인할 때 성능을 향상시키기 위해 외래키를 지정하려고 시도했습니다.

결과는 실패 ...

심지어 이 테이블은 PK 값도 없는 테이블이고 Unique Key 를 통해 관리하고 있어. 외래키 설정도 불가능 했습니다..

( Enum 타입을 사용했으면 좋았을 것 같습니다 ... 하지만 회사에서 사용하는 이유도 당연히 있겠죠 ! )

 

도전 2.  index 설정

CD_NO 값을 기준으로 index를 생성해 보았습니다.

다른 테이블에서 CD_NAME을 구하기 위해서 CD_NO 을 통해 조인하기 때문에 CD_NO 값에 index를 설정해 정렬해 주면 결과가 외래키는 아니지만 성능이 향상될 것이라고 생각했습니다.

 

그리고 조인하는 테이블도 CD_NO에 index를 설정해야 하지만 조인 되는 테이블 또한 ! CD_NO 값에 index를 설정해야 합니다 !

보통 다른 테이블의 PK 값을 조인하기 때문에 조인 되는 테이블의 index는 크게 고려하지 않았을 수 있습니다. 

( PK로 지정하면 자동으로 index가 생성 됩니다 ! )
하지만 PK가 아닌 값을 참조할 때는 참조하는 쪽도 참조 되는 쪽도 모두 index가 설정되어 있어야 합니다.

 

🐳결과

결과는 대성공 !!

다시 한 번 바뀐 실행계획을 살펴봅시다.

 

type을 보면 처음에 문제가 됐던 tc table의 type이 ALL -> ref 로 변화 했다 !
또한 rows 도 289 -> 1 로 tc table을 전부 스캔해 289개의 데이터를 탐색하는 것에서 1개로 줄어든 것을 볼 수 있었다.

rows 가 의미하는 것은 table에 index를 사용하는 조건에 일치하는 레코드 건수를 예측한 것이다.

(index가 설정되어 있지 않은 속성을 WHERE절에 선언했을 경우 rows는 아마 변화가 없을 것이다 ! )

 

시행 결과도 확인해 보자 !
기존에 index를 설정하기 전에는 5170ms 에서 index 설정 후 1065ms 로 줄었다.

테스트는 JMeter를 사용해 같은 API를 10번 호출해 보았습니다. ( 10번은 너무 적긴 하네 ... 다음엔 더 넉넉하게 테스트 해봐야지 )

 

🐳후기

너무 간단하게 index 설정으로 해결 되었다고 생각하실 수도 있지만 이 글의 중요 포인트는 그게 아닙니다 !
문제를 해결하기 위해서는 문제가 무엇인지 알아야 하는 것처럼

실행계획을 확인하고 해석해 어떤 부분이 문제점인지 알아내는 방법을 알게 된 것이 더 중요한 포인트가 아닐까요 ?