MySQL, 효과적인 InnoDB 사용방법

Mysql 공식 InnoDB 테이블에 대한 모범 사례 참조

[1] 기본 키 설정

[1.1] 자주 조회되는 열을 기본 키로 지정

// 실제로 사용되지 않는 AUTO PK 값
CREATE TABLE student_courses (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 불필요한 AUTO_INCREMENT
    student_id BIGINT,
    course_id BIGINT,
    semester VARCHAR(20),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

SELECT * FROM student_courses
WHERE student_id = 1234 AND course_id = 5678;

// 실제로 사용되는 AUTO PK 값, 각 row 사이 무결성을 지킬 수 있다면 AUTO PK 값은 필요하지 않습니다.
CREATE TABLE student_courses (
    student_id BIGINT,
    course_id BIGINT,
    semester VARCHAR(20),
    PRIMARY KEY (student_id, course_id),  -- 자주 조회되는 실제 비즈니스 키
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

• 불필요한 인덱스 제거로 저장 공간 절약

• 자주 사용되는 조회 조건이 PK에 포함되어 있어 조회 성능 향상

• 실제 비즈니스 규칙을 DB 레벨에서 강제 (한 학생이 같은 과목을 같은 학기에 중복 수강할 수 없음)

[1.2] 명확한 기본 키가 없다면 자동 증가(AUTO_INCREMENT) 값 사용

• 실제로 고유한 식별자가 필요한 엔티티 테이블 (예: 사용자, 주문, 상품 등)

• 이력 관리가 필요한 테이블

• 자연키로 식별하기 어려운 경우
• 자연 키(Natural Keys) :
• 도메인 레벨에서 실제 의미가 있는 데이터를 키로 사용
• 주민등록번호, 사업자등록번호, 이메일 주소 등
• 대리 키(Surrogate key) :
• 의미 없이 인공적으로 생성한 키
• 보통 AUTO_INCREMENT나 UUID 사용

[2] 조인 최적화

[2.1] 조인되는 열들의 데이터 타입을 동일하게 맞추기

// 데이터 타입이 다른 경우
CREATE TABLE users (
    id VARCHAR(36),  // uuid
    name VARCHAR(100),
    PRIMARY KEY (id)
);

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT,
    user_id BIGINT,  // bigint
    amount DECIMAL(10,2),
    PRIMARY KEY (id)
    // fk 키 설정 없음
);

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;  // 타입 변환으로 인한 성능 저하

[2.2] 테이블 간 조인할 때는 반드시 외래 키 설정

• FK 키의 장점
• 모든 PK/FK가 동일한 데이터 타입(BIGINT) 사용
• 적절한 외래키 제약조건으로 데이터 정합성 보장
• 자동 생성되는 인덱스로 조인 성능 최적화
• 비즈니스 식별자(product_code)는 UNIQUE 인덱스로 분리
• 연관 테이블 간의 참조 무결성 보장

[3] 트랜잭션 관리

[3.1] 자동 커밋 기능 OFF

// 자동 커밋 모드 (각 INSERT마다 커밋 발생)
SET autocommit = 1;

// 10,000건의 주문 데이터 입력 (10,000번의 커밋 발생)
INSERT INTO orders (user_id, amount) VALUES (1, 1000);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 2);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 2, 1);

• 매 INSERT마다 디스크 쓰기 발생

• 성능 극도로 저하

• 도중 실패 시 부분적 데이터만 입력될 수 있음

[3.2] 관련된 작업들은 START TRANSACTION과 COMMIT으로 묶기

// 주문 처리 로직 (트랜잭션 없음)

// 주문 생성
INSERT INTO orders (user_id, total_amount) VALUES (1, 5000);
SET order_id = LAST_INSERT_ID();

// 주문 상품 입력
INSERT INTO order_items (order_id, product_id, quantity) 
VALUES (order_id, 1, 2);

// 재고 감소
UPDATE products 
SET stock = stock - 2 
WHERE id = 1;

• 원자성 보장 못함

• 일관성 깨질 수 있음

• 부분적 데이터만 처리될 수 있음

[3.3] 너무 잦은 커밋, 너무 큰 트랜잭션 피하기

// 한 트랜잭션에서 너무 많은 작업 수행
START TRANSACTION;

// 대량의 데이터 처리
DELETE FROM old_logs WHERE created_at < '2023-01-01'; // 수백만 건
UPDATE users SET status = 'inactive' WHERE last_login < '2023-06-01'; // 수십만 건
INSERT INTO audit_logs SELECT * FROM temporary_logs; // 수백만 건

// 다른 세션의 작업이 오래 대기하게 됨
UPDATE products SET stock = stock - 1 WHERE id = 123;

COMMIT;

• 트랜잭션 롤백 시 복구가 오래 걸림

