🔫 우기의 MySQL 아키텍쳐

우기의 MySQL 아키텍처

[10분 테코톡] 🔫 우기의 MySQL 아키텍처 를 듣고 정리함.

MySQL 아키텍쳐

MySQL 접속 클라이언트

대부분의 프로그래밍 언어에 대해 접속 API를 제공한다. 쉘에서도 쉘 스크립트를 통해 MySQ을 이용할 수 있따.

MySQL 엔진

• 클라이언트 접속과 SQL 엔진을 처리한다.
• 쿼리파서, 전처리기(Preprocessor), 옵티마이저, 실행 엔진 등으로 이루어진다. 이중 옵티마이저가 핵심이다.
• 옵티마이저 : 요청된 SQL문을 최적화해서 실행시키기위해 실행계획을 짜는 중요한 역할을 한다.

MySQL 스토리지 엔진

• 데이터를 실제로 디스크에 저장하거나 디스크에 저장된 데이터를 읽어오는 역할을 한다.
• 옵티마이저가 작성한 실행 계획에 따라서 스토리지 엔진을 적절히 호출해서 쿼리를 실행한다.
• MySQL엔진이 스토리지를 호출할때 사용하는 API를 핸들러 API라고 한다. 핸들러 API는 커스텀 구현 가능하여 나만의 스토리지 엔진을 설계할 수도 있다.

운영체제, 하드웨어

실제 테이블의 데이터와 로그 데이터를 파일로 저장하는 운영체제 파일 시스템과 하드웨어이다.

쿼리의 실행 과정

쿼리 캐시

• SQL 실행 결과를 메모리에 캐싱하는 역할
• 동일 SQL 실행 시 이전 결과 즉시 반환
• 테이블의 데이터가 변경되면 캐싱된 데이터 삭제 필요(동시 처리 성능 저하)
• MySQL 8.0부터 완전히 제거됨

사용자가 SQL 요청을 날리면 가장 먼저 만나게 되는 모듈로 쿼리 요청 결과를 캐싱한다. 동일한 요청에 대한 결과를 빠르게 받을 수 있었으나 여러가지 단점이 존재한다. 만일 캐시하고 있는 데이터의 테이블이 변경된다면 더 이상 쓸모 없어지게된 캐싱 데이터를 삭제해야한다. 이렇게 캐싱 데이터가 삭제될 떄 마다 쿼리 캐시에 접근하는 쓰레드에 락이 걸리는데 이는 심각한 동시처리 성능 저하를 야기한다.

쿼리 파서

• SQL 문장을 토큰으로 쪼개서 트리로 만듦

• 이 과정에서 쿼리 문장의 기본 문법 오류를 체크

  쿼리 파서는 sql 명령의 syntax 오류를 검출한다 그리고 sql 문장을 의미있는 단위의 토큰으로 쪼갠 다음에 트리로 만든다. 이 트리를 parse 트리라고 부른다. MySQL은 내부적으로 이 parse 트리를 사용하여 쿼리를 실행한다.

전처리기

• Parse Tree를 기반으로 SQL의 문장 구조를 체크
• Parse Tree의 토큰이 유효한지 체크

전처리기(Preprocessor)는 쿼리 파서가 만든 Parse Tree를 기반으로, 쿼리 문장에 구조적인 문제를 검사한다. 파스 트리의 토큰을 하나씩 검사하면서 토큰에 해당하는 테이블 이름이나 칼럼 등이 실제로 존재하는지 체크하고 접근 권한도 체크한다.

옵티마이저

• SQL 실행을 최적화해서 실행 계획을 수립
• 규칙 기반 최적화
  • 옵티마이저에 내장된 우선순위에 따라 실행 계획 수립
• 비용 기반 최적화
  • 작업의 비용과 대상 테이블의 통계 정보를 활용해서 실행 계획 수립

쿼리 실행 계획을 만듦

쿼리 실행 엔진

