MySQL – Loose Index Scan 동작 원리의 이해

MySQL 매뉴얼에서는 Loose Index Scan 방식을 Group by 최적화를 위한 기법이라고 설명하고 있습니다.
그런데 매뉴얼의 설명과 용어만으로는 Loose Index Scan의 동작 방식을 정확히 이해하기가 힘듭니다. 또한, Loose Index Scan 방식은 많은 제약 사항이 있습니다. 이 같은 이유를 저 나름대로 분석해보면, Loose Index Scan 방식은 Index를 이용해서 Group by를 최적화하기 위해 고안된 방식이라기보다는 “인덱스를 이용해서 Group 별 Min, Max 값을 효율적으로 처리하기 위해 고안된 방식“이기 때문입니다. 즉, 정의를 조금 더 축소해서 명확해서 해뒀으면 이해하기 수월했을 텐데, “Group by 최적화”와 같은 거창한(?) 정의를 해둔 탓에 이해하기도 힘들고 제약 사항도 많아진 것 같습니다.

 

 

그림으로 보는 Loose Index Scan의 원리


아래 그림을 보면, Loose Index Scan의 원리를 쉽게 파악할 수 있으리라 봅니다. 예를 들어, 인덱스를 c1+c2+c3 칼럼을 구성하고, c1으로 group by를 한 후에 min(c2), max(c2)를 구하거나, c1, c2 칼럼으로 group by를 한 후에 min(c3), max(c3)을 구한다고 가정해보죠. 이때는 인덱스의 특정 위치만 액세스해서 원하는 결과를 추출할 수 있습니다.(첫 번째 그림 참조)

그런데 group by 를 c1으로 한 후에 max(c3)을 구한다고 가정해보죠. 이때는 Loose Index Scan 방식으로 처리할 수 없습니다. 왜냐면 group by를 c1 칼럼으로 했으므로, max(c3) 값은 min/max 위치가 아닌 인덱스의 중간 부분에 위치할 수도 있기 때문입니다.(두 번째 그림 참조)

이 같은 이유로 인해, “min, max 칼럼은 group by 칼럼의 바로 뒤 칼럼이어야 한다”는 제약 조건이 있습니다. 원리를 이해하면 이것은 당연한 말이죠. 또한, 같은 이유로 sum(), count()와 같이 모든 인덱스 킷값을 액세스해야 하는 함수 또한 사용할 수 없습니다. 즉, Loose Index Scan은 오로지 min/max 값을 처리하기 위한 방식이라고 할 수 있습니다.

MySQL-Loose Index Scan 예제
MySQL-Tight Index Scan 예제

 

테스트 데이터 생성


실제 데이터를 가지고 테스트를 해보겠습니다.

drop table t1;

create table t1 (id integer primary key, c1 integer, c2 integer, c3 integer, dummy char(100));

CALL generate_series(1,1000000);

insert into t1 select series, mod(series,10), mod(series,100), mod(series,1000), 'dummy' from series_tmp;

create index t1_idx01 on t1(c1,c2,c3);

 

Loose Index Scan으로 수행되는 예제 #1


아래의 예제는 c1 칼럼으로 group by를 한 후에 min(c2), max(c2)를 구하는 예제입니다. Loose Index Scan 방식으로 수행될 때 Explain 결과를 보면 “Using index for group-by“라고 표시됩니다. 그리고 이때 Handler 사용 통계를 보면, Handler_read_key 값이 12로 나타납니다. 이 값을 통해 인덱스 키를 12번만 액세스했다는 것을 알 수 있습니다. c1 칼럼으로 group by를 하면 10건이므로 10건+(2번)만큼 인덱스 키를 액세스합니다. 그리고 max(c2)인 경우에는 이 값이 22입니다. 아마도 max 값 추출 시에는 min 값을 거쳐서 max 값으로 건너뛰는 방식을 사용하는 것으로 추정됩니다.

