[DB] SQL 튜닝

안녕하세요! 오늘은 SQL문을 작성할 때 최대 성능을 발휘할 수 있도록 쿼리를 짜는 방법에 대해 이야기 하려고 합니다.

SQL 튜닝에 정해진 답은 없지만 SQL을 이해하고 테이블, 인덱스, 컬럼 등의 정보, 업무적 성격 등 SQL이 가진 정보를 분석하는 것이 도움이 됩니다. SQL의 의미 및 작성 의도를 정확히 파악하여 성능 문제를 유발하지 않도록 개선하는 것이 중요합니다.

더 빠른 SQL 쿼리를 위한 규칙들에 대해 알아보겠습니다.

---

💡 옵티마이저

쿼리 최적화 도구에는 SQL 실행 계획을 수립하여 SQL을 실행하는 옵티마이저가 존재합니다. 동일한 결과가 나오는 SQL도 어떻게 실행하느냐에 따라서 성능이 달라지기 때문에 이름 그대로 최적화에 도움이 됩니다. 최근에 많이 사용하고 있는 옵티마이저(CBO)는 데이터 딕셔너리에 있는 정보를 사용해서 예상 비용을 산정하고, 최저 비용을 가지고 있는 계획을 선택해서 SQL을 실행합니다.

	규칙 기반 옵티마이저 RBO	비용 기반 옵티마이저 CBO
개념	사전에 정의된 규칙 기반	최소 비용 계산 실행계획 수립
기준	실행 우선 순위 (Ranking)	액세스 비용 (Cost)
인덱스	인덱스 존재 시 가장 우선시 사용	Cost에 의한 결정
성능	사용자 SQL 작성 숙련도	옵티마이저 예측 성능
장점	판단이 매우 규칙적이고 실행 예상 가능	통계 정보를 통한 현실 요소 적용
단점	예측 통계 정보 요소 무시	최소 선능 보장 계획의 예측 제어가 어려움
사례	AND 중심 양쪽 '=' 시 Index Merge 사용	AND 중심 양쪽 '=' 시 분포도별 Index 선택

 

규칙 기반 옵티마이저 RBO

오라클8 이하의 버전에서 기본으로 설정된 옵티마이저로, 실행 속도가 빠른 순으로 규칙을 먼저 세워두고 우선순위가 앞서는 방법을 채택합니다. 규칙의 우선순위는 아래와 같습니다.

순위	설명
1	ROWID를 사용한 단일 행인 경우
2	클러스터 조인에 의한 단일 행인 경우
3	유일하거나 기본키(Primary Key)를 가진 해시 클러스터 키에 의한 단일 행인 경우
4	유일하거나 기본키(Primary Key)에 의한 단일 행인 경우
5	클러스터 조인인 경우
6	해시 클러스터 조인인 경우
7	인덱스 클러스터 키인 경우
8	복합 칼럼 인덱스인 경우
9	단일 칼럼 인덱스인 경우
10	인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우
11	인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우
12	정렬-병합(Sort-Merge) 조인인 경우
13	인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우
14	인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우
15	전체 테이블을 스캔(FULL TABLE SCAN)하는 경우

* 클러스터 조인: 디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장시키는 방법

이 우선순위는 쿼리문을 효율적으로 작성하는 데 참고할 수도 있습니다. 순위가 미리 매겨져 있기 때문에 실행계획을 미리 예측할 수 있다는 장점이 있습니다.

그러나 정해진 우선순위만 고려하게 되면 상황에 맞지 않는 비효율적인 실행 계획이 도출될 수 있으며, HINT, HASH JOIN과 같이 규칙 기반 옵티마이저 이후에 나온 개념을 사용할 수 없습니다.

 

비용 기반 옵티마이저 CBO

최근에 많이 사용하고 있는 옵티마이저 방식이며 오라클 10 이후 버전부터는 공식적으로 비용 기반 옵티마이저를 사용합니다. 비용 기반 옵티마이저는 실행 계획을 세운 뒤 비용이 최소한으로 나온 실행 계획을 수행합니다. 비용을 예측하기 위해서 사용하지 않는 테이블, 인덱스, 컬럼 등 다양한 객체 정보를 이용합니다. 통계 정보가 없는 경우 비효율적인 실행계획을 생성할 수 있으므로 정확한 통계 정보를 유지하는 것이 좋습니다.

