회원가입   로그인
IMG-LOGO
공지사항 :

MySQL에서의 CRUD(생성·조회·갱신·삭제)

lmkfox - 2025-09-24 06:53:34 8 Views 0 Comment

예제 테이블(기본으로 사용할 샘플)

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password_hash CHAR(60) NOT NULL,
  version INT NOT NULL DEFAULT 1,        -- optimistic locking 예제용
  deleted_at DATETIME DEFAULT NULL,      -- soft delete 예제용
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY ux_users_email (email),
  KEY idx_users_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1) CREATE — 데이터 생성 (INSERT)

기본 단일 행 삽입

INSERT INTO users (username, email, password_hash)
VALUES ('kim', 'kim@example.com', '...hash...');

  • LAST_INSERT_ID() 또는 API의 lastInsertId()로 자동증가 키(id)를 확인할 수 있음.

다중 행 삽입 (배치)

INSERT INTO users (username, email, password_hash)
VALUES
  ('a','a@example.com','h1'),
  ('b','b@example.com','h2'),
  ('c','c@example.com','h3');

  • 다중 행 한 번에 넣으면 네트워크 왕복이 줄어 성능 이점.

Upsert / 충돌 처리

  • INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO users (email, username) VALUES ('x@y','x')
ON DUPLICATE KEY UPDATE username = VALUES(username), version = version + 1;

  • REPLACE INTO는 기존 행 삭제 후 삽입(부작용 주의).

  • INSERT IGNORE는 오류(키 충돌 등)를 무시하고 계속 진행.

대량 로드

  • LOAD DATA INFILE은 CSV 등 대량 로드에 매우 빠름(권한·경로 주의).

LOAD DATA INFILE '/path/file.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(username,email,password_hash);

성능 팁 (생성)

  • 대량 삽입 시 트랜잭션으로 묶기(START TRANSACTION; ... COMMIT;) — 자동 커밋을 끄면 속도 향상.

  • 인덱스가 많은 테이블은 대량 로드 전에 인덱스 제거 후 로드, 재생성 고려(환경 따라 다름).

  • LOAD DATA INFILE 권장.

2) READ — 조회 (SELECT)

기본 SELECT

SELECT id, username, email FROM users WHERE deleted_at IS NULL;

정렬·제한·페이징

SELECT id, username FROM users WHERE deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

  • OFFSET 기반 페이징은 큰 OFFSET에 비효율적 → 키셋 페이징(예: WHERE created_at < ? ORDER BY created_at DESC LIMIT 20) 권장.

집계·그룹화

SELECT status, COUNT(*) AS cnt
FROM orders
GROUP BY status
HAVING COUNT(*) > 10;

JOIN (관계 조회)

SELECT u.id, u.username, o.id AS order_id, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 123;

  • 필요한 JOIN 타입(inner/left/right) 정확히 사용.

인덱스·쿼리 플랜 확인

EXPLAIN FORMAT=JSON
SELECT ...;

  • EXPLAIN으로 인덱스 사용 여부, 스캔된 행 수 예측 등을 확인하고 인덱스 설계 개선.

성능 팁 (조회)

  • SELECT * 피하기 — 필요한 컬럼만 선택.

  • 자주 쓰는 WHERE/JOIN 컬럼에 인덱스 생성.

  • 복합 인덱스는 쿼리의 컬럼 순서(Left-most rule)에 맞춰 설계.

  • 큰 테이블의 반복 조회는 캐시 또는 읽기 전용 분리(리플리카) 고려.

3) UPDATE — 갱신

기본 업데이트

UPDATE users
SET username = 'kim_updated', updated_at = NOW()
WHERE id = 123 AND deleted_at IS NULL;

조건에 따른 대량 업데이트

UPDATE users SET version = version + 1 WHERE last_login < '2024-01-01';

JOIN을 이용한 업데이트

UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.last_order_total = o.total
WHERE o.id = 999;

낙관적 동시성(Optimistic Locking)

  • version 컬럼 사용 예:

UPDATE users
SET username = 'new', version = version + 1
WHERE id = 123 AND version = 5;

  • 위 쿼리의 영향 행 수가 0이면(다른 트랜잭션이 먼저 변경) 충돌 처리 필요.

영향된 행 확인

  • SQL 함수 ROW_COUNT() 또는 API의 affected_rows로 확인.

성능 팁 (갱신)

  • WHERE 절에 인덱스를 사용하면 대상 행을 빠르게 찾음.

  • 대량 UPDATE는 배치로 나눠 실행(예: 10k씩) — 트랜잭션 크기 관리.

  • 가능하면 UPDATE 시 불필요한 전체 테이블 스캔 피함.

