My Books

My Slides

rss 아이콘 이미지

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


 

티스토리 툴바