MySQL InnoDB의 동작 - 실제 쿼리 상황에서 InnoDB는 어떻게 동작할까? Lock과 Index

RainMind 프로젝트를 수행하면서, 데이터 일관성을 맞추기 위해 이런 쿼리를 사용했었다:

@Query(value = """
  INSERT INTO schedules (user_id, title, location_id, start_at, end_at) 
  SELECT :userId, :title, :locationId, :start_at, :end_at
  WHERE (SELECT COUNT(*) FROM schedules WHERE user_id = :userId) <= 30
""")  
-- user_id : users 테이블로부터 빌려온 foreign key
-- location_id : location 테이블로부터 빌려온 foreign key

WHERE 절 내부의 SELECT와 INSERT 2개의 구문으로 이뤄진 복합 쿼리이므로 데이터 정합성이 당연히 깨질 것(원래 의도: 유저 1명당 schedule 31개 이하로만 만들도록)이라고 생각했다.

근데 아무리 VU, DB 커넥션 풀, 유저당 schedule 상한 등 조정할 수 있는 모든 변수를 조정해서 k6 스크립트를 돌려도 정합성이 깨지는 현상은 존재하지 않았다.

이에 따라, 내가 사용하는 DB인 MySQL 기준으로 이제까지는 무의식적으로 작성했던 쿼리에 대해, 실제 쿼리를 날렸을 때 어떠한 동작이 수행되는지 자세히 알고 싶어졌다. 또 내가 무심코 쓰고 있는 쿼리가 어떤 성능을 미칠지 궁금해졌다.

MySQL 기본 트랜잭션 격리 수준은 RR(Repeatable Read)이며, 이를 기준으로 정리해보고 격리 수준을 조정하면 어떠한 일이 발생할지도 적어보고자 한다.

1. Lock

MVCC(Multi Version Concurrency Control)

Lock을 사용하지 않고 스냅샷을 이용하여 읽기 연산의 정합성을 제공하는 기법이다.

단순 읽기 작업을 하기 위해 S-Lock을 거는 것은 성능상 비효율적이므로, MVCC 기술을 이용해 잠금 없는 일관된 읽기를 지원한다.

다만 트랜잭션 격리 수준에 따라 다른데,

• READ UNCOMMITTED = Dirty Read 가능
• READ COMMITTED / REPEATABLE READ = 필요시 Undo Log를 이용하여 이전 버전의 데이터를 읽는다.

READ COMMITTED 이상의 격리 수준에 대해, 일반 SELECT시 수행되는 과정은 아래와 같다.

(1) Clustered Index의 record마다 DB_TRX_ID(마지막으로 이 record를 변경한 트랜잭션 id)와 DB_ROLL_PTR(이전 버전의 데이터를 담은 undo log record를 가리키는 포인터)가 존재한다.

(2) SELECT 쿼리를 실행한다. 이때 읽으려는 데이터가 InnoDB 버퍼풀에 없으면 디스크에서 가져온다.

(3) 해당 쿼리를 실행하는 트랜잭션의 Read View(현재 트랜잭션 기준으로, 어떤 트랜잭션의 변경이 보여야 하는지를 담은 정보)를 사용한다. 여기서 읽으려는 record가 Read View에

• (A) 보이면 : 해당 record를 InnoDB 버퍼풀에서 읽는다.
• (B) 보이지 않으면 : DB_ROLL_PTR을 통해, Read View에 보일때까지 Undo Log를 따라 변경사항을 추적하여 이전 버전을 읽어온다.

다른 트랜잭션이 record를 변경 후 commit하지 않았더라도 이 변경사항은 또 다른 트랜잭션들의 SELECT에 영향을 주지 않는다.

이를 잠금 없는 일관된 읽기(Non-Locking Consistent Read)라고 하며, MySQL은 MVCC와 Undo Log를 통해 구현한다.

