독서/업무에 바로 쓰는 SQL 튜닝

개념적인 튜닝용어

yoon_seon 2024. 1. 30. 00:55

기초 용어

오브젝트 스캔 유형

  • 테이블 스캔과 인덱스 스캔으로 구분
  • 테이블 스캔 : 인덱스를 거치지 않고 바로 디스크에 위치한 테이블 데이터에 접근
    • 테이블 풀 스캔
  • 인덱스 스캔 : 인덱스로 테이블 데이터에 접근
    • 인덱스 범위 스캔
    • 인덱스 풀 스캔
    • 인덱스 고유 스캔
    • 인덱스 루스 스캔
    • 인덱스 병합 스캔


📈 테이블 풀 스캔

  • 인덱스를 거치지 않고 테이블에서 바로 처음부터 끝까지 훑는 방식
  • WHERE 절의 조건문을 기준으로 활용할 인덱스가 없거나, 전체 데이터 대비 대량의 데이터가 필요할 때 수행된다.
  • 성능 측면에서는 부정적이다.
  • 인덱스 없이 사용하는 유일한 방식이다.


📈 인덱스 범위 스캔

  • 인덱스 범위 기준으로 스캔한 뒤 테이블의 데이터를 찾아가는 방식
  • SQL문에서 BETWEEN ~ AND 구문이나, <, >, LIKE 구문 등 비교 연산 및 구문에 포함될 경우 인덱스 범위 스캔으로 수행된다.
  • 좁은 범위를 스캔할 때는 매우 효율적, 넓은 범위를 스캔할 때는 비효율적인 방식


📈 인덱스 풀 스캔

  • 인덱스를 처음부터 끝까지 수행하는 방식
  • 테이블에 접근하지 않고 인덱스로 구성된 열 정보만 요구하는 SQL문에서 수행된다.
  • 인덱스는 테이블보다 상대적으로 적은 양을 차지하므로 인덱스 풀 스캔 방식이 테이블 풀 스캔보다 성능상 유리하다.
  • 인덱스라는 오브젝트의 전 영역을 모두 검색하는 방식인 만큼 검색 범위를 최대한 줄이는 방향으로 SQL 튜닝 해야한다.


📈 인덱스 고유 스캔

  • 기본키나 고유 인덱스로 테이블에 접근하는 방식
  • 인덱스를 사용하는 스캔 방식 중 가장 효율적인 스캔 방법
  • WHERE절에 = 조건으로 작성하며, 해당 조인 열이 기본키 또는 고유 인덱스의 선두 열로 설정되었을때 활용된다.


📈 인덱스 루스 스캔

  • 인덱스의 필요 부분만 골라 스캔하는 방식
  • 인덱스 범위 스캔처럼 넓은 범위에 접근하지 않고 WHERE 절 조건문 기준으로 필요한 데이터에만 접근
  • GROUP BY 구문이나 MAX(), MIN() 함수가 포함되면 작동한다. → 이미 오름차순으로 정렬된 인덱스에서 최댓값이나 최솟값이 필요한 경우 해당된다.


📈 인덱스 병합 스캔

  • 테이블 내에 생성된 인덱스들을 통합해서 스캔하는 방식
  • WHERE 문 조건절의 열들이 서로 다른 인덱스로 존재하면 옵티마이저가 해당하는 인덱스를 가져와 모듀 활용하는 방식을 취한다.
  • 통합하는 방법으로는 결합(union), 교차(intersection) 방식이 있으며 이들 방식은 모두 실행 계획으로 출력된다.
  • 물리적으로 존재하는 개별 인덱스를 각각 수행하므로 인덱스에 접근하는 시간 소모가 크기 때문에, 일반적으로 여러 개의 별개 인덱스 대신 하나의 인덱스로 통합하거나, SQL문을 변경하여 단일 인덱스만 사용하도록 튜닝한다.


디스크 접근 방식

