[MySQL] 주요 스토리지 엔진(Storage Engine) 간단 비교

MySQL은 크게 아래의 2가지 구조로 되어 있다.

• 서버 엔진 : 클라이언트(또는 사용자)가 Query를 요청했을때, Query Parsing과, 스토리지 엔진에 데이터를 요청하는 작업을 수행.
  
  
• 스토리지 엔진 : 물리적 저장장치에서 데이터를 읽어오는 역할을 담당.

여기서 중점적으로 봐야할 것은 '스토리지 엔진' 인데, 그 이유는 데이터를 직접적으로 다루는 역할을 하므로 엔진 종류 마다 동작원리가 다르고, 따라서 트랜잭션, 성능과 같은 주요 이슈에도 밀접하게 연관되어 있기 때문이다.

MySQL의 스토리지 엔진은 'Plug in' 방식이며, 기본적으로 8가지의 스토리지 엔진이 탑재 되어 있다. 아래의 명령으로도 탑재된 스토리지 엔진을 확인 할 수 있다.

mysql> SHOW ENGINES;

위에서 말한 것과 같이 Plug 'in' 방식 이기때문에 스토리지 엔진 교체 작업이 비교적 간단하다. 또한 기본 탑재 플러그인 외에 서드파티에서 제공하는 다양한 스토리지 엔진을 손쉽게 적용할 수 있다는 것이 장점이다.

각 플러그인의 '.so'(Shared Object) 파일을 얻었다면 아래와 같이 설치/삭제 할 수 있다.

INSTALL PLUGIN ha_example SONAME 'ha_example.so';

UNINSTALL PLUGIN ha_example;

그리고 'CREATE TABLE' 문을 사용하여 테이블을 생성할 때, 맨 마지막 구문에 스토리지 엔진의 이름을 추가함으로 아주 간단하게 설정 할 수 있다. 아래와 같다.

-- ENGINE=INNODB not needed unless you have set a different
-- default storage engine.
CREATE TABLE t1 (i INT) ENGINE = INNODB;
-- Simple table definitions can be switched from one to another.
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

자세한것은 공식 문서 참고

이 포스팅에서는 가장 많이 쓰인다고 알려진 스토리지 엔진인 MyISAM, InnoDB, Archive
의 3가지 스토리지 엔진을 비교/정리 하려고 한다.

• InnoDB : 따로 스토리지 엔진을 명시하지 않으면 default 로 설정되는 스토리지 엔진이다. InnoDB는 transaction-safe 하며, 커밋과 롤백, 그리고 데이터 복구 기능을 제공하므로 데이터를 효과적으로 보호 할 수 있다.
  
  InnoDB는 기본적으로 row-level locking 제공하며, 또한 데이터를 clustered index에 저장하여 PK 기반의 query의 I/O 비용을 줄인다. 또한 FK 제약을 제공하여 데이터 무결성을 보장한다.
  
  
• MyISAM : 트랜잭션을 지원하지 않고 table-level locking을 제공한다. 따라서 multi-thread 환경에서 성능이 저하 될 수 있다. 특정 세션이 테이블을 변경하는 동안 테이블 단위로 lock이 잡히기 때문이다.
  
  텍스트 전문 검색(Fulltext Searching)과 지리정보 처리 기능도 지원되는데, 이를 사용할 시에는 파티셔닝을 사용할 수 없다는 단점이 있다.
  
  
• Archive : '로그 수집'에 적합한 엔진이다. 데이터가 메모리상에서 압축되고 압축된 상태로 디스크에 저장이 되기 때문에 row-level locking이 가능하다.
  
  다만, 한번 INSERT된 데이터는 UPDATE, DELETE를 사용할 수 없으며 인덱스를 지원하지 않는다. 따라서 거의 가공하지 않을 원시 로그 데이터를 관리하는데에 효율적일 수 있고, 테이블 파티셔닝도 지원한다. 다만 트랜잭션은 지원하지 않는다.

아래는 상세 도표다. (수시로 참고 해야겠당..)

InnoDB Storage Engine

Storage limits	64TB	Transactions	Yes	Locking granularity	Row
MVCC	Yes	Geospatial data type support	Yes	Geospatial indexing support	Yes
B-tree indexes	Yes	T-tree indexes	No	Hash indexes	No
Full-text search indexes	Yes	Clustered indexes	Yes	Data caches	Yes
Index caches	Yes	Compressed data	Yes	Encrypted data	Yes
Cluster database support	No	Replication support	Yes	Foreign key support	Yes
Backup / point-in-time recovery	Yes	Query cache support	Yes	Update statistics for data dictionary	Yes

MyISAM Storage Engine

Storage limits	256TB	Transactions	No	Locking granularity	Table
MVCC	No	Geospatial data type support	Yes	Geospatial indexing support	Yes
B-tree indexes	Yes	T-tree indexes	No	Hash indexes	No
Full-text search indexes	Yes	Clustered indexes	No	Data caches	No
Index caches	Yes	Compressed data	Yes	Encrypted data	Yes
Cluster database support	No	Replication support	Yes	Foreign key support	No
Backup / point-in-time recovery	Yes	Query cache support	Yes	Update statistics for data dictionary	Yes

