MySQL 서버 = MySQL 엔진 + 스토리지 엔진
MySQL 엔진 = 클라이언트 접속 + 스토리지 엔진에 쿼리를 요청하는 커넥션 핸들러, SQL파서, 전처리기, 옵티마이저
스토리지 엔진 = 디스크에 저장, 불러오기, SQL 문장 분석(파서와 다름), SQL 최적화, InnoDB의 경우 캐싱 기능(버퍼 풀)
MySQL 스레딩 구조 [InnoDB 기준]
포그라운드 스레드
- 사용자와 커넥션을 맺고 사용자 수만큼 존재
- 읽기 : MySQL의 버퍼나 캐시에 데이터가 존재하면 가져오고, 없다면 디스크로부터 읽는다.
- 쓰기 : 버퍼나 캐시까지는 직접 쓰기, 버퍼로부터 디스크까지 쓰기는 백그라운드 스레드 담당
백그라운드 스레드
- 로그 스레드 : 로그를 디스크에 쓰기
- 쓰기 스레드 : 버퍼 -> 디스크 쓰기
INSERT, UPDATE, DELETE는 쓰기 지연으로 쿼리 완료를 대기하지 않는다.
MySQL 메모리 영역
글로벌 메모리 영역 : 모든 스레드에의해 공유 (스레드 수에 상관없이 하나만 할당)
- 테이블 캐시
- 버퍼풀
- 어댑티브 해시 인덱스
- 리두 로그 버퍼
로컬 메모리 영역(세션 혹은 클라이언트 영역) : 스레드 별로 독립적으로 할당
- 정렬 버퍼 (동적으로 할당)
- 조인 버퍼 (동적으로 할당)
- 바이너리 로그 캐시
- 네트워크 버퍼
MySQL 엔진의 쿼리 실행 구조
- 쿼리 파서 : 쿼리를 토큰으로 분리해 트리 형태 구조로 변환 -> 문법오류를 검사
- 전처리기 : 구조적 문제점 확인 -> 테이블 이름, 컬럼 이름, 객체 존재 여부, 권한 검사
- 옵티마이저 : 쿼리를 어떻게 효율적으로 처리할 것인지를 결정
- 실행 엔진 : 핸들러를 이용해 명령을 전달
- 스토리지 엔진(핸들러) : 디스크에 저장하고 쓰는 역할
InnoDB 스토리지 엔진 아키텍처
프라이머리 키에 의한 클러스터링
InnoDB의 모든 테이블은 프라이머리 키를 기준으로 물리적으로 정렬된다.
테이블 전체가 프라이머리 키에 대한 인덱스 페이지라 봐도 무방하다.
세컨더리 키는 PK를 논리적 주소로 사용한다.
MVCC(Multi Version Concurrency Control) [InnoDB 기준]
MVCC = 잠금을 사용하지 않는 일관된 읽기
MVCC는 읽기에 잠금을 걸지 않고 언두 로그를 이용해 하나의 레코드를 여러 버전으로 동시에 관리해 동시성을 높이고 일관된 읽기를 제공한다.
InnoDB의 MVCC를 이해하기 위해서는 메모리 영역과 디스크 영역을 구분해야 한다.
메모리 영역 : 버퍼 풀 + 언두 로그
디스크 : 실제 데이터 파일
시나리오
INSERT -> COMMIT : 디스크와 버퍼 풀에 레코드를 기록
UPDATE : 커밋 여부와 관계없이 버퍼 풀에는 새로운 값으로 변경, 변경 전 값을 언두 로그에 복사
이때 디스크에 새로운 값이 반영 됐을 수도 있고 안 됐을 수도 있다. (체크포인트에 따라 다름)
이후 SELECT 문으로 해당 레코드를 읽는 경우
1. READ_UNCOMMITTED : 커밋 여부와 상관없이 무조건 버퍼 풀에서 변경된 데이터를 읽어온다.
2. READ_COMMITTED 이상의 격리 수준 : 커밋하지 않았다면 언두 영역의 데이터를 읽는다.
트랜잭션이 매우 길어지면?
관리해야 하는 언두 영역이 커지게 된다. 언두 영역은 메모리 영역이기 때문에 메모리를 많이 소모한다.
커밋하면 무조건 언두영역 삭제?
언두 영역을 참고하는 트랜잭션이 하나도 없다면 삭제한다.
자동 데드락 감지
교착상태에 빠진 트랜잭션을 주기적으로 검사하고 교착상태에 빠졌다면 둘 중 하나를 강제 종료한다. 기준은 언두로그를 적게 가진 트랜잭션이다. (당연하게도 언두 로그가 적어야 롤백 처리해야 할 양이 적기 때문)
