MySQL ‐ 옵티마이저와 힌트 - woojin-playground/Backend-PlayGround GitHub Wiki

📚 옵티마이저와 힌트

MySQL에서는 EXPLAIN이라는 명령으로 쿼리의 실행 계획을 확인할 수 있는데, EXPLAIN 명령의 결과에는 상당히 많은 정보가 출력된다.
실행 계획에 표시되는 내용을 제대로 이해하려면 MySQL 서버 옵티마이저가 실행하는 최적화에 대해 어느 정도 지식이 필요하다.
이번 회사 프로젝트에서 많은 데이터를 조회하는 부분을 개발하면서 인덱스 필요성을 느꼈고 그에 따라 필요한 내용이라고 판단되어 공부

📚 쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정을 크게 3단계로 나눌 수 있다.

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

📚 풀 테이블 스캔(Full Table Scan)과 풀 인덱스 스캔(Full Index Scan)

풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업을 의미한다.
MySQL 옵티마이저는 다음과 같은 조건에 부합할 경우 주로 풀 테이블 스캔을 사용한다.

1. 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 효율적인 경우
2. WHERE절이나 ON절에 인덱스를 이용할 수 없는 적절한 조건이 없는 경우
3. 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 또는 레코드 건수가 너무 많은 경우

일반적으로 테이블 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기를 필요로 한다.
그래서 대부분 DBMS는 풀 테이블 스캔을 실행할 때, 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다.
하지만 MySQL에는 풀 테이블 스캔을 실행할 때, 한꺼번에 몇 개씩 페이지를 읽어올지 설정하는 시스템 변수는 없다.
InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 쓰레드에 의해 리드 어헤드(Read Ahead) 작업이 자동으로 시작된다. 리드 어헤드란, 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 말한다.
즉, 풀 테이블 스캔이 실행되면 처음 몇 개의 데이터 페이지는 포그라운드 쓰레드가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 쓰레드로 넘긴다.
일반적으로 디폴트 설정으로도 충분하나 데이터 웨어하우스용으로 MySQL을 사용한다면 이 옵션을 더 낮은 값으로 설정해서 더 빨리 리드 어헤드가 시작되게 유도하는 것도 좋은 방법이다.
풀 인덱스 스캔은 인덱스를 처음부터 끝까지 스캔하는 방법을 말한다.
MySQL 서버는 단순히 레코드 건수만 필요로 하는 쿼리라면 인덱스를 스캔할 가능성이 높은데 그 이유는 용량이 작은 인덱스를 선택하는 것이 디스크 읽기 횟수를 줄일 수 있기 때문이다.
하지만 다음과 같이 레코드에만 있는 컬럼이 필요한 쿼리의 경우에는 풀 인덱스 스캔을 활용하지 못하고 풀 테이블 스캔을 사용한다.

SELECT COUNT(*) FROM employees;  # full index scan
SELECT * FROM employees;         # full table scan

📚 ORDER BY 처리(Using filesort)

레코드 1~2건을 가져오는 쿼리를 제외하면 대부분의 SELECT 쿼리에서 정렬은 필수적으로 사용된다.
레코드 정렬은 크게 인덱스를 이용하는 방법과 쿼리가 실행될 때, "Filesort"라는 별도 처리를 이용하는 방법으로 나눌 수 있다.

	장점	단점
인덱스	1. INSERT, UPDATE, DELETE 쿼리가 실행될 때, 이미 인덱스로 정렬이 되어 있어 순서대로 읽기만 하면 되므로 매우 빠르다.	1. INSERT, UPDATE, DELETE 작업 시 부가적으로 인덱스 추가/삭제 작업이 필요하기 때문에 느리다. 2. 인덱스 때문에 디스크 공간이 더 많이 필요하다. 3. 인덱스 개수가 늘어날수록 InnoDB 버퍼 풀을 위한 메모리가 많이 필요하다.
Filesort	1. 인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때 단점이 장점으로 바뀐다. 2. 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다.	1. 정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리 응답 속도가 느리다.

MySQL 서버에서 인덱스를 이용하지 않고 별도 정렬 처리를 수행했는지 실행 계획의 Extra 컬럼에 Using filesort 메시지가 표시되는가 여부로 판단할 수 있다.

📚 소트 버퍼

MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 소트 버퍼라고 한다.
소트 버퍼는 정렬이 필요한 경우에만 할당되며 버퍼 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size라는 시스템 변수로 설정할 수 있다.
소트 버퍼를 위한 메모리 공간은 쿼리 실행이 완료되면 즉시 시스템으로 반납된다.
정렬해야 할 레코드가 아주 소량이어서 메모리에 할당된 소트 버퍼만으로 정렬할 수 있다면 아주 빠르게 정렬이 처리가 되나 만약 정렬해야 할 레코드 건수가 소트 버퍼로 할당된 공간보다 크다면 MySQL은 정렬해야 할 레코드를 여러 조각으로 나눠 처리하는데, 이 과정에서 임시 저장을 위해 디스크를 사용하게 된다.
메모리의 소트 버퍼에서 정렬을 수행하고 그 결과를 임시로 디스크에 기록해둔다. 그런 다음 레코드를 가져와서 다시 정렬해서 반복적으로 디스크에 임시 저장한다.
이처럼 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 한다.
이런 작업들이 모두 디스크 쓰기와 읽기를 유발하며, 레코드 건수가 많을수록 반복 작업 횟수가 많아진다.
sort_buffer_size 시스템 변수 설정값이 무조건 크면 메모리에서 모두 처리되니 빨라질 것으로 예상하지만 실제로는 그렇지 않다. 리눅스 계열 운영체제에서는 너무 큰 sort_buffer_size를 사용하게 되면 큰 메모리 공간 할당 때문에 오히려 성능이 훨씬 저하될 수 있다.

📚 정렬 처리 방법

쿼리에 ORDER BY절이 사용되면 반드시 다음 3가지 방법 중 하나로 정렬이 처리가 된다. 일반적으로 아래쪽에 있는 정렬 방법으로 갈수록 처리 속도는 떨어진다.

정렬 처리 방법	실행 계획 Extra 칼럼 내용
인덱스를 사용한 정렬 방식	별도 표기X
조인에서 드라이빙 테이블만 정렬	Using filesort
조인에서 조인 결과를 임시 테이블로 저장 후 정렬	Using temporary; Using filesort