따라서 단순 SELECT 문의 경우, Lock 없이 snapshot 만으로 결과를 반환한다. 따라서 처음 제시했던 쿼리도 데이터가 없을 경우, WHERE 내부의 SELECT 구문이 어떠한 Lock도 잡지 않아 동시성 이슈를 발생시킬 것이라고 생각했다. 그러나 그렇지 않았다.

그 이유를 이해하기 위해서는 Gap Lock에 대해서 먼저 이해해야 한다.

Gap Lock

MySQL은 머리를 굴려서 RR을 유지하는 선에서 phantom read를 막고자 노력했다.

특정 record를 읽거나 쓸때 해당 record에 락을 거는 것은 당연하다.

하지만 이것들”만” 있다면, phantom read를 막을 수 없다. 예를 들어, id = 10, 20인 record가 있는데 여기서 다음 순서로 쿼리를 날렸다고 해보자.

(가정: Record Lock만 존재한다)
1) (트랜잭션 A) SELECT …. WHERE id > 8; 2) (트랜잭션 B) INSERT INTO … (id) VALUES (12); 3) (트랜잭션 A) SELECT …. WHERE id > 8;

1번에서는 2개가 조회되고, 3번에서는 3개가 조회된다. id = 10, 20인 record”만” 잠그기 때문에, 같은 트랜잭션(A) 안에서 같은 쿼리의 조회 결과가 다르다. 이것이 phantom read이다.

그래서 MySQL은 그냥 조회하는 레코드랑, 그 사이도 싹 다 막아버리자라는 선택을 하게 된다. 이게 Gap Lock이다.

(Gap Lock 다시 정의)
Record들 사이의 빈 공간에 걸어버리는 잠금이다.

형식적으로는 S,GAP LOCK과 X,GAP LOCK으로 나뉘긴 하지만, 탄생 목적 자체가 다른 트랜잭션이 이 범위의 데이터를 수정하지 못하게하는 것이므로 특이하게 중첩이 된다. 즉 충돌이 없다.

그래서 그냥 Gap Lock이라는 하나의 종류로 보아도 무방할 듯 하다.

예를 들어 보자. 방금의 그 쿼리를 다시 가져왔다.

@Query(value = """
  INSERT INTO schedules (user_id, title, location_id, start_at, end_at) 
  SELECT :userId, :title, :locationId, :start_at, :end_at
  WHERE (SELECT COUNT(*) FROM schedules WHERE user_id = :userId) <= 30
""")

해당 쿼리를 실제 실행해보면, 내부의 SELECT 문에 의해 schedules 테이블에 S,GAP이 걸리는 것을 확인할 수 있다.

(실제 Lock 확인 명령어와 실행 결과는 이 링크 참조 -> 링크 1st <-)

그러나 위 쿼리의 실행 결과를 보면(위 링크의 명령어 실행 결과 참조), 뭔가 이상한 점이 2가지가 있다.

• (1) 지금 건드리는건 schedules 테이블뿐인데, 왜 users / location 테이블의 record까지 S-Lock이 걸리는가?
• (2) MySQL의 기본 동작에 따라, SELECT문은 MVCC를 사용하여 lock없이 읽기를 수행할 것인데 왜 Gap Lock이 걸리는가?

1번 질문은 참조 무결성 제약 조건, IS Lock, IX Lock과 관련된 내용이며, 2번 질문은 Gap Lock의 의도와 관련된 질문이다. 지금 Gap Lock과 관련있는 2번 질문부터 먼저 대답하도록 하자.

• 2번 질문에 대한 해답 : 이 쿼리는 일반 SELECT 구문이 아닌, INSERT … SELECT 복합 구문이므로 잠금 없는 일관된 읽기가 아닌, Next-key Lock이 사용된다.

일반적으로 이를 또다른 이름으로 부르는데, 이를 Next-key Lock이라고 한다.

Next-key Lock

Gap Lock과 Record Lock을 합쳐놓은 형태의 lock이며, 해당 record들과 조건(WHERE절)에 맞는 record 들의 사이를 전부 잠그는데 사용하는 lock이다.

