전체 구조

• MySQL 서버는 사람의 머리 역할을 하는 MySQL 엔진과 손발 역할을 담당하는 스토리지 엔진으로 구분한다. 스토리지 엔진은 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고받는다.

1. MySQL 엔진

1) 커넥션 핸들러 (Connection Handler)

• 클라이언트(JDBC, ODBC, ...)와 MySQL 서버 간 연결을 관리하는 MySQL 내부의 컴포넌트이다. 쉽게 말해 SQL 쿼리를 수신하고, 이를 MySQL 엔진에 전달하며 결과를 클라이언트로 반환한다.

2) SQL 파서 (Parser)

• 사용자 요청으로 들어온 쿼리 문장을 최소 단위로 분리(토큰)해 트리 형태의 구조로 만들어내는 작업을 한다.
• 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달한다.

3) 전처리기 (PreProcessor)

• 파서 트리를 기반으로 쿼리 문장에 구조적인 문제가 있는지 확인한다. 토큰을 테이블, 칼럼, 내장 함수와 같은 객체로 매핑하여 해당 객체의 존재 여부와 접근 권환 등을 확인한다.

4) 옵티마이저 (Optimizer)

• SQL 쿼리 요청을 가장 효율적으로 처리하기 위해 쿼리 변환, 비용 최적화, 실행 계획 수립한다. 각각을 간단히 살펴보면,
  1. 쿼리 변환: 쿼리의 구조를 변경하여 더 나은 성능을 제공할 수도 있다.
  2. 비용 최적화: 여러 실행 계획을 시뮬레이션하고, 각 실행 계획에 대한 비용을 계산한다. 비용은 대체로 처리할 데이터의 양, 필요한 I/O, CPU 사용량 등을 기준으로 평가하며 가장 비용이 적은 실행 계획을 선택한다.
  3. 실행 계획 수립: 사용자가 작성한 SQL 쿼리를 분석한 후, 이를 가장 효율적으로 실행할 방법을 결정한다. 인덱스 선택, 조인 순서 결정, 필터 조건 적용 순서 등을 포함한다.

5) 실행 엔진 (Query Execution Engine)

• 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 임무를 수행한다.
• 옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유할 수 있다. 옵티마이저가 group by를 사용하기로 했다면,
  1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
  2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
  3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
  4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어오라고 핸들러에게 요청
  5. 최종적으로 실행 엔진은 최종 결과를 사용자나 다른 모듈로 넘긴다.

위 쿼리가 실행되는 과정을 그림으로 나타내면 아래와 같다.

Handler API

• MySQL과 스토리지 엔진 간의 인터페이스 역할로, 실행 엔진(쿼리 실행기) 요청을 스토리지 엔진으로 전달한다. 아래에서 Handler API가 어떤 식으로 동작하는지 파악할 수 있다. https://github.com/mysql/mysql-server/blob/trunk/sql/sql_handler.cc

2. 스토리지 엔진(InnoDB)

• MyISAM 등 다양한 스토리지 엔진이 존재하지만, 거의 유일하게 레코드 기반 잠금을 제공하는 InnoDB 스토리지 엔진에 대해서 알아본다.
• InnoDB 모든 테이블은 기본적으로 기본 키(Primary Key)를 기준으로 클러스터링 되어 저장된다. 즉, 기본 키값 순서대로 디스크에 저장되며 세컨더리 인덱스는 레코드의 주소 대신 기본 키의 값을 논리적인 주소로 사용한다.
  

1) MVCC(Multi Version Concurrency Control) 지원

• MVCC는 하나의 레코드에 대해 여러 개의 버전을 동시에 관리하여 잠금을 사용하지 않고 일관된 읽기를 제공한다. InnoDB는 언두로그를 이용해 이 기능을 구현하고 있다.
  

{12, 홍길동, 서울} 에서 '서울'을 '경기'로 변경해 보자. InnoDB 버퍼 풀은 새로운 값인 '경기'로 즉시 업데이트된다. 언두 로그에는 변경 전 값인 '서울'만 복사하여 저장한다. 이때, 해당 컬럼을 조회했을 때, 어떻게 보일까?

답은 트랜잭션 격리 수준(Transaction Isolation Level)에 따라 다르다. 격리 수준이 READ_UNCOMMITED 라면 InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다. 격리 수준이 READ_COMMITED 이상이라면 아직 커밋되지 않았기에 언두 로그에 있는 변경 전의 데이터인 '서울'을 반환하는 것이다.

