[업무에 바로 쓰는 SQL 튜닝] 2. SQL 튜닝 용어를 직관적으로 이해하기

양바른 님의 "Real MySQL" 책을 정리한 포스팅 입니다.

 

1. 물리 엔진과 오브젝트 용어

SQL 프로세스 용어

파서

• 사용자가 요청한 SQL 문을 쪼개 최소 단위로 분리하고 트리를 만듬
• 트리를 만들면서 문법 검사를 수행함

 

전처리기

• 파서에서 생성한 트리를 토대로 SQL 문에 구조적인 문제가 없는지 확인
• SQL 문에 사용된 오브젝트들이 실직적으로 존재하는지, 접근 권한은 부여되어 있는지 확인

 

옵티마이저

• 전달된 파서 트리를 토대로 연산 과정을 단순화함 (필요하지 않은 조건을 제거함)
• 실행 계획 수립 (테이블 접근 순서, 인덱스 사용 유무, 임시테이블 사용 유무)

 

엔진 실행기

• 수립된 실행 계획을 참고하여 스토리지 엔진에서 데이터를 가져옴
  • 읽어온 데이터를 정렬하거나 조인
  • 불필요한 데이터는 필터링

 

DB 오브젝트 용어

기본키

• 특정 행을 대표하는 열
• 기본키 구성 열 순서를 기준으로 물리적인 스토리지에 데이터가 쌓임
• MySQL/MariaDB에서 기본키는 클러스터형 인덱스로 동작함
• 인덱스 역할도 수행함

 

인덱스

• 키 기준으로 정렬된 오브젝트
  • 데이터 접근 속도를 높이고자 생성됨
  • 키 값으로 실제 데이터 위치를 식별함
• 열의 속성에 따라 구분됨
  • 고유 인덱스: 인덱스를 구성하는 열들의 데이터가 유일함 
  • 비고유 인덱스: 고유 인덱스에서 데이터의 유일한 속성만 제외한 키

 

2. 논리적인 SQL 개념 용어

조인

블록 중첩 루프 조인

• 중첩 루프 조인의 효율성을 높이고자 고안된 방식
  • 조인 버퍼를 사용하여 성능을 향상함
  • 드리븐 테이블을 한번의 테이블 풀 스캔으로 처리가능
• 과정
  1. 드라이빙 테이블의 데이터를 모두 조인 버퍼에 적재
  2. 드리븐 테이블을 한행씩 읽으며, 조인 버퍼의 모든 데이터와 비교하여 매칭되는 행을 찾음
  3. 드리븐 테이블의 모든 행을 처리할 때까지 반복

 

배치 키 액세스 조인

• 중첩 루프 조인의 성능을 개선하기 위해 사용되는 방법
  • 중첩 루프 조인을 사용할 경우, 랜덤 액세스 기반이므로 액세스할 데이터 범위가 넓다면 비효율적임
  • 블록 중첩 루프 조인에서 활용한 드라이빙 테이블의 조인 버퍼 개념을 그대로 사용함
  • 랜덤 버퍼 도입
• MRR
  • 드리븐 테이블의 데이터를 예측하고 정렬된 상태로 랜덤 버퍼에 담음
  • 랜덤 액세스가 아닌 시퀀셜 액세스

• 과정
  1. 드라이빙 테이블의 데이터를 모두 조인 버퍼에 적재
  2. 드리븐 테이블의 인덱스를 기반으로 필요한 데이터를 예측하여 랜덤 버퍼에 적재
  3. 조인 버퍼와 랜덤 버퍼의 행들을 비교하여 매칭되는 행을 찾음
  4. 랜덤 버퍼에서 매칭된 행들의 id를 가지고 드리븐 테이블을 조회하여 최종 결과를 만듬

 

해시 조인

• 블록 중첩 루프 조인과 배치 키 액세스 조인의 성능을 개선한 방법
• 과정
  1. 해시 값으로 데이터 그룹화
     • 조인에 참여하는 각 테이블의 데이터를 해시값으로 변환하여, 조인할 데이터를 그룹화함
     • 그룹화된 데이터를 해시 테이블에 저장하여 비교할 수 있게 함
  2. 해시 값 기반 내부 조인 수행
     • 같은 해시값을 가지는 행끼리 조인을 수행
     • 인덱스 없이 효율적인 처리 가능
  3. 내부 조인을 수행한 결과는 조인 버퍼에 저장됨

 

3. 개념적인 튜닝 용어

오브젝트 스캔 유형

인덱스 고유 스캔

• 기본키나 고유 인덱스로 테이블에 접근하는 방법
• 조인문에서 인덱스의 선두열로 설정되었을 때 사용
• 가장 효율적인 방법

 

인덱스 루스 스캔

• 인덱스의 필요한 부분들만 골라 스캔하는 방식
• 인덱스를 완전히 스캔하지 않음
• GROUP BY나 ORDER BY 구문에서 주로 사용

 

인덱스 병합 스캔

• 테이블 내의 생성된 인덱스들을 통합해서 사용
• 결합과 교차 방식으로 통합

 

SELECT * FROM employees WHERE first_name = 'Noah' AND hire_date = '2025-06-01';
SELECT * FROM employees WHERE first_name = 'Noah' OR hire_date = '2025-06-01';

• first_name 과 hire_date 컬럼에 각각 다른 인덱스가 있을 경우, 두 인덱스를 병합해서 효율적으로 쿼리를 실행함
  • AND 조건을 사용할 떄, 두 인덱스를 결합하여 사용함
  • OR 조건을 사용할 떄, 두 인덱스를 병합하여 사용함

 

응용 용어

선택도

• 테이블의 특정 열을 기준으로 해당 열의 조건절에 따라 선택되는 데이터 비율
• 해당 열에 중복되는 데이터가 많으면 '선택도가 높다' 평가됨

 

카디널리티

• 하나의 데이터 유형으로 정의되는 데이터 행의 개수
• 전체 데이터에 접근한 뒤 출력될 것이라 예상되는 데이터 건수
• 전체 데이터 건수에 해당 열의 선택도를 곱하여 계산할 수 있음

 

힌트

• 데이터를 빨리 찾을 수 있게 옵티마이저에게 실행계획 수립에 필요한 추가 정보를 제공하는 객체

 

히스토그램

• 열 값이 어떻게 분포되어 있는지 확인하는 통계정보
• 옵티마이저가 실행 계획을 최적화하고자 참고하는 정보
• 열의 분포를 저장할 떄는 높이 균형 히스토그램 방식을 사용함

ANALYZE TABLE 테이블명 UPDATE HISTOGRAM ON 열명;

SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;