책 내용이 쉽지 않아 이해가 잘가지 않았던 부분에 나만의 주석을 달아놓기 위함
+ 실무적으로 참고하고 싶은 부분
4장 아키텍쳐
4.2.1 프라이머리 키에 의한 클러스터링
p 99. '프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리 될 수 있다'
프라이머리 키에 의한 클러스터링이란, 데이터의 실제 물리적 주소가 PK 값을 기준으로 배열되어있다는 뜻이다. 클러스터링 키를 지원하지 않는 MyISAM 엔진의 경우 데이터의 물리적 주소는 여기저기 분산되어있고, 인덱스는 이 주소값을 가지고 있는 방식으로 동작한다.
전자(클러스터링 키) 가 후자(비 클러스터링 키) 보다 근본적으로 성능의 차이를 보이는 이유는 데이터의 물리적인 주소가 한군데에 같이 모여 있다는 것이다. 그렇게 되면 한 테이블 내에서 여러건의 데이터를 검색할 때 랜덤 io 작업이 아닌 순차 io 작업이 일어나게 되기 때문이다.
Q1. 랜덤 IO 나 순차 IO 가 비슷한 성능을 가지는 SSD 에서도 이러한 성능상 이점이 유효한가?
mysql 에서 데이터는 페이지 단위로 저장이 되고, 데이터가 캐시될 때에도 페이지 단위로 캐시가 된다. 클러스터링 인덱스를 지원하는 경우에는 데이터 페이지가 그대로 메모리로 로드되는데, 이렇게 연속적인 데이터 블록을 처리하는 경우, ssd 라고 하더라도 한번의 io 요청으로 io 를 수행할 수 있기 때문에 더 나은 성능을 기대할 수 있다.
Q2. PK 가 순차적으로 증가되는 값이 아니라 UUID 이면 실제 데이터도 물리적으로 계속 재배치가 되지 않나?
그래서 auto_increment 같은 순차적으로 증가하는 PK 를 쓰는 것이 클러스터링 인덱스에서 쓰기 작업에 훨씬 유리하다. 왜냐면 가장 끝부분에 데이터를 추가하면 되기 때문이다. 이 경우에는 물리적인 공간의 재배치가 일어나지 않는다. UUID 값을 컬럼에 쓰고 싶다면 자동 증가 컬럼을 PK 로 둔 상태로(인조 식별자) UUID 는 세컨더리 인덱스로 쓰는 것이 좋다. (UUIDv1 / UUIDv7 같이 어느정도 순차성을 띄는 UUID 를 사용하는 옵션도 있는데 별로 좋아보이진 않는다.) 특히, 로그성 데이터 같이 Insert 가 요청의 주를 이루는 경우는 쓰기 작업의 효율이 중요하므로 순차적인 PK 를 사용할 것.
Q3. PK 가 데이터 간의 연관성을 전혀 반영하고 있지 않으면 데이터가 물리적으로 근접해있다고 하더라도 결국 랜덤 IO 가 일어나고, 페이지 단위의 캐시도 히트율이 낮지 않을까?
그래서 PK 가 데이터 간의 강한 연관성을 가지는 경우, 쓰기 작업이 자동증가 PK 에 비해서 불리하다고 하더라도 버퍼 풀의 캐시 히트율을 높일 수 있고 레인지 스캔시 순차 io 를 유도할 수 있다.
--> 인덱스 관련한 더 자세한 내용은 8장 인덱스 부분에서 확인 할 수 있음
4.2.2 외래키 지원
p99. 'InnoDB 에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 컬럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많으므로 개발할 때도 외래 키의 존재에 주의하는 것이 좋다.'
p99. 'foreign_key_checks 시스템 변수를 OFF 로 설정하면 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다.'
4.2.4 잠금 없는 일관된 읽기(Non-Locking Consistent Read)
p.104 '오랜 시간 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제가 발생할 때가 가끔 있는데, 바로 이러한 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하기 때문에 발생하는 문제다.'
4.2.12 어댑티브 해시 인덱스
p 137. InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있다.
클러스터링 인덱스를 사용하는 경우 보조 인덱스를 통해 원하는 데이터를 찾아낸다고 하더라도 클러스터링 인덱스에서 다시 한번 트리 검색을 해야한다. AHI 는 실제 데이터 페이지의 주소를 값으로 가지고 있어서 이러한 검색을 줄여준다.
5장 트랜잭션과 잠금
5.3.1.3 넥스트 키락
p 168. InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적이다. 그런데 의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다. 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.
Statement 기반 바이너리 로그는 리두 로그로 실행된 sql 문을 저장하는 방식이다. 이를 이용해 레플리카 서버를 마스터 서버와 동기화 시키게 된다. 그런데 마스터 서버와 레플리카 서버에서 동일한 SQL을 실행한다고 해도, 실행 환경의 차이(예: 타이밍, 락 순서 등)로 인해 다른 결과가 나올 가능성이 있다. 넥스트 키 락, 갭 락은 이런 문제를 방지한다. 그런데 생각보다 이러한 과정에서 경합이 심하게 발생하기 때문에, 이러한 경합을 줄이도록 Row 기반 바이너리 포맷을 사용하라는 이야기다.
5.4.3 Repeatable Read
p 183. SELECT ... FOR UPDATE 쿼리는 SELECT하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸수 없다. 그래서 SELECT ... FOR UPDATE 나 SELECT ... LOCK IN SHARE MODE로 조회되는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는것이다.
언두 영역은 트랜잭션 중간에 데이터가 변경되었더라도 동일한 데이터를 읽을 수 있도록(Repeatable Read) 변경 전 데이터를 저장해두는 공간이다. 그런데 Repeatable Read 격리 수준에서도 Select for update 에서는 동일한 데이터를 읽지 못하기도 하는데, 이를 Phantom Read 라고 한다. Select for update 를 위해서는 쓰기 잠금이 걸려있어야 하는데, 언두 영역은 단순히 데이터를 기록하는 공간이기 때문에 잠금을 걸 수가 없고, 그래서 언두로그의 데이터가 아닌 현재 레코드 정보를 가지고 온다.
p 183. 하지만 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 "PHANTOM READ" 가 발생하지 않기 때문에 굳이 SERIALIZABLE 을 사용할 필요성은 없어 보인다.
Phantom Read 는 트랜잭션 내에서 이전에 조회되지 않았던 데이터가 동일한 쿼리에서 조회가 되는 부작용을 의미한다. 그런데 MVCC 에서는 읽기 시 스냅샷을 이용하기 때문에 Phantom Read 가 일어나지 않는다. 그러나 범위 조회에서는 해당 범위에 있는 데이터를 읽어올 때는 MVCC 가 레코드 삽입까지 감지하여 이를 방지해주지 못한다. 이를 위해 갭 락 / 넥스트 키 락을 이용하여 Phantom Read 를 방지할 수 있다. 이 때는 SELECT ... FOR UPDATE 와 같은 명시적인 락 쿼리를 사용하여야 한다.
+)
MVCC 와 낙관적 락
MVCC 는 Multi Version Concurrency Control 의 줄임말로, 스냅샷(버전)을 통해 트랜잭션이 시작한 이전의 데이터만 읽기 때문에, 반복 읽기를 가능하게 한다. 그러나 Update 시에 해당 데이터가 트랜잭션 이전의 데이터와 달라졌는지를 확인하는 지를 수행하지 않기 때문에 Lost Update 의 가능성이 있다. 이러한 Lost Update 를 예방하기 위해서는 Select ... For Update 를 통해 데이터베이스 레벨에서의 잠금(Shared Lock) 을 걸어주어 데이터가 Update 되지 않도록 해야한다. 이때 걸린 락은 다른 트랜잭션의 수정을 미리 염두해 두고 수행되기 때문에 비관적 락이라고 한다.
* 참고로, 이때 Select ... For Update 쿼리는 MVCC 를 통해 언두 로그에서 데이터를 읽지 않고, 현재 데이터 상태를 읽어오기 때문에 이전의 조회 결과와 달라지는 Non-Repeatable Read 가 발생한다. 책에서는 이부분을 모호하게 Phantom Read 라고 설명하였는데, 나는 MVCC 에서 새로운 데이터가 삽입되었을 때 범위 조회 수행시 해당 데이터가 읽히는 현상인 Phantom Read 랑은 조금 결이 다르다고 생각한다.
하지만 이러한 조치는 다른 트랜잭션의 수정을 블락시키기 때문에 동시성이 저해될 수 있다. 이를 더 개선하고자 어플리케이션 레벨에서 낙관적 락이라는 매커니즘을 구현할 수 있다. 테이블에 버전 컬럼을 추가하여 업데이트 마다 버전을 증가하게 해놓고, 업데이트를 시도할 때 이전에 읽어온 값의 버전과 일치하는 지를 확인하는 것이다. 자바의 Atomic 클래스에서 사용하는 CAS(Compare-And-Swap) 알고리즘과 비슷한 매커니즘으로 동작하는 것이다. 이 경우에는 락을 걸지 않으므로 다른 트랜잭션이 락을 위해 대기를 할 필요가 없게 되므로 동시성이 개선된다. 또한, 어플리케이션 레벨에서 구현되므로 트랜잭션을 롤백하지 않고 재시도 한다거나 아니면 요청자에게 경고 메세지를 전달하는 등 비지니스 로직을 추가할 수 있다는 장점도 있다.
8장 인덱스
8.3.2.4 인덱스 키 검색
p 225. 따라서 UPDATE 나 DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다.
레코드 락을 걸때는 락이 걸리는 대상을 선별하게 되는데, 이 때 인덱스가 그 락의 범위를 결정짓는 기반이 된다. 변경 요청이 들어오면 변경의 대상이 되는 레코드를 찾기 위해 우선 데이터를 스캔을 한다. 만약 레코드를 스캔하는 과정에서 스캔 범위 안에서 데이터 변경이 생기게 되면 데이터 일관성이 깨지게 된다. 이러한 데이터 변경을 막기 위해 해당 스캔 범위에 있는 모든 레코드를 잠가버리는데, 만약에 검색 조건에 인덱스가 포함되면 스캔이 일어나는 범위를 좁힐 수 있게 된고, 그만큼 락의 범위 또한 좁힐 수 있다. 참고로 스캔의 범위 안에 새로운 데이터가 INSERT 되는 것 또한 정합성을 깨트릴 수 있기 때문에, 갭 락과 넥스트 키 락이라는 매커니즘을 제공한다.
8.3.3.3 선택도(기수성)
p 228 선택도가 좋지 않다고 하더라도 정렬이나 그루핑과 같은 작업을 위해 인덱스를 만드는 것이 훨씬 나은 경우도 많다. 인덱스가 항상 검색에만 사용되는 것은 아니므로 여러 가지 용도를 고려해 적절히 인덱스를 설계할 필요가 있다
9장 옵티마이저와 힌트
9.2.3.2 정렬 알고리즘
p 292. 하지만 운영체제별로 STL의 stable_sort() 가 어떤 정렬 알고리즘을 사용하는지는 조금씩 차이가 있다. 리눅스 서버에서 사용하는 GNU C++의 STL에서는 퀵 소트(Quick-sort)와 힙 소트(Heap-sort) 알고리즘을 복합적으로 사용한다.
예시로들은 옵티마이저 트레이스에서 "sort_algorithm": "std::stable_sort" 라고 되어있는데 퀵 소트와 힙 소트는 stable 한 정렬 알고리즘이 아니다. 그래서 이건 서술 자체에 모순이 있어보인다. 아마도 c++ 에서 sort 라는 함수가 퀵 소트와 힙 소트를 복합적으로 사용하는데 (퀵 소트는 최악의 경우 시간복잡도가 n^2 가 될 수 있어서 이를 방지하기 위함) 그걸 말하려고 하는 것 같다. 실제로 c++ 에서 stable_sort() 는 머지 소트를 변형해서 사용한다.
9.2.5.2 집합 함수와 함께 사용된 DISTINCT
p 312. 이때 임시 테이블의 salary 칼럼에는 유니크 인덱스가 생성되기 때문에 레코드 건수가 많아진다면 상당히 느려질 수 있는 형태의 쿼리다.
COUNT(DISTINCT s.salary) 이 부분을 처리하는 과정에서 유니크 인덱스를 이용한다는 뜻이다. 즉, 임시테이블을 생성하면서 유니크 인덱스를 임시로 생성해서 B-Tree 정렬을 하고, 중복 여부 검사를 수행하기 때문에 시간이 오래걸린다는 듯이다.
