[MySQL] DBMS를 구성하는 물리엔진과 내부 프로세스

 

 

다음은 '업무에 바로 쓰는 SQL 튜닝' 책을 읽고 작성한 내용입니다.

 

---

 

MySQL 이라는 DBMS 는 데이터를 저장하고, 저장된 데이터를 가공하는 연산을 수행한다.

사용자는 DB 에서 원하는 데이터를 가져오고자 SQL 문을 수행한다

 

실행된 SQL 문은 MySQL 엔진에 있는 파서(parser) 가

• 문법에러가 있는지
• DB에 존재하는 테이블 대상으로 SQL 문을 작성했는지

와 같은 사항들을 문법과 구문으로 검사한다

 

그 이후 옵티마이저(optimizer)는 사용자가 요청한 데이터를 빠르고 효율적으로 찾아가는 전략적 계획을 수립한다.

이 계획을 토대로 스토리지 엔진에 위치한 데이터까지 찾아간 뒤 해당 데이터를 MySQL 엔진으로 전달한다.

 

MySQL 엔진은 전달된 데이터에서 불필요한 부분을 필터링 (변경, 제거) 하고 필요한 연산을 수행한 뒤 사용자에게 최종 결과를 알려준다. 

 

 

 

▶ 스토리지엔진

InnoDB, MyISAM, Memory 등의 스토리지 엔진은 사용자가 요청한 SQL 문을 토대로 DB 에 저장된 디스크나 메모리에서 필요한 데이터를 가져오고, 해당 데이터를 MySQL 엔진을 보내준다. 데이터를 저장하는 방식에 따라 각각의 스토리지 엔진을 선택해서 사용할 수 있다. 

 

▷ InnoDB 엔진

OLTP 환경에서 사용되는 MySQL 의 스토리지 엔진이다. 일반적으로는 OLTP 환경이 대다수인만큼 주로 InnoDB 엔진을 사용한다.

 

OLTP(Online Transaction Processing) 

데이터베이스 시스템에서 실시간으로 대량의 작은 트랜잭션을 처리하는 환경을 뜻한다. OLTP 환경은 은행거래, 예약시스템, 주문처리와 같은 실시간 트랜잭션을 처리하는 곳에서 사용된다. 

 

▷ MyISAM 엔진

대량의 쓰기 트랜잭션이 발생하면 사용된다

 

▷ Memory 엔진

메모리 데이터를 로드해서 빠르게 읽는 효과를 낼 수 있다

 

 

 

▶ MySQL 엔진

 

▷ MySQL 엔진의 역할

 

MySQL 엔진은 SQL문의 시작 및 마무리 단계에 관여하며, 스토리지 엔진으로부터 필요한 데이터만을 가져오는 핵심 역할을 담당한다. 

• SQL 문법검사
• 적절한 오브젝트 활용 검사
• SQL 문을 최소 단위로 분리해서 원하는 데이터를 빠르게 찾는 경로를 모색
• 스토리지 엔진으로부터 전달받은 데이터 대상으로 불필요한 데이터를 제거/가공/연산

 

 

 

SQL 실행과정에서 핵심 역할을 수행하는 오브젝트는 크게 파서, 전처리기, 옵티마이저, 엔진실행기로 구분된다. 

 

▷ 파서(parser)

사용자가 SQL 문을 수행하면

파서는 MySQL이 이해할 수 있는 최소 단위로 구성요소를 분리하고 해당 구성요소를 트리로 만든다

 

트리

• 파서는 트리를 만드는 과정에서 문법 오류가 있는지 검토한다
• 트리의 최소 단위는 <,>,= 드의 기호나 SQL 키워드로 분리한다
• 트리에 허용되지 않는 문법이 포함된다면 에러가 발생하며 실행이 종료된다

 

▷ 전처리기(preprocessor)

파서에서 생성한 트리를 토대로 아래 내용을 검증한다

 

• SQL문에 작성된 테이블, 열, 함수, 뷰와 같은 오브젝트가 실질적으로 이미 생성된 오브젝트인지
• 존재하지 않은 열을 포함하지 않는지
• 조회 권한이 없는 테이블을 조회하는지

전처리기는 검증 중 유효하지 않은 오브젝트가 있거나 권한이 없는 오브젝트를 호출하면 에러를 발생해 사용자에게 표시한다

 

▷ 옵티마이저(optimizer)

MySQL의 핵심 엔진 중 하나이다. 

트리를 구성하는 오브젝트의 데이터를 효율적으로 가져오기 위해 

가장 적은 비용으로 데이터를 검색하는 방법에 대한 실행계획을 수립하며

전달된 파서트리를 토대로 필요하지 않은 조건은 제거하거나 연산 과정을 단순화한다. 

 

실행계획은 아래와 같이 수립한다. 

• 어떤 순서로 테이블에 접근할지
• 인덱스를 사용할지, 사용한다면 어떤 인덱스를 사용할지
• 정렬할때 인덱스를 사용할지 아니면 임시테이블을 사용할지

단 실행계획으로 도출할 수 있는 경우의 수가 지나치게 많을 때는 시간이 오래걸리는 만큼 모든 실행 계획을 판단하지는 않는다. 

이는 옵티마이저가 선택한 최적의 실행계획이 최상의 실행계획이 아닐 가능성도 있다는 것을 의미한다.

 

▷ 엔진실행기(engine executor)

MySQL엔진과 스토리지 엔진영역 모두에 걸치는 오브젝트이다. 

옵티마이저가 수립한 실행계획으로 스토리지엔진에서 필요한 데이터를 가져온다. 

이후에 엔진 실행기는 스토리지 엔진을 통해서 가져온 데이터 중 불필요한 데이터를 필터링해서 사용자가 원하는 결과를 전달한다.