[스터디] 05. InnoDB vs MyISAM

목차

 

스토리지 엔진은 데이터베이스 관리 시스템(DBMS)에서 데이터베이스에 대해 데이터를 삽입, 추출, 업데이트 및 삭제하는 데 사용하는 기본 소프트웨어 컴포넌트입니다. MySQL 데이터베이스를 사용하는 경우, 스토리지 엔진으로 주로 InnoDB와 MyISAM이 대표적입니다. InnoDB와 MyISAM의 특징과 장단점 등에 대해서 정리하고 학습해 보겠습니다.

1. InnoDB

• 특징:
  • MySQL의 트랜잭션-세이프(Transaction-safe) 스토리지 엔진으로, 작업들을 그룹화하여 전체가 성공하거나 실패하도록 관리함.
  • MySQL 5.0 버전 이상에서는 기본 스토리지 엔진으로 설정됨.
  • ACID(Atomicity, Consistency, Isolation, Durability) 트랜잭션을 지원해 DML 작업을 하나의 작업 단위로 처리할 수 있음.
  • 데이터베이스에서 외래 키 제약 조건을 강제로 적용할 수 있어 데이터의 정확성을 유지할 수 있음.
  • commit, rollback, 장애 복구 기능을 지원하여 시스템 장애가 발생했을 때 복구할 수 있음.
  • Row-level Locking으로 높은 동시성을 제공하며, 하나의 행에 대한 락으로 전체 테이블이 락되는 것을 방지함.
  • 테이블과 인덱스를 테이블 스페이스에 저장하여 관리함. 테이블 스페이스는 다수의 서버 파일이나 디스크 파티션으로 구성됨.
  • InnoDB 테이블은 OS의 파일 사이즈 한계를 초과할 수 있어 대용량 데이터베이스 관리에 적합함.
• 장점:
  • 높은 데이터 무결성과 복구 기능
  • 다수의 사용자가 동시에 데이터에 접근해도 성능이 유지됨.
• 단점:
  • 상대적으로 많은 시스템 자원(메모리, 디스크 저장공간) 요구
  • 관리 및 운영이 복잡함.

2. MyISAM

• 특징:
  • MySQL의 비-트랜잭션-세이프(non-transaction-safe) 엔진으로, 트랜잭션을 지원하지 않음.
  • 모든 MySQL 버전에서 지원되며, 초기의 MySQL 스토리지 엔진으로 사용됨.
  • SELECT 작업에 최적화된 능력으로 빠른 읽기 성능을 제공함.
  • Table-level locking으로 데이터를 수정하는 DML 작업 시 테이블 전체에 배타적 락을 걸어 해당 락이 풀릴 때까지 다른 세션에서는 해당 테이블에 대한 SELECT나 DML 작업을 수행할 수 없음.
  • 테이블, 데이터, 인덱스를 각각 분리된 파일로 관리함. (예: tablename.FRM, tablename.MYD, tablename.MYI)
  • 풀텍스트 인덱스(Full-text index)를 지원하여 자연 언어를 이용한 검색이 가능함. (MySQL 8.0 버전부터는 InnoDB도 지원함.)
• 장점:
  • 빠른 읽기 속도
  • 간단한 구조로 관리가 용이함.
  • 적은 시스템 자원 사용
• 단점:
  • 트랜잭션과 외래 키를 지원하지 않아 데이터 무결성이 보장되지 않음.
  • 테이블 레벨 락킹으로 인해 동시성이 낮아짐.

3. InnoDB vs MyISAM

테이블이나 데이터베이스 설계에 대하여 InnoDB와 MyISAM의 주요 차이점은 참조 무결성과 트랜잭션 지원입니다.

• InnoDB는 외래 키를 통한 참조 무결성 지원이 가능합니다. 하지만, MyISAM은 외래 키를 지원하지 않아 응용 프로그램에서 별도의 무결성 체크 로직을 구현해야 합니다.
• InnoDB는 트랜잭션을 지원하여 여러 데이터 변경 작업을 하나의 작업 단위로 묶어 전체가 성공적으로 완료되거나 실패할 경우 원래 상태로 롤백하는 것이 가능합니다. 반면에 MyISAM은 트랜잭션을 지원하지 않아 갑작스러운 시스템 중단이 발생했을 때 데이터 손실의 위험이 있습니다.

특징	InnoDB	MyISAM
엔진 타입	트랜잭션-세이프 스토리지 엔진	비-트랜잭션-세이프 스토리지 엔진
데이터 관리	테이블 스페이스에서 통합 관리	파일 별도 관리
트랜잭션 지원	지원	지원하지 않음
록킹 메커니즘	row-level locking	table-level locking
외래 키 지원	지원	지원하지 않음
인덱싱	버퍼 풀을 이용한 데이터 캐싱과 인덱싱	풀텍스트 인덱스 지원
장점	데이터 무결성 보장, 복잡한 트랜잭션 처리 가능	빠른 데이터 읽기, 자원 효율성
단점	pull-text index 미지원, 관리 복잡성	row-level locking 미지원, 데이터 무결성 보장 어려움

4. 실습

MySQL Workbench 8.0 CE에서 두 엔진의 차이점을 확인하기 위한 간단한 실습을 진행했습니다.

 

1) testdb 데이터베이스 생성

CREATE DATABASE testdb;
USE testdb;

2) InnoDB 테이블 생성

CREATE TABLE customers_innodb (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    PRIMARY KEY (id)
) ENGINE=InnoDB;

3) MyISAM 테이블 생성

CREATE TABLE customers_myisam (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    PRIMARY KEY (id)
) ENGINE=MyISAM;

