[MySQL] 9장 옵티마이저와 힌트

 

MySQL에서는 explain이라는 명령으로 쿼리의 실행 계획을 확인할 수 있다. 

MySQL 서버가 사용자의 요청을 처리하기 위해 데이터를 가공하는 기본 절차와 빠른 성능을 보장하기 위해 수행하는 최적화 절차를 살펴보자.

(현재는 기본 절차만 스터디하고 정리하였다)

개요

쿼리 실행 절차

• sql 문장을 서버가 이해할 수 있는 수준으로 분리(parse tree)
• parse tree를 확인하며 테이블, 인덱스 정보를 선택
• 2단계에서 결정된 정보를 활용해 스토리지 엔진으로부터 데이터를 가져옴

옵티마이저의 종류

• 비용 기반 최적화 -> 대부분의 RDBMS가 사용
• 규칙 기반 최적화 

 

기본 데이터 처리

MySQL 서버를 포함한 모든 RDBMS는 데이터를 정렬하거나 그루핑 하는 등의 기본 데이터 가공 기능을 가지고 있다. 

기본적인 가공을 위해 MySQL 서버가 어떤 알고리즘을 사용하는지 간단히 살펴보자.

 

풀 테이블 스캔과 풀 인덱스 스캔

몇몇 경우에는 인덱스 스캔을 하는것보다 풀 테이블 스캔을 선택한다

• 레코드 건수가 작을때
• where, on절에 적절한 인덱스 조건이 없을 때
• 인덱스 스캔을 하더라고 조건 일치 레코드 건수가 전체 테이블 레코드의 20~25%를 넘어서는 경우 

대부분의 DBMS는 풀 테이블 스캔을 하는 경우 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다.

하지만 mySQL은 풀테이블 스캔을 실행할 때 한꺼번에 몇 개씩 페이지를 읽어올지 설정하는 시스템 변수는 없다. 

InnoDB에서는 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤더 작업이 자동으로 시작된다.

리드 어헤더(read ahead) : 어떤 데이터의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디시크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것

innodb_read_ahead_threshold : 언제 리드 어헤드를 시작할지 임계값을 설정

 

병렬 처리

하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리하는 것

innodb_parallel_read_threads

병렬 처리용 스레드 개수를 아무리 늘려도 서버에 장착된 CPU의 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수도 있으니 주의

 

Order By 처리 ( using filesort)

대부분의 select 쿼리에서 정렬은 필수적으로 사용된다.

정렬을 처리하는 방법은 인덱스를 이용하는 방법, 쿼리가 실행될 때 Filesort라는 별도의 처리를 이용하는 방법 

 

인덱스를 이용하는 방법

• insert, update, delete 쿼리가 실행될 때 이미 인덱스가 정렬돼 있어 읽을 때 빠르다
• insert, update, delete 쿼리가 실행될 때 부가적인 인덱스 추가/삭제 작업이 필요해 느리다
• 인덱스 때문에 디스크 공간이 더 많이 필요
• 인덱스의 개수가 늘어날수록 innoDB 버퍼 풀을 위한 메모리가 많이 필요

Filesort 이용

• 정렬해야 할 레코드가 많지 않으면 메모리에서 filesort가 처리되어 빠르다
• 정렬 작업이 쿼리 실행 시 처리되어 대상 레코드가 많아질수록 쿼리의 응답 속도가 느리다

실행 계획의 extra 칼람에 using filesort 메시지가 표시되어 있다면 인덱스를 이용하지 않고 별도의 정렬 처리가 수행된 것 

 

mysql의 정렬 특성을 이해하면 쿼리를 튜닝할 때 이해도가 높아질 수 있다.

(책 내용 뒤에서 자세히 설명하지만 우선 생략.. TODO)

 

Group By 처리, Distinct 처리, 내부 임시 테이블 활용

각 처리에 대해서 기본적인 내용 먼저 학습하고 한 번 더 보기로.. TODO

 

여기까지가 기본 데이터 처리에 대한 설명