영암사는 승경이네

개요

 InnoDB 스토리지 엔진은 MySQL 엔진에서 제공하는 잠금과는 별개로 레코드 기반의 잠금 방식을 탑재하고 있다. 테이블 기반의 잠금 방식을 채택하고 있는 MyISAM 엔진보다 동시성 처리 측면에서 뛰어나다.  레코드 락 뿐 아니라 레코드와 레코드 사이의 간격을 잠그는 갭 락(GAP LOCK), 이 두 락을 합쳐놓은 형태의 넥스트 키 락(NEXT KEY LOCK)도 지원한다. 이번 게시글에서는 이 세가지 락에 대해 이해하고, 팬텀 리드와 연관지어 생각해보도록 하자.

 아마 트랜잭션 격리 수준에 대해 공부했던 경험이 있다면 MySQL에서는 팬텀리드를 방지할수 있다는 얘기를 들어봤을것이다. 이를 넥스트 키락과 연계시켜 이해해볼것이다.

* 참고로 갭 락은 타겟 레코드를 잠그지 않는다. 레코드와 레코드 사이의 빈 공간(GAP)만을 잠근다.

레코드 락 (Record Lock)

 말 그대로 레코드에 대한 락이다. 특정 레코드를 수정하거나 잠금과 함께 조회할 때, 그 레코드만을 잠그고, 나머지 레코드에는 자유롭게 접근할 수 있다. 단, 일반적인 레코드 락과 다른점이 하나 있는데, 레코드 자체가 아닌 인덱스의 레코드를 잠근다는 것이다. 인덱스가 없는 테이블이더라도 자동 생성된 클러스터드 인덱스를 이용해 잠금을 설정한다. 아래 쿼리는 id = 1 인 프라이머리 키(인덱스)를 통해 레코드를 찾음과 동시에 인덱스의 레코드에 락을 거는 것이다.

SELECT * FROM [table] WHERE id = 1 FOR UPDATE;

인덱스 레코드를 잠근다? 🤔

MySQL의 데이터들은 B+트리 구조로 관리된다. 즉, 인덱스의 레코드는 리프 노드를 의미한다. 클러스터드 인덱스의 경우 리프 노드에 실제 레코드 데이터들이 들어가있다. 즉, 리프노드에 잠금을 건다는 건 실제 레코드에 락을 획득해 접근할 수 없도록 잠그는것과 같다.

논 클러스터드 인덱스라면? 🤔

마찬가지로 논 클러스터드 인덱스의 리프 노드를 잠근다. 단, 실제 데이터 레코드에 접근하기 위해 클러스터드 인덱스를 사용해야 하는 상황이 발생하면, 클러스터드 인덱스의 리프 노드 또한 잠근다. 아래와 같이 users 테이블이 있고, email 컬럼에 대해 논 클러스터드 인덱스를 설정한 상태에서 실제 레코드 인덱스가 잠기는지 테스트해보자.

CREATE TABLE users (
  id INT PRIMARY KEY, -- 클러스터드 인덱스
  name VARCHAR(100),
  email VARCHAR(100)
);

CREATE INDEX idx_email ON users(email); -- 논 클러스터드 인덱스

-- 데이터 추가
insert into users values (1,'andrew','andrew@tistory.com');
insert into users values (2,'test','test@tistory.com');
insert into users values (3,'maple','maple@tistory.com');
insert into users values (4,'andrew2','andrew@tistory.com');

start transaction; -- 트랜잭션 1 시작

SELECT name FROM users WHERE email = 'andrew@tistory.com' FOR UPDATE; -- 논클러스터드 인덱스를 조건으로 검색



start transaction; -- 트랜잭션 2 시작

update users set name = 'andrew.sim' where email = 'andrew@tistory.com'; -- 논 클러스터드 인덱스 잠금으로 인한 대기
update users set name = 'andrew.sim' where id = 1; -- 클러스터드 인덱스 잠금으로 인한 대기
update users set name = 'andrew.sim' where id = 2; -- 클러스터드 인덱스가 잠기지 않았으므로 실행 완료

그 이후 email 컬럼을 잠금과 함께 조회한다면, 논 클러스터드 인덱스의 리프노드가 잠길것이다. 그런데 조회하는 컬럼 name이므로 이를 조회하기 위해서는 클러스터드 인덱스를 사용하여 데이터 레코드에 접근해야한다. 이때 클러스터드 인덱스의 리프노드도 함께 잠기게 된다.

갭 락 (GAP LOCK)

레코드와 레코드 사이를 잠그는 락이다. 갭 락의 역할은 레코드와 레코드 사이에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 것이다. 갭 락은 단독으로 거의 사용되지 않고 넥스트 키 락의 일부로 사용된다.

레코드 락은 인덱스 레코드로 잠그는데... 갭락은 뭘로 잠그죠?  🤔

 레코드 락은 인덱스 레코드를 잠그는 것이라고 했다. 하지만 갭 락은 실제로 존재하는 뭔가가 없다. 레코드와 레코드 사이는 텅 빈 값인데, 도대체 어떻게 이 범위에 잠금을 거는걸까? 바로 메타데이터를 활용해 잠금을 건다. 인덱스 레코드에 존재하는 메타데이터에는 자신과 인접한 인덱스 레코드의 정보도 포함되어 있다. 이 정보를 활용해 갭 락을 거는 것이다.

 만약 책장에 5번 책과 10책이 꽂혀있다고 해보자. 5번 책과와 10번 책 사이에 무언가를 끼우는 행위를 막으려면 10번 책에에 포스트잇을 붙여 '이전 책은 5번 책입니다.' 또는 5번 책에 '다음 책은 10번 책입니다' 라는 정보를 기재해주면 된다. 그럼 5번 책을 보면 다음 책은 10번 책이니 5 ~ 10 사이에는 갭이 있다는 것을 알 수 있다.

넥스트 키 락 (NEXT KEY LOCK)

 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금으로 락 기반의 조회 쿼리(SELECT ... FOR UPDATE)에 범위 조건(<, >, BETWEEN)을 걸었을 때 설정되며, 해당 레코드 뿐 아니라 갭(GAP)에도 락이 걸리게 된다. 레코드 락과 갭 락이 함께 걸리는 이유를 이해하기 위해서는 InnoDB 엔진의 B+트리 구조를 이해해야한다. B+트리 구조에 대해서는 따로 설명하지 않겠다. 여기서는 B+트리 구조와 넥스크 키 락의 관계와 팬텀리드를 방지할 수 있는 이유를 알아볼것이다.

