2023/02/27 ~ 2023/03/06

[mojh7]

다음 스터디

2023-03-06 pm 10:10 월요일

정리 범위

• 단 11.4.13.2 ~ 11.5 전까지
• 모 11.5 ~11.5.2

[danbi5228]

11.4.13.2 NOWAIT & SKIP LOCKED

• NOWAIT: FOR UPDATE 시에 해당 레코드가 이미 잠겨진 상태라면 그냥 무시하고 즉시 에러를 반환하도록 하는 옵션
  • 이 옵션이 없다면 시스템 변수에 설정된 시간만큼 대기했다가 에러 메세지를 받게 됨
• SKIP LOCKED: SELECT 하려는 레코드가 다른 트랜잭션에 의해 이미 잠겨진 상태라면 에러를 반환하지 않고 잠긴 레코드는 무시하고 잠금이 걸리지 않은 레코드만 가져옴
  • 트랜잭션의 간섭에 의해 다른 결과를 반환할 수도 있는 '비확정적인' 쿼리가 됨
  • cf. 확정적인 쿼리: 입력이 동일하면 시점에 관계없이 동일한 결과를 반환하는 것
  • Queue와 같은 기능을 MySQL에서 구현하려고 할 때 유용
  • 동시에 100명의 사용자가 쿠폰을 요청한다고 가정했을 때, 각 사용자별로 잠금 대기 없이 사용 가능한 레코드를 찾도록 처리 가능
  • 순차적으로 진행 시 서버 자원 활용률이 떨어질 수 있음 (p.146 ~ 147 참고)
  • MySQL 8.0 이전 버전에서는 Redis나 Memcached 같은 캐시 솔루션을 별도로 구축해서 쿠폰을 발급하도록 구현
• NOWAIT 과 SKIP LOCKED 옵션은 모두 SELECT .. FOR UPDATE 구문에서만 사용 가능
  • 이 옵션들은 쿼리 자체를 비확정적으로 만들어 결국 UPDATE 나 DELETE 쿼리로 실행된 레코드를 알 수 없어지게 되는 문제가 생김

[mojh7]

11.5 INSERT

많은 insert 문장이 동시에 실행되는 경우 insert 문장 보다는 테이블의 구조가 성능에 더 큰 영향을 미침

insert와 select 동시에 빠르게 만들 수 있는 테이블 구조는 없다

• 어느 정도 타협하면서 테이블 구조를 설계해야 함

11.5.1 고급 옵션

두 옵션 모두 유니크 인덱스나 PK에 대해 중복 레코드를 어떻게 처리할지를 결정함

11.5.1.1 INSERT IGNORE

다음 경우를 모두 무시하고 다음 레코드를 처리할 수 있게한다

• 저장하는 레코드의 PK나 유니크 인덱스 칼럼의 값이 이미 테이블에 존재하는 레코드와 중복되는 경우

• 저장하는 레코드의 칼럼이 테이블의 칼럼과 호환되지 않은 경우

주로 여러 레코드를 하나의 INSERT 문장으로 처리하는 경우 유용(149p 쿼리)

salaries 테이블의 PK는 (emp_no, from_date) 인데 중복되는 경우 에러가 발생하지만 ignore 옵션이 있는 경우

에러를 경고 수준의 메시지로 바꾸고 나머지 insert를 계속 진행한다

insert 하고자 하는 데이터가 정교하지 않아도 되는 경우 insert를 실행하기 전에 레코드 건건이 중복 체크를 실행하지 않고 insert ignore 명령으로 처리하는 방식으로 자주 사용됨

데이터 타입이 일치하지 않아서 INSERT를 할 수 없는 경우에 칼럼의 기본값으로 insert를 하도록 만든다(149p 아래 예제)

insert 도중 에러가 발생하면 처리하던 내용을 포기하고 트랜잭션을 롤백하기 때문에 ignore 옵션은 에러 대신 경고 메시지로 수준을 낮추는 것

11.5.1.2 INSERT ... ON DUPLICATE KEY UPDATE

PK나 유니크 인덱스의 중복이 발생하면 UPDATE 문장의 역할을 수행하게 해준다

중복된 레코드가 있다면 기존 레코드를 삭제하지 않고 기존 레코드의 칼럼을 UPDATE 하는 방식으로 작동한다

