mysql-patterns

MySQL 패턴

MySQL 또는 MariaDB 스키마 설계, 마이그레이션, 느린 쿼리 조사, 큐 스타일 트랜잭션, 연결 풀, 프로덕션 데이터베이스 설정 시 이 스킬을 사용하십시오. MySQL과 MariaDB는 여러 SQL 세부 사항이 분기되었으므로, 기능별 패턴을 적용하기 전에 정확한 버전을 확인하는 것이 좋습니다.

활성화 시점

• MySQL 또는 MariaDB 테이블, 인덱스, 제약 조건 설계
• 대규모 프로덕션 테이블에서 실행되기 전 마이그레이션 검토
• 느린 쿼리, 잠금 대기, 데드락, 연결 고갈 디버깅
• 키셋 페이지네이션, 업서트, 풀텍스트 검색, JSON 컬럼, 큐 추가
• 애플리케이션 연결 풀, 읽기 복제본, TLS, 슬로우 로그 구성

버전 확인

엔진과 버전을 식별하는 것부터 시작하세요:

SELECT VERSION();
SHOW VARIABLES LIKE 'version_comment';

구문이 다른 경우 MySQL과 MariaDB 지침을 분리하여 유지하세요:

• MySQL 문서는 ON DUPLICATE KEY UPDATE에서 VALUES(col)를 대체하는 행 별칭을 문서화합니다. VALUES(col)은 더 이상 사용되지 않습니다.
• MariaDB 문서는 ON DUPLICATE KEY UPDATE에서 삽입된 값을 참조하는 지원되는 방법으로 VALUES(col)을 문서화합니다. 크로스 엔진 호환성을 위해 사용하세요.
• SKIP LOCKED는 큐와 같은 작업에만 적합합니다. 잠긴 행을 건너뛰고 일관성 없는 뷰를 반환할 수 있으므로 일반 회계나 무결성이 중요한 읽기에는 사용하지 마십시오.

