Mysql 서버 5.0의 신기능

 

MySQL 데이터베이스 서버 5.0의 신기능

MySQL 사용자들이 2년 이상의 개발기간 동안 애타게 기다려 왔던 MySQL 데이터베이스 서버 5.0 버전의 발표가 이제 11월로 다가왔다.
MySQL은 많은 버전업에도 불구하고 크게 눈에 띄는 기능상의 변화가 적었다. 하지만 그 동안의 MySQL 사용자들이 다른 데이터베이스 서버에
비해 부족하다고 생각했던 기능들을 이번 5.0 버전에서는 대부분 충족시켜줄 정도로 혁신적인 업그레이드가 이뤄졌다.
MySQL AB사가 새로운 도약을 위해 심혈을 기울인 MySQL 5.0에서는 어떠한 변화가 이뤄졌는지 자세히 살펴보자.

 

MySQL은 3.x 버전에서 본격적으로 사용되기 시작했고 4.x 버전에서 기본 기능들을 안정화시키는 기간을 거쳐 이제는 성숙기에 접어드는
5.0 버전을 발표하기에 이르렀다. 이제까지는 기본 기능들을 안정화시키고 여러 스토리지 엔진의 도입에 따른 혼란을 해결하는데 중점을
뒀었다면 이번 5.0 버전은 그 동안에 쌓은 토대 위에 본격적으로 기능 추가에 주력했다. 5.0 버전에 도입되는 새로운 기능들은 MyISAM이나
InnoDB 등의 특정 스토리지 엔진에만 해당되는 것이 아닌 서버 전반에 모두 적용되도록 설계됐다. 먼저 그동안 MySQL이 어떻게 변화해
왔는지 살펴보고 5.0 버전에는 어떤 기능들이 추가됐는지 알아보자.

 

MySQL 진화의 역사

MySQL 데이터베이스 서버가 5.0까지 발전하면서 크게 세 번의 메이저 업그레이드가 있었다. 그 첫 번째는 3.23으로의 버전업은 현재
MySQL의 토대가 되었다. 메인 스토리지 엔진이 ISAM 엔진에서 My ISAM 엔진으로 변화됐고 트랜잭션 지원을 위해 InnoDB 엔진과
Berkeley DB를 지원하기 시작했다. 두 번째 메이저 버전업은 4.0으로의 버전업으로 주요 내용은 쿼리 캐시의 지원과 본적적인
풀 텍스트 인덱스의 지원이었고 5.0 이전의 마지막 메이저 버전업인 4.1에서는 서브쿼리의 지원과 UTF-8 유니코드의 지원이 가장
큰 특징이다. <표1>에서 주요 기능 변화 내역을 살펴보자.

버전	변경내용
3.23버전	대용량 파일과 더 나은 SQL지원을 위해 MyISAM 스토리지 엔진의 지원
	로우 레벨 락킹과 오라클과 비슷한 구조를 가진 트랜잭션 지원 스토리지 엔진인 InnoDB의 탑재
	트랜잭션 지원을 위해 Berkeley DB 라이브러리를 스토리지 엔진으로 사용가능
	마스터/슬레이브 구조의 리플리케이션 지원
	기본적인 풀 텍스트 인덱스의 지원
4.0버전	InnoDB 엔진이 기본 바이너리에 포함
	쿼리와 완전한 결과값을 캐싱하는 쿼리 캐시를 도입함으로써 큰 폭의 성능 향상
	풀 텍스트 인덱스를 통해 Boolean 및 문장 검색이 가능해지면서 본격적인 풀 텍스트 인덱스의 지원
	Merge 테이블에서 Insert와 Auto Increment 지원
	Select문에서 UNION 사용가능
	다중 테이블에 대한 DELETE 사용가능
	임베디드 서버 라이브러리인 libmysqld 지원
	동적으로 서버 변수의 설정이 가능해 짐으로써 서버의 재 시작 없이 설정의 변경이 가능해 짐
	리플리케이션에 대한 대폭적인 수정 및 안정화