넥스트 키 락이 팬텀 리드를 방지할 수 있는 이유? 🤔

 MySQL에서는 넥스트 키 락에 의해 팬텀리드 현상을 방지한다. 이 넥스트 키락이 존재할 수 있게 하는 것이 B+트리이다. B+트리의 특성을 활용해 넥스트 키 락을 걸고, 이 락에 의해 팬텀 리드 현상을 막게 되는 것이다. B+트리의 가장 큰 특성 중 하나는 리프노드가 오른쪽 리프노드의 포인터를 갖는 연결 구조로 되어있다는 것이다. 이를 활용한다면 특정 리프 노드는 다음으로 큰 리프노드에 빠르게 접근할 수 있다.

 그럼 아래와 같이 10 단위의 ID 가 있는 B+ 트리 구조에서 특정 범위에 대한 SELECT ... FOR UPDATE 쿼리를 날리면 어떻게 될까?

SELECT * FROM USERS WHERE ID >= 10 AND ID <= 55 FOR UPDATE;

 먼저 ID가 10 이상인 인덱스 레코드(리프노드)를 찾고, 인덱스 레코드를 잠근다. 그 후 포인터를 통해 오른쪽 리프노드인 20으로 이동한다.

 그 후 현재 인덱스 레코드의 ID가 55 이하인지를 체크한다. 55 이하일 경우 마찬가지로 인덱스 레코드를 잠그고 오른쪽 리프노드로 이동하기를 반복해나간다. 그러다 ID가 60인 값을 만났을 때 55 이하라는 조건에 만족하지 않아 멈추게 된다.

 최종적으로 10, 20, 30, 40, 50 인덱스 레코드가 잠기고(레코드 락), 각 인덱스 레코드의 메타데이터를 통해 갭도 잠기게 된다.(갭 락) 레코드 락과 갭 락이 동시에 걸렸다. B+트리의 특성을 활용해 넥스트 키 락을 건 것이다.

이제 넥스트 키 락이 걸렸을 때, 팬텀 리드를 방지할 수 있을지를 생각해보자. 넥스트 키락은 레코드와 갭을 잠근다. 해당 범위에 인덱스 레코드를 삽입할 수 없다는 뜻이다. 그럼 너무나 당연하게도 팬텀리드 현상을 방지할 수 있게된다.

넥스트 키 락이 팬텀 리드를 방지할 수 있는 이유! 🤩

 정리하면, MySQL은 InnoDB 엔진을 사용하기에 B+트리 구조로 데이터가 저장되고, B+트리 구조에서 넥스트 키 락을 걸 수 있으며, 넥스트 키 락에 의해 팬텀 리드 현상이 방지되는 것이다. 

MySQL에서는 팬텀 리드가 발생하지 않는다고도 하던데... 🤔

이건 잘못된 말이다. MySQL에서도 팬텀 리드 현상이 발생할 수 있다. 다만, 잠금을 사용하여 조회한다면 B+트리를 사용하는 InnoDB엔진 특성 상 넥스트 키 락이 걸릴거고, 넥스트 키 락에 의해 레코드와 갭이 잠기면서 팬텀리드 현상을 방지하게 되는것이다. 잠금을 사용하여 조회하지 않는다면?? 팬텀리드 현상은 당연히 발생한다.

자동 증가 락 (AUTO INCREMENT LOCK)

 자동 증가하는 숫자 값을 채번하기 위한 잠금으로 테이블 락과 함께 동작한다. AUTO_INCREMENT 속성이 사용된 컬럼의 테이블에 레코드가 INSERT 될때 잠기며, UPDATE나 DELETE 쿼리에서는 잠금이 걸리지 않는다. AUTO_INCREMENT 락을 명시적으로 획득하는 방법은 없으며, 아주 짧은 시간동안 걸리는 잠금이기에 대부분의 경우 문제가 되지 않는다.

 MYSQL 5.1 이상부터는 innodb_autoinc_lock_mode 라는 시스템 변수를 사용해 자동 증가 락의 작동 방식을 변경할 수 있다.

1. innodb_autoinc_lock_mode = 0

테이블 락 기반의 기본적인 자동 증가 락을 사용한다.

2. innodb_autoinc_lock_mode = 1

여러 건의 레코드를 INSERT 하는 중, MySQL 서버가 INSERT 되는 레코드의 건수를 정확히 예측할 수 있을 때 자동 증가 락을 사용하지 않고, 훨씬 가볍고 빠른 래치(Latch)를 이용해 처리한다. 자동 증가 락보다 훨씬 짧은 시간동안만 잠금을 걸고, 필요한 자동 증가 값을 한번에 가져온다. 다만, INSERT ... SELECT 와 같이 MySQL 서버가 건수를 예측할 수 없을 때는 자동 증가 락이 사용된다.

3. innodb_autoinc_lock_mode = 2 (MySQL 8.0 기본 값)

자동 증가 락은 사용하지 않고 래치만을 사용한다. 이 방식은 연속된 자동 증가 값을 보장하지 않지만, INSERT ... SELECT와 같이 대량 INSERT 문이 설정되는 중에도 다른 커넥션에서 INSERT를 수행할 수 있다.

래치(Latch)가 뭔가요? 🤔

InnoDB 내부에서 데이터 구조를 보호하기 위해 사용하는 경량의 잠금이다. 자동 증가 락은 락의 범위가 테이블로, 자동 증가 값인 AUTO_INC 값을 가져올때와 INSERT가 완료될때까지 잠금을 유지한다. 여러 트랜잭션이 INSERT 시 잠금이 끝날때까지 대기하며 순차적으로 처리되는 구조이다.
 이에 반해 래치(Latch)는 AUTO_INC 값을 가져올때만 잠금을 유지한다. 여러 트랜잭션이 INSERT 시 병렬 INSERT 처리가 가능한 것이다.

개요

 MySQL의 잠금은 크게 두가지로 MySQL 엔진, InnoDB 스토리지 엔진 잠금이 있다. 이번 포스팅에서는 먼저 MySQL 엔진에 대한 잠금을 알아보자.