• 이처럼 InnoDB 버퍼 풀 또는 언두 로그 데이터를 반환하는 식으로 여러 개의 버전이 존재하므로 이를 MVCC라고 한다.
• 변경되기 전의 데이터는 언두 로그 영역에서, 변경 후의 데이터는 InnoDB에서 읽어오면 되기에 잠금 없는 읽기가 가능한 것이다.

2) InnoDB 버퍼 풀

• 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간이다. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다. 버퍼는 변경 작업을 모아서 처리하여 랜덤 디스크 접근을 최소화하는 역할을 한다고 보면 된다.
• InnoDB 버퍼 풀 메모리 공간은 전체 메모리에서 50%에서 시작하여 조금씩 올려가며 최적점을 찾는 것을 권장한다. (innodb_buffer_pool_size)

InnoDB 버퍼 풀 구조

• InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기 조각으로 쪼개어 데이터를 각 페이지에 저장한다.
• 버퍼 풀 페이지 조각을 관리하기 위해 LRU(Least Recently Used) 리스트와 플러시(Flush) 리스트, 그리고 프리(Free) 리스트 자료 구조를 사용한다.
  
  • LRU 리스트: 가장 최근에 사용되지 않은 페이지 목록이며, 가장 오래 사용되지 않은 페이지일수록 리스트 하단에 위치한다. 메모리 공간이 부족할 때 LRU 리스트의 하단에 위치한 페이지부터 FREE 리스트로 이동한다.
  • FLUSH 리스트: 변경된 데이터 페이지 목록이며, 디스크 변경 작업이 필요한 페이지를 효율적으로 관리하기 위해 사용한다. 주기적으로 플러시 리스트의 플러시 함수를 호출해서 오래전에 변경된 데이터 페이지를 순서대로 디스크에 동기화하는 작업을 수행한다.
  • FREE 리스트: 비어 있는 페이지 목록이며, 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용한다.

InnoDB 스토리지 엔진에서 데이터를 찾는 과정

1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
   1) InnoDB 어댑티브 해시 인덱스를 이용해 페이지 검색
   2) 해당 테이블 인덱스를 이용해 버퍼 풀에서 페이지 검색
   3) 버퍼 풀에 이미 데이터 페이지가 있었다면 페이지 포인터를 LRU 상단 방향으로 승급
2. (없었다면) 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지 포인터를 LRU 리스트에 추가
3. 해당 데이터가 실제로 읽히면 페이지 포인터를 LRU 상단 방향으로 승급
4. 버퍼 풀에 상주하는 데이터 페이지는 나이(Age)가 부여되어 오랫동안 사용되지 않으면 버퍼
   풀에서 제거
5. 해당 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가

버퍼 풀과 리두 로그

• InnoDB 버퍼 풀은 서버의 메모리가 허용되는 만큼 크게 설정하면 쿼리의 성능이 빨라진다. (데이터 캐시 역할)
• 또 다른 기능은 InnoDB 버퍼 풀 역할은 쓰기 버퍼링인데, 버퍼 풀의 크기가 크다고해서 쓰기 버퍼링 성능이 좋아지지 않는다. 그렇다면 해당 기능은 어떻게 향상시킬 수 있을까?

고성능 저장 장치를 사용하거나 쓰기 작업을 처리하는 쓰레드 수를 늘리는 방법을 생각해 볼 수 있겠지만, 로그 파일 크기와 관련지어보자.


데이터 수정 작업이 일어나면 리두 로그에 변경 내용이 기록된다. 리두 로그에 기록이 쌓이면 디스크에 반영해야 하는 동기화 문제가 발생한다. MySQL에서는 주기적으로 체크포인트를 발생시켜 변경된 페이지, 리두 로그, 디스크 간 동기화 작업을 진행한다. InnoDB 버퍼 풀이 매우 크지만, 리두 로그 파일의 크기가 매우 적다면 아주 적은 더티 페이지만 버퍼 풀에 보관할 수 있기에 쓰기 버퍼링의 효과는 거의 볼 수 없다. 반대로, InnoDB 버퍼 풀이 매우 작지만, 리두 로그 파일의 매우 크다면 매우 많은 더티 페이지를 한 번에 기록해야 하는 상황이 올 수 있다.