레코드 하나를 조회/수정하는 작업이 엄청난 성능 저하를 일으킬 수도 있다는 느낌이 든다. 조건에 맞는 레코드들과 그 사이를 전부 잠궈버리기 때문이다.

그러면 또 이런 의문이 든다.

• Q. 트랜잭션 처리 속도 저하와 데드락까지 일으킬 수도 있다는 생각이 든다. 그러나 왜 이걸 쓸까? 그냥 격리 수준을 낮추어 Record Lock만 쓰면 안되는걸까?

앞서 조건에 맞는 record들 사이를 전부 잠그는 것이 Gap Lock이라고 했었다. 그렇다면, 애초부터 WHERE 절로 검색하는 조건이 unique함을 보장한다면, 즉 Unique Key(또는 PK) Index를 unique search 조건으로 정확히 검색하는 경우에는?

정답은 Gap Lock 없이 Record Lock만 사용한다.

예시로 아래 쿼리를 보자. 실제 SNUXI 팀프로젝트를 하며 사용했던 쿼리이다.

UPDATE Pots p
SET p.status = CASE 
    WHEN p.currentCount + 1 >= p.minCapacity THEN :successStatus 
    ELSE p.status
  END,
  p.currentCount = p.currentCount + 1
WHERE p.id = :potId
AND p.currentCount < p.maxCapacity

-- Pots : id를 PK(Primary Key)로 하여, 현재 채팅방의 번호와 최소/최대 수용인원, 채팅방의 모집 상태를 저장하는 테이블

WHERE 절의 검색 조건이 Pots 테이블의 Primary Key이다. 즉, 저 WHERE 절의 조건에 부합하는 record는 존재한다면 단 1개이며, PK 특성상 MySQL 수준에서 unique함이 보장된다.

따라서, MySQL은 실제로 Gap Lock을 걸지 않고 Record Lock만 걸게 된다.
(실제 Lock 확인 명령어와 실행 결과는 이 링크 참조 -> 링크 2nd <-)

이제 모든 퍼즐이 맞춰진다.
요약하면,

1) 데이터의 단순 조회만 수행하는 경우, MVCC를 이용하여 잠금 없는 읽기를 지원한다.
2) 그외에, UPDATE/DELETE 또는 위의 쿼리처럼 잠금 읽기가 수행되는 경우에는 어떤 index를 어떻게 스캔했는지에 따라 lock 범위가 달라진다.
3) Unique Index(or PK)를 unique하게 검색되도록 조건을 주는 경우에는 Record Lock만 걸리며, unique 하더라도 range scan을 하거나 / unique하지 않으면 next-key lock이 사용된다.
(RR 격리수준 기준에서는 그렇고, RC 격리수준에서는 FK 체크 & 중복 key 체크 외에는 Gap Lock을 사용하지 않는다.)

즉, 맨 처음의 INSERT … SELECT 복합 구문도, unique search 조건이 아니므로, user_id FK 인덱스를 기준으로 하여 해당하는 record 및 gap들에 Next-Key Lock이 걸린다는 것이다.
(오해하면 안되는게, FK 여부로 결정되는게 아니라 핵심은 non-unique하게 검색될 수 있느냐의 여부가 결정한다)

여기서 알 수 있는건 또 하나, 저 쿼리는 서로 다른 유저가 저 쿼리를 동시에 실행할 경우, 모든 record가 아닌 입력받은 user_id에 해당하는 record및 그 앞뒤 gap들에 대해서만 lock이 걸리므로, 서로 다른 유저의 요청은 동시에 처리 가능하다(동시 처리에 유리하다).

그래서 이전 게시물들에서 분석한 성능 측정 결과에서 API 응답 속도가 획기적으로 단축되었던 것이다(RainMind 카테고리의 성능 측정 게시물 참고하면 k6 스크립트/표/그래프 등 확인 가능).

추가 질문

