[데이터베이스] 스토리지 엔진 - InnoDB vs myISAM

서유진·2022년 9월 26일

기술이 술술

목록 보기

4/5

스토리지 엔진(데이터베이스 엔진)

데이터베이스 엔진(database engine) 또는 스토리지 엔진(storage engine)은 데이터베이스 관리 시스템(DBMS)이 데이터베이스에 대해 데이터를 삽입, 추출, 업데이트 및 삭제(CRUD 참조)하는 데 사용하는 기본 소프트웨어 컴포넌트이다. 위키피디아
1개의 DBMS에 여러개의 스토리지 엔진을 지원하기도 한다.
서버 엔진이 필요한 데이터를 물리적인 장치에서 가져오는 역할을 한다.

`InnoDB`

InnoDB 엔진은 트랜젝션에 안전한 테이블을 제공하는 트랜잭션-세이프 스토리지 엔진이다.

MySQL 5.5부터 기본 스토리지 엔진
백업, 복구 지원
row-level locking 지원
- 트랜잭션 처리가 필요한 대용량의 데이터에 유리한 점이 있어서, 사용자의 CRUD가 많은 서비스에 유리하다.
자체적으로 메인 메모리 안에 데이터 캐싱과 인덱싱을 위한 버퍼 풀(pool)관리
오라클 기능 사용 가능 (제약 조건, 외래 키 생성 가능)
높은 퍼포먼스가 필요한 대용량 사이트에 적합
테이블 스페이스라는 논리적 저장공간을 활용한다.

table space

설명이 잘 되어있는 글

`myISAM`

MyISAM은 비-트랜젝션-세이프(non-transactional-safe) 테이블을 관리한다.

MySQL 5.5 이전까지 기본 스토리지 엔진
백업, 복구 지원
table-level-locking
- 안전하지만, 작업 시 특정 행을 수행하려고 하면 테이블 전체에 락이 걸려서 다른 사람이 작업할 수 없음
트랜잭션을 지원하지 않으므로 무결성이 보장되지 않음
전체 문장 검색 능력(Full Text 인덱싱이 가능해서 복합 검색이 가능)
트랜잭션을 지원하지 않기 때문에, auto-commit
테이블과 인덱스를 분리된 파일로 관리
항상 테이블에 ROW COUNT를 가지고 있기 때문에 SELECT count(*) 명령시 빠르고, SELECT 명령시에도 빠른 속도를 지원한다.
트래픽이 많은 웹, 정적인 테이블, Select 위주의 테이블, 갱신을 자주 하지 않는 시스템

정리

-	장점	단점	사용하는 곳
InnoDB	row-level locking, 트랜잭션 지원, ORACLE 기능 지원	~~Full Text 인덱싱 X~~ 👮‍♂️5.7 이후 가능 시스템 자원을 많이 사용함	대량의 테이블 보안성이 요구되는 테이블 데이터 입력 및 수정이 잦을 때
myISAM	Full Text 인덱싱 속도 빠름	row-level locking 안됨 무결성 보장 XXX	SELETE문 잦음 정적인 테이블 트래픽이 많은 웹

작은 테스트

1000개의 데이터가 들어가 있는 테이블과 1개의 데이터가 들어가 있는 테이블의 스토리지 엔진 테스트

대규모 데이터

-- 테이블 생성
CREATE TABLE test.tbl_board (
	board_idx int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    board_title  VARCHAR(30),
    board_content VARCHAR(30),
    board_writer  VARCHAR(30),
    board_regdate date
);
  
-- 더미 데이터 생성
-- https://chobopark.tistory.com/177
DELIMITER $$
DROP PROCEDURE IF EXISTS loopInsert$$
CREATE PROCEDURE loopInsert()
BEGIN
    DECLARE i INT DEFAULT 1;      
    WHILE i <= 500 DO
        INSERT INTO test.tbl_board(board_title , board_content, board_writer , board_regdate)
          VALUES(concat('제목',i), concat('내용',i), 'Admin', now());
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER $$
CALL loopInsert;

데이터 생성 후 엔진을 바꾼다

-- 스토리지 엔진 INNODB로 설정
ALTER TABLE test.tbl_board ENGINE = INNODB;
select * from test.tbl_board;
-- 스토리지 엔진 MYISAM로 설정
ALTER TABLE test.tbl_board ENGINE = MYISAM;
select * from test.tbl_board;

대규모 데이터의 경우 INNODB 일 때 SELECT 문 속도가 빠르다는 사실을 알 수 있다

소규모 데이터

  -- 테이블 생성
CREATE TABLE test.tbl_board2 (
	board_idx int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    board_title  VARCHAR(30),
    board_content VARCHAR(30),
    board_writer  VARCHAR(30),
    board_regdate date
);
  
-- 데이터 주입
insert into test.tbl_board2 values (1, '안녕', '안녕22','유진쓰', now());

마찬가지로 데이터 생성 후 엔진을 바꾼다

-- 스토리지 엔진 INNODB로 설정
ALTER TABLE test.tbl_board2 ENGINE = INNODB;
select * from test.tbl_board2;

-- 스토리지 엔진 MYISAM로 설정
ALTER TABLE test.tbl_board2 ENGINE = MYISAM;
select * from test.tbl_board2;