My Books

My Slides

rss 아이콘 이미지

Search

'MySQL'에 해당되는 글 19건

  1. 2017.05.18 MySQL - Covering Index 사용 시에 ICP가 동작하지 않는 문제점과 해결 방안
  2. 2017.05.17 MySQL - Index Condition Pushdown (ICP) 동작 원리의 정확한 이해
  3. 2017.05.16 MySQL - Multi Range Read (MRR)와 관련된 매뉴얼 Bug 및 소스 디버깅을 통한 검증
  4. 2017.05.15 MySQL - Multi Buffer Pool의 문제점과 innodb_buffer_pool_instances 파라미터를 이용한 개선 방안
  5. 2017.05.10 MySQL - Derived Table을 이용해서 NL 조인 시에 발생하는 Block Nested-Loop (BNL)를 튜닝하는 방법
  6. 2017.05.10 MySQL – Block Nested-Loop (BNL)에 대한 이해
  7. 2017.05.10 MySQL – Loose Index Scan 동작 원리의 이해
  8. 2017.05.10 MySQL - IN 절과 EXISTS 절 간의 처리 방식 비교
  9. 2017.05.03 MySQL에서 Complex View 처리 시의 문제점 및 코딩 시 주의 사항
  10. 2017.05.02 MySQL에서 unnamed derived table 사용 시의 주의 사항
  11. 2017.04.28 한 서버에 MySQL 5.6 버전과 5.7 버전을 설치하는 방법
  12. 2017.04.21 STRAIGHT_JOIN 힌트를 이용한 MySQL Explain 읽는 순서 확인
  13. 2017.04.21 MySQL의 Query Cache는 쿼리 블록 단위로 캐싱이 가능할까?
  14. 2017.04.20 MySQL PK 칼럼 순서 설정에 따른 성능 비교
  15. 2017.04.20 MySQL의 Secondary Index에는 PK 칼럼이 포함될까?
  16. 2017.04.20 MySQL InnoDB에서 Loop Insert 속도를 10배 이상 빠르게 하는 아주 간단한 방법
  17. 2017.04.13 pt-query-digest 명령어를 이용한 MySQL Slow Query Log 분석 방법
  18. 2017.04.13 MySQL에서 PostgreSQL의 generate_series() 흉내내기
  19. 2017.04.13 MySQL의 Explain 결과를 보는 몇 가지 방법들

이번 시간에는 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 ICP, MySQL

이번에 살펴볼 내용은 MySQL 5.6 버전부터 지원하는 Index Condition Pushdown (이하 ICP)의 동작 원리입니다.

 

MariaDB 매뉴얼을 보면, (여기 참조) ICP의 동작 원리를 그림으로 매우 쉽게 설명하고 있습니다. 즉, 이 설명대로라면 ICP는 다른 DBMS에서 아주 오래전부터 지원하고 있는 "인덱스 필터링 기능"입니다.

 

그렇다면 MySQL은 다른 DBMS에는 너무나 당연히 지원했던 인덱스 필터링 기능을 5.6 버전에 와서야 지원을 시작했다는 걸까요? 아니면 ICP는 인덱스 필터링과는 다른, 특별한 기능이 있는 걸까요?

 

MySQL 매뉴얼을 보면 조금 헷갈린 설명이 있습니다. (매뉴얼 전문은 여기 참조)

"With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. "

 

매뉴얼에서 표현한 "storage engine" 이란 용어 때문에 ICP는 인덱스 필터링보다는 더 특별한 무언가가 있지 않을까? 하는 생각을 해봤습니다.

 

하지만 스토리지 엔진이란것도 따지고 보면, MySQL 서버 내에 있는 "디스크 + 처리 로직"의 개념일 뿐입니다. 즉, IO 처리를 위해서는 반드시 InnoDB 버퍼 풀로 디스크 블록을 로딩해야만 하는 구조입니다.

 

다시 말해, 오라클의 EXADATA와 같이 DB 서버와 스토리지 서버가 분리된 구조는 아니란 거죠.

 

따라서 MySQL의 ICP가 EXADATA에서 제공하는 Offloading 기능 (스토리지 박스 내에서 IO를 처리하는 방식)을 의미하는 것도 아닐 것 같습니다.

 

그렇다면 ICP는 대체 뭘까요?

 

일련의 테스트를 통해서 내린 결론은 "ICP는 인덱스 필터링 기능이다" 입니다.

 

그리고 테스트를 하는 과정에 MySQL은 인덱스 필터 처리를 매우 비효율적인 방식으로 수행했었다는 것도 알 수 있었습니다.

 

그럼 테스트 내용을 살펴보겠습니다.

 

테스트 환경 구성

 

테스트 환경은 MRR 테스트를 수행했던 환경을 이용합니다. (여기 참조)

 

테이블과 인덱스 정보는 다음과 같습니다.

+------------+------------+-------------+---------------+-------------+----------------+--------------+
| table_name | table_rows | data_length | DATA_SIZE(MB) | DATA_BLOCKS | INDEX_SIZE(MB) | INDEX_BLOCKS |
+------------+------------+-------------+---------------+-------------+----------------+--------------+
| t2         |    9913212 |  1427111936 |          1361 |       87104 |            204 |        13041 |
+------------+------------+-------------+---------------+-------------+----------------+--------------+

+---------------+------------+------------+----------------+--------------+
| database_name | table_name | index_name | INDEX_SIZE(MB) | INDEX_BLOCKS |
+---------------+------------+------------+----------------+--------------+
| mysql         | t2         | PRIMARY    |           1361 |        87104 |
| mysql         | t2         | t2_idx01   |            204 |        13041 |
+---------------+------------+------------+----------------+--------------+

 

테스트 시나리오 및 결과 요약

 

다음과 같은 3가지 경우에 대해서 각각 Disk IO 발생 시와 Memory IO 수행 시의 성능 통계 정보와 수행 속도를 비교했습니다.

 

1. 인덱스가 없는 경우
2. 인덱스 생성 후에 ICP 기능 사용 안 함
3. 인덱스 생성 후에 ICP 기능 사용 함

 

수행 결과는 다음과 같습니다.

 

 

위의 결과를 보면, ICP 방식이 NO ICP 방식에 비해서 10배 가까이 빠릅니다.

 

ICP 방식이 빠른 이유는 Innodb_data_reads 횟수가 NO ICP에 비해서 현저히 적기 때문입니다.

 

Innodb_data_reads는 Disk Read 횟수를 의미하는 성능 지표입니다.

 

즉, ICP 방식을 사용하면 Disk Read (및 Logical Read) 횟수가 현저히 줄어듭니다. 수치를 조금 더 자세히 분석해보겠습니다.

 

ICP 방식의 Disk IO 블록 수는 1,149블록입니다. 이 수치는 T2_IDX01 인덱스 블록의 1/10 정도 되는 수치입니다.

 

즉, 쿼리 수행을 위해서 인덱스의 1/10되는 범위를 액세스하면서 필터링을 수행했고, 필터링에 만족하는 1건만 테이블 블록을 액세스했다고 볼 수 있습니다.

 

반면, ICP 방식의 Disk IO 블록 수는 9,892블록입니다. 대략적으로 계산해서 9,892블록 중에서 8,700블록 정도는 테이블 블록을 위한 IO라고 할 수 있습니다.

 

테이블 블록이 87,104블록이므로 테이블 전체 블록의 1/10을 액세스한 셈입니다.

 

즉, 쿼리 수행을 위해서 인덱스의 1/10되는 범위를 액세스하지만, 인덱스 필터링을 수행하지는 못함에 따라서 테이블 블록을 액세스한 후에 필터링을 수행했다는 것을 알 수 있습니다.

 

다시 말해, ICP 방식을 사용하지 않으면, 인덱스에 필터 칼럼 (C2 칼럼)이 있어도 인덱스 필터링이 불가능합니다.

 

그럼 테스트 내용을 살펴보겠습니다.

 

INDEX가 없는 경우

 

테스트를 위해서 인덱스를 Drop 합니다.

alter table t2 drop index t2_idx01;

 

Explain 결과를 보면 type은 ALL이고 Extra는 Using where입니다. 즉 테이블을 Full Scan하면서 Where 조건을 이용해서 필터링한다는 의미입니다.

mysql> explain select * from t2 where c1 between 1 and 1000000 and c2=100;

+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9913212 |     1.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+

-- 1회차 수행 (DB 재기동 및 OS 캐시 클리어 직후)

# free && sync && echo 3 > /proc/sys/vm/drop_caches && free

mysql> select * from t2 where c1 between 1 and 1000000 and c2=100;
+-----+------+------+-------+
| id  | c1   | c2   | dummy |
+-----+------+------+-------+
| 100 |  100 |  100 | dummy |
+-----+------+------+-------+
1 row in set (10.70 sec)

--@@ 성능 지표 Diff 결과 

+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Handler_read_rnd_next            |   10000265 |
| Innodb_buffer_pool_pages_data    |      87016 |
| Innodb_buffer_pool_bytes_data    | 1425670144 |
| Innodb_buffer_pool_read_requests |    1284907 |
| Innodb_buffer_pool_reads         |        104 |
| Innodb_data_read                 | 1425670144 |
| Innodb_data_reads                |      87017 |
| Innodb_pages_read                |      87016 |
| Innodb_rows_read                 |   10000000 |
+----------------------------------+------------+

-- 2회차 수행 (Memory IO로 수행)

mysql> select * from t2 where c1 between 1 and 1000000 and c2=100;

+-----+------+------+-------+
| id  | c1   | c2   | dummy |
+-----+------+------+-------+
| 100 |  100 |  100 | dummy |
+-----+------+------+-------+
1 row in set (2.26 sec)

--@@ 성능 지표 Diff 결과 
+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Handler_read_key                 |          1 |
| Handler_read_rnd_next            |   10000208 |
| Innodb_buffer_pool_read_requests |    1198076 |
| Innodb_rows_read                 |   10000000 |
+----------------------------------+------------+

 

NO_ICP 힌트를 이용해서 ICP 기능을 비활성화

 

테스트를 위해서 인덱스를 생성합니다.

create index t2_idx01 on t2 (c1,c2);

 

NO_ICP 힌트를 적용하면 ICP 방식으로 동작하지 않습니다. 이로 인해 Extra 칼럼에는 Using where라고 표시됩니다.

 

Extra에 표시되는 Using where의 의미는 인덱스를 이용해서 처리 범위를 한정하긴 하지만, 필터 처리를 위해서는 테이블을 액세스한다는 것입니다.  

mysql> explain select /*+ NO_ICP(t2) */ * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-------------+
| 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 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-------------+

-- 1회차 수행 (DB 재기동 및 OS 캐시 클리어 직후)

# free && sync && echo 3 > /proc/sys/vm/drop_caches && free

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

--@@ 성능 지표 Diff 결과 

+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Handler_read_next                |     999998 |
| Handler_read_rnd_next            |        264 |
| Innodb_buffer_pool_pages_data    |       9891 |
| Innodb_buffer_pool_bytes_data    |  162054144 |
| Innodb_buffer_pool_read_requests |    3115486 |
| Innodb_buffer_pool_reads         |       1251 |
| Innodb_data_read                 |  162054144 | 
| Innodb_data_reads                |       9892 |
| Innodb_pages_read                |       9891 |
| Innodb_rows_read                 |     999999 |
+----------------------------------+------------+

-- 2회차 수행 (Memory IO로 수행)

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

--@@ 성능 지표 Diff 결과 

+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Handler_read_next                |     999998 |
| Handler_read_rnd_next            |        207 |
| Handler_write                    |         71 |
| Innodb_buffer_pool_read_requests |    3022034 |
| Innodb_rows_read                 |     999999 |
+----------------------------------+------------+

 

ICP 기능을 사용한 경우

 

NO_ICP 힌트를 제거하면 ICP 방식으로 수행됩니다. ICP 힌트는 별도로 존재하지 않는데요. 이는 옵티마이저가 ICP 방식을 우선적으로 검토하기 때문입니다

.

ICP 방식을 사용하면, Extra는 Using index condition으로 표시됩니다. 즉, 인덱스를 이용해서 조건을 검사한다는 뜻입니다.

 

다시 말해, 인덱스 핕터링이죠.

mysql> explain select * from t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+
| 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 | 2185996 |    10.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+

-- 1회차 수행 (DB 재기동 및 OS 캐시 클리어 직후)

# free && sync && echo 3 > /proc/sys/vm/drop_caches && free

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

--@@ 성능 지표 Diff 결과 

+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Handler_read_rnd_next            |        264 |
| Innodb_buffer_pool_pages_data    |       1148 |
| Innodb_buffer_pool_bytes_data    |   18808832 |
| Innodb_buffer_pool_read_requests |       2301 |
| Innodb_buffer_pool_reads         |       1148 |
| Innodb_data_read                 |   18808832 |
| Innodb_data_reads                |       1149 |
| Innodb_pages_read                |       1148 |
| Innodb_rows_read                 |          1 | 
+----------------------------------+------------+

-- 2회차 수행 (Memory IO로 수행)

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

--@@ 성능 지표 Diff 결과 )

+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Handler_read_key                 |          1 |
| Handler_read_next                |          1 |
| Handler_read_rnd_next            |        207 |
| Handler_write                    |         69 |
| Innodb_buffer_pool_read_requests |       1161 |
| Innodb_rows_read                 |          1 |
+----------------------------------+------------+

 

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


Multi Range Read (MRR)를 연구하던 중에 MySQL 매뉴얼에서 다음과 같은 내용을 접했습니다. 


Without MRR, an index scan covers all index tuples for the key_part1 range from 1000 up to 2000, regardless of the key_part2 value in these tuples. The scan does extra work to the extent that tuples in the range contain key_part2 values other than 10000.

With MRR, the scan is broken up into multiple ranges, each for a single value of key_part1 (1000, 1001, ... , 1999). Each of these scans need look only for tuples with key_part2 = 10000. If the index contains many tuples for which key_part2 is not 10000, MRR results in many fewer index tuples being read.


위의 내용대로라면 MRR 방식을 이용하면 BETWEEN 조건을 매우 효율적으로 처리할 수가 있습니다.


예를 들어 C1, C2 칼럼이 각각 유니크한 칼럼이고, 인덱스를 C1+C2로 생성했다고 가정해보죠.


매뉴얼대로라면, 다음과 같은 조건이 입력될 때 1개의 인덱스 블록만 액세스하면 됩니다. (놀라운 일이죠?)


WHERE C1 BETWEEN 1 AND 1000000 
AND   C2 = 100; 


그런데 아무리 봐도 상식적으로 이해가 가질 않습니다. 인덱스 리프 블록을 액세스하지 않고, 어떻게 해당 조건에 만족하는 1건만 추출할 수가 있다는 걸까요?


할 수 없이, 소스를 분석해보기로 했습니다. MRR 동작과 관련된 소스는 sql/handler.cc 입니다. 며칠간 소스 분석 및 디버깅을 수행한 제 결론은 다음과 같습니다. 


"매뉴얼 내용은 오류가 있다. MRR 방식을 사용하더라도 액세스하는 리프 블록 범위는 줄지 않는다. 단, 조건에 만족하는 레코드만 랜덤 버퍼에 입력된다."