150~151p 예제

daily_statistic 테이블의 PK는 (target_date, stat_name) 조합으로 생성돼 있기 때문에 일별로 stat_name은 하나씩만 존재할 수 있다

특정 날짜의 stat_name이 최초로 저장되는 경우 INSERT 문장만 실행되고 ON DUPLICATE KEY UPDATE 절 이하의 내용은 무시된다

이미 레코드가 존재한다면 INSERT 대신 ON DUPLICATE KEY UPDATE 절 이하의 내용이 실행된다

예제 쿼리에서는 이미 해당 날짜의 집계 레코드가 존재한다면 기존 레코드의 stat_value 칼럼 값에 +1 한 값을 update한다

150p 아래 예제는 ON DUPLICATE KEY UPDATE 절에서 GROUP BY 결과인 COUNT(*)를 참조할 수 없어서 에러가 발생함 이러한 경우 VALUES() 함수를 사용하면 된다 VALUES() 함수는 컬럼명을 인자로 사용하는데 VALUES(stat_value) 라고 사용하면

MySQL 서버는 인자로 주어진 stat_value 칼럼에 insert 하려고 했던 값을 반환한다

그래서 insert ... select ... group by 문장에서 실제로 저장하려고 했던 값이 무엇인지 몰라도

VALUES() 함수를 사용하면 stat_value 칼럼에 insert 하고자 했던 값을 다시 가져올 수 있다

해당 쿼리에서는 stat_value 칼럼에 insert 하려고 했던 값과 기존 레코드의 stat_value 칼럼이 가지고 있던 값의 합을 stat_value 칼럼에 업데이트 한다

MySQL 8.0.20 버전부터는 VALUES() 함수가 지원되지 않을 예정(Deprecated)이라 152p 처럼 문법으로 대체해서 사용할 것을 권장

insert ... select ... 형태의 문법이 아닌 경우 insert되는 레코드에 대해 별칭을 부여해서 참조하는 문법을 사용하면 VALUES() 함수의 사용을 피할 수 있다

11.5.2 LOAD DATA 명령 주의 사항

일반적으로 RDBMS에서 데이터를 빠르게 적재할 수 있는 방법으로 LOAD DATA 명령이 자주 소개됨

내부적으로 MySQL 엔진과 스토리지 엔진의 호출 횟수를 최소화하고 스토리지 엔진이 직접 데이터를 적재하기 때문에 일반적인 insert 명령과 비교했을 때 매우 빠르다고 볼 수 있음

단점

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

적재하는 데이터가 아주 많지 않다면 해당 단점은 문제가 되지 않음

하지만 데이터가 매우 커서 실행 시간이 길어진다면 다른 온라인 트랜잭션 쿼리들의 성능이 영향을 받을 수 있음

테이블에 여러 인덱스가 있다면?

• 레코드를 insert하고 인덱스에도 키 값을 insert 해야함

테이블에 레코드가 insert 될수록 테이블과 인덱스의 크기가 커지게됨

LOAD DATA 문장은 단일 스레드로 실행되기 때문에 시간이 지날수록 insert 속도가 현저히 떨어짐

하나의 트랜잭션으로 처리되기 때문에 LOAD DATA 문장이 시작한 시점부터 Undo Log가 삭제되지 못하고 유지돼야 한다

• 언두 로그를 디스크로 기록해야하는 부하를 만듬
• 언두 로그가 쌓이면 레코드를 읽는 쿼리들이 필요한 레코드를 찾는데 더 많은 오버헤드를 만듬

가능하다면 LOAD DATA 문장으로 적재할 데이터 파일을 하나보다는 여러 개의 파일로 준비해서

동시에 여러 트랜잭션으로 나뉘어 실행하는 것이 좋다

테이블 간 데이터 복사 작업이라면 INSERT ... SELECT ... 문장으로 WHERE 조건 절에서 데이터를 부분적으로 잘라서 효율적으로 insert할 수 있게 해주는 것이 좋다

실제 데이터 간의 복사는 LOAD DATA 문장으로 데이터를 잘라서 INSERT ... SELECT ... 문장으로 PK 값을 기준으로 데이터를 잘라서 여러 개의 스레드로 실행하기가 훨씬 용이하다