[MySQL] 옵티마이저

 

DBMS에 대해서 입력과 출력에만 집중하는 경우가 많다.
즉, DBMS 서버가 그 결과를 만들기 위해서 내부적으로 무슨 과정을 거쳤는지는 크게 관심을 가지지 않는다.
그런데 AWS 같은 클라우드 환경의 서버들은 자원을 무제한 사용하도록 해주고 사용한 만큼 비용을 가져간다. 컴퓨터 자원이 너무 쉽게 낭비될 수 있는 환경이고 이런 낭비는 결국 비용 증가로 연결될 것이다.
클라우드 서비스 회사와 ORM도구들은 우리가 지불해야 할 비용에 대해서 걱정해주지 않기 때문에
결국 사용자 입장에서 필요한 만큼 지식과 경험을 갖추고 있어야 하는 것이다.

- Real MySQL 8.0 (백은빈, 이성욱)

차주부터 Real MySQL 8.0 책으로 스터디를 진행한다. BI 프로젝트를 진행할 때만 해도 화면상에서 최대한 대시보드가 빠르게 뜰 수 있도록 만드는 것이 중요하다. 서버에서 화면 캐쉬를 미리 설정한다든지, 대시보드를 만들때 시트를 최소화해서 올린다든지, 계산식을 간결하게 짜는 작업 등이 필요하다. 하지만 그러한 작업은 이후의 문제이며 화면에 사용되는 데이터를 최대한 가볍게 가져올 수 있도록 구성해야한다. 효율적인 SQL 쿼리를 짜기 위해서는 SQL 의 개념과 원리를 어느정도 파악하는게 필요하다고 생각했고, MySQL을 기준으로 개념을 알아두면 다른 DBMS와 비교도 가능할 것 같다.

---

쿼리 실행 과정은 크게 3가지의 단계를 거친다.

1. SQL 파싱 -> 2. 최적화 및 실행 수립 단계 -> 3. 레코드를 가져와 조인 및 정렬

 

1. SQL 파싱
SQL 쿼리 문장을 잘게 쪼개는 순서로 SQL parser 모듈로 처리한다. MySQL 엔진에서 처리한다. ->  파스 트리를 만들어 냄
(+) SQL 파서는 문법 확인함

2. 최적화 및 실행 수립 단계
만들어진 파스트리를 통해 어떤 인덱스로 어떤 테이블을 먼저 읽을지 선택하며 옵티마이저에서 처리한다. 실행 계획을 도출하고 해당 부분은 MySQL 엔진에서 처리한다.

3. 레코드를 가져와 조인 및 정렬
실행 계획에 따라 스토리지 엔진이 레코드를 가져오고 MySQL 엔진이 조인 및 정렬한다. 스토리지 엔진, MySQL 엔진이 동시에 사용된다.

---

9. 옵티마이저와 힌트

 

 

해당 파트가 앞에서 말한 효율적인 쿼리를 짜는데 필요한 부분 중 하나이다.  옵티마이저란 각 테이블 데이터가 어떤 분포로 저장되어 있는지를 통계 정보로 확인하고 이를 통해 최소 비용을 소모하는 2. 최적화 및 실행 수립 단계 를 계획하는 기능을 담당한다. 대부분의 DBMS 옵티마이저들이 이런 기능을 수행하며 MySQL에서는 EXPLAIN이라는 명령으로 쿼리 실행 계획을 확인할 수 있다.

 

 

(1) 옵티마이저 종류

옵티마이저에는 크게 CBO, RBO 2가지 종류가 있다.

- CBO (Cost Based Optimizer)  비용 기반 최적화 (현재 많이 사용)
쿼리를 처리하기 위한 여러 방법이 존재한다. 단위 작업의 비용 정보와 예측 통계 정보를 이용해 비용이 최소로 소요되는 처리로 실행함
 
- RBO (Rule Based Optimizer) 규칙 기반 최적화 (과거 Oracle DBMS)
레코드 건수, 컬럼값의 분포도 등의 통계 정보 고려하지 않는다. 내장된 특정 우선순위에 따라 실행 계획을 수립하는데 그래서 같은 쿼리에 대해서 거의 같은 실행 방법을 사용한다. 오늘날의 데이터는 분포도가 다양하기에 부적합하고 느린 CPU 연산 속도를 가지고 있다.