4.1버전	Prepared statement와 parameter binding을 지원하는 더 빠른 서버/클라이언트간 프로토콜 사용
	Heap(memory)테이블에 B-Tree 인덱스를 지원함으로써 훨씬 빨라진 검색 가능 (이전 버전까지는 Heap테이블에서는 hash 인덱스만을 지원)
	MyISAM 스토리지 엔지에서 OpenGIS기반의 지리 정보 데이터의 처리를 지원
	SSL 을 통해 보안이 강화된 리플리케이션을 지원
	새로운 서버/클라이언트 라이브러를 통해 한번에 다수의 Warning을 처리할 수 있게 됨. 이를 통해 벌크 데이터 insert시 발생하는 경고를 훨씬 편리하게 추적 가능
	다양한 지역 언어를 완벽하게 지원하기 위해서 MySQL은 UTF-8과 UCS-2 캐릭터셋을 지원
	캐릭터셋을 데이터베이스, 테이블 그리고 컬럼단위로 지정 가능해 짐으로써 다중 언어를 지원하는 웹사이트 등의 개발 시 훨씬 유연한 작업 가능
	커넥션 별 타임 존 설정이 가능. 이를 통해 접속하는 클라이언트 별로 다른 타임 존 사용 가능
	GROUP_CONCAT() 기능이 추가되어 그릅핑된 row들 사이에서 단일 결과값을 편리하게 얻어낼 수 있게 됨

<표1. MySQL 버전별 기능 비교>

MySQL 5.0에서 추가되는 주요 기능

MySQL 5.0에서 추가되는 기능들의 특징이라면 그 동안 오라클이나 IBM의 DB2와 같은 상용 데이터베이스 시스템에서 많이 활용되는
기능이었으나 MySQL에는 없었던 그러한 기능들이라는 것이다. 사실 이러한 부분이 MySQL이 기업환경의 핵심업무에 적용되는 것을
어렵게 해왔으며 그 만큼 MySQL 사용자들이 오랜 기간 요구했던 기능들이다. 이번 5.0버전에 추가되는 주요 기능은 다음과 같다.

• 스토어드 프로시저(Stored Procedures)와 함수(functions)
• 트리거(Triggers)
• 뷰(Views)
• 인포메이션 스키마(Information Schema)
• 서버사이드 커서(Server-side Cursors)
• 분산 트랜잭션(XA Transactions)
• 아카이브 스토리지 엔진(Archive Storage Engine)
• 페더레이티드 스토리지 엔진(Federated Storage Engine)
• 인스턴스 매니저(Instance Manager)

이 외에도 많은 추가 기능들이 있지만 먼저 위의 핵심 기능들에 대해 하나씩 자세히 알아보도록 하자.

스토어드 프로시저와 함수 (Stored Procedures and Functions)

스토어드 프로시저란 일련의 SQL문을 하나의 묶음으로 데이터베이스 서버에 저장하는 기능으로써 한번 서버에 저장되면 클라이언트는
매번 각각의 SQL문을 보낼 필요 없이 저장된 스토어드 프로시저를 호출하게 된다. 스토어드 프로시저는 아래와 같은 경우에 특히 유용하다.

• 여러 클라이언트 어플리케이션이 각각 다른 프로그래밍 언어를 이용하여 작성되었거나 다른 플랫폼에 작동하나
  동일한 데이터베이스 작업이 필요할 때
• 은행과 같이 보안이 가장 중요한 경우에는 일반적으로 모든 데이터베이스 작업을 스토어드 프로시저로 처리함으로써
  어플리케이션이나 데이터베이스 사용자는 테이블에 직접 접근하지 않고 모두 스토어드 프로시저를 통해서만 접근하게
  되어 뛰어난 보안성이 보장되며 또한 하나의 작업단위로 적절히 수행되었는지 로깅이 가능해짐

위의 은행의 예에서 보듯이 스토어드 프로시저는 기업의 핵심업무에 적용되는 데이터베이스에서는 반드시 필요한 기능이며 MySQL의 5.0
이전버전에서 스토어드 프로시저가 지원되지 않는 것이 MySQL의 기업시장 진출에 많은 걸림돌이 되었던 것이 사실이다.

