2020-2-1 DB Storage엔진

DB Storage Engine

  • MySQL은 크게 서버엔진과 스토리지 엔진으로 구성이 된다.
  • 서버엔진 : 클라이언트가 Query를 요청했을때, Query Parsing과 스토리지 엔진에 데이터를 요청하는 작업을 수행한다.

  • 스토리지 엔진 : 물리적 저장장치에서 데이터를 읽어오는 역할을 담당. 스토리지 엔진은 데이터를 직접적으로 다루기때문에 엔진 종류마다 동작원리가 다르고 트랜잭션이나 성능과 같은 주요 이슈와 관련이 있다.

  • MySQL에는 MyISAM과 InnoDB라는 대표적인 DB엔진들이 있다.
  • MyISAM 테이블과 InnoDB테이블을 같이 사용할 경우 조인시 유의해야함!!

MyISAM

  • MyISAM의 경우 테이블에 row count를 가지고 있기때문에 select count(*)를 할때 빠르고, select명령어를 할 경우 빠른속도를 지원한다.
  • 풀텍스트 인덱스를 지원하는데 이건 잘모르겠다?.. => 검색엔진과 유사한 방법으로 자연 언어를 이용해 검색할 수 있는 특별한 인덱스라고 알려져있다…
  • ready only기능에서는 유리. 하지만 row level locking을 지원하지 않아서 select, insert, update, delete시 해당 테이블 전체 lock이 걸린다.
  • Order By가 들어가면 InnoDB가 압도적으로 빠르다.
  • 트랙잭션이나 복구가 필요없을때, 전문검색이 필요할때, 대량의 데이터를 입력하는 배치성 테이블에서 유리.

InnoDB

  • MyISAM과 다르게 row level locking을 지원한다. 트랜잭션 처리가 필요한 대용량 데이터에 유리하다.
  • CRUD가 많은 서비스에 유리.
  • 풀텍스트 인덱스를 지원하지 않는다.
  • 복구가 필요할 경우, 정렬이 들어갈때, CRUD가 빈번하게 일어날때 유리.

Archive

  • 로그수집에 적합. 데이터가 메모리상에 압축되고 압축된 상태로 디스크에 저장되기때문에 row-level locking이 가능.
  • 한번 insert된 데이터는 update, delete를 할 수 없으며 인덱스를 지원하지 않는다.
create table myisam(
	Region text,
    Country text,
    ItemType text,
    SalesChannel text,
    OrderPriority text,
    OrderDate text,
    OrderID text,
    ShipDate text,
    UnitsSold int(11),
    UnitPrice double,
    UnitCost double,
    TotalRevenue double,
    TotalCost double,
    TotalProfit double
) ENGINE=MyISAM;

// bulk insert
load data local infile 'filepath' into table myisam fields terminated by ',' lines terminated by '\n' ignore 1 rows (
Region, Country, ItemType, SalesChannel, OrderPriority, OrderDate, OrderID, ShipDate, UnitsSold, UnitPrice, UnitCost, TotalRevenue, TotalCost, TotalProfit);

// Performance
select * from myisam; /* 4.9sec */
select * from inno; /*11.1 sec*/

select count(Region) from myisam; /* 0.4989 sec */
select count(Region) from inno; /* 5.7 sec */

select * from myisam where UnitsSold > 3000; /* Before indexing 3.49 sec After indexing 3.56sec */
select * from inno where UnitsSold > 3000; /* Before indexing 9.59 sec After indexing 10.49sec */

bulk insert example => bulk insert를 이용하니 100만개의 row를 insert하는데 8.6sec정도가 걸렸다.

  • innoDB와 MyISAM 스토리지 엔진을 사용해서 동일한 조건으로 100만 bulk insert를 했다. innoDB는 9.7초, myisam은 4.9초가 걸렸다.
  • 동일하게 select * from table을 통해 쿼리를 날렸는데, innoDB는 11.172초, myisam은 4.9초가 걸렸다. myisam이 select에 적합하다고 했는데, 시간 조회를 통해 확인할 수 있었다.
  • 위의 결과들로 인해 select에서는 myisam이 속도가 2배정도 특히 count를 하는데 있어서는 매우 빠른것을 확인할 수 있다.
  • 왜 인덱스를 걸어도 더 느린걸까??? 찾아보니 mysql innodb buffer poll size의 문제라고 알려져있다.

InnoDB buffer pool size

  • innoDB는 데이터와 인덱스를 메모리에 캐싱하기 위한 버퍼풀이라는 저장영역을 유지관리한다.
  • buffer pool에는 데이터와 메모리를 저장한다. engine type이 innodb인 테이블에만 해당이된다.
  • innodb쿼리 매커니즘 : index,데이터가 innodb_buffer_pool에 있는지 확인 => 데이터가 있으면 캐시 데이터 활용 => 디스크 I/O를 수행하여 새로운 데이터를 버퍼풀에 캐싱하여 LRU페이징 방식으로 캐싱수행.

엔진 변경하는 법

// InnoDB
ALTER TABLE `TABLE NAME` ENGINE = INNODB
// MyISAM
ALTER TABLE `TABLE NAME` ENGINE = MYISAM

// check current table engine
show table status;

// show engines
mysql> show engines;

오늘의느낀점

  • 디비엔진이라는 말을 들었을때 단순히 서버엔진을 이야기하는줄 알았었다. 대부분이 스토리지 엔진을 의미하는 것도 몰랐고 테이블을 만들때도 innoDB인지, MyISAM인지 구분을 안하고 사용을 했었다. MySQL에도 다양한 엔진들이 존재했고 특징들이 있다는것을 확인했다. 특히 가장 많이 사용하는 innoDB와 MyISAM에 동일한 데이터를 넣고 시간차를 비교해면서 select에서 얼마나 차이가 나는지 확인할 수 있었다. 내일은 프로시저를 이용하여 Create, Update, Delete를 테스트 해봐야겠다.
Written on February 1, 2020