MySQL 쿼리 동작원리(실행계획, 인덱스)

 

MySQL 쿼리 동작 원리

 

Mysql에서 쿼리가 어떻게실행되는지 알아보자

 

1. 사용자로 부터 요청된 SQL 문장을 잘게 쪼개서 Mysql 서버가 이해할 수 있는 수준으로 분리
2. SQL의 파싱정보를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
3. 두번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 *스토리지 엔진으로부터 데이터를 가져온다.

 

스토리지 엔진이란?

데이터베이스 관리 시스템(DBMS)이 데이터베이스에 대해 데이터를 삽입,추출,업데이트 및 삭제하는데 사용하는 기본 소프트웨어 컴포넌트이다.

스토리지 엔진은 DB에서 데이터를 어떠한 방식으로 저장하고 접근할 것인지에 대한 기능을 제공해주는데, 스토리지 엔진의 특성에 따라 데이터 접근이 얼마나 빠르고, 안정적인지 차이점이 있다.

 

Mysql의 스토리지 엔진은 5개가 있다. 각각의 엔진 특징은 아래의 참조에서 확인할 수 있다.

 

현재는 스토리지 엔진의 특징을 완벽히 파악하고 있지 않기 때문에 default인 InnoDB를 사용하고 있다.

 

 

다시 본문으로 돌아가 첫 번째 단계를 SQL파싱 이라고 하며 Mysql 서버의 SQL파서 라는 모듈로 처리한다. 이 단계에서 SQL의 문법오류를 걸러내고 *SQL파스트리가 만들어진다. Mysql 서버는 SQL 문장 그자체가 아닌 SQL 파스트리를 이용해 쿼리를 실행한다.

 

파스트리란 ?

파스트리(Parse Tree) 혹은 파싱트리란 올바른 문장에 대해 트리 구조로 나타낸 것을 말한다. 즉, SQL파스트리는 SQL문에 대한 구문 분석을 수행하여 파스트리구조로 저장하는 것을 말한다.

 

두 번째 단계에서는 최적화 및 실행 계획 수립이 이루어지고 Mysql 서버의 옵티마이저 에서 처리한다. 

두 번째 단계가 완료되면 쿼리의 실행계획이 만들어진다.

 

세 번째 단계는 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, Mysql 엔진에서는 스토리지엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.

 

첫 번째 단계와 두 번째 단계는 거의 Mysql 엔진에서 처리하며, 세 번째 단계는 Mysql 엔진과 스토리지 엔진이 동시에 참여해서 처리한다.

 

 

아래 그림은 SQL 파서와 옵티마이저 가 Mysql 전체적인 아키텍처에서 어느 위치에 있는지 보여준다.

 

 

간단하게 Mysql에서 쿼리가 어떻게실행되는지 알아보았다.

 

 

실행 계획

추가로 실행 계획에 대해 살짝 알아보자.

 

실행계획을 보기 위해서는 아래 처럼 select의 쿼리 문 앞에 explain 명령을 사용하면된다.

 

EXPLAIN SELECT      
*
FROM  SEND_HIS_LOG T1
WHERE T1.ADVER_ID = 'bocaci'
AND   T1.CAMP_NO  = '1289';

 

인덱스 생성전

 

현재 테이블에는 아무런 인덱스도 생성되어 있지 않다.

 

여튼, 현재 실행계획을 조회했을때 나오는 데이터의 컬럼의 뜻과 로우에 나오는 데이터가 무슨 뜻인지 알아보자.

현재는 1 row 만 출력이 되었지만 쿼리 문장에서 사용된 테이블(서브 쿼리로 임시 테이블을 생성한 경우 그 임시 테이블까지 포함)의 갯수만큼 출력된다.

 

또한 UPDATE, INSERT, DELETE 문장에 대해서는 실행 계획을 확인 할 방법이 없기 때문에 WHERE 조건절만 같은 SELECT 문장을 만들어서 대략적으로 계획을 확인해 봐야 한다.

 

 

조회시 가장 많이 사용되는 컬럼을 이용하여 인덱스를 생성해보자.

 

범위가 넓은 즉 분포도가 넓은 ADVER_ID를 앞에 두고 그 다음으로 CAMP_NO를 뒤에 두어 ADVER_ID로 넓은 분포도에서 범위가 좁은 분포도를 가진 CAMP_NO로 인덱스를 생성하였다.

 

create index SEND_HIS_LOG_IDX_02 on CRM.SEND_HIS_LOG(ADVER_ID,CAMP_NO);

 

인덱스 생성 후

인덱스 생성전의 그림과 다르게 컬럼에 값들이 들어가 있는 것을 확인 할 수 있다.

 

각 컬럼별로 의미하는 내용은 참조 블로그에 정리되어 있다.

 

 

 

 

참조

---

https://thefif19wlsvy.tistory.com/26

데이터베이스 엔진이란?

https://enterone.tistory.com/232

[MySQL] MySQL 실행 계획