정보처리산업기사 - 데이터베이스 프로그래밍 - 쿼리 성능 최적화

※ 공부 내용의 복습 개념으로 정리된 글입니다. - 출처 시나공 

 

쿼리 성능 최적화의 개요

쿼리 성능 최적화는 데이터 입 · 출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화하는 것입니다.

• 쿼리 성능을 최적화하기 전에 성능 측정 도구인 APM을 사용하여 최적화 할 쿼리를 선정해야 합니다.
• 최적화 할 쿼리에 대해 옵티마이저가 수립한 실행 계획을 검토하고 SQL 코드와 인덱스를 재구성합니다.

 

옵티마이저(Optimizer)

• 옵티마이저는 작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아 주는 모듈입니다.
• 옵티마이저의 종류에는 RBO(Rule Based Optimizer)와 CBO(Cost Based Optimizer)가 있으며, 실무에서 주로 CBO가 사용됩니다.

RBO와 CBO의 차이점

 

APM(Application Performance Management/Monitoring)

APM은 애플리케이션의 성능 관리를 위해 접속자, 자원 현황, 트랜잭션 수행 내역, 장애 진단 등 다양한 모니터링 기능을 제공하는 도구를 의미합니다.

• APM은 리소스 방식과 엔드투엔드(End-to-End)의 두 가지 유형이 있습니다.

 

※ CBO

CBO는 입 · 출력 속도, CPU 사용량, 쿼리의 블록 개수, 쿼리에 사용되는 개체의 속성, 튜플의 개수 등을 종합하여 각 DBMS마다 고유의 알고리즘에 따라 산출되는 '비용'을 계산합니다. 그러므로 개체나 DBMS의 버전이 변경되어 알고리즘에 변화가 생기면 실행 계획을 다시 확인해야 합니다.

 

※ 스카우터(Scouter)

스카우터는 애플리케이션 및 OS자원에 대한 모니터링 기능을 제공하는 오픈소스 APM 소프트웨어 입니다.

 

※ 제니퍼(Jennifer)

제니퍼는 애플리케이션의 개발부터 테스트, 오픈, 운영, 안정화까지, 전 단계에 걸쳐 성능을 모니터링하고 분석해주는 APM 소프트웨어 입니다.

 

 

실행 계획(Execution Plan)

실행 계획은 DBMS의 옵티마이저가 수립한 SQL 코드의 실행 절차와 방법을 의미합니다.

• 실행 계획은 EXPLAIN 명령어를 통해 확인할 수 있으며, 그래픽이나 텍스트로 표현됩니다.
• 실행 계획에는 요구사항들을 처리하기 위한 연산 순서가 적혀있으며, 연산에는 조인, 테이블 검색, 필터, 정렬 등이 있습니다.

예제1

다음은 MySQL에서 4개의 테이블을 조인하는 <SQL>의 <실행 계획>을 그래픽으로 나타낸 것입니다.

 

※ Full Table Scan

Full Table Scan은 전체 테이블을 대상으로 첫 번째 튜플부터 차례대로 데이터를 조회하는 방식을 의미합니다.

 

※ NL, BNL

A 테이블과 B 테이블을 조인했을 때, A 테이블의 튜플만큼 B 테이블의 튜플을 반복하여 조회해 나가는 방식을 NL(Nested Loop)이라고 하고, 두 테이블 모두 인덱스가 없는 경우 조회 성능 개선을 위해 한 테이블의 일정 양의 튜플을 버퍼 메모리에 저장한 후 NL방식으로 조회하는 것을 BNL(Block Nested Loop)이라고 합니다.

 

※ filesort

filesort는 분류(sort) 작업을 수행하려는 테이블에 인덱스가 없는 경우 메모리 공간 절약을 위해 디스크에 파일 형식으로 테이블을 저장한 후 분류하는 방식을 의미합니다.

 

728x90

 

쿼리 성능 최적화

쿼리 성능 최적화는 실행 계획에 표시된 연산 순서, 조인 방식, 테이블 조회 방법 등을 참고하여 SQL문이 더 빠르고 효율적으로 작동하도록 SQL 코드와 인덱스를 재구성하는 것을 의미합니다.

 

SQL 코드 재구성

• WHERE 절에 연산자가 포함되면 INDEX를 활용하지 못하므로 가능한 한 연산자 사용을 자제합니다.
• 서브 쿼리에 특정 데이터가 존재하는지 확인할 때는 IN보다 EXISTS를 활용합니다.
• 옵티마이저의 실행 계획이 잘못되었다고 판단하는 경우 힌트를 활용하여 실행 계획의 액세스 경로 및 조인 순서를 변경합니다.

인덱스 재구성

• SQL 코드에서 조회되는 속성과 조건들을 고려하여 인덱스를 구성합니다.
• 실행 계획을 참고하여 인덱스를 추가하거나 기존 인덱스의 열 순서를 변경합니다.
• 인덱스의 추가 및 변경은 해당 테이블을 참조하는 다른 SQL문에도 영향을 줄 수 있으므로 신중히 결정합니다.
• 단일 인덱스로 쓰기나 수정 없이 읽기로만 사용되는 테이블의 경우 IOT(Index-Organized Table)로 구성하는 것을 고려합니다.
• 불필요한 인덱스를 제거합니다.

예제2

다음은 에제1의 <SQL>의 각 테이블에서 조인에 활용되는 속성들을 인덱스로 설정하고, 힌트를 통해 조회 순서를 변경하는 최적화를 수행한 후의 <실행 계획>입니다.

 

※ EXISTS

EXISTS는 서브 쿼리의 모든 데이터를 확인하고 IN과 달리 데이터의 존재여부가 확인되면 검색이 종료되므로 IN보다 처리 속도가 빠릅니다.

 

※ 힌트(Hint)

힌트는 SQL문에 추가되어 테이블 접근 순서를 변경하거나, 인덱스 사용을 강제하는 동안 실행 계획에 영향을 줄 수 있는 문장을 말합니다.

 

※ IOT(Index-Oranized Table)

일반적으로 인덱스가 있는 테이블을 조회할 때, 인덱스를 검색하여 주소를 얻으며 주소를 다시 찾아가는 과정을 거칩니다.

반면 IOT는 인덱스 안에 테이블 데이터를 직접 삽입하여 저장함으로써 주소를 얻는 과정이 생략되어 더욱 빠른 조회가 가능합니다.