• 만약, user_id에 인덱스가 없었다면? 조회하는 모든 레코드들에 대해 Next-Key Lock이 걸려 성능이 심히 저하되었을 것이다. 이래서 인덱스가 중요하다. 인덱스 관련은 Lock 정리가 끝난 후 아래에서 더 자세히 다뤄보자.

• 동일 user_id에 대한 동시 요청이 매우 고강도로 들어오면 어떻게 되는가? lock 선점을 위한 대기 및 deadlock 가능성이 급격히 커질 수 있다. 해당 쿼리를 사용하려면 try-catch 또는 재시도 로직 등을 도입할 필요가 있을 것으로 예상된다.

이제 이전의 2번 물음에 대한 답이 충분히 된 것 같다.

그러나 우리에게는 1번 물음에 대한 답을 해결해야 하는 과제가 남아있다. 1번 물음을 다시 가져와보자.

• (1) 지금 건드리는건 schedules 테이블뿐인데, 왜 users / location 테이블의 record까지 S-Lock이 걸리는가?

이는 Database의 참조 무결성 제약 조건과 관련이 있다. 아래에서 좀 더 자세히 다뤄보자.

참조 무결성 제약 조건(Referential Integrity Constraint)

참조 관계의 record들 사이의 일관성을 유지해야 한다는 조건이다.

대표적으로 foreign key가 있겠다. 참조 관계이므로, 상식적으로 생각해보면 참조하는 record와 참조되는 record의 정합성이 보장되어야 하겠다.

MySQL에서는 참조되는 부모 테이블의 record에 S-Lock을 걸어 부모 record의 수정을 방지하여 이러한 정합성을 보장한다.

그래서 링크 1st 에 다시 들어가보면, 참조되는 부모 테이블인 users / location 테이블에 S,REC_NOT_GAP을 걸어 보호함을 알 수 있다.

이제 프로젝트 수행하며 실제로 확인했던 Lock을 거의 다 정리한 듯 하다. 마지막으로 정리할 내용은, 링크 2nd에 있었던 IX라는 정체모를 Lock이다.

Intention eXclusive Lock(IX Lock)

특정 테이블의 row에 X-Lock을 걸 것임을 나타내는 테이블 수준의 intention lock이다.

ALTER 명령어와 같은, 테이블의 구조를 바꾸는 즉 테이블 전체 잠금을 요구하는 상황에서 테이블의 모든 record를 전수조사하지 않고도 Lock이 걸려있는지 확인할 수 있다.

Intention Shared Lock(IS Lock)

특정 테이블의 row에 S-Lock을 걸 것임을 나타내는 테이블 수준의 intention lock이다.

IS Lock 또한 IX Lock과 마찬가지로, record 전수조사를 하지 않고도 Lock이 걸려있는지 확인할 수 있다.

이제 정말 마지막으로 정리해보자. 맨 처음 제시했던 쿼리를 실행하면 대략 아래와 같은 Lock들이 관찰될 수 있다.

• IS Lock : WHERE 내부의 SELECT 구문이 실행되며, schedules 테이블에 IS Lock이 걸린다.
• Next-key Lock : user_id = :userId 조건에 맞는 record 및 gap에 Next-key Lock이 걸린다.
• S-Lock : 부모 테이블인 users, location의 record에 참조 무결성을 위해 S-Lock이 걸린다.
• IX Lock : schedules 테이블에 IX Lock이 걸린다.
• Insert Intention Lock : INSERT 구문이 들어갈 Gap에 Insert Intention Lock을 잡는다.
• X-Lock : 데이터가 들어갈 자리에 X-Lock을 잡고, commit 직후까지 lock이 유지된다.

이제 프로젝트 수행하면서 사용했던, 그리고 공부했던 Lock의 종류는 전부 정리한 듯 하다. 이제 Index로 넘어가서, 앞선 lock 매커니즘이 Index와 어떤 상호작용을 하는지 보도록 하자.

2. Index