MySQL 엔진과 InnoDB 스토리지 엔진이 뭔가요?

MySQL 엔진과 InnoDB 스토리지 엔진은 MySQL 서버의 구성요소이다. MySQL 엔진은 쿼리 파싱, 최적화, 실행 뿐 아니라 캐시, 커넥션과 같은 부가 기능들을 관리하고, InnoDB 스토리지 엔진은 실제 데이터의 저장, 디스크 I/O, 인덱스 관리와 같은 하드웨어 처리를 담당한다. 즉, MySQL 엔진이 쿼리를 실행하면, InnoDB 스토리지 엔진은 데이터를 읽고, 쓰는 역할을 하는것이다.

MySQL 엔진 잠금

1.  글로벌 락

 글로벌 락은 SELECT를 제외한 DDL이나 DML 문장에 대한 락으로, MySQL 서버에 존재하는 모든 테이블과 DB에 영향을 미치는 락이다. FLUSH TABLES WITH READ LOCK 명령으로 락을 획득한다.

 다만, 이미 UPDATE와 같은 DML 트랜잭션이 진행중이라면 끝날때까지 대기해야하는데, 읽기 잠금을 획득해야하기 때문이다. 또한 다른 트랜잭션에서 장시간 SELECT 쿼리를 실행하는 상황에서도 대기해야한다.

 글로벌 락은 mysqldump와 같이 일관된 백업을 받아야할 때 사용하곤 한다.

DDL (Data Definition Language)
데이터 구조(스키마)를 정의/변경하는 명령어이다.

DML (Data Manipulation Language)
데이터 자체 조작하는 명령어로 SELECT, INSERT, UPDATE, DELETE 가 있다.

SELECT 쿼리를 기다려야 하는 이유?

FLUSH TABLES WITH READ LOCK을 끊어 읽어보면 FLUSH, READ LOCK 이라는 단어가 눈에 띈다. FLUSH는 버퍼 풀의 레코드를 디스크에 저장하는 작업을, READ LOCK은 읽기 잠금을 뜻한다. 읽기 잠금인데 다른 트랜잭션에서 발생하는 SELECT 쿼리에 영향을 받는 이유는 뭘까? 글로벌 락은 당연하게도 READ LOCK 뿐 아니라 METADATA LOCK을 획득해야 하고, 더 나아가 MySQL의 테이블 핸들이 닫혀있어야 한다. SELECT 쿼리는 테이블 핸들을 필요(열어야하는)로 하는 작업이다.

테이블 핸들 (Table Handle)
접근하는 테이블을 가리키는 내부 객체이다. 쿼리를 통해 테이블을 액세스 하면 테이블 핸들이 열리고, 트랜잭션이 끝나면 핸들이 닫힌다.

글로벌 락 테스트해보기

총 두가지를 테스트해볼 것이다. 첫번째는 글로벌 락이 걸렸을 때 읽기 잠금이 걸리는지, 두번째는 테이블 핸들이 열렸을 때 글로벌 락을 획득하지 못하고 대기하는지이다.

1) 글로벌 락이 걸렸을 때 읽기 잠금 

1번 트랜잭션에서 글로벌 락 획득

# transaction 1

start transaction; // 트랜잭션 시작

FLUSH TABLES WITH READ LOCK; // 글로벌 락

2번 트랜잭션에서 읽기 잠금 테스트

# transaction 2

start transaction;

SELECT * FROM TBL_USER; //성공 : 테이블 데이터 조회됨

UPDATE TBL_USER SET NAME = '승경' WHERE USER_ID = 1; // 실패 : 글로벌 락으로 인함

2) 테이블 핸들이 열렸을 때 글로벌 락 획득 대기

1번 트랜잭션에서 select sleep from [table] 쿼리를 통해 테이블 핸들 오픈

# transaction 1 

start transaction;

select sleep(10) from tbl_user; // tbl_user 에 로우 개수만큼 10초씩 sleep (테이블 핸들을 열기 위함)

2번 트랜잭션에서 글로벌 락 획득 시도

# transaction 2

start transaction;

FLUSH TABLES WITH READ LOCK; // 실패 또는 SELECT 작업이 끝난 후 성공

2.  테이블 락

테이블 락은 테이블 단위로 설정되는 잠금으로 명시적, 묵시적으로 락을 획득할 수 있다. 명시적 테이블 락은 'LOCK TABLES [TABLE NAME] [ READ or WRITE ]' 명령으로 획득할 수 있다. 단, 테이블 락은 어플리케이션에 상당한 영향을 끼치므로 실무에서는 거의 사용되지 않는다.

 묵시적 테이블 락은 DDL 쿼리 실행 시 발생하며, 읽기/쓰기를 모두 차단한다. DML 쿼리에 대해서는 스토리지 엔진에 따라 달리 동작하는데, MyISAM 또는 MEMORY 스토리지 엔진의 경우 자동으로 테이블 락이 발생한다. 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금이 해제되는 프로세스이다. InnoDB 스토리지 엔진은 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 DML 쿼리로 테이블 락이 설정되지 않는다.

DDL 쿼리도 트랜잭션처럼 Commit 해야 반영되나요?

맞다. Commit을 해야 실제로 반영된다. 다만, DDL 쿼리는 Auto Commit 으로 처리된다.

테이블 락  테스트해보기

테이블에 쓰기 잠금을 걸었을 때 테이블 데이터를 조회, 수정하는 쿼리를 날려보자.

1) 1번 트랜잭션에서 WRITE LOCK 실행

start transaction;

LOCK TABLES tbl_user WRITE; // tbl_user에 대한 Write Lock

2) 2번 트랜잭션에서 SELECT 및 UPDATE 쿼리 실행

start transaction;

select * from tbl_user; // 실패 : 테이블 Write Lock 으로 인함
update tbl_user set name = '승경' where user_id = 1; // 실패 : 테이블 Write Lock 으로 인함

3.  네임드 락

 네임드락은 임의의 문자열에 대한 잠금으로, GET_LOCK() 함수를 사용한다. 이 잠금은 테이블이나 레코드, 데이터 베이스 객체가 아닌 단순히 사용자가 지정한 문자열에 대한 잠금을 획득하고 반납하는 잠금이다. 반환은 RELEASE_LOCK() 함수를 사용한다.

 네임드 락을 사용하는 예는 다중 어플리케이션에서의 배치 실행이다. 다중 어플리케이션에서 한 번의 배치만 돌게 하기 위해 네임드 락을 사용하기도 한다.

