일반적으로 온라인 트랜잭션 서비스에서 INSERT 문장은 대부분 1건 또는 소량의 레코드를 처리하므로 그다지 성능에 대해 고려할 부분이 많지 않다.

오히려 많은 INSERT 문장이 동시에 실행되는 경우 INSERT 문장 자체보다는 테이블의 구조가 성능에 더 영향을 미친다.

하지만 많은 경우 ISNERT의 성능과 SELECT의 성능을 동시에 빠르게 만들 수 있는 테이블 구조는 없기 때문에, INSERT와 SELECT 성능을 어느 정도 타협하면서 테이블 구조를 설계해야 한다.

고급 옵션

INSERT IGNORE

INSERT 문장의 IGNORE 옵션은 특정 상황에 대한 오류를 모두 무시하고 다음 레코드를 처리할 수 있게 한다.

• 저장하는 레코드의 프라이머리 키나 유니크 인덱스 컬럼의 값이 이미 테이블에 존재하는 레코드와 중복되는 경우
  • 에러를 경고 수준의 메시지로 바꾸고 나마저 레코드 처리를 진행한다.
• 저장하는 레코드의 컬럼이 테이블의 컬럼과 호환되지 않는 경우
  • 에러를 경고 수준의 메시지로 바꾸고 해당 컬럼의 기본 값으로 INSERT 하도록 만든다.

프로그램 코드에서 중복을 무시하기 위해 INSERT IGNORE 옵션을 사용한다면 데이터 중복 이외의 에러가 발생할 여지가 없는지 면밀히 확인한 후 적용하는 것이 좋다.

제대로 검증되지 않은 INSERT IGNORE 문장은 의도하지 않은 에러까지 모두 무시해버릴 수도 있다.

INSERT … ON DUPLICATE KEY UPDATE

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

• MySQL 서버의 REPLACE 문장도 비슷한 역할을 하지만 REPLACE는 내부적으로 DELETE 후 INSERT로 작동한다.

1
2
3
4
5
6
7
8

INSERT INTO daily_statistic
    SELECT DATE(visited_at), 'VISIT', COUNT(*)
    FROM access_log
    GROUP BY DATE(visited_at)
    ON DUPLICATE KEY UPDATE stat_value = stat_value + COUNT(*)
;

# ERROR 1111 (HY000): Invalid use of group function

위 예제에서 stat_value 컬럼에는 GROUP BY 결과 건수를 저장하고 있다

• 중복으로 실행 된 ON DUPLICATE KEY UPDATE 절에서 GROUP BY 결과인 COUNT(*)를 참조할 수 없어 에러가 발생한다.
• 이러한 경우 VALUES() 함수를 사용하면 해결할 수 있다.

1
2
3
4
5
6

INSERT INTO daily_statistic
    SELECT DATE(visited_at), 'VISIT', COUNT(*)
    FROM access_log
    GROUP BY DATE(visited_at)
    ON DUPLICATE KEY UPDATE stat_value = stat_value + VALUES(stat_value)
;

• VALUES() 함수는 컬럼명을 인자로 사용하는데, 위의 예제같이 사용하면 INSERT 하려고 했던 값을 반환한다.

MySQL 8.0.20 버전부터 VALUES() 함수가 지원되지 않을 예정이므로 아래와 같이 작성해야 한다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

INSERT INTO daily_statistic
    SELECT target_date, stat_name, stat_value
    FROM (
        SELECT DATE(visited_at) target_date, 'VISIT' stat_name, COUNT(*) stat_value
        FROM access_log
        GROUP BY DATE(visited_at)
    ) stat
ON DUPLICATE KEY UPDATE 
    daily_statistic.stat_value = daily_statistic.stat_value + stat.stat_value
;

1
2
3
4
5
6

INSERT INTO daily_statistic (target_date, stat_name, stat_value)
    VALUES ('2020-09-01', 'VISIT', 1)
        ,('2020-09-02', 'VISIT', 1) AS new
ON DUPLICATE KEY UPDATE 
    daily_statistic.stat_value = daily_statistic.stat_value + new.stat_value
;

LOAD DATA 명령 주의 사항

LOAD DATA 명령은 내부적으로 MySQL 엔진과 스토리지 엔진의 호출 횟수를 최소화하고 스토리지 엔진이 직접 데이터를 적재하기 때문에 INSERT 명령과 비교했을때 매우 빠르다.