즉, 대부분 레코드가 인덱스 레벨에서 필터링 되는 쿼리는 MRR 방식을 사용하더라도 처리 속도가 빨라지지 않습니다.


그럼 분석 내용을 살펴보겠습니다.


테스트 환경 구성

drop table t2;
create table t2 (id integer primary key, c1 integer, c2 integer, dummy char(100)) engine=InnoDB;

delimiter $$
drop procedure if exists insert_t2_proc$$

-- C1, C2 칼럼에 유니크한 값을 입력합니다. 

create procedure insert_t2_proc()
begin
   declare i int default 1;
   
   while (i <= 10000000) do
      insert into t2 values (i, i, i, 'dummy');
	  set i=i+1;
   end while;
end $$

delimiter ;

start transaction;

call insert_t2_proc(); 

commit;

-- C1 + C2 칼럼으로 구성된 결합 인덱스를 생성합니다. 

create index t2_idx01 on t2(c1, c2);

analyze table t2;

-- 테이블과 인덱스 크기를 확인합니다. 

select table_name,
       table_rows,
	   data_length,
       round(data_length/(1024*1024),0)  as 'DATA_SIZE(MB)',
	   round(data_length/16384,0)        as 'DATA_BLOCKS',
       round(index_length/(1024*1024),0) as 'INDEX_SIZE(MB)',
	   round(index_length/16384,0)       as 'INDEX_BLOCKS'
FROM  information_schema.TABLES
where table_name='t2';
+------------+------------+-------------+---------------+-------------+----------------+--------------+
| table_name | table_rows | data_length | DATA_SIZE(MB) | DATA_BLOCKS | INDEX_SIZE(MB) | INDEX_BLOCKS |
+------------+------------+-------------+---------------+-------------+----------------+--------------+
| t2         |    9913212 |  1427111936 |          1361 |       87104 |            204 |        13041 |
+------------+------------+-------------+---------------+-------------+----------------+--------------+

select database_name, 
       table_name, 
	   index_name, 
	   round(stat_value*@@innodb_page_size/(1024*1024),0) as 'INDEX_SIZE(MB)',
	   stat_value as 'INDEX_BLOCKS'
from   mysql.innodb_index_stats 
where  stat_name='size'
and    table_name='t2';
+---------------+------------+------------+----------------+--------------+
| database_name | table_name | index_name | INDEX_SIZE(MB) | INDEX_BLOCKS |
+---------------+------------+------------+----------------+--------------+
| mysql         | t2         | PRIMARY    |           1361 |        87104 |
| mysql         | t2         | t2_idx01   |            204 |        13041 |
+---------------+------------+------------+----------------+--------------+


MRR 방식을 사용하지 않는 경우


MRR 방식을 사용하지 않을 때의 수행 시간은 0.13초입니다. 이때 Innodb_buffer_pool_read_requests는 1,160번입니다. t2_idx01 인덱스 블록 수가 13,041 블록이고 액세스 범위가 1/10이므로, 대략 리프 블록의 1/10을 읽었다고 볼 수 있습니다.


explain select /*+ NO_MRR(t2) */ * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-----------------------+
| 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 /*+ NO_MRR(t2) */ * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+-----+------+------+-------+
| id  | c1   | c2   | dummy |
+-----+------+------+-------+
| 100 |  100 |  100 | dummy |
+-----+------+------+-------+
1 row in set (0.13 sec)

--@@ 성능 지표 diff value 확인 결과 결과 

+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Innodb_buffer_pool_read_ahead    |          0 |
| Innodb_buffer_pool_read_requests |       1160 |
| Innodb_buffer_pool_reads         |          0 |
| Innodb_data_read                 |          0 |
| Innodb_data_reads                |          0 |
+----------------------------------+------------+


MRR 방식을 사용할 경우


아래와 같이 MRR 방식을 사용하더라도 수행 속도와 IO 블록 수가 개선되지 않습니다. 이전과 거의 비슷한 속도이고 IO 블록 수도 거의 같습니다. 즉, MRR 방식을 사용하더라도 매뉴얼의 설명대로 동작하지 않습니다.


mysql> explain select /*+ MRR(t2) */ * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+----------------------------------+
| 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 | 2185996 |    10.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+----------------------------------+

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

--@@ 성능 지표 diff value 확인 결과 결과 

+----------------------------------+------------+
| variable_name                    | diff_value |
+----------------------------------+------------+
| Innodb_buffer_pool_read_ahead    |          0 |
| Innodb_buffer_pool_read_requests |       1159 |
| Innodb_buffer_pool_reads         |          0 |
| Innodb_data_read                 |          0 |
| Innodb_data_reads                |          0 |
+----------------------------------+------------+


소스 분석을 통한 랜덤 버퍼 디버깅


랜덤 버퍼에 레코드를 저장하는 함수는 dsmrr_fill_buffer() 입니다. 이 부분에 집중적으로 디버깅을 해보면 됩니다.


수행 결과, 아래의 쿼리 수행 시에 1건 (rec_cnt=[1])을 랜덤 버퍼에 저장합니다. 즉, Single Point Interval인 [100,100] 1건만 랜덤 버퍼에 저장합니다. (혹시 매뉴얼에서 설명하고자 했던 것이 이것이었을까요?)

mysql> select /*+ MRR(t2) */ * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000 and c2=100;

