MySQL Index (4) 커버링 인덱스

커버링 인덱스는 쿼리를 실행하는 필요한 모든 데이터를 가지는 인덱스를 의미합니다.

 

https://www.geeksforgeeks.org/difference-between-b-tree-and-b-tree/

B+TREE 구조에서 리프(leaf)노드에 실제 데이터가 저장된다고 했었습니다. 하지만, 만약에 쿼리 필요한 모든 데이터가 인덱스에 포함되있다면, 리프(leaf)노드까지 찾아가지 않아도 되기 때문에, 매우 빠르게 검색이 가능해집니다.

 

index로 설정한 컬럼으로만 SELECT / WHERE / ORDER BY / GROUP BY를 작성하면, 커버링 인덱스를 이용할 수 있게 됩니다.

 

 

 

이전 포스트에서 작성한 테스트 테이블을 가지고 테스트를 계속 진행하겠습니다.

테스트를 위해서 간단한 인덱스를 만들겠습니다. 

 

CREATE INDEX IDX_COVERING_TEST ON employees (email);

 

아래 쿼리를 실행해보겠습니다.

mysql> EXPLAIN SELECT * FROM employees WHERE email = 'kgsdjha@gmail.com'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: IDX_COVERING_TEST
          key: IDX_COVERING_TEST
      key_len: 152
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.20 sec)

 

EXPLAIN 출력결과에 대해서는 이전 포스트(https://db-diary.tistory.com/21)  에서 살펴보았습니다.

 

위 출력내용을 먼저 확인해보겠습니다.

 

• select_type: SIMPLE
  UNION이나 Subquery가 존재하지 않는 SELECT문입니다.
  
  
• type: ref
  WHERE 조건을 지정할 때 컬럼을 PRIMARY KEY나 UNIQUE index를 사용하지 않는 경우에 표시됩니다. MySQL매뉴얼상에는 조회 결과가 몇개 row에만 해당하는 경우에는 괜찮은 join type이라고 합니다.
  
  
• key: IDX_COVERING_TEST
  테스트를 위한 INDEX를 이용하고 있는 것을 알 수 있습니다.
  
  
• key_len: 152
  사용한 INDEX의 길이입니다.
  
  
• ref: const
  WHERE 조건의 조건값을 고정값으로 설정했다는 의미입니다.
  
  
• Extra:
  값이 없는 경우에는 실제 데이터에 접근하였다는 의미입니다.

 

계속해서 아래 쿼리를 실행해보겠습니다.

mysql> EXPLAIN SELECT email FROM employees WHERE email = 'kgsdjha@gmail.com'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: IDX_COVERING_TEST
          key: IDX_COVERING_TEST
      key_len: 152
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index

 

이번에는 Extra에 Using index가 표시됩니다. 

MySQL 매뉴얼에는 아래 내용이 있습니다.

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

실제 행을 읽는데 인덱스 정보만 사용하여 테이블에서 검색된다는 의미입니다. 그래서 using index라고 표시되는 출력 내용에서 헷갈릴 수 있는데, index를 사용한다는 의미가 아니고, SELECT 절, WHERE절이 모두 index 컬럼으로만 작성되어서 커버링 인덱스를 이용하는 경우를 의미합니다.

 

 

인덱스는 계층구조로 관리되기 때문에, GROUP BY 절에서도 인덱스로 설정한 순서대로 작성하여야 합니다.

 

GROUP BY 조건에 포함된 인덱스 컬럼 (인덱스를 A, B, C 로 작성한 경우)	인덱스 이용 여부	비고
GROUP BY A, B, C	O
GROUP BY A, C	X	인덱스 컬럼 순서에서 빠진 컬럼이 있는 경우는 인덱스를 타지 못함.
GROUP BY B, C	X	인덱스에 첫번째 컬럼이 포함안됨
GROUP BY A	O	인덱스에 뒤쪽 컬럼은 포함되지 않아도 됨
GROUP BY A, B	O
GROUP BY A, B, C, D	X	인덱스에 없는 컬럼 D 포함

 

여기서 예외적으로 WHERE조건과 GROUP BY 가 함께 사용되는 경우에 WHERE조건이 동등비교인 경우에 해당 컬럼이 GROUP BY 절에 없어도 인덱스가 적용됩니다.

 

인덱스를 A, B, C 순으로 생성했다고 했을 때, 아래 조건의 경우에도 인덱스를 이용하게 됩니다.

WHERE A = 1 GROUP BY B, C

WHERE A = 1 AND B = 1 GROUP C

 

 

위의 내용을 실제 테스트 데이터를 이용해서 테스트해보겠습니다.

먼저 인덱스를 생성합니다.

CREATE INDEX IDX_COVERING_TEST2 ON employees (mobile, email, zipcode);

 

인덱스를 이용할 수 있는 동등비교 조건으로 EXPLAIN을 확인하였습니다.

EXPLAIN SELECT * FROM employees
WHERE mobile='990-278-0659'
GROUP BY email, zipcode\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: IDX_CARDINALITY_ORDER,IDX_COVERING_TEST2
          key: IDX_COVERING_TEST2
      key_len: 92
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.19 sec)

Extra가 Using index condition으로 나오고 있습니다.

Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary.

Using index condition은 index정보를 통해서 테이블을 full scan하지 않는 경우에 출려된다고 합니다.

 

 

인덱스를 이용하지 못하는 조건으로 EXPLAIN을 확인하였습니다.

EXPLAIN SELECT * FROM employees
WHERE mobile like '990-278-065%'
GROUP BY email, zipcode\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: range
possible_keys: IDX_COVERING_TEST2
          key: IDX_COVERING_TEST2
      key_len: 92
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using temporary; Using filesort
1 row in set, 1 warning (0.19 sec)

이번에는 Using temporary와 Using filesort 가 출력되고 있습니다.

Using temporary는 임시테이블을 생성한다는 의미이고, 

Using filesort는 GROUP BY 절에 의해서 정렬을 하기 위한 추가 처리를 하는 것을 의미합니다.

동등비교 조건으로 작성하지 않고 like 조건으로 작성하였기 때문에, 인덱스를 이용하지 못하는 것을 알 수 있습니다.

 

 

이번에는 커버링 인덱스를 이용해서 테스트를 진행해보겠습니다.

EXPLAIN SELECT mobile,email,zipcode FROM employees
WHERE mobile = '990-278-0659'
GROUP BY email, zipcode\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: IDX_CARDINALITY_ORDER_2,IDX_CARDINALITY_ORDER,IDX_COVERING_TEST2
          key: IDX_COVERING_TEST2
      key_len: 92
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index

커버링 인덱스를 이용하는 경우는 Using index 로 출력됩니다.

 

 

<참조 문서>

https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

https://jojoldu.tistory.com/476