MySQL 5.0에 도입된 스토어드 프로시저는 IBM의 DB2와 같은 SQL2003 표준의 문법을 따르며 아직도 계속 기능이 보강되고 있는
중이다. MySQL 5.0에서 스토어드 프로시저의 기본적인 사용법은 아래와 같다.

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

트리거 (Triggers)

트리거에 대한 기본적인 지원은 MySQL 5.0.2버전부터 포함되었다. 트리거란 테이블에 연결되는 하나의 데이터베이스 오브젝트로서
테이블에 특정 이벤트가 발생하면 동작하게 된다. 예를 들어 아래의 SQL문은 하나의 테이블을 생성한 후 그 테이블에 INSERT에
대한 트리거를 만든다. 생성된 트리거는 테이블에 INSERT가 수행되면 amount컴럼의 합계를 구하게 된다.

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;

현재 트리거의 기능은 계속해서 추가되고 있는 중이며 최신 베타버전에서 가지고 있는 트리거 관련 제한 사항은 다음과 같다.

• 트리거는 CALL을 이용하여 스토어드 프로시저를 호출할 수 없다
• INSERT, UPDATE, DELETE등 하나의 동작에는 하나의 트리거만을 설정이 가능하다. 하지만 하나의 동작에
  BEGIN....END 사이에 여러 SQL문을 넣을 수 있기 때문에 크게 제한되는 부분은 아니다.
• 트리거에서는 START TRANSACTION, COMMIT, ROLLBACK과 같은 트랜잭션관련 SQL문을 쓸 수 없습니다.

뷰 (Views)

뷰는 데이터베이스 유저가 관계된 테이블 또는 정보에 쉽게 접근할 수 있도록 해주며 또한 특정 유저가 테이블의 정보 중
허용된 정보만 볼 수 있도록 하는 권한권리로서의 용도도 가지고 있다. 다음은 뷰의 기본적인 사용법이다.

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+

MySQL 5.0의 뷰 기능이 가진 주요 제한 사항은 다음과 같다.

• SELECT문에서 FROM절에 서브쿼리를 사용할 수 없음
• SELECT문에서 시스템이나 유저 변수를 참조할 수 없음
• 임시 테이블을 참조할 수 없으면 임시 뷰 역시 사용할 수 없음
• 뷰에서 참조하는 테이블 명은 먼저 존재하고 있어야 함
• 트리거와 뷰를 연결시킬 수 없음

인포메이션 스키마 (Information Schema)

인포메이션 스키마는 데이터베이스의 메타데이터 즉 데이터베이스 또는 테이블의 이름, 접속권한 등에 대한 접근을
제공한다. MySQL 5.0에 도입된 인포메이션 스키마는 ANSI/ISO SQL 2003 표준 정의의 11챕터 'Schemata'를 따르고
있으며 SQL 2003 표준의 핵심 정의인 'Basic Information Schema'을 대부분 만족시키고 있다.

MySQL 5.0에서 인포메이션 스키마에 대한 접근은 일반적으로 사용하는 SELECT문을 통하여 이루어 지면 사용법은 다음과 같다.

mysql> SELECT table_name, table_type, engine
    -> FROM information_schema.tables
    -> WHERE table_schema = 'db5'
    -> ORDER BY table_name DESC;
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| v56        | VIEW       | NULL   |
| v3         | VIEW       | NULL   |
| tables     | BASE TABLE | MyISAM |
| t7         | BASE TABLE | MyISAM |
| t3         | BASE TABLE | MyISAM |
| pk         | BASE TABLE | InnoDB |
| loop       | BASE TABLE | MyISAM |
| goto       | BASE TABLE | MyISAM |
| fk2        | BASE TABLE | InnoDB |
| fk         | BASE TABLE | InnoDB |
+------------+------------+--------+
17 rows in set (0.01 sec)

서버사이드 커서(Server-side Cursors)

