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

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

 

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