데이터베이스
[MySQL] 복합 인덱스와 커버링 인덱스
jwKim96
2023. 1. 26. 17:18
MySQL 에서 복합 인덱스를 걸었을 때 실제로 인덱스를 타는건 어떤 상황에 탈까?
1. 테이블 & 인덱스
mysql> desc review;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| id | bigint | NO | PRI | NULL | |
| review_content | varchar(255) | YES | | NULL | |
| seq | bigint | YES | MUL | NULL | |
| store_id | bigint | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
Indexes
- PRIMARY (id)
- idx_seq_store_id(seq, store_id)2. 인덱스 테스트
review 테이블에 데이터를 1000건 넣어놓고 여러 경우의 수를 테스트해볼 예정입니다.
2.1 seq 만 조회
1) WHERE =
mysql> EXPLAIN SELECT seq FROM review WHERE seq = 0;
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | review | NULL | ref | idx_seq_storeId | idx_seq_storeId | 9 | const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+type 은 ref, Extra 에는 Using index 라고 되어있습니다.
- ref : where 절의 '=' 에 대한 연산을 index 를 이용했다는 의미
- Using index : 테이블에 접근하지 않고 인덱스에만 접근해서 처리함(커버링 인덱스)
2) WHERE >
mysql> EXPLAIN SELECT seq FROM review WHERE seq > 0;
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | review | NULL | range | idx_seq_storeId | idx_seq_storeId | 9 | NULL | 999 | 100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+type 은 range, Extra 에는 Using where; Using index 라고 되어있습니다.
- range : index 의 일정 범위에만 접근했다는 의미(where 절의 '>' 에 대한 연산을 index 를 이용했다는 의미)
- Using where : 데이터를 가져온 후 where 절의 조건을 이용하여 범위를 축소함
- Using index : 테이블에 접근하지 않고 인덱스에만 접근해서 처리함(커버링 인덱스)
3) ORDER BY
mysql> EXPLAIN SELECT seq FROM review ORDER BY seq;
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | review | NULL | index | NULL | idx_seq_storeId | 18 | NULL | 1000 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+type 은 index, Extra 에는 Using index 라고 되어있습니다.
- index : index 전체를 풀 스캔 했다는 의미
- Using index : 테이블에 접근하지 않고 인덱스에만 접근해서 처리함(커버링 인덱스)
seq 만 했을때는 인덱스를 탄다.
2.2 seq, store_id 조회(복합인덱스 순서대로)
mysql> EXPLAIN SELECT seq, store_id FROM review WHERE seq = 0 AND store_id = 0;
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | review | NULL | ref | idx_seq_storeId | idx_seq_storeId | 18 | const,const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
mysql> EXPLAIN SELECT seq, store_id FROM review WHERE seq > 0 AND store_id > 0;
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | review | NULL | range | idx_seq_storeId | idx_seq_storeId | 9 | NULL | 999 | 33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
mysql> EXPLAIN SELECT seq, store_id FROM review ORDER BY seq, store_id;
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | review | NULL | index | NULL | idx_seq_storeId | 18 | NULL | 1000 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+- type: ref, Extra: Using index
- type: range, Extra: Using where; Using index
- type: index, Extra: Using index
결과는 차례대로 보면 위와 같은데, 이는 2.1 과 동일한 결과이다.(즉, 이 경우에도 인덱스를 탄다)
2.3 store_id 만 조회
1) WHERE =
mysql> EXPLAIN SELECT store_id FROM review WHERE store_id = 0;
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | review | NULL | index | idx_seq_storeId | idx_seq_storeId | 18 | NULL | 1000 | 10.00 | Using where; Using index |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+type 은 index, Extra 에는 Using where; Using index 라고 되어있습니다.
- index : index 전체를 스캔했다는 의미
- Using where; Using index : 조회한 데이터를 where 조건에 맞게 필터링 했지만 인덱스만 접근해서 처리함(커버링 인덱스)
WHERE
2) WHERE >
mysql> EXPLAIN SELECT store_id FROM review WHERE store_id > 0;
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | review | NULL | index | idx_seq_storeId | idx_seq_storeId | 18 | NULL | 1000 | 33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+마찬가지로 type 은 index, Extra 에는 Using where; Using index 라고 되어있습니다.
- index : index 전체를 스캔했다는 의미
- Using where; Using index : 조회한 데이터를 where 조건에 맞게 필터링 했지만 인덱스만 접근해서 처리함(커버링 인덱스)
여기서 비슷한 형태의 쿼리를 살펴봤던 2.1 의 2번의 경우에는 type 이 range 였습니다.
하지만 여기서는 index 입니다.
즉, index 를 사용하긴 했지만 인덱스 풀스캔을 하며 성능이 2.1 의 2번보다는 떨어질 수 있다는 의미가 됩니다.
3) ORDER BY
mysql> EXPLAIN SELECT store_id FROM review ORDER BY store_id;
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | review | NULL | index | NULL | idx_seq_storeId | 18 | NULL | 1000 | 100.00 | Using index; Using filesort |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+type 은 index, Extra 에는 Using index; Using filesort 라고 되어있습니다.
- index : index 전체를 풀 스캔 했다는 의미
- Using index; Using filesort : 데이터는 인덱스를 통해 가져왔지만, 정렬은 MySQL 스토리지 엔진에서 했다는 의미
이 경우는 커버링 인덱스가 적용되었습니다.
하지만 인덱스만으로 정렬은 하지 못하고 추가로 스토리지 엔진에서 filesort 를 수행합니다
2.4 store_id, seq 조회(복합인덱스 순서 반대로)
1) WHERE =
mysql> EXPLAIN SELECT store_id, seq FROM review WHERE store_id = 0 AND seq = 0;
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | review | NULL | ref | idx_seq_storeId | idx_seq_storeId | 18 | const,const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+type 은 ref, Extra 에는 Using index 라고 되어있습니다.
- ref : where 절의 '=' 에 대한 연산을 index 를 이용했다는 의미
- Using index : 테이블에 접근하지 않고 인덱스에만 접근해서 처리함(커버링 인덱스)
이 경우는 순서를 반대로 입력해서 안될 것 같았지만, 옵티마이저가 인덱스를 타도록 최적화를 한 결과인 것 같습니다.
2) WHERE >
mysql> EXPLAIN SELECT seq, store_id FROM review WHERE store_id > 0 AND seq > 0;
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | review | NULL | range | idx_seq_storeId | idx_seq_storeId | 9 | NULL | 999 | 33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+type 은 range, Extra 에는 Using where; Using index 라고 되어있습니다.
- range : index 의 일정 범위에만 접근했다는 의미(where 절의 '>' 에 대한 연산을 index 를 이용했다는 의미)
- Using where : 데이터를 가져온 후 where 절의 조건을 이용하여 범위를 축소함
- Using index : 테이블에 접근하지 않고 인덱스에만 접근해서 처리함(커버링 인덱스)
이 경우도 인덱스를 탔고, range 로 실행되어 효율적으로 실행되었습니다.
마찬가지로 옵티마이저의 최적화 결과인 것 같습니다.
3) ORDER BY
mysql> EXPLAIN SELECT seq, store_id FROM review ORDER BY store_id, seq;
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | review | NULL | index | NULL | idx_seq_storeId | 18 | NULL | 1000 | 100.00 | Using index; Using filesort |
+----+-------------+--------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------------+type 은 index, Extra 에는 Using index; Using filesort 라고 되어있습니다.
- index : index 전체를 풀 스캔 했다는 의미
- Using index; Using filesort : 데이터는 인덱스를 통해 가져왔지만, 정렬은 MySQL 스토리지 엔진에서 했다는 의미
이 경우에도 커버링 인덱스가 적용되었습니다.
하지만 여기도 인덱스만으로 정렬은 하지 못하고 추가로 스토리지 엔진에서 filesort 를 수행합니다
3. 정리
위 내용들을 토대로 커버링 인덱스 여부를 표로 정리하면 다음과 같습니다.
(인덱스는 (seq, store_id) 순서로 선언)
| WHERE = | WHERE > | ORDER BY | |
|---|---|---|---|
| seq | O | O | O |
| seq, store_id | O | O | O |
| store_id | O | O | O |
| store_id, seq | O | O | O |
하지만 type, Extra 를 보며 성능이 어떨지를 보겠습니다.
| (A) WHERE = | (B) WHERE > | (C) ORDER BY | |
|---|---|---|---|
| (1) seq | - type: ref - Extra: Using index |
- type: range - Extra: Using where; Using index |
- type: index - Extra: Using index |
| (2) seq, store_id | - type: ref - Extra: Using index |
- type: range - Extra: Using where; Using index |
- type: index - Extra: Using index |
| (3) store_id | - type: index - Extra: Using where; Using index |
- type: index - Extra: Using where; Using index |
- type: index - Extra: Using index; Using filesort |
| (4) store_id, seq | - type: ref - Extra: Using index |
- type: range - Extra: Using where; Using index |
- type: index - Extra: Using index; Using filesort |
3.1 A 컬럼(WHERE =)
먼저 A1~4를 살펴보면 A3을 제외하고는 모두 type 이 ref 입니다.
index 만을 이용해서 등가 비교를 했다는것 인데요.
그런데 A3 은 같은 등가 비교 연산인데도 불구하고 type 이 index 로 인덱스 풀스캔이 일어났습니다.
이를 보면 복합 인덱스 컬럼 순서에 맞지않는 일부 컬럼만 조건에 넣는다면 다른 경우들 보다는 쿼리 성능이 떨어진다는 것을 알 수 있습니다.
3.2 B 컬럼(WHERE >)
범위 조건 비교를 테스트해본 B1~4를 살펴보면 3.1과 마찬가지로 B3을 제외하고는 모두 type 이 range 인 것을 알 수 있습니다.
이는 인덱스 레인지 스캔을 하여 조건에 따라 필요한 일부 데이터만 가져오도록 동작했다는 의미입니다.
만면에 B3 의 경우에는 3.1에서와 같이 인덱스 풀스캔이 일어난 것을 확인할 수 있습니다.
이 경우 또한 복합 인덱스 컬럼 순서에 맞지않는 일부 컬럼만 넣는다면 상대적으로 성능이 떨어질 수 있다는 것을 알 수 있습니다.
3.3 C 컬럼(ORDER BY)
C1~4(ORDER BY) 까지는 조건문에 의하여 필터링 되는 부분이 없으니 전체 데이터를 읽을 수 밖에 없습니다.
그래도 테이블을 풀스캔하지 않고, 커버링 인덱스가 적용되에 인덱스 풀 스캔이 일어나서 효율적으로 동작했다고 볼 수 있습니다.
여기서 C1, C2 의 경우에는 인덱스 순서에 맞게 했기 때문에 인덱스 순서대로 쿼리 결과를 도출할 수 있습니다.
하지만 C3, C4 는 인덱스 순서와 다르게 했기 때문에 filesort 가 추가된것을 확인할 수 있는데요.
이를 통해 ORDER BY 의 경우에는 인덱스 순서와 다르다면 filesort 가 추가되어 상대적으로 비효율 적이라는 것을 알 수 있습니다.
참고한 링크