• 단일 스레드로 실행
• 단일 트랜잭션으로 실행

적재하는 데이터가 아주 많지 않다면 큰 문제가 되지는 않지만, 데이터가 매우 커서 실행 시간이 아주 길어진다면 다른 온라인 트랜잭션 쿼리들의 성능이 영향을 받을 수 있다.

• LOAD DATA는 단일 스레드로 실행되기 때문에 테이블과 인덱스가 커질수록 INSERT 속도가 떨어진다.
  • 테이블에 여러 인덱스가 있다면 LOAD DATA 문장이 레코드를 INSERT하고 인덱스에도 키 값을 INSERT 해야 한다.
  • 레코드가 INSERT 될수록 테이블과 인덱스의 크기도 커진다.
• LOAD DATA는 단일 트랜잭션으로 처리되기 때문에 문장이 시작한 시점부터 언두로그가 삭제되지 못하고 유지되어야 한다.
  • 언두로그가 많이 쌓이면 레코드를 읽는 쿼리들이 필요한 레코드를 찾는 데 더 많은 오버헤드를 만들어 내기도 한다.

• 가능하다면 LOAD DATA 문장으로 적재할 데이터 파일 하나보다는 여러 개의 파일로 준비하여 동시에 여러 트랜잭션으로 나뉘어 실행되게 하는 것이 좋다.
• 테이블 간 데이터 복사 작업이라면 INSERT ... SELECT ... 문장으로 조건절에서 데이터를 부분적으로 잘라 효율적으 INSERT할 수 있게 해주는 것이 좋다.
  • 프라이머리 키 값을 기준으로 데이터를 잘라 여러 개의 스레드로 실행하기가 훨씬 용이하다.

성능을 위한 테이블 구조

INSERT 문장의 성능은 쿼리 문장 자체보다는 테이블의 구조에 의해 많이 결정된다.

• 대부분 INSERT 문장은 단일 레코드를 저장하는 형태로 많이 사용되기 때문에 ISNERT 문장 자체는 튜닝할 수 있는 부분이 별로 없다.
• 실제 쿼리 튜닝을 할 때도 소량의 레코드를 INSERT 하는 문장 자체는 무시하는 경우가 많다.

대량 INSERT 성능

하나의 INSERT 문장으로 수백, 수천 건의 레코드를 INSERT한다면 대상 레코드들을 키 값 기준으로 미리 정렬하여 INSERT 문장을 구성하는 것이 성능에 도움이 될 수 있다.

프라이머리 키

레코드를 INSERT 할 때마다 InnoDB 스토리지 엔진은 프라이머리 키를 검색하여 레코드가 저장될 위치를 찾아야한다.

• 정렬없이 랜덤한 레코드를 저장할 경우 프라이머리 키의 B-Tree에서 랜덤한 위치의 페이지를 메모리로 읽어와야 하기 때문에 처리가 더 느리다.
• InnoDB 스토리지 엔진의 버퍼 풀이 충분히 크다면 비교적 덜 느릴 수 있다.

세컨더리 인덱스

SELECT의 성능을 높히지만 INSERT 성능은 떨어지므로 남용하는 것은 성능상 좋지 않다.

• 테이블에 세컨더리 인덱스가 많을수록, 테이블이 클수록 INSERT 성능이 떨어지게 된다.
• 세컨더리 인덱스도 정렬된 순서대로 처리할 수 있다면 더 빠른 성능을 얻을 수 있지만, 세컨더리 인덱스가 저장되는 순서대로 정렬되게 보장하기 어렵다.
• 세컨더리 인덱스의 변경은 일시적으로 체인지 버퍼에 버퍼링됐다가 백그라운드 스레드에 의해 일괄 처리될 수 있으나, 너무 많은 세컨더리 인덱스는 백그라운드 작업의 부하를 유발하므로 전체적인 성능은 떨어진다.

프라이머리 키 선정

프라이머리 키의 선정은 INSERT 성능과 SELECT 성능의 대립되는 두가지 요소 중에서 하나를 선택해야 함을 의미한다.

