treecode

데이터를 안전하게 저장, 관리하고 효율적으로 조회하려면 옵티마이저가 어떻게 동작할지, 쿼리의 실행 계획에 대해 알아야한다. 

실행 계획은 쿼리를 실행할때 EXPLAIN 키워드를 붙여서 실행하면 된다. 실행 계획도 그때그때 찾아보는 것이 좋을 것 같은데 자주 보이고 알면 좋은 부분만 정리해봤다.

실행 계획 결과는 기본적으로 테이블(json, tree 같은 포맷도 있다)로 여러 정보를 알려준다.

1. id

- 단위 select 쿼리(select 키워드 단위로 구분한 것)의 식별자로 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 생기지만 같은 id 값을 가진다. (id 값이 같으면 조인된 경우)

- id가 테이블의 접근 순서를 의미하지는 않는다.

2. select_type

- 단위 select 쿼리가 어떤 타입의 쿼리인지 나타낸다.

- 서브쿼리를 사용하지 않는 단순 쿼리를 나타내는 SIMPLE, 서브 쿼리를 가지는 select 쿼리의 가장 바깥쪽에 있는 쿼리를 나타내는 PRIMARY 등의 키워드가 있다.

- DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블(파생 테이블)을 생성하는 것을 의미하는데 주로 from절 서브쿼리를 사용하는 경우를 나타낸다. (성능상 주의깊게 볼 부분)

3. type

- MySQL 서버가 각 테이블을 어떤 방식으로 읽을지를 나타낸다.

- 주로 쓰이는 타입으로는 all < index < range < index_subquery < unique_subquery < ref < eq_ref < const 의 순서대로 빠른 순서를 보인다.

- const(Unique index scan)는 where절로 PK 또는 유니크 키로 1건만 조회하는 쿼리로 많이 사용하는 방식이다.

- eq_ref는 join에서 처음 읽은 테이블의 컬럼값을, 그 다음 읽어야 할 테이블의 PK나 유니크 키 컬럼의 검색 조건에 사용할 때 나타낸다.

- ref는 인덱스 종류와 관계없이 동등 조건으로 검색할 때를 나타낸다.

- index_subquery, unique_subquery는 where 조건절에서 사용되는 in(subquery) 형태의 쿼리를 나타낸다. MySQL8.0 버전은 in subquery 형태의 세미 조인의 최적화된 기능이 많이 도입 되어서 실제 테스트 해보니 ref로 실행 된다거나 하는 경우가 많았다. (책에서도 최적화 옵션을 비활성화 하고 테스트를 하고 있다)

- range는 인덱스 레인지 스캔 형태의 접근 방법으로 인덱스를 범위로 검색하는 경우 (<, >, IS NULL, BETWEEN, IN, LIKE)등의 연산자로 검색시 사용된다. 속도는 생각보다 낮지만 range로 조회만 하더라도 어느정도 최적의 성능이 보장된다고 한다.

- index는 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔방식이다

- all은 풀 테이블 스캔 방식으로 InnoDB는 풀 스캔시 대량의 디스크 I/O를 유발하는 작업을 위해 한번에 많은 페이지를 읽어 들이는 기능(Read AHead)을 제공한다. 대용량 레코드 처리시 잘못 튜닝된 인덱스 쿼리보다는 풀 테이블 스캔이 더 나은 경우도 있다.

4. keys

- 실행 계획에서 사용하는 인덱스를 의미하며 쿼리를 튜닝할 때는 key 컬럼에 의도한 인덱스가 표시되는지 확인하는 것이 중요하다.

5. rows

- 얼마나 많은 레코드를 읽고 체크해야 하는지 예측한 레코드 건수를 의미한다.

- 스토리지 엔진이 가지고 있는 통계 정보를 참조해서 MySQL 옵티마이저가 산출해 낸 예상값이라서 정확하지는 않다.

6. filtered

- 필터링되고 남은 레코드의 비율을 의미한다.

7. extra

- 성능에 중요한 부분, 처리 방식등을 보여준다.