[DEBUG-MRR] multi_range_read_init [Tue May 16 18:22:18 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [293086112] rec_cnt=[1] [Tue May 16 18:22:20 2017]


비교를 위해서, C2 조건을 빼볼까요? 그러면 MRR 방식으로 처리해야 할 레코드가 100만 건이 됩니다. 현재 랜덤 버퍼의 크기는 256KB이고 해당 공간에는 65,536개의 정수(4바이트)를 저장할 수 있습니다. 따라서 100만 건을 처리하기 위해 dsmrr_fill_buffer() 함수를 16번 호출합니다.

mysql> explain select /*+ MRR(t2) */ * from  t2 FORCE INDEX(t2_idx01) where c1 between 1 and 1000000;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+----------------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | t2_idx01      | t2_idx01 | 5       | NULL | 2186450 |   100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+----------------------------------+

[dsmrr_init]
[DEBUG-MRR] multi_range_read_init [Tue May 16 18:28:28 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1985640849] rec_cnt=[65536] [Tue May 16 18:28:29 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [217748122]  rec_cnt=[65536] [Tue May 16 18:28:30 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1078195231] rec_cnt=[65536] [Tue May 16 18:28:32 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1579427864] rec_cnt=[65536] [Tue May 16 18:28:34 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1580987960] rec_cnt=[65536] [Tue May 16 18:28:35 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [205242914]  rec_cnt=[65536] [Tue May 16 18:28:37 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [319501994]  rec_cnt=[65536] [Tue May 16 18:28:38 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1685075907] rec_cnt=[65536] [Tue May 16 18:28:40 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [19939101]   rec_cnt=[65536] [Tue May 16 18:28:41 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [542792330]  rec_cnt=[65536] [Tue May 16 18:28:43 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1949408410] rec_cnt=[65536] [Tue May 16 18:28:44 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1637581751] rec_cnt=[65536] [Tue May 16 18:28:46 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1142998578] rec_cnt=[65536] [Tue May 16 18:28:47 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [2038101548] rec_cnt=[65536] [Tue May 16 18:28:49 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [1197209131] rec_cnt=[65536] [Tue May 16 18:28:50 2017]
[DEBUG-MRR] dsmrr_fill_buffer() end of while [368741656]  rec_cnt=[16960] [Tue May 16 18:28:51 2017]



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

 

아래의 테스트 결과는 완전히 잘못됐습니다. 혼란을 드려서 죄송합니다. 디버그 옵션을 켜고 테스트를 한 탓에 속도가 너무 느리게 나오기도 했고, 테스트 케이스 간의 성능 비교도 부정확했습니다.

 

디버그 옵션을 끄고 다시 컴파일한 후에 테스트를 해보니, 아래의 테스트 케이스에서는 Mutl Buffer Pool인 경우가 가장 빠릅니다.

 

DISK IO  

  •  256 MB                  : (6.86 sec)
  •  2 GB + Multi Buffer  : (6.64 sec)
  •  2 GB + Single Buffer : (6.68 sec) 

Memory IO

  •  256 MB                  : (3.27 sec)
  •  2 GB + Multi Buffer  : (2.32 sec)
  •  2 GB + Single Buffer : (2.34 sec)

 

연구 시에, 잘못된 테스트 환경이 가져오는 위험성을 보여주기 위해서 아래의 내용은 그대로 두기로 했습니다. 테스트하면서 버퍼 IO 속도가 너무 느리다는 점을 간과한 저의 실수입니다.(부끄럽네요. ㅠㅠ)

 

-- 아래 --

 

1GB 테이블에 대한 Full Scan을 빠르게 처리하기 위해서 innodb 버퍼 풀을 128MB(기본 설정값)에서 2GB로 변경했습니다. 과연 빨라졌을까요?

 

이상하게도 2배 가까이 느려졌습니다. 왜 이런 현상이 발생했을까요?

 

Oracle이나 PostgreSQL 처럼 대량의 스캔으로부터 버퍼 캐시를 보호하는 전략이 있다면 처리 속도가 기존과 비슷해야 할 것이고, 만일 그런 전략이 없다면, 처리 속도가 더 빨라져야함에도 불구하고 매우 느려졌습니다.

 

그 이유는 Multi Buffer Pool (이하 멀티 버퍼 풀) 사용 시에 발생하는 성능 문제 때문입니다.

 

멀티 버퍼 풀이란 버퍼 풀을 여러 개의 서브 풀로 나눠서 관리하는 것을 말하며, 버퍼 풀 크기를 1GB 이상으로 설정하면 멀티 버퍼 풀로 동작합니다.

 

하나의 버퍼 풀을 논리적 또는 물리적으로 나눠서 관리하는 것은 트랜잭션의 동시성을 높이기 위해서 필요한 기법임에는 분명합니다. 공유 메모리를 액세스할 때의 경합을 감소하기 위한 기본 원칙은 병목 지점을 분산하거나 더 많은 래치 또는 LW 락을 제공하는 것이니까요.

 

그런데 멀티 버퍼 풀은 매우 큰 테이블을 스캔할 때는 매우 성능이 떨어집니다. 2016년 논문에서도 이 문제점을 지적하고 있습니다.

 

즉, 트랜잭션의 동시성을 높인다는 목적은 달성했지만 대용량 처리를 위한 최적화는 아직 덜 된 상태로 보입니다.

 

이 문제를 개선하기 위해서는 innodb_buffer_pool_instances 파라미터를 1로 설정하면 됩니다.

 

해당 파라미터는 서브 풀 개수를 지정하는 파라미터이며, 1로 설정하면 서브 풀을 사용하지 않게 됩니다. 즉, 멀티 버퍼 풀 기능을 비활성화합니다. 서브 풀을 사용하지 않으면 대용량 테이블 스캔 속도는 빨라집니다. 반면 동시성은 떨어집니다. 따라서 MySQL 5.7 버전까지는 DBA가 업무 환경에 맞게 파라미터를 적절히 설정해야 합니다. (사실 쉽지 않은 일이죠. 대부분 Hybrid용으로 사용할테니까요. 결국 당연한 말이지만 우선 순위에 따라 결정을 해야할 겁니다. MySQL 8 버전에서는 이 문제가 개선되었으면 합니다.)

 

이와 관련된 또 다른 문제점은 버퍼 풀을 크게 설정하는 것이 반드시 대용량 스캔에 유리한 것은 아니라는 점입니다. 버퍼 풀을 2GB로 설정하고, 서브 풀을 비활성화 했음에도 스캔 속도는 기존보다는 20% 가까이 느립니다. 이 같은 현상은 일반적으로 해시 테이블 버킷 수의 부족, 또는 최적화되지 않은 해시 함수로 인해 발생하는 해시 충돌의 문제 때문입니다. 이 같은 현상은 테스트 환경에 따라서 다를 수 있습니다만, "메모리 IO가 Disk IO보다 항상 빠르다"는 일반 상식과는 다른 결과가 나올 수도 있다는 점을 유의해야 합니다.

 

그럼 테스트한 내용을 살펴보겠습니다. 

 

테스트 환경 구성

 

테스트 환경은 MySQL 5.7입니다.

 

show variables like "version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| version                 | 5.7.18-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+

drop table t1;
create table t1 (c1 integer primary key, c2 integer, dummy char(100)) engine=InnoDB;

delimiter $$
drop procedure if exists insert_t1_proc$$

-- 천만 건을 입력합니다. (참고로, 이 프로시저는 MRR 이상 현상을 분석하기 위해 사용한 것입니다. MRR 이상 현상을 분석하다가 이 문제를 발견하게 됐습니다)

create procedure insert_t1_proc()
begin
   declare i int default 1;
   declare j int default 1;
   
   while (i <= 10000) do
      while (j <= 1000) do
	     insert into t1 values (j+(1000*(i-1)), i+(10000*(j-1)), 'dummy');
	     set j=j+1;
	  end while;
	  set i=i+1;
	  set j=1;
   end while;
end $$

delimiter ;

start transaction;

call insert_t1_proc(); 

commit;

create index t1_c2_idx on t1(c2);

analyze table t1;

-- 테이블 크기는 1.3GB 정도이고, 블록 수는 8만4천 블록 정도입니다. (MySQL 블록 크기는 16KB)

select table_name,
       table_rows,
	   data_length,
       round(data_length/(1024*1024),0)  as 'DATA_SIZE(MB)',
	   round(data_length/16384,0)         as 'DATA_BLOCKS',
       round(index_length/(1024*1024),0) as 'INDEX_SIZE(MB)'
FROM  information_schema.TABLES
where table_name='t1';
+------------+------------+-------------+----------------+-------------+-----------------+
| table_name | table_rows | data_length | DATA_SIZE(MB) | DATA_BLOCKS | INDEX_SIZE(MB) |
+------------+------------+-------------+----------------+-------------+-----------------+
| t1         |    9916130 |  1378877440 |           1315 |       84160 |             160 |
+------------+------------+-------------+----------------+-------------+-----------------+

 

 

 

Pre-Load 관련 파라미터 OFF로 설정

 

MySQL은 Shutdown 전에 버퍼 풀의 25% (기본 설정값)에 해당하는 버퍼의 메타 정보를 ib_buffer_pool 파일에 기록합니다. 그런 후에 DB를 재기동하면 해당 내용을 이용해서 백그라운드로 로딩 작업을 수행합니다. 자세한 내용은 여기를 참고하세요. (MySQL 관련해서 다양한 정보를 제공하는 블로그입니다) 이 기능은 운영 시에는 좋은 기능이지만 정확한 테스트를 위해서 해당 파라미터를 OFF로 설정합니다.

[mysqld]
innodb_buffer_pool_dump_at_shutdown=OFF
innodb_buffer_pool_load_at_startup=OFF

 

Innodb 버퍼 풀이 128MB일 때의 스캔 속도

 

explain 결과를 보면, 아래의 쿼리가 테이블 Full Scan으로 동작하는 것을 알 수 있습니다.

mysql> explain select count(*) from t1 where dummy='dummy';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9916130 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+

 

정확한 테스트를 위해서 매 테스트마다 DB 재기동 및 OS 버퍼를 클리어합니다.

-- db stop
-- disk cache clear
# free && sync && echo 3 > /proc/sys/vm/drop_caches && free
-- db start 

-- 버퍼 풀 크기는 128MB이고 버퍼 풀 개수는 1개임을 확인합니다.

mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+

mysql> select @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              1 |
+--------------------------------+

 

-- 1회차 수행 속도

 

1회차 수행 속도는 64초입니다.

mysql> select count(*) from t1 where dummy='dummy';
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1 min 4.06 sec)

 

이때 버퍼 모니터링을 하면, Pages read (DISK IO 블록 수)는 84,418 블록임을 알 수 있습니다.

mysql> SHOW ENGINE INNODB STATUS\G
BUFFER POOL AND MEMORY
----------------------
Pages read 84418, created 35, written 37

 

-- 2회차 수행 속도

 

2회차 수행 속도는 57초입니다. Disk IO 블록 수는 79,090 (163508 - 84418)입니다. Disk IO가 조금 줄어서 약간 더 빨라진 것을 알 수 있습니다. (참고로 3회차 수행 속도도 이와 비슷합니다)

mysql> select count(*) from t1 where dummy='dummy';
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (56.77 sec)
mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Pages read 163508, created 35, written 37

 

 

Innodb 버퍼 풀을 2GB로 변경한 후의 스캔 속도

 

버퍼 풀 크기를 2GB로 증가합니다.

[mysqld]
innodb_buffer_pool_size = 2048M

-- db stop
-- disk cache clear
# free && sync && echo 3 > /proc/sys/vm/drop_caches && free
-- db start 

버퍼 풀이 2GB이고, 이때 서브 풀이 8개로 설정된 것을 알 수 있습니다.

mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                2147483648 |
+---------------------------+

mysql> select @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              8 |
+--------------------------------+

 

-- 1회차 수행 속도

 

1회차 수행 속도는 125초 입니다. 기존 대비 2배 이상 느립니다. 이상하죠?

mysql>  select count(*) from t1 where dummy='dummy';
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2 min 5.00 sec)

이때, 버퍼 풀을 모니터링해보면 Disk IO 블록 수는 84,418이고 이는 이전과 동일합니다. 단, 8개의 풀에 대략 만 블록씩 나눠서 로딩된 것을 알 수 있습니다.

mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Pages read 84418, created 35, written 37
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Pages read 10597, created 0, written 2
---BUFFER POOL 1
Pages read 10566, created 0, written 0
---BUFFER POOL 2
Pages read 10568, created 0, written 0
---BUFFER POOL 3
Pages read 10349, created 0, written 0
---BUFFER POOL 4
Pages read 10556, created 0, written 0
---BUFFER POOL 5
Pages read 10625, created 0, written 0
---BUFFER POOL 6
Pages read 10566, created 35, written 35
---BUFFER POOL 7
Pages read 10591, created 0, written 0

 

-- 2회차 수행 속도

 

아래의 버퍼 모니터링 결과를 보면, Pages read 수치가 이전과 동일한 84,418입니다. 즉, 2회차 수행은 모두 Memory IO로 처리됐습니다. 이를 통해 MySQL은 대량의 스캔으로부터 버퍼를 보호하는 전략은 없는 것을 알 수 있습니다. 그런데 수행 속도가 그리 빠르지 않습니다. Memory IO로 처리했음에도 처리 속도는 기존과 거의 비슷합니다.

select count(*) from t1 where dummy='dummy';
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2 min 4.93 sec)
mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Pages read 84418, created 35, written 37

 

innodb_buffer_pool_instances 파라미터를 1로 변경한 후의 테스트 결과

 

서브 풀을 사용하지 않도록 innodb_buffer_pool_instances 파라미터를 1로 설정합니다.

[mysqld]
innodb_buffer_pool_instances = 1

-- db stop
-- disk cache clear
# free && sync && echo 3 > /proc/sys/vm/drop_caches && free
-- db start

 

파라미터 설정 효과 인해 버퍼 풀이 1개로 설정된 것을 알 수 있습니다. 

mysql> select @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              1 |
+--------------------------------+

 

-- 1회차 수행 속도

 

수행 속도도 제법 빨라졌습니다. 하지만 여전히 버퍼 크기가 128MB일때보다는 느립니다.

mysql>  select count(*) from t1 where dummy='dummy';
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1 min 19.56 sec)
mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Pages read 84418, created 35, written 37

 

-- 2회차 수행 속도

 

2회차 수행은 모두 Memory IO로 처리됐습니다. 덕분에 기존 대비 10초 정도 빨라졌지만, 이 속도 역시 버퍼 풀이 128 MB일때에 비해서는 조금 느립니다.

 

mysql> select count(*) from t1 where dummy='dummy';
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1 min 9.68 sec)
mysql> SHOW ENGINE INNODB STATUS\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Pages read 84418, created 35, written 37

 

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

이번 시간에 살펴볼 내용은 Derived Table을 이용해서 Nested Loop 조인 시에 발생하는 BNL에 대한 튜닝 방법입니다. 이 내용은 이전 포스트인 “MySQL – Block Nested-Loop (BNL)에 대한 이해“와 연결된 내용입니다.

 

이번 포스팅에서 다룰 내용이 길지 않음에도 별도의 포스트로 분리한 이유는, 나름 참신한 방법으로 Nested Loop 시에 발생하는 BNL을 튜닝할 수 있는 방법이기 때문입니다. 결론부터 말하면, Derived Table의 특징을 이용하면 인덱스를 생성하지 않고도 BNL에 대한 성능 튜닝이 가능합니다. 왜냐면 TEMPTABLE 알고리즘을 이용하는 Derived Table은 조인 칼럼에 대해서 내부적으로 임시 인덱스를 생성하기 때문입니다. 이 원리를 이용하면 매우 극적인 튜닝이 가능합니다. 예제를 통해 살펴보겠습니다.

 

앞선 테스트에서 이어서 테스트를 진행하므로, 일단 인덱스를 drop 합니다.

alter table t2 drop index t2_c1_idx;

그리고 다음과 같이 TEMPTABLE 알고리즘을 이용하는 뷰 (named Derived Table)를 생성합니다. 뷰를 생성하는 이유는 5.7부터는 aggregate가 없는 인라인 뷰 (unnamed Derived Table)를 Merge 방식을 사용하므로 TEMPTABLE 방식으로 유도되지 않기 때문입니다. (이 내용은 여기를 참고하세요)

create or replace ALGORITHM=TEMPTABLE view t2_v 
as select * from t2;

 

뷰를 이용한 Explain 결과를 보면, t2 테이블을 스캔한 후에 c1 칼럼에 대해서 내부적인 임시 인덱스 (auto_key0) 생성한 후, 해당 키를 이용해서 조인을 수행한 것을 알 수 있습니다. 그리고 수행 속도를 보면, BNL 방식을 사용할 때보다 월등히 빨라졌습니다. 거의 인덱스를 생성할 때의 수행 속도와 비슷할 정도입니다. 뷰를 생성하는 번거로움은 있지만, 성능 개선 효율을 보면 생각해 볼 만한 방식인 것은 분명합니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
explain
select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 50;
+----+-------------+------------+------------+------+---------------+-------------+---------+------------+-------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref        | rows  | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+-------------+---------+------------+-------+----------+-------------+
|  1 | PRIMARY     | a          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL       |  4956 |    11.11 | Using where |
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 5       | mysql.a.c1 |    10 |   100.00 | NULL        |
|  2 | DERIVED     | t2         | NULL       | ALL  | NULL          | NULL        | NULL    | NULL       | 49720 |   100.00 | NULL        |
+----+-------------+------------+------------+------+---------------+-------------+---------+------------+-------+----------+-------------+
 
set profiling=1;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 50;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 500;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 5000;
 
show profiles;
+----------+------------+---------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                       |
+----------+------------+---------------------------------------------------------------------------------------------+
|        1 | 0.06712000 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 50   |
|        2 | 0.06936275 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 500  |
|        3 | 0.11768975 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2_v b ON a.c1=b.c1 and a.c1 between 1 and 5000 |
+----------+------------+---------------------------------------------------------------------------------------------+

 

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

MySQL – Block Nested-Loop (BNL)에 대한 이해

MySQL 2017.05.10 15:51 Posted by 시연아카데미

이번 시간에 살펴볼 내용은 Block Nested-Loop (이하 BNL)입니다.

 

MySQL이 BNL을 제공하는 이유는 Nested Loop 조인만 지원하는 한계점을 보완하기 위해서입니다. 예를 들어, 조인 칼럼 인덱스가 없다고 가정해보죠. 이 경우, ORACLE과 PostgreSQL과 같은 DBMS는 해시 조인 또는 머지 조인을 사용합니다. 그런데 Nested Loop 조인만 지원하는 MySQL은 이 문제를 어떻게 처리할까요?

 

만일 BNL 방식이 없다면, Driving 테이블의 건수만큼 Inner 테이블을 스캔 (또는 Index Full Scan)해야만 합니다. 이것은 어마어마한 성능 저하를 초래합니다. 이 문제를 어느 정도 완화하기 위해서 MySQL은 BNL 방식을 고안했습니다.

 

BNL 방식은 프로세스 내에 별도의 버퍼 (이를 조인 버퍼라고 합니다)에 Driving 테이블의 레코드를 저장한 후에 Inner 테이블을 스캔하면서 조인 버퍼를 탐색하는 방식입니다. 따라서 BNL 방식을 사용하면 BNL 방식을 사용하지 않는 것에 비해서는 빠릅니다. 하지만 BNL 방식은 Nested Loop 조인의 한계를 개선하기 위한 차선책일 뿐 근본적인 해결책은 아닙니다. Sort Merge 조인이나 Hash 조인에 비해서 턱없이 느린 방식이기 때문입니다. 이 때문에 MySQL에서도 가능한 빨리 Sort Merge나 Hash 조인을 지원하면 좋겠지만, MySQL 8에서도 아직 지원 예정은 없는 것 같습니다.

 

그럼 예제를 통해 살펴보겠습니다. 참고로, Sort Merge, Hash 조인의 성능을 간접적으로 비교하기 위해서 PostgreSQL과 ORACLE에서 테스트한 결과도 첨부합니다.

 

테스트 환경 구성


 

테스트 환경은 MySQL 5.7입니다.

show variables like "version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| version                 | 5.7.18-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+

drop table t1;
drop table t2;

create table t1 (c1 integer, c2 integer, dummy char(100)) engine=InnoDB;
create table t2 (c1 integer, c2 integer, dummy char(100)) engine=InnoDB;

call generate_series(1,50000);

insert into t1 select series, series, 'dummy' from  series_tmp order by series limit 5000;
insert into t2 select mod(series,5000), series, 'dummy' from  series_tmp order by series limit 50000;

 

BNL 성능 테스트


 

아래의 Explain 결과처럼 Extra 칼럼에 “Using Join buffer (Block Nested Loop)“가 표시되면 BNL 방식으로 수행된 것입니다. BNL 방식은 조인 칼럼에 적절한 인덱스가 없을 때 수행될 수 있는 방식이므로, BNL 방식으로 수행될 때의 type은 ALL (Table Full Scan) 또는 Index (Index Full Scan)입니다.

 

그리고 Driving 테이블의 레코드를 조인 버퍼에 저장하기 때문에 조인 버퍼의 크기가 매우 중요합니다. 만일 Driving 테이블의 레코드를 조인 버퍼에 모두 저장할 수 없다면, Inner 테이블을 여러 번 스캔해야 하는 부하가 발생합니다. 참고로 아래의 예제는 모두 조인 버퍼 내에서 수행됐으므로, T2 테이블을 1회만 스캔했습니다.

 

따라서 3개의 쿼리 간의 성능 차이는 조인 버퍼를 탐색하는 시간이라고 해도 무방하며, 조인 버퍼 내에 레코드가 많을수록 선형적으로 처리 성능이 저하되는 것을 알 수 있습니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
explain
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  4956 |    11.11 | Using where                                        |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49720 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
 
set profiling=1;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000;
 
show profiles;
+----------+-------------+-------------------------------------------------------------------------------------------+
| Query_ID | Duration    | Query                                                                                     |
+----------+-------------+-------------------------------------------------------------------------------------------+
|        1 |  0.17276250 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50   |
|        2 |  1.57978475 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500  |
|        3 | 15.68152675 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000 |
+----------+-------------+-------------------------------------------------------------------------------------------+

 

BNL DISABLE 후의 성능 테스트


 

그렇다면 과면 BNL은 정말 효과가 있는 방식일까요?

다음과 같이, 5.7에서부터 제공하는 NO_BNL 힌트를 이용해서 수행 속도를 비교해보겠습니다. 아래의 Explain 결과를 보면 Extra 칼럼에 BNL이 나타나지 않습니다. 즉, NO_BNL 힌트의 영향때문에 BNL 방식으로 수행되지 않은 것인데요. 처리 결과를 보면 알 수 있듯이, BNL 방식에 비해서 5배 이상 느립니다. 이로써 BNL 방식은 어느 정도의 튜닝 효과가 있다고 할 수 있습니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
explain
select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  4956 |    11.11 | Using where |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49720 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
 
set profiling=1;
 
select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
 
select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500;
 
select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000;
 
show profiles;
 
+----------+-------------+------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration    | Query                                                                                                            |
+----------+-------------+------------------------------------------------------------------------------------------------------------------+
|        1 |  0.83281350 | select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50         |
|        2 |  8.31119800 | select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500        |
|        3 | 85.36058425 | select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000       |
+----------+-------------+------------------------------------------------------------------------------------------------------------------+

 

인덱스 생성 후의 성능 테스트


 

그렇다면 인덱스를 생성하면 어떻게 될까요?

당연한 결과지만, 너무나도 빠르게 처리됩니다. 10초 이상 수행된 3번째 쿼리가 인덱스 생성 후에는 0.1초 만에 수행되므로 100배 이상 빨라진 것이죠. 하지만 운영 단계에서 인덱스를 생성하는 것은 매우 부담스러운 작업입니다. 입력 속도 저하, 스토리지 사용량 증가, 다른 쿼리에 대한 영향도 분석 필요 등등. 매우 많은 고려가 필요합니다. 물론 설계 상의 실수로 인해, 반드시 생성해야 할 칼럼에 인덱스를 생성하지 않았던 것이라면 이런 고민을 덜 하겠지만, NDV가 좋지 않은 칼럼에 대한 인덱스 생성은 많은 고려가 필요한 게 사실이니까요. 그렇다면 인덱스를 생성할 수 없다면 어떻게 튜닝을 해야 할까요? 이 부분은 여기를 참고하세요.

 

— 인덱스 생성

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create index t2_c1_idx on t2(c1);
 
explain
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL      | NULL    | NULL       | 4956 |    11.11 | Using where |
|  1 | SIMPLE      | b     | NULL       | ref  | t2_c1_idx     | t2_c1_idx | 5       | mysql.a.c1 |    9 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+
 
set profiling=1;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500;
 
select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000;
 
show profiles;
+----------+------------+-------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                     |
+----------+------------+-------------------------------------------------------------------------------------------+
|        1 | 0.00297075 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50   |
|        2 | 0.01187425 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500  |
|        3 | 0.12604875 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000 |
+----------+------------+-------------------------------------------------------------------------------------------+

 

PostgreSQL 수행 내역


 

PostgreSQL은 해시 조인과 머지 조인을 지원합니다. 따라서 해시 조인과 머지 조인의 수행 결과를 보면 모두 0.1초 이내에 수행됩니다. NL 조인의 수행 속도는 58초 정도인데요. 이 속도를 MySQL과 비교해보면 BNL 방식을 적용한 것보다는 느리고 NO BNL 방식보다는 조금 빠른 정도입니다.

 

— 해시 조인

 

1
2
3
4
5
6
7
8
9
10
11
12
explain analyze select * From t1 a, t2 b where a.c1=b.c1;
 
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=122.23..5444.27 rows=121631 width=824) (actual time=1.662..26.778 rows=49990 loops=1)
   Hash Cond: (b.c1 = a.c1)
   ->  Seq Scan on t2 b  (cost=0.00..1018.34 rows=15534 width=412) (actual time=0.009..7.129 rows=50000 loops=1)
   ->  Hash  (cost=102.66..102.66 rows=1566 width=412) (actual time=1.643..1.643 rows=5000 loops=1)
         Buckets: 8192 (originally 2048)  Batches: 1 (originally 1)  Memory Usage: 753kB
         ->  Seq Scan on t1 a  (cost=0.00..102.66 rows=1566 width=412) (actual time=0.004..0.754 rows=5000 loops=1)
 Planning time: 0.073 ms
 Execution time: 30.958 ms

 

— 머지 조인

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
set enable_hashjoin=off;
explain analyze select * From t1 a, t2 b where a.c1=b.c1;
                                                     QUERY PLAN
 
-----------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=5208.01..7079.14 rows=121631 width=824) (actual time=40.566..81.406 rows=49990 loops=1)
   Merge Cond: (a.c1 = b.c1)
   ->  Sort  (cost=185.76..189.67 rows=1566 width=412) (actual time=1.420..2.105 rows=5000 loops=1)
         Sort Key: a.c1
         Sort Method: quicksort  Memory: 896kB
         ->  Seq Scan on t1 a  (cost=0.00..102.66 rows=1566 width=412) (actual time=0.007..0.647 rows=5000 loops=1)
   ->  Materialize  (cost=5022.25..5099.92 rows=15534 width=412) (actual time=39.133..58.476 rows=50000 loops=1)
         ->  Sort  (cost=5022.25..5061.09 rows=15534 width=412) (actual time=39.130..48.962 rows=50000 loops=1)
               Sort Key: b.c1
               Sort Method: external merge  Disk: 5816kB
               ->  Seq Scan on t2 b  (cost=0.00..1018.34 rows=15534 width=412) (actual time=0.005..7.529 rows=50000 loops=1)
 Planning time: 0.095 ms
 Execution time: 88.168 ms

 

— NL 조인

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
set enable_mergejoin=off;
explain (analyze, buffers) select * From t1 a, t2 b where a.c1=b.c1;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..366018.58 rows=121631 width=824) (actual time=0.014..58100.334 rows=49990 loops=1)
   Join Filter: (a.c1 = b.c1)
   Rows Removed by Join Filter: 249950010
   Buffers: shared hit=950
   ->  Seq Scan on t2 b  (cost=0.00..1018.34 rows=15534 width=412) (actual time=0.007..40.626 rows=50000 loops=1)
         Buffers: shared hit=863
   ->  Materialize  (cost=0.00..110.49 rows=1566 width=412) (actual time=0.000..0.477 rows=5000 loops=50000)
         Buffers: shared hit=87
         ->  Seq Scan on t1 a  (cost=0.00..102.66 rows=1566 width=412) (actual time=0.003..0.739 rows=5000 loops=1)
               Buffers: shared hit=87
 Planning time: 0.043 ms
 Execution time: 58114.032 ms

 