모드	설명
CHOOSE	현재는 잘 사용하지 않지만 SQL이 실행되는 환경에서 통계 정보를 가져올 수 있으면 비용 기반 옵티마이저로 그렇지 않다면 규칙 기반 옵티마이저로 작동시키는 모드입니다.
FIRST_ROWS	옵티마이저가 처리 결과 중 첫 건을 출력하는 데 걸리는 시간을 최소화할 수 있는 실행 계획을 세우는 모드입니다.
FIRST_ROWS_n	SQL의 실행 결과를 출력하는 데 까지 걸리는 응답 속도를 최적화하는 모드입니다.
ALL_ROWS	SQL 실행 결과 전체를 빠르게 처리하는 데 최적화된 실행 계획을 세우는 모드입니다. 마지막으로 출력될 행까지 최소한의 자원을 사용하여 최대한 빨리 가져오게 하며 오라클 10 이후로는 이 모드가 기본값으로 설정되어 있습니다.

 

하지만 옵티마이저의 비용 계산 결과가 정확하지 않을 수 있습니다. 따라서 옵티마이저의 실행계획을 보고 직접 쿼리를 수정해야 합니다. Nested Loops, Index Scan, Full Scan, Temporary 등의 키워드를 피하도록 쿼리를 작성하는 것이 좋습니다.

 

---

💡 실행 계획 (Execution Plan)

실행 계획이란 사용자가 작성한 SQL이 요구한 데이터를 추출하기 위해 옵티마이저가 작업의 방법과 순서를 결정한 계획을 의미합니다.

EXPLAIN 키워드를 실행할 쿼리문 가장 앞에 붙여 실행하면 옵티마이저가 선택한 최적의 실행 계획을 보여줍니다. 또는MySQL Workbench에서 번개와 돋보기가 함께 있는 버튼을 클릭하면 Visual Explain/Tabluar Expain 두 가지 형태로 볼 수 있습니다.

EXPLAIN 쿼리

 

실행 계획 보는 법

https://dev.mysql.com/doc/workbench/en/wb-performance-explain.html

• 해석할 때는 왼쪽 아래에서 오른쪽 위로 읽습니다.
• 다이아몬드 모양(nested loop)은 join을 나타냅니다.
• 박스 모양은 테이블, GROUP 또는 SORT, 혹은 서브쿼리를 의미합니다.
• 각 box의 왼쪽 위에는 'cost'가 표시됩니다. 실행 시 드는 비용으로 높을수록 안좋고 느리다는 것을 의미합니다.
• 각 box의 오른쪽 위에는 '필터링 후 사용할 행의 수'를 표시합니다. select문의 조건에 의해 필터링 되고 select될 것으로 예상되는 행의 수를 나타냅니다.
• 각 box의 하단에는 '테이블명' 혹은 'alias'가 표시됩니다.
• 각 box 하단에 index key가 표시됩니다.
• 각 box에는 색상이 표시되어 있습니다. 진한 빨간색에 가까울수록 cost가 높고 성능이 안좋으며, 파란색에 가까울수록 cost가 낮고 성능이 좋다는 것을 의미합니다.

Visual Explain 다이어그램 정보

시스템 이름	색	다이어그램 텍스트	비용
SYSTEM	Blue	Single row: system constant	Very low cost
CONST	Blue	Single row: constant	Very low cost
EQ_REF	Green	Unique Key Lookup	Low cost
REF	Green	Non-Unique Key Lookup	Low-medium -- 일치하는 행 수가 적으면 비용이 낮고, 행 수가 증가할수록 높아집니다.
FULLTEXT	Yellow	Fulltext Index Search	Low -- 전문화된 FULLTEXT 검색
REF_OR_NULL	Green	Key Lookup + Fetch NULL Values	Low-medium
INDEX_MERGE	Green	Index Merge	Medium -- 성능 향상을 위해 쿼리에서 더 나은 인덱스 선택을 찾습니다.
UNIQUE_SUBQUERY	Orange	Unique Key Lookup into table of subquery	Low -- 효율적인 하위 쿼리 처리에 사용
INDEX_SUBQUERY	Orange	Non-Unique Key Lookup into table of subquery	Low
RANGE	Orange	Index Range Scan	Medium -- 부분 인덱스 스캔
INDEX	Red	Full Index Scan	High
ALL	Red	Full Table Scan	Very High -- 데이터의 수가 많을 때 모든 행을 검색하기 때문에 비용이 많이 듭니다.
UNKNOWN	Black	unknown	일치 항목을 확인할 수 없는 경우를 위한 기본값

 