스키마 기본값

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    account_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(32) NOT NULL,
    total DECIMAL(15, 2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_orders_account_status_created (account_id, status, created_at),
    KEY idx_orders_active (account_id, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

기본 선택 사항:

사용 사례	선호	지양
대체 기본 키	BIGINT UNSIGNED AUTO_INCREMENT	20억 행 이상 커질 수 있는 테이블의 INT
UUID 조회 키	변환 헬퍼와 함께 BINARY(16)	핫 테이블의 VARCHAR(36) 기본 키
금액 및 정확한 수량	DECIMAL(p, s)	FLOAT 또는 DOUBLE
사용자 대상 텍스트	utf8mb4 테이블 및 인덱스	MySQL utf8 / utf8mb3 기본값
애플리케이션 타임스탬프	앱에서 UTC를 관리하는 DATETIME	DATETIME이 시간대 메타데이터를 저장한다고 가정
소프트 삭제	deleted_at DATETIME NULL + 범위 지정 인덱스	인덱스 없이 소프트 삭제된 행 필터링
확장 가능한 상태 값	조회 테이블 또는 제한된 VARCHAR	값이 자주 변경되는 경우 ENUM

인덱싱

복합 인덱스 순서는 일반적으로 동등 조건을 먼저, 그 다음에 범위 또는 정렬 컬럼을 따릅니다:

CREATE INDEX idx_orders_account_status_created
    ON orders (account_id, status, created_at);

SELECT id, total
FROM orders
WHERE account_id = ?
  AND status = 'pending'
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT 50;

인덱스를 추가하거나 변경하기 전에 EXPLAIN을 사용하세요:

EXPLAIN
SELECT id, total
FROM orders
WHERE account_id = 123 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

조사해야 할 신호:

필드	위험 신호
type	대규모 테이블에서 ALL
key	선택적 조건이 있는데 NULL
rows	대화형 경로에 대해 매우 높은 행 추정치
Extra	Using temporary, Using filesort, 또는 광범위한 Using where

무분별하게 인덱스를 추가하지 마십시오. 각 인덱스는 쓰기 비용, 마이그레이션 시간, 백업 크기, 버퍼 풀 압력을 증가시킵니다.

쿼리 패턴

업서트

크로스 엔진 호환 형식:

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
    setting_value = VALUES(setting_value),
    updated_at = CURRENT_TIMESTAMP;

MySQL 행 별칭 형식:

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?) AS new
ON DUPLICATE KEY UPDATE
    setting_value = new.setting_value,
    updated_at = CURRENT_TIMESTAMP;

행 별칭 형식은 대상이 MySQL임을 확인한 후에만 사용하세요. MariaDB 또는 혼합 MySQL/MariaDB 환경에서는 VALUES(col)을 사용하세요.

키셋 페이지네이션

SELECT id, name, created_at
FROM products
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;

커서와 일치하는 인덱스로 백업하세요:

CREATE INDEX idx_products_created_id ON products (created_at, id);

대규모 테이블에서 깊은 OFFSET 페이지네이션을 사용하지 마십시오. 서버가 페이지를 반환하기 전에 행을 스캔하고 버리도록 만듭니다.

JSON 필드

JSON 컬럼은 확장 데이터에 사용하고, 많은 관계형 필터링이나 제약 조건이 필요한 필드에는 사용하지 마십시오.

CREATE TABLE events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payload JSON NOT NULL,
    event_type VARCHAR(64)
        GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.type'))) STORED,
    KEY idx_events_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

자주 쿼리되는 JSON 경로의 경우 생성 컬럼을 노출하고 해당 컬럼에 인덱스를 생성하세요. 외래 키, 소유권, 테넌시, 수명 주기 필드는 관계형으로 유지하세요.

풀텍스트 검색

ALTER TABLE articles ADD FULLTEXT KEY ft_articles_title_body (title, body);

SELECT id, title, MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;

오타 허용, 복잡한 순위 지정, 크로스 테이블 패싯, 또는 기본 제공 풀텍스트 동작을 넘어서는 언어별 분석이 필요한 경우 외부 검색을 사용하세요.

트랜잭션

트랜잭션을 짧게 유지하고 행을 일관된 순서로 잠그세요:

START TRANSACTION;

SELECT id, balance
FROM accounts
WHERE id IN (?, ?)
ORDER BY id
FOR UPDATE;

UPDATE accounts SET balance = balance - ? WHERE id = ?;
UPDATE accounts SET balance = balance + ? WHERE id = ?;

COMMIT;

데드락 및 잠금 대기 체크리스트:

• 모든 코드 경로에서 결정론적 순서로 행을 잠그세요.
• 트랜잭션을 열기 전에 외부 API 호출을 수행하고, 내부에서 수행하지 마세요.
• UPDATE, DELETE, 잠금 읽기에 사용되는 조건에 인덱스를 추가하세요.
• 데드락 발생 시 롤백하고 제한된 재시도 예산 내에서 전체 트랜잭션을 재시도하세요.
• 데드락 직후 SHOW ENGINE INNODB STATUS\G를 캡처하세요. 이후 이벤트에 의해 덮어쓰여집니다.

큐 스타일 워커 클레임:

START TRANSACTION;

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE jobs
SET status = 'processing', started_at = CURRENT_TIMESTAMP
WHERE id = ?;

COMMIT;

SKIP LOCKED는 잠긴 행을 건너뛰는 것이 허용되는 큐와 같은 워크로드에만 사용하세요. 일반적인 트랜잭션 일관성을 대체하지 않습니다.

연결 풀

SQLAlchemy 예제:

from sqlalchemy import create_engine

engine = create_engine(
    "mysql+mysqlconnector://app:[email protected]/app",
    pool_size=10,
    max_overflow=5,
    pool_timeout=30,
    pool_recycle=240,
    pool_pre_ping=True,
    connect_args={"connect_timeout": 5},
)

Node.js mysql2 예제:

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 30000,
});

const [rows] = await pool.execute(
  'SELECT id, total FROM orders WHERE account_id = ? LIMIT 50',
  [accountId],
);

애플리케이션 풀 재활용 시간을 서버 wait_timeout보다 낮게 유지하세요. 서버가 wait_timeout = 300을 사용하는 경우 pool_recycle을 약 240초로 설정하는 것이 적절합니다. pool_pre_ping은 여전히 네트워크 및 장애 조치 이벤트로부터 복구하는 데 도움이 됩니다.

진단

유용한 첫 번째 명령어:

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G;
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

통제된 환경에서 슬로우 로그를 활성화하세요:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