- Using where은 MySQL 엔진에서 스토리지 엔진으로부터 받은 데이터를 별도의 가공을 해서 필터링 작업을 처리한 경우에 나타난다. (검색 조건에 따라 스토리지 엔진에서 필터링을 할 수도, MySQL 엔진에서 필터링을 할 수도 있다.)

- Using index(커버링 인덱스)는 인덱스만 읽어서 쿼리를 처리할 수 있는 경우 나타난다. 인덱스를 이용해 처리하는 쿼리에서 가장 큰 부하는 인덱스 검색에서 일치하는 키 값들의 레코드를 읽기 위해 데이터 파일을 검색하는 작업이다. InnoDB의 세컨더리 인덱스는 데이터 레코드를 찾아가기 위한 논리 주소로 사용하기 위해 PK를 같이 저장하는데 이는 추가 컬럼을 하나 더 가지는 인덱스 효과도 얻을 수 있다. 하지만 너무 과도하게 인덱스 컬럼이 많아지면 메모리 낭비가 심해지고 레코드 저장, 변경 작업이 매우 느려질 수 있어서 주의해야한다.

-Using index condition는 (MySQL 5.6 버전부터) 인덱스를 범위 제한 조건으로 사용하지 못한다고 하더라도 인덱스에 포함된 컬럼의 조건이 있다면 모두 같이 모아서 스토리지 엔진으로 전달하는 것을 말한다. 만약 스토리지 엔진에서 범위 제한 조건으로 10000건을 조회해서 MySQL엔진으로 전달했는데 MySQL 엔진에서 체크 조건으로 1건만 필터링이 된다면 매우 비효율적이다.

- Using filesort는 order  by를 처리하기 위해 적절한 인덱스를 이용하지 못해서 조회된 레코드를 메모리 버퍼에 복사해 다시 한번 정렬하는 경우를 말한다. (성능상 주의해야 될 부분)

explain analyze로 검색하면 쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있다.

- 들여쓰기가 같은 레벨에서는 상단 라인이 먼저 실행

- 들여쓰기가 다른 레벨에서는 가장 안쪽 라인이 먼저 실행

- actual time=0.007..0.009 : 첫번째 레코드를 가져오는 데 걸린 평균 시간(ms), 두번째는 마지막 레코드를 가져오는 데 걸린 평균 시간

- rows 처리한 레코드 건수

- loops 반복 횟수

실행 계획과 더불어 쿼리 시간을 측정하는 경우 주의할 부분이 있다.

MySQL 서버는 운영체제의 파일 시스템 관련 기능(시스템 콜)을 이용해 데이터 파일을 읽어온다. 그런데 일반적으로 대부분의 운영체제는 한 번 읽은 데이터는 운영체제가 관리하는 별도의 캐시 영역에 보관해 뒀다가 다시 해당 데이터가 요청되면 디스크를 읽지 않고 캐시의 내용을 바로 MySQL 서버로 반환한다. InnoDB 스토리지 엔진은 일반적으로 파일 시스템의 캐시나 버퍼를 거치지 않는 Direct I/O를 사용하므로 운영체제의 캐시가 큰 영향을 미치지 않는다.

하지만 MySQL 서버에서도 데이터 파일의 내용을 페이지 단위로 캐시하는 기능을 제공한다. InnoDB 스토리지 엔진이 관리하는 캐시를 버퍼 풀이라고 하는데 이는 인덱스 페이지는 물론이고 데이터 페이지까지 캐시하며, 쓰기 작업을 위한 버퍼링 작업까지 겸해서 처리한다.

MySQL 서버가 한번 시작되면 InnoDB의 버퍼 풀을 강제로 삭제할 수 있는 방법이 없다.

InnoDB 버퍼 풀은 MySQL 서버가 종료될 때 자동으로 덤프됐다가 다시 시작될 때 자동으로 적재된다.

• SET GLObAL innodb_buffer_pool_dump_at_shutdown=OFF
• SET GLObAL innodb_buffer_pool_load_at_startup=OFF