ORACLE 수행 내역


 

ORACLE 수행 내역을 보면 해시 조인과 머지 조인 모두 0.1초 내로 수행됩니다. 뿐만 아니라 NL 조인도 12초 정도에 수행됩니다. 이 속도는 MySQL의 BNL 방식보다 더 빠르네요.

 

— 해시 조인

 

1
2
3
4
5
6
7
8
9
10
11
select /*+ leading(a b) use_hash(b) gather_plan_statistics */ * from t1 a, t2 b where a.c1=b.c1;
 
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |  49990 |00:00:00.09 |    4196 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |  48072 |  49990 |00:00:00.09 |    4196 |  1397K|  1118K| 1445K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   5309 |   5000 |00:00:00.01 |      84 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |  48070 |  50000 |00:00:00.03 |    4112 |       |       |          |
----------------------------------------------------------------------------------------------------------------

 

— 머지 조인

 

1
2
3
4
5
6
7
8
9
10
11
12
13
select /*+ leading(a b) use_merge(b) gather_plan_statistics */ * from t1 a, t2 b where a.c1=b.c1;
 
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |  49990 |00:00:00.10 |     919 |       |       |          |
|   1 |  MERGE JOIN         |      |      1 |  48072 |  49990 |00:00:00.10 |     919 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |   5309 |   5000 |00:00:00.01 |      84 |   832K|   511K|  739K (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |   5309 |   5000 |00:00:00.01 |      84 |       |       |          |
|*  4 |   SORT JOIN         |      |   5000 |  48070 |  49990 |00:00:00.04 |     835 |  7282K|  1075K| 6472K (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |  48070 |  50000 |00:00:00.01 |     835 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

 

— NL 조인

 

1
2
3
4
5
6
7
8
9
10
11
select /*+ leading(a b) use_nl(b) gather_plan_statistics */ * from t1 a, t2 b where a.c1=b.c1;
 
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |  49990 |00:00:12.80 |    4181K|
|   1 |  NESTED LOOPS      |      |      1 |  48072 |  49990 |00:00:12.80 |    4181K|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   5309 |   5000 |00:00:00.01 |    3339 |
|*  3 |   TABLE ACCESS FULL| T2   |   5000 |      9 |  49990 |00:00:11.89 |    4178K|
-------------------------------------------------------------------------------------

 

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

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

MySQL 2017.05.10 15:46 Posted by 시연아카데미

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 값으로 건너뛰는 방식을 사용하는 것으로 추정됩니다.

1
2
3
4
5
6
7
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입니다.

1
2
3
4
5
6
7
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을 한 것을 의미합니다.

1
2
3
4
5
6
7
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초 미만)

1
2
3
4
5
6
7
8
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   |
+-----------------------+-------+

 

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

MySQL - IN 절과 EXISTS 절 간의 처리 방식 비교

MySQL 2017.05.10 15:40 Posted by 시연아카데미

이번 시간에 다룰 주제는 MySQL의 IN 절과 EXISTS 절 처리 방식입니다.

 

IN 절과 EXISTS 절은 논리적으로 동일합니다. 즉, IN 절을 사용하던 EXISTS 절을 사용하든 간에 쿼리 수행 결과는 같습니다. 이 같은 속성 때문에 ORACLE과 PostgreSQL은 이 2개의 실행 계획이 같습니다. 즉, 개발자가 어떤 형태로 쿼리를 작성하더라도 구문의 차이 때문에 다른 실행 계획을 수립하지는 않습니다. MySQL을 연구하기 전까지는 이것을 너무나 당연하게 생각했었는데요. MySQL은 좀 다릅니다.

 

MySQL은 EXISTS 절을 처리하는 방식과 IN 절을 처리하는 방식 간의 차이가 존재합니다. 제가 테스트한 버전은 5.7인데요. 예전 문서를 보면 가급적 IN 절을 사용하지 말라는 글도 있습니다. 5.x 초기 버전에서는 IN 절에 대한 최적화가 미흡했던 것 같습니다. 하지만 정확한 버전은 모르겠지만 제가 테스트한 5.6, 5.7 버전에서는 IN 절에 대한 최적화 기법이 많이 적용된 것을 확인할 수 있었습니다.

자 그럼 테스트를 통해 살펴보겠습니다. 참고로, Handler 관련 통계 정보 확인을 위해서 매 테스트 전에 항상 flush status 명령어를 수행해서 해당 수치를 초기화했고, 아래의 결과는 5.7에서 수행한 결과입니다.

 

테스트 데이터 생성

drop table t1;
drop table t2;

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

set max_heap_table_size=16777216*10;

CALL generate_series(1,1000000);

-- t1 테이블에는 100 건을 입력하고, t2 테이블에는 100 만건을 입력합니다. 

insert into t1 select series, series, 'dummy' from series_tmp order by series limit 100;
insert into t2 select mod(series,100), series, 'dummy' from series_tmp;

 

 

 

메인 쿼리 집합이 매우 작고 서브 쿼리 칼럼에 인덱스가 없는 경우

 

EXISTS 절은 항상 DEPENDENT SUBQUERY 방식으로 수행됩니다. 따라서 이 같은 경우는 EXISTS 절이 유리합니다. (물론 서브 쿼리 칼럼에 적절한 인덱스가 없는 것은 설계 오류이므로, 설명을 위한 예제일 뿐이라고 이해해주시면 좋을 것 같습니다)

 

DEPENDENT SUBQUERY 방식은 Main 쿼리 집합 건수만큼 반복적으로 서브 쿼리 테이블을 액세스하는 방식입니다. 이때 T1 테이블에서 C2=10 조건을 만족하는 레코드가 1건 뿐이므로 T2 테이블을 1번만 액세스하면 됩니다. 즉, T2 테이블이 크긴 하지만 1번만 Full Scan을 하면 될 뿐더러, 세미 조인의 특성 상 매칭 되는 레코드를 1건만 찾으면 더 이상 스캔을 수행하지 않으므로, 만일 매칭 되는 레코드가 앞 부분에 위치한다면 스캔의 부하도 크지 않습니다.

Handler_read_rnd_next 수치는 Full Scan 시에 Scan Call을 요청한 횟수를 의미합니다. 현재 수치가 111이며 이 수치는 다음과 같이 해석할 수 있습니다.

  1. T1 테이블을 Full Scan 할때 101번 Call 수행 (100건 + 1회)
  2. T2 테이블에서 매칭되는 레코드를 찾기 위해서 10회 Call 수행. 즉, 앞에서 10 번째에 위치한 레코드를 찾음

-- EXISTS 절 사용

explain
select * from t1 where c2=10 and exists (select 1 from t2 where t2.c1=t1.c1);
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | PRIMARY            | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    100 |    10.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 991672 |    10.00 | Using where |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

수행 결과: 1 row in set (0.00 sec)

show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 2     |
| Handler_read_key      | 2     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 111   |
+-----------------------+-------+

 

이때, IN 절을 사용하면 MATERIALIZED 방식을 사용합니다. 이때도 DEPENDENT SUBQUERY 방식을 사용하면 좋지만 옵티마이저는 MATERIALIZED 방식이 더 유리하다고 판단한 것 같습니다. (결과적으로는 더 느립니다) MATERIALIZED 방식을 사용하므로 100 만 건이나 되는 T2 테이블을 Scan 한 후에 내부적으로 인덱스를 생성하는 작업을 수행합니다. 이로 인해 수행 속도도 EXISTS 절에 비해서 매우 느립니다. Handler_read_rnd_next 수치를 보면 T2 테이블 레코드 건수인 100만 건+1회만큼 Scan Call을 수행했고, T1 테이블 레코드 건수인 100건+1회만큼 Scan Call을 수행한 것을 알 수 있습니다.

 


-- IN 절 사용

explain
select * from t1 where c2=10 and c1 in (select c1 from t2);
+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------+--------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref         | rows   | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------+--------+----------+-------------+
|  1 | SIMPLE       | t1          | NULL       | ALL    | PRIMARY       | NULL       | NULL    | NULL        |    100 |    10.00 | Using where |
|  1 | SIMPLE       |  | NULL       | eq_ref |     |  | 5       | mysql.t1.c1 |      1 |   100.00 | NULL        |
|  2 | MATERIALIZED | t2          | NULL       | ALL    | NULL          | NULL       | NULL    | NULL        | 991672 |   100.00 | NULL        |
+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------+--------+----------+-------------+

수행 결과: 1 row in set (0.48 sec)

show session status like 'handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 2       |
| Handler_read_key      | 3       |
| Handler_read_last     | 0       |
| Handler_read_next     | 0       |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 1000102 |
+-----------------------+---------+

 

 

메인 쿼리 집합이 매우 작고 서브 쿼리 칼럼에 인덱스가 있는 경우

 

서브 쿼리 칼럼에 인덱스를 생성한 후에 동일한 테스트를 수행하면 EXISTS와 IN 절의 성능은 동일합니다. 아래 2개의 결과를 보면 수행 시간은 모두 0.01초 미만이며, 동일한 IO 일량을 나타냅니다. 이때, IN 절의 Explain 결과를 보면 FirstMatch라고 표시됩니다. FirstMatch는 IN 절을 수행하는 옵티마이저의 전략 중의 하나인데요. 이름 그대로 첫 번째 매칭되는 레코드를 찾으면 더 이상 조인을 수행하지 않겠다는 것입니다. 어찌보면 세미 조인의 원리라고도 할 수 있겠네요. 그리고 이 방식은 DEPENDENT SUBQUERY와 동일합니다. 처리 방식이 동일하니 일량도 동일합니다.  

 

 

-- 인덱스 생성

create index t2_c1_idx on t2(c1);

 

-- EXISTS 절 사용

explain
select * from t1 where c2=10 and exists (select 1 from t2 where t2.c1=t1.c1);
+----+--------------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key       | key_len | ref         | rows  | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-------------+
|  1 | PRIMARY            | t1    | NULL       | ALL  | NULL          | NULL      | NULL    | NULL        |   100 |    10.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | ref  | t2_c1_idx     | t2_c1_idx | 5       | mysql.t1.c1 | 10438 |   100.00 | Using index |
+----+--------------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-------------+

수행 결과: 1 row in set (0.00 sec)

show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 2     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 101   |
+-----------------------+-------+

 

-- IN 절 사용

explain
select * from t1 where c2=10 and c1 in (select c1 from t2);
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows  | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | PRIMARY       | NULL      | NULL    | NULL        |   100 |    10.00 | Using where                 |
|  1 | SIMPLE      | t2    | NULL       | ref  | t2_c1_idx     | t2_c1_idx | 5       | mysql.t1.c1 | 10438 |   100.00 | Using index; FirstMatch(t1) |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+-------+----------+-----------------------------+

수행 결과: 1 row in set (0.00 sec)

show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 2     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 101   |
+-----------------------+-------+

 

 

메인 쿼리 집합이 매우 큰 경우

 

테스트 결과 간의 비교를 쉽게 하기 위해서 약간 과장된 예제를 들어보겠습니다. 아래의 예제처럼 메인 쿼리 테이블은 매우 크고, 메인 쿼리에는 필터 조건이 없고, 서브 쿼리 내에는 매우 좋은 필터 조건이 있는 경우를 가정해보겠습니다.


이때 EXISTS 절을 사용하면 매우 비효율적으로 쿼리가 수행됩니다. EXISTS 절은 DEPENDENT SUBQUERY 방식으로만 수행되므로 100만 번 서브 쿼리를 수행합니다.


반면 아래의 IN 절의 Explain 결과를 보면 서브 쿼리가 아닌 조인으로 수행됐고 이때 조인 순서는 T1->T2 인 것을 알 수 있습니다. 이 또한 IN 서브 쿼리 최적화 기법의 하나입니다. 즉, T1 테이블에서 C2=10 조건에 만족하는 1건을 추출한 후에 T2 테이블과 조인을 수행한 것입니다. 이때 t2_c1_idx 인덱스를 이용해서 T2 테이블을 10,000번 액세스하게 됩니다. (Handler_read_next=10000)

 

-- EXISTS 절 사용

explain 
select * from t2 where exists (select 1 from t1 where t1.c1=t2.c1 and c2=10);
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------------+--------+----------+-------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref         | rows   | filtered | Extra       |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------------+--------+----------+-------------+
|  1 | PRIMARY            | t2    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL        | 991672 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | mysql.t2.c1 |      1 |    10.00 | Using where |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------------+--------+----------+-------------+

수행 결과: 10000 rows in set (2.18 sec)

mysql> show session status like 'handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 1       |
| Handler_read_key      | 1000001 |
| Handler_read_last     | 0       |
| Handler_read_next     | 0       |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 1000001 |
+-----------------------+---------+

 

 

-- IN 절 사용

explain 
select * from t2 where c1 in  (select c1 from t1 where c2=10);
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | PRIMARY       | NULL      | NULL    | NULL        |  100 |    10.00 | Using where |
|  1 | SIMPLE      | t2    | NULL       | ref  | t2_c1_idx     | t2_c1_idx | 5       | mysql.t1.c1 | 9818 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+

수행 결과: 10000 rows in set (0.08 sec)

show session status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 2     |
| Handler_read_last     | 0     |
| Handler_read_next     | 10000 |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 101   |
+-----------------------+-------+

 

 

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

MySQL은 Complex View (aggregate가 존재하는 derived table)에 대한 View Merging을 지원하지 않을 뿐만 아니라 JPPD (Join Predicate Push-Down)도 지원하지 않습니다. 게다가 Lateral Inline View도 제공하지 않습니다. (Lateral Inline View는 여기를 참조하세요)


이같은 한계로 인해 "과연 MySQL이 제대로 된 성능을 내고 있을까?" 하는 의문이 들기도 합니다. (아니면 제가 모르는 다른 묘안이 있을까요?)


ORACLE은 Complex View에 대한 View Merging 또는 JPPD의 지원 범위를 꾸준히 늘리고 있습니다. 이제 대부분의 Complex View (Union을 포함한)도 JPPD가 가능할 정도입니다. 이뿐만 아니라 12c 버전에서는 Lateral Inline View를 사용자에게 제공함으로써 사용자가 직접 JPPD를 구현할 수도 있습니다.


PostgreSQL은 아직까지는 제한적인 View Merging과 JPPD를 지원하지만, Lateral Inline View를 제공함으로써 이러한 약점을 극복하고 있습니다.


그런데 MySQL은 현재까지 제가 조사한 바로는 Complex View에 대한 어떠한 튜닝 방안도 제시하고 있지 않습니다. 그나마 여기를 보면 Function 리턴 값을 이용해서 Complex View 내로 특정 상숫값을 푸시하는 기법을 제시하고 있지만, 이 방법은 매우 제한적인 방법입니다. 왜냐면 이 방법은 특정 상수값을 View로 푸시할 수 있는 경우에만 유효한 방법이기 때문입니다. 만일 외부 상수 조건을 푸시하는 것이 아니라 조인 조건을 푸시하는 경우에는 사용할 수 없습니다.


전통적인 튜닝 원칙 중의 하나는 "조인 횟수를 줄이는 것이 좋다"는 것입니다. 이를 위해 group by를 먼저 수행한 후에 조인을 수행하는 방식을 사용하곤 했습니다. 그런데 이 원칙을 MySQL에 적용하면 성능상의 문제가 발생하게 됩니다. 예를 들어 살펴보겠습니다.


테스트 환경 구성


drop table dept;
drop table emp;
 
create table dept (deptno integer primary key, dname char(100));
create table emp  (empno integer primary key, ename char(100), deptno integer, salary double);   


generate_series 함수 생성 방법은 여기를 참고하세요. 

CALL generate_series(1,1000000);
 
insert into dept select series, concat("dname_", convert(series,char)) from series_tmp order by series limit 10000;
insert into emp  select series, concat("ename_", convert(series,char)), mod(series,10000), rand()*100000 from series_tmp order by series limit 1000000;
 
analyze table dept;
analyze table emp;


emp 테이블의 dept 칼럼에 인덱스를 생성하지 않고 테스트 수행



아래는 부서별 급여을 추출하는 쿼리입니다. case-1과 같이 쿼리를 작성하면 조인 횟수가 불필요하게 많아지므로 "가능한 조인 횟수를 줄이는 것이 좋다"라는 튜닝 원칙에는 위배됩니다. 따라서 Case-2와 같이 Group by 후에 조인을 수행하는 것이 일반적인 방식이었습니다. 물론 아래와 같이 필터 조건이 없는 경우에는 MySQL도 동일한 원칙이 적용됩니다. Case-1의 수행 속도는 4.2초이고 Case-2의 수행 속도는 1.17초이므로 Case-2가 2배 이상 빠릅니다.


Case-1) 조인 후에 Group by

