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 이상의 풀 재활용 | 오래된 풀 연결 | 제한 시간 미만으로 재활용 및 사전 핑 |
| 쓰기 후 복제본 읽기 | 오래된 사용자 대상 상태 | 읽기-후-쓰기 흐름을 기본으로 고정 |
출력 기대치
이 스킬이 검토에 사용될 때 다음을 반환하십시오:
- 엔진/버전 가정.
- 가장 위험도가 높은 정확성, 잠금, 보안 및 마이그레이션 문제.
- 안전한 경로에 대한 정확한 SQL 또는 코드 변경.
- 검증 계획:
EXPLAIN, 마이그레이션 드라이 런, 잠금/교착 상태 확인 및 롤백 기준. - 권장 사항에 영향을 미치는 MySQL/MariaDB 구문 차이.
관련
- 스킬:
postgres-patterns- PostgreSQL 관련 스키마 및 쿼리 패턴 - 스킬:
database-migrations- 마이그레이션 계획 및 롤아웃 안전 - 스킬:
backend-patterns- API 및 서비스 계층 패턴 - 스킬:
security-review- 비밀 처리, 인증 및 최소 권한 - 에이전트:
database-reviewer- 더 넓은 데이터베이스 검토 워크플로우