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

이전 포스팅에서 언급한 것과 같이 MySQL InnoDB에 테이블을 생성하면 항상 Clustered Index로 생성됩니다.

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

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

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

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

1. 테스트 시나리오

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 |
+----------------------+----------+----------+------------+--------------+---------------+
Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s