만년 꼴지 공대생 세상 이야기

Real MySQL 8.0 - 10.실행 계획 - 인덱스 Type

  • DB
  • 2023. 7. 31.

    들어가기 전

    이 글은 Real MySQL 8.0을 공부하며 작성한 글입니다. 

     

    인덱스 레인지 스캔이란?

    • const, ref, range로 인덱스를 접근하면 인덱스 레인지 스캔이라고 의미함. 
    • 인덱스 레인지 스캔 = 인덱스를 효율적으로 이용함 = 작업 범위 결정 조건으로 인덱스를 이용함

     

    10.3.5 Type 컬럼

    실행 계획에서 Type 이후의 컬럼은 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타낸다. 인덱스를 탔는지, 인덱스를 탔다면 어떤 방식으로 탔는지, 테이블 풀스캔을 했는지 등을 알 수 있다. Type 컬럼에 올 수 있는 값은 다음과 같다. 

    • const
    • eq_ref
    • ref
    • fulltext
    • ref_or_null
    • unique_subquery
    • index_subquery
    • range
    • index_merge
    • index
    • ALL

    여기서 ALL은 테이블 풀 스캔이고, 나머지 모두는 인덱스를 탄 것이다. 어떻게 인덱스를 탔느냐에 따라 효율이 결정된다.

    const

    다음 조건을 만족하면 const 인덱스를 탄다.

    • 쿼리가 WHERE 조건 절에 PK / 유니크 인덱스를 사용함. 
      • 다중 컬럼을 이용한 PK / 유니크 인덱스 중 일부만 이용할 때는, const 인덱스를 타지 않음. 
    • 반환하는 레코드는 반드시 1개.
    • type const인 실행 계획은 옵티마이저가 쿼리를 최적화 할 때, 해당 쿼리를 실행해서 결과를 상수로 만들어서 다른 쿼리에 사용한다. 
      • WHERE first_name = (SELECT first_name FROM employees e2 WHERE  emp_no=1); 이런 조건이 있고, SELECT 절의 실행 계획이 const인 상황. 그리고 결과는 hello임.
      • 이 경우, 쿼리가 실행될 때 WHERE first_name = 'hello'으로 치환되서 쿼리가 실행됨 

    예를 들어 아래 쿼리는 const 인덱스를 탄다. 왜냐하면 PK가 조건절에 왔고, PK 인덱스에 해당되는 레코드는 1건이기 때문이다. 

    // PK -> (emp_no, dept_no)
EXPLAIN
SELECT * FROM employees WHERE emp_no=10001 and dept_no=30000\G;

id : 1
select_type : SIMPLE
table : employees
type : const
key : PRIMARY
key_len : 4

    반면 PK 인덱스 중 일부 컬럼(emp_no)만 사용하는 경우에는 ref 형식으로 인덱스를 탄다. 

    // PK -> (emp_no, dept_no)
EXPLAIN
SELECT * FROM employees WHERE emp_no=10001\G;

id : 1
select_type : SIMPLE
table : employees
type : ref
key : PRIMARY
key_len : 4

    eq_ref

    다음 조건을 만족하면 eq_ref 인덱스를 사용한다. 

    • 여러 테이블이 조인되는 쿼리일 때. 
    • 드라이빙 테이블에서 읽은 테이블의 컬럼값이 두번째 테이블의 PK, 유니크 키 칼럼의 검색 조건(WHERE)에 사용.
    • 두번째 테이블에서 읽는 레코드의 값이 단 1개일 때

    예를 들면 다음 쿼리에서 사용된다. 

    // 첫번째 테이블 dept_emp de
// 두번째 테이블 employees e
// 검색 조건 e.emp_no = de.emp_no

EXPLAIN
SELECT * FROM dept_emp de, employees e
WHERE e.emp_no=de.emp_no AND de.dept_no='d005'\G;

id  select_type  table    type     key       key_len  rows
1   SIMPLE       de       ref      PRIMARY   16       166561
1   SIMPLE       e        eq_ref   PRIMARY   4        1

    위의 경우 두번째 테이블이 해당된다.

    첫번째 테이블에서 읽은 컬럼이 두번째 테이블의 WHERE 조건의 PK로 사용되고, 반환하는 레코드가 1건이기 때문에 대응된다. 

    ref

    • ref는 조인 조건 / 유니크 인덱스 / PK 인덱스와 관련없이 사용된다. 
    • 인덱스 종류와 관계 없이 동등 조건 (Equal)로 검색할 때 Ref가 사용됨. 

    ref 인덱스로 검색 시, 검색 결과가 하나의 레코드가 아닐 수 있기 때문에 eq_ref, const에 비해서는 빠르지 않지만 동등한 조건으로만 비교되기 때문에 빠른 레코드 조회 방법이다. 

    // dept_no는 PK가 아님.
EXPLAIN
SELECT * FROM dept_emp WHERE dept_no='d005';