• 옵티마이저가 만든 실행 계획대로 스토리지 엔진을 호출해서 레코드를 읽고 씀.

스토리지 엔진

• 쿼리 실행 엔진이 요청하는 대로 데이터를 디스크로 저장하고 읽음
• 핸들러 API에 의해 동작(핸들러라고도 불림)

• 플러그인 형태로 제공, 사용자가 원하는 것을 선택 가능

  (단점 : 플러그인끼리는 서로 통신할 수 없고, 플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 캡슐화를 위반한다) → MySQL 8.0부터는 컴포넌트 이를 개선한 아키텍쳐를 제공한다.

• 대표적으로 InnoDb, MyISAM 등이 있다.

InnoDB 스토리지 엔진, 특징들

• Primary Key에 의한 클러스터링
• 트랜잭션 지원 (MVCC, 리두로그 & 언두 로그, 레코드 단위 잠금)
• InnoDB 버퍼풀 & 어댑티브 해시 인덱스

1. Primary Key 에 의한 클러스터링

• 레코드를 PK 순으로 정렬해서 저장
• PK 인덱스 자동 생성

PK 기준으로 데이터를 ‘정렬’ 하여 묶여서 한 군데 저장되기 때문에 PK 기반 범위 검색이 매우 빠르다.

단점으론, 쓰기 성능은 매우 저하된다. 왜냐하면 모든 레코드를 재정렬하는 비용이 수반되기 때문이다.

일반적인 웹 서비스는 쓰기보다 읽기 요청이 훨씬 많기 때문에 쓰기 성능을 포기하고 읽기 성능을 얻는 클러스터링을 하는 것이 합리적이다.

참고로 InnoDB에서는 Pk 를 지정하지 않으면 내부적으로 PK를 자동생성해서 클러스터링한다. 내부적으로 생성된 PK 는 사용자가 직접 쓸 수 없다. 따라서 InnoDB에서 테이블을 설계할 때에는 PK를 직접 설정해주는 것이 좋다.

트랜잭션 - MVCC(Multi Version Concurrency Control)

InnoDB는 기본적으로 Commit, Rollback 기능을 제공하며 이에 더해 MVCC라는 기능도 제공한다.

InnoDB 버퍼풀 : 변경된 데이터를 디스크에 반영하기 전까지 잠시 버퍼링 하는 공간

언두로그 : 변경되기 이전 데이터를 백업 해두는 공간

예시)

먼저 insert query를 날리고 쿼리 커밋을 한 상태이다.

이상태에서 유재석의 취미를 코딩으로 변경하는 UPDATE query를 날린다.

버퍼 풀에 있는 유재석 레코드의 취미는 그 즉시 코딩으로 변경됨과 동시에 변경 이전 취미인 독서가 언두로그에 복사된다.

이 상태에서 유재석을 조회하면 취미 칼럼은 무슨 값을 반환할까??

→ 정담은 디비에 설정된 트랜잭션 격리 수준에 따라 다르다 이다.

트랜잭션 격리 수준이 만일 READ_UNCOMMITED 라면 취미가 코딩인 유재석 레코드가 버퍼풀에서 조회된다. 만약 트랜잭션 격리 수준이 READ_COMMITED, 또는 REPEATABLE_READ 또는 SERIALIZABLE이라면 언두 로그에 있는 변경되기 이전 데이터인 취미가 독서인 유재석 레코드를 조회한다.

이것이 MVCC multi version concurrency control이다. 예시에서는 다양한 버전이(코딩, 독서)가 버퍼풀과 언두로그에서 동시에 트랜잭션 격리 레벨에 따라 관리되고 있다.

이 기능 덕에 레코드에 잠금을 걸지 않고도 트랜잭션 격리 레벨에 따라 일관된 읽기를 할 수 있다

트랜잭션 - 언두 로그와 리두 로그

언두로그에는 변경되기 이전 데이터를 백업해서 트랜잭션을 보장한다.