---

이어서 실행 계획을 토대로 최적화된 쿼리를 짜는 방법들에 대해 알아보겠습니다.

✅ 필요한 컬럼만 조회
*을 사용하여 모든 컬럼을 조회할 경우 불필요한 리소스를 소모합니다. 따라서 필요한 컬럼만 조회하는 것이 좋습니다.

SELECT * FROM EMP; # x
SELECT EMPCODE FROM EMP # o

✅ 서브 쿼리에서 ORDER BY 지양
많은 비용이 발생할 수 있으므로 서브 쿼리에서는 ORDER BY를 지양하는 것이 좋습니다.

✅ IN 대신 EXISTS 사용
EXISTS는 일치하는 항목이 발견되는 즉시 검색 프로세스를 종료하지만 IN은 모든 항목을 비교하므로 비용이 많이 듭니다.

✅ 중복 값을 제거하는 함수 사용 시 유의점
DISTINCT - 가급적 사용하지 않는 것이 좋습니다.
사용한다면 UNION와 DISTINCT를 같이 사용하지 않아야 합니다. UNION에는 중복 값을 제거하는 기능이 이미 존재합니다.
GROUP BY와 DISTINCT도 같이 사용하지 않아야 합니다. GROUP BY절에 입력된 컬럼을 그룹화하므로 DISTINCT를 사용할 필요가 없습니다.

✅ 암시적 변환 금지
암시적 변환은 데이터베이스에서 값을 비교할 때 데이터 타입이 다른 경우 데이터베이스에서 자동으로 타입 변환 후 값을 비교하는 방식입니다. 암시적 변환을 수행하는 데 불필요한 리소스가 소모되므로 동일한 타입으로 값을 비교하는 것이 좋습니다.

📋 DEPTNO가 1인 값을 찾으려고 합니다. (단, 타입은 문자)

SELECT EMPCODE, DEPTNO FROM EMP
WHERE DEPTNO = 1 # x
WHERE DEPTNO = '1' # o

✅ WHERE절에 연산 피하기
인덱스가 있는 컬럼을 변형하게 되면 인덱스를 사용하지 못하게 됩니다.

WHERE SUBSTR(EMPNAME, 1, 5) = 'david' # x
WHERE EMPNAME LIKE 'david%' # o

WHERE EMPNAME || DEPTNAME = 'DAVIDSERVICE' # x
WHERE EMPNAME = 'DAVID' AND DEPTNAME = 'SERVICE' # o

WHERE SAL + 1000 < 2000 # x
WHERE SAL < 1000 # o

✅ 조인 시 유의사항
3개 이상의 테이블을 조인할 때는 크기가 가장 작은 테이블을 먼저 배치합니다. 혹은 실행 계획을 토대로 가장 효율적인 순서를 찾아 선택합니다.
조인되는 데이터가 작다면 스칼라 서브쿼리(SELECT절에 사용하는 서브쿼리 - 함수와 같이 레코드 당 하나의 값을 리턴)를 사용하는 것도 효과적입니다.

 

 

---

[참조]

 

https://coding-factory.tistory.com/743

[DB] 데이터베이스 옵티마이저(Optimizer)에 대하여

https://dncjf0223.tistory.com/69

SQL 튜닝 - 실행계획, 옵티마이저, 튜닝 절차

https://velog.io/@blakekim93/%EC%BF%BC%EB%A6%AC-%EC%B5%9C%EC%A0%81%ED%99%94-eu4ti0e0

쿼리 최적화

https://blog.naver.com/PostView.naver?blogId=writer0713&logNo=222297230510&parentCategoryNo=&categoryNo=120&viewDate=&isShowPopularPosts=true&from=search 

[mysql] 실행계획 (query plan) 제대로 보기

https://dev.mysql.com/doc/workbench/en/wb-performance-explain.html

MySQL :: MySQL Workbench Manual :: 7.4 Visual Explain Plan

https://developer-talk.tistory.com/420

[SQL]간단하면서 쉬운 쿼리 최적화 방법