앞선 쿼리를 분석하면서, 인덱스 쓰면 단순히 성능 좋아져요~ 수준으로 이해하면 안된다는 것을 알게 되었다. 본인이 작성한 쿼리와, 본인이 제공하는 서비스의 성격과 API 우선순위에 따라 인덱스 도입 유무는 달라질 수 있다.

우선 인덱스가 어떤 방식으로 동작하는지 보자.

일단, InnoDB에서 테이블은 Clustered Index 그 자체이다. 그 외에 우리가 만드는 모든 Index는 Secondary Index이다.

Clustered Index

테이블 당 단 하나만 존재하는 Index로, Primary Key가 곧 이것이 된다.
실제 record들은 Clustered Index의 B+Tree의 Leaf에 저장된다.

즉 PK를 무엇으로 정하느냐에 따라 record의 배치 순서가 달라질 수 있다.

일반적으로 MySQL에서는 id BIGINT AUTO_INCREMENT PRIMARY KEY와 같은 방식으로 PK를 지정하고는 한다. 그러나 PK가 없는 테이블의 경우에는 어떻게 될까? 답은 일정한 규칙에 따라 PK를 대체할 column을 선정한다.

(일정한 규칙)

• (1) Unique하며 NOT NULL인 column 중 첫번째를 선택한다
• (2) (1)에 해당하는 column이 없으면, 아무 의미없는 숫자를 내부적으로 생성하여 사용한다.

Secondary Index

Leaf에 PK와 해당 Index column들의 값을 저장하는 B+Tree Index이다.

그러므로, PK의 물리적 크기가 커지면(자료형 변화 등) 모든 Secondary Index의 Leaf 각각의 물리적 크기가 커진다. 이는 동일한 Page size에 들어갈 수 있는 record 수를 줄여, 더 많은 page를 읽을 가능성이 커지고, Disk I/O 비용 증가와 메모리 버퍼풀 효율 감소 가능성으로 이어진다. 이는 본인이 지정한 Index column의 크기에서도 마찬가지다.

만약 Secondary Index를 통해 검색을 수행하게 되면, PK를 확인하고 -> Clustered Index를 검색해서 최종 record를 가져오는 작업을 거친다.

그러나 가져와야 하는 column이 이미 Secondary Index의 Leaf에 모두 존재하는 경우에는 추가적인 Clustered Index 검색 작업이 필요없다. 이를 Covering Index(커버링 인덱스)라 한다.

즉 Secondary Index도 아무 생각없이 WHERE 구문에 적힌대로 다 만들면 안된다. 단순 Clustered / Secondary 차이로 인한 Disk I/O 뿐만 아니라, Index는 쿼리의 Lock 범위와 직결되는 요소이기 때문에 Index 설계는 성능과 메모리 버퍼풀 효율 측면에서 매우 중요하다.

Index와 Lock 범위

쿼리를 실행할때, WHERE 구문의 탐색 조건에 해당하는 레코드를 잠그는게 아니라 어떤 인덱스를 어떤 범위로 스캔했는지에 따라 lock의 범위가 달라진다.

아까 소개했던, 실제 프로젝트에 사용했던 쿼리들을 다시 가져와서 간략하게 리마인드해보자.

@Query(value = """
  INSERT INTO schedules (user_id, title, location_id, start_at, end_at) 
  SELECT :userId, :title, :locationId, :start_at, :end_at
  WHERE (SELECT COUNT(*) FROM schedules WHERE user_id = :userId) <= 30
""")  
-- user_id : users 테이블로부터 빌려온 foreign key
-- location_id : location 테이블로부터 빌려온 foreign key

이 쿼리는, user_id가 FK이므로 FK Index에서 user_id = :userId인 record들과 그 사이 gap만 Next-key Lock으로 잠긴다.

이전에 이야기했던 대로, user_id가 FK가 아니면 Index 없이 Full Scan을 수행하게 될 것이고 그렇게 되면 거의 모든 record들에 Next-key Lock이 걸리게 된다.
(오해하면 안되는 것이, FK가 없어서 -> full scan이 아니라, 탐색 조건에 맞는 index가 없거나 부적절하면 full scan이 일어나 테이블 전체가 잠길 수 있다는 것이다)