SELECT GET_LOCK('batch_lock', 0); // batch_lock 획득 시도, 획득 대기시간 없음(0초)

 여러 어플리케이션에서 동시에 batch_lock 이라는 네임드 락 획득을 시도하면 최초 획득한 트랜잭션의 어플리케이션만 배치가 돌고, 나머지는 대기 시간이 없어 실패와 함께 배치 실행을 하지 않게 된다.

 네임드 락을 사용하는 또 다른 예는 비지니스 로직에 대한 잠금 범위를 지정하고 싶을때이다. 특정 로직이 실행되기 전 네임드 락을 획득하고, 로직이 끝난 후 네임드 락을 반환한다면, 멀티 스레드 환경에서 해당 로직이 순차적으로 처리될것이다.

// 비지니스 로직 시작
...
SELECT GET_LOCK('service_1', 100); // 네임드 락 획득
...
// 비지니스 로직 종료
...
SELECT RELEASE_LOCK('service_1'); // 네임드 락 반환

단, 락을 획득한 후 해제해주지 않으면 시스템이 멈추는 위험을 초래할 수 있고, 락 이름 충돌 가능성이 있으므로 구분할 수 있는 prefix를 붙여주는 것이 좋다.

4.  메타 데이터 락

 메타 데이터 락은 테이블이나 뷰 등 DB 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다. 이 락은 자동으로 획득/반환되는 묵시적 잠금이다. 앞서 설명한 글로벌 락, 테이블 락이 발생할 경우 메타 데이터 락도 함께 획득한다.

개요

슬로우 쿼리 개선을 위해 스프링 부트 환경에서 AOP 기반으로 슬로우 쿼리 로그를 남기는 인터셉터를 구현했다. 다만, JPA 환경이냐 Mybatis 환경이냐에 따라 다른 방식으로의 구현이 필요했고, Interceptor 타겟을 설정하는 부분에 따라 로깅을 남기는 구조였기 때문에 '정말 모든 쿼리를 다 체크하고 있을까?' 라는 의문이 들었다.

 그러던 중 Real MySQL의 슬로우 쿼리 관련 챕터를 읽게 되었고, MySQL 자체에 슬로우 쿼리 관련 설정이 있다는 것을 알게 되었다. 이 설정들을 적용해보고, 어떤 형태로 슬로우 쿼리가 추출되는지 확인해보았다.

MySQL 슬로우 쿼리 로깅 활성화

먼저 MySQL에 접속 후 여러 옵션값들을 확인하고 설정해야한다.

1) slow_query_log

슬로우 쿼리 로깅 활성화 여부를 나타내는 설정값이다. 기본값은 OFF이므로 ON 또는 1로 설정한다.

show variables like '%slow_query_log%'; // 슬로우쿼리 활성화 여부 확인

set global slow_query_log = 'ON'; // 슬로우쿼리 활성화

2) long_query_time

슬로우 쿼리에 대한 기준 시간을 나타내는 설정값이다. 기본값은 10초로 되어있어 1초로 수정하였다.

show variables like '%long_query_time%'; // 슬로우 쿼리 기준 시간 확인

set global long_query_time = 1; // 슬로우 쿼리 기준 시간을 1초로 변경

set global 명령어를 통해 변수들을 설정할 경우 MySQL이 재시작될 때 설정값들이 초기화된다. 영구 설정을 하고자하면 my.ini나 my.cnf 파일을 다음과 같이 수정하면 된다.

// my.ini 파일
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

3) log_output

슬로우 쿼리를 남길 수 있는 방법은 두 가지로, 테이블 혹은 디스크이다. 온프레미스 환경에서는 디스크에 저장하는 것을 선호한다. 테이블로 설정할 경우 슬로우 쿼리가 발생할때마다 쓰기 작업이 발생하며, 데이터가 많아질 경우 부하가 발생할 수 있기 때문인데, 이를 방지하기 위해 슬로우 쿼리에 적재된 데이터를 주기적으로 삭제해주는 작업이 필요하다.

 AWS RDS 환경일 경우 파일 시스템에 직접 접근하지 못하므로 테이블에 저장하기도 한다. 참고로, 디스크로 설정한 후 RDS 설정에 SlowQuery Logging 을 활성화하면 Cloud Watch 의 로그 그룹에 저장된다. 필자의 경우 테이블에 적재되도록 설정하였다.

show variables like '%log_output%'; // 슬로우 쿼리 저장 타입 확인 (TABLE or FILE)

set global log_output = 'TABLE'; // 슬로우 쿼리를 테이블에 저장하도록 설정

슬로우 쿼리 테이블 확인

슬로우 쿼리 테이블은 mysql.slow_log 이다. 아래 명령어를 사용해 데이터가 쌓였는지 확인해보자. 아마 비어있을건데, 다음은 슬로우 쿼리를 직접 발생시켜 적재되는지를 확인해볼 것이다.

select * from mysql.slow_log order by start_time desc;

* rows_examined 값이 매우 크면 인덱스가 제대로 안 쓰이고 있을 가능성이 높다.

슬로우 쿼리 발생시키기

슬로우 쿼리가 발생했을 때, mysql.slow_log 테이블에 적재되는지 확인하기 위해 select sleep(2) 쿼리를 날려보자.

 만약 슬로우 쿼리에 로그가 남지 않는다면, 아까 설정했던 global 설정 값들이 현재 세션에는 적용되지 않은 상태일 가능성이 있다. 이 경우 재접속하면 된다.

select sleep(2);

select * from mysql.slow_log order by start_time

sql_text 값이 BLOB 형태로 출력된다면 아래 쿼리를 통해 조회하면 된다.

SELECT 
  start_time,
  user_host,
  CAST(sql_text AS CHAR) AS sql_text,
  query_time,
  lock_time,
  rows_sent,
  rows_examined
FROM mysql.slow_log;

1. 개요

 리두로그와 언두로그를 공부하려했더니 Buffer Pool 이라는 개념이 등장했다. 테이블을 메모리에 캐싱해두는 개념인데, 단순 개념만 알고있기에는 중요한 내용이기에 정리하였다.