MySQL 5.0에서는 스토어드 프로시저와 함수에서 사용할 수 있는 단순한 서버사이드-커서 기능을 지원한다. 현재 MySQL 5.0에서
지원하는 커서는 읽기전용이며 스크롤을 지원하지 않고 결과 테이블의 복사본을 만들지 않는다.

MySQL 5.0에서 스토어드 프로시저 또는 함수에서 커서를 사용하려면 반드시 핸들러를 선언하기 전에 커서를 선언해 주어야 하며
변수와 조건은 반드시 커서 또는 핸들러가 선언된 후에 선언되어야 한다. 다음은 MySQL 5.0에서 커서의 사용 예제이다.

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur1;
  OPEN cur2;

  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  CLOSE cur2;
END

분산 트랜잭션(XA Transactions)

MySQL은 5.0.3 버전부터 분산 트랜잭션을 지원하며 현재는 InnoDB 스토리지 엔진에서만 지원된다. MySQL에 도입된
분산 트랜잭션은 Open Group 에서 출판한 *"Distributed Transaction Processing: The XA Specification"*을
따르고 있으며 이 책은 http://www.opengroup.org/public/pubs/catalog/c193.htm 에서 무료로 볼 수 있다.

분산 트랜잭션을 사용하기 위해서는 클라이언트 측면에서는 XA 키워드를 사용하는 SQL문을 받아들일 수 있어야 한다는
것 외에는 특별한 제약 사항이 없으며 MySQL의 클라이언트 라이브러리와 링크될 필요 또한 없다. 현재 MySQL의 커넥터
중에서는 MySQL Connector/J 5.0.0이 XA를 직접 지원하고 있다.

MySQL이 지원하는 분산 트랜잭션에서는 다양한 트랜잭션 가능한 리소스들이 글로벌 트랜잭션 안에서 함께 처리될 수 있도록
하고 있으며 여기서 말하는 트랜잭션 가능한 리소스들이란 MySQL 또는 오라클과 같은 관계 형 데이터베이스 시스템 이거나
기타 다른 것일 수도 있다. 분산 트랜잭션의 예를 들면 다음과 같다.

*어플리케이션에서 서로 다른 데이터베이스 서버에 영향을 미치는 동작을 수행 하는 경우로써 데이터베이스는 MySQL과
오라클이거나 여러 대의 MySQL서버일 수 있다. 이러한 경우 각각의 데이터베이스 서버는 글로벌 트랜잭션의 일부로써
포함되게 되어 각각 별도의 로컬 트랜잭션을 거치지 않고 수행이 가능하다.
*은행이 계정 정보를 관계 형 데이터베이스에 저장하고 현금 인출기를 통해 현금이 인출될 때 계정에서 돈이 인출되는
트랜잭션은 현금 인출기와 데이터베이스에서 글로벌 트랜잭션을 통해 이루어질 수 있다.

분산 트랜잭션에 관한 내용은 작은 지면에서 설명하기에는 지나치게 복잡해질 수 있으므로 자세한 내용은 MySQL 레퍼런스
매뉴얼 http://dev.mysql.com/doc/mysql/en/xa.html 또는 위에 언급한 책을 참조하기 바란다.

아카이브 스토리지 엔진(Archive Storage Engine)

데이터베이스에는 빈번하게 사용되는 데이터뿐만 아니라 의사결정 또는 통계에 사용하기 위해 계속 누적되는 데이터
역시 많은 부분 존재한다. 이러한 경우 데이터베이스의 용량은 필연적으로 계속적으로 증가하기 마련이고 이러한 데이터가
차지하는 공간을 줄이고 효율적으로 사용하기 위해 도입된 것이 바로 아카이브 스토리지 엔진이다.

MySQL에서는 그 동안 빈번히 사용되지 않는 대용량 데이터를 처리하기 위해 압축 MyISAM 테이블이라는 것을 사용했다.
그렇다면 아카이브 스토리지 엔진이 압축 MyISAM 테이블에 비해 어떠한 장점이 있는지 알아보자.

