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 스토리지 엔진이 된다.