2. InnoDB Buffer Pool이 뭐에요?

말 그대로 InnoDB 엔진에서 제공하는 버퍼 풀인데, 이녀석의 역할은 바로 캐싱이다.

테이블이나 인덱스를 캐싱한다고 한다. 그럼 정확히 어떤 형태로 캐싱을 하고, 실제 환경에서 어떻게 사용되는지 알아보자.

3. Buffer Pool 은 '페이지'들이 캐싱되어 있어요

 버퍼 풀에는 '페이지'들이 저장되어 있다. InnoDB 엔진에서의 페이지란 데이터베이스에서 데이터를 저장 단위이다. I/O를 통해 디스크로부터 페이지 단위로 데이터를 조회하면, Buffer Pool에 이를 그대로 저장한다. 페이지 하나의 크기는 16KB이다. (수정 가능하다)

 페이지들은 버퍼 풀에 차곡차곡 쌓이는게 아니다. 버퍼 풀은 데이터를 효율적으로 캐싱하기 위해 LRU 리스트, Flush 리스트, Free 리스트 라는 3개의 리스트로 구성되어 있으며, 이 중 LRU 리스트에 어떤 규칙에 따라 저장되게 된다.

3.1. Free 리스트

비어있는 페이지들의 목록이다. 사용자의 쿼리가 디스크의 데이터 페이지를 읽어와야 하는 경우 사용된다. 

3.2. Flush 리스트

디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(== 더티 페이지)들의 목록이다. 즉, 디스크에서 데이터 페이지를 읽은 후 변경이 가해진 데이터 페이지들의 목록이다. 데이터가 변경되면 해당 페이지는 Flush 리스트에서 관리됨과 동시에 변경 내용을 리두 로그에 기록하고, 버퍼 풀에도 변경 내용을 반영한다. 그 후 특정 시점에 리두 로그가 디스크로 기록된다.

3.3. LRU (+MRU) 리스트

MySQL InnoDB 에서는 변형된 LRU 방식을 채택하고 있다. 바로 LRU와 MRU(Most Recently Used)가 결합된 하이브리드 방식이다. 참고로 LRU와 MRU는 캐시 알고리즘이다.

* LRU (Least Recently Used)
가장 최근에 사용되지 않은 페이지를 교체하는 알고리즘. 가장 오래된 캐시를 제거한다.

* MRU (Most Recently Used)
가장 최근에 사용된 페이지를 교체하는 알고리즘. 가장 최근에 사용된 캐시를 제거한다.

4. 가장 오래된 페이지와 가장 최근의 페이지를 제거한다고??

위 두 캐시 알고리즘이 버퍼 풀에 적용되어 있는 구조이다. 이해가 잘 되지 않는다면 어떻게 동작하는지에 대한 메커니즘을 그려나가보면 이해할 수 있을것이다.

아래는 변형된 LRU, 즉 LRU와 MRU가 결합된 형태의 리스트이다.

InnoDB에서의 페이지 캐시 메커니즘에 의해 New 서브리스트 자주 사용되는 페이지가 저장되는 영역, Old 서브리스트는 신규 혹은 자주 사용되지 않는 페이지가 저장되는 영역으로 사용될것이다. 편의상 New 서브 리스트가 존재하는 영역을 MRU 영역, Old 서브 리스트가 존재하는 영역을 LRU 영역이라 표현하겠다.

긴말 필요없이 바로 동작 메커니즘을 살펴보자

1) 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사한다. [InnoDB 어댑티브 해시 인덱스 > 테이블 인덱스] 를 통해 페이지를 검색한다. 

* 어댑티브 해시 인덱스
B-Tree 의 성능 개선을 위해 InnoDB 에서 자동으로 추가하는 인덱스이다. 자주 읽히는 데이터 페이지의 키값을 이용해 해시 인덱스를 만들고, 필요할 때마다 해당 데이터 페이지로 즉시 찾아갈 수 있게 한다.
- LRU/MRU를 설명하는 글이므로 자세히 다루지 않겠다.

2) 버퍼 풀에 데이터 페이지가 있다면 MRU 영역 방향으로 승급한다. (MRU 리스트로 들어가는게 아닌 방향으로 승급이다.)

3) 버퍼 풀에 데이터가 없다면 디스크로부터 필요한 데이터 페이지를 버퍼 풀에 적재와 동시에 LRU 영역 Head 부분에 추가한다.

4) LRU 영역에 추가한 데이터 페이지가 조회되면 MRU 영역 방향으로 승급하고, 버퍼풀에 없다면 LRU Head 부분에 추가하는 과정을 반복한다.

5) 이때, 승급하는 데이터 페이지가 LRU 영역의 Head에 위치했을 경우, 바로 위인 MRU 영역의 Tail이 아닌 MRU 영역의 Head로 이동한다. (이부분이 핵심이다!)

6) 1,2,3,4,5 과정이 반복되면서 자주 사용되지 않는 데이터 페이지들은 자연스럽게 LRU 영역의 Tail 쪽으로 밀려난다. 끝까지 밀려난 데이터 페이지들은 버퍼 풀에서 제거된다.

7) 자주 사용되는 데이터 페이지의 경우 성능 향상을 위해 페이지의 인덱스 키를 '어댑티브 해시 인덱스'에 추가한다.

5. LRU, 오래된 데이터가 제거되는 것은 이해가는데.. MRU는?

LRU 알고리즘이 적용됐다는 것은 이해가 갈것이다. 그럼 최근에 사용된 데이터가 먼저 삭제된다는 MRU는 대체 어디에 적용된 것일까? 데이터 레코드를 버퍼 풀로 읽어오는 과정을 직접 그려보며 '최근 사용된 데이터가 먼저 삭제'되는 MRU 부분을 이해해보자.

1) 1시 30분, 디스크로부터 A 데이터 페이지를 읽어 버퍼 풀에 적재한다. 최초 버퍼 풀 적재시에는 LRU 영역 Head 부분에 저장된다.

2) 1시 32분, 디스크로부터 B 데이터 페이지를 읽어 버퍼 풀에 적재한다. LRU 영역 Head 부분에 저장됨과 동시에 기존 A 데이터 페이지는 Tail 쪽으로 밀려난다.

