SQL 성능튜닝 가즈아

 

11번가 코딩테스트에서 매우 어려운 SQL문제가 나왔습니다. 프로그래머스에서 SQL 고득점 kit를 푼 정도로는 해결할 수 없는 레벨이었습니다. 마감 1분을 남겨두고 제출하긴 했지만, 여전히 제 실력이 부족하다는 것을 느끼게 되는 계기였습니다.

 

백엔드 개발자는 SQL을 자유자재로 구사해야 합니다. 업무와 밀접하기도 하고, 어떻게 작성하고 튜닝하느냐에 따라 성능이 수백배 차이나기도 합니다.

WAS 장애 포인트의 90퍼센트 이상이 DB 성능문제라고 하니, 실행계획을 보고 SQL과 인덱스를 개선하는 능력은 필수입니다.

 

실무에서 장애를 몸으로 겪으며 성장하는 것이 가장 빠르겠지만, 그럴 수 없으니.... 책으로 미리 경험하고 실무에 투입하면 좋을 것이라고 생각해서 책을 읽기 시작했습니다. RealMySQL을 읽으면서 다 안다고 생각했지만, 모르는 것이 너무 많았습니다. 리마인드 하고자 미래의 나에게도 도움이 되기 위해 글을 써봅니다 - MySQL을 기준으로 쓰여진 글입니다.

 

 

 

---

 

실행 계획이란 ? 

말 그대로 SQL 문으로 요청한 데이터를 어떻게 불러올 것인지에 관한 계획, 즉 경로를 의미합니다. 지름길을 사용해 데이터를 빠르게 찾아낼 것인지, 지름길이 있어도 멀리 돌아가서 찾을 것인지 실행 전에 미리 확인할 수 있습니다. EXPLAIN, DESCRIBE, DESC 등의 명령어로 수행합니다.

EXPLAIN
SELECT * FROM TEMP_TABLE;

 

명령어를 실행하면 나오는 테이블

테이블의 각 컬럼의 값을 주의깊게 살펴보고 적절한 조치를 취하는 것이 개발자의 역할입니다.

각 컬럼이 무엇을 의미하는지는 검색해보시면 쉽게 찾아보실 수 있습니다.

 

 

---

SQL문 단순 수정으로 착한 쿼리 만들기

 

1. 기본 키를 변형하는 SQL문

 

사원번호가 1100으로 시작하면서 사원번호가 5자리인 정보를 모두 출력하는 예제입니다

SELECT *
FROM 사원
WHERE SUBSTRING(사원번호,1,4)=1100
AND LENGTH(사원번호)=5

실행계획은 이렇습니다.

simple 타입으로 조회했고, 풀 테이블 스캔, 인덱스는 활용하지 않았고 rows가 299157 이라는 아주 높은 숫자를 보입니다.

테이블의 전체 데이터가 약 30만건이니 모든 데이터를 다 살펴봤다고 가정할 수 있습니다.

왜 인덱스를 타지 않고 풀 테이블 스캔을 했을까요?

SUBSTRING() 함수에 비밀이 있습니다. 인덱스를 가공했기 때문에 엔진 입장에서도 어찌할 방도가 없이 풀 테이블스캔으로 돌릴 수 밖에 없었습니다.

 

튜닝 후:

SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 11000 AND 11009

이번엔 primary key를 이용한 range 쿼리가 되었습니다. rows가 10인걸 보니 딱 필요한 데이터에만 접근했다고 생각할 수 있습니다.

인덱스로 사용된 컬럼은 변형이 가해지면 사용할 수 없음을 기억합시다

 

 

 

 

인덱스 고려 없이 열을 사용하는 SQL문

다음은 성과 성별 순서로 그룹핑하여 몇 건의 데이터가 있는지 묻는 쿼리입니다.

예를 들어 성이 김씨인 여자는 몇 명이고 이씨인 남자는 몇 명인지 확인할 수 있습니다.

 

SELECT 성, 성별, COUNT(1) as 카운트
FROM 사원
GROUP BY 성, 성별

커버링 인덱스로 보여집니다. extra의 'Using temporary' 항목을 보니 정렬을 위해 임시 테이블을 활용한 것으로 보입니다.

 

사원 테이블에는 (성별,성) 순서로 생성된 인덱스가 있습니다. 즉, 해당 인덱스는 '성별' 기준으로 정렬된 뒤 그 다음에 '성' 순서로 정렬되었다는 뜻입니다. 이 인덱스를 활용해야 합니다. 인덱스에 기재된 열 순서대로 group by 에 조건을 작성합시다.

SELECT 성, 성별, COUNT(1) as 카운트
FROM 사원
GROUP BY 성별, 성

using temporary가 사라졌습니다. group by 에 있는 조건만으로도 정렬을 완성할 수 있기 때문에 임시 테이블을 활용할 필요가 없습니다.

 

 

 

메인 테이블에 계속 의존하는 SQL문

다음 쿼리는 사원번호가 450,000보다 크고 최대 연봉이 100,000 보다 큰 데이터를 찾아 출력합니다.

이때 메인쿼리인 사원테이블에서는 WHERE 조건절에서 사원번호 추출 대상을 정의하고, 중첩 서브쿼리의 급여 테이블에서는 메인 테이블의 사원번호를 매번 받아와 해당 사원의 최대 연봉 데이터를 확인합니다. 직감적으로 비효율적이다는 생각이 듭니다.

SELECT 사원.사원번호, 사원.이름, 사원.성
FROM 사원
WHERE 사원번호 > 450000
AND ( SELECT MAX(연봉) FROM 급여 WHERE 사원번호 = 사원.사원번호) > 100000

select_type에 'DEPENDENT SUBQUERY'가 나타났다면 우선적인 튜닝의 대상이 됩니다.

450000번 사원번호부터 104330 개의 레코드가 id 2번의 MAX() 함수를 타게 됩니다. 매우 비효율적이라고 볼 수 있겠습니다.

 

튜닝 수행 - WHERE절의 서브쿼리를 조인으로 변경합니다. GROUP BY와 HAVING을 이용해서 기존의 최댓값을 구하는 로직을 그대로 구현합니다.

SELECT 사원.사원번호, 사원.이름, 사원.성
FROM 사원, 급여
WHERE 사원.사원번호 > 450000
AND 사원.사원번호 = 급여.사원번호
GROUP BY 사원.사원번호
HAVING MAX(급여.연봉) > 100000

id가 동일하므로 조인이 수행되었다는 걸 알 수 있고, 그에 따라 select_type도 'SIMPLE' 로 변경되었습니다. 더 이상 급여 테이블이 사원의 각 사원번호에 의존하지 않아 성능이 개선되었습니다.

 

 

성능 튜닝은 이런 방향으로 흘러갑니다... 더 많은 내용은 책에서....!

 

 

 

 

 

이 글은 '업무에 바로 쓰는 SQL 튜닝 - 한빛미디어' 도서를 보고 학습한 내용을 정리한 글입니다.