SQL 튜닝 용어

오브젝트 스캔 유형

  1. 테이블 스캔 : 인덱스를 사용하지 않고 바로 디스크에 있는 테이블 데이터에 접근
  2. 인덱스 스캔 : 인덱스로 테이블 데이터에 접근

테이블 스캔

  1. 테이블 풀 스캔(Table Full Scan) : 테이블의 처음부터 끝까지 스캔하는 방식
    • 인덱스 없이 사용하는 유일한 방식 image

인덱스 스캔

  1. 인덱스 범위 스캔(Index Range Scan) : 인덱스를 범위 기준으로 스캔한 후 스캔 결과를 토대로 데이터를 찾는 방식
    • BETWEEN,>, <, LIKE 등의 구문, 비교 연산시 사용
    • 좁은 범위를 스캔할 때는 효율적이지만 넓은 범위를 스캔할 때는 비효율적 image
  2. 인덱스 풀 스캔(Index Full Scan) : 인덱스를 처음부터 끝까지 스캔하는 방식
    • 테이블에 접근하지 않으며 인덱스만 탐색
    • 인덱스를 구성된 컬럼만 요구하는 SQL 문에 사용 image
  3. 인덱스 고유 스캔(Index Unique Scan) : PK나 Unique Index로 테이블에 접근하는 방식
    • WHERE 절에 = 조건으로 작성시 사용
    • 가장 효율적인 스캔 방식 image
  4. 인덱스 루스 스캔(Index loose Scan) : 인덱스의 필요한 부분만 스캔하는 방식
    • 인덱스 범위 스캔처럼 넓은 범위에 전부 접근하지 않고 WHERE 조건문 기준으로 필요한 데이터와 필요하지 않은 데이터를 구분한 뒤 불필요한 인덱스 키는 무시
    • GROUP BY, MAX, MIN 포함시 사용 image
  5. 인덱스 병합 스캔(Index Merge Scan) : 테이블 내의 생성된 인덱스를 통합해서 스캔하는 방식
    • WHERE 조건절의 컬럼이 서로 다른 인덱스에 존재하면 옵티마이저가 해당 하는 인덱스들을 가져와 모두 활용
    • 물리적으로 분리된 인덱스를 각각 수행하므로 비효율적이기에 하나의 인덱스로 통합하여 튜닝하거나 SQL문을 수정해 하나의 인덱스만 사용하도록 튜닝해야 한다 image

디스크 접근 방식

  • 원하는 데이터를 찾기위해 데이터가 저장된 디스크(스토리지)의 페이지에 접근해야 한다
  • 페이지 : 데이터를 검색하는 최소단위
  1. 시퀀셜 액세스(Sequential Access) : 물리적으로 인접한 페이지를 차례대로 순차 접근 방식
    • 테이블 풀 스캔에 사용
  2. 랜덤 엑세스(Random Access) : 물리적으로 떨어진 페이지에 임의로 접근하는 방식
    • 데이터 접근 수행시간이 오래 걸림

조건 유형

  1. 액세스 조건(Access Condition) : 디스크에 있는 데이터에 어떻게 접근해 가져올 것인지를 결정하는 조건
    • 옵티마이저는 WHERE 절의 특정 조건문을 이용해 소량의 데이터를 가져오고 인덱스를 통해 시간 낭비를 줄이는 조건문을 선택한다
    • ex) SELECT * FROM TAB WHERE id=1 AND code='A', Table2_idx:TAB(id)
    • id로 인덱스가 있기 때문에 id가 액세스 조건이 된다
  2. 필터 조건(Filter Condition) : 액세스 조건을 이용해 가져온 데이터를 기준으로 불필요한 데이터를 제거하거나 가공하는 조건
    • ex) id를 통해 가져온 데이터는 code='A'를 필터 조건으로 적용해 필터링 작업을 수행

쿼리 플랜

EXPLAIN SELECT * FROM customer
WHERE customer_id BETWEEN 100001 AND 200000;

image

1. id : 실행 순서

  • ID가 작을 수록 먼저 수행된 것이고 ID가 같다면 두 테이블의 조인이 이루어졌다고 해석

2. select_type : SELECT문 유형

  • SIMPLE : union이나 서브쿼리 없는 SELECT문
  • PRIMARY : 서브쿼리가 포함된 SQL문이나 union에서 에서 첫번째 SELECT 문에 해당하는 구문
  • SUBQUERY : 서브쿼리
  • UNION : union이나 union all에서 첫번째 SELECT문이 아닌 구문
  • DERIVED : FROM절에 작성된 서브쿼리
  • UNION RESULT : union all이 아닌 union 구문에서 SELECT절을 결합했을 때 출력
  • DEPENDENT SUBQUERY : union이나 union all을 사용한 서브쿼리가 메인 테이블의 영향을 받는 경우, union으로 연결된 단위 쿼리들 중에서 처음으로 작성한 단위 쿼리에 해당
  • DEPENDENT UNION : union이나 union all을 사용한 서브쿼리가 메인 테이블의 영향을 받는 경우, 두 번째 단위 쿼리에 해당
  • UNCACHEABLE SUBQUERY : 메모리에 상주하여 재활용 되어야 할 서브쿼리가 재사용 되지 못할 때 출력 (튜닝대상)
  • MATERIALIZED : in 절 구문에 연결된 서브쿼리가 임시 테이블을 생성한 뒤, 조인이나 가공 작업을 수행할 때 출력

3. table : 테이블 명