3) 2시 0분, 디스크로부터 C 데이터 페이지를 읽어 버퍼 풀에 적재한다. LRU 영역 Head 부분에 저장됨과 동시에 A, B 데이터 페이지는 Tail 쪽으로 밀려난다.

4) 2시 10분, 드디어 C 데이터 페이지를 찾는 쿼리가 실행됐고, C 데이터 페이지를 버퍼 풀에서 로드하게 된다. 이때 C 데이터 페이지는 LRU 영역의 Head에 위치했기 때문에 MRU 영역의 Head로 이동하게 된다.

5) 2시 20분, 디스크로부터 D 데이터 페이지를 읽어 버퍼 풀에 적재한다. LRU 영역 Head 부분에 저장됨과 동시에 A, B 데이터 페이지는 Tail 쪽으로 밀려난다.

6) 계속해서 신규 쿼리가 들어옴에 따라 디스크로부터 데이터 페이지를 읽어오게 되었다. E, F, G... X, Y, Z 까지, 다양한 데이터 페이지를 디스크로부터 로드하다보니 5시 30분이 되었다.

6. 최신의 데이터가 먼저 제거된다!

2시 10분에 저장한 C 데이터 페이지보다 최근에 사용했었던 E, F, G, H... 들의 데이터 페이지가 버퍼 풀에서 먼저 제거되었다. MRU 영역에서도 자주 사용되지 않은 데이터 페이지들은 Tail 쪽으로 밀려난다. 단, MRU 영역에 있는 C 데이터 페이지가 삭제되지 않은 이유는, 다른 데이터 페이지들이 여러번 재사용되지 않았고, 이로 인해 MRU 영역에서의 '경쟁'이 발생하지 않았기 때문이다. 

 '최근 사용된 데이터인 E,F,G,H 등의 데이터 페이지들이 먼저 삭제'된다는 점에서 MRU 알고리즘이 적용되었음을 알 수 있다.

7. 정리하면... InnoDB의 LRU + MRU 버퍼 풀

 InnoDB 버퍼 풀은 하이브리드 방식을 사용함으로써 단순 오래된 데이터 페이지 뿐 아니라, 자주 사용되지 않은 최신의 데이터 페이지들도 제거하고 있다. 만약 MRU 알고리즘이 적용되지 않았다면 어떨까? 최근 액세스한 데이터 페이지 들 중 자주 사용되지 않아 사실상 필요없는 데이터 페이지가 메모리에서 공간만 차지할것이고, 버퍼 풀이 가득 차서야 제거될것이다. 캐시 메모리를 최대한 활용하기 위해서는 자주 사용하는 데이터를 많이 저장하고, 최신의 데이터라할지라도 자주 사용하지 않은 데이터는 제거하는 것이 좋지 않겠는가?!

오늘은 InnoDB Buffer Pool에 대한 기본 구조와 InnoDB의 LRU + MRU 리스트에 대해 알아보았다. 다음은 리두 로그와 언두 로그에 대해 공부해보도록 하겠다!

개요

InnoDB의 잠금에 대해 알아보기 전 비관적 잠금과 낙관적 잠금에 대해 알아보았다.

낙관적 잠금

각 트랜잭션이 같은 레코드를 변경할 가능성이 낮다고 낙관적으로 가정하는 상황에서의 잠금이다. 리소스를 잠그지 않고 데이터를 업데이트 한 후, 마지막 커밋 시 충돌을 확인하는 방식이다.

충돌이 감지되면(트랜잭션이 시작되고 끝나는 사이, 다른 트랜잭션이 데이터를 수정) 트랜잭션이 롤백되고 오류를 반환한다.

충돌을 어떻게 확인할까?

테이블에 수정 시점을 추적할 수 있는 버전 필드 또는 타임스탬프를 추가하는 것이다.

버전 필드 (Version Field)
낙관적 잠금에서 각 데이터 레코드에 추가되는 필드로, 해당 레코드가 마지막으로 수정된 시점을 추적하는 용도로 사용된다. 레코드가 업데이트 될 때마다 이 버전 번호가 증가하는 형태이다.

트랜잭션이 데이터를 읽을 때 버전 필드를 함께 읽고, 트랜잭션이 커밋할 때 버전 번호를 확인하여 일치하는지를 확인한다. 만약 버전 필드 값이 달라졌다면, 다른 트랜잭션에서 데이터를 수정한 것이라 판단하고 트랜잭션을 롤백 또는 재시작한다.

 타임스탬프를 사용할 경우 해당 레코드의 수정 시간을 기재한다. 마찬가지로 트랜잭션이 시작, 종료될 때 레코드의 수정 시간이 달라졌다면 롤백을, 같다면 커밋을 하는 방식이다.

-- 1. 데이터 읽을 때 버전 정보도 함께 읽음
SELECT product_id, product_name, price, version
FROM products
WHERE product_id = 1;

-- 2. 수정 시 버전 확인
UPDATE products
SET price = 200.00, version = version + 1
WHERE product_id = 1 AND version = 2;  -- '2'는 읽은 버전 번호

낙관적 잠금 장단점

자원에 대한 잠금이 없기때문에, 대기 시간이 짧고, 처리 속도가 향상될 수 있다. 특히 데이터 충돌이 드문 경우에는 트랜잭션이 빠르게 처리된다. 또한 여러 트랜잭션이 동시에 데이터를 읽고 수정할 수 있으므로, 데이터 충돌이 발생하는 상황이 적다면 그만큼 높은 동시성을 유지하게된다.

하지만 데이터 충돌이 발생할 경우 트랜잭션을 다시 시도해야 한다.  충돌이 자주 발생하거나 처리 비용이 높은 트랜잭션이라면, 재시도 비용도 증가하게 된다. 또한 충돌에 대한 예외 로직이 필요하다. 변경을 다시 시도하거나, 사용자에게 알림을 주는 등의 별도 처리가 필요하다. 이러한 예외 코드를 잘못 구현하기라도 한다면 데이터 무결성이 손상될수 있으므로 주의해야한다.

비관적 잠금

각 트랜잭션이 같은 레코드를 변경할 가능성이 높다고 비관적으로 가정하는 상황에서의 잠금이다.즉, 하나의 트랜잭션이 데이터를 조회하는 시점에 락을 걸고, 트랜잭션이 끝날때까지 유지한다.