UPDATE Pots p
SET p.status = CASE 
    WHEN p.currentCount + 1 >= p.minCapacity THEN :successStatus 
    ELSE p.status
  END,
  p.currentCount = p.currentCount + 1
WHERE p.id = :potId
AND p.currentCount < p.maxCapacity

-- Pots : id를 PK(Primary Key)로 하여, 현재 채팅방의 번호와 최소/최대 수용인원, 채팅방의 모집 상태를 저장하는 테이블

이 쿼리는, id가 PK이므로 탐색되는 record가 Unique함이 보장되어있다. 따라서, Clustered Index를 기준으로 탐색하고 Next-key Lock이 아닌 Record Lock(X-Lock)만 걸리게 된다.

Index 쓰기 비용

이때까지 어떤 Index를 사용하느냐에 따라 Lock의 범위가 달라질 수 있음을 확인했고, 이는 성능에 매우 중요한 영향을 미친다.

그러나 성능에 영향을 미치는 요소는 Lock 범위 뿐만이 아니다. 동일한 정보를 여러개의 B+Tree(여러개의 Secondary Index)에 저장하므로, 특정 정보가 수정/삭제/삽입되면 그 정보를 가지고 있는 Secondary Index에도 이를 반영해줘야 한다. 이에 따라 추가 쓰기 비용이 발생한다.

우선 핵심: 일반적으로 ‘테이블’이라고 부르는 것은 ‘Clustered Index’이고, Secondary Index는 별도의 논리적 B+Tree를 유지하는 것이다.

그러므로, SELECT의 경우에는 앞서 설명한대로 Secondary Index만 사용하던지 or Clustered Index까지 추가로 탐색하던지 둘 중 하나를 사용하게 되고, 잠금 없는 일관된 읽기를 통해 record를 가져온다.

그러나 INSERT, UPDATE, DELETE의 경우에는 Secondary Index도 추가로 수정해주어야 한다.

그런데 여기서 Secondary Index가 여러개 있다면, 각 Secondary Index 페이지를 업데이트하기 위해 Disk Random I/O가 발생한다. 따라서, 매번 Disk 접근이 이루어진다면 DB의 쿼리 처리 속도가 매우 저하될 것이다(거북이가 된다).

따라서 MySQL은 Change Buffer를 통해 이를 완화한다.

Change Buffer

Unique하지 않은 Secondary Index의 변경사항에 한하여, 이를 기록해두는 디스크 및 메모리 영역 내의 Buffer이다(change_buffering 시스템 변수 설정에 따라 활성/비활성화 가능).

왜 Unique하지 않은 Secondary Index로 범위를 제한할까? PK, Unique Key 처럼 반드시 중복 여부를 확인해야 하는 경우, 관련 Index 페이지를 실제로 확인해 쿼리 유효성(중복)을 확인해야 하므로, non-unique secondary index처럼 변경을 뒤로 미루기가 어렵다.

그러므로, 아래에서 정리한 내용들은 모두 Non-Unique Secondary Index 기준으로 작성한다.

메모리 버퍼풀에 내가 수정하고자 하는 Index 페이지가

• 있으면 : 메모리 버퍼풀의 내용을 갱신한다
• 없으면 : Change Buffer에 변경 사항을 기록하고, success 처리된다. 이후 Change Buffer 내의 변경사항은 해당 Index 페이지가 나중에 메모리 버퍼풀로 읽혀 들어올때 병합되거나 / 시스템이 한가할 때 반영된다.

그러면 이러한 질문을 해볼 수 있다.

• Q : Change Buffer의 내용들은 그럼 메모리 날아가면 없던 걸로 되나요?
• A : 아니요. Change Buffer 관련 변경사항은 Redo Log 기록으로 보호되며, 디스크의 system tablespace에도 Change Buffer 내용이 저장될 수 있습니다. 따라서 서버 재시작 시에도 메모리의 Change Buffer 내용이 복구될 수 있습니다.

