MySQL 옵티마이저

MySQL 서버로 요청된 쿼리는 결과는 동일하지만 그 결과를 만들어 내는 방법은 매우 다양하다.

 

다양한 방법 중, 가장 최적이고 최소의 비용이 소모되는 방법을 선택하는 작업을 옵티마이저가 담당한다.

 

MySQL 옵티마이저는 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출한다.

 

산출된 비용을 바탕으로, 비용이 최소로 소요되는 처리 방식을 선택하는 💡 비용 기반 옵티마이저 이다.

옵티마이저가 만들어 내는 실행 계획을 이해할 수 있어야 실행 계획의 불합리한 부분을 찾아내고, 더 최적화된 방법으로 유도할 수 있다.

 

데이터 처리

 

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

 

풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 모든 레코드를 읽는 것이다.

 

풀 인덱스 스캔은, 말 그대로 인덱스로 설정된 칼럼을 기준으로 인덱싱 된 모든 레코드를 읽는 것이다.

 

 

🚨 인덱스를 이용할 수 없는 조건문이 있거나 테이블 레코드 전체 건수가 너무 작아서 인덱스를 통해 읽는 것이 비효율적이라고 판단되는 경우 테이블 풀 스캔을 사용한다.

 

💡 MySQL InnoDB 는 특정 테이블의 연속된 데이터 페이지가 읽히면, 요청이 오기 전에 페이지를 버퍼에 저장(리드 어헤드 : Read Ahead)해두어 성능을 향상시킨다.

 

innodb_read_ahead_threshold 시스템 변수를 이용해, 변수에 설정된 개수만큼 연속된 페이지가 읽히면 리드 어헤드를 시작할 수 있도록 임계값을 설정할 수 있다.

 

 

📌 병렬 처리

 

MySQL 8.0은 innodb_parallel_read_threads 시스템 변수를 이용해 하나의 쿼리를 최대 몇개의 스레드를 이용해 처리할지 설정할 수 있다.

 

💡 아무런 WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만, 하나의 쿼리를 병렬로 처리할 수 있다.

 

 

📌 ORDER BY 처리

 

레코드를 1~2건 가져오는 쿼리를 제외하면 대부분의 SELECT 쿼리에서 정렬은 필수적으로 사용된다.

 

 

소트 버퍼

MySQL이 정렬을 수행하기 위해 별도로 할당받은 공간을 소트 버퍼라고 한다.

 

버퍼의 크기는 레코드의 크기에 따라 가변적으로 증가하고, 최대 사용 간으한 소트 버퍼의 공간은 sort_buffer_size 라는 시스템 변수로 설정할 수 있다.

 

🚨 만약 정렬해야할 레코드의 건수가 소트 버퍼로 할당된 공간보다 큰 경우 문제가 될 수 있다.

 

소트 버퍼에 담을 수 있는 만큼 가져와서 정렬을 수행한 뒤, 임시로 디스크에 기록해두고

 

다음 레코드를 가져와서 정렬을 수행하고 디스크에 기록하는 작업을 반복하게 된다.

 

이 작업이 잦아질 수록 성능이 떨어지고, 그렇다고 소트 버퍼의 크기를 키우면 큰 메모리 할당 때문에 성능이 떨어질 수 있다.

 

 

정렬 알고리즘

 

레코드를 정렬할 때, 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 나눌 수 있다.

 

-- // 옵티마이저 트레이스 활성화
SET OPTIMIZER_TRACE = "enabled=on", END_MARKERS_IN_JSON=on;

SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;

 

위 구문으로 옵티마이저 트레이스를 활성화 시키고 쿼리를 실행한 뒤

 

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G

 

위 구문으로 트레이스 내용을 확인해보면

 

sort_mode 필드로 정렬 방식을 확인할 수 있다.

 

<sort_key , rowid > : 정렬 키와 레코드의 로우 아이디(Row ID)만 가져와서 정렬하는 방식

<sort_key , additional_fields > : 정렬 키와 레코드 전체를 가져와서 정렬하는 방식 (레코드 칼럼들은 고정 사이즈로 메모리 저장)

<sort_key , packed_additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬하는 방식 (레코드 칼럼들은 가변 사이즈로 메모리 저장)

 

MySQL 5.7 버전 부터는 메모리 공간의 효율적인 사용을 위해 3번째 방식을 사용한다고 한다.

 

💡레코드 전체를 가져와서 정렬하는 방식은 많은 소트 버퍼 공간이 필요하지만 테이블을 2번 읽을 필요가 없고

 

Row ID만 가져와서 정렬하는 방식은 소트 버퍼의 크기는 적게 사용할 수 있지만 다른 칼럼을 가져오기 위해 정렬된 ROW ID 로 테이블을 한번 더 읽어야 한다.

 

따라서, 레코드의 크기나 건수가 작을 경우 레코드 전체를 가져오는 것이 성능이 좋다.