업무에 바로 쓰는 SQL 튜닝 2 - SQL 튜닝 실습 (1)

지난 포스팅에서 SQL 튜닝 이론에 대해 살펴보았는데요, 이번 포스팅에서는 실제 여러 테이블의 데이터를 기반으로 쿼리의 문제점을 파악하고, 튜닝하여 개선해 보겠습니다.

(실제 튜닝 전 SQL 문을 확인하여 무엇이 문제인지 파악해보고, 직접 개선해보고 튜닝 후 SQL 문을 확인하는 방식도 좋을 것 같습니다.)

 

악성 SQL 튜닝으로 초보자 탈출하기

실무적인 SQL 튜닝 절차 이해하기

1. SQL 문 실행 결과 & 현황 파악

• 결과 및 소요시간 확인
• 조인 / 서브쿼리 구조
• 동등 / 범위 조건

2. 가시적

• 테이블의 데이터 건수
• SELECT 절 컬럼 분석
• 조건절 컬럼 분석
• 그루핑 / 정렬 컬럼

3. 비가시적

• 실행계획
• 인덱스 현황
• 데이터 변경 추이
• 업무적 특징

4. 튜닝 방향 판단 & 개선 / 적용 (테스트)

 

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

위 데이터 세팅은 아래 레포지토리의 sql 파일을 통해 설정할 수 있습니다.

https://github.com/7ieon/SQLtune

 

테이블 데이터 건수

No	테이블 명	데이터 건수
1	급여	2,844,047
2	부서	9
3	부서관리자	24
4	부서사원_매핑	331,603
5	사원	300,024
6	사원출입기록	660,000
7	직급	443,308

 

 

테이블 인덱스 목록

테이블명	키 유형	키명	키_구성열
급여	PK	PRIMARY KEY	사원번호 + 시작일자
	INDEX	I_사용여부	사용여부
부서	PK	PRIMARY KEY	부서번호
	UNIQUE INDEX	UI_부서명	부서명
부서관리자	PK	PRIMARY KEY	사원번호 + 부서번호
	INDEX	I_부서번호	부서번호
부서사원_매핑	PK	PRIMARY KEY	사원번호 + 부서번호
	INDEX	I_부서번호	부서번호
사원	PK	PRIMARY KEY	사원번호
	INDEX	I_입사일자	입사일자
	INDEX	I_성별_성	성별 + 성
사원출입기록	PK	PRIMARY KEY	순번 + 사원번호
	INDEX	I_출입문	출입문
	INDEX	I_지역	지역
	INDEX	I_시간	입출입시간
직급	PK	PRIMARY KEY	사원번호 + 직급명 + 시작일자

 

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

튜닝 전 SQL, 실행 계획

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

• type = ALL 이므로 풀 스캔 방식으로 인덱스를 사용하지 않고 테이블에 바로 접근 합니다.
• 사원 테이블의 전체 데이터가 약 30만건 이므로, 거의 모든 데이터에 접근합니다.

튜닝 후 SQL, 실행 계획

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

• 기본 키 (사원번호)를 가공 없이 범위 검색을 활용하여 기본 키, 인덱스를 활용합니다.

 

2. 사용하지 않는 함수를 포함하는 나쁜 SQL 문

튜닝 전 SQL, 실행 계획

SELECT IFNULL(성별, 'NO DATA') AS 성별, COUNT(1) 건수
FROM 사원
GROUP BY IFNULL(성별, 'NO DATA');

• Extra 항목이 Using temporary 이므로 임시 테이블을 생성합니다.

튜닝 후 SQL, 실행 계획

SELECT 성별, COUNT(1) 건수
FROM 사원
GROUP BY 성별;

• Extra 항목에 임시 테이블(Using temporary) 없이 인덱스만 사용하여 데이터를 추출합니다.
• 단, 성별 컬럼에는 NOT NULL이고, NULL 데이터가 없다는 가정 하에 튜닝한 쿼리입니다.

 

3. 형변환으로 인덱스를 활용하지 못하는 나쁜 SQL 문

튜닝 전 SQL, 실행 계획

SELECT COUNT(1) FROM 급여 WHERE 사용여부 = 1;

• filtered 항목이 10이므로, MySQL 엔진으로 가져온 데이터 중 10%를 추출해서 최종 데이터를 출력함을 알 수 있습니다.
• rows에 해당하는 2,838,398건의 데이터 중 10%에 해당하는 약 3만 건의 데이터가 최종 출력되리라고 예측할 수 있습니다.
• 대략 0.3초

