MySQL Storage Engine ¶
송은영(f405@sds.co.kr)-작성,방창현(winchild@sds.co.kr)-정리및등록 / (주)삼정데이터서비스 연구소
최종갱신일: 2006년2월4일 11시00분
아래 글들을 정리했음 ¶
그외에 저작자를 알수 없는 글들... (자신의 글이 있으면 알려주세요. ^^)
MyISAM ¶
DB는 디렉토리별로 생성/관리된다.
테이블은 디렉토리 내 파일로 생성/관리되고, 하나의 테이블당 3개의 데이터파일로 구성된다.
dbakorea.frm : 테이블구조정보(스키마정보)
dbakorea.myd : 순수 데이터
dbakorea.myi : 인덱스 정보
테이블을 구성하는 row는 다음과 같이 3가지 형식으로 분류할 수 있다.
- 고정 포맷(fixed row format)
컬럼 타입으로 varchar, text, blob을 사용하지 않을 때.
가능하다면 가급적 고정포맷을 사용하는 것이 좋다.
동적포맷보다 메모리사용이 적고, 인덱스파일크기도 작아진다. 당연히 속도 또한 향상된다.
파일구조의 고정길이레코드가 고정포맷, 가변길이레코드가 동적포맷이라고 생각해주면 이해하기 쉬울 것이다.
- 동적 포맷(dynamic row format)
컬럼 타입으로 varchar, text, blob을 사용할 때.
주의> varchar(3)보다 작다면 고정포맷이 사용됨
고정포맷에 비해 디스크사용에 있어 효율성을 가지나, 속도는 상대적으로 느리다.
테이블에 빈번한 레코드의 수정/삭제가 이루어지면 단편화가 유발하므로 주기적인 optimize table이 요구된다.
text, blob은 별도로 저장되므로, optimize table을 수행할 필요성이 없다.
- 압축 포맷(compressed row format)
읽기전용이다. myisampack 명령어로 만들 수 있다.
디스크공간을 적게 차지하므로 CD에 백업할 때 사용하면 되겠다.
MyISAM은 동시성제어를 위해 테이블단위 락킹(table-level locking)을 사용한다.
참고로, 대부분의 상용 DBMS들은 행 단위 락킹(row-level locking)을 사용한다.
행 단위 락킹이 더 세밀하고 정밀한 제어가 가능한 반면, 테이블단위 락킹은 단순하다.
다음은 사용되는 3가지 락
- READ LOCAL lock
query문(select)에서만 사용됨.
갱신작업들을 블럭. 다른 query문들은 블록 안 됨.
insert문에서 .myd파일의 끝에 데이터를 추가하는 경우에는 블럭되지 않음.
- READ, or shared locks
모든 갱신작업들(insert는 모두 적용됨)이 블럭됨. myisamcheck는 이 락을 사용.
- WRITE, or exclusive locks
insert(몇몇 종류만), update, delete시 사용됨. 다른 모든 읽기작업/쓰기작업이 블럭됨.
인덱스 : key buffer에 캐싱되어 모든 놈들( MySQL 스레드들)이 공유
데이터 : OS의 캐싱에 의존.
주의> 캐싱에 대해
InnoDB가 인덱스/데이터 캐싱 모두를 관리하는 것에 비해( MySQL서버가 관리한다는 의미)
MyISAM은, 인덱스만 MySQL서버가 관리하고, 데이터는 관리하지 않는다. (데이터는 OS 캐싱에 의존한다는 의미)
InnoDB는 innodb_buffer_pool_size, MyISAM은 key_buffer_size변수를 사용한다.
변수값보기: show variables
변수값설정: my.ini(윈도우)
Unix계열은 (/etc/my.cnf)에 set-variable=key_buffer=16M 이런 식으로 설정
자주 사용되는 테이블들의 .myi파일크기를 합하면 대략적은 인덱스 캐싱크기를 구할 수 있다.
3가지 인덱스 사용가능: btree, rtree(지리학 데이터), fulltext (트랜잭션 지원 안 됨)
mysqldump(SQL문으로 생성), mysqlhotcopy(이진형식으로 생성)로 백업가능
- MyISAM MERGE
테이블들을 union으로 묶은 일종의 뷰.
실제 데이터는 기반테이블들에 있음.
보통 히스토리 데이터나 로그를 가지는 테이블들에서 사용됨.
오라클의 파티셔닝과 그 개념이 유사.
- InnoDB
ACID 트랜잭션, multi-versioning, row-level locking, foreign key제약조건 지원됨.
크래쉬 후 자동복구 지원
데이터와 인덱스가 모두 저장되는 테이블스페이스 개념이 사용됨.
오라클의 테이블스페이스와 같이 여러 개의 파일들로 구성될 수 있다.
select는 락킹이 필요치 않으며, 갱신작업들은 행 단위 락킹을 사용.
높은 동시성을 제공하지만, MyISAM에 비해 3배정도의 디스크 사용량을 요구함.
최적의 성능을 위해 많은 메모리가 InnoDB buffer pool에 할당되어야 함.
클러스터된 프라이머리키로 btree인덱스 사용
commit된 트랜잭션은 redo log에 기록되고, 이는 적정한 시간에 테이블스페이스에 기록된다.
mysqldump로 백업가능.
아래 질의문으로 InnoDB사용가능 여부를 알 수 있다.
show variables like 'have_innodb'
참고>
show variables는 서버 변수값을 파악 시 사용한다. 오라클에서의 show parameter와 유사하다.
특정 변수값만을 알아낼 때는 like를 사용해 해당 변수만을 지정하면 된다.
SQL문의 like문법이 적용되므로 %, _를 사용하여 특정영역의 변수들만을 지정할 수도 있다.
참고로 오라클의 경우 like를 지정하지 않고 그냥 패러미터를 지정할 수 있다.
mysql> show variables like 'have%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+--------+
| have_bdb | NO |
| have_crypt | NO |
| have_compress | YES |
| have_innodb | YES |
| have_isam | NO |
| have_raid | NO |
| have_symlink | YES |
| have_openssl | NO |
| have_query_cache | YES |
+-------------------------+--------+
9 rows in set (0.00 sec)
SQL> show parameter timed
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_os_statistics integer 0
timed_statistics boolean TRUE
SQL>
- MEMORY(HEAP)
모든 데이터들은 메모리에 저장됨.
다른 테이블타입들에 비해 속도가 월등히 빠르지만, 서버가 셧다운시 데이터는 모두 소실된다.
동등조건( ex) where a=10) 검색에 HASH기반 검색을 제공한다.
이는 범위 검색시 인덱스가 사용되지 못한다는 것을 의미한다. varchar, blob, text 컬럼 사용 못 함.
4.1버전부터는 트리기반 인덱스도 사용 가능함.
NDB로 칭한다. (Network DataBase) 별도의 문서인 " MySQL 클러스터" 에서 언급.
사용하기 ¶
- MyISAM
정적인 테이블, 로그 테이블
쓰기작업이 별로 없는 select 위주의 테이블.
current insert기능이 read시에 insert가 가능하게 하므로 로그 테이블에 사용될 수 있다.
- InnoDB
민감한 정보를 갖는 테이블(회원테이블, 돈에 관련된 테이블)
갱신(읽기/쓰기) 위주의 트랜잭션이 요구되는 테이블.
인덱스가 많이 걸린 대량의 테이블은 이 놈을 사용하는 것이 좋다.
- Heap
일시적으로만 사용되는 임시 테이블.
MySQL이 웹에서 자주 사용되므로 이와 관련해서 설명하면,
로깅이나 검색에서는 MyISAM을, 등록정보나 배너시스템에서는 InnoDB를,
임시테이블, 뉴스의 헤드라인, 로드가 많은 페이지의 데이터에 대해선 heap을 권장 사용한다.
|
|