explain
select d.dname, sum(e.salary), min(e.salary), max(e.salary)
from   dept d, emp e
where  d.deptno = e.deptno
group by d.dname;

+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows   | filtered | Extra                                        |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | e     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           | 991230 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | mysql.e.deptno |      1 |   100.00 | NULL                                         |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+----------------------------------------------+
9999 rows in set (4.20 sec)


Case-2) Group by 후에 조인

explain
select d.dname, e.sum_sal, e.min_sal, e.max_sal 
from   dept d, (select deptno, 
                       sum(salary) sum_sal, 
					   min(salary) min_sal, 
					   max(salary) max_sal
                from   emp 
				group by deptno) e
where  d.deptno = e.deptno;
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref            | rows   | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
|  1 | PRIMARY     | d          | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL           |   9749 |   100.00 | NULL                            |
|  1 | PRIMARY     |  | NULL       | ref  |    |  | 5       | mysql.d.deptno |    101 |   100.00 | NULL                            |
|  2 | DERIVED     | emp        | NULL       | ALL  | NULL          | NULL        | NULL    | NULL           | 991230 |   100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
9999 rows in set (1.17 sec)


아래와 같이 필터 조건이 추가된 경우에도 이전의 예제와 동일하게 Group by 후에 조인하는 패턴이 더 빠른 수행 속도를 나타내고 있습니다. 여기까지는 다른 DBMS와 동일합니다. 그런데 인덱스를 생성한 후에는 전혀 다른 결과를 보입니다.


Case-3) 필터 조건 추가 -조인 후 Group by

explain
select d.dname, sum(e.salary), min(e.salary), max(e.salary)
from   dept d, emp e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990'
group by d.dname;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+-------------+
|  1 | SIMPLE      | e     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           | 991230 |   100.00 | Using where |
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | mysql.e.deptno |      1 |    10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+--------+----------+-------------+
1 row in set (3.70 sec)


Case-4) 필터 조건 추가 - Group by 후 조인

explain
select d.dname, e.sum_sal, e.min_sal, e.max_sal 
from   dept d, (select deptno, 
                       sum(salary) sum_sal, 
					   min(salary) min_sal, 
					   max(salary) max_sal
                from   emp 
				group by deptno) e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990';
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref            | rows   | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
|  1 | PRIMARY     | d          | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL           |   9749 |    10.00 | Using where                     |
|  1 | PRIMARY     |  | NULL       | ref  |    |  | 5       | mysql.d.deptno |    101 |   100.00 | NULL                            |
|  2 | DERIVED     | emp        | NULL       | ALL  | NULL          | NULL        | NULL    | NULL           | 991230 |   100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+--------+----------+---------------------------------+
1 row in set (0.75 sec)


emp 테이블의 dept 칼럼에 인덱스를 생성한 후에 테스트 수행


-- 인덱스 생성 

create index emp_deptno_idx on emp (deptno);


인덱스 생성 후에 Case-1 쿼리를 수행하면 기존보다 2배 이상 느려집니다. 그 이유는 조인 순서가 dept->emp로 변경되었고 이로 인해 랜덤 IO가 더 많아졌기 때문입니다. 속도를 향상시키기 위해 생성한 인덱스의 부작용이 발생한 셈이죠. 더군다나 Case-2 쿼리는 기존 대비 7배 이상 느려졌습니다. 기존 방식은 emp 테이블을 full scan하면서 temp table을 생성했지만, 인덱스를 생성함에 따라 emp_deptno_idx 인덱스를 이용해서 건건이 테이블을 액세스하는 부하가 가중되었기 때문입니다. 이 또한, 인덱스 생성에 의한 부작용입니다.


Case-1) 조인 후에 Group By

explain
select d.dname, sum(e.salary), min(e.salary), max(e.salary)
from   dept d, emp e
where  d.deptno = e.deptno
group by d.dname;
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref            | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+---------------------------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY        | NULL           | NULL    | NULL           | 9749 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | e     | NULL       | ref  | emp_deptno_idx | emp_deptno_idx | 5       | mysql.d.deptno |  100 |   100.00 | NULL                            |
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+---------------------------------+
9999 rows in set (8.44 sec)


Case-2)  Group By 후에 조인

explain
select d.dname, e.sum_sal, e.min_sal, e.max_sal 
from   dept d, (select deptno, 
                       sum(salary) sum_sal, 
					   min(salary) min_sal, 
					   max(salary) max_sal
                from   emp 
				group by deptno) e
where  d.deptno = e.deptno;
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys  | key            | key_len | ref            | rows   | filtered | Extra |
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------+
|  1 | PRIMARY     | d          | NULL       | ALL   | PRIMARY        | NULL           | NULL    | NULL           |   9749 |   100.00 | NULL  |
|  1 | PRIMARY     |  | NULL       | ref   |     |     | 5       | mysql.d.deptno |    101 |   100.00 | NULL  |
|  2 | DERIVED     | emp        | NULL       | index | emp_deptno_idx | emp_deptno_idx | 5       | NULL           | 991230 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------+
9999 rows in set (8.17 sec)



인덱스 생성 후에 Case-3과 Case-4의 결과를 보면, 인덱스 생성 전과 비교해서 확연한 차이를 보입니다. Case-3과 같이 조인을 수행한 후에 group by를 수행하는 경우는 매우 빠른 수행 속도를 보입니다. 그런데 Case-4와 같이 Complex View가 있는 경우는 기존보다 훨씬 더 느립니다.


Case-3) 필터 조건 추가 -조인 후 Group by

explain
select d.dname, sum(e.salary), min(e.salary), max(e.salary)
from   dept d, emp e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990'
group by d.dname;
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref            | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+-------------+
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY        | NULL           | NULL    | NULL           | 9749 |    10.00 | Using where |
|  1 | SIMPLE      | e     | NULL       | ref  | emp_deptno_idx | emp_deptno_idx | 5       | mysql.d.deptno |  100 |   100.00 | NULL        |
+----+-------------+-------+------------+------+----------------+----------------+---------+----------------+------+----------+-------------+
1 row in set (0.05 sec)


Case-4) 필터 조건 추가 - Group by 후 조인

explain
select d.dname, e.sum_sal, e.min_sal, e.max_sal 
from   dept d, (select deptno, 
                       sum(salary) sum_sal, 
					   min(salary) min_sal, 
					   max(salary) max_sal
                from   emp 
				group by deptno) e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990';
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys  | key            | key_len | ref            | rows   | filtered | Extra       |
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------------+
|  1 | PRIMARY     | d          | NULL       | ALL   | PRIMARY        | NULL           | NULL    | NULL           |   9749 |    10.00 | Using where |
|  1 | PRIMARY     |  | NULL       | ref   |     |     | 5       | mysql.d.deptno |    101 |   100.00 | NULL        |
|  2 | DERIVED     | emp        | NULL       | index | emp_deptno_idx | emp_deptno_idx | 5       | NULL           | 991230 |   100.00 | NULL        |
+----+-------------+------------+------------+-------+----------------+----------------+---------+----------------+--------+----------+-------------+
1 row in set (8.39 sec)


정리해보면, 필터 조건이 없이 전체 범위를 처리하는 경우에는 인덱스 없이 (또는 IGNORE INDEX 힌트 이용) Complex View 형태로 처리하는 것이 유리합니다. 하지만 필터 조건이 있다면, 일반 조인 형태로 처리한 후에 Group by를 수행하는 것이 가장 빠릅니다. (물론 이때 조인 칼럼에 인덱스가 있어야 하겠지요)


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

MySQL에서 unnamed derived table 사용 시의 주의 사항

MySQL 2017.05.02 00:52 Posted by 시연아카데미

MySQL 서버팀에서 제공하는 블로그 내용을 보면 5.7부터 unnamed derived table을 처리하는 방식이 달라졌다는 내용이 있습니다. (여기 참조)
DBMS마다 용어가 달라서 조금 헷갈린대요. unnamed derived table은 ORACLE에서 말하는 in-line view, 즉, FROM 절에 나타나는 SELECT 문장을 의미합니다. 그리고 named derived table은 View입니다. 이 2가지를 통칭해서 derived table이라고 합니다. 그리고 논의의 편의를 위해서 aggregate가 없는 것을 simple한 derived table 이라고 하고, aggregate가 있는 것을 complex한 derived table이라고 하겠습니다.


MySQL의 derived table 처리방식과 타 DBMS의 비교



오라클과 PostgreSQL은 In-line view와 view의 처리 방식이 완벽히 동일합니다. 어찌 보면 사용자 입장에서는 이 방식이 직관적입니다. 그런데 MySQL은 이와 다릅니다. 5.6까지는 simple한 unnamed derived table도 View Merging이 불가능합니다. 즉, 실수로 잘못 코딩한 SQL 때문에 성능이 저하되는 문제가 존재합니다. 5.7부터는 이 문제가 해결되었습니다. 사실 5.6의 동작 방식을 문제라고 해야 할지, 아니면 개발자의 계획된 설계라고 해야 할지는 잘 모르겠습니다. 오히려 이러한 동작 방식을 이용해서 튜닝할 수도 있을 테니까요.


하지만 정확한 동작 원리를 모른 체 작성한 SQL 때문에 생각지도 못한 성능 저하가 발생했다면 이것은 개선해야 할 사항이기도 했을 것 같습니다. 여하튼 5.7에서는 simple 한 unnamed derived table에 대해서는 View Merging 기능을 제공합니다.


테스트 환경 구성


drop table dept;
drop table emp;

create table dept (deptno integer primary key, dname char(100));
create table emp  (empno integer primary key, ename char(100), deptno integer, salary double);   
generate_series 함수 생성 방법은 여기를 참고하세요.
CALL generate_series(1,1000000);

insert into dept select series, concat("dname_", convert(series,char)) from series_tmp order by series limit 10000;
insert into emp  select series, concat("ename_", convert(series,char)), mod(series,10000), rand()*100000 from series_tmp order by series limit 1000000;

create index emp_deptno_idx on emp (deptno); 

analyze table dept;
analyze table emp;



