MySQL 스토리지 엔진과 트랜잭션
데이터베이스 엔진이란?
데이터베이스 엔진 혹은 스토리지 엔진은 DBMS가 데이터베이스에 CRUD 작업을 할 때 사용되는 기본 소프트웨어 컴포넌트입니다.
DB에서 데이터에 어떠한 방식으로 접근, 저장할 것인지에 대한 기능을 제공
스토리지 엔진의 특성에 따라서 접근 속도, 안정성, 트랙잭션 등의 기능의 차이점이 발생한다
데이터베이스 서버, 데이터베이스 관리 시스템(DBMS)라고도 불리기도 하는 데이터 베이스 엔진을 조작하는 방법은 2가지가 있습니다.
- DBMS 고유의 사용자 인터페이스 이용
- 포트 번호를 통한 조작
대부분의 DBMS는 1번 방법(사용자 인터페이스 이용) 외에 사용자가 내장된 엔진과 상호작용을 할 수 있는 API를 가지고 있습니다.
또한 동일한 DB 내에 다중 저장엔진을 지원하기도 합니다.
저장 엔진의 종류
| 저장엔진 | 트랜잭션 지원 여부 |
|---|---|
| Aria | X |
| BlitzDB | X |
| Falcon | O |
| InnoDB | O |
| MyISAM | X |
| InfiniDB | X |
| TokuDB | O |
| XtraDB | O |
MySQL 구조
MySQL 서버 = MySQL 엔진 + 스토리지 엔진
MySQL 엔진은 클라이언트의 요청을 처리하고
- SQL 문장 분석
- 최적화 등
스토리지 엔진은 실제 데이터를 스토리지에 저장, 조회하는 부분을 담당한다.
MySQL 엔진
- Connection Handler : 커넥션, 쿼리 요청 처리
- SQL Interface : DML, DDL, View, Procedure 등 인터페이스 제공
- SQL parser : syntax 검사, 쿼리 문장을 토큰 단위로 구분하여 트리 형태로 파싱
- SQL Optimizer : 쿼리의 최적화 실행 담당
- 캐시와 버퍼 : 성능 향상을 위한 보조 저장소
스토리지 엔진
- MySQL 엔진과
plugin형태도 연동이 가능하다 - Handler API를 통해 스토리지 엔진에 CRUD 요청이 가능하다.
MySQL 스토리지 엔진의 종류 및 특징
Schema
MySQL은 데이터베이스(schema)를 파일 시스템 안의 데이터 디렉토리의 하위 디렉토리로 설정합니다.
테이블을 생성하게 되면 테이블명.frm 파일을 생성하고, 파일 내부에 테이블 정보를 저장합니다.
Database 이름과 테이블 정의를 저장할 때 파일 시스템을 사용하므로, OS에 따라 대/소문자 구분 여부가 달라집니다.
윈도우 : 대/소문자 구분 없음
UNIX 계열 : 대/소문자를 구분한다
스토리지 엔진
테이블의 정의는 서버에서 담당하고,
테이블의 데이터와 인덱스 저장방식은 스토리지 엔진에 따라 달라집니다.
SHOW TABLE STATUS 명령어를 통해 특정 테이블이 어떠한 스토리지 엔진을 사용하는지 확인할 수 있습니다.
주요 컬럼 확인하기
| 항목 | 설명 |
|---|---|
| Name | 테이블 이름 |
| Engine | 테이블에 적용된 스토리지 엔진 |
| Row_format | 레코드 포멧. MyISAM 테이블은 Dynamic(동적), Fixed(고정), Compressed(압축)의 형식을 취할 수 있다. Dynamic(동적) 레코드 : VARCHAR, BLOB 등 길이가 다양하다. Fixed 레코드는 CHAR 이나 INTEGER처럼 길이가 변하지 않고 고정된 칼럼으로 구성된다. |
| Rows | 테이블 내 행의 개수. 비-트랜잭션 테이블에서는 정확한 값이지만, 트랜잭션 테이블에서는 주로 추정 값으로 나온다 |
| Avg_row_length | 행의 평균 바이트 수 |
| Data_length | 전체 테이블의 데이터량 (Byte) |
| Max_data_length | 테이블이 가질 수 있는 최대 데이터량 |
| Index_length | 인덱스 데이터의 디스크 공간 소비량 |
| Data_free | MyISAM 테이블에 할당되었으나 아직 사용하지 않은 공간. 이전에 삭제된 행이 남아있으며 나중에 INSERT 구문이 실행될 때 재사용될 수 있다. |
| Auto_increment | Id를 위한 다음 AUTO INCREMENT 값 |
| Create_time | 테이블이 처음 생성된 시간 |
| Update_time | 데이터가 마지막으로 갱신된 시간 |
| Check_time | 테이블이 CHECK TABLE이나 myisamchk를 사용하여 마지막으로 검사된 시기 |
| Collation | 테이블 내 캐릭터 레코드의 기본 Character Set 과 Collations |
| Checksum | 테이블 전체 콘텐츠의 유효한 체크섬 값 |
| Create_options | 테이블 생성 시에 지정된 별도 옵션 |
| Comment | 이 필드는 다양한 부수 정보를 갖는다. MyISAM : 테이블이 생성되었을 때 설정된 주석을 갖는다. InnoDB 스토리지 엔진 : InnoDB 테이블 스페이스에 있는 빈 공간에 대한 정보가 여기에 나온다. 테이블이 뷰라면 이 필드에는 ‘VIEW’라는 문자가 있을 것이다. |
InnoDB 엔진
InnoDB 엔진은 트랜잭션을 처리하기 위해 고안되었다. 대부분의 케이스는 정상 종료되는 짧은 트랜잭션이 많은 상황을 처리하기 좋다.
- 가장 많이 사용되는 스토리지 엔진 중 하나
- ACID 트랜잭션 지원
- 성능, 장애 복구 기능
- 클러스터 인덱스 위에 구성되어 있다
- 다른 MySQL 스토리지 엔지의 인덱스 구조와는 상이함
- 신속한 PK 조회 가능
- PK가 커지면 인덱스 또한 커지므로, 테이블이 여러 인덱스를 가진다면 기본키가 작은 값을 갖게 해야한다.
InnoDB의 특징 자세히 살펴보기
PK에 의한 클러스터링
모든 테이블은 기본적으로
PK를 기준으로 클러스터링되어 저장된다.
키 값 순서대로 Disk에 저장이 되고, PK에 의한 range 스캔은 빨리 처리도리 수 있다.
Lock이 필요 없는 읽기 작업
MVCC(Multi Version Concurrency Control) 기술을 통해 다른 트랜잭션이 가지고 있는 Lock 기다리지 않고 읽기 작업을 수행한다.
ForeignKey 지원
InnoDB 스토리지 엔진 레벨에서 지원하고,
MyISAM,MEMORY테이블에서는 사용할 수 없다.
자동 데드락 감지
그래프 기반의 데드락 체크 방식을 사용하여
발생과 동시에 감지된다.
감지된 데드락은 관련 transaction 중 ROLLBACK이 가장 용이한 트랜잭션을 자동으로 강제 종료한다.
자동화된 장애 복구
손실, 장애로부터 데이터를 보호하기 위한 매커니즘이 탑재되어 있고,
MySQL서버가 시작될 때완료되지 못한 트랜잭션이나, 디스크에 일부만 기록된 트랜잭션 등에 대한일련의 복구작업이 자동으로 진행된다.
오라클의 아키텍처 적용
- Lock을 기다리지 않고 조회를 하는 MVCC 기능
- 테이블 스페이스 개념 등
- Undo 데이터가 시스템 테이블 스페이스에 관리
MyISAM 엔진
색인 순차 접근 방식(Indexed Sequential Access Method, ISAM)은 빠른 데이터 검색을 위한 파일 시스템 구조이다.
아래와 같은 유용한 기능을 제공하지만 트랜잭션이나 Row 수준의 잠금을 지원하지 않는다.
- 전문(full-text) 인덱싱
- 압축
- 지리 관련 공간 함수 등 제공
Transaction을 지원하지 않기 때문에 InnoDB에 비해 심플하고 빠르지만, 동시성 제어가 어렵다.
- Read 쿼리가 많은 DW(Data Warehousing) 환경에서 많이 사용된다.
각 테이블을 데이터 파일(.MYD), 인덱스 파일(.MYI) 두 곳에 주로 저장한다. MyISAM 테이블은 정적, 동적인 행을 모두 가질 수 있으며, 테이블 정의를 토대로 하여 사용할 포맷을 결정한다.
MyISAM의 특징 자세히 살펴보기
Key Cache
InnoDB의 버퍼풀과 비슷한 역할을 하지만,
인덱스를 대상으로만 작동하며,인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 해준다.
Lock과 동시성
MyISAM은 행단위가 아닌
테이블 전체에 Lock을 건다.
- 읽기 동작 : 읽는 모든 테이블에 대한 공유된 읽기 권한
- 쓰기 동작 : 배타적 쓰기 잠금 권한
SELECT 쿼리 실행 중에도 테이블에 새 행을 삽입(동시 삽입)할 수 있다.
수동 복구
CHECK TABLE 테이블명,REPAIR TABLE 테이블명명령어를 통해서 오류를 조사하고, 복구할 수 있다.
myisamchk(MyISAM Check) 명령어를 사용하면 서버가 오프라인일 때도 테이블을 조사하고 복구할 수 있다.
지연된 Key 쓰기
테이블을 생성할 때
DELAY_KEY_WRITE(지연된 Key 쓰기) 옵션을 ON으로 설정하면
해당 테이블은마지막에 변경된 데이터를 Disk에 쓰지 않고,메모리상의 버퍼에 버퍼링한다.
버퍼를 정리하거나, 테이블을 닫을 때 flush 해준다. 이용 빈도가 높고, 변경이 잦은 테이블의 성능 향상에 도움이 된다.
하지만 버퍼링을 하기 때문에 conflict가 발생했을 때 손상되므로 복구 작업이 필요하다.
- 해결 방법
- myisamchk를 실행하는 자동 script
- 자동 복구옵션 사용
Memory 엔진
메모리에 데이터를 저장하는 엔진으로, Transaction을 지원하지 않고 table-level의 잠금을 사용한다.
HEAP 테이블이라 불리던 메모리 테이블은 변하지 않는 데이터나재시작 이후 지속되지 않는 데이터에 빠르게 접근하는데 유용하다
- 메모리를 사용하여 속도가 빠르다 (I/O 작업을 기다릴 필요가 없다)
- 데이터를 잃어버릴 위험이 있다
- 임시 테이블로 많이 사용된다
- 서버 재 시작 시
- 테이블 구조는 지속
- 데이터는 삭제
사용 예시
쿼리를 처리할 때 중간 결과를 저장할 임시테이블로 주로 사용이 된다.
- 조회용, 매핑용 테이블
- 주기적으로 집계되는 데이터의 결과를 캐시하는 테이블
- 데이터 분석 시 중간 결과 저장용 테이블
MySQL은 내부적으로 메모리 엔진을 사용하지만,
중간 결과가 저장하기에 너무 커지거나, text, blob을 포함하면 MyISAM 테이블로 대체한다.
MEMORY TABLE vs. TEMPORARY TABLE
- MEMORY TABLE
- 메모리에 저장되는 임시 테이블
- TEMPORARY TABLE
- 모든 스토리지 엔진을 사용 가능
- 단일 연결에만 보이고, 연결이 끝나면 사라진다.
Archive 엔진
저장소(archive)라는 네이밍에서 알 수 있 듯, INSERT 쿼리를 빠르게 처리할 수 있는 엔진이다.
데이터 쓰기 작업을 버퍼링하고, 각 행이 삽입될 때마다 zlib으로 압축하여
MyISAM보다 디스크 입출력을 적게 일으킨다.
- Index를 지원하지 않는다
- INSERT/REPLACE/SELECT 쿼리 지원
- DELETE/UPDATE 미지원
- transaction을 지원하지 않는다.
- row-level locking을 사용하여, 많은 양의 로그성 데이터를 저장하고 읽는데 사용된다.
CSV 엔진
CSV(Comma Separated Values)로 구성된 파일을 테이블로 처리하지만, 인덱스를 지원해주지 않는다.
서버가 실행되는 동안 DB 내외로 파일을 복사하게 해준다.
- 스프레드시트에서 csv 파일을 내보낸다.
- MySQL 서버의 directory에 저장한다.
- 서버에서 즉시 파일을 읽을 수 있다.
Federated 엔진
데이터를 자체 스토리지에 저장하지 않고, 각 Federated 테이블은 원격 MySQL 서버의 테이블을 참조한다.
모든 작업을 원격 서버에 연결하여 진행한다.
장점
- 기본키를 이용한 단일 행 조회
- 원격 서버에 실행 될 INSERT 쿼리
단점
- 집계 쿼리, 조인 등 다른 기본 작업의 성능이 낮다.
BlackHole 엔진
저장 메커니즘을 가지고 있지 않고, INSERT 구문은 모두 버린다.
서버는 Blackhole 테이블에 대한 쿼리를 로그에 기록하고, slave에 복제하는 등 로그에 남긴다.
복잡한 복제 구성, 감사용 로깅에 유용하게 사용된다.
MySQL 엔진 선택 시 고려사항
설계 단에서 테이블 별로 어떠한 스토리지 엔진을 사용할 것인지 고려해야한다.
테이블이 어떻게 사용되고, 데이터가 어떻게 저장되는지 이해하고, 확장성 또한 고려해야한다.
트랜잭션이 필요할 때
InnoDB 엔진이 가장 안정적이다. MyISAM은 트랜잭션이 필요없는 사이트, SELECT가 메인인 업무에 적합하다.
참고 링크
MySQL 스토리지 엔진과 트랜잭션
install_url to use ShareThis. Please set it in _config.yml.