Real MySQL 8.0 11장. 쿼리 작성 및 최적화
INSERT / UPDATE·DELETE / DDL / 성능 테스트
11.5 INSERT
#sec-11-511.5.1 고급 옵션
11.5.1.1 INSERT IGNORE
- 동작: PK/UK 중복, 타입 불일치, 제약 위반 등을 에러가 아니라 경고로 낮추고 해당 row를 건너뜀(또는 기본값으로 보정).
- NOT NULL 컬럼에 NULL 입력: 해당 컬럼의 기본값으로 저장됨 (예:
INT → 0, 문자열 →''). - 주의: 입력 검증이 약하면 “조용히” 잘못된 데이터가 들어가 데이터 품질이 망가질 수 있음.
주의 silent data corruption
11.5.1.2 INSERT ... ON DUPLICATE KEY UPDATE
- 의미: 키 기준으로 없으면 INSERT, 있으면 UPDATE (ANSI
MERGE유사). - REPLACE와 차이:
REPLACE= 사실상 DELETE + INSERTON DUPLICATE KEY UPDATE= UPDATE
- 권장: InnoDB에서는 보통
REPLACE보다ON DUPLICATE KEY UPDATE가 유리.
추천 InnoDB: DUPLICATE KEY UPDATE
11.5.2 LOAD DATA 명령 주의 사항
- 왜 빠른가? MySQL 엔진↔스토리지 엔진 호출을 줄이고 적재 경로가 최적화되어 일반 INSERT 대비 유리.
- 단점(핵심):
- 단일 스레드: 파일이 크면 인덱스 갱신까지 직렬로 오래 걸림
- 단일 트랜잭션: Undo가 오래 유지되어 디스크/성능 부담, MVCC 관점 오버헤드 증가
- 완화 전략: 파일 분할(다중 트랜잭션), PK 범위로 배치 분할, (상황에 따라) 인덱스/FK 비활성화 후 적재.
Concurrency 옵션(문서 요약):
LOW_PRIORITY: 테이블을 읽는 클라이언트가 없을 때까지 대기 성격(테이블 락 엔진에서 영향 큼).CONCURRENT: MyISAM에서 조건 충족 시 LOAD 중에도 읽기를 허용(약간의 성능 저하 가능).
11.5.3 성능을 위한 테이블 구조
11.5.3.1 대량 INSERT 성능
- PK 기준 정렬 후 INSERT가 유리: 랜덤 삽입이면 매 row마다 B-Tree 여러 페이지를 읽어야 함.
- 가능하면 세컨더리 인덱스도 최소화/정렬 방향이 성능에 도움.
11.5.3.2 프라이머리 키 선정
- 로그성 테이블(INSERT 위주): 단조 증가/감소 키(AUTO_INCREMENT 등) 권장.
- 업무성 테이블(SELECT 위주): 조회 패턴에 도움되는 컬럼(또는 조합)을 PK로 고려.
- 현실적으로 INSERT/SELECT를 동시에 완벽 최적화하기 어렵고 우선순위 선택이 필요.
11.5.3.3 AUTO_INCREMENT 컬럼
- 장점: INSERT 최적화(클러스터드 인덱스 끝에 append).
- 주의: 단조 증가 PK는 오른쪽 leaf에 insert가 몰려 경합(핫스팟)이 생길 수 있음.
LAST_INSERT_ID(): 현재 커넥션의 마지막 값 반환, 다중 row INSERT면 첫 번째 row 값만 반환.
INSERT INTO test_tbl VALUES (NULL, '111');
SELECT LAST_INSERT_ID(); -- 1
INSERT INTO test_tbl VALUES (NULL, '222'),
(NULL, '333'),
(NULL, '444');
SELECT LAST_INSERT_ID(); -- 2 (첫 번째 row만)
11.6 UPDATE와 DELETE
#sec-11-611.6.1 UPDATE ... ORDER BY ... LIMIT n
ORDER BY+LIMIT로 상위 n건만 업데이트 가능.- 대량 정리/배치에서 한 번에 몰아서 처리하지 말고 잘라서 반복할 때 유용.
11.6.2 JOIN UPDATE
- 여러 테이블 조인 결과로 UPDATE 가능.
- 락 규칙(핵심):
- 읽기 참조 테이블: 읽기 락
- 변경 테이블: 쓰기 락
- OLTP 주의: 락 범위가 커져 데드락/대기 위험 → 배치/통계성에 적합.
- 제한:
JOIN UPDATE에GROUP BY,ORDER BY직접 사용 불가. - 우회: 파생 테이블(서브쿼리) 또는
LATERAL JOIN활용. - 조인 순서 제어:
STRAIGHT_JOIN또는 MySQL 8.0+JOIN_ORDER힌트.
UPDATE departments d,
(SELECT de.dept_no, COUNT(*) AS emp_count
FROM dept_emp de
GROUP BY de.dept_no) dc
SET d.emp_count = dc.emp_count
WHERE dc.dept_no = d.dept_no;
11.6.3 여러 레코드 UPDATE
- MySQL 8.0에서 Row Constructor로 레코드별 다른 값 업데이트 가능.
- 개념적으로 “SQL 안에서 임시 테이블을 만든 뒤 JOIN UPDATE”에 가깝게 동작.
11.6.4 JOIN DELETE
- 조인 후 특정 테이블만 삭제 가능:
DELETE뒤에 삭제 대상 테이블을 명시. - 여러 테이블 삭제도 가능.
- JOIN UPDATE와 동일하게 조인 순서 힌트 사용 가능.
DELETE e, de
FROM employees e, dept_emp de, departments d
WHERE e.emp_no = de.emp_no
AND de.dept_no = d.dept_no
AND d.dept_no = 'd001';
11.7 스키마 조작(DDL)
#sec-11-711.7.1 온라인 DDL
- 목적: 스키마 변경 중에도 DML(INSERT/UPDATE/SELECT) 가능.
- 사용:
ALTER TABLE ... ALGORITHM=..., LOCK=... - 주의: 온라인이라도 리빌드/정렬이 들어가면 부하가 생길 수 있음.
11.7.1.1 온라인 DDL 알고리즘
우선순위 INSTANT → INPLACE → COPY
LOCK=NONE도 메타데이터 락은 필요
- INSTANT: 데이터 변경 없이 메타데이터만 변경(아주 짧은 메타데이터 락).
- INPLACE: 임시 테이블 복사 없이 변경(상황에 따라 리빌드 발생 가능).
- COPY: 임시 테이블 생성 + 데이터 복사 + rename(가장 무거움).
- LOCK 옵션:
NONE / SHARED / EXCLUSIVE
11.7.1.3 INPLACE 알고리즘과 온라인 변경 로그
- DDL 중 발생한 DML을 반영하기 위해 “온라인 변경 로그”에 누적 후 최종 적용.
- 온라인 변경 로그는 메모리 기반, 용량은
innodb_online_alter_log_max_size로 제어.
11.7.1.4 온라인 DDL 실패 케이스
- DDL이 오래 수행되는 동안 DML이 많아 온라인 변경 로그 공간 부족
- 변경 후 제약에 걸리는 데이터가 DDL 중 유입(UNIQUE/타입/길이 등)
- 필요 잠금보다 낮은 LOCK 옵션 강제
- 메타데이터 락 획득 실패로 타임아웃(
lock_wait_timeout) - 온라인 인덱스 생성 시 tmpdir 임시 디스크 공간 부족
11.7.1.5 진행 상황 모니터링
performance_schema+ instrument/consumer 활성화 필요events_stages_current의work_completed/work_estimated로 진행률 추정
SELECT event_name, work_completed, work_estimated
FROM performance_schema.events_stages_current;
/* 진행률 예시:
(work_completed * 100 / work_estimated) */
11.7.2 데이터베이스 변경 (요약)
CREATE DATABASE [IF NOT EXISTS] employees;
SHOW DATABASES;
USE employees;
ALTER DATABASE employees CHARACTER SET = euckr COLLATE = euckr_korean_ci;
DROP DATABASE [IF EXISTS] employees;
11.7.3 테이블 스페이스 변경
- 테이블스페이스: 데이터를 저장하는 큰 논리 단위(내부적으로 extent/page 등 계층).
- 제너럴 테이블스페이스: 여러 테이블 데이터를 한 공간에 저장.
- 장점: 테이블이 많을 때 파일 핸들/메모리 관리 비용 절감 가능.
- 단점: 파티션 불가, DROP해도 공간 회수 어려움, TDE/압축 제약 등.
11.7.4 테이블 변경
11.7.4.2 테이블 구조 조회
SHOW CREATE TABLE: 생성 DDL 전체(키/콜레이션 포함)DESC: 컬럼 중심 요약
11.7.4.3 테이블 구조 변경(리빌드 목적 포함)
- 삭제가 잦아 fragmentation이 커지면 리빌드로 공간 회수/정리 목적의 작업을 하기도 함.
ALTER TABLE employees ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;
11.7.4.4 테이블 명 변경
RENAME TABLE은 메타정보 변경이라 보통 매우 빠름.- 여러 rename을 한 문장으로 묶어 “테이블 교체”에 활용 가능.
RENAME TABLE batch TO batch_old,
batch_new TO batch;
11.7.4.5 테이블 상태 조회
SHOW TABLE STATUS로 엔진/레코드수/콜레이션 등 확인 가능.- InnoDB 레코드 수 등은 대략치일 수 있어 맹신 금지.
11.7.4.6 테이블 구조 복사
CREATE TABLE temp_employees LIKE employees;
11.7.4.7 테이블 삭제
- 대용량 테이블 삭제 시 파일 조각이 분산돼 있으면 디스크 I/O가 커져 다른 쿼리 성능에 악영향 가능.
- 파일시스템 특성(ext 계열 등)과 운영 환경을 고려해 작업 시간대/부하를 설계하는 게 중요.
11.7.5 칼럼 변경
11.7.5.1 칼럼 추가
- 마지막에 추가: INSTANT 가능
- 중간 위치 지정(AFTER/BEFORE): 리빌드 필요 → INPLACE 등
ALTER TABLE employees
ADD COLUMN emp_telno VARCHAR(20),
ALGORITHM=INSTANT;
ALTER TABLE employees
ADD COLUMN emp_telno2 VARCHAR(20) AFTER emp_no,
ALGORITHM=INPLACE, LOCK=NONE;
11.7.5.2 칼럼 삭제
- 컬럼 삭제는 보통 리빌드가 필요하여 INSTANT 불가.
11.7.5.3 칼럼 이름 및 타입 변경
- 타입 변경은 많은 경우 COPY 필요 (INPLACE 불가 에러가 흔함).
VARCHAR길이 변경은 255/256 경계(길이 저장 바이트 변화)에 따라 리빌드 여부가 갈릴 수 있음.
ALTER TABLE salaries
CHANGE to_date end_date DATE NOT NULL,
ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE salaries
MODIFY salary VARCHAR(20),
ALGORITHM=COPY, LOCK=SHARED;
11.7.6 인덱스 변경
- 조회:
SHOW INDEX FROM t(컬럼별 상세),SHOW CREATE TABLE t(DDL 전체) - 이름 변경: 보통 리빌드 없이 빠르게 교체 가능
- 가시성:
INVISIBLE은 SELECT에서만 배제, DML 유지비용은 동일
SHOW INDEX FROM employees;
ALTER TABLE salaries
RENAME INDEX ix_salary TO ix_salary2,
ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE employees
ALTER INDEX ix_firstname INVISIBLE;
11.7.7 테이블 변경 묶음 실행
- 같은 성격/알고리즘 작업은 묶으면 풀스캔/리빌드 횟수를 줄일 수 있음.
- 하지만 알고리즘이 크게 다른 작업(INSTANT vs INPLACE 등)은 굳이 한 번에 묶는 이점이 줄어듦.
-- 따로 하면 테이블 풀스캔이 각각 발생할 수 있음
ALTER TABLE test ADD INDEX idx_1;
ALTER TABLE test ADD INDEX idx_2;
-- 같이 하면 풀스캔이 한 번으로 끝날 수 있음
ALTER TABLE test
ADD INDEX idx_1,
ADD INDEX idx_2;
11.7.8 프로세스 조회 및 강제 종료
SHOW PROCESSLIST: 현재 접속/쿼리 상태 요약(긴 쿼리는 잘림)- 쿼리 전문이 필요하면
information_schema.PROCESSLIST조회 KILL QUERY는 쿼리만 종료,KILL은 커넥션까지 종료
SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST;
KILL QUERY 4228; -- 쿼리만 종료
KILL 4228; -- 커넥션 종료
11.7.9 활성 트랜잭션 조회
- 오래 열린 트랜잭션은 MVCC/Undo를 키워 성능에 악영향(디스크/오버헤드 증가).
information_schema.innodb_trx로 활성 트랜잭션을 조회할 수 있음.
SELECT * FROM information_schema.innodb_trx;
11.8 쿼리 성능 테스트
#sec-11-811.8.1 쿼리 성능에 영향을 미치는 요소
11.8.1.1 운영체제의 캐시
- InnoDB는 Direct I/O 성향이라 OS 캐시 영향이 상대적으로 적음.
- MyISAM은 OS 캐시 의존이 커서 캐시 상태에 따라 성능 변동이 큼.
- 캐시 삭제 후 테스트는 환경별 주의(운영 환경에서는 금지급).
# Linux 예시
sync
echo 3 > /proc/sys/vm/drop_caches
11.8.1.2 MySQL 서버의 버퍼 풀 / 키 캐시
- InnoDB 버퍼 풀: 데이터/인덱스/쓰기 버퍼링까지 캐시.
- MyISAM 키 캐시: 인덱스 중심(데이터는 OS 캐시에 의존).
- 서버 재시작 없이 완전 초기화가 어려움 → 덤프/로드 옵션으로 통제.
SET GLOBAL INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN = OFF;
SET GLOBAL INNODB_BUFFER_POOL_LOAD_AT_STARTUP = OFF;
11.8.1.3 독립된 MySQL 서버
- 웹/배치 등 다른 프로그램이 같이 돌면 자원 경합으로 측정값이 흔들림 → 가능한 분리.
11.8.1.4 쿼리 테스트 횟수
- 콜드/웜 상태를 구분하고 측정 기준을 정해야 함.
- 여러 번(예: 6~7회) 번갈아 실행 후 초반 값을 버리고 평균 비교 권장.
- 테스트 결과는 절대값보다 상대 비교로 보는 게 안전.
주의 단발 실행값 맹신 금지
팁 워밍업 후 평균 비교
'책 > Real MySQL 8.0' 카테고리의 다른 글
| [Real MySQL 8.0] 11장. 쿼리 작성 및 최적화(1) (1) | 2026.01.18 |
|---|---|
| [Real MySQL 8.0] 10장. 실행 계획 (3) | 2026.01.11 |
| [Real MySQL 8.0] 9장. 옵티마이저와 힌트 (1) | 2025.12.30 |
| [Real MySQL 8.0] 6장. 데이터 압축 & 7장. 데이터 암호화 (2) | 2025.12.18 |
| [Real MySQL 8.0] 8장. 인덱스 (1) | 2025.12.09 |