💾 시퀀셜 액세스

  • 물리적으로 인접한 페이지를 차례대로 읽는 순차 접근 방식으로 보통 테이블 풀 스캔에서 활용한다.
  • 주로 사용되는 상황: 테이블의 전체 데이터를 검색해야 할 때, 또는 범위 검색이 아닌 전체 데이터를 순차적으로 처리해야 할 때 시퀀셜 액세스가 활용
  • 디스크 헤더 이동 최소화: 시퀀셜 액세스는 데이터를 찾고자 하는 디스크 헤더의 이동을 최소화하면서 인접한 페이지를 순차적으로 읽는다.
  • 다중 페이지 읽기: 테이블 풀 스캔 시에는 인접한 페이지를 여러 개 읽는 다중 페이지 읽기 방식(multi-page read)으로 수행되며 이는 성능을 향상시키는데 기여할 수 있음.


💾 랜덤 액세스

  • 페이지의 물리적인 위치를 고려하지 않고, 임의의 페이지에 직접 접근하는 방식
  • 시간 소요: 페이지에 디스크 헤더가 물리적으로 이동하면서 데이터에 접근하므로, 데이터 접근에 많은 시간이 소요된다.
  • 다중 페이지 액세스 어려움: 랜덤 액세스는 하나의 페이지에 대한 액세스만을 고려하기 때문에 다중 페이지에 대한 효율적인 액세스가 어려울 수 있다.
  • 튜닝 필요: 최소한의 페이지에 접근할 수 있도록 접근 범위를 줄이고, 효율적인 인덱스를 사용하여 랜덤 액세스의 성능을 향상시킬 수 있다.


조건 유형

맨 처음 디스크에서 검색하는 조건을 액세스 조건이라고하고, 디스크에서 가져온 데이터를 추가로 추출하거나 가공 및 연산하는 조건을 필터 조인이라고 한다.

📌 액세스 조건

  • 데이터베이스에서 데이터를 검색할 때, 옵티마이저가 WHERE 절의 특정 조건문을 기반으로 소량의 데이터를 가져오기 위해 선택한 조건
  • 이 조건은 주로 인덱스를 활용하여 효율적으로 데이터에 접근할 수 있는지를 결정한다.

📌 필터 조건

  • 액세스 조건을 이용해 MySQL 엔진으로 가져온 데이터를 기준으로 추가로 불필요한 데이터를 제거하거나 가공하는 조건
  • 만약 필터 조건에 따라 필터링할 데이터가 없다면 훌륭한 SQL, 필터 조건으로 제거된 데이터가 다수 존재한다면 비효율적인 SQL이다. → 스토리지 엔진에서 MySQL 엔진으로 데이터를 전달하는 오버헤드가 있으며 필터 조건으로 제거될 데이터라면 스토리지 엔진의 데이터에 접근 과정에서 같이 제외되는 편이 성능적으로 효율적이기 때문
  • 필터 조건으로 제거되는 데이터의 비율을 확인하고 SQL 튜닝이 필요한지 판단할 수 있따.
    • 해당 비율은 실행 계획의 filtered 항목에서 확인 가능하다.

응용 용어

선택도

  • 테이블의 특정 열을 기준으로 해당 열의 조건절(WHERE절 조건문)에 따라 선택되는 데이터 비율을 의미
  • 열에 중복되는 열이 많다면 선택도가 높다고하며, 실제로 조건절에 따라 대량의 데이터가 선택된 것이다.
  • 열에 중복되는 열이 적다면 선택도가 낮다고하며, 실제로 조건절에 따라 소량의 데이터가 선택된 것이다.
  • 낮은 선택도를 가지는 열은 데이터를 조회하는 SQL에서 원하는 데이터를 빨리 찾기 위한 인덱스 열을 생성할 때 주요 고려 대상이 된다.

선택도를 계산하는 수식

선택도 = 선택한 데이터 건수 / 전체 데이터 건수

그러나 매번 선택하는 조건절의 데이터 건수를 계산할 수 없고 삽입 수정이 빈번한 만큼 중복이 제거된 데이터의 건수를 활용하여 선택도를 일반화한다.

변형된 선택도 = 1 / DISTINCT(COUNT 열명)


카디널리티

  • 하나의 데이터 유형으로 정의되는 데이터 행의 개수 → 전체 데이터에 접근한 뒤 출력될 것이라 예상되는 데이터 건수
  • 전체 행에 대한 특정 열의 중복 수치를 나타내는 지표로 자주 활용한다.