• 메모리 사용량 증가

• 다른 트랜잭션 블로킹

• 데드락 가능성 증가

[4] 테이블 잠금 관리

[4.1] 적절한 잠금 관리

• 부적절한 인덱스로 인한 과도한 Row Lock

// 인덱스 없는 컬럼으로 업데이트 (테이블 풀 스캔)
START TRANSACTION;

UPDATE orders 
SET status = 'COMPLETED' 
WHERE customer_name = 'John'  // customer_name에 인덱스 없음
FOR UPDATE;  // 테이블 전체 스캔하면서 모든 행을 잠금

COMMIT;

• Lock 범위가 넓은 배치 처리

START TRANSACTION;

// 많은 수의 주문을 한 번에 처리
SELECT * FROM orders 
WHERE status = 'PENDING' 
AND created_at < '2024-01-01'  // 대량의 데이터
FOR UPDATE;  // 많은 행을 오래 잠금

// 여러 처리 수행...
UPDATE orders SET status = 'PROCESSING' 
WHERE id IN (/* 위에서 선택된 주문 IDs */);

COMMIT;

• 잘못된 순서의 Lock 획득 (데드락 유발)

// 세션 1
START TRANSACTION;
UPDATE users SET points = points - 100 WHERE id = 1;
// 다른 처리
UPDATE orders SET status = 'COMPLETED' WHERE id = 999;
COMMIT;

// 세션 2 (동시에 실행)
START TRANSACTION;
UPDATE orders SET status = 'PROCESSING' WHERE id = 999;
// 다른 처리
UPDATE users SET last_order_date = NOW() WHERE id = 1;
COMMIT;

[4.2] SELECT ... FOR UPDATE 구문으로 필요한 행만 잠금

// 재고 차감 프로세스
START TRANSACTION;

// 재고 확인 및 잠금
SELECT id, stock 
FROM products 
WHERE id = 123 
AND stock > 0 
FOR UPDATE;  // 다른 트랜잭션이 이 행을 수정하지 못하도록 잠금

// 재고가 충분한 경우만 차감
UPDATE products 
SET stock = stock - 1 
WHERE id = 123;

COMMIT;

// ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

// 필요한 특정 주문만 잠금
SELECT * FROM orders 
WHERE id = 123 
AND status = 'PENDING'
FOR UPDATE SKIP LOCKED;  // 이미 잠긴 행은 건너뜀

[5] 저장 공간 최적화

• 데이터 특성에 따라 테이블/페이지 압축 고려

[5-1] innodb_file_per_table 활성화하여 테이블별 separate 파일 사용

// 서버 설정 확인
SHOW VARIABLES LIKE 'innodb_file_per_table';

// 활성화 (my.cnf 또는 my.ini 파일에 설정)
innodb_file_per_table=1

// 압축된 테이블 생성
CREATE TABLE compressed_table (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    description TEXT,
    PRIMARY KEY (id)
) ROW_FORMAT=COMPRESSED 
  KEY_BLOCK_SIZE=8;  // 8KB 압축 (기본 16KB의 절반)

// 기존 테이블 압축 변환
ALTER TABLE existing_table 
ROW_FORMAT=COMPRESSED 
KEY_BLOCK_SIZE=8;

[5-2] 데이터 특성에 따라 테이블/페이지 압축 고려

• SELECT 쿼리
• 문법적 차이 없음

SELECT * FROM compressed_table WHERE id = 123;

• 읽기 성능:
• 디스크 I/O 감소 (→ 더 빠름)
• CPU 사용량 증가 (압축 해제 시)
• 특히 SSD보다 HDD에서 성능 향상이 더 큼
• 메모리 사용:
• 버퍼 풀에서는 압축 해제된 상태로 저장
• 더 많은 데이터를 메모리에 캐시 가능

• INSERT/UPDATE

// 일반 쿼리와 동일
INSERT INTO compressed_table (name, description) 
VALUES ('test', 'test description');

• 쓰기 작업 시 CPU 오버헤드 발생 (압축 처리)
• 특히 대량 INSERT/UPDATE 시 성능 저하 가능

[5.3] 권장 사용 예시

• 권장 사용
• 읽기가 많고 쓰기가 적은 테이블
• 텍스트 데이터가 많은 테이블
• 디스크 공간이 제한적인 환경

• 비권장
• OLTP 환경의 트랜잭션이 많은 테이블
• 이미 압축된 데이터(이미지, PDF 등)가 많은 테이블
• CPU 리소스가 제한적인 환경

[6] 설정 관리

SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';

CREATE TABLE test_table (
    id INT PRIMARY KEY
) ENGINE=InnoDB;  

// 지원하지 않는 엔진인 경우 에러 발생:
// ERROR 1286 (42000): Unknown storage engine 'InnoDB'