수행 방식 테스트 (5.6, 5.7 버전)



1) 일반 조인


일반 조인은 5.6, 5.7 모두 매우 빠르게 수행됩니다.

explain
select d.dname, d.deptno, e.empno, e.ename
from   dept d, emp e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990';

+----+-------------+-------+-----+----------------+----------------+------+-------------+
| id | select_type | table |type | key            | ref            | rows | Extra       |
+----+-------------+-------+-----+----------------+----------------+------+-------------+
|  1 | SIMPLE      | d     |ALL  | NULL           | NULL           | 9749 | Using where |
|  1 | SIMPLE      | e     |ref  | emp_deptno_idx | mysql.d.deptno |  100 | NULL        |
+----+-------------+-------+-----+----------------+----------------+------+-------------+
100 rows in set (0.01 sec)



2) unnamed derived table 사용 시


5.6 버전까지는 unnamed derived table은 항상 temp 테이블을 생성했습니다. 따라서 emp 테이블 전체를 temp 테이블로 생성한 후에 조인을 수행합니다. 이때, temp 테이블에는 index가 없는 셈이므로 내부적으로 auto_key0이라고 하는 temp 테이블용 인덱스를 생성합니다.


물론, 이 역시 부하가 발생하는 작업이지만 dept 테이블 매 건마다 temp 테이블을 full scan 하는 부하보다는 훨씬 더 적은 부하이므로, temp 테이블에 대한 인덱스를 생성하는 작업을 수행하는 것입니다. 이때 Explain의 type 칼럼은 "ref"라고 표시됩니다. 따라서 5.6까지는 unnamed derived table을 사용할 때 이러한 특성을 인지해야만 합니다.


하지만 5.7부터는 simple한 unnamed derived table에 대해서는 View Merging이 가능합니다. 아래의 Explain 결과를 보면 Explain 결과가 2줄로 변경됐고, DERIVED 오퍼레이션이 사라진 것을 알 수 있습니다. 다시말해, 일반 조인과 동일한 방식으로 수행된 것입니다.


explain
select d.dname, d.deptno, e.empno, e.ename
from   dept d, (select empno, ename, deptno from emp) e
where  d.deptno = e.deptno
and    d.dname  = 'dname_9990';

-- 5.6 버전 Explain 결과와 수행 시간 


+----+-------------+------------+------+-------------+----------------+--------+-------------+
| id | select_type | table      | type | key         | ref            | rows   | Extra       |
+----+-------------+------------+------+-------------+----------------+--------+-------------+
|  1 | PRIMARY     | d          | ALL  | NULL        | NULL           |   9749 | Using where |
|  1 | PRIMARY     |  | ref  |  | mysql.d.deptno |     99 | NULL        |
|  2 | DERIVED     | emp        | ALL  | NULL        | NULL           | 966226 | NULL        | 
+----+-------------+------------+------+-------------+----------------+--------+-------------+
100 rows in set (7.29 sec)

-- 5.7 버전 Explain 결과와 수행 시간 

+----+-------------+-------+-----+----------------+---------------+------+------------+
| id | select_type | table |type | key            |ref            | rows |Extra       |
+----+-------------+-------+-----+----------------+---------------+------+------------+
|  1 | SIMPLE      | d     |ALL  | NULL           |NULL           | 9749 |Using where |
|  1 | SIMPLE      | emp   |ref  | emp_deptno_idx |mysql.d.deptno |  100 |NULL        |
+----+-------------+-------+-----+----------------+---------------+------+------------+
100 rows in set (0.01 sec)


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

한 서버에 MySQL 5.6 버전과 5.7 버전을 설치하는 방법

MySQL 2017.04.28 22:12 Posted by 시연아카데미

5.7을 이용해서 MySQL 연구를 하던 중에 5.7의 옵티마이저 동작 원리가 5.6과 달라진 부분이 많아서 5.6을 설치할 필요가 생겼습니다. 그런데 한 서버에 MySQL 5.6과 5.7 버전을 설치하는 것이 약간 까다롭네요.


PostgreSQL은 RPM을 이용해서 설치하더라도 설치 디렉토리, 접속 포트 등을 지정할 수 있었는데, MySQL은 RPM을 이용해서는 지정된 디렉토리 (/var/lib/mysql)에만 설치가 가능한 것 같습니다.


일부 문서를 보면 --relocate 옵션을 이용해서 디렉토리 지정이 가능하다고 되어있는데, 5.6, 5.7 버전은 relocate가 불가능합니다.


그래서 이참에 소스 컴파일로 두 개의 버전을 설치해보기로 했습니다. 그런데 이 또한 사전 설치 작업, 라이브러리 설치, socket 지정 등의 작업으로 인한 시행착오가 일부 있었습니다. 따라서 관련 내용을 공유하고자 포스팅합니다.


사전 설치 작업



소스 컴파일을 위해선 cmake와 Bison을 설치해야 합니다. 그리고 5.7은  Boost 라이브러리를 추가로 설치해야 합니다.


1) cmake 설치


다운로드 사이트: https://cmake.org/
다운로드 파일: cmake-3.8.0-Linux-x86_64.tar.gz 


-- 설치 단계


배포용 파일이므로 컴파일은 필요없고 디렉토리에 압축만 풀면됩니다.


# gunzip cmake-3.8.0-Linux-x86_64.tar.gz
# tar xvf cmake-3.8.0-Linux-x86_64.tar
# cp -rp cmake-3.8.0-Linux-x86_64 /usr/local/cmake


-- .bash_profile PATH를 수정합니다.


PATH=$PATH:$HOME/bin:/usr/local/cmake/bin


# . .bash_profile


2) Bison 설치


다운로드 사이트: http://www.gnu.org/software/bison/
다운로드 파일: bison-3.0.tar.gz


-- 설치 단계 


# gunzip bison-3.0.tar.gz
# tar xvf bison-3.0.tar
# cd bison-3.0
# ./configure
# make
# make install


3) Boost 라이브러리 설치 (참고사이트: http://dslee1.blogspot.kr/2016/04/mysql-5711-cmake-error-at.html)


-- 설치 단계


# cd /usr/local/src/
# wget http://downloads.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz
# tar xvfz boost_1_59_0.tar.gz


4) 유저 및 그룹 생성


# groupadd mysql56
# useradd -r -g mysql56 -s /bin/false mysql56

# groupadd mysql57
# useradd -r -g mysql57 -s /bin/false mysql57


5) 소스 다운로드


https://github.com/mysql/mysql-server/tree/5.6
https://github.com/mysql/mysql-server/tree/5.7


MySQL 5.6 설치



설치 단계는 다음과 같습니다.


1) 설치 단계


# unzip mysql-server-5.6.zip
# cd mysql-server-5.6
# mkdir bld
# cd bld
# cmake ..


-- CmakeCache.txt 파일을 열어서 설치 디렉토리 위치를 변경합니다.


CMAKE_INSTALL_PREFIX:PATH=/usr/local/mysql56
MYSQL_DATADIR:PATH=/usr/local/mysql56/data
SYSCONFDIR:PATH=/usr/local/mysql56/etc


-- 내용을 수정한 후에 make 및 make install을 수행합니다.


# make
# make install


2) 후속 작업


# chown -R mysql56:mysql56 /usr/local/mysql56
# /usr/local/mysql56/scripts/mysql_install_db --user=mysql56


3) 비밀번호 변경 (참고사이트: http://egloos.zum.com/spongebody/v/3530932)


5.6을 소스로 설치할 때 root 비번을 잘 모르는 경우가 있습니다. 이때는 다음과 같이 조치합니다.


# /usr/local/mysql56/bin/mysqld_safe --user=mysql56 --skip-grant-tables &
# mysql -u root mysql
mysql> update user set password=password('rootpassword') where user = 'root';
mysql> flush privileges;


# killall mysqld
# /usr/local/mysql56/bin/mysqld_safe --user=mysql56 &


4) /usr/local/mysql56/my.cnf 파일 편집


저 같은 경우에는 5.6은 기본 포트인 3306으로, socket은 /tmp/mysql56.sock 으로 설정했습니다. 따라서 /usr/local/mysql56/my.cnf 파일을 다음과 같이 편집합니다.


[mysqld]
port = 3306
socket=/tmp/mysql56.sock
이하 생략


5) 접속 방법


소켓을 별도로 지정했으므로 -S 옵션을 이용해서 해당 소켓을 지정합니다.

# mysql -uroot -p -S /tmp/mysql56.sock


MySQL 5.7 설치



설치 단계는 다음과 같습니다.


1) 설치 단계


# unzip mysql-server-5.7.zip
# cd mysql-server-5.7
# mkdir bld
# cd bld
# cmake .. -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/src/boost_1_59_0


-- CmakeCache.txt 파일을 열어서 설치 디렉토리 위치를 변경합니다.


CMAKE_INSTALL_PREFIX:PATH=/usr/local/mysql57
MYSQL_DATADIR:PATH=/usr/local/mysql57/data
MYSQL_KEYRINGDIR:PATH=/usr/local/mysql57/keyring
SYSCONFDIR:PATH=/usr/local/mysql57/etc


-- 내용을 수정한 후에 make 및 make install을 수행합니다.


# make
# make install


2) 후속 작업


아래 작업 시에 화면에 root 임시 비번이 출력됩니다. 해당 비번을 복사해둡니다.


chown -R mysql57:mysql57 /usr/local/mysql57
/usr/local/mysql57/bin/mysqld --initialize --user=mysql57
/usr/local/mysql57/bin/mysql_ssl_rsa_setup


3) /usr/local/mysql57/my.cnf 파일 편집 


[mysqld]
socket=/tmp/mysql57.sock
port=3307
이하 생략


4) MySQL 시작
 
# /usr/local/mysql57/bin/mysqld_safe --user=mysql57 &


5) 비밀번호 변경


# mysql -uroot -ptcDqMfDh:7h? -S /tmp/mysql57.sock
mysql> alter user 'root'@'localhost' identified by 'rootpassword';
mysql> flush privileges;


6) 접속


소켓을 별도로 지정했으므로 -S 옵션을 이용해서 해당 소켓을 지정합니다.


# mysql -uroot -p -S /tmp/mysql57.sock


이로써 한 서버에 2개의 MySQL 설치가 완료됐습니다.


저는 서버에서 테스트용으로 2개 버전을 설치한 것이라서 mysql 접속만 하면 되는데요.

만일 PHP를 이용하는 경우에는 기본적으로는 /tmp/mysql.sock을 이용하므로 socket 명을 변경한 경우에는 php.ini를 수정하는 등의 작업이 필요할 것 같습니다.


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

STRAIGHT_JOIN 힌트를 이용한 MySQL Explain 읽는 순서 확인

MySQL 2017.04.21 21:39 Posted by 시연아카데미

MySQL의 Explain 결과는 다른 DBMS와는 사뭇 다릅니다. 따라서 MySQL의 Explain 결과를 처음 접했을 때는 약간 당황스럽기도 합니다. 조금 더 익숙해지면 오히려 더 편리할 수도 있을지는 모르겠으나, 저로서는 아직은 생소합니다.


읽는 순서는 매우 단순합니다. "위에서부터 아래로 읽으면 됩니다."


과연 그럴까요? 이를 검증해보기 위해서 STRAIGHT_JOIN 힌트를 이용해서 몇 가지 경우를 확인해봤습니다. (STRAIGHT_JOIN 힌트는 FROM 절 순서대로 조인을 수행하도록 유도하는 힌트입니다.)


단순 조인인 경우는 위에서부터 읽어내려가면 됩니다. Derived 테이블이 있는 경우는 약간 헷갈린 데, 그 이유는 Derived 테이블의 원본 테이블이 맨 하단부에 위치하기 때문입니다. 이 역시 어느 정도 적응이 필요할 것 같습니다.


참고로 MySQL은 ORACLE의 In-Line View를 Derived 테이블 또는 From 절 내의 Subquery라고 합니다. 그리고 In-Line View와 같이 From 절에 View 쿼리를 직접 작성한 것을 unnamed derived 테이블이라고 하고, View를 named derived 테이블이라고도 합니다.


아래는 테스트 내용입니다.


-- 테이블 생성 

drop table t1;
drop table t2;
drop table t3;

create table t1 (c1 integer, c2 integer);
create table t2 (c1 integer, c2 integer);
create table t3 (c1 integer, c2 integer);

		 
-- STRAIGHT_JOIN 힌트를 적용했으므로 조인 순서는 T3->T2->T1이며 
-- Explain 결과도 이를 나타내고 있습니다.

explain
select STRAIGHT_JOIN * from t3, t2, t1
where t1.c1 = t2.c1
and   t1.c1 = t3.c1;
+----+-------------+-------+----------------------------------------------------+
| id | select_type | table | Extra                                              |
+----+-------------+-------+----------------------------------------------------+
|  1 | SIMPLE      | t3    | NULL                                               |
|  1 | SIMPLE      | t2    | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | t1    | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+----------------------------------------------------+

-- pt-visual-explain 결과는 아래에서 위로 읽습니다. 

[root@pgserver ~]# mysql -u root -p mydb01 < ./2.sql | pt-visual-explain
Enter password:
JOIN
+- Join buffer
|  +- Filter with WHERE
|     +- Table scan
|        rows           1
|        +- Table
|           table          t1
+- JOIN
   +- Join buffer
   |  +- Filter with WHERE
   |     +- Table scan
   |        rows           1
   |        +- Table
   |           table          t2
   +- Table scan
      rows           1
      +- Table
         table          t3

		 
-- Derived 테이블이 있는 경우를 살펴보겠습니다. 
-- 아래의 예제는 (t1 scan) -> derived 테이블 생성 -> t2 -> t3 순서로 수행되며,
-- Explain은 이를 다음과 같이 표현하고 있습니다.
-- ID가 1인 것들이 조인 대상 집합이며 ID가 2인 것이 derived 테이블 생성 작업을 나타냅니다.

explain
select STRAIGHT_JOIN * from (select c1, count(*) cnt from t1 group by c1) a, t2, t3
where  a.c1 = t2.c1
and    a.c1 = t3.c1;
+----+-------------+------------+----------------------------------------------------+
| id | select_type | table      | Extra                                              |
+----+-------------+------------+----------------------------------------------------+
|  1 | PRIMARY     |  | NULL                                               |
|  1 | PRIMARY     | t2         | Using where; Using join buffer (Block Nested Loop) |
|  1 | PRIMARY     | t3         | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | t1         | Using temporary; Using filesort                    |
+----+-------------+------------+----------------------------------------------------+

-- pt-visual-explain은 관련있는 것을 Tree 구조로 표현해줍니다. 
-- 아래의 결과를 보면, t1 scan -> derived 테이블 생성 작업을 연관해서 트리 구조로 표현합니다.

[root@pgserver ~]# mysql -u root -p mydb01 < ./3.sql | pt-visual-explain
Enter password:
JOIN
+- Join buffer
|  +- Filter with WHERE
|     +- Table scan
|        rows           1
|        +- Table
|           table          t3
+- JOIN
   +- Join buffer
   |  +- Filter with WHERE
   |     +- Table scan
   |        rows           1
   |        +- Table
   |           table          t2
   +- Table scan
      rows           2
      +- DERIVED
         table          derived(temporary(t1))
         +- Filesort
            +- TEMPORARY
               table          temporary(t1)
               +- Table scan
                  rows           1
                  +- Table
                     table          t1

