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

예제 테이블(기본으로 사용할 샘플) 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]);