[3과목 데이터베이스 구축] SQL 활용 - 120. 쿼리 성능 최적화

• 데이터 입 · 출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화하는 것
• 쿼리 성능 최적화 전에 성능 측정 도구인 APM을 사용하여 최적화 할 쿼리를 선정해야 함

💡 APM (Application Perfomance Management/Monitoring)
- 애플리케이션의 성능관리를 위해 접속자, 자원 혀황, 트랜잭션 수행 내역, 장애 진단 등 다양한 모니터링기능을 제공하는 도구
- 리소스 방식 : Nagios, Zabbix, Cacti
- 에느투엔드 방식 : VisualVM, 제니퍼, 스카우터

• 최적화 할 쿼리에 대해 옵티마이저가 수립한 실행 계획을 검토하고 SQL코드와 인덱스를 재구성

💡 옵티마이저
- 작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아주는 모듈
- 실무에서는 주로 CBO 사용
→ 입 · 출력 속도, CPU 사용량, 쿼리의 블록 개수, 쿼리에 사용되는 개체의 속성, 튜플의 개수 등 종합하여 DBMS마다 고유의 알고리즘에 따라 산출되는 '비용'을 계산하기 때문에 변경되면 실행 계획을 다시 확인해야함

	RBO(Rule Based Optimizer)	CBO(Cost Based OPtimizer)
최적화 기준	규칙에 정의된 우선순위	액세스 비용
성능 기준	개발자의 SQL 숙력도	옵티마이저의 예측 성능
특징	실행 계획 예측이 쉬움	성능 통계치 정보 활용, 예측이 복잡함
고려사항	개발자의 규칙 이해도, 규칙의 효율성	비용 산출 공식의 정확성

 

1. 실행 계획(Execution Plan)

• DBMS의 옵티마이저가 수립한 SQL 코드의 실행 정차와 방법을 의미
• 실행 계획은 EXPLAIN 명령어를 통해 확인 가능
• 그래픽이나 텍스트로 표현
• 요구사항들을 처리하기 위한 연산 순서가 적혀있으며, 연산에는 조인, 테이블 검색, 필터, 정렬 등이 존재

2. 쿼리 성능 최적화

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

1) SQL 코드 재구성

• WHERE절을 추가해 일부 레코드만 조회하게 함으로써 조회 비용을 줄임
• WHERE정에 연산자가 포함되면 INDEX를 활용하지 못하므로 가능한 한 연산자 사용을 자제
• 서브 쿼리에 특정 데이터가 존재하는지 확인할 때는 IN보다 EXISTS 활용
• 옵티마이저의 실행 계획이 잘못되었다고 판단되는 경우 힌트를 활용해 실행 계획의 액세스 경로 및 조인 순서를 변경
  • 힌트(Hint) : SQL문에 추가되어 테이블 접근 순서를 변경하거나, 인덱스 사용을 강제하는 등의 실행 계획에 영햘을 줄 수 있는 문장

2) 인덱스 재구성

• 조회되는 속성과 조건들을 고려하여 인덱스를 구성
• 실행 계획을 참고하여 인덱스를 추가하거나 기존 인덱스의 열 순서를 변경
• 인덱스의 추가 및 변경은 해당 테이블을 참조하는 다은 SQL문에도 영향을 줄 수 있으므로 신중히 결정
• 단일 인덱스로 쓰기나 수정없이 읽기로만 사용되는 테이블의 경우 IOT(Index-Organized Table)로 구성하는 것을 고려
  • IOT : 인덱스 안에 테이블 데이터를 직접 삽입하여 저장함으로써 주소를 얻는 과정이 생략되어 빠른 조회 가능
• 불필요한 인덱스 제거

 

 

📖 Reference

2023 시나공 정보처리기사 필기 : 네이버 도서