그래서 실제 쿼리의 성능 테스트를 MySQL 서버의 상태가 워밍업된 상태(캐시나 버퍼등의 데이터가 준비된 상태)로 할지 아니면 콜드 상태(캐시나 버퍼가 모두 초기화된 상태)에서 진행할지도 고려해야 한다. 일반적으로 쿼리의 성능 테스트는 워밍업된 상태를 가정하고 테스트한다고 한다.

MySQL의 버퍼 풀은 크기가 제한적이라서 쿼리에서 필요로 하는 데이터나 인덱스 페이지보다 크기가 작으면 플러시 작업과 캐시 작업이 반복해서 발생하므로 쿼리를 6~7번 정도 반복해서 실행한 후, 처음 한두 번의 결과는 버리고 나머지 결과의 평균값을 기준으로 비교하는 것이 좋다.

[참고]

RealMySQL 8.0

SRS(Spatial REference System)은 익숙한 용어로 좌표계(Coordinate System)라고 생각하면 된다.

SRS는 GCS(Geographic Coordinate System)와 PCS(Projected Coordinate System)로 구분이 된다.

GCS는 지구 구체상의 특정 위치나 공간을 표현하는 좌표계로 위도(Latitude)와 경도(Longitude)와 같은 각도 단위로 표시된다. PCS는 지구를 평면으로 투영시킨 좌표계를 말한다.

목적이나 용도별로 사용하는 지리 좌표가 매우 다양해서 동일 지점이라고 해도 어느 공간 좌표계(SRS)를 사용하느냐에 따라 표시 방법이 달라진다.

SRID(Spatial Reference ID, SRS_ID)는 특정 SRS를 지칭하는 고유 번호로 흔히 사용하는 WGS 84 좌표계의 SRID는 4326이다.

MySQL 8.0부터 SRID 지원이 추가되면서 SRID를 별도로 지정하지 않으면 SRID가 0인 평면 좌표계로 인식이 된다. SRID가 0으로 정의된 컬럼에 WGS 84 좌표계를 참조하는 공간 데이터를 저장하려고 하면 에러가 발생한다. 만약 SRID를 명시적으로 정의하지 않으면 해당 컬럼은 모든 SRID를 저장할 수 있지만 SRID가 제각각이면 인덱스 검색이 불가능하기 때문에 명시하는 것이 좋다. 그리고 MySQL 8.0에서 지원되는 공간 함수들이 모든 SRID를 지원하는 것은 아니기 때문에 잘 보고 선택을 해야 한다.

MySQL의 공간 인덱스(Spatial Index)는 R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 인덱스이다.

내부 메커니즘은 B-Tree와 흡사한데 B-Tree는 인덱스를 구성하는 컬럼의 값이 1차원 scalar 값인 반면, R-Tree는 2차원의 공간 개념 값이다.

MySQL의 공간 확장(Spatial Extension)을 이용하면 지도, 위치 기반의 서비스를 구현하는데 큰 도움이 된다.

Spatial Extension 기능

- 공간 데이터를 저장할 수 있는 데이터 타입

- 공간 데이터의 검색을 위한 공간 인덱스(R-Tree)

- 공간 데이터의 연산 함수(거리 또는 포함 관계의 처리)

공간 데이터 타입

- 공간 정보의 저장 및 검색을 위해 여러 가지 기하학적 도형 정보를 관리할 수 있는 데이터 타입으로 Geometry는 Point, Line, Polygon의 부모 타입이다

MBR(Minimum Bounding Rectangle)이란 공간 데이터 타입의 도형을 감싸는 최소 크기의 사각형을 의미한다. 그리고 MBR의 포함 관계를 B-Tree 형태로 구현한 인덱스가 R-Tree 인덱스다.

아래 그림을 보면 공간 데이터 도형을 감싸는 MBR이 있고 최소 MBR 사각형들을 그룹 단위로 감싸는 MBR이 있다. 그리고 이러한 MBR을 감싸는 MBR이 있다. 이렇게 공간 데이터의 영역을 포함 관계로 인덱스를 만들면 일정 반경 내의 검색을 인덱스를 활용하여 효율적으로 할 수 있게 된다.