튜닝 후 SQL, 실행 계획

SELECT COUNT(1) FROM 급여 WHERE 사용여부 = '1';

• 사용여부 열은 문자형 char(1) 데이터 유형으로 구성되어서, 이전 쿼리에서는 숫자 유형으로 써서 데이터에 접근했으므로 DBMS 내부의 묵시적 형변환이 발생했고, 그 결과 인덱스를 제대로 활용하지 못하고 전체 데이터를 스캔하였습니다.
• 대략 0.02초

오라클의 경우, 특정 버전 이상(?) 에서는 위와 같은 문제가 발생하지 않게 자동으로 형변환을 처리해주는 기능이 있어서 위와 같은 문제가 발생하지 않았던 것으로 기억을 하는데, 확실하지는 않습니다.

 

4. 다수 쿼리를 UNION 연산자로만 합치는 나쁜 SQL 문

튜닝 전 SQL, 실행 계획

SELECT 'M' AS 성별, 사원번호
FROM 사원
WHERE 성별 = 'M'
  AND 성 = 'Baba'

UNION

SELECT 'F' AS 성별, 사원번호
FROM 사원
WHERE 성별 = 'F'
  AND 성 = 'Baba';

• id가 1, 2인 행은 사원 테이블에 대해 I_성별_성 인덱스를 활용합니다.
• id가 3인 행은 id가 1, 2인 행의 결과를 통합하여 중복을 제거하는 작업을 합니다. 여기서 임시 테이블(Using temporary)을 생성하고 그 내부에서 각 결과의 UNION 연산 작업을 수행하리라 예측할 수 있습니다.

 

튜닝 후 SQL, 실행 계획

SELECT 'M' AS 성별, 사원번호
FROM 사원
WHERE 성별 = 'M'
  AND 성 = 'Baba'

UNION ALL

SELECT 'F' AS 성별, 사원번호
FROM 사원
WHERE 성별 = 'F'
  AND 성 = 'Baba';

• 단순히 UNION -> UNION ALL 으로 변경합니다.
• 첫 번째 SELECT와 두 번째 SELECT의 결과 데이터가 중복되지 않으므로, 중복 제거하는 작업이 필요하지 않습니다.
• 튜닝 전 실행 계획과는 달리 세 번째 추가 행(Using temporary)은 필요하지 않습니다.

 

5. 인덱스 고려 없이 열을 사용하는 나쁜 SQL 문

튜닝 전 SQL, 실행 계획

SELECT 성, 성별, COUNT(*) AS 카운트
FROM 사원
GROUP BY 성, 성별;

• I_성별_성 인덱스를 활용하고, 임시 테이블 (Using temporary)을 생성하여 성과 성별을 그루핑해 카운트 연산을 수행합니다.

I_성별_성 인덱스는 성별과 성 순으로 생성된 인덱스로, 성별 기준으로 정렬된 뒤 성 기준으로 정렬되었습니다.

 

튜닝 후 SQL, 실행 계획

SELECT 성, 성별, COUNT(*) AS 카운트
FROM 사원
GROUP BY 성별, 성;

• 튜닝 후 실행 계획에서는 별도로 임시 테이블을 생성하지 않고도 작업을 수행합니다. (인덱스 활용, 성별 -> 성)
• 튜닝 전 소요 시간(0.3초) -> 튜닝 후 소요 시간(0.03초)

 

6. 동등 조건으로 인덱스를 사용하는 나쁜 SQL 문

튜닝 전 SQL, 실행 계획

SELECT * FROM 사원출입기록 WHERE 출입문 = 'B';

• 출입문 B에 대한 명확한 상수 조건으로 ref 항목이 const로 출력됩니다.
• 하지만 사원출입기록 테이블의 전체 데이터(660,000) 중 출입문이 B에 해당하는 데이터는 약 50% (300,000)에 해당하므로, 이를 위해 인덱스를 활용하는 게 과연 효율적일지 고민해봐야 합니다. (인덱스 손익분기점)

친절한 SQL 튜닝

 

튜닝 후 SQL, 실행 계획

SELECT * FROM 사원출입기록 IGNORE INDEX(I_출입문) WHERE 출입문 = 'B';