-- 참고로 조인 순서를 변경하면 다음과 같이 Explain 결과가 변경됩니다. 
-- 이 예제 또한, ID가 2인 것이 맨 아래에 위치한다는 것을 알 수 있습니다.

explain
select STRAIGHT_JOIN * from t2, (select c1, count(*) cnt from t1 group by c1) a, t3
where  a.c1 = t2.c1
and    a.c1 = t3.c1;
+----+-------------+------------+----------------------------------------------------+
| id | select_type | table      | Extra                                              |
+----+-------------+------------+----------------------------------------------------+
|  1 | PRIMARY     | t2         | Using where                                        |
|  1 | PRIMARY     |  | NULL                                               |
|  1 | PRIMARY     | t3         | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | t1         | Using temporary; Using filesort                    |
+----+-------------+------------+----------------------------------------------------+

[root@pgserver ~]# mysql -u root -p mydb01 < ./4.sql | pt-visual-explain
Enter password:
JOIN
+- Join buffer
|  +- Filter with WHERE
|     +- Table scan
|        rows           1
|        +- Table
|           table          t3
+- JOIN
   +- Bookmark lookup
   |  +- DERIVED
   |  |  table          derived(temporary(t1))
   |  |  possible_keys  
   |  |  +- Filesort
   |  |     +- TEMPORARY
   |  |        table          temporary(t1)
   |  |        +- Table scan
   |  |           rows           1
   |  |           +- Table
   |  |              table          t1
   |  +- Index lookup
   |     key            derived(temporary(t1))->
   |     possible_keys  
   |     key_len        5
   |     ref            mydb01.t2.c1
   |     rows           2
   +- Filter with WHERE
      +- Table scan
         rows           1
         +- Table
            table          t2


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

MySQL은 Query Cache 기능을 제공합니다. 이 기능은 쿼리 수행 결과를 메모리에 저장한 후에, 동일한 SQL이 수행되면 메모리에 저장된 수행 결과를 리턴하는 기능입니다.
쿼리 수행 대신 메모리에 있는 수행 결과를 리턴해주므로 수행 속도는 매우 빠릅니다. ORACLE 11g부터 제공하는 Result Cache의 Subset이라고 보면 될 것 같습니다.


이 기능을 잘 활용할 수 있는 업무는 주로 게시판 카운트 쿼리입니다. 특히, 입력 빈도는 높지 않으나 조회 빈도가 높은 게시판인 경우에는 Query Cache의 효과가 크겠죠.
또한, 편법이기는 하지만 임원들이 자주 보는 화면의 특정 조건 쿼리에 쿼리 캐시를 적용하면, 업무 담당자들이 스트레스가 조금은 줄어들 것 같습니다.


제가 MySQL의 쿼리 캐시에 대해서 궁금했던 점은 ORACLE과 같이 쿼리 블록 단위로도 캐싱이 가능한지 였습니다. 결론부터 말하면 안되네요. 사실, UI 화면은 매우 복잡하고 다양한 검색 조건이 존재합니다. 그리고 속도가 느린 쿼리의 대부분은 복잡한 쿼리겠죠. 하지만 간혹 이런 쿼리들의 공통 분모(쿼리 블록)가 존재하고, 이 부분이 항상 느리다면 쿼리 캐시 기능이 매우 좋은 해결책이 됩니다. 하지만 MySQL은 아쉽게도 이 부분은 지원되지 않고 있습니다. 아래의 테스트 내용을 참고하세요.


-- 파라미터 설정

query_cache_size=10485760
query_cache_type=2  -- 0:off 1: 항상 enable 2: on demand (SQL CACHE 힌트 사용시 사용) 

-- 확인 

mysql> show variables like 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 10485760 |
| query_cache_type             | DEMAND   |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

-- 조회 속도가 느림 

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 36500000 |
+----------+
1 row in set (23.66 sec)

-- 쿼리 캐시를 적용함 

mysql> select SQL_CACHE count(*) from t1;
+----------+
| count(*) |
+----------+
| 36500000 |
+----------+
1 row in set (23.42 sec)

-- 캐시된 결과를 리턴하므로 매우 빠름

mysql> select SQL_CACHE count(*) from t1;
+----------+
| count(*) |
+----------+
| 36500000 |
+----------+
1 row in set (0.00 sec)

-- 관련 Stat 수치가 증가하는 것을 확인할 수 있음

mysql> show status where Variable_name like 'Qcache_hits%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 3     |
+---------------+-------+

-- 대소문자를 구분함

mysql> SELECT SQL_CACHE count(*) from t1;
+----------+
| count(*) |
+----------+
| 36500000 |
+----------+
1 row in set (21.67 sec)

-- 스페이스도 구분함 

mysql> SELECT  SQL_CACHE count(*) from t1;
+----------+
| count(*) |
+----------+
| 36500000 |
+----------+
1 row in set (22.73 sec)

-- 쿼리 블록 단위로는 사용이 불가능함.

mysql> select max(c1) from (select SQL_CACHE count(*) c1 from t1) a;
ERROR 1234 (42000): Incorrect usage/placement of 'SQL_CACHE'

-- 테이블에 변경이 생기면 Query Cache는 Invalid 상태가 됨

mysql> delete from t1 limit 1;

mysql> select SQL_CACHE count(*) from t1;
+----------+
| count(*) |
+----------+
| 36499999 |
+----------+
1 row in set (23.03 sec)


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

MySQL PK 칼럼 순서 설정에 따른 성능 비교

MySQL 2017.04.20 21:26 Posted by 시연아카데미

이전 포스팅에서 언급한 것과 같이 MySQL InnoDB에 테이블을 생성하면 항상 Clustered Index로 생성됩니다. MySQL 개발자들이 이런 선택을 한 합리적인 이유도 있을 것이나 이로 인한 성능상의 문제점도 존재합니다. PK를 지정하지 않아도 내부적인 Hidden Clustered Index를 생성하므로, 이왕 생성할 거면 상황에 맞는 적절한 칼럼으로 PK를 지정함으로써, 레코드를 PK 칼럼으로 정렬하는 것이 좋습니다.


이번 포스팅에서 살펴볼 내용은 PK를 결합 인덱스로 생성할 때, 칼럼 순서에 따른 Insert 속도와 조회 속도 비교입니다.


상식적으로 생각해보면 Index Split이 적게 발생하는 순서로 PK를 설정하는 것이 Insert 성능에는 유리할 것이고, 조회 패턴에서 Equal 조건이 선두 칼럼으로 구성하는 것이 조회 속도에는 유리할 것입니다. 이 2가지를 모두 만족하면 좋겠지만, 이 2가지 사항이 서로 배치될 때는 상황에 맞는, 그리고 손익 계산을 통해 적절한 선택을 해야 합니다. 물론, 이때 손해 보는 부분에 대한 다른 대안이 있는지도 고려 대상이겠죠. 이렇듯 튜닝 관점에서 보면, 단순해 보이는 하나의 결정도 여러 가지 경우의 수를 고려해서 선택해야 합니다.


그럼 본론으로 들어가겠습니다.


테스트 시나리오


10만 명의 고객이 매일 1건씩 상품을 구매한 내역을 저장한 테이블이 있다고 가정하겠습니다. 조회 조건은 고객 번호가 Equal 조건으로 입력되고 날자 조건은 대략 세 달 정도의 기간으로 들어옵니다. 이런 경우, INSERT 성능을 빠르게 하려면 log_date + cust_no로 PK를 생성하고, 조회 속도를 빠르게 하려면 cust_no + log_date로 PK를 생성하는 것이 좋습니다.(당연하죠?)

그럼 입력 성능과 조회 성능의 차이가 얼마나 나는지 비교해보겠습니다.


-- 테이블 생성 
1) T1 테이블은 CUST_NO  + LOG_DATE로 PK 생성
2) T2 테이블은 LOG_DATE + CUST_NO로  PK 생성

drop table t1;
drop table t2;

create table t1 (cust_no integer, log_date date, dummy char(100), primary key(cust_no, log_date));
create table t2 (cust_no integer, log_date date, dummy char(100), primary key(log_date, cust_no));

-- T1 테이블 용 프로시저 생성 

delimiter $$
drop procedure if exists insert_t1_proc$$
create procedure insert_t1_proc()
begin
   declare i_log_date int default 1;
   declare i_cust_no  int default 1;
   
   while (i_log_date <= 365) do  -- 1년 기간
       while (i_cust_no <= 100000) do -- 고객 10만명 
	       insert into t1 values(i_cust_no, date_add(curdate(), interval i_log_date day) , 'dummy');
		   set i_cust_no=i_cust_no+1;
	   end while;
	   set i_log_date=i_log_date+1;
	   set i_cust_no=1;
   end while;
end $$


-- T2 테이블 용 프로시저 생성 
delimiter $$
drop procedure if exists insert_t2_proc$$
create procedure insert_t2_proc()
begin
   declare i_log_date int default 1;
   declare i_cust_no  int default 1;
   
   while (i_log_date <= 365) do  -- 1년 기간
       while (i_cust_no <= 100000) do -- 고객 10만명 
	       insert into t2 values(i_cust_no, date_add(curdate(), interval i_log_date day) , 'dummy');
		   set i_cust_no=i_cust_no+1;
	   end while;
	   set i_log_date=i_log_date+1;
	   set i_cust_no=1;
   end while;
end $$

delimiter ;

-- 입력 성능 비교 결과
-- T1 테이블에 3650만 건 입력 시에는 3시간 3분이 소요되고 T2 테이블 입력 시에는 47분이 소요됐습니다.
-- T1 테이블에 대한 입력이 3배가량 늦다는 것을 알 수 있습니다. 

mysql> start transaction;
mysql> call insert_t1_proc();
Query OK, 1 row affected (3 hours 3 min 22.15 sec)

mysql> commit;

mysql> start transaction;
mysql> call insert_t2_proc();
Query OK, 1 row affected (47 min 10.63 sec)

mysql> commit;


-- PK 인덱스 크기 비교
-- T1 테이블의 PK가 50%가량 큽니다. 이는 잦은 Index Block Split 때문인 것 같습니다.
 
select database_name, table_name, index_name, round(stat_value*@@innodb_page_size/1024/1024,0) as "size(mb)"
from   mysql.innodb_index_stats
where stat_name='size' and table_name in ('t1','t2');
+---------------+------------+------------+----------+
| database_name | table_name | index_name | size(mb) |
+---------------+------------+------------+----------+
| mydb01        | t1         | PRIMARY    |     7559 |
| mydb01        | t2         | PRIMARY    |     4759 |
+---------------+------------+------------+----------+

-- Explain 결과 확인 
-- 실제 Run 테스트 전에 Explain 결과를 확인해보면, 2개의 쿼리 모두 PK 인덱스를 이용한다는 것을 알 수 있습니다.

explain 
select * 
from   t1 
where  cust_no=99 
and    log_date between str_to_date('20170420','%Y%m%d') 
                    and str_to_date('20170731','%Y%m%d')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 7
          ref: NULL
         rows: 103
     filtered: 100.00
        Extra: Using where

explain 
select * 
from   t2
where  cust_no=99 
and    log_date between str_to_date('20170420','%Y%m%d') 
                    and str_to_date('20170731','%Y%m%d')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 7
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

		
		
-- DB 재기동, 캐시 클리어 후에 T1 테이블 조회용 쿼리 Run 테스트 
-- 아래의 수행 결과를 보면, 1번째 쿼리 속도는 0.00초, 2번째 쿼리 속도는 7.8초 정도입니다. 
-- 그리고 Disk IO 블록 수도 큰 차이를 보입니다.
stop
sync;  echo 1 > /proc/sys/vm/drop_caches;
start

set profiling=1;

select * 
from   t1 
where  cust_no=99 
and    log_date between str_to_date('20170420','%Y%m%d') 
                    and str_to_date('20170731','%Y%m%d');
103 rows in set (0.00 sec)

show profiles;
+----------+------------+----------------------------------------+
| Query_ID | Duration   | Query                                  |
+----------+------------+----------------------------------------+
|        1 | 0.00281925 | select * from t1 where cust_no=99 ...  |
+----------+------------+----------------------------------------+

show profile cpu, block io for query 1;
+----------------------+----------+----------+------------+------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000126 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000013 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000032 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.002424 | 0.000000 |   0.000000 |          120 |             0 |
| preparing            | 0.000026 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.000104 | 0.000000 |   0.001000 |            0 |             0 |
| end                  | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000025 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+

-- DB 재기동, 캐시 클리어 후에 T2 테이블 조회용 쿼리 Run 테스트 

stop
sync;  echo 1 > /proc/sys/vm/drop_caches;
start


set profiling=1;

select * 
from   t2 
where  cust_no=99 
and    log_date between str_to_date('20170420','%Y%m%d') 
                    and str_to_date('20170731','%Y%m%d');

103 rows in set (7.83 sec)

show profiles;
+----------+------------+----------------------------------------+
| Query_ID | Duration   | Query                                  |
+----------+------------+----------------------------------------+
|        1 | 7.82868050 | select * from t2 where cust_no=99 ...  |
+----------+------------+----------------------------------------+

show profile cpu, block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000153 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000018 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000023 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000045 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.011644 | 0.000000 |   0.001000 |         1848 |             0 |
| preparing            | 0.000050 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 7.816537 | 1.690000 |   3.313000 |      2723232 |             0 |
| end                  | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000036 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query   | 0.000099 | 0.001000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000017 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+


그렇다면 2번째 쿼리는 어떤 식으로 성능 개선을 할 수 있을까요? 이는 일반적인 쿼리 튜닝과 동일합니다. 즉, log_date를 IN 조건으로 입력함으로써 Between 범위 대신 Equal 조건 효과를 내는 것입니다. 아래와 같이 수행하면, 0.07초 정도에 수행되며 Disk IO 블록 수도 대폭 감소한 것을 알 수 있습니다. 그런데 이 방법은 log_date 조건이 "날자"인 경우에만 가능한 방법입니다. 만일, "날자:시:분:초" 형식이라면 이 방법도 적용할 수 없습니다.


create table log_date_master(log_date date);

delimiter $$
drop procedure if exists insert_log_date_master_proc$$
create procedure insert_log_date_master_proc()
begin
   declare i_log_date int default 1;
   declare i_cust_no  int default 1;
   
   while (i_log_date <= 365) do  -- 1년 기간
	  insert into log_date_master values(date_add(curdate(), interval i_log_date day));
	  set i_log_date=i_log_date+1;
   end while;
end $$

delimiter ; 

call insert_log_date_master_proc();


stop
sync;  echo 1 > /proc/sys/vm/drop_caches;
start

set profiling=1;

select * 
from  t2 
where cust_no=99 
and   log_date in (select log_date 
                   from   log_date_master 
				   where  log_date between str_to_date('20170420','%Y%m%d') and str_to_date('20170731','%Y%m%d'));
102 rows in set (0.07 sec)

show profiles;
+----------+------------+----------------------------------------+
| Query_ID | Duration   | Query                                  |
+----------+------------+----------------------------------------+
|        1 | 0.06814325 | select * from t2 where cust_no=99 ...  |
+----------+------------+----------------------------------------+