4) 데이터 삽입

INSERT INTO customers_innodb (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO customers_innodb (name, email) VALUES ('Bob', 'bob@example.com');
INSERT INTO customers_myisam (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO customers_myisam (name, email) VALUES ('Bob', 'bob@example.com');

5) InnoDB 트랜잭션 테스트

START TRANSACTION;
INSERT INTO customers_innodb (name, email) VALUES ('Charlie', 'charlie@example.com');
-- 롤백 전 데이터 확인
SELECT * FROM customers_innodb;
-- 롤백 실행
ROLLBACK;
-- 롤백 후 데이터 확인
SELECT * FROM customers_innodb;

▲ 롤백 전 데이터

▲ 롤백 후 데이터

InnoDB 테이블에서 트랜잭션을 시작하고 'Charlie'라는 데이터를 삽입한 후 롤백 전후 데이터 상태를 확인할 수 있습니다. 트랜잭션이 하나의 단위로 처리되어, 트랜잭션 내 수행된 모든 변경사항이 롤백 명령으로 취소된 것입니다.

 

6) MyISAM 록 테스트(트랜잭션 미지원이므로)

-- 테이블 락
LOCK TABLES customers_myisam WRITE;
-- 락이 걸린 상태에서 다른 세션에서 SELECT 시도
SELECT * FROM customers_myisam;
-- 락 해제
UNLOCK TABLES;

▲ 세션 1에서 SELECT 실행

▲ 세션 2에서 SELECT 실행

 

MyISAM 테이블에서는 테이블 락을 사용했습니다. 테이블 전체를 락킹하여 다른 세션(세션 2)의 데이터 접근을 차단하는 것을 확인할 수 있습니다.

 

7) SELECT 성능 비교

 

10,000개, 100.000개, 1,000,000개의 데이터를 삽입하고 SELECT 쿼리를 실행하여 응답 시간을 비교해 봅니다.

-- InnoDB 테이블에 1,000,000개의 레코드 삽입
INSERT INTO customers_innodb (name, email)
SELECT CONCAT('Name', a.num), CONCAT('email', a.num, '@example.com')
FROM (
    SELECT 1 + t1.num + t2.num*10 + t3.num*100 + t4.num*1000 + t5.num*10000 + t6.num*100000 AS num
    FROM (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1
    CROSS JOIN (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2
    CROSS JOIN (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3
    CROSS JOIN (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4
    CROSS JOIN (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t5
    CROSS JOIN (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t6
) a
LIMIT 1000000;

-- MyISAM 테이블에 1,000,000개의 레코드 삽입
INSERT INTO customers_myisam (name, email)
SELECT CONCAT('Name', a.num), CONCAT('email', a.num, '@example.com')
FROM (
    SELECT 1 + t1.num + t2.num*10 + t3.num*100 + t4.num*1000 + t5.num*10000 + t6.num*100000 AS num
    FROM (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1
    CROSS JOIN (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2
    CROSS JOIN (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3
    CROSS JOIN (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4
    CROSS JOIN (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t5
    CROSS JOIN (SELECT 0 AS num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t6
) a
LIMIT 1000000;

-- InnoDB 테이블에서 SELECT 쿼리 실행
SELECT * FROM customers_innodb;

-- MyISAM 테이블에서 SELECT 쿼리 실행
SELECT * FROM customers_myisam;

10,000개로는 데이터의 양이 적기 때문인지 조회 성능을 확인하기 어려웠습니다. 그래서 100,000개로도 진행했는데요.

마찬가지로 조회 성능을 확인하기 어려웠습니다. 마지막으로 1,000,000개로 진행했습니다.

100만 개의 데이터에 대한 조회도 성능을 비교하기가 어려워서, 이것을 해결할 방법은 고민을 해봐야 할 것 같습니다.

 

8) 동시성 테스트

-- 세션 1에서 트랜잭션 시작
START TRANSACTION;
UPDATE customers_innodb SET email = 'update@example.com' WHERE id = 1;

-- 세션 2에서 동시에 다른 레코드 조회
SELECT * FROM customers_innodb WHERE id = 2;
COMMIT;

▲ 세션 2의 SELECT 결과

-- 세션 1에서 테이블 락 걸기
LOCK TABLES customers_myisam WRITE;
UPDATE customers_myisam SET email = 'update@example.com' WHERE id = 1;

-- 세션 2에서 동시에 다른 레코드 조회 시도 (이 쿼리는 세션 1이 테이블 락을 해제할 때까지 대기 상태에 머물게 됨)
SELECT * FROM customers_myisam WHERE id = 2;
-- 세션 1에서 락 해제
UNLOCK TABLES;

▲ 세션 2의 SELECT 결과 2

InnoDB는 특정 행을 업데이트하는 동안 다른 행에 대한 접근이 가능하지만, MyISAM에서는 한 세션에서 테이블 락을 걸면 다른 세션에서 조회가 이루어지지 않는 것을 확인할 수 있었습니다.

 

일반적으로 InnoDB를 많이 사용하지만, InnoDB의 기능이 필요하지 않다면 MyISAM도 사용하는 것 같습니다. 각각의 특징과 사용 용도에 따라 InnoDB와 MyISAM 중 하나를 선택하면 됩니다.

5. 참고자료

1) GilLog, "[MySQL]InnoDB VS MyISAM", 2021.01.26, https://velog.io/@gillog/DBInnoDB-VS-MyISAM

2) http://www.mysqlkorea.com/gnuboard4/bbs/board.php?bo_table=community_03&wr_id=1702

3) https://stackoverflow.com/questions/12614541/whats-the-difference-between-myisam-and-innodb

 

이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.