Archive Storage Engine

Storage limits	None	Transactions	No	Locking granularity	Row
MVCC	No	Geospatial data type support	Yes	Geospatial indexing support	No
B-tree indexes	No	T-tree indexes	No	Hash indexes	No
Full-text search indexes	No	Clustered indexes	No	Data caches	No
Index caches	No	Compressed data	Yes	Encrypted data	Yes
Cluster database support	No	Replication support	Yes	Foreign key support	No
Backup / point-in-time recovery	Yes	Query cache support	Yes	Update statistics for data dictionary	Yes

[MySQL] 실행계획 (Query Plan)

원문 - http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

개요

---

 MySQL 쿼리 옵티마이저는 쿼리를 실행할때 최적의 계획을 세운다. 그 계획을 Database용어로 '실행계획'(Query Plan)이라고 하는데, MySQL에서는 'EXPLAIN' 키워드를 이용해 실행계획에 대한 정보를 살펴 볼 수 있다.

 이는 이슈가 발생하는 문제의 쿼리를 이해하고, 어떻게 최적화 할지에대한 insight를 제공하는 매우 강력한 도구가 될 수 있는데, 불행하게도 이를 잘 사용하는 개발자는 별로 없는것 같다. (나 포함 ㅠ)

Understanding EXPLAIN's Output

---

'EXPLAIN' 을 사용하면 쿼리를 실행하기전에 실행계획을 분석하여 출력한다. 아래와 같은 예를 들어보자

EXPLAIN SELECT * FROM categories

********************** 1. row **********************
           id: 1
  select_type: SIMPLE
        table: categories
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: 
1 row in set (0.00 sec)

이제부터 해당 항목들에대해 자세히 살펴보도록 한다

• id : 쿼리 안에 있는 각 select 문에 대한 순차 식별자이다. 이순서대로 select문이 실행된다고 생각하면 된다.
• select_type : select 문의 유형을 말한다. 각 유형은 아래와 같다
• SIMPLE : 서브쿼리나 'union'이 없는 가장 단순한 select문을 말한다
• PRIMARY : 가장 바깥에 있는 select 문을 말한다
• DERIVED : form 문 안에있는 서브쿼리의 select 문이다.
• SUBQUERY : 가장 바깥의 select 문에 있는 서브쿼리이다.
• DEPENDENT SUBQUERY : 기본적으로 SUBQUERY와 같은 유형이며, 가장 바깥의 select문에 '의존성'을 가진 서브쿼리의 select문이다.
• UNCACHEABLE SUBQUERY
• UNION : union 문의 두번째 select 문을 말한다
• DEPENDENT UNION : 바깥 쿼리에 의존성을 가진 union문의 두번째 select문을 말한다
• table : 참조되는 테이블을 말한다
• type : MySQL이 어떤식으로 테이블들을 조인하는지를 나타내는 항목이다. 이는 매우 중요한데, 이유는 이 타입을 분석함으로써 어떤 인덱스가 사용되고 사용되지 않았는지를 알 수 있고, 이를통해 어떤식으로 쿼리가 튜닝되어야하는지에 대한 insight를 제공하기 때문이다. 각 유형은 아래와 같다
• system : 0개 또는 하나의 row를 가진 테이블이다.
• const 
• eq_ref : primary key나 unique not null column으로 생성된 인덱스를 사용해 조인을 하는 경우이다. const 방식 다음으로 빠른 방법이다.
• ref : 인덱스로 지정된 컬럼끼리의 '=' , '<=>' 와 같은 연산자를 통한 비교로 수행되는 조인이다
• index_merge 
• unique_subquery : 오직 하나의 결과만을 반환하는 'IN'이 포함된 서브쿼리의 경우이다.
• index_subquery : unique_subquery와 비슷하지만 여러개의 결과를 반환한다
• range : 특정한 범위의 rows들을 매칭시키는데 인덱스가 사용된 경우이다. BETWEEN이나 IN, '>', '>=' 등이 사용될 때이다.
• all : 조인시에 모든 테이블의 모든 row를 스캔하는경우이다. 물론 성능이 가장 좋지 않다.
• possible_keys : 테이블에서 row를 매핑시키기 위해 사용 가능한 (사용하지 않더라도) 키를 보여준다.  
• key : 실제적으로 쿼리 실행에 사용된 key의 목록이다. 이 항목에는 possible_keys 목록에 나타지 않은 인덱스도 포함 될 수 있다.
• ref : key column에 지정된 인덱스와 비교되는 column 또는 constants를 보여준다.
• rows : 결과 산출에 있어서 접근되는 record의 숫자이다. 조인문이나 서브쿼리 최적화에 있어서 중요한 항목이다.
• Extra : 실행계획에 있어서 부가적인 정보를 보여준다.
• http://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain-extra-information