show profile cpu, block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000890 | 0.000000 |   0.000000 |           72 |             0 |
| checking permissions | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000063 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000028 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000061 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000017 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.002837 | 0.001000 |   0.000000 |          472 |             0 |
| preparing            | 0.000035 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.064098 | 0.001000 |   0.009000 |         6368 |             0 |
| end                  | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000034 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000013 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+


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

MySQL의 Secondary Index에는 PK 칼럼이 포함될까?

MySQL 2017.04.20 16:27 Posted by 시연아카데미

MySQL의 InnoDB의 특징 중 하나는 모든 테이블을 Clustered Index로 생성한다는 것입니다.


즉, 모든 테이블이 인덱스라는 것이죠. ORACLE로 치면 IOT입니다.


따라서 튜닝 시에는 이러한 속성을 잘 이용해야 합니다. 다시말해, Clustered Index는 특정 칼럼으로 정렬을 유지한다는 속성이 있으므로 이 특성을 잘 이용해야 합니다. (이 부분은 별도 포스팅 예정입니다)


MySQL은 PK 칼럼을 이용해서 데이터를 정렬합니다. 만일 PK가 없다면 첫 번째 Unique Key 칼럼을 이용합니다. 만일 이마저도 없다면 내부적으로 6바이트짜리 키를 생성한 후에 데이터를 정렬하는 방식을 사용합니다.


그런데 Clustered Index (또는 IOT)의 문제점 중의 하나는 Secondary Index를 처리하는 것이 조금 힘들다는 점입니다. 왜냐면 Clustered Index는 빈번하게 Split이 발생하므로 레코드가 저장된 물리적인 블록의 위치가 쉽게 바뀌기 때문인데요.


이를 위해 ORACLE은 Direct Access, Guessing, PK 이용의 기법을 사용합니다. 그런데 MySQL 매뉴얼에 보면 MySQL은 Secondary Index 생성 시에 PK 칼럼을 추가한다는 내용이 있습니다. (헉!)


정말 그럴까요? 정말 그렇습니다. 테스트 내용은 아래를 참고하세요.




-- 테이블 생성(t1 테이블 PK 칼럼을 Integer, t1 테이블 PK 칼럼은 CHAR(100))

drop table t1;
drop table t2;

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

-- T1 테이블에 100만 건 입력용 프로시저 생성 

delimiter $$
drop procedure if exists insert_t1_proc$$
create procedure insert_t1_proc()
begin
   declare i int default 1;
   
   while (i <= 1000000) do
       insert into t1 values(i, i, 'dummy');
	   set i=i+1;
   end while;
end $$

-- T2 테이블에 100만 건 입력용 프로시저 생성

drop procedure if exists insert_t2_proc$$
create procedure insert_t2_proc()
begin
   declare i int default 1;
   
   while (i <= 1000000) do
       insert into t2 values(lpad(i,100,'0'), i, 'dummy');
	   set i=i+1;
   end while;
end $$

delimiter ;

-- 프로시저 수행  

start transaction;
call insert_t1_proc();
commit;

start transaction;
call insert_t2_proc();
commit;

-- 현재 인덱스 크기 비교 (현재 T2 테이블에 생성된 PK의 크기가 2배 가량 큽니다)

analyze table t1;
analyze table t2;

select database_name, table_name, index_name, round(stat_value*@@innodb_page_size/1024/1024,0) as "size(mb)"
from   mysql.innodb_index_stats
where stat_name='size' and table_name in ('t1','t2');

+---------------+------------+------------+----------+
| database_name | table_name | index_name | size(mb) |
+---------------+------------+------------+----------+
| mydb01        | t1         | PRIMARY    |      132 |
| mydb01        | t2         | PRIMARY    |      233 |
+---------------+------------+------------+----------+

-- secondary index 생성 및 크기 비교 (T2의 PK의 크기가 큰 탓에 T2의 Secondary Index의 크기가 T1에 비해서 무려 7배 가량 큽니다)

create index t1_idx01 on t1(c2);
create index t2_idx01 on t2(c2);

analyze table t1;
analyze table t2;

select database_name, table_name, index_name, round(stat_value*@@innodb_page_size/1024/1024,0) as "size(mb)"
from   mysql.innodb_index_stats
where stat_name='size' and table_name in ('t1','t2');
+---------------+------------+------------+----------+
| database_name | table_name | index_name | size(mb) |
+---------------+------------+------------+----------+
| mydb01        | t1         | PRIMARY    |      132 |
| mydb01        | t1         | t1_idx01   |       17 |
| mydb01        | t2         | PRIMARY    |      233 |
| mydb01        | t2         | t2_idx01   |      123 |
+---------------+------------+------------+----------+

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

제목이 너무 자극적이죠?


MySQL 성능 테스트를 위해 Loop Insert 하는 Procedure를 생성했는데 처리 속도가 너무 느렸습니다. 100만 건 입력하는데 10분 이상 소요됐으니까요.

같은 유형의 프로시저를 PostgreSQL에서 수행하면 10초 이내에 끝나는 것에 비하면 극심한 성능 차이가 발생하는 것인데요.


이 문제를 처음 접했을 때는 MySQL의 InnoDB 엔진의 특성상 모든 테이블을 클러스터 인덱스로 생성하는 이유 때문인 것으로 추정했었습니다. 즉, Index Split 현상 떄문에 입력 시의 지연 현상이 발생한다고 생각했으나 결과적으로 이 문제는 아니었습니다. (물론, 이로 인한 약간의 성능 손실은 있습니다)


문제의 원인은 MySQL이 Loop를 처리하는 방식 때문입니다. PostgreSQL은 프로시저 내의 Loop를 1-Transaction으로 처리합니다. 그런데 MySQL은 매건 COMMIT을 하는 방식으로 수행합니다.


따라서 이 문제를 해결하는 법은 간단합니다. Loop 수행 전에 SET TRANSACTION을 지정하거나 SET AUTOCOMMIT=OFF로 설정하면 됩니다. 간단하죠?


아래는 테스트를 위한 스크립트입니다.


-- 테이블 및 프로시저 생성 

drop table t1;
create table t1 (c1 integer, c2 integer, dummy char(100));

delimiter $$
drop procedure if exists insert_t1_proc$$
create procedure insert_t1_proc()
begin
   declare i int default 1;
   declare j int default 0;
   
   while (i <= 10000) do
       while (j <= 99) do
	       insert into t1 values(i+(j*10000), i+(j*10000), 'dummy');
		   set j=j+1;
	   end while;
	   set i=i+1;
	   set j=0;
   end while;
end $$

delimiter ;

-- 프로시저 수행 결과 (747초나 소요됨)

call insert_t1_proc();
Query OK, 1 row affected (12 min 27.08 sec) (747초)


-- START TRANSACTION 적용 후의 수행 결과 (19초로 단축됨)

start transaction;
call insert_t1_proc(); 
Query OK, 1 row affected (19.20 sec)
commit;


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

MySQL은 튜닝 대상 SQL을 수집하기 위해 Slow Query Log 기능을 제공합니다. (설정 방법은 여기 참조)

 

Slow Query Log는 파일 및 테이블에 기록이 가능합니다만, 아마 운영 환경에서는 관리 문제로 인해서 주로 파일에 기록할 것으로 보입니다. 이렇게 파일에 기록된 Slow Query Log를 눈으로 확인하는 것은 매우 힘든데요.

 

percona 툴킷에서 제공하는 pt-query-digest 명령어를 이용하면 이러한 작업을 매우 효율적으로 수행할 수 있습니다. 뿐만 아니라 Literal SQL (띄어쓰기가 다르거나 심지어 대소문자가 달라도)을 하나의 SQL로 묶어서 리포트를 해주므로 분석 시에 매우 유용할 것 같습니다.

 

아래와 같이 Literal SQL, 띄어쓰기, 대소문자가 다른 SQL을 각각 수행 (총 4번 수행)

select * from  (select c1 from t1 where c1=1 group by c1) a, t2 b where a.c1=b.c1;
select * from  (select c1 from t1 where c1=2 group by c1) a, t2 b where a.c1=b.c1;
SELECT * FROM (SELECT c1 FROM t1 WHERE c1=2 GROUP BY c1) a, t2 b WHERE a.c1=b.c1;
SELECT * FROM        (SELECT c1 FROM t1 WHERE c1=2 GROUP BY c1) a, t2 b WHERE a.c1=b.c1;

 

 

pt-query_digest로 분석 수행

$ pt-query-digest /var/log/mysql/mysql-slow.log

 

아래의 결과에서 보는것과 같이, Overall:4, Count:4 입니다. 즉, 위의 4개의 SQL이 1개로 Grouping되서 리포트되는 것을 알 수 있습니다.

# Overall: 4 total, 1 unique, 0.04 QPS, 0.06x concurrency ________________
# Time range: 2017-04-12T21:32:24 to 2017-04-12T21:34:08
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time             6s      1s      2s      2s      2s    31ms      2s
# Lock time          481us   108us   149us   120us   144us    16us   108us
# Rows sent            385       1     128   96.25  124.25   53.37  124.25
# Rows examine      16.76M   4.19M   4.19M   4.19M   4.06M       0   4.06M
# Query size           330      80      88   82.50   84.10    2.76   80.10



# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count        100       4
# Exec time    100      6s      1s      2s      2s      2s    31ms      2s
# Lock time    100   481us   108us   149us   120us   144us    16us   108us
# Rows sent    100     385       1     128   96.25  124.25   53.37  124.25
# Rows examine 100  16.76M   4.19M   4.19M   4.19M   4.06M       0   4.06M
# Query size   100     330      80      88   82.50   84.10    2.76   80.10
# String:
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 't1'\G
#    SHOW CREATE TABLE `t1`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from  (select c1 from t1 where c1=2 group by c1) a, t2 b where a.c1=b.c1\G

 

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

MySQL에서 PostgreSQL의 generate_series() 흉내내기

MySQL 2017.04.13 05:49 Posted by 시연아카데미

테스트 데이터 Set을 만들때, 일련 번호를 생성하는 PostgreSQL의 generate_series() 함수는 매우 유용합니다. 그런데 MySQL은 이런 함수를 내장하고 있지 않습니다. (조금 불편하네요)

 

이를 보완하기 위해서 Function을 만들어서 공유해준 분이 있습니다.

 

 

Function은 많은 기능들을 제공하나, 일련 번호 생성을 위해서는 이 중에서 generate_series_base(), generate_series_n_base(), generate_series() 함수만 생성하면 됩니다.

 

generate_series() 함수 수행 후에는 해당 결과가 series_tmp 테이블에 저장됩니다. 따라서 다음과 같은 순서로 일련 번호를 테이블에 입력하면 됩니다.

 

  1. CALL generate_series(1,100000);
  2. insert into t1 (c1) select * from series_tmp;

 

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

MySQL의 Explain 결과를 보는 몇 가지 방법들

MySQL 2017.04.13 04:32 Posted by 시연아카데미

드디어 MySQL 성능 이야기를 시작합니다.

 

성능 이야기의 첫 주제는 Explain입니다. 튜닝의 시작 단계에서는 Explain을 잘 활용해야 하기 떄문입니다.

 

그런데 MySQL의 Explain은 조금 당황스럽네요. 지금 MySQL 필드를 뛰시는 튜너 분들은 Explain 결과를 이용해서 어떤식으로 튜닝을 진행하는지가 궁금해졌습니다.

 

왜냐면 MySQL의 Explain은 Run-Time 결과를 제공하지 않기 때문입니다. 다시 말해, Explain 결과 중에서 속도가 느리거나, Disk IO, 또는 Buffer IO가 과다한 Step을 찾을 수 없습니다.

 

물론, profiling을 이용해서 SQL 레벨의 수행 시간, CPU Time, Disk IO Block 수는 확인할 수 있지만, 쿼리 내의 문제 부분을 특정할 수 없다는 것은 튜닝 작업의 걸림돌이 되는 것만은 확실할 것 같습니다.

 

그리고 기본적으로 제공되는 Explain의 결과는 테이블 Format입니다. 즉, 트리구조로 제공되지 않습니다. 이 역시 가독성을 떨어뜨리는 요소입니다.

 

이를 조금이나마 보완하는 방법은 3가지입니다.

 

1. JSON Format (이 역시 JSON에 익숙하지 않으면 보기 어려움)

2. pt-visual-explain (percona toolkit 설치 필요)

   - 해당 Toolkit은 PERL을 사용하므로 PERL 관련 모듈 몇 개(DBI, DBD::mysql)를 설치해야 합니다.   

3. MySQL Workbench의 Visual Explain (JSON을 그래픽으로 변환해서 보여주는 기능)

 

출력 결과는 다음과 같습니다.

 

1. JSON Format

 

| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3.60"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "a",
          "access_type": "ALL",
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "1.20",
            "data_read_per_join": "8"
          },
          "used_columns": [
            "c1"
          ],
          "attached_condition": "(`mydb01`.`a`.`c1` is not null)"
        }
      },
      {
        "table": {
          "table_name": "c",
          "access_type": "ALL",
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "2.40",
            "data_read_per_join": "8"
          },
          "used_columns": [
            "c1"
          ],
          "attached_condition": "(`mydb01`.`c`.`c1` = `mydb01`.`a`.`c1`)"
        }
      },
      {
        "table": {
          "table_name": "b",
          "access_type": "ref",
          "possible_keys": [
            "t2_idx01"
          ],
          "key": "t2_idx01",
          "used_key_parts": [
            "c1"
          ],
          "key_length": "5",
          "ref": [
            "mydb01.a.c1"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.20",
            "prefix_cost": "3.60",
            "data_read_per_join": "8"
          },
          "used_columns": [
            "c1"
          ]
        }
      }
    ]
  }
} |

2. pt-visual-explain

[root@pgserver ~]# mysql -u root -p mydb01 -e "explain select * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1" | pt-visual-explain
Enter password:
JOIN
+- Index lookup
|  key            b->t2_idx01
|  possible_keys  t2_idx01
|  key_len        5
|  ref            mydb01.a.c1
|  rows           1
+- JOIN
   +- Join buffer
   |  +- Filter with WHERE
   |     +- Table scan
   |        rows           1
   |        +- Table
   |           table          c
   +- Filter with WHERE
      +- Table scan
         rows           1
         +- Table
            table          a

 

3. MySQL Workbench의 Visual Explain

 

 

 

 

참고사이트

 

http://stackoverflow.com/questions/36305809/is-there-a-way-to-show-mysql-explain-plan-like-oracle-as-a-tree

 

 

사족

 

새로운 블로그에만 기술 컨텐츠를 작성하려고 했으나, 워드프레스가 구글 검색이 잘 안 되는 문제가 있습니다. SEO가 잘된다고 해서 워드프레스를 선택한 이유도 있는데, 무료라서 그런 것인지? 아니면 설치형이 아닌 가입형이라서 그런 것인지? 동일한 내용을 구글 웹마스터에서 크롤링을 시켜봐도 워드프레스가 티스토리에 비해서 검색이 잘 안됩니다. 따라서 약간의 공수가 더 들겠지만, 동일한 내용은 양쪽 블로그에 모두 포스팅하도록 하겠습니다.

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


 

티스토리 툴바