쿼리별 Index 쓰기 작업 상세

이제 Change Buffer까지 정리했으니, 본격적으로 나머지인 INSERT / DELETE / UPDATE 쿼리의 동작을 살펴보자.

1) INSERT 쿼리

수행 과정

• Undo Log에 rollback을 위한 undo 정보를 남긴다.

• Clustered Index를 먼저 반영한다. 삽입할 위치에 대해 Insert Intention Lock (IX도 물론)을 획득하고, 쿼리 유효성을 파악 후 메모리 버퍼풀에 반영, Redo Log에 기록한다.

• Secondary Index를 수정하는데, 만약 해당 인덱스가 non-unique secondary index이고 change buffer 활성화 되어있을때 : 메모리 버퍼풀에 있으면 바로 수정 + Redo Log 기록하고 / 없으면 Change Buffer에 변경사항을 기록 + Redo Log 기록한다.
  (이는 flush_log_at_trx_commit 시스템 변수의 값에 따라 동작이 달라질 수 있다. 지금은 기본값(=1) 기준으로 서술한다)

• 이후 트랜잭션이 commit 될때, Redo Log Buffer에 기록한 내용을 Disk의 Redo Log에 Sequential Disk I/O를 통해 기록한 후 success 응답을 반환한다.

여기서 또 질문을 해볼 수 있다.

• Q : INSERT가 엄청 많이 일어나면요?
• A : Page Split이 일어나서 성능이 박살날 수 있습니다.

Page Split

메모리 버퍼풀의 페이지 내부에 새로운 Data를 넣을 공간이 없을 때, 기존 페이지를 쪼개서 새로운 공간을 확보하는 과정이다.

메모리 안에서 일어나는 과정이기도 하고, 단순히 페이지를 쪼갠다고 생각할 수 있다. 그러나 그렇지 않다. Page Split이 발생하면, CPU가 메모리 내부에서 Data를 재배치하고 / 재배치 변경사항은 모조리 Redo Log에 작성되고 / 나중에 Disk에 반영해야할 Dirty Page의 수가 늘어나므로 절대 무시해서는 안된다.

특히 Clustered Index는 Change Buffer 없이 즉시 반영되어야 하므로, Page Split을 반드시 신경써야한다.

그래서 웬만하면 PK는 AUTO_INCREMENT를 쓰라는 것이다. AUTO_INCREMENT는 페이지가 꽉 차면 그냥 페이지 수를 늘여 데이터를 순차적으로 연결하면 되므로 기존 페이지들의 Split이 거의 발생하지 않고, 데이터 접근의 Locality를 적극 활용할 수 있다.

그러나 PK를 AUTO_INCREMENT가 아닌 UUID 등 비순차적 값을 사용하면, 새로운 데이터가 기존 페이지들의 중간중간에 무작위로 끼어들게 되며, 빈번한 Page Split이 발생하고 이는 Locality를 전혀 활용하지 못해 지속적인 Disk I/O를 일으킬 뿐 아니라, 메모리 버퍼풀 공간의 fragmentation(단편화)를 유도하여 메모리 버퍼풀 효율을 저하시킨다.

2) DELETE 쿼리
Secondary Index에서, DELETE는 실제 물리 레코드를 삭제하지 않고, 삭제 마킹만 하고 이후 해당 record를 참조하지 않게될 경우 백그라운드에서 조용히 삭제되거나, 해당 공간이 새로운 데이터로 덮어씌워진다.

이에 대한 이유는 이때까지의 지식을 토대로, 아래와 같이 생각해볼 수 있다.

• 성능 : 즉시 record를 삭제하고 B+Tree를 재구성해야 하므로 Random Disk I/O가 발생할 수 있다.