조회 시점에 잠금을 획득하는 대표적인 방법은 SELECT FOR UPDATE 이다.

비관적 잠금의 장단점

트랜잭션의 동시 접근을 확실하게 방지할 수 있기에 데이터의 무결성이 보장된다.

하지만 동시성이 떨어져 처리 속도가 느리다. 트랜잭션이 각각의 자원을 점유하는 상태에서 서로의 자원을 요청한다면, 데드락이 발생하게 된다.

비관적 잠금 데드락 재현

1) 1번 트랜잭션이 users 테이블의 id = 1인 레코드에 대해 비관적 잠금을 설정한다. 트랜잭션은 끝나지 않고, 다른 작업들을 처리하고 있다.

select * from users where id = 1 FOR UPDATE;

2) 도중 2번 트랜잭션이 시작되었고, users 테이블의 id = 10인 레코드에 대해 비관적 잠금을 설정한다. 마찬가지로 트랜잭션은 끝나지 않고, 다른 작업들을 처리하고 있다.

select * from users where id = 10 FOR UPDATE;

3) 1번 트랜잭션의 작업 중 유저 정보 일괄 수정 위해 업데이트 쿼리가 나갔지만, 2번 트랜잭션에서 id = 10인 레코드에 대한 잠금으로 인해 '대기상태'에 들어간다.

update users set name = '이름';

4) 2번 트랜잭션에서도 마찬가지로 유저 정보 일괄 수정 위해 업데이트 쿼리가 나갔지만, 1번 트랜잭션에서 id = 1인 레코드에 대한 잠금으로 인해 '대기상태'에 들어간다.

5) 트랜잭션 1, 2번은 데드락 상태에 빠지게 된다.

하지만 실제 테스트를 해보니 무기한 데드락이 걸리지 않고 한쪽 트랜잭션을 롤백시키는 것을 볼 수 있는데, 이는 InnoDB에서 제공하는 자동 데드락 탐지 기능에 의해 한쪽 트랜잭션이 롤백되는 것이다.

또한 락에 대한 타임아웃도 설정되어있기에, 락이 걸린 자원을 획득하지 못한다면 무한정 대기상태에 들어가지 않고, 에러 및 롤백 후 트랜잭션 재시작을 시도한다.

잠금에 대한 대기 시간 변수는 innodb_lock_wait_timeout이며 , 기본 50초로 설정되어 있다.

1. 개요

 우리가 설치하고, 쿼리를 작성하는 MySQL 서버는 크게 두 엔진으로 구성된다. 하나는 MySQL 엔진, 하나는 스토리지 엔진. 스토리지 엔진은 데이터를 저장하는 하드웨어와 관련된 엔진이고, MySQL 엔진은 그 외 알쏭달쏭한 모든것이라고 생각하면 된다. 

뭔 개소린가 싶겠지만 일단은 이렇게 이해하고 시작해보도록 하자. 이게 정신건강에는 좋은것같다.

2. MySQL 전체 구조 핥아보기

 상당히 친해보이는 두 엔진이 MySQL 어디에 속하는지 알아보자. 아래는 Real MySQL8.0 에 기재된 MySQL 서버 구조이다.

 우리의 몸에 여러 기관들이 있듯이 하나의 엔진에도 여러 요소들이 존재한다. 각 요소들을 살펴보기 전에 그림에 나온 MySQL 서버의 전체 구조를 이해해보자.

3. MySQL 서버 전체 구조

MySQL 서버 전체 구조는 크게 프로그래밍 API, MySQL 서버, 운영체제 하드웨어로 나뉜다.

1) 프로그래밍 API 

MySQL 서버로 들어오는 요청에 대한 API를 말한다. Java 진영의 백엔드 개발자 입장에서는 JPA, MyBatis 를 사용해 MySQL 서버로 연동하는 부분이라 생각하면 된다.

2) MySQL 서버

우리가 배워야할 MySQL 엔진과 스토리지 엔진으로 구성된 서버이다. 생소한 용어들이 대부분이나 InnoDB는 뭔가 익숙하다. 일단 넘어가자.

3) 운영체제 하드웨어

DB 데이터는 운영체제 내 디스크에 저장된다. 단순 DB 데이터 뿐 아니라 로그파일도 저장된다.

4. MySQL 엔진 요소

1) 커넥션 핸들러 

 MySQL을 설치하고 가장 먼저 MySQL 서버에 접속한다. 접속에 성공하면 MySQL 서버와 접속한 클라이언트 간 커넥션이 생성된다. 커넥션 핸들러는 이러한 커넥션을 생성하고 클라이언트의 쿼리 요청, 즉, 커넥션에 관한 처리들을 핸들링한다.

 여기서 중요한건 '쿼리 요청을 핸들링'하는 것이다. 커넥션 핸들러가 쿼리를 분석하여 '처리'하진 않는다. 쿼리 요청이 오면 쿼리를 처리하는 어떤 녀석에게 "select 쿼리 처리해주세요!, insert 쿼리 처리해주세요!" 처럼 요청할 뿐이다.

2) SQL 인터페이스

DML, DDL, 프로시져, 뷰, 커서, 트리거, 함수 등의 지원을 위한 인터페이스를 말한다.

3) 쿼리 파서 (SQL 파서)

 커넥션 핸들러로부터 들어온 요청을 처리하며, 요청받은 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어준다. 이러한 트리형태의 데이터를 '파서 트리'라고 하며, 이 과정에서 문법 오류를 체크한다.

4) SQL 옵티마이저

 파서 트리를 효율적으로 처리하기 위한 실행 계획을 결정한다. 여기서 결정된 실행 계획에 따라 쿼리 처리 속도나 비용이 달라지게 된다. MySQL 뿐만 아니라 모든 DBMS에서도 쿼리의 실행계획을 수립하는 옵티마이저를 갖고 있다. 실행 계획에 대해 잘 이해하여 쿼리의 불합리한 부분을 찾아낸다면 옵티마이저의 최적화에 도움을 줄 수 있을것이다.

 추후 실행계획에 대해서 공부한 후 포스팅하도록 하겠다.

5) 캐시

