[MySQL] 쿼리성능 개선하며 알게된 것 기록 📝

 

사내 운영툴 메인 페이지의 로딩 속도가 너무 느린 이슈가 있었고 호출되는 API의 응답속도가 거의 2초 이상 소요되는 것을 발견했다. 현재 서비스를 사용하고 있는 브랜드와 매장 및 각 매장의 상태 값과 포스 신호 수신 여부 등에 대한 데이터를 한 번에 가져오는 API이기는 하나, 몇십만 건의 데이터도 아닌데 이 정도로 속도가 느린 건 문제 있다고 판단했다. 해당 API에서 사용하는 프로시저는 2개였고 각각 약 1.2초, 0.6초 이상 걸리는 슬로우 쿼리였다. (보통 1초 이상 걸리는 경우를 슬로우 쿼리라고 판단하나, 데이터 양이 많지 않은데 0.6초나 걸리면 이 역시 슬로우 쿼리다.) 가져오는 데이터 양을 줄이는 방법도 속도 개선을 하는 방법 중 하나이지만, 그전에 근본적인 원인부터 고치는 게 맞다고 생각하여 각 쿼리의 문제점을 파악, 수정했고 이 과정에서 알게 된 것과 기억할 것을 정리해보겠다.

API 응답까지 걸리는 시간: (개선 전) 약 1.8 ~ 2초  / (개선 후) 약 0.5 ~ 0.6초

 

 

실행계획 type이 index라면 한 번 더 살펴보자

EXPALIN으로 실행 계획을 확인해보니 type이 index인 부분을 발견했다. where절 조건문에서 사용하는 테이블의 컬럼에 인덱스가 걸려있지 않았기 때문에 Full index scan을 하고 있었다 (테이블에는 약 26만 건의 데이터가 있었다). 해당 컬럼에 인덱스를 걸고 다시 실행계획을 확인하니 type이 range로 변경되었고 실제로 프로시저 실행 속도가 1.5s → 415ms로 감소했다 (추가로 where절에 몇 가지 조건을 더해 필요 없는 데이터는 탐색하지 않도록 했다). 아래는 type의 종류이며 성능이 낮은 것에서부터 높은 순서대로 정렬했다.

• ALL: 테이블을 처음부터 끝까지 탐색하여 데이터를 찾는 방식. (Table Full scan)
• index: 인덱스를 처음부터 끝까지 탐색하여 데이터를 찾는 방식 (Index Full scan)
• ragne: 특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출. 데이터가 방대하지 않다면 단순 SELECT 에서는 나쁘지 않음.
• ref: 조인을 할 때 Primary Key 혹은 Unique Key가 아닌 Key로 매칭 하는 경우
• eq_ref: 조인을 할 때 Primary Key로 매칭
• const: SELECT에서 Primary Key 혹은 Unique Key를 살수로 조회하는 경우로 많아야 한 건의 데이터만 있음
• system: 테이블에 1개의 데이터만 있는 경우

 

CURRENT_DATE 보다 리터럴을 사용하여 날짜 비교를 하자

다른 프로시저의 경우, 테이블에 인덱스 적용이 잘 되어있었고 쿼리에서도 단순히 where 절로 날짜 타입을 조건으로 걸어 검색했으나 인덱스를 타지 않았다. CURRENT_DATE에 대한 참조 때문에 쿼리 캐시가 결과를 캐싱하지 못했던 것이었다. 리터럴로 변경하여 캐싱되도록 처리하니 642ms → 74ms로 속도가 개선되었다. =, >=, <=처럼 범위 비교할 때 부등호 왼쪽에 함수를 적용하면 인덱스를 타지 않는다는 것은 알고 있었으나, 오른쪽에 CURRENT_DATE 같은 함수를 써도 인덱스 타는데 영향을 미칠 수 있다는 것은 이번에 알게 되었다.

인덱스 컬럼에 IN이 인덱스를 타지 않는다면 range_optimizer_max_mem_size을 확인하자

range_optimizer_max_mem_size는 range optimizer로 이용할 수 있는 메모리를 조절하는 시스템 변수이다. 기본 값은 mysql ver 5.7.12 이상인 경우 8M, 5.7.11 이하인 경우 1.5M이며 아래 방법으로 해당 변수를 임의로 세팅할 수 있다.

# range_optimizer_max_mem_size 확인
SHOW VARIABLES LIKE '%mem%';

# 해당 세션에서만 적용 & 메모리 제한 없음 (no limit)
SET session range_optimizer_max_mem_size=0;

# 글로벌 적용 & 123456bytes 로 메모리 제한
SET range_optimizer_max_mem_size=123456;

지정한 메모리를 초과할 경우, 내부적으로 range 함수 대신 다른 방법으로 데이터를 탐색한다. 이때 full scan이 이뤄질 수도 있으며 이는 성능을 저하시키기 때문에, range_optimizer_max_mem_size를 조절하는 것도 성능 개선의 한 가지 방법일 수 있다. 그런데 range_optimizer_max_mem_size를 충분한 크기로 조절해도 (혹은 0으로 설정했거나) 인덱스를 타지 않을 수 있다. 이는 조건에 일치하는 레코드가 너무 많아 MySQL 옵티마이저가 range scan을 포기하고 full scan을 하는 것일 수 있다. (내 경우가 이랬음) 이런 경우 조건 범위를 줄이거나 다른 방법으로 성능 개선하도록 하자.

 

 

WHERE 절에 case 문 사용을 피하자

case문을 적용한 값은 인덱스가 걸려있어도 인덱스를 타지 않는다. union을 활용하는 방법도 있다고 하지만 나는 좀 더 쉬운 방법으로 or을 사용하였다.

# WHERE절의 CASE문
...
WHERE CASE WHEN _var IS NULL THEN tb_a.var = _var ELSE TRUE END
...;

# OR을 사용하여 true인 조건만 적용하도록 수정
...
WHERE ((_var IS NOT NULL AND tb_a.var = _var) OR (_var IS NULL AND TRUE))
...;

 

 

SELECT하는 대상 테이블이 가장 최선의 선택인지 확인하자

이 부분은 쿼리 튜닝에 대한 내용은 아니지만 항상 머릿속에 있어야할 내용이라 기록한다. 모집단이 너무 크거나, 쓸데없이 조인을 많이 해야한다면 지금 쿼리에서 사용중인 테이블 외에 더 적합한 테이블은 없는지 확인해보자. (이를 위해선 테이블 구조를 확실히 파악해야한다. )

 

 

 

---

참고자료

• https://hoing.io/archives/24493
• https://jobc.tistory.com/216
• https://hckcksrl.medium.com/index%EB%A5%BC-%ED%83%80%EC%A7%80%EC%95%8A%EB%8A%94-%EC%BF%BC%EB%A6%AC-41f0417bfe03
• https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html
• https://snowple.tistory.com/377
• https://daniel-hebn.github.io/2018/04/14/2018-04-14-MySQL-%EC%84%B1%EB%8A%A5%EC%B5%9C%EC%A0%81%ED%99%94-3/
• https://nomadlee.com/mysql-explain-sql/#type
• https://jaehoney.tistory.com/193
• https://dataonair.or.kr/db-tech-reference/d-guide/dbms-2/?mod=document&uid=62466