R-Tree 인덱스를 이용하기 위해서는 ST_Contains(), ST_Within() 등과 같은 포함 관계를 비교하는 함수로 검색을 해야 한다. 예제를 찾아보면 ST_Distance(), ST_Distance_Sphere()를 이용한 경우도 보이는데 이 함수는 공간 인덱스를 사용하지 못한다. 

열심히 썼는데 글이 날아가서 자세한 부분은 문서를 보고 사용하는 것이 좋을 것 같다 ㅠㅠ

ST_Buffer()를 사용하면 특정 위치로부터 반경(m)에 해당하는 점들을 반환하는데 ST_Buffer_Strategy()를 추가할 수 있다. 기본 point 전략이 ST_Buffer_Strategy(point_circle, 32)로 조회를 해보면 33개의 좌표로 이루어진 Polygon 타입을 반환한다.

참고로 WGS 84 좌표계는 POINT(위도, 경도)로 입력을 해야 되는데 SRID 값에 따라 POINT(경도, 위도)인 좌표계도 있어서 스펙을 찾아보고 해야 한다.

ST_Buffer()를 통해 범위를 결정하고 ST_Contains() 같은 MBR을 이용해서 포함 관계를 비교하는 함수를 통해 일정 반경 내의 장소들을 검색할 수 있다. 인덱스를 설정하고 더미 데이터를 10000건 정도 넣어서 확인을 해봤는데 range 접근 방식으로 데이터를 읽는 것을 확인할 수 있다.

ST_Distance_sphere() 함수를 사용했을 때는 풀 테이블 스캔을 하기 때문에 주의해야 한다.

[참고]

Real MySQL 8.0

개념

- 디스크 읽기 방식

- 인덱스란?

- B-Tree 인덱스

- 클러스터링 인덱스

- 유니크 인덱스

- 외래키

디스크 읽기 방식

디스크를 읽는 방식에는 랜덤(Random) I/O, 순차(Sequential) I/O가 있다. 이전에 가상 메모리, 페이징을 공부하면서 디스크 I/O를 줄이는 것이 중요하다는 것을 배웠는데 일반적으로 데이터베이스도 디스크에 데이터를 읽고 저장하기 때문에 디스크 I/O가 성능에 큰 영향을 끼친다.

순차 I/O는 디스크 헤더를 움직이지 않고 한번에 많은 데이터를 읽는 작업으로 비중이 크지 않다.

랜덤 I/O는 부분적으로 작은 데이터를 읽고 쓰는 작업으로 대부분의 작업이 이에 해당한다.

디스크 원판을 가지지 않는 SSD는 HDD에 비해 랜덤 I/O 속도가 훨씬 빠른데 그래도 순차 I/O에 비하면 전체 스루풋(Throughput)이 떨어진다고 한다.

일반적으로 쿼리를 튜닝하는 것은 랜덤 I/O 자체를 줄여주는 것이 목적으로 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.

인덱스란?

인덱스에 대해 찾아보면 대부분 책 뒤에 있는 색인으로 비유를 하는데 "ㄱ", "ㄴ", "ㄷ" 와 같은 순서로 정렬을 해서 원하는 데이터를 빠르게 찾을 수 있도록 도와주는 역할을 한다. 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 읽기 속도를 향상 시키는 것이다.

인덱스는 유니크(Unique)한 인덱스와 유니크 하지 않은 인덱스로 구분할 수 있는데 이는 실제 쿼리를 실행해야 하는 옵티마이저에게 상당히 중요한 문제이다. 유니크 인덱스에 대해 동등 조건('=')으로 검색한다는 것은 항상 1건의 코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 낸다.

B-Tree 인덱스

인덱싱 알고리즘으로 가장 일반적으로 사용되는 B-Tree(Balanced-Tree)는 다음과 같은 구조로 되어 있다.

- 최상위 루트 노드(Root node)

- 중간 자식 노드 (Branch node)

- 최하위 자식 노드 (Leaf node)

데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데 일반적으로 인덱스의 리프 노드는 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.

데이터 파일은 일반적으로 정렬이 되지 않은 상태로 저장이 되는데 DELETE 된 공간이 다시 재활용 되기 때문에 순서가 섞인다.

InnoDB 테이블