테이블의 프라이머리 키는 INSERT 성능을 결정하는 가장 중요한 부분이지만, 대부분의 온라인 트랜잭션 처리를 위한 테이블은 쓰기보다는 읽기 쿼리 비율이 압도적으로 높다. 이러한 부분을 모두 만족하는 프라이머리 키를 선택하는 것이 가장 좋지만, 매우 드문 경우이므로 적절히 타협해 프라이머리 키를 선정해야한다.

• INSERT

  • 프라이머리 키의 순서: 프라이머리 키를 단조 증가 또는 단조 감소하는 패턴의 값을 선택하는 것이 좋다.
    • INSERT 하는 처리는 프라이머리 키의 B-Tree가 메모리에 적재돼 있어야 빠른 INSERT를 보장할 수 있다.
    • 프라이머리 키의 전체 범위에 대해 랜덤하게 저장된다면 더 큰 범위의 키를 메모리에 적재하여 키를 탐색해야한다.
    • 메모리가 부족한 경우 저장될 위치를 찾기 위해 디스크 읽기가 발생하게되어 성능이 매우 떨어질 수 있다.
  • 인덱스의 개수를 최소화 해야한다.

• SELECT

  • 클러스터링 키의 특성: 프라이머리 키에 대한 클러스터링 키를 사용하는 InnoDB 스토리지 엔진은 세컨더리 인덱스를 이용하는 쿼리보다 프라이머리 키를 이용하는 쿼리의 성능이 훨씬 빠르다.
    • 프라이머리 키를 데이터 조회에 최적화된 컬럼으로 구성하는 것이 좋을 수 있다.
  • 쿼리에 맞게 필요한 인덱스들을 추가해도 시스템 전반겅으로 영향도가 크지 않다.

Auto-Increment 컬럼

SELECT 보다는 INSERT에 최적화된 테이블을 생성하기 위해서는 다음 두가지 요소를 갖춰 테이블을 준비한다.

• 단조증가 또는 단조 감소되는 값으로 프라이머리 키 선정
• 세컨더리 인덱스 최소화

InnoDB 스토리지 엔진을 사용하는 테이블은 자동으로 프라이머리 키로 클러스터링 된다. 하지만 자동 증가 컬럼을 이용하면 클러스터링 되지 않는 테이블의 효과를 얻을 수 있다.

• 자동 증가 값을 프라이머리 키로 해서 테이블을 생성하는 것이 가장 빠른 INSERT를 보장한다.

MySQL 서버에서는 자동 증가 값의 채번을 위해서 잠금이 필요한데, 이를 AUTO-INC 잠금이라고 하며, 시스템 변수를 통해 변경할 수 있다.

• innodb_autoinc_lock_mode=0
  • 항상 AUTO-INC 잠금을 걸고 한 번에 1 씩만 증가된 값을 가져온다.
  • MySQL 5.1 호환성과 테스트 용도로만 사용하기 위한 설정이다.
• innodb_autoinc_lock_mode=1: Consecutive mode, MySQL 5.7 Default
  • 레코드 한 건씩 INSERT하는 쿼리에서는 잠금을 사용하지 않고 뮤텍스를 이용해 처리한다.
  • 하나의 INSERT 문으로 여러 레코드를 처리하거나 LOAD DATA 처리시 잠금을 걸고 필요한 만큼 자동 증가 값을 한꺼번에 가져와 사용한다.
  • 순서대로 채번된 자동 증가 값은 일관되고, 연속된 번호를 갖게 된다.
• innodb_autoinc_lock_mode=2: Interleaved mode, MySQL 8 Default
  • LOAD DATA나 벌크 INSERT를 포함한 문장을 실행할 때 잠금을 사용하지 않는다.
  • 자동 증가 값을 적당히 미리 받아 처리할 수 있으므로 가장 빠르다.
  • 채번된 번호는 단조 증가하는 유니크한 번호까지만 보장하며, 순서와 번호의 연속성은 보장하지 않는다.
  • 쿼리 기반의 복제를 사용하는 MySQL 서버와 레플리카 서버의 자동 증가 값이 동기화되지 못할 수도 있으므로 주의해야한다.

복제를 STATEMENT 바이너리 로크 포맷으로 사용 중이라면 1로 설정해야한다.

자동 증가 값이 반드시 연속이어야 한다면 2보다는 1로 설정하는 것이 좋지만, 0이나 1로 설정하더라도 시간이 지마면 연속된 값에 빈 공간이 생기므로 집착하지 않아도 괜찮다.