• 정합성(MVCC 지원) : 즉시 record를 삭제해버리면, 해당 데이터를 참조하려 하는 다른 트랜잭션이 볼 수 없게 된다.

그럼 이러한 이해를 바탕으로, DELETE 쿼리의 실행 과정을 분석해보자.

수행 과정

• Undo Log에 rollback을 위한 undo 정보를 남긴다.

• Clustered Index를 먼저 반영한다. Next-key Lock 혹은 Record Lock(X-Lock)을 획득한 후(IX도 물론), record를 “삭제됨” 마킹만 수행한다.

• Redo Log에 “삭제 마킹함” 정보를 기록한다.

• Secondary Index에 대해 반영한다. 만약 해당 인덱스가 non-unique secondary index이고 change buffer 활성화 되어있을때 : 메모리에 페이지가 있으면 = 즉시 삭제 마킹 & Redo Log 기록 / 없으면 = Change Buffer에 삭제 마킹 기록 & Redo Log 기록

• 이후 트랜잭션이 commit 될때, Redo Log Buffer에 기록한 내용을 Disk의 Redo Log에 Sequential Disk I/O를 통해 기록한 후 success 응답을 반환한다.

• 추후 백그라운드 스레드가, 해당 record를 참조하는 트랜잭션이 없는 경우에 진짜로 물리적으로 제거한다.

3) UPDATE 쿼리

수행 과정

• Undo Log에 rollback을 위한 undo 정보를 남긴다.

• Clustered Index를 먼저 반영한다. Next-key Lock 혹은 Record Lock(X-Lock)을 획득한 후(IX도 물론), 아래 두 가지 경우에 따라 동작이 달라진다.

• PK가 수정되거나, 변경한 데이터의 크기가 기존 공간에 수용이 불가능할때 : 기존 record 삭제 마킹 + 새 값 삽입 + Redo Log 기록 / 그렇지 않을 경우 : 기존 record 값을 덮어씀 + Redo Log 기록.

• Secondary Index에 대해 반영한다. 만약 해당 인덱스가 non-unique secondary index이고 change buffer 활성화 되어있을때 : 메모리에 페이지가 있으면 = 기존 값 삭제 마킹 + 새 값 삽입 + Redo Log 기록 / 메모리에 페이지가 없으면 = Change Buffer에 삭제 마킹 + 새 값 삽입 요청 + Redo Log 기록

• 이후 트랜잭션이 commit 될때, Redo Log Buffer에 기록한 내용을 Disk의 Redo Log에 Sequential Disk I/O를 통해 기록한 후 success 응답을 반환한다.

• 추후 백그라운드 스레드가, 해당 record(delete 마킹된)를 참조하는 트랜잭션이 없는 경우에 진짜로 물리적으로 제거한다.

##

이때까지 쿼리 하나의 동작 의문을 해소하기 위해, MySQL의 Next-key Lock / IS, IX 및 그와 관련된 Index의 종류와 Lock의 범위, Redo/Undo Log를 포함하여 각 쿼리별 InnoDB의 동작을 상세하게 살펴보았다.

이 모든 내용을 학습 직후 단번에 100% 바로 적용하기에는 쉽지 않을 것이다. 그러나, 무의식적으로 findBy… findAllBy… countBy…and…와 같은 쿼리들을 사용”만” 한다면 서비스 장애를 마주치거나, 혹은 장애를 예방하는건 거의 불가능할 것이다.

본인도 이러한 내용들을 최대한 공부하며 프로젝트에 적용하고, 성능 테스트 및 락 범위 확인을 통해 로직을 최적화하려는 노력을 하고 있는 만큼(성능 최적화 게시물들 참조), 앞으로 단순히 기능을 가져다 쓰는 것보다 이런 내부 동작을 정확히 이해하고 분석하는 자세가 중요하다는 것을 깨닫는다.

다음 게시물에서는, Redo / Undo Log와 MySQL은 Durability를 어떻게 보장하는지에 대해 공부하고 정리해보도록 하겠다.