id : 1
select_type : SIMPLE
table : dept_emp
type : ref
key : PRIMARY
key_len : 16
ref : const

    상당히 빠른 인덱스 조회 조건

    • const : 조인의 순서와 관계없이 PK, 유니크 키의 모든 컬럼에 대해 equal 조건으로 검색 + 반드시 1건 반환하는 경우
    • eq_req : 조인 시, 첫번째 테이블의 읽은 컬럼이 두번째 테이블의 검색 조건 (WHERE)의 유니크 인덱스 / PK로 동등 (equal) 조건 검색. 반드시 1건 반환하는 경우
    • ref : 동등 조건으로 검색. 조인 / 인덱스 종류 상관없음. 여러 레코드 반환 가능 

    이 인덱스의 공통점은 모두 'equal' 조건으로 검색을 해야한다는 것이다. 세 가지 모두 좋은 접근 방법이고, 이 인덱스가 사용되는 경우 쿼리 튜닝을 더 하지 않아도 된다. 

    ref_or_null

    • ref 인덱스와 접근 방법은 동일한데, NULL 비교가(IS NULL) 추가됨. 

    아래 쿼리에서 사용됨. 

    SELECT * FROM titles
WHERE to_date='1985-03-01' OR to_date IS NULL; // 인덱스에 NULL 비교를 하는 경우. 

id : 1
select_type : SIMPLE
table : titles
type : ref_or_null // ref_or_null 사용됨
key : ix_todate
key_len : 4
ref : const
rows : 2

     

    unique_subquery

    • WHERE 조건 절에서 IN(Subquery) 형태의 쿼리를 위한 접근 방법이다. 
    • Subquery에서 유니크한 값만 반환할 때 이 접근 방법이 사용됨. 
      • 이 쿼리의 결과가 유니크한 값만 반환한다는 것이 보장될 때임. 중복을 따로 제거해주는 것이 아님. 
    EXPLAIN
SELECT * FROM departments
WHERE dept_no IN (SELECT dept_no FROM dept_emp WHERE emp_no=10001);

id : 1
select_type : PRIMARY
table : departments
type : index
key :ux_deptname
key_len : 162

id : 2
select_type : DEPENDENT SUBQUERY
table : dept_emp
type : unique_subquery // 여기서 사용됨
key : PRIMARY
key_len : 20

     

     

    index_subquery

    • IN 절에는 중복이 제거된 값이 들어가야한다. 
    • unique_subquery는 IN 절에서 사용된 서브 쿼리의 결과가 유니크한 값을 반환하는 것이 보장될 때 사용되는 방법이다.
    • 쿼리가 유니크한 값을 반환하지 않는데, 사용한 인덱스를 이용해 중복 값을 제거할 수 있는 경우 index_subquery를 사용한다.

     

     

    range

    • 인덱스 레인지 스캔을 의미함. 
    • 좋은 성능을 보여줌. 
    • 범위로 검색하는 경우에 주로 사용됨. (<, >, BETWEEN, IS NULL, IN, LIKE )
    EXPLAIN
SELECT * FROM employees WHERE emp_no BETWEEN 10002 AND 10004;

id: 1
select_type: SIMPLE
table: employees
type: range // RANGE 스캔이 사용됨. 
key: PRIMARY
key_len: 4
rows: 3

     

    index_merge

    • 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어 낸 후, 이 결과를 Merge 한 후에 레코드를 반환함. 
    • 효율적으로 동작하지 않음. 
      • 각각의 인덱스를 검색해야 함. 
      • Merge 하는 과정에서 중복되는 것들을 제거하는 비교 연산이 들어감. 

    예를 들면 아래 쿼리에서 사용한다. 

    // 인덱스 ix_firstname (first_name)

mysql> 
EXPLAIN
SELECT * FROM employees
WHERE emp_no BETWEEN 10001 AND 11000
	OR first_name='Smith';
    
>>>
id : 1
type : index_merge
key : PRIMARY, ix_firstname
key_len : 4,58
Extra : Using Union(PRIMARY, ix_firstname); Using Where

    위 쿼리를 살펴보면 emp_no (PK)로 검색을 한 결과이거나 first_name이 스미스인 경우를 찾는다. 인덱스를 이용해 각각을 검색할 수 있기 때문에 다음과 같이 처리한다.

    1. PRIMARY, ix_firstname 인덱스에 대해서 각각 검색한다.
    2. MERGE 한 후에 결과를 반환함. 

     

     

    index

    • 풀 인덱스 스캔을 의미함. 만들어진 인덱스를 처음부터 끝까지 다 읽음.
    • 풀 테이블 스캔과 읽는 레코드 건수는 동일하지만, 인덱스의 용량이 적기 때문에 디스크 I/O가 적게 발생함. 
    • 또한 인덱스는 정렬이 되어 있기 때문에 이 순서대로 읽기를 원한다면, 인덱스를 이용하는게 더 좋음 
    • 아래 경우에 풀 인덱스 스캔을 함.
      • const, ref, range 접근 방법으로 인덱스를 사용하지 못하는 경우 (필)
      • 인덱스에 포함된 컬럼으로만 처리될 수 있는 커리인 경우 (선1)
      • 인덱스를 이용한 정렬이나 그룹핑 작업이 가능한 경우 (선2)
      • 필수 조건에 선택 1 / 2 중 하나를 만족하면 풀 인덱스 스캔으로 동작함. 

     

    ALL

    • 풀 테이블 스캔을 의미함. 테이블을 처음부터 끝까지 읽음.  가장 비효율적임. 
    • 선택할 수 있는 방법이 없을 때 선택함. 
    • 다만 처리 속도를 빠르게 하기 위해서 MySQL은 Read Ahead를 지원함. (클라이언트 쓰레드는 백그라운드 쓰레드가 미리 가져온 데이터를 서빙만 함.

     

     

    저작자표시 비영리 변경금지

    댓글

    Copyright © 만년 꼴지 공대생 세상 이야기 All Rights Reserved

    Designed by JB FACTORY

    티스토리툴바