결과적으로 CBO 가 적합한 방법이다.

 

 (2) MySQL에서의 기본적 데이터 처리

정렬이나 그룹화하는 기본적인 데이터 처리라도 dbms별로 다양한 방법이 있으며 MySQL 안에서도 아래와 같이 나눌 수 있다.

1. 풀 테이블 스캔 / 풀 인덱스 스캔
2. 병렬처리
3. Order By 에 사용되는 처리방법
4. Group By 에 사용되는 처리방법
5. Distinct 에 사용되는 처리방법
6. 내부 임시 테이블을 활용하는 방법

해당 글에서는 1. 풀 테이블 스캔 / 풀 인덱스 스캔과 2. 병렬 처리만 정리한다.

 

- 1. 풀 테이블 스캔 / 풀 인덱스 스캔

 

• 풀 인덱스 스캔

SELECT COUNT(*) FROM employees;

카운트와 같이 단순한 레코드 건수를 구할때 인덱스는 주로 2~3개 컬럼으로만 구성되기에 훨씬 가벼워 풀 인덱스 스캔을 사용할 것이다.
 

 

• 풀 테이블 스캔
   

SELECT * FROM employees;

인덱스 없이 테이블 데이터를 다 읽어 처리하는 작업으로 많은 디스크 읽기가 필요하다. 레코드에만 있는 컬럼이 필요한 쿼리이기에 위의 코드의 경우 풀 테이블 스캔 방식을 사용한다.
 
풀 태이블 스캔을 선택하는 조건은 아래와 같다.

- 레코드 건수가 매우 작아 인덱스보다 풀 스캔이 더 빠를 경우 (페이지 1개)
- WHERE절, ON절에 인덱스를 이용할 수 있는 조건이 없는 경우
- 옵티마이저가 판단했을 때 조건 일치 레코드 건수가 너무 많은 경우
 

풀 테이블 스캔을 실행하는 방법으로 다른 DBMS는 한꺼번에 여러 블록이나 페이지를 읽어오는 기능을 내장하고 있다. 하지만 mysql의 경우에는 한번에 몇 페이지씩 읽어올지 설정하는 시스템 변수가 없다.

MyISAM 스토리지 엔진일 경우와 InnoDB 스토리지 엔진의 경우로 나눠서 볼 수 있는데 MyISAM 스토리지 엔진의 경우에는 디스크로부터 페이지를 하나씩 읽어오는 방식이다.

InnoDB 스토리지 엔진의 경우에는 포그라운드 스레드가 초반 몇 개의 데이터 페이지를 읽다가 테이블의 데이터 페이지를 연속적으로 읽는다는 판단(innodb_read_ahead_threshold 시스템 변수로 설정 가능)에 다다르면,  백그라운드 스레드가 유사한 요청이 올 것이라고 판단해 미리 페이지를 디스크에서 읽어 InnoDB 버퍼풀에 가져다 둔다. 한번에 최대 64개 페이지까지 읽어둘 수 있고 포그라운드 스레드는 빠르게 데이터를 가져다 사용하면 되어 쿼리 속도가 빨라진다.
 

 
- 2. 병렬 처리
 

MySQL에서 여러 스레드가 동시에 여러 쿼리를 각각 처리하는 것은 가능했다.
추가로 하나의 쿼리를 여러 스레드(innodb_parallel_read_threads 시스템 변수로 설정) 가 동시에 처리하는 병렬 처리가 MySQL 8.0 이후부터 가능해졌다. 다만 WHERE 조건없이 단순히 전체 건수를 가져오는 쿼리만 가능하다.
 
ex ) SET SESSION innodb_parallel_read_threads = 8;

병렬 스레드 개수를 아무리 늘려도 CPU 코어 갯수를 넘으면 오히려 성능이 떨어질 수도 있으니 주의해야한다.

---

 

 

 

 

(출처 : Real MySQL 8.0 - 백은빈, 이성욱)