EXPLAIN ANALYZE는 쿼리를 실행해도 안전한 경우에만 사용하세요. 쿼리를 실행하며 프로덕션 규모 데이터에서 비용이 많이 들 수 있습니다.

복제

읽기 복제본은 지연될 수 있습니다. 쓰기 직후 읽기-후-쓰기 경로, 체크아웃 흐름, 권한 검사, 멱등성 키 읽기를 복제본으로 라우팅하지 마십시오.

-- MySQL 레거시 용어, 기존 환경에서 여전히 일반적
SHOW SLAVE STATUS\G;

-- 지원되는 경우 최신 용어
SHOW REPLICA STATUS\G;

하나의 명령어를 표준화하기 전에 엔진/버전을 확인하세요. TCP 연결이 살아 있는지 여부뿐만 아니라 복제본 SQL 스레드 상태, IO 스레드 상태, 지연을 모니터링하세요.

보안

CREATE USER 'app'@'%' IDENTIFIED BY 'use-a-secret-manager';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'%';

ALTER USER 'app'@'%' REQUIRE SSL;

SELECT user, host
FROM mysql.user
WHERE user = '';

DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'%';

보안 검토 사항:

• 애플리케이션 사용자에게 ALL PRIVILEGES 또는 *.*를 부여하지 마십시오.
• 트래픽이 호스트나 네트워크를 통과하는 경우 애플리케이션 사용자에게 TLS를 요구하세요.
• 자격 증명을 예제, 스크립트, 저장소 파일이 아닌 플랫폼 시크릿 관리자에 저장하세요.
• 마이그레이션/관리 사용자와 런타임 애플리케이션 사용자를 분리하세요.
• 성능 튜닝 전에 공개 네트워크 노출 및 바인드 주소를 감사하세요.

설정

전용 데이터베이스 호스트를 위한 예제 시작점:

[mysqld]
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

max_connections = 300
thread_cache_size = 50

wait_timeout = 300
interactive_timeout = 300
innodb_lock_wait_timeout = 10

slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = ON

log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800

구성 값을 보편적인 사전 설정이 아닌 검토를 위한 프롬프트로 취급하세요. 메모리, 연결, 로그 보존, 내구성 설정을 워크로드, 하드웨어, 백업 정책, 복구 목표에 맞게 조정하세요.

안티 패턴

안티 패턴	위험	더 나은 패턴
핫 경로에서 SELECT *	과도한 데이터 가져오기 및 취약한 클라이언트	명시적 컬럼 선택
깊은 OFFSET 페이지네이션	선형 스캔 및 느린 페이지	키셋 페이지네이션
외래 키 조인에 인덱스 없음	느린 조인 및 잠금이 많은 삭제	FK 컬럼에 의도적으로 인덱스 생성
긴 트랜잭션	잠금 대기 및 큰 실행 취소 기록	작은 작업 단위 커밋
mysql.user에 대한 직접 DML	권한 부여 테이블 손상 위험	CREATE USER, ALTER USER, DROP USER 사용
관리자 권한이 있는 애플리케이션 사용자	높은 폭발 반경	최소 권한 런타임 사용자
wait_timeout 이상의 풀 재활용	오래된 풀 연결	제한 시간 미만으로 재활용 및 사전 핑
쓰기 후 복제본 읽기	오래된 사용자 대상 상태	읽기-후-쓰기 흐름을 기본으로 고정

출력 기대 사항

이 스킬이 검토에 사용될 때 다음을 반환하세요:

1. 엔진/버전 가정.
2. 최고 위험 정확성, 잠금, 보안 및 마이그레이션 문제.
3. 안전한 경로에 대한 정확한 SQL 또는 코드 변경 사항.
4. 검증 계획: EXPLAIN, 마이그레이션 드라이 런, 잠금/데드락 확인, 롤백 기준.
5. 권장 사항에 영향을 미치는 MySQL/MariaDB 구문 차이.

관련

• 스킬: postgres-patterns - PostgreSQL 특화 스키마 및 쿼리 패턴
• 스킬: database-migrations - 마이그레이션 계획 및 롤아웃 안전성
• 스킬: backend-patterns - API 및 서비스 계층 패턴
• 스킬: security-review - 시크릿 처리, 인증, 최소 권한
• 에이전트: database-reviewer - 광범위한 데이터베이스 검토 워크플로우