- 레코드가 클러스터 되어 디스크에 저장 되기 때문에 기본적으로 프라이머리 키 순서로 정렬이 되어 저장

- 리프 노드가 실제 주솟값이 아닌 프라이머리 키를 논리적인 주소로 사용 (세컨더리 인덱스 검색에서 프라이머리 키를 저장하고 있는 B-Tree를 다시 한번 검색)

인덱스 키를 추가할때 리프 노드가 꽉차면 분리가 되면서 상위 브랜치 노드까지 처리의 범위가 넓어지는데 이러한 이유로 B-Tree는 상대적으로 쓰기 작업에 비용이 많이 든다. (이 비용의 대부분이 메모리와 CPU에서 처리하는 시간이 아니라 디스크로부터 인덱스 페이지를 읽고 쓰기를 해야 해서 걸리는 시간)

아무튼 이런 비용을 감당하면서 인덱스를 사용하는 이유는 빠른 검색을 위해서이다. DB을 잘 사용하려면 얻는 것과 잃는 것에 대해 많은 경험, 고민이 필요한 것 같다.

인덱스를 이용한 검색에서 주의할 점은 인덱스의 키 값에 변형이 가해진 후 비교되는 경우 B-Tree의 빠른 검색 기능을 사용할 수 없다는 것인데 함수나 연산을 수행한 결과로 정렬, 검색을 하는 작업은 B-Tree의 장점을 이용할 수 없으므로 주의해야 한다.

InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 또는 블록이라고 하며 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다. 위의 루트, 브랜치, 리프 노드를 구분한 기준도 페이지 단위이다.

하나의 인덱스 페이지는 4KB ~ 64KB(기본값 16KB)의 크기를 가지는데 인덱스 키 값이 커지면 그만큼 하나의 페이지에 담을 수 있는 키가 줄어들고 페이지 깊이(depth)가 증가하게 된다. 이는 디스크로부터 읽어야 하는 횟수가 늘어나고 그만큼 느려질 수 있다는 것을 의미한다.

인덱스를 통해 테이블의 레코드를 일는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업인데 인덱스를 통해 읽어야 할 레코드의 건수가 많다면(대략 전체 테이블 레코드의 20~25% 기준) 인덱스를 사용하지 않고 테이블을 모두 읽어서 필터링 하는 방식이 효율적이다. (MySQL의 옵티마이저는 이런 경우 예상을 해서 효율적인 방식으로 처리한다)

클러스터링 인덱스

MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한 것(프라이머리 키)들끼리 묶어서 저장하는 형태로 구현되는데 InnoDB는 프라이머리 키를 클러스터링 인덱스로 사용한다. 중요한 것은 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다는 것이다.

프라이머리 키가 세컨더리 인덱스에 미치는 영향

InnoDB 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키(클러스터링 인덱스) 값을 저장하도록 구현이 되어 있다. 

세컨더리 인덱스가 프라이머리 키 값을 포함하고 있기 때문에 프라이머리 키의 크기가 커질 경우 세컨더리 인덱스도 자동으로 크기가 커지게 되서 위에서 말한대로 디스크 I/O가 증가하게 된다. 그래서 프라이머리 키의 크기는 가능하면 작게 사용하는 것이 효율적이다.

하지만 InnoDB의 프라이머리 키는 클러스터링 키로 사용되며 검색에 큰 이점이 있어서 해당 칼럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 프라이머리 키로 설정하는 것도 좋다고 한다.

유니크 인덱스

유니크 인덱스는 고유한 값으로 검색 시 1건만 찾으면 되기 때문에 유니크하지 않은 인덱스에 비해 훨씬 빠를 것 같지만 유니크하지 않은 인덱스의 경우 CPU에서 칼럼값을 비교하는 작업이 추가되는 것이라서 성능상 큰 차이는 없다고 한다.