따라서, 리두 로그 파일의 전체 크기를 버퍼 풀 크기의 대략 5~10%로 설정하고 조금씩 늘려가며 최적값을 늘리는 것이 권장된다.

Double Writer Buffer

• 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만을 기록한다. 이로 인해 InnoDB 스토리지 엔진에서 더티 페이지를 디스크에 플러시할 때 일부만 기록되는 문제가 발생하면 그 페이지 내용은 복구할 수 없을 수도 있다. 이 같은 문제를 막기 위해 Double Writer Buffer를 사용한다.
  
• 실제 데이터 파일에 변경 내용을 기록하기 전에 'A'~'E'까지의 더티 페이지를 우선 묶어 한 번의 디스크 쓰기로 DoubleWrite 버퍼에 기록한 후 각 더티 페이지를 파일의 적당한 위치에 하나씩 랜덤 쓰기를 실행한다.
• 이는 실제로 'A'~'E' 더티 페이지가 정상적으로 기록되면 필요 없어진다. 데이터 파일의 쓰기가 중간에 실패한 경우에만 DoubleWriter 버퍼의 내용과 비교하여 복구에 사용된다.

3) 언두 로그

• 트랜잭션 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업한다.
• 트랜잭션이 롤백되면 언두 로그에 백업해 둔 이전 버전의 데이터를 이용해 복구하며(트랜잭션 보장), 특정 격리 수준에 맞게 언두 로그에 백업해둔 데이터를 읽어서 반환한다.(격리 수준 보장)

4) 체인지 버퍼

• RDBMS에서 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이므로 테이블에 인덱스가 많다면 상당한 자원을 소모하게 된다.
• InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 그렇지 않고 디스크에서 읽어와 변경해야 한다면 이를 즉시 실행하지 않고, 이를 임시 공간에 저장해두고 바로 사용자에게 반환하는 형태로 성능을 향상시킨다. 이때 임시 메모리 공간을 체인지 버퍼라고 한다.

4) 리두 로그 및 리두 버퍼

• 서버가 비정상적으로 종료됐을 때 디스크에 기록되지 못한 데이터를 잃지 않게 해주는 안전 장치이다.
• 커밋됐지만 데이터 파일에 기록되지 않은 데이터는 단순히 리두 로그에 저장된 데이터를 데이터 파일에 다시 적용하면 된다.
• 롤백됐지만 데이터 파일에 이미 기록된 데이터는 언두 로그의 내용을 가져와 데이터 파일에 다시 적용한다. 이때, 그 변경이 커밋, 롤백, 트랜잭션 실행 중간 상태인지 파악하기 위해 리두 로그를 활용한다.

5) 어댑티브 해시 인덱스

• B-TREE 인덱스에서 특정 값을 찾기 위해서 루트 노드, 브랜치 노드, 리프 노드를 거쳐야 원하는 레코드를 읽을 수 있다. 이러한 BTREE 검색 시간을 줄이기 위해 어댑티브 해시 인덱스가 도입되었다.

• 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다.

• 해시 인덱스는 '인덱스 키 값'과 해당 인덱스 키 값이 저장된 '데이터 페이지 주소' 쌍으로 관리된다.

• 성능에 유리하지 않은 상황

  1. 디스크 읽기가 많은 경우
  2. 특정 패턴의 쿼리가 많은 경우(조인, LIKE 패턴)
  3. 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

• 성능에 유리한 상황

  1. 디스크 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
  2. 동등 검색 조건(동등 비교와 IN 연산자)이 많은 경우
  3. 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

어댑티브 해시 인덱스는 메모리(버퍼 풀) 내에서 접근하는 것을 더 빠르게 만드는 기능이기 때문에 디스크에서 읽어오는 경우가 빈번하다면, 아무런 도움이 되지 않는다는 것을 명심하자!

• 어댑티브 해시 인덱스 또한 메모리 공간을 추가로 차지하며, 생성 및 제거 시에 추가적인 작업이 필요하다. 따라서, 어댑티브 해시 인덱스의 도움을 많이 받을수록 테이블 삭제 또는 변경 작업은 더 치명적인 작업이 된다는 것이다.

참고 자료

• https://dev.mysql.com/doc/refman/8.4/en/pluggable-storage-overview.html
• RealMySQL 8.0