The World's Most Popular Open Source Database
MySQL은 세계에서 가장 널리 사용되는 오픈소스 관계형 데이터베이스입니다. LAMP(Linux, Apache, MySQL, PHP) 스택의 핵심으로, InnoDB 스토리지 엔진을 통해 ACID 트랜잭션을 지원하며, 웹 애플리케이션의 사실상 표준 데이터베이스입니다.
MySQL의 역사와 현재 - 1995년 스웨덴에서 탄생한 MySQL은 Sun Microsystems를 거쳐 현재 Oracle Corporation이 소유하고 있습니다. Facebook, Twitter, YouTube 등 수많은 대형 서비스가 MySQL을 사용하며, 전 세계 웹사이트의 상당수가 MySQL 기반입니다.
InnoDB 스토리지 엔진 - MySQL 5.5부터 기본 스토리지 엔진인 InnoDB는 ACID를 완벽히 지원합니다. Row-level Locking, MVCC, Foreign Key, Crash Recovery를 제공하며, 트랜잭션이 필요한 엔터프라이즈 환경에 적합합니다. MyISAM은 레거시로, 전문검색용으로만 제한적 사용됩니다.
Replication 아키텍처 - MySQL은 Master-Slave(Primary-Replica) 복제를 기본 지원합니다. 읽기 부하 분산, 백업, 고가용성을 위해 사용되며, GTID(Global Transaction ID)를 통해 복제 관리가 편리해졌습니다. Group Replication으로 Multi-Master 구성도 가능합니다.
MySQL vs MariaDB - MariaDB는 MySQL의 오리지널 개발자가 Oracle 인수 후 포크한 프로젝트입니다. 문법적으로 거의 호환되지만, MariaDB는 Aria, ColumnStore 등 추가 스토리지 엔진과 일부 고급 기능을 제공합니다. AWS Aurora도 MySQL 호환 옵션을 제공합니다.
성능과 확장성 - 적절한 인덱싱, 쿼리 최적화, Connection Pooling으로 초당 수만 쿼리 처리가 가능합니다. ProxySQL, MySQL Router로 읽기/쓰기 분리, 샤딩으로 수평 확장을 구현할 수 있습니다.
# MySQL 연결 및 CRUD - Python + mysql-connector
import mysql.connector
from mysql.connector import pooling, Error
from contextlib import contextmanager
# 커넥션 풀 설정 (권장)
db_config = {
"host": "localhost",
"user": "app_user",
"password": "secure_password",
"database": "myapp",
"pool_name": "mypool",
"pool_size": 10,
"pool_reset_session": True,
}
connection_pool = pooling.MySQLConnectionPool(**db_config)
@contextmanager
def get_connection():
"""커넥션 풀에서 연결 획득"""
conn = connection_pool.get_connection()
try:
yield conn
conn.commit()
except Error as e:
conn.rollback()
raise e
finally:
conn.close()
# 트랜잭션 예제 - 계좌 이체
def transfer_money(from_account: int, to_account: int, amount: float):
"""ACID 보장 계좌 이체"""
with get_connection() as conn:
cursor = conn.cursor(dictionary=True)
try:
# 1. 출금 계좌 잔액 확인 (FOR UPDATE로 락)
cursor.execute("""
SELECT balance FROM accounts
WHERE id = %s FOR UPDATE
""", (from_account,))
result = cursor.fetchone()
if not result or result['balance'] < amount:
raise ValueError("잔액 부족")
# 2. 출금
cursor.execute("""
UPDATE accounts
SET balance = balance - %s
WHERE id = %s
""", (amount, from_account))
# 3. 입금
cursor.execute("""
UPDATE accounts
SET balance = balance + %s
WHERE id = %s
""", (amount, to_account))
# 4. 이체 기록
cursor.execute("""
INSERT INTO transfers (from_id, to_id, amount, created_at)
VALUES (%s, %s, %s, NOW())
""", (from_account, to_account, amount))
print(f"✅ 이체 완료: {from_account} → {to_account}, {amount}원")
except Error as e:
print(f"❌ 이체 실패: {e}")
raise
finally:
cursor.close()
# Prepared Statement (SQL Injection 방지)
def safe_search(keyword: str, category: int):
"""Prepared Statement로 안전한 검색"""
with get_connection() as conn:
cursor = conn.cursor(dictionary=True, prepared=True)
try:
# %s 플레이스홀더 사용 (절대 f-string 사용 금지!)
cursor.execute("""
SELECT id, name, price
FROM products
WHERE name LIKE %s
AND category_id = %s
ORDER BY created_at DESC
LIMIT 20
""", (f"%{keyword}%", category))
return cursor.fetchall()
finally:
cursor.close()
# 배치 삽입 (executemany)
def batch_insert_products(products: list):
"""대량 데이터 효율적 삽입"""
with get_connection() as conn:
cursor = conn.cursor()
try:
cursor.executemany("""
INSERT INTO products (name, price, category_id)
VALUES (%s, %s, %s)
ON DUPLICATE KEY UPDATE
price = VALUES(price),
updated_at = NOW()
""", [(p['name'], p['price'], p['category_id']) for p in products])
print(f"✅ {cursor.rowcount}개 삽입/업데이트")
finally:
cursor.close()
# 페이지네이션 (Cursor 기반 권장)
def get_products_cursor_pagination(last_id: int = 0, limit: int = 20):
"""Cursor 기반 페이지네이션 (OFFSET보다 효율적)"""
with get_connection() as conn:
cursor = conn.cursor(dictionary=True)
try:
cursor.execute("""
SELECT id, name, price, created_at
FROM products
WHERE id > %s
ORDER BY id ASC
LIMIT %s
""", (last_id, limit))
results = cursor.fetchall()
next_cursor = results[-1]['id'] if results else None
return {"data": results, "next_cursor": next_cursor}
finally:
cursor.close()
if __name__ == "__main__":
# 안전한 검색
results = safe_search("아이폰", 1)
print(f"검색 결과: {len(results)}건")
# 계좌 이체
transfer_money(1001, 1002, 50000)
// MySQL 연결 및 CRUD - Node.js + mysql2
const mysql = require('mysql2/promise');
// 커넥션 풀 생성
const pool = mysql.createPool({
host: 'localhost',
user: 'app_user',
password: 'secure_password',
database: 'myapp',
waitForConnections: true,
connectionLimit: 20,
queueLimit: 0,
enableKeepAlive: true,
keepAliveInitialDelay: 0,
});
// 트랜잭션 헬퍼
async function withTransaction(callback) {
const connection = await pool.getConnection();
try {
await connection.beginTransaction();
const result = await callback(connection);
await connection.commit();
return result;
} catch (error) {
await connection.rollback();
throw error;
} finally {
connection.release();
}
}
// 계좌 이체 (트랜잭션)
async function transferMoney(fromAccount, toAccount, amount) {
return withTransaction(async (conn) => {
// 1. 출금 계좌 확인 (FOR UPDATE)
const [rows] = await conn.execute(
'SELECT balance FROM accounts WHERE id = ? FOR UPDATE',
[fromAccount]
);
if (rows.length === 0 || rows[0].balance < amount) {
throw new Error('잔액 부족');
}
// 2. 출금
await conn.execute(
'UPDATE accounts SET balance = balance - ? WHERE id = ?',
[amount, fromAccount]
);
// 3. 입금
await conn.execute(
'UPDATE accounts SET balance = balance + ? WHERE id = ?',
[amount, toAccount]
);
// 4. 기록
await conn.execute(
'INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)',
[fromAccount, toAccount, amount]
);
console.log(`✅ 이체 완료: ${fromAccount} → ${toAccount}, ${amount}원`);
return true;
});
}
// Prepared Statement (execute 사용)
async function safeSearch(keyword, categoryId) {
// execute()는 Prepared Statement 사용 (query()보다 안전)
const [rows] = await pool.execute(
`SELECT id, name, price
FROM products
WHERE name LIKE ?
AND category_id = ?
ORDER BY created_at DESC
LIMIT 20`,
[`%${keyword}%`, categoryId]
);
return rows;
}
// 배치 삽입
async function batchInsert(products) {
const values = products.map(p => [p.name, p.price, p.categoryId]);
const [result] = await pool.query(
`INSERT INTO products (name, price, category_id)
VALUES ?
ON DUPLICATE KEY UPDATE
price = VALUES(price),
updated_at = NOW()`,
[values]
);
console.log(`✅ ${result.affectedRows}개 처리`);
return result;
}
// 스트리밍 대용량 조회
async function streamLargeData(callback) {
const connection = await pool.getConnection();
try {
const stream = connection.connection.query(
'SELECT * FROM large_table'
).stream();
for await (const row of stream) {
await callback(row);
}
} finally {
connection.release();
}
}
// JSON 컬럼 활용 (MySQL 5.7+)
async function jsonOperations() {
// JSON 데이터 삽입
await pool.execute(
`INSERT INTO products (name, price, metadata)
VALUES (?, ?, ?)`,
['MacBook Pro', 2499, JSON.stringify({
brand: 'Apple',
specs: { cpu: 'M3', ram: '16GB' }
})]
);
// JSON 필드 검색
const [rows] = await pool.execute(
`SELECT name, price,
JSON_EXTRACT(metadata, '$.brand') as brand,
JSON_EXTRACT(metadata, '$.specs.cpu') as cpu
FROM products
WHERE JSON_EXTRACT(metadata, '$.brand') = ?`,
['Apple']
);
return rows;
}
// 실행
(async () => {
try {
await transferMoney(1001, 1002, 50000);
const results = await safeSearch('아이폰', 1);
console.log(`검색 결과: ${results.length}건`);
} catch (error) {
console.error('에러:', error.message);
} finally {
await pool.end();
}
})();
-- MySQL 실무 SQL 예제
-- ============================================
-- 1. InnoDB 트랜잭션
-- ============================================
START TRANSACTION;
-- 출금
UPDATE accounts
SET balance = balance - 50000
WHERE id = 1001 AND balance >= 50000;
-- 영향받은 행이 1개인지 확인 (앱에서 처리)
-- ROW_COUNT() = 1 이면 성공
-- 입금
UPDATE accounts
SET balance = balance + 50000
WHERE id = 1002;
-- 모두 성공 시 커밋
COMMIT;
-- 실패 시 롤백
-- ROLLBACK;
-- ============================================
-- 2. 인덱스 최적화
-- ============================================
-- 복합 인덱스 (왼쪽부터 사용됨)
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date DESC);
-- 커버링 인덱스 (SELECT 컬럼도 포함)
CREATE INDEX idx_products_category_covering
ON products (category_id, price, name);
-- EXPLAIN으로 실행 계획 확인
EXPLAIN SELECT id, name, price
FROM products
WHERE category_id = 5
ORDER BY price DESC
LIMIT 10;
-- 인덱스 힌트 (옵티마이저 강제)
SELECT * FROM products
USE INDEX (idx_products_category_covering)
WHERE category_id = 5;
-- ============================================
-- 3. JSON 컬럼 활용 (MySQL 5.7+)
-- ============================================
-- JSON 컬럼 생성
ALTER TABLE products
ADD COLUMN metadata JSON;
-- JSON 데이터 삽입
INSERT INTO products (name, price, metadata)
VALUES ('iPhone 15', 1200, '{
"brand": "Apple",
"specs": {"storage": "256GB", "color": "blue"},
"tags": ["smartphone", "5G"]
}');
-- JSON 경로로 조회
SELECT
name,
JSON_EXTRACT(metadata, '$.brand') as brand,
metadata->>'$.specs.storage' as storage, -- 단축 문법
JSON_EXTRACT(metadata, '$.tags[0]') as first_tag
FROM products
WHERE metadata->>'$.brand' = 'Apple';
-- JSON 배열 검색
SELECT * FROM products
WHERE JSON_CONTAINS(metadata->'$.tags', '"5G"');
-- Generated Column으로 인덱싱
ALTER TABLE products
ADD COLUMN brand VARCHAR(50)
GENERATED ALWAYS AS (metadata->>'$.brand') STORED,
ADD INDEX idx_brand (brand);
-- ============================================
-- 4. 윈도우 함수 (MySQL 8.0+)
-- ============================================
SELECT
name,
category_id,
price,
-- 카테고리별 순위
RANK() OVER (PARTITION BY category_id ORDER BY price DESC) as price_rank,
-- 전체 누적 합
SUM(price) OVER (ORDER BY id) as running_total,
-- 이전 행 값
LAG(price, 1) OVER (ORDER BY id) as prev_price,
-- N-tile (4분위)
NTILE(4) OVER (ORDER BY price) as quartile
FROM products;
-- ============================================
-- 5. CTE와 재귀 쿼리 (MySQL 8.0+)
-- ============================================
WITH RECURSIVE category_path AS (
-- 기본 케이스
SELECT id, name, parent_id, 1 as depth,
CAST(name AS CHAR(500)) as path
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- 재귀
SELECT c.id, c.name, c.parent_id, cp.depth + 1,
CONCAT(cp.path, ' > ', c.name)
FROM categories c
JOIN category_path cp ON c.parent_id = cp.id
)
SELECT * FROM category_path ORDER BY path;
-- ============================================
-- 6. 성능 모니터링
-- ============================================
-- 슬로우 쿼리 확인
SHOW VARIABLES LIKE 'slow_query%';
-- 현재 실행 중인 쿼리
SHOW PROCESSLIST;
-- 테이블 상태
SHOW TABLE STATUS LIKE 'products';
-- InnoDB 상태
SHOW ENGINE INNODB STATUS;
-- 인덱스 사용 통계
SELECT * FROM sys.schema_index_statistics
WHERE table_name = 'products';
-- ============================================
-- 7. 레플리케이션 상태 확인
-- ============================================
-- Replica 상태
SHOW REPLICA STATUS\G
-- GTID 확인
SELECT @@GLOBAL.gtid_executed;
-- 바이너리 로그 위치
SHOW MASTER STATUS;
"초기 단계에서는 MySQL로 시작하는 게 좋습니다. AWS RDS MySQL이나 Aurora MySQL로 관리 부담을 줄이고, 나중에 트래픽이 늘면 Read Replica 추가하고, ProxySQL로 읽기/쓰기 분리하면 충분히 확장 가능해요."
"EXPLAIN 결과를 보니 filesort가 발생하고 있습니다. ORDER BY 컬럼이 인덱스에 없어서요. 복합 인덱스를 (category_id, created_at DESC)로 만들면 인덱스만으로 정렬이 가능해서 쿼리 속도가 10배는 빨라질 겁니다."
"MySQL에서 PostgreSQL로 마이그레이션할 때 주의할 점이 있어요. MySQL의 AUTO_INCREMENT는 PostgreSQL에서 SERIAL로, TINYINT는 SMALLINT로, ON DUPLICATE KEY UPDATE는 ON CONFLICT DO UPDATE로 변환해야 합니다."
절대 문자열 연결로 쿼리를 만들지 마세요. 반드시 Prepared Statement(?나 %s 플레이스홀더)를 사용하세요. ORM 사용 시에도 raw query 주의.
필요한 컬럼만 명시하세요. 불필요한 데이터 전송 감소, 커버링 인덱스 활용 가능, 스키마 변경 시 버그 예방 효과가 있습니다.
대량 데이터에서 OFFSET은 성능이 급격히 저하됩니다. Cursor 기반 페이지네이션(WHERE id > last_id)을 사용하세요.
InnoDB 사용, 적절한 인덱스 설계, Connection Pooling, 쿼리 로깅 및 분석, 정기 백업(mysqldump/xtrabackup), Replica 구성으로 고가용성 확보.