오히려 유니크 인덱스는 주의해야 될 점이 있는데 유니크 인덱스 쓰기 작업의 경우 별도로 중복된 값이 있는지 없는지 체크를 한다. MySQL에서는 중복된 값을 체크할 때 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 빈번히 발생한다. 그리고 InnoDB 스토리지 엔진은 인덱스 키의 저장을 체인지 버퍼(Change Buffer)를 사용해서 버퍼링을 하기 때문에 인덱스의 쓰기 작업을 빨리 처리할 수 있는데 유니크 인덱스의 경우는 중복 체크를 해야 하기 때문에 작업 자체를 버퍼링하지 못한다. (= 꼭 필요할때 사용)

외래키

InnoDB의 외래키 관리

- 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.

- 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)를 발생시키지 않는다.

자식 테이블의 외래 키 칼럼의 변경은 부모 테이블의 확인이 필요한데, 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때까지 기다리게 된다. 자식 테이블의 외래키가 아닌 칼럼의 변경은 외래키로 인한 잠금 확장이 발생하지 않는다.

데이터베이스에서 외래 키를 물리적으로 생성하려면 이러한 잠금 경합을 고려해 모델링을 해야한다. 물리적으로 외래키를 생성하면 자식 테이블에 레코드가 추가되는 경우 해당 참조키가 부모 테이블에 있는지 확인하는데 이때 연관 테이블에 읽기 잠금을 걸어서 이런 잠금 확장으로 인한 영항을 신경 써야한다.

[참고]

Real MySQL 책

트랜잭션의 격리 수준이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.

격리 수준

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ
SERIALIZABLE

(밑으로 갈수록 격리 수준이 높아지고, 처리 성능이 떨어지지만 SERIALIZABLE이 아니라면 크게 성능 개선, 저하는 발생하지 않는다)

READ UNCOMMITTED는 트랜잭션이 커밋되지 않은 상태에서도 조회가 가능하기 때문에 이상한 값이 읽힐 수 있고 이를 더티 리드라고 하는데 일반적인 데이터베이스에서는 거의 사용하지 않는다.

SERIALIZABLE도 동시성이 중요한 데이터베이스에서 거의 사용되지 않는다.

READ COMMITTED는 오라클에서 기본으로 사용되는 격리 수준으로 가장 많이 선택되는 단계인데 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다. 만약 트랜잭션이 커밋되기 전에 다른 트랜잭션에서 조회를 한다면 언두 로그에 백업(복사)해둔 변경 전 데이터가 반환이 된다. 

READ COMMITTED는 NON-REPEATABLE READ라는 부정합의 문제가 남아있는데 처음 조회를 했을 때는 바뀌지 않은 데이터가 중간에 다른 트랜잭션에서 UPDATE를 하고 커밋을 한 뒤에 다시 조회를 했을 때 바뀐 데이터가 반환되는 것이다.

REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준으로 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 이상의 격리 수준을 사용해야 한다.

InnoDB 스토리지 엔진은 트랜잭션이 롤백될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경하는데 이러한 변경 방식을 MVCC(Multi Version Concurrency Control)라고 한다.

[MVCC와 언두 로그]

REPEATABLE READ는 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.

READ COMMITTED도 MVCC를 이용해 커밋되기 전의 데이터를 보여주는데 둘의 차이는 언두 영역에 백업된 레코드의 여러 개의 멀티 버전 가운데 몇번째 이전 버전까지 찾아 들어가느냐에 있다.

모든 InnoDB 트랜잭션은 순차적으로 증가하는 고유한 트랜잭션 번호를 가지며, 언두 영역에 백업된 모든 레코드에는 트랜잭션의 번호가 기록되어 있다. (언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요해지면 주기적으로 삭제)

REPEATABLE READ은 MVCC를 보장하기 위해 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수 없다. 정확히는 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존돼야 한다.

userB(tx_id: 10)이 트랜잭션을 시작하고 다음 트랜잭션인 userA(tx_id: 12)가 시작해서 값을 바꿔도 userB는 언두 로그에 백업해둔 자기보다 낮은 tx_id의 데이터가 보인다. 하지만 REPEATABLE READ도 SELECT ... FOR UPDATE로 조회를 할 경우 현재 레코드의 값을 가져오는 부정합의 문제가 있긴 하다. 이렇게 다른 트랜잭션에서 변경을 해서 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ라고 한다.