OS: Microsoft Windows 2000 5.00.2195 Service Pack 3
MySQL: 4.0.7-gamma-nt
CPU: x86 Family 6 Model 8 Stepping 10
RAM: 512MB
작성자: 강명규(kang@dbakorea.pe.kr)
MERGE TABLE은 오라클에서의 union all, 단순 VIEW, PARTITIIONING과 유사한 기능을 제공한다.
여러 테이블의 합이라는 점에서 union all
4.0이하의 버전에서 직접적인 insert를 못하는 점에서는 view
여러개의 데이터파일(테이블은 각각 하나의 데이터파일을 갖는다)을 가진다는 개념에서 파티셔닝.
아래의 예를 보면 알 수 있듯이 HISTORY데이터를 처리하는데 쓰면 유용하다.
몇가지 단점이 있는데 자세한 내용은 스스로 알아보도록 하자.
create table member_2000
(
id varchar(15),
regdate datetime,
name varchar(12),
KEY member_2000_idx (id)
);
create table member_2001
(
id varchar(15),
regdate datetime,
name varchar(12),
KEY member_2001_idx (id)
);
create table member_2002
(
id varchar(15),
regdate datetime,
name varchar(12),
KEY member_2002_idx (id)
);
create table member_2003
(
id varchar(15),
regdate datetime,
name varchar(12),
KEY member_2003_idx (id)
);
insert into member_2000 values('maddog2000', '2000-01-01', '강명규2000');
insert into member_2000 values('batman2000', '2000-01-01', '배트맨2000');
insert into member_2000 values('superman2000', '2000-01-01', '슈퍼맨2000');
insert into member_2001 values('maddog2001', '2001-01-01', '강명규2001');
insert into member_2001 values('batman2001', '2001-01-01', '배트맨2001');
insert into member_2001 values('superman2001', '2001-01-01', '슈퍼맨2001');
insert into member_2002 values('maddog2002', '2002-01-01', '강명규2002');
insert into member_2002 values('batman2002', '2002-01-01', '배트맨2002');
insert into member_2002 values('superman2002', '2002-01-01', '슈퍼맨2002');
insert into member_2003 values('maddog2003', '2003-01-01', '강명규2003');
insert into member_2003 values('batman2003', '2003-01-01', '배트맨2003');
insert into member_2003 values('superman2003', '2003-01-01', '슈퍼맨2003');
CREATE TABLE member_all
(
id varchar(15),
regdate datetime,
name varchar(12),
KEY member_idx (id)
) TYPE=MERGE UNION=(member_2000,member_2001,member_2002,member_2003) INSERT_METHOD=LAST;
INSERT_METHOD는 4.0이상부터 사용할 수 있으며 3가지를 지정할 수 있다. NO, FIRST, LAST
위에서는 LAST로 지정했는데, 이는 INSERT가 발생하면 UNION의 마지막에 지정한 테이블인 MEMBER_2003에
데이터가 INSERT됨을 의미한다. first: 처음에 지정한 테이블, no: insert금지
4.0이하라면 merge table이 아닌 각각의 테이블에 insert를 수행해야 한다.
mysql> select * from member_2000;
+--------------+---------------------+------------+
| id | regdate | name |
+--------------+---------------------+------------+
| maddog2000 | 2000-01-01 00:00:00 | 강명규2000 |
| batman2000 | 2000-01-01 00:00:00 | 배트맨2000 |
| superman2000 | 2000-01-01 00:00:00 | 슈퍼맨2000 |
+--------------+---------------------+------------+
3 rows in set (0.00 sec)
mysql> select * from member_2001;
+--------------+---------------------+------------+
| id | regdate | name |
+--------------+---------------------+------------+
| maddog2001 | 2001-01-01 00:00:00 | 강명규2001 |
| batman2001 | 2001-01-01 00:00:00 | 배트맨2001 |
| superman2001 | 2001-01-01 00:00:00 | 슈퍼맨2001 |
+--------------+---------------------+------------+
3 rows in set (0.00 sec)
mysql> select * from member_2002;
+--------------+---------------------+------------+
| id | regdate | name |
+--------------+---------------------+------------+
| maddog2002 | 2002-01-01 00:00:00 | 강명규2002 |
| batman2002 | 2002-01-01 00:00:00 | 배트맨2002 |
| superman2002 | 2002-01-01 00:00:00 | 슈퍼맨2002 |
+--------------+---------------------+------------+
3 rows in set (0.00 sec)
mysql> select * from member_2003;
+--------------+---------------------+------------+
| id | regdate | name |
+--------------+---------------------+------------+
| maddog2003 | 2003-01-01 00:00:00 | 강명규2003 |
| batman2003 | 2003-01-01 00:00:00 | 배트맨2003 |
| superman2003 | 2003-01-01 00:00:00 | 슈퍼맨2003 |
+--------------+---------------------+------------+
3 rows in set (0.00 sec)
mysql> select * from member_all;
+--------------+---------------------+------------+
| id | regdate | name |
+--------------+---------------------+------------+
| maddog2000 | 2000-01-01 00:00:00 | 강명규2000 |
| batman2000 | 2000-01-01 00:00:00 | 배트맨2000 |
| superman2000 | 2000-01-01 00:00:00 | 슈퍼맨2000 |
| maddog2001 | 2001-01-01 00:00:00 | 강명규2001 |
| batman2001 | 2001-01-01 00:00:00 | 배트맨2001 |
| superman2001 | 2001-01-01 00:00:00 | 슈퍼맨2001 |
| maddog2002 | 2002-01-01 00:00:00 | 강명규2002 |
| batman2002 | 2002-01-01 00:00:00 | 배트맨2002 |
| superman2002 | 2002-01-01 00:00:00 | 슈퍼맨2002 |
| maddog2003 | 2003-01-01 00:00:00 | 강명규2003 |
| batman2003 | 2003-01-01 00:00:00 | 배트맨2003 |
| superman2003 | 2003-01-01 00:00:00 | 슈퍼맨2003 |
+--------------+---------------------+------------+
12 rows in set (0.00 sec)
이제 insert하면 어떻게 되는지 함 보자. 위에서 지정했듯이 member_2003에 insert된다.
mysql> insert into member_all values('member_new', now(), '강명규');
Query OK, 1 row affected (0.01 sec)
mysql> select * from member_all;
+--------------+---------------------+------------+
| id | regdate | name |
+--------------+---------------------+------------+
| maddog2000 | 2000-01-01 00:00:00 | 강명규2000 |
| batman2000 | 2000-01-01 00:00:00 | 배트맨2000 |
| superman2000 | 2000-01-01 00:00:00 | 슈퍼맨2000 |
| maddog2001 | 2001-01-01 00:00:00 | 강명규2001 |
| batman2001 | 2001-01-01 00:00:00 | 배트맨2001 |
| superman2001 | 2001-01-01 00:00:00 | 슈퍼맨2001 |
| maddog2002 | 2002-01-01 00:00:00 | 강명규2002 |
| batman2002 | 2002-01-01 00:00:00 | 배트맨2002 |
| superman2002 | 2002-01-01 00:00:00 | 슈퍼맨2002 |
| maddog2003 | 2003-01-01 00:00:00 | 강명규2003 |
| batman2003 | 2003-01-01 00:00:00 | 배트맨2003 |
| superman2003 | 2003-01-01 00:00:00 | 슈퍼맨2003 |
| member_new | 2003-02-18 16:27:15 | 강명규 |
+--------------+---------------------+------------+
13 rows in set (0.00 sec)
mysql> select * from member_2003;
+--------------+---------------------+------------+
| id | regdate | name |
+--------------+---------------------+------------+
| maddog2003 | 2003-01-01 00:00:00 | 강명규2003 |
| batman2003 | 2003-01-01 00:00:00 | 배트맨2003 |
| superman2003 | 2003-01-01 00:00:00 | 슈퍼맨2003 |
| member_new | 2003-02-18 16:27:15 | 강명규 |
+--------------+---------------------+------------+
4 rows in set (0.00 sec)
mysql>
위의 테이블을 보면 2003년도의 데이터만이 UPDATE연산이 발생하고, 나머지 테이블인
member_2000, member_2001, member_2002등은 주로 select만이 발생한다.
따라서 member_2003을 제외한 테이블등은 mysql쉘명령인 myisampack을 사용하여 압축시켜두면
공간의 절약할 수 있다. 테스트에는 member_2000에만 myisampack을 사용하여 압축해봤다.
row가 너무 작으면 압축이 수행되지 않으므로, 2000개의 row를 추가적으로 insert한 후에 테스트했다.
insert into member_2000 values('a0001',date_add('2000-01-01 00:00:00', interval 1 minute),'a0001');
insert into member_2000 values('a0002',date_add('2000-01-01 00:00:00', interval 2 minute),'a0002');
.
.
insert into member_2000 values('a1999',date_add('2000-01-01 00:00:00', interval 1999 minute),'a1999');
insert into member_2000 values('a2000',date_add('2000-01-01 00:00:00', interval 2000 minute),'a2000');
C:mysqldatadbakorea>dir member_*
C 드라이브의 볼륨: WIN2000
볼륨 일련 번호: F845-C077
C:mysqldatadbakorea 디렉터리
2003-02-18 04:21p 8,604 member_2000.frm
2003-02-18 05:05p 56,112 member_2000.MYD
2003-02-18 05:04p 29,696 member_2000.MYI
2003-02-18 04:21p 8,604 member_2001.frm
2003-02-18 04:21p 112 member_2001.MYD
2003-02-18 04:26p 2,048 member_2001.MYI
2003-02-18 04:21p 8,604 member_2002.frm
2003-02-18 04:21p 112 member_2002.MYD
2003-02-18 04:26p 2,048 member_2002.MYI
2003-02-18 04:21p 8,604 member_2003.frm
2003-02-18 04:27p 144 member_2003.MYD
2003-02-18 04:56p 2,048 member_2003.MYI
2003-02-18 04:22p 8,604 member_all.frm
2003-02-18 04:22p 68 member_all.MRG
14개 파일 135,408 바이트
0 디렉터리 12,436,623,360 바이트 남음
C:mysqldatadbakorea>c:mysqlinmyisamchk -dvv member_2000
MyISAM file: member_2000
Record format: Packed
Character set: latin1 (8)
File-version: 1
Creation time: 2003-02-18 16:21:45
Status: open,changed
Data records: 2003 Deleted blocks: 0
Datafile parts: 2003 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4
Datafile length: 56112 Keyfile length: 29696
Max datafile length: 4294967294 Max keyfile length: 4398046510079
Recordlength: 36
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 15 multip. char packed stripped NULL 0 3072 1024
Field Start Length Nullpos Nullbit Type
1 1 1
2 2 15 1 1 no endspace
3 17 8 1 2 no zeros
4 25 12 1 4 no endspace
데이터베이스를 shutdown후 다음 작업을 수행한다.
C:mysqldatadbakorea>c:mysqlinmyisampack member_2000.myi
Compressing member_2000.MYD: (2003 records)
- Calculating statistics
- Compressing file
61.7%
Remember to run myisamchk -rq on compressed tables
C:mysqldatadbakorea>dir member_2000*
C 드라이브의 볼륨: WIN2000
볼륨 일련 번호: F845-C077
C:mysqldatadbakorea 디렉터리
2003-02-18 04:21p 8,604 member_2000.frm
2003-02-18 05:05p 21,497 member_2000.MYD
2003-02-18 05:09p 1,024 member_2000.MYI
3개 파일 31,125 바이트
0 디렉터리 12,436,697,088 바이트 남음
C:mysqldatadbakorea>c:mysqlinmyisamchk -rq member_2000
- check key delete-chain
- check record delete-chain
- recovering (with sort) MyISAM-table 'member_2000'
Data records: 2003
- Fixing index 1
C:mysqldatadbakorea>c:mysqlinmyisamchk -dvv member_2000
MyISAM file: member_2000
Record format: Compressed
Character set: latin1 (8)
File-version: 1
Creation time: 2003-02-18 16:21:45
Recover time: 2003-02-18 17:10:42
Status: checked,analyzed,optimized keys
Checksum: 1818400583
Data records: 2003 Deleted blocks: 0
Datafile parts: 2003 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 2
Datafile length: 21490 Keyfile length: 17408
Max datafile length: 4294967294 Max keyfile length: 67107839
Recordlength: 36
table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 15 multip. char packed stripped NULL 0 16384 1024
Field Start Length Nullpos Nullbit Type Huff tree Bits
1 1 1 constant 1 0
2 2 15 1 1 no endspace 2 9
3 17 8 1 2 zerofill(2) 3 9
4 25 12 1 4 no endspace 2 9
C:mysqldatadbakorea>
mysql> select year(regdate) as year, count(*) as entries from member_all group by year;
+------+---------+
| year | entries |
+------+---------+
| 2000 | 2003 |
| 2001 | 3 |
| 2002 | 3 |
| 2003 | 4 |
+------+---------+
4 rows in set (0.06 sec)
member_2000테이블은 isampack에 의해 read only로 변했으므로 insert는 할 수 없다.
mysql> insert into member_2000 values('a2001',date_add('2000-01-01 00:00:00', interval 2001 minute), 'a2001');
ERROR 1036: Table 'member_2000' is read only
테스트를 마쳤으면 작업한 테이블들을 지워버리자.(하위버전은 multiple table drop을 지원하지 않음)
mysql> drop table member_all, member_2003, member_2002, member_2001, member_2000;
Query OK, 0 rows affected (0.06 sec)
|
This article comes from dbakorea.pe.kr (Leave this line as is)
|
