[Real MySQL] 쿼리 작성 및 최적화 2

INSERT

INSERT IGNORE

• 다음과 같은 경우에 추가되는 레코드를 무시
  • 프라이머리 키나 유니크 인덱스 컬럼의 값이 이미 테이블에 존재하는 경우
  • 저장하는 레코드의 컬럼이 테이블의 컬럼과 호환되지 않는 경우

INSERT … ON DUPLICATE KEY UPDATE

• 프라이머리 키나 유니크 인덱스의 중복이 발생하면 UPDATE 문장의 역할을 수행

LOAD DATA

• LOAD DATA 명령은 단일 스레드로 실행되기 때문에 적재해야 할 데이터 파일이 매우 크다면 시간이 매우 길어질 수 있다.

프라이머리 키 선정

• 프라이머리 키의 선정은 “INSERT 성능”과 “SELECT 성능” 중의 하나를 선택하는 것
• INSERT가 매우 많이 실행되는 테이블의 경우, 프라이머리 키를 단조 증가 또는 단조 감소하는 패턴의 값을 사용하는 것이 좋다.
  • 주로 로그 등을 저장하는 테이블
• 사용자 정보와 같은 중요한 정보를 저장한 테이블의 경우 읽기 비율이 매우 높기 때문에 SELECT 쿼리를 빠르게 만드는 프라이머리 키를 선정하는 것이 좋다.

Auto-Increment 컬럼

• INSERT에 최적화된 테이블을 생성하기 위한 조건
  • 단조 증가 또는 단조 감소되는 프라이머리 키
  • 세컨더리 인덱스의 최소화
• INSERT를 빠르게 수행하기 위해서는 별도의 자동 증가 컬럼을 생성해 해당 컬럼을 프라이머리 키로 지정

CREATE TABLE access_log (
	id BIGINT NOT NULL AUTO_INCREMENT,
	...,
	
	PRIMARY KEY(id)
);

UPDATE / DELETE

UPDATE … ORDER BY … LIMIT N

• UPDATE나 DELETE 문장에 ORDER BY + LIMIT n 구문을 사용해 상위 몇 건만 제거 및 수정을 할 수 있다.

JOIN UPDATE

• 두 개 이상의 테이블을 조인해 조인된 결과 레코드를 변경 및 삭제하는 쿼리
• JOIN UPDATE는 조인되는 테이블 중 읽기 참조만 되는 테이블은 읽기 잠금이 걸리고, 컬럼이 변경되는 테이블은 쓰기 잠금이 걸린다.
• 웹 서비스 같은 OLTP 환경에서는 데드락을 유발할 가능성이 높기 때문에 빈번하게 사용하는 것은 지양