아래는 mysql의 구조를 최대한 간단히 나타낸 그림이다.

MySQL의 아키텍처를 간단하게 표현하자면 위와 같다. 이 아키텍처에서 SQL문이 어떻게 실행되는 지 과정을 알아보자.

즉, MySQL 서버안에 MySQL 엔진과 스토리지 엔진이 존재하는 형태이다. 

개발자가 DB에 SQL 요청을 보낸다.

MySQL 엔진에서 옵티마이저가 SQL문을 분석한 뒤 빠르고 효율적으로 데이터를 가져올 수 있는 계획을 세운다. 어떤 순서로 테이블에 접근할 지, 인덱스를 사용할 지, 어떤 인덱스를 사용할 지 등을 결정한다. (옵티마이저가 세운 계획은 완벽하지 않으며, 잘못된 최적화를 하는 경우도 있다. 따라서 SQL 튜닝이 필요하다.)

옵티마이저가 세운 계획을 바탕으로 스토리지 엔진에서 데이터를 가져온다. (DB 성능에 문제가 생기는 대부분의 원인은 스토리지 엔진으로부터 데이터를 가져올 때 발생한다. 데이터를 찾기가 어려워서 오래 걸리거나, 가져올 데이터가 너무 많아서 오래 걸린다. SQL 튜닝의 핵심은 스토리지 엔진으로부터 되도록이면 데이터를 찾기 쉽게 바꾸고, 적은 데이터를 가져오도록 바꾸는 것을 말한다.)

MySQL 엔진에서 정렬, 필터링 등의 마지막 처리를 한 뒤에 클라이언트에게 SQL 결과를 응답한다.

옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진이다.

즉, 개발자가 작성한 SQL을 읽은후 DBMS에서는 옵티마이저를 기반으로 실행계획을 세워서 쿼리를 실행한다.

쿼리의 실행절차를 크게 나누게 되면 아래의 4단계로 볼 수 있다.

Parsing > Optimization > Generation > Execution

실질적으로 개발자가 작성한 SQL이 실행계획이라 생각되겠지만, DBMS는 우리가 세운 실행계획을 쪼개서 SQL파서를 통해 옵티마이저로 파싱한다.

우리가 전송한 SQL을 쪼개서 구성요소를 파악하고 이를 파싱해서 파싱트리를 만든다.

대부분의 SQL문장의 문법적인 검사는 파싱단계에서 이루어진다. SQL문법이 잘못되었다면 이 단계에서 걸러지며, 정상적이라면 SQL파싱 트리가 만들어진다.

파싱단계에서 만들어진 SQL파싱트리를 기반으로 옵티마이저가 가진 여러가지 동작을 수행한다.

Query Transformer: 파싱된 SQL을 보고 같은 결과를 도출하되, 좀 더 나은 실행 계획을 갖는 SQL로 변환이 가능한지를 판단하여 변환 작업을 수행한다. 이 때 불필요한 조건을 제거  하거나 복잡한 연산을 단순화 시키는 등의 작업을 수행한다.

Estimator : 시스템 통계정보를 딕셔너리로부터 수집하여 SQL을 실행할 때 소요되는 총비용을 계산한다.

Plan Generator : Estimator를 통해 계산된 값들을 토대로 후보군이 되는 실행계획을 도출한다.


옵티마이저가 생성한 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드나 프로시저 형태로 포맷팅한다.

포맷팅된 SQL을 실행하고 결과를 사용자에게 전달한다.

옵티마이저는 규칙 기반 최적화(Rule-Based Optimizer, RBO) 혹은 비용 기반 (Cost-Based Optimizer, CBO)를 통해 실행 계획을 수립한다.

규칙 기반 최적화 (Rule-Based Optimizer, RBO)

실행 속도가 빠른순으로 규칙을 세워두고 우선순위가 높은 방법을 채택하는방식이다.

RBO는 쿼리 실행 계획을 만들 때 일반적으로 사용되는 JOIN, WHERE 등의 SQL 구문에 대한 고정 규칙을 적용한다.

일반적으로 규칙을 세워둘 때 실행 속도가 빠른순으로 규칙을 세워두고 우선순위가 높은 방법을 채택하여 실행계획을 세운다.

규칙이란 액세스 경로별 우선순위로서, 인덱스 구조, 연산, 조건절 형태가 순위를 결정짓는 주요인이 된다.

DBMS에서 제공하는 RBO의 경우, 대부분의 경우 규칙의 우선순위를 사용자가 변경할 수 없다. 그러나 일부 DBMS(오라클의 ORSERED hint)에서는 사용자가 규칙의 우선순위를 변경할 수 있는 설정을 제공하기도 한다.

비용 기반 최적화 (Cost-Based Optimizer, CBO)

CBO는 실행 계획을 선택하기 위해 쿼리를 수행하는데 소요되는 일 량 또는 시간을 기반으로 최적화하는 방식이다.

CBO는 실행 계획을 최대 2천 개까지 세운 뒤 비용이 최소한으로 나온 실행 계획을 수행하게 된다.

이때 실행계획을 수립할 때 판단 기준이 되는 비용은 예상치다. 따라서 CBO는 비용을 예측하기 위해 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보 및 시스템 통계정보(CPU 속도, 디스크 I/O 속도 등)를 이용한다.

통계정보가 없는 경우 비효율적인 실행계획을 생성할 수 있으므로, 정확한 통계정보를 유지하는 것이 중요하다.

다시 한번 말하지만, 옵티마이저는 만능이 아니다. 효율성이 낮은 실행 계획을 수립한다면 개발자가 적절한 인덱스 컨트롤링을 통해 쿼리의 성능을 높여야 한다.

(실행계획의 type, keys, rows, time등의 지표를 잘 보며 효율적인 쿼리 튜닝이 필요하다.)

id는 실행순서, type은 실행 계획의 종류, key는 인덱스, rows는 접근한 데이터의 숫자 (정확한 숫자는 아니며 예측이다.) 

참고로, Maria DB 에서는 아래와 같이 JSON FORMAT으로도 볼수 있다.

ANALYZE FORMAT = JSON SELECT * from user_info, creator where user_info.member_index > 10 and creator_index > 2

type, key, rows 및 time의 지표를 위주로 분석하고, Full Table Scan이 일어난 경우 혹은 rows의 숫자가 비정상적으로 높은 경우를 관측하여, 인덱스를 걸어주는 방식으로 튜닝이 가능하다.

다시 한번 말하지만, 실행 계획은 만능이아니다. Optimizer가 잘못된 CBO를 통해 비효율적인 방식의 실행 계획을 수립하거나, 인덱스를 걸어주었는데도 (SubString등의 함수로 인덱스 컬럼을 가공하여 SQL을 작성한 경우) 인덱스를 인지하지 못하는 등의 다양한 경우의 수가 존재한다.

아래는 또 다른 쿼리 튜닝 예제이다.

특정 기간사이에 로그인한 유저를  보여주는 간단한 쿼리이다

실행계획을 보면, 아래와 같다.

id, type,keys, rows, time등의 지표를 유의 깊게 봐야한다.

full table 스캔이 일어나고 있는 것을 확인 할수 있다.

실행계획을 통한 SQL 튜닝

Body Parser란?

Express JS란?

Request, Response

Resposne - send(), json(), end()

컨트롤러, 라우터

링크

링크

실행계획을 통한 SQL 튜닝