SQL 최적화 원리

옵티마이저와 실행 계획

%%
옵티마이저란?
가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진
%%

DBMS 옵티마이저

• 규칙기반 옵티마이저 (RBO)
  • 특정 규칙세트에 따라 쿼리를 최적화함
  • 주로 인덱스 사용함
  • 단 데이터의 실질적인 내용을 고려 하지 않음
• 비용기반 옵티마이저 (CBO)
  • 비용을 계싼하여 최적의 쿼리 실행 전략 선택함
• 옵티마이저의 동작 방식
  • Parser - 파싱 트리를 생성 (이진트리)
  • Query Transformer
  • Estimator
  • Plan Generator
  • Row-Source Denerator
  • SQL Engine

공통점과 차이점

• 공통점
  • 두개 모두 SQL 쿼리의 싱행 전략을 선택하는 역할
  • 두개 모두 성능을 최적화 하기 위함
• 차이점
  • 규칙은 규칙세트를 사용하여 쿼리를 최적화함 즉 데이터 베이스의 상태를 고려 하지 않음
  • 비용기반은 실질적인 상태를 고려 하여 쿼리를 최적화함
• 튜닝 팁
  • MySQL 에서는 옵티마이저 힌트를 제공하여 특정 쿼리의 실행 방식을 제어 할수 있음!
  • 인덱싱 전략
    • 적절한 인덱스 구성은 쿼리 성능을 크게 향상시킴
  • 쿼리 리팩토링
    • 비효율적인 쿼리는 다시 작성하여 성능 개선 가능

SQL 처리 흐름도!

1. 파싱 - SQL 쿼리를 읽고 분석함
2. 최적화 - 쿼리 최적화할수 있는 방법을 찾음
3. 실행 - 최적화 단계에서 선택한 실행 계획에 따라 쿼리 행

---

인덱스 기본

%%
인덱스(Index)는 데이터베이스에서 데이터 검색 속도를 향상시키기 위한 자료구조임. 책의 색인과 비슷하게 동작하며, 특정 컬럼(들)의 값과 해당 데이터가 저장된 위치를 매핑하여 빠른 검색을 가능하게 함
%%

인덱스의 주요 특징

1. 데이터 검색 속도 향상
2. 자료구조
3. 인덱스 생성과 유지 관리
4. 인덱스의 유형
   • 단일 컬럼 인덱스
   • 복합 컬럼 인덱스
   • 유니크 인덱스
   • 클러스터 인덱스
   • 넌클러스터 인덱스

인덱스의 작동 방식

• 인덱스를 통해 해당 데이터를 빠르게 찾음
• 인덱스가 가르키는 위치를 통해 실제 데이터를 불러옴

단 모든 경우에 인덱스를 사용하는 것이 좋은것만은 아님
인덱싱이 많을 경우 조회에는 좋지만 수정 삭제 등에는 추가적인 비용이 발생하므로 무조건적으로 좋다고만은 할수 없음.

==인덱스는 조회 성능을 향상시키지만, 데이터의 삽입, 수정, 삭제 연산에서는 성능 저하==

---

조인 수행 원리

Nested Loop Join

• 줄여서 NL 조인 -> 루프가 중첩되어 있는 형태로 작동하는 조인 방식
• 가장 기본적이고 직관적인 방식임

수행되는 순서

1. 두개의 테이블이 있을때 테이블중 하나를 외부 테이블 다른 하나를 내부 테이블로 지정
2. 외부 테이블 순회
3. 외부 테이블의 각 행에 대해서 내부 테이블의 모든 행을 순회하는 또다른 루프를 순회 (이 과정에서 조인 조건에 부합하는 행을 찾음)
4. 조건에 부합하는 행을 찾으면 두 행을 조인해서 결과 집합에 추가함
5. 외부 테이블의 모든 행에 대해 이 과정을 반복함

%%
테이블이 많거나 데이터가 많을때는 비효율적임
%%

Sort Merge Join

• Sort Merge Join은 두 테이블을 조인할 때, 각 테이블의 조인 조건에 해당하는 열을 먼저 정렬한 후에 조인을 수행하는 방식입니다. 이 방식은 두 테이블이 이미 정렬되어 있는 경우, 또는 정렬이 용이한 경우에 효율적인 방법이 될 수 있습니다.

Hash Join

• Hash Join은 조인할 두 테이블 중 하나를 메모리에 적재할 수 있는 충분한 공간이 있다면 매우 효율적인 방법으로 조인을 수행하는 방식입니다.

---

쿼리 튜닝

• limit -> 전체 크기 줄임
• offset -> 시작 지점 지정 (초기화 값)

쿼리 최적화의 필요성

튜닝 절차