카디널리티 = 전체 데이터 건수 * 선택도
  • 특정 열에 중복된 값이 많다면 카디널리티가 낮다고 표현, 해당 열을 조회하면 상당수의 데이터를 거르지 못한 채 대량의 데이터가 출력될 것을 예측할 수 있다.
  • 중복도 ⬆️ 카디널리티 ⬇️
  • 중복도 ⬇️ 카디널리티 ⬆️
일상생활에서의  카디널리티 적용 사례
- 주민등록번호 : 카디널리티 높음
- 이름 : 카디널리티 중간
- 성별 : 카디널리티 낮음


힌트

데이터를 빨리 찾을 수 있도록 추가 정보를 전달

STRAIGHT_JOIN FROM 절에 작성된 테이블 순으로 조인을 유도하는 힌트 높음
USE INDEX 특정 인덱스를 사용하도록 유도하는 힌트 높음
FORCE INDEX 특정 인덱스를 사용하도록 강하게 유도하는 힌트 낮음
IGNORE INDEX 특정 인덱스를 사용하지 못하도록 유도하는 힌트 낮음

 

강력하지 않은 힌트란?
명시적으로 힌트를 작성해도 옵티마이저는 무조건 힌트를 참고하지 않는다. 티마이저가 비효율적이라고 예측하면 사용자가 작성한 힌트는 무시될 수 있다.
힌트 사용 시 고려사항
힌트가 적용된 환경에서는 데이터 건수가 수시로 급변할 수 있고 테이블이나 인덱스/뷰 등에 변화가 생기면 SQL문 실행 시 오류가 발생할 수 있다. 이때는 SQL 문에 힌트를 작성하면 별도로 관리해야한다.

 

 

콜레이션

특정 문자셋으로 데이터베이스에 저장된 값을 비교하거나 정렬하는 작업의 규칙

  • 예시
    • 10 과 11 중 더 큰값은? → 11
    • 2015/11/22와 2016/12/31 중 더 큰 값은? → 2016/12/31
    • a와 A중 더 큰 값은? → ?
    • a와 b중 더 큰 값은? → ?
utf8_bin utf8_general_ci
A A
B a
a B
b b
  • utf8_bin은 A, B, a, b 순으로 크고 utf8_general_ci는 A,a, B, b 수능로 나타난다.


콜레이션은 데이터베이스 단위, 테이블 단위, 열 단위를 세세하게 설정할 수 있다.

만약 학생 테이블의 콜레이션이 utf8_general_ci로 설정되어 있다면, 학번 열과 전공코드 열에 콜레이션이 명시되어 있지 않아도 utf8_general_ci로 적용된다.

이 때 열에 utf8_bin을 명시했다면 상위 콜레이션을 무시하고 utf8_bin으로 명시된다.

캐릭터 셋 vs 콜레이션
캐릭터 셋은 데이터를 어떻게 저장할지 결정
콜레이션은 데이터를 어떻게 정렬할지 결정


통계 정보

  • 옵티마이저가 SQL 실행 계획을 수립할 때 사용하는 데이터베이스 통계에 기반한 정보
  • MySQL은 시스템 변수를 통해 활용할 통계정보의 수준을 정의할 수 있다.
  • 기본적으로 통계정보에는 다음과 같은 내용이 포함될 수 있다. → 따라서 통계정보의 최신성 유지 및 관리가 매우 중요
    1. 테이블 통계 정보: 테이블의 레코드 개수, 인덱스의 크기 등과 같은 테이블 전체에 대한 정보
    2. 인덱스 통계 정보: 인덱스의 카디널리티, 특정 값이나 범위에 대한 인덱스의 분포 등과 같은 인덱스에 대한 정보
    3. 열 통계 정보: 특정 열의 값의 분포, 중복도 등과 같은 열에 대한 정보


히스토그램

  • 테이블의 열값이 어떻게 분포되어 있는지 확인하는 통계정보로 옵티마이저가 실행 계획을 최적화하고자 참고하는 정보
  • 잘못된 히스토그램 정보가 있다면 잘못된 실행계획으로 SQL이 수행될 수 있다.
  • 만약 특정 열값들의 통계정보가 히스토그램으로 수집되지 않았다면 중복이 제거된 열값의 개수로 대략적인 열 값은 분포를 예측하고 실행 계획을 수립한다.