My Books

My Slides

rss 아이콘 이미지

이번 시간에는 Covering Index 사용 시에 ICP가 동작하지 않는 문제점을 알아보고, 대안을 찾아보도록 하겠습니다.


Covering Index는 쿼리의 조건절과 SELECT 절에 해당하는 모든 칼럼을 포함하는 인덱스를 의미합니다. 따라서 Covering 인덱스를 이용하면 테이블을 액세스할 필요가 없습니다.


그리고 ICP는 이전 시간에 설명할 것과 같이 인덱스 필터링 기능입니다.


즉, ICP는 인덱스 필터링 후에 추출된 인덱스 키에 대해서는 테이블을 액세스합니다.


따라서 ORACLE과 PostgreSQL과 같은 DBMS는 Covering Index를 사용하는 것이 인덱스 필터링보다 빠릅니다. (테이블을 액세스하지 않으므로, 당연한 결과입니다)


그런데 MySQL은 특정한 상황에서는 Covering Index를 사용하는 것보다 ICP 방식이 더 빠릅니다.


이 같은 문제의 원인은 Covering Index를 사용하면 핸들러 레벨에서 필터링을 처리하지만, ICP 방식을 사용하면 스토리지 엔진 레벨에서 필터링하기 때문입니다.


더 근본적인 이유는 Covering Index에 대한 ICP 기능이 개발되지 않아서입니다.


이 문제는 이미 5.6 시절에 페르코나 엔지니어에 의해서 리포팅되고 (여기 참조), 버그로도 등록되었는데요.


5.7 버전에서도 아직 이 문제가 해결되지 않고 있습니다.


그럼 어떤 내용인지 테스트를 통해서 살펴보고, 이 문제를 해결할 방안을 찾아보도록 하겠습니다.



테스트 환경 구성


테스트 환경은 이전 시간에 사용한 환경을 이용합니다. (여기 참조)



ICP로 동작하는 경우


아래의 쿼리는 천만 건 중에서 인덱스 범위로 500만 건을 액세스한 후에 인덱스 필터링을 통해서 1건만 테이블을 액세스합니다. (극단적인 예제입니다)


이때 Extra 칼럼을 보면 Using index condition으로 표시되므로, 이 쿼리는 ICP 방식을 사용한 것을 알 수 있습니다. 이때, 수행속도는 0.6초입니다.

mysql> explain select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+
|  1 | SIMPLE      | t2    | NULL       | range | t2_idx01      | t2_idx01 | 10      | NULL | 4956606 |    10.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+

mysql> select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=1;
+----+------+------+-------+
| id | c1   | c2   | dummy |
+----+------+------+-------+
|  1 |    1 |    1 | dummy |
+----+------+------+-------+
1 row in set (0.60 sec)


Covering Index를 사용하는 경우


SELECT 절을 c1으로 변경한 후에는 ICP 방식으로 동작하지 않습니다. T2_IDX01 인덱스는 C1+C2 칼럼으로 구성된 결합인덱스이고, 쿼리는 C1, C2 칼럼만 존재하므로 Covering Index라고 할 수 있습니다.


그런데 이때 Extra 칼럼을 보면, Using Where; Using index라고 표시됩니다.


Using Where; Using index가 의미하는 것은 Index를 이용해서 쿼리 조건절에 해당하는 범위를 액세스한 후에, 필터링은 핸들러 레벨에서 수행한다는 것입니다.


이렇게 처리하다 보니, Disk 에서 InnoDB 버퍼로 로딩하는 블록 수는 동일하지만, ICP에 비해서 InnoDB 버퍼를 액세스하는 횟수가 상당히 많습니다. (테이블-1 참조)


결과적으로 수행 속도도 70% 가까이 느려집니다.

mysql> explain select c1 from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | t2_idx01      | t2_idx01 | 10      | NULL | 4956606 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
 
mysql> select c1 from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=1;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (1.03 sec)


테이블-1. ICP 방식과 Covering 인덱스 방식 간의 일량 비교


COUNT(*) 쿼리는 ICP로 동작하지 않는다.


문제는 또 있는데요. Covering Index를 이용한 COUNT(*) 쿼리도 ICP로 동작하지 않습니다.

mysql> explain select count(*) from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | t2_idx01      | t2_idx01 | 10      | NULL | 4956606 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+

mysql> select count(*) from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (1.08 sec)


개선 방안


그렇다면 어떡헤 해야 할까요?  MySQL 아키텍처의 특성 상, ICP가 훨씬 유리해 보이는데 말이죠.


제가 생각한 방법은 Derived Table을 이용하는 것입니다.


5.6 버전까지는 Unnamed Derived Table은 Merge가 되지 않습니다. 이 속성을 이용하면, ICP 방식으로 유도할 수 있습니다.

아래의 Explain 결과를 보면, 쿼리를 수정한 후에는 ICP 방식으로 동작하는 것을 알 수 있습니다.


이로 인해 처리 속도도 빨라졌습니다.


-- Version 5.6

mysql> explain select count(*) from (select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=100) a;
+----+-------------+------------+-------+---------------+----------+---------+------+---------+----------------------------------+
| id | select_type | table      | type  | possible_keys | key      | key_len | ref  | rows    | Extra                            |
+----+-------------+------------+-------+---------------+----------+---------+------+---------+----------------------------------+
|  1 | PRIMARY     |  | ALL   | NULL          | NULL     | NULL    | NULL | 4830516 | NULL                             |
|  2 | DERIVED     | t2         | range | t2_idx01      | t2_idx01 | 10      | NULL | 4830516 | Using index condition; Using MRR |
+----+-------------+------------+-------+---------------+----------+---------+------+---------+----------------------------------+

mysql> select count(*) from (select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=100) a;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.69 sec)


-- Version 5.7


그런데, 5.7 버전에서는 이 방법을 사용할 수가 없습니다. 왜냐면 5.7 버전부터는 Unnamed Derived Table은 외부 쿼리와 View Merging이 되기 때문입니다.


따라서 아래와 같이 쿼리를 수정해봐도 여전히 sing where; Using index 방식으로 동작합니다.


TEMPTABLE 알고리즘 속성을 가진 View (Named Derived Table)를 생성하면 되긴 합니다만, 이 방식은 사실 업무에 적용하기는 불가능해 보입니다. (단순 참고용입니다)

mysql> explain select count(*) from (select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=100) a;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | t2_idx01      | t2_idx01 | 10      | NULL | 4956606 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+

mysql> select count(*) from (select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=100) a;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (1.05 sec)

create or replace ALGORITHM=TEMPTABLE view t2_v 
as select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 5000000 and c2=100;

explain select count(*) from t2_v; 
+----+-------------+------------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                 |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+
|  1 | PRIMARY     |  | NULL       | ALL   | NULL          | NULL     | NULL    | NULL |  495660 |   100.00 | NULL                  |
|  2 | DERIVED     | t2         | NULL       | range | t2_idx01      | t2_idx01 | 10      | NULL | 4956606 |    10.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+

mysql> select count(*) from t2_v;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.62 sec)


-- Version 8


8 버전부터는 Unnamed Derived Table에 대한 NO_MERGE 힌트를 제공합니다. 테스트를 해보진 못했지만, 아마도 이 힌트를 사용하면 ICP로 동작할 것 같습니다. (이 힌트는 5.7에도 제공해주면 좋을 것 같습니다.)

explain select /*+ NO_MERGE(a) */ count(*) from (select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100) a;


저작자 표시 비영리 변경 금지
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
TAG ,


 

티스토리 툴바