• 튜닝 후 실행 계획은 테이블 풀 스캔(type: ALL)으로 수행됨을 확인할 수 있습니다.
• 즉, 인덱스를 사용하지 않은 채 약 66만건의 전체 데이터를 가져와 조건절에 필요한 데이터를 추출하는 방식으로, 랜덤 액세스가 발생하지 않고 한 번에 다수의 페이지에 접근하는 테이블 풀 스캔 방식으로 수행됩니다.
• 소요 시간은 3.5초 -> 1.2초로 감소

 

7. 작은 테이블이 먼저 조인에 참여하는(드라이빙 테이블) 나쁜 SQL 문

부서 테이블: 9건, 부서사원_매핑 테이블: 33만건

 

튜닝 전 SQL, 실행 계획

SELECT 매핑.사원번호, 부서.부서번호
FROM 부서사원_매핑 매핑
JOIN 부서 ON 매핑.부서번호 = 부서.부서번호
WHERE 매핑.시작일자 >= '2002-03-01';

• 드라이빙 테이블(부서), 드리븐 테이블(부서사원_매핑) 중첩 루프 조인을 수행합니다.
• 부서사원_매핑 테이블의 rows 항목의 40355 수치는, SQL 문을 수행하고자 조사한 행의 예측 건수로, 인덱스 스캔을 하고 랜덤 액세스로 테이블에 접근하게 됩니다. 또한 MySQL 엔진으로 가져온 모든 데이터에 대해 WHERE 절의 필터 조건 (시작일자 >= ...) 을 수행합니다.

튜닝 후 SQL, 실행 계획

SELECT STRAIGHT_JOIN 매핑.사원번호, 부서.부서번호
FROM 부서사원_매핑 매핑
JOIN 부서 ON 매핑.부서번호 = 부서.부서번호
WHERE 매핑.시작일자 >= '2002-03-01';

• 상대적으로 규모가 큰 부서사원_매핑 테이블의 매핑.시작일자 >= '2002-03-01' 조건절을 먼저 적용한다면, 조인할 때 비교 대상이 줄어들 것입니다. (해당 데이터는 약 1300건)
• 부서사원_매핑 테이블을 먼저 접근(드라이빙)하여 테이블 풀 스캔으로 처리하고, 부서 테이블에는 PK로 접근하여 1개의 데이터만 접근하는 식으로 수행됩니다.
• 고려해야 할 점은, 시작일자 열이 범위 조건으로 작성되는지, 그 범위에 해당하는 데이터가 위에서 설명했던 인덱스 손익분기점을 넘기지 않는 정도의 비율이라면 인덱스를 생성하는 것이 더 효율적일 수 있습니다.

 

8. 메인 테이블에 계속 의존하는 나쁜 SQL 문

사원 테이블: 30만건, 급여 테이블: 280만건

 

튜닝 전 SQL, 실행 계획

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

• 1: 사원 테이블의 PK를 활용하여 범위 스캔을 수행합니다.
• 2: 급여 테이블에 접근하는데, 외부의 사원 테이블로부터 조건절을 전달받아 수행해야 하는 의존성을 가진 서브쿼리입니다. 그리고 급여 테이블은 PK를 활용합니다.

💡 보통 실행 계획의 select_type 항목에 DEPENDENT라는 키워드가 있으면, 외부 테이블에서 조건절을 받은 뒤 처리되어야 하므로 튜닝 대상으로 고려할 수 있습니다.

💡 서브쿼리 vs 조인
필자 경험상 서브쿼리보다는 조인으로 수행하는 편이 성능 측면에서 유리할 가능성이 높습니다.

 

튜닝 후 SQL, 실행 계획

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

• WHERE 절의 서브쿼리를 조인으로 변경하여 GROUP BY, HAVING 절을 통해 튜닝 전 그룹별 최댓값을 계산하도록 개선합니다.
• 급여 테이블의 DEPENDENT SUBQUERY 방식은 제거되고, 단순 조인하는 방식으로 효율 향상
• rows는 예측값이므로 단순히 rows 데이터로 튜닝 성공 여부를 판단하기는 어렵습니다.

 

여러 상황에 대해 SQL 튜닝을 진행해 보았는데요, 실제로 일을 하면서 비슷한 사례를 겪었던 케이스도 있었고, 이론적으로만 알고 있었던 내용이나, 전혀 몰랐던 내용에 대해서도 학습할 수 있었습니다.

 

다음은 조금 더 실무에 가까운(?) 복잡한 쿼리를 토대로 인덱스 변경, 테이블 구조 변경 및 SQL 재작성 등 쿼리 튜닝을 수행할 예정입니다.