MYSQL. Handler API와 Status

j_6367·2022년 3월 5일

mysql8

목록 보기

5/8

기본 개념

InnnoDB와 같은 스토리지 엔진은 Handler API를 구현한다.

MYSQL은 모든 쿼리에서 handler Api를 이용해서 실제 데이터 작업을 처리한다.

이 때 상태변수에 이러한 작업 처리 횟수를 기록해 놓는다.

# 상태변수 초기화
flush status;

# 실행해볼 쿼리
SELECT ~

# 상태변수 조회
show status like 'handler_%'

select에 관련된 주요 상태 변수

Handler_read_first
인덱스 처음 값을 읽은 횟수
Handler_read_key
인덱스로 값 찾은 횟수
Handler_read_last
인덱스 마지막 값을 읽은 횟수
Handler_read_next
인덱스로 값 찾은 후 다음값으로 이동한 횟수
Handler_read_prev
인덱스로 값 찾은 후 이전값으로 이동한 횟수
Handler_read_rnd

테이블에서 고정된 위치 찾은 횟수
Handler_read_rnd_next
테이블 다음 데이터 읽기

Example

1. 인덱스로 검색

SELECT *
FROM salaries
WHERE salary = 100

salary에 인덱스가 걸려있는 경우

인덱스로 salary가 100인 첫번째 값 찾고 ( handler_read_key + 1 )

다음값을 읽어서 salary가 100 만족하는지 확인한다. ( handler_read_next + 1 )

유니크 인덱스인 경우 다음값이 없으므로 handler_read_next가 증가하지 않으며

아닌 경우 아닌 값이 나올 때 까지 다음 값을 검색하므로 찾은 값보다 1 더 많이 증가한다.

2. 조인 (Nested Loop)

SELECT *
FROM employees e
     INNER JOIN dept_emp de
                ON e.emp_no = de.emp_no
WHERE e.emp_no BETWEEN 20001 AND 20010

employees가 드라이빙 테이블이 되는 경우

제일 먼저 emp_no로 20001인 값을 찾는다 (handler_read_key + 1)

emp_no가 20011될 때까지 뒤로가며 찾는다 (handler_read_next + 10)

찾은 employees 행들에 대해 dept_emp인 값을 찾는다.

하나의 emp_no마다 dept_emp를 1회씩 찾고 (handler_read_key + 1)

유니크값이 아니므로 다음 값도 확인해봐야한다. (handler_read_next + 1)

이것을 모든 로우에 대해 반복한다 ( 20001~20010 모두 존재하는 경우 10회 )

해당 쿼리로 handler_read_key 11회, handler_read_next는 21회가 증가한다.

풀스캔

SELECT *
FROM employees
LIMIT 10000

employees가 innodb인 경우 PK가 테이블이므로 시작위치로 포인터 이동 후 10000건 찾으므로

handler_read_first, handler_read_key가 1씩 증가하고 handler_read_rnd_next가 10000 증가하는 듯 하다. handler_read_first와 handler_read_key가 myISAM에서는 증가하지 않았다.

Handler API 직접 사용

테이블에 대해 handler를 open하고 직접 사용해볼 수 있다.

각 인덱스와 테이블에 대해 행을 가리키는 포인터가 있는 듯 하다.

인덱스에서 포인터는 시작부분이나 끝부분, 또는 특정 값으로 검색해서 이동할 수 있다

테이블의 경우 시작부분으로 이동하거나 다음 값으로 이동할 수 있다.

Example

(1) 우선 테이블에 대해 핸들러를 open.

HANDLER employees OPEN

(2) PK 값으로 1건 검색

HANDLER employees READ 'PRIMARY' = (20000)

# pk값이 20000인 행 한건 검색 (limit 없는 경우 1건)
# handler_read_key + 1

(3) 위 (2)에서 이동한 포인터에서 10건을 추가로 읽기

HANDLER employees READ 'PRIMARY' NEXT LIMIT 10

# pk값이 20001~20010인 행이 검색됨
# handler_read_next + 10

(4) 인덱스 이용해서 검색

HANDLER employees READ ix_firstname = ('Parto') LIMIT 10

# firstname 값이 Parto인 행 최대 10건까지 검색됨
# handler_read_key + 1
# handler_read_next + 9 (10건 검색될경우)

(5) 인덱스 없이 테이블 읽기

HANDLER employees READ NEXT LIMIT 10;

# 테이블에 대한 현재 포인터가 없으므로 시작부터 10건 검색됨
# innoDB인 경우 PK=테이블 이지만 pk가 위치한 포인터에서 시작하지 않음
# 하지만 테이블 읽기 할 경우 pk 포인터 초기화됨
# handler_read_rnd_next + 9

(5) 위 (4)에서 사용한 인덱스 추가 검색

HANDLER employees READ ix_firstname NEXT WHERE emp_no < 20000 LIMIT 5;

# emp_no가 20000보다 아래인 값 5건 찾을 때 까지
# (4)에서 실행했던 위치부터 정방향으로 찾는다.
# (4)에서 검색값이었던 Parto가 10건이 넘은 경우 Parto인 행부터 나온다.

(6) PK로 시작값부터 검색

HANDLER employees READ 'PRIMARY' FIRST;
HANDLER employees READ 'PRIMARY' = (10001) # PK 첫번째 값이 10001

# 위 값의 경우 handler_read_first도 1 증가하며
# 두 쿼리 모두 handler_read_key가 1씩 증가한다.