explain
select c1, min(c2) from t1 group by c1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | t1_idx01      | t1_idx01 | 10      | NULL |   10 |   100.00 | Using index for group-by |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+

—  MIN() 추출 시

flush status;
select c1, min(c2) from t1 group by c1;
show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 12    |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

— MAX() 추출 시

flush status;
select c1, max(c2) from t1 group by c1;
show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 22    |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

 

Loose Index Scan으로 수행되는 예제 #2


아래의 예제는 c1, c2 칼럼으로 group by를 한 후에 min(c3), max(c3)를 구하는 예제입니다. min(c3)을 추출할 때는 Handler_read_key 값이 102입니다. c1,c2 칼럼으로 group by를 하면 100건이므로, 100건+(2번)만큼 인덱스 키를 액세스한 것을 나타냅니다. 그리고 max(c3)인 경우에는 이 값이 202입니다.

explain
select c1, c2, min(c3) from t1 group by c1,c2;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | t1_idx01      | t1_idx01 | 15      | NULL |   96 |   100.00 | Using index for group-by |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+

—  MIN() 추출 시

flush status;
select c1, c2, min(c3) from t1 group by c1,c2;
show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 102   |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

— MAX() 추출 시

flush status;
select c1, c2, max(c3) from t1 group by c1,c2;
show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 202   |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

 

Loose Index Scan으로 수행되지 않는 예제


아래의 쿼리는 Index Loose Scan 방식으로 동작하지 않습니다. Explain 결과를 봐도 Using Index로 표시됩니다.
쿼리를 수행하면, Handler_read_key 값은 1이고 Handler_read_next 값은 1,000,000입니다. 즉, 인덱스를 1번 액세스한 후에 다음 킷값을 액세스하기 위한 next 콜을 100만 번 수행한 것입니다. 즉, Index Full Scan을 한 것을 의미합니다.

explain
select c1, max(c3) from t1 group by c1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | t1_idx01      | t1_idx01 | 15      | NULL | 991230 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+

수행 시간: 10 rows in set (0.26 sec)

flush status;
select c1, max(c3) from t1 group by c1;
show session status like 'handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 1       |
| Handler_read_key      | 1       |
| Handler_read_last     | 0       |
| Handler_read_next     | 1000000 |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 0       |
+-----------------------+---------+

 

위의 쿼리를 Loose Index Scan 방식으로 유도하려면? 


위의 쿼리를 Loose Index Scan 방식으로 유도하기 위해서 c1+c3으로 구성된 결합 인덱스를 생성하는 것은 매우 비효율적이고 비싼 방식입니다. 이런 경우에는 다음과 같이 약간의 쿼리 변경을 통해서 Loose Index Scan 방식을 이용한 튜닝을 수행할 수 있습니다. 즉, c1, c2 칼럼으로 group by를 수행함으로써 Loose Index Scan 방식으로 유도한 후에, 다시 c1 칼럼으로 group by를 수행하면 됩니다. (때에 따라 효과적인 튜닝 방법이 될 수도 있을 것 같습니다)
수행 시간은 이전보다 월등히 빨라집니다. (이전 수행 시간: 0.26초, 튜닝 후: 0.01초 미만)

explain
select c1, max(c3) from (select c1, c2, max(c3) c3 from t1 group by c1, c2) a group by c1;
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+---------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL     | NULL    | NULL |   96 |   100.00 | Using temporary; Using filesort |
|  2 | DERIVED     | t1         | NULL       | range | t1_idx01      | t1_idx01 | 10      | NULL |   96 |   100.00 | Using index for group-by        |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+---------------------------------+

수행 시간: 10 rows in set (0.00 sec)

flush status;
select c1, max(c3) from (select c1, c2, max(c3) c3 from t1 group by c1, c2) a group by c1;
show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 302   |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 10    |
| Handler_read_rnd_next | 112   |
+-----------------------+-------+
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s