예를 들어 롤백을 한다면 언두로그에 백업된 데이터를 그대로 복원하면 된다.

트랜잭션 격리수준도 보장한다.(예시에서처럼)

리두 로그는 트랜잭션의 영속성을 보장해 준다. 리두로그에는 변경되었고 커밋이 완료된 데이터를 백업한다. 하드웨어 또는 소프트웨어 문제로 MySQL이 비정상적으로 종료되면 리두 로그를 통해 데이터를 복원한다.

트랜잭션 - 레코드 단위 잠금

참고하면 좋은 링크

데이터베이스에서 데이터를 변경할 때에는 동시성 문제를 고려해서 레코드에 대한 접근을 막는다(락, 잠금).

이노디비는 레코드 단위로 잠금을 수행하기때문에 동시처리 성능이 좋다

→실제로는 레코드 그 자체를 잠그는 것이 아니라, 인덱스 레코드를 잠그는 것이다.

예시)

박병욱의 취미 칼럼을 코딩에서 축구로 바꾸는 업데이트 쿼리를 날리면 업데이트를 수행하기 위해 데이터베이스는 먼저 박병욱의 레코드를 검색하게 된다. 이때 성씨 인덱스 칼럼을 사용하게된다. 이 상황에서 이노디비는 레코드 자체를 잠그는게 아니라 인덱스 레코드를 잠그는 방식으로 락을 처리한다. 다시 말해, 업데이트의 대상 레코드를 검색할 때 사용된 인덱스 레코드가 잠기게 된다. 검색에 사용된 박씨 레코드 300개 가 모두 잠기게 되는 것이다.

만일 성씨 인덱스가 없더라면??

박병욱 레코드를 찾아내기 위해서 5000명 회원의 데이터를 모두 풀스캔 하게 된다. 즉 5000개의 레코드가 전부 잠기게 되는 것이다.

만일 성씨와 이름 복합 인덱스를 생성했다면 박병욱 레코드 한개만 잠그게 됐을 것이다

인덱스를 어떻게 설정하는지에 따라 레코드의 잠금 범위가, 즉 동시처리 성능에 직접적인 영향이 가므로 이노디비를 사용할 때는 인덱스를 신중하게 설정하는 것이 좋다

버퍼풀

• 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐싱해두는 공간이다.
• 버퍼풀은 쓰기 작업을 지연시켜서 일괄적으로 작업을 처리해주기도 한다.

데이터 캐싱

• 버퍼풀은 sql 요청의 결과를 일정한 크기의 페이지 단위로 캐싱한다.
• 운영체제가 가상 메모리를 효율적으로 사용하기위해 페이징을 하는 것 처럼 데이터 베이스도 테이블 데이터에 대해 페이징을 한다. → 이노디비는 페이지 교체 알고리즘으로 LRU 알고리즘을 사용하고 있다.

쓰기 지연 버퍼

• 버퍼풀은 쓰기 지연 버퍼로도 사용된다. Insert Update Delete 명령으로 변경된 페이지를 더티 페이지라고 부른다. 이노디비는 더티 페이지들을 모았다가 주기적으로 이벤트를 발생시켜서 한번에 디스크에 반영한다.
• 변경된 데이터를 한번에 모았다가 처리하는 이유는 랜덤 I/O를 줄이기 위해서다.
• JPA 영속 컨텍스트의 쓰기 지연 SQL 저장소랑 비슷

어댑티브 해시 인덱스

• 인덱스 키와 페이지의 주소값 쌍으로 구성된 인덱스이다. 페이지에 빠르게 접근하기 위해 사용된다.
• 사용자가 자주 요청하는 데이터에 대해서 이노디비가 자동으로 만들어준다.
• 어댑티브 해시 인덱스를 통해 원하는 페이지에 빠르게 접근할 수 있기 때문에 쿼리를 더빠르게 처리할 수 있다.