4) DELETE — 삭제

기본 삭제 (하드 삭제)

DELETE FROM users WHERE id = 123;

다중 조건 삭제

DELETE FROM sessions WHERE last_access < '2024-01-01';

다중-테이블 삭제

DELETE u, p FROM users u JOIN posts p ON u.id = p.user_id WHERE u.inactive = 1;

소프트 삭제(권장되는 패턴)

  • deleted_at 컬럼을 두고 삭제 시 타임스탬프 입력:

UPDATE users SET deleted_at = NOW() WHERE id = 123;

  • 조회시 WHERE deleted_at IS NULL 필터 추가.

외래키와 CASCADE

  • 외래키 ON DELETE CASCADE가 설정되어 있으면 부모 삭제 시 자식 자동 삭제(편리하지만 주의).

5) 트랜잭션과 동시성 제어

트랜잭션 기본

START TRANSACTION;
-- 여러 DML
COMMIT;   -- 또는 ROLLBACK;

  • 여러 DML을 하나의 원자적 단위로 묶어야 할 때 사용.

행 잠금 (Pessimistic locking)

  • SELECT ... FOR UPDATE 로 행을 잠그고 갱신:

START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- 계산 후 UPDATE
COMMIT;

  • 데드락 가능성 존재 → 다음에 설명할 예외 처리 필요.

격리 수준(Isolation)

  • MySQL 기본은 REPEATABLE READ(버전/설정 확인). 필요에 따라 READ COMMITTED, SERIALIZABLE 등 설정.

  • 높은 격리 수준은 일관성 향상 ↔ 동시성(성능) 저하.

충돌 처리 / 재시도

  • Deadlock(교착) 발생 시 MySQL은 ER_LOCK_DEADLOCK 에러 반환 → 트랜잭션을 롤백하고 재시도하는 로직 필요.

6) 보안: SQL 인젝션 방지

  • 무조건 Prepared Statements(파라미터화 쿼리) 사용. 예: PHP PDO

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]);
$user = $stmt->fetch();

  • ORMs나 DB 드라이버의 바인딩 기능 사용.

7) 준비문(prepared statement) — 서버/클라이언트 양쪽

  • MySQL 서버 side 예:

PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
SET @id = 123;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

  • 하지만 보통 애플리케이션 레벨(언어 드라이버)의 prepared statement 사용 권장.

8) 모니터링·디버깅

  • EXPLAIN으로 쿼리계획 점검.

  • Slow Query Log 활성화하여 느린 쿼리 찾기.

  • SHOW PROCESSLIST로 현재 쿼리 상태 확인.

  • INFORMATION_SCHEMA / PERFORMANCE_SCHEMA로 통계 확인.

9) 실무 팁 정리 (체크리스트)

  • 항상 파라미터화: SQL 인젝션 방지.

  • 트랜잭션은 짧게 유지: 락 유지 시간 최소화.

  • 인덱스 설계: 조회·조인 패턴에 맞춰 최소한의 인덱스 유지.

  • 대량 작업은 배치: 쓰기 작업은 트랜잭션으로 묶고 적절한 청크로 처리.

  • 페이징은 키셋 방식 고려: OFFSET 큰 값 사용 지양.

  • 소프트 삭제 패턴 고려(복구/감사 용이).

  • 업데이트 충돌 처리: optimistic locking(버전 컬럼) 사용 권장.

  • 죽은 쿼리·인덱스 정리 주기적 수행.

10) 예제 — 간단한 CRUD 흐름 (PDO 사용 예)

// 1. INSERT
$stmt = $pdo->prepare("INSERT INTO users (username,email,password_hash) VALUES (?,?,?)");
$stmt->execute([$u, $e, $pw_hash]);
$id = $pdo->lastInsertId();

// 2. SELECT
$stmt = $pdo->prepare("SELECT id,username,email FROM users WHERE id = ?");
$stmt->execute([$id]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

// 3. UPDATE (optimistic locking)
$stmt = $pdo->prepare("UPDATE users SET username = ?, version = version + 1 WHERE id = ? AND version = ?");
$affected = $stmt->execute([$newName, $id, $currentVersion]);
if ($stmt->rowCount() === 0) {
  // 충돌 처리: 재시도 또는 사용자에게 충돌 알림
}

// 4. SOFT DELETE
$stmt = $pdo->prepare("UPDATE users SET deleted_at = NOW() WHERE id = ?");
$stmt->execute([$id]);


댓글