레거시 MySQL 서버를 인수받았는데 일부 테이블이 MyISAM 엔진을 사용하고 있습니다. 트랜잭션 중 서버가 재시작됐고 데이터 일부가 반영안된 상태로 남았습니다. MyISAM은 트랜잭션을 지원하지 않아서 중간 실패 시 롤백이 없습니다. 스토리지 엔진이 다르면 잠금 방식, 복구 가능성, 성능 특성이 모두 달라집니다. MySQL을 올바르게 사용하려면 InnoDB의 핵심 특성을 이해하는 것이 출발점입니다.

InnoDB vs MyISAM

InnoDB는 트랜잭션, 외래 키, 행 수준 잠금, WAL 기반 자동 복구를 모두 지원합니다. MyISAM은 이 기능들이 없어 데이터 일관성을 보장하지 못하고, 쓰기 충돌 시 테이블 전체를 잠급니다. 2010년대 이전 레거시 시스템에서만 MyISAM을 볼 수 있으며, 모든 새 테이블은 반드시 InnoDB로 만들어야 합니다.

테이블 엔진 확인과 변환 방법은 다음과 같습니다.

SHOW TABLE STATUS WHERE Name = 'users';
SHOW CREATE TABLE users;

ALTER TABLE legacy_table ENGINE = InnoDB;

실행 완료 또는 조회 결과가 표시됩니다.

my.cnf에 default_storage_engine = InnoDB를 설정하면 CREATE TABLE 시 ENGINE 절을 생략해도 InnoDB가 기본으로 적용됩니다.

클러스터드 인덱스 — InnoDB의 핵심

InnoDB에서 테이블 데이터는 PK 순서로 물리적으로 정렬된 B-Tree에 직접 저장됩니다. 별도의 힙(heap) 파일이 없고 PK B-Tree 리프 노드 자체가 데이터 행입니다. 이 구조를 클러스터드 인덱스라고 합니다.

PK 기반 범위 조회는 물리적으로 인접한 페이지를 순차 읽기하므로 매우 빠릅니다. 반면 UUID처럼 무작위 순서의 PK를 사용하면 새 행을 삽입할 때마다 기존 B-Tree 중간에 끼워 넣어야 해 페이지 단편화가 빈번히 발생합니다. 세컨더리 인덱스는 내부적으로 PK를 포함하므로, 세컨더리 인덱스로 조회하면 PK를 통해 실제 데이터에 한 번 더 접근하는 이중 조회가 발생합니다.

SELECT * FROM orders WHERE id BETWEEN 1000 AND 2000;

CREATE TABLE events_uuid (
    id    CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    title VARCHAR(200)
);

CREATE INDEX idx_users_email ON users (email);

Buffer Pool — InnoDB 메모리 캐시

Buffer Pool은 InnoDB가 디스크에서 읽은 페이지를 메모리에 캐시하는 공간입니다. 히트율이 높을수록 디스크 I/O 없이 메모리에서 처리되므로 성능이 좋습니다. 일반적으로 서버 전체 RAM의 60~70%를 Buffer Pool에 할당합니다. 히트율이 95% 미만이면 Buffer Pool 크기 증가를 검토하세요.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

SELECT
    (1 - (
        (SELECT variable_value FROM information_schema.global_status
         WHERE variable_name = 'Innodb_buffer_pool_reads') /
        (SELECT variable_value FROM information_schema.global_status
         WHERE variable_name = 'Innodb_buffer_pool_read_requests')
    )) * 100 AS buffer_pool_hit_rate;

my.cnf 권장 설정 예시입니다. innodb_buffer_pool_instances는 Buffer Pool을 여러 인스턴스로 분할해 동시 접근 시 잠금 경쟁을 줄입니다.

SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

utf8 vs utf8mb4 함정

MySQL의 utf8 캐릭터셋은 실제 UTF-8 표준과 다릅니다. 최대 3바이트만 지원하므로 4바이트가 필요한 이모지(U+1F600 이상)를 저장하면 오류가 발생합니다. 진짜 UTF-8을 사용하려면 반드시 utf8mb4를 써야 합니다.

Collation은 utf8mb4_unicode_ci를 기본으로 사용하세요. 유니코드 표준을 올바르게 따르며 독일어 ß 같은 특수 문자도 정확히 처리합니다. utf8mb4_general_ci는 성능이 약간 빠르지만 표준 준수도가 낮아 레거시 시스템에서만 사용합니다.