4. partitions : 접근한 파티션

  • 파티션 : 데이터가 저장된 논리적인 영역

5. type : 데이터를 찾는 방법

  • system : 테이블에 데이터가 없거나 하나만 있는경우
    • BEST 타입
  • const : 조회되는 데이터가 1개일 경우
    • unique index나 pk로 단 1개의 데이터만 접근하기에 매우 유리한 타입
  • eq_ref : 조인 수행시 드리븐 테이블(join된 테이블)의 데이터에 접근할 때 unique index나 pk로 단 1개의 데이터를 조회하는 경우
    • 조인 수행시 가장 유리한 타입
  • ref : 조인 수행시 드리븐 테이블의 테이터 접근 범위가 2개 이상일 경우
    • 드리븐 데이터의 데이터가 많다면 접근할 데이터 범위가 넓어져 성능 저하가 될 수 있다
    • 또한 =, <, > 등의 연산자를 사용해 인덱스로 생성된 열을 비교할 경우에도 사용됨
  • ref_or_null : ref와 유사하지만 IS NULL에 대해 인덱스를 사용해 최적화된 방식
    • 이 때, NULL이 가장 앞쪽에 정렬됨
    • NULL 데이터양이 적다면 ref_or_null 방식을 사용해 효율적인 SQL이 되지만, 많다면 SQL 튜닝 대상이 된다
  • range : 테이블 내의 연속된 데이터 범위를 조회하는 경우
    • =, <, <=, >=, >, <>, IS NULL, <=>, BETWEEN, IN 등 범위 스캔 사용시 수행
    • 스캔할 범위가 넓으면 튜닝 대상이 된다
  • fulltext : 텍스트 검색을 빠르게 처리하기 위해 전문 인덱스(full text index)를 사용
  • index_merge : 2개 이상의 인덱스를 사용할 경우
  • index : 인덱스 풀 스캔을 사용할 경우
    • 인덱스는 테이블 크기보다 작으므로 테이블 풀 스캔보다 빠를 가능성이 높음
  • ALL : 테이블 풀 스캔을 사용할 경우
    • 사용할 인덱스가 없거나 인덱스가 비효율적이라고 옵티마이저가 판단한 경우
    • 인덱스를 추가하거나 기존 인덱스를 변경할 수 있지만 전체 테이블 중 10~20%이상의 데이터를 조회할 경우 ALL이 유리할 수 있음

6. possible_keys : 옵티마이저가 사용할 수 있는 인덱스 목록

7. key : 옵티마이저가 사용한 PK나 인덱스

8. key_len : 사용한 인덱스 byte수

  • 인덱스를 사용할 때는 인덱스 전체를 사용하거나 일부 인덱스만 사용한다
  • UTF-8기준 단위당 VARCHAR는 3byte, INT는 4byte로 인지하고 계산가능

9. ref : 해당 테이블에 액세스한 조건

10. rows : 접근한 데이터 row수

  • 최종 결과 수와 비교해 rows 수가 크게 차이가 난다면 불필요하게 엔진이 데이터를 많이 가져왔다는 의미이므로 튜닝 대상이 될 수 있다

11. filtered : 필터 조건으로 필터링된 비율(%)

  • DB 엔진으로 100건의 데이터를 가져왔을 때, WHERE id BETWEEN 1 AND 10에 의해 10건으로 필터링되면 filtered는 10(%)이 된다

12. extra : 추가 정보

  • Distinct : 중복 제거
  • Using where : WHERE 절의 필터 조건을 사용
  • Using temporary : 데이터의 중간 결과를 저장하고자 임시 테이블을 생성
    • 데이터를 가져와 저장한 뒤 정렬 작업을 수행하거나 중복을 제거하는 작업 등을 수행
    • DISTINCT, GROUP BY, ORDER BY 등이 포함된 경우
    • 성능 저하의 원인이 될 수 있음
  • Using index : 데이터를 읽지 않고 인덱스만 읽어 처리할 수 있는 경우
    • index로 구성된 컬럼만 SQL문에서 사용한 경우
    • 성능 우수
  • Using filesort : 정렬이 필요한 데이터를 메모리에 올리고 정렬 작업을 수행
    • 이미 정렬된 인덱스를 사용할 경우 필요 없지만, 인덱스를 사용하지 못할 경우 정렬을 위해 메모리에 데이터를 올림
    • 추가적인 정렬 작업이므로 인덱스를 사용하도록 튜닝 대상이 될 수 있음
  • Using join buffer : 조인을 사용하기 위해 중간 데이터 결과를 저장하는 조인 버퍼를 사용
  • Using union / Using intersect / Using sort_union : 인덱스를 병합해서 사용할 경우 방법을 설명함
  • Using index condition : 필터 조건을 스토리지 엔진으로 전달해 필터링 작업에 대한 Mysql 엔진의 부하를 줄이는 방식
  • Using index for group-by : GROUP BY나 DISTINCT 구문이 포함될 경우 인덱스로 정렬 작업을 수행해서 최적화 하는 방식
  • Not exists : 하나의 일치하는 행을 찾으면 추가로 행을 더 검색하지 않아도 될 경우 출력

튜닝 대상

select_type

  • GOOD : SIMPLE, PRIMARY, DERIVED
  • BAD : DEPENDENT *, UNCACHEBLE *

type

  • GOOD : system, const, eq_ref
  • BAD : index, all

extra

  • GOOD : Using index
  • BAD : Using filesort, Using temporary