• 압축 MyISAM 테이블은 MyISAM 테이블을 압축하여 보관하는 것으로써 반드시 테이블이 오프라인 상태여야 했다.
  하지만 아카이브 테이블은 온라인 상에서 모든 작업이 가능하다
• MyISAM 테이블을 압축하기 위해서는 DBA가 운영체제 상에서 myisampack이라는 유틸리티를 실행시켜야 했지만
  아카이브 테이블은 MySQL클라이언트 상에서 MySQL SQL 커맨드로서 가능하다
• 압축 MyISAM 테이블은 오직 SELECT만 가능하였지만 아카이브 테이블은 SELECT와 INSERT가 모두 가능하며
  끊김 없는 읽기 메커니즘을 통해 쓰는 동안 읽기 작업이 중단되지 않는다
• 아카이브 테이블은 일반 MyISAM테이블에 비해 75%나 용량이 감소하며 압축 MyISAM테이블에 비해서도 7% 이상 작은 용량을 가진다

페더레이티드 스토리지 엔진 (Federated Storage Engine)

페더레이티드 스토리지 엔진은 오라클의 DB Link와 같은 기능을 하는 스토리지 엔진으로써 리모트 서버에 있는 테이블을
로컬 테이블과 같이 사용할 수 있도록 한다. 이를 통해 여러 대의 MySQL서버를 용도에 따라 구분하고 필요한 경우 서로
참조하여 사용할 수 있게 되었다.

페더레이티드 스토리지 엔진의 사용은 매우 간단한 작업만으로 가능하다. 두 대의 MySQL서버를 사용한다고 가정하고 어떻게
페더레이티드 스토리지 엔진을 사용할 수 있는지 알아보자.

먼저 실제로 테이블이 생성되고 데이터가 저장될 MySQL 서버에 다음과 같이 테이블을 만들었다.

CREATE TABLE test_table (
    id     int(20) NOT NULL auto_increment,
    name   varchar(32) NOT NULL default '',
    other  int(20) NOT NULL default '0',
    PRIMARY KEY  (id),
    KEY name (name),
    KEY other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=UTF-8;

그리고 이 테이블을 원격에서 사용하고 하는 서버에 아래와 같이 페더레이티드 스토리지 엔지 기반의 테이블을
만들어 줌으로서 바로 사용이 가능하다.

CREATE TABLE federated_table (
    id     int(20) NOT NULL auto_increment,
    name   varchar(32) NOT NULL default '',
    other  int(20) NOT NULL default '0',
    PRIMARY KEY  (id),
    KEY name (name),
    KEY other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
COMMENT='mysql://root@remote_host:3306/federated/test_table';

맨 아래의 COMMENT부분만 주의해서 보면 쉽게 사용이 가능하며 COMMENT부분에 대한 문법은 다음과 같다.

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

인스턴스 매니저(Instance Manager)

MySQL 인스턴스 매니저는 TCP/IP 포트 위에서 동작하는 데몬으로써 MySQL 데이터베이스 서버의 인스턴스를 관리하고
모니터링 한다. MySQL 인스턴스 매니저를 통해 리모트에서도 MySQL 데이터베이스 서버를 시작하고 중단할 수 있고
MySQL 데이터베이스 서버 인스턴스가 중단될 경우 자동으로 재 시작 시키며 한 서버의 여러 개의 MySQL 서버 인스턴스를
관리할 수 있다.

현재 MySQL 인스턴스 매니저는 리눅스 및 유닉스 계열 운영체제에서만 사용이 가능하며 환경설정은 /etc/my.cnf 의
[manager]탭에서 하게 된다. 일반적인 인스턴스 매니저 환경설정은 다음과 같다.

# MySQL Instance Manager options section
[manager]
default-mysqld-path = /usr/local/mysql/libexec/mysqld
socket=/tmp/manager.sock
pid-file=/tmp/manager.pid
password-file = /home/cps/.mysqlmanager.passwd
monitoring-interval = 2
port = 1999	
bind-address = 192.168.1.5