MySQL 에서는 InnoDB Buffer Pool, Table Open Cache, Thread Cache 캐시가 사용된다. 캐시에 대한 자세한 내용은 다음에 다루도록 하겠다.

* 제거된 쿼리 캐시
 MySQL 8.0 부터 쿼리 캐시 기능이 제거됐다. 쿼리 캐시는 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 매우 중요한 역할을 담당했었다. SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하도록 동작했다. 하지만 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들을 모두 삭제해야 했다. 이는 심각한 동시 처리 성능 저하, 버그를 유발해 결국 제거됐다.

6) 버퍼

Join, Order By, Group By, Select 등의 쿼리 실행 시 사용되는 임시 메모리 공간을 의미한다. 종류로 Sort Buffer, Join Buffer, Read Buffer 가 있다. 각각에 대해 알아보자.

- Sort Buffer

 ORDER BY나 GROUP BY를 사용할 때 사용되는 버퍼로, 정렬에 필요한 데이터를 메모리에 저장하고, 이 안에서 정렬을 수행한다. 만약 정렬할 데이터가 Sort Buffer에 담기지 않을 경우 일부 데이터를 디스크의 임시 파일에 저장하고, 메모리와 디스크를 함께 사용해 정렬한다.

* ORDER BY 시 Select 절에는 꼭 필요한 컬럼만 기재하자
Order By 사용 시 Select 절 "*" 를 넣거나 굳이 필요없는 컬럼 추가한다면 Sort Buffer 의 사이즈를 초과할 수 있다. 이 경우 디스크에 임시 파일 형태로 데이터들이 저장될것이고 많은 I/O 비용을 들이는 메모리&디스크 정렬이 수행되기 때문이다.

- Join Buffer

인덱스가 없는 조인 작업을 수행할 때 사용하는 버퍼이다. 두 테이블을 조인할 때 보다 작은 테이블의 데이터를 Join Buffer에 저장하고, 큰 테이블의 데이터를 반복해서 비교하는 방식이다. 만약 테이블의 데이터가 Join Buffer에 담기지 않을 경우 여러번에 나누어 조인을 수행한다. 이는 성능 저하를 초래한다.

* Join Buffer에 의존하기보단 인덱스 기반 조인을 사용하자
일반적으로 Join Buffer에 의존한 조인보다 인덱스 기반의 조인이 성능면에서 우수하다. 키나 인덱스로 설정되지 않는 컬럼에 대한조인 시, 속도가 느리다면 Join Buffer를 추가하는것보다 인덱스 설정을 고려하자. 

- Read Buffer

 테이블의 데이터를 순차적(Full Scan)으로 읽을 때 사용되는 버퍼이다. 규모가 큰 테이블일수록 버퍼의 효율이 높아진다.

* 규모가 클수록 효율이 높은 이유
 Full Scan이 발생하면 MySQL은 디스크에서 테이블 데이터를 한번에 읽어오는게 아닌 조금씩 읽어온다. 이 과정에서 I/O 작업이 발생하는데, 읽어야할 테이블의 데이터가 많을 수록 I/O 작업은 늘어나게 된다. Buffer를 사용하게 된다면 조금씩이 아닌 버퍼 사이즈만큼 읽어올 수 있으므로 버퍼의 효율이 높은것이다. read_buffer_size 옵션으로 조절 가능하며 기본 값은 128KB이다.

5. MySQL 쿼리 실행 구조

스토리지 엔진을 알아보기 전 MySQL 엔진에서 일어나는 쿼리 실행 프로세스에 대해 알아보자. 여기서 사용되는 개념은 앞선 MySQL 서버 전체 구조의 요소에 중복된 내용이 있을 수 있으니 복습한다 생각하고 참고하도록 하자.

1) 클라이언트 접속

 클라이언트가 MySQL 서버에 접속하기 위해 ID/PW를 입력한다. 입력하는 시점에  MySQL 네이티브 프로토콜을 통해 MySQL 서버와 TCP/IP 기반의 커넥션이 맺어지게 된다. 이때 커넥션이 생성된다는 뜻인데, 정확히는 '임시 커넥션'이 생성되며, ID/PW 인증이 성공할 경우 완전한 커넥션이 맺어지게 된다.

2) 쿼리 요청 처리

 클라이언트가 쿼리를 입력하면 커넥션 핸들러가 쿼리 요청을 처리하기 위해 쿼리를 쿼리 파서에게 전달한다.

3) 파서 트리 생성 및 문법 오류 체크

 쿼리 파서는 받은 쿼리를 토큰으로 분리해 파서 트리를 생성한다. 추가로 이 과정에서 문법 오류를 체크한다. 문법에 이상이 없으면 전처리기에게 파서 트리를 전달한다.

4) 개체 검사 및 권한 체크

 전처리기는 파서 트리를 분석해 쿼리 문장에 구조적인 문제점이 있는지 확인한다. 각 토큰을 테이블 명, 컬럼 명, 내장 함수 명 등과 같은 개체에 매핑하여 유효성을 검사하고, 접근 권한을 확인한다. 실제 존재하지 않거나, 권한 상 접근할 수 있는 개체의 토큰은 이 단계에서 체크한다. 그 후 옵티마이저에게 전달한다.

5) 실행 계획 수립

 옵티마이저는 쿼리를 분석해 실행 계획을 수립한다. 하지만 계획을 할당하고, 이행하는 주체는 따로 있다. 실행 엔진과 핸들러인데, 이들의 관계를 회사로 비유하면 아래와 같다.

 - 옵티마이저 : 회사의 경영진 (회사의 계획 수립)

 - 실행 엔진 : 중간 관리자 (계획을 받아 업무를 할당)

 - 핸들러 : 실무자 (업무를 이행)

 이 단계에서는 옵티마이저가 실행 엔진에게 실행 계획을 전달한다.

6) 핸들러에게 처리 요청

 실행 엔진은 (옵티마이저가 만든) 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러의 요청의 입력으로 연결하는 역할을 수행한다.

7) 핸들러의 작업 처리

 핸들러는 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하거나 읽어 오는 역할을 담당한다. 즉, 핸들러는 스토리지 엔진을 의미하며, MyISAM 테이블을 조작하는 경우에는 핸들러가 MyISAM 스토리지 엔진이 되고, InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 스토리지 엔진이 된다.