SHOW ENGINES;위와 같이 명령어를 입력하면 database의 엔진을 확인할 수 있습니다. 현재 사용하고 있는 dbms는 mariadb입니다.
| Engine | Support | Comment | Transactions | XA | Savepoints |
|---|---|---|---|---|---|
| InnoDB | DEFAULT | Supports transactions | YES | YES | YES |
| MyISAM | YES | Non-transactional engine | NO | NO | NO |
| MEMORY | YES | Stored in memory | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
- Engine: 스토리지 엔진의 이름.
- Support: 엔진이 활성화되어 있는지 여부 (
YES,NO,DEFAULT). - Comment: 간단한 설명.
- Transactions: 트랜잭션 지원 여부.
- XA: 분산 트랜잭션 지원 여부.
- Savepoints: 트랜잭션 내의 저장 지점 지원 여부.
InnoDB와 MyISAM 비교
| 특징 | InnoDB | MyISAM |
|---|---|---|
| 트랜잭션 지원 | 지원함 (ACID 준수) | 지원하지 않음 |
| 외래 키 | 지원함 | 지원하지 않음 |
| 잠금 방식 | 행 수준 잠금 (Row-level locking) | 테이블 수준 잠금 (Table-level locking) |
| 데이터 복구 (Crash Recovery) | 자동 복구 기능 제공 | 자동 복구 기능 없음 |
| 성능 | 다중 사용자 환경에서 뛰어난 동시성 제공 | 읽기 작업(SELECT)에서 더 빠른 성능 |
| 디스크 공간 사용량 | 복잡한 구조로 인해 더 많은 디스크 공간 사용 | 단순한 구조로 인해 디스크 공간을 효율적으로 사용 |
| 전체 텍스트 검색 | 지원하지 않음 | 지원함 (Full-Text Search Index 사용 가능) |
| 데이터 저장 방식 | 데이터를 클러스터된 인덱스에 저장 (Clustered Index) | 데이터를 독립적으로 저장 |
| 락 경합 | 여러 트랜잭션에서 적은 경합 발생 | 다중 쓰기 작업 시 경합 발생 |
| ACID 특성 | 지원 (Atomicity, Consistency, Isolation, Durability) | 지원하지 않음 |
주요 차이점 설명
1. 트랜잭션 지원
- InnoDB는 트랜잭션을 지원하며,
COMMIT,ROLLBACK등을 통해 작업을 원자적으로 관리할 수 있습니다. - MyISAM은 트랜잭션을 지원하지 않아, 데이터 변경이 즉시 저장되며 작업의 원자성을 보장하지 않습니다.
2. 잠금 수준
- InnoDB는 행 수준 잠금을 제공하여, 동시성을 높이고 다중 사용자 환경에서 성능을 향상시킵니다.
- MyISAM은 테이블 수준 잠금을 사용하여, 동시 업데이트 시 성능 저하가 발생할 수 있습니다.
3. 외래 키(Foreign Key)
- InnoDB는 외래 키 제약 조건을 지원하여 데이터 무결성을 보장합니다.
- MyISAM은 외래 키를 지원하지 않아, 관계형 무결성은 애플리케이션 레벨에서 처리해야 합니다.
4. 데이터 복구
- InnoDB는 로그 파일을 기반으로 충돌 이후 데이터 복구를 지원합니다.
- MyISAM은 데이터 손실 발생 시 수동으로 복구해야 하며, 복구 시간이 길어질 수 있습니다.
5. 성능
- InnoDB는 다중 쓰기 및 읽기 환경에서 성능이 우수하며, 대규모 트랜잭션 처리에 적합합니다.
- MyISAM은 단일 쓰기 및 읽기 작업에서 더 높은 성능을 발휘합니다. 특히 읽기 중심의 시스템에서 효율적입니다.
6. 전체 텍스트 검색
- MyISAM은 기본적으로 Full-Text Search를 지원하여 텍스트 기반 데이터 검색에 유리합니다.
- InnoDB는 MySQL 5.6 이후부터 Full-Text Search를 제한적으로 지원합니다.
사용 시 고려 사항
InnoDB 사용에 적합한 경우
- 데이터 무결성이 중요한 애플리케이션 (e.g., 금융 시스템).
- 다중 사용자가 데이터를 동시에 읽고 쓰는 환경.
- 트랜잭션이 필요한 애플리케이션.
MyISAM 사용에 적합한 경우
- 읽기 작업이 대부분인 데이터베이스 (e.g., 로그 분석).
- 외래 키나 트랜잭션이 필요 없는 간단한 데이터 구조.
- 데이터 복구가 중요한 고려사항이 아닌 경우.
실습을 통해 차이 이해하기
(1) 테이블 생성 시 엔진 선택
CREATE TABLE test_innodb (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
) ENGINE=InnoDB;
CREATE TABLE test_myisam (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
) ENGINE=MyISAM;
(2) 기본 스토리지 엔진 확인
SHOW VARIABLES LIKE 'default_storage_engine';
(3) 현재 테이블의 엔진 확인
SELECT TABLE_NAME, ENGINE
FROM information_schema.tables
WHERE table_schema = 'sample';
1. Transaction(트랜잭션)의 정의
- 트랜잭션은 데이터베이스에서 실행되는 작업의 논리적인 단위입니다.
- 트랜잭션은 여러 작업(쿼리)을 묶어 하나의 작업처럼 실행하며, 모두 성공하거나 모두 실패하도록 보장합니다.
- ACID 특성을 기반으로 동작합니다:
- Atomicity (원자성): 트랜잭션의 모든 작업은 성공하거나 실패하며, 일부만 실행되는 경우는 없음.
- Consistency (일관성): 트랜잭션 완료 후 데이터베이스는 항상 일관된 상태를 유지함.
- Isolation (격리성): 트랜잭션은 독립적으로 실행되어 다른 트랜잭션의 영향을 받지 않음.
- Durability (지속성): 트랜잭션 완료 후 변경된 데이터는 영구적으로 저장됨.
2. TCL(Transaction Control Language)의 정의
- TCL은 트랜잭션을 관리하기 위한 SQL 명령어 집합입니다.
- 트랜잭션의 완료 또는 취소를 결정하고, 데이터베이스의 상태를 제어합니다.
3. TCL 명령어
MYSQL의 경우, TCL 명령어는 auto commit을 제공합니다.
(1) COMMIT
- 트랜잭션의 작업을 데이터베이스에 영구적으로 저장.
- 트랜잭션 성공 시 호출.
- 명령어 예시:
START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
(2) ROLLBACK
- 트랜잭션 내의 모든 작업을 취소하고, 데이터베이스를 이전 상태로 되돌림.
- 트랜잭션 실패 시 호출.
- 명령어 예시:
START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 실수로 에러가 발생한 경우 ROLLBACK;
(3) SAVEPOINT
- 트랜잭션 내에서 중간 저장 지점을 생성.
- 필요 시 저장 지점으로 되돌릴 수 있음.
- 명령어 예시:
START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; SAVEPOINT sp1; UPDATE accounts SET balance = balance - 50 WHERE id = 2; ROLLBACK TO sp1; -- sp1으로 롤백
(4) SET TRANSACTION
- 트랜잭션의 격리 수준을 설정.
- 트랜잭션 실행 중 다른 트랜잭션과의 격리 정도를 제어.
- 명령어 예시:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
4. Transaction의 실행 흐름
(1) 기본 흐름
START TRANSACTION: 트랜잭션 시작.- 쿼리 실행: 여러 데이터 변경 작업 수행.
COMMIT또는ROLLBACK: 성공 시COMMIT, 실패 시ROLLBACK.
(2) SAVEPOINT 활용 흐름
START TRANSACTION- 중간 저장 지점 생성:
SAVEPOINT - 필요 시 저장 지점으로 되돌아가기:
ROLLBACK TO SAVEPOINT - 최종적으로
COMMIT또는ROLLBACK
5. 트랜잭션 격리 수준 (Isolation Levels)
트랜잭션 간 간섭을 방지하기 위한 격리 수준을 설정합니다.
| 격리 수준 | 설명 | 읽기 현상 |
|---|---|---|
| READ UNCOMMITTED | 커밋되지 않은 데이터를 읽을 수 있음. (Dirty Read 가능) | Dirty Read, Non-repeatable Read, Phantom Read |
| READ COMMITTED | 커밋된 데이터만 읽을 수 있음. (Non-repeatable Read 가능) | Non-repeatable Read, Phantom Read |
| REPEATABLE READ | 동일 트랜잭션 내에서는 동일 데이터만 읽음. (Phantom Read 가능) | Phantom Read |
| SERIALIZABLE | 트랜잭션을 순차적으로 실행하여 완벽한 격리 보장. (가장 느리지만 완벽한 일관성 제공) | 없음 |
6. 예제
(1) 기본 트랜잭션 작업
START TRANSACTION;
-- 데이터 변경
UPDATE products SET stock = stock - 1 WHERE product_id = 101;
INSERT INTO orders (order_id, product_id, quantity) VALUES (1, 101, 1);
-- 트랜잭션 저장
COMMIT;
(2) SAVEPOINT 활용
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- sp1으로 롤백
ROLLBACK TO sp1;
COMMIT;
주의사항
1. 서로 다른 세션에서의 데이터 접근
(1) 트랜잭션의 격리 수준 문제
- 서로 다른 세션이 동시에 데이터에 접근할 경우, 격리 수준 (Isolation Level) 에 따라
트랜잭션 간 데이터 충돌이 발생하거나 읽기 오류가 나타날 수 있습니다. - 격리 수준에 따라 나타날 수 있는 문제:
- Dirty Read: 커밋되지 않은 데이터를 다른 세션에서 읽음.
- Non-repeatable Read: 동일 트랜잭션 내에서 같은 데이터를 읽었을 때 값이 달라짐.
- Phantom Read: 다른 세션에서 삽입된 데이터가 갑자기 조회 결과에 나타남.
(2) 해결 방법
- 적절한 격리 수준 설정:
- 예:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
- 예:
- 트랜잭션 충돌을 방지하기 위해 락(Lock) 사용:
- 공유 락: 다른 세션에서 읽기는 가능하지만 쓰기는 제한.
- 배타 락: 다른 세션에서 읽기 및 쓰기를 모두 제한.
- 예시:
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 배타 락
2. 트랜잭션 도중에 DDL 실행
(1) DDL 명령어의 특징
- DDL 명령어 (
CREATE,DROP,ALTER등)는 자동으로 COMMIT을 발생시킵니다. - 트랜잭션 중에 DDL 명령어를 실행하면 트랜잭션이 강제로 종료됩니다.
(2) 예시
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- DDL 실행 (자동 COMMIT 발생)
CREATE TABLE new_table (id INT);
-- 이후 트랜잭션은 유효하지 않음
ROLLBACK; -- 아무 효과 없음
(3) 해결 방법
- 트랜잭션 내에서는 DDL 명령어를 사용하지 않도록 설계.
- DDL이 필요한 경우, 트랜잭션 외부에서 실행:
- 트랜잭션을
COMMIT하거나ROLLBACK한 후에 DDL 실행.
- 트랜잭션을
- DDL 작업을 분리하여 실행하고 필요한 경우 애플리케이션에서 트랜잭션을 재구성.
3. 자동 커밋 (Autocommit) 주의
- 데이터베이스의
autocommit설정에 따라 트랜잭션 동작이 달라질 수 있습니다. - 자동 커밋 활성화 상태에서 트랜잭션 사용 시 문제:
START TRANSACTION을 명시적으로 호출하지 않으면 각 쿼리가 자동으로 커밋됩니다.
(1) 확인 및 설정
-- 자동 커밋 상태 확인
SELECT @@autocommit;
-- 자동 커밋 비활성화
SET autocommit = 0;
4. 트랜잭션 내에서 잠금 경합 (Lock Contention)
(1) 문제 상황
- 여러 트랜잭션이 동일한 데이터에 접근하려고 하면 잠금 경합이 발생.
- 경합이 심할 경우 Deadlock(교착 상태) 이 발생할 수 있음.
(2) 해결 방법
-
트랜잭션이
짧고 간결하게유지되도록 설계. -
잠금 대상을
최소화:- 예: 특정 행만 잠그도록 쿼리를 설계.
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 특정 행 잠금 -
Deadlock 감지 및 회피:
- 데이터베이스는 Deadlock을 감지하고 한쪽 트랜잭션을 강제로 중단시킴.
- 애플리케이션에서 이를 감지하고 적절히 재시도하도록 구현.
5. 트랜잭션과 연결 해제 문제
(1) 문제 상황
- 트랜잭션 중 클라이언트가 데이터베이스 연결을 끊으면, 데이터베이스는 자동으로 해당 트랜잭션을 ROLLBACK 처리.
- 장시간 트랜잭션은 연결 끊김 가능성을 높임.
(2) 해결 방법
- 트랜잭션 실행 시간을 최소화.
- 장시간 트랜잭션이 필요한 경우 Connection Pooling을 사용하여 안정성을 확보.
6. 트랜잭션과 로그 관리
- 로그 파일 크기와 트랜잭션 처리량이 관련.
- 트랜잭션 로그 (
redo log)가 가득 차면 성능 저하 발생.
(1) 로그 설정 확인
SHOW VARIABLES LIKE 'innodb_log_file_size';
(2) 로그 크기 조정
SET GLOBAL innodb_log_file_size = 256M;
graph TD
A[Service Layer] --> B[Persistence Layer]
B --> C["Data Access Object (DAO)"]
C --> D[Database Connection Pool]
D --> E[Database]
E --> D
D --> C
C --> B
B --> A