CREATE TABLE comments (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE comments_bad
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Collation이 다른 두 테이블을 JOIN하면 오류가 발생합니다. 테이블 A가 utf8mb4_unicode_ci이고 테이블 B가 utf8mb4_general_ci이면 ON 조건에서 ERROR 1267: Illegal mix of collations가 납니다. 임시 해결책은 아래처럼 명시적 COLLATE를 지정하는 것이지만, 근본적으로는 양쪽 테이블의 Collation을 통일해야 합니다.

SELECT a.*, b.*
FROM table_a a
JOIN table_b b ON a.name = b.name COLLATE utf8mb4_unicode_ci;

주문 ID가 연속되지 않습니다. 결제 실패한 주문이 있었을 뿐인데 ID에 구멍이 생겼고 고객 문의가 들어옵니다. 다른 서비스에서 MySQL로 마이그레이션했는데 GROUP BY 쿼리가 MySQL에서만 다른 결과를 반환합니다. 운영 서버에서 슬로우 쿼리가 주기적으로 발생하는데 어디서 확인해야 할지 모릅니다. MySQL 고유의 동작 방식을 모르면 코드는 맞는데 결과가 예상과 다른 상황을 만납니다.

AUTO_INCREMENT 갭(Gap) 문제

MySQL의 AUTO_INCREMENT는 INSERT 시 번호를 먼저 할당하고, 트랜잭션이 롤백되어도 그 번호를 재사용하지 않습니다. 따라서 id 값에는 건너뜀이 생깁니다. 또한 서버를 재시작하면 이전에 삭제된 최대값 이후부터 다시 시작하는 것이 아니라, 테이블 MAX(id) + 1에서 시작하므로 삭제된 레코드의 ID가 재사용될 수 있습니다(MySQL 5.7 이하).

innodb_autoinc_lock_mode 설정에 따라 갭 발생 방식이 다릅니다. 기본값 1(연속 모드)은 단건 INSERT에서는 갭이 없지만 대량 INSERT에서는 발생할 수 있습니다. 값 2(인터리브 모드)는 성능이 높지만 갭이 더 자주 발생합니다.

BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 50000);
ROLLBACK;

BEGIN;
INSERT INTO orders (user_id, amount) VALUES (2, 30000);
COMMIT;

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

MySQL 5.7 이하의 느슨한 GROUP BY

MySQL 5.7 이하는 ONLY_FULL_GROUP_BY sql_mode가 기본으로 비활성화되어 있습니다. 이 때문에 GROUP BY 절에 없는 컬럼을 SELECT할 수 있어 비결정적 결과가 반환됩니다. PostgreSQL이라면 오류로 처리했을 쿼리가 MySQL에서는 조용히 잘못된 값을 반환합니다.

MySQL 5.7 이상에서 안전한 설정과 올바른 쿼리 작성법입니다.

SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

SELECT user_id, MAX(name) AS name, SUM(amount) AS total
FROM orders
GROUP BY user_id;

ON DUPLICATE KEY UPDATE — Upsert 패턴

ON DUPLICATE KEY UPDATE는 PRIMARY KEY 또는 UNIQUE KEY 충돌 시 INSERT 대신 UPDATE를 실행합니다. VALUES() 함수는 INSERT하려 했던 값을 참조합니다. PostgreSQL의 EXCLUDED와 동일한 역할입니다.

INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (42, 1, NOW())
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    last_login  = NOW();

INSERT INTO inventory (product_id, stock)
VALUES (100, 50)
ON DUPLICATE KEY UPDATE
    stock = stock + VALUES(stock);

INSERT INTO product_prices (product_id, price, updated_at)
VALUES
    (1, 10000, NOW()),
    (2, 20000, NOW()),
    (3, 30000, NOW())
ON DUPLICATE KEY UPDATE
    price      = VALUES(price),
    updated_at = VALUES(updated_at);

SHOW PROCESSLIST와 슬로우 쿼리 찾기

운영 중 응답이 느릴 때 가장 먼저 실행 중인 쿼리 목록을 확인합니다. SHOW FULL PROCESSLIST는 전체 쿼리 텍스트를 보여줍니다. 슬로우 쿼리 로그는 my.cnf에서 활성화하고 long_query_time을 1초로 설정하면 1초 이상 걸리는 쿼리를 모두 기록합니다.

SHOW FULL PROCESSLIST;

SELECT *
FROM information_schema.PROCESSLIST
WHERE TIME > 10
  AND COMMAND != 'Sleep'
ORDER BY TIME DESC;

KILL QUERY 12345;
KILL 12345;

EXPLAIN 출력 해석

EXPLAIN은 MySQL이 쿼리를 어떻게 실행할지 보여줍니다. type 컬럼이 핵심입니다. const와 eq_ref는 최적 상태이고, ALL(Full Table Scan)은 인덱스가 없거나 적절하지 않다는 신호입니다. Extra 컬럼에 Using filesort가 보이면 인덱스를 이용하지 않는 정렬이 발생 중입니다.

EXPLAIN SELECT u.name, COUNT(o.id) AS orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
ORDER BY orders DESC
LIMIT 10;

key 컬럼이 NULL이면 인덱스 미사용, Extra에 Using index가 있으면 커버링 인덱스(Covering Index)로 데이터 페이지 접근 없이 인덱스만으로 처리되는 최적 상태입니다.

MySQL vs MariaDB 주요 차이점

MariaDB는 MySQL 5.5 포크에서 시작한 독립 프로젝트입니다. 대부분의 SQL 문법이 호환되지만 고급 기능에서 차이가 있습니다.

MariaDB의 SEQUENCE는 Oracle 스타일 시퀀스 객체로, AUTO_INCREMENT의 갭 문제를 우회하는 데 활용할 수 있습니다.

CREATE SEQUENCE order_seq START WITH 1000;
SELECT NEXTVAL(order_seq);