[Real MySQL 8.0] 11장. 쿼리 작성 및 최적화(2)

Real MySQL 8.0 11장. 쿼리 작성 및 최적화

INSERT / UPDATE·DELETE / DDL / 성능 테스트

Contents

11.5 INSERT 고급 옵션 · LOAD DATA · 테이블 구조 11.6 UPDATE와 DELETE ORDER BY/LIMIT · JOIN · 다중 업데이트 11.7 스키마 조작(DDL) 온라인 DDL · 테이블/칼럼/인덱스 11.8 쿼리 성능 테스트 캐시/버퍼 · 환경 · 반복 측정

11.5 INSERT

#sec-11-5

11.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 + INSERT
  • ON 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-6

11.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-7

11.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;

※ charset/collation은 비교/정렬 규칙과 인덱스 활용 여부에도 영향을 줄 수 있음.

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;

※ 보통 컬럼/인덱스는 복사되지만, FK는 복사되지 않는 것으로 알려져 있음(주의).

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-8

11.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회) 번갈아 실행 후 초반 값을 버리고 평균 비교 권장.
• 테스트 결과는 절대값보다 상대 비교로 보는 게 안전.

주의 단발 실행값 맹신 금지 팁 워밍업 후 평균 비교