✅ 옵티마이저

옵티마이저(Optimizer)는 사용자가 요청한 쿼리문을 가장 효율적으로 처리할 수 있는 실행 계획을 선택하는 DBMS의 핵심 엔진이다.

• 하나의 쿼리문을 처리하는 방법은 여러 가지가 존재하며, 옵티마이저는 이들 중에서 가장 비용이 적은 최적의 실행 계획을 선택하여 실행한다.

• 옵티마이저는 속도가 가장 빠른 계획이 아닌, 가장 효율적(리소스 비용이 적은) 계획을 찾는다. 이는 DBMS를 여러 사용자가 같이 사용하기 때문이다. (리소스 독점X)

이처럼 비용 기반의 옵티마이저를 "비용 기반 최적화(Cost Base)"라 부른다.

📌 스캔

MySQL에서 데이터를 스캔(조회)할 때는 크게 두 가지 방식이 사용된다.

• 풀 스캔(Full Table Scan)과 인덱스 스캔(Index Scan)

일반적으로는 테이블의 특정 컬럼 인덱스를 생성하고, 이를 기반으로 데이터를 조회하는 인덱스 스캔 방식이 더 효율적이다. 하지만, 아래와 같은 상황에서는 풀 스캔이 오히려 더 효율적일 수 있다.

• 풀 스캔(Full Scan)이 효율적인 경우
  • 테이블의 레코드 수가 적은 경우 인덱스를 통한 스캔보다 풀 스캔이 빠를 수 있다. (일반적으로 페이지가 1개인 경우)
  • WHERE절 또는 ON절에 인덱스를 적절하게 사용할 수 없는 경우
  • 인덱스를 사용하더라도 일치하는 레코드 수가 매우 많은 경우

📌 병렬 처리

MySQL8.0 이후부터 생겨난 기능으로 데이터 처리를 여러 스레드에 처리할 수 있는 기능이다.

이러한 병렬 처리는 WHERE 조건문 없이 전체 테이블을 조회할 때만 처리 가능하다.

innodb_parallel_read_threads라는 시스템 변수를 사용하여 하나의 쿼리문을 여러 스레드로 처리할 수 있다.

-- 병렬 읽기 스레드를 3개로 설정
SET SESSION innodb_parallel_read_threads = 3;

-- 병렬 읽기를 활용한 쿼리 실행
SELECT COUNT(*)
FROM TMP_TB;

📌 정렬(SORT)

MySQL에서 정렬(SORT)은 "인덱스 방식" 또는 "Filesort" 방식으로 처리된다.

인덱스를 기준의 정렬은 이미 인덱스를 생성할 때 데이터가 인덱스를 기준으로 데이터가 정렬된다. 이러한 정렬을 인덱스 방식 정렬이다.

인덱스를 사용하지 않고 쿼리문에 ORDER BY를 작성하여 정렬된 방식을 Filesort 방식의 정렬이라 부른다.

• 인덱스 정렬
  • 쿼리가 실행할 때 이미 인덱스에 의해 정렬되어 있기 때문에 매우 빠른 속도를 자랑한다.
  • 별도의 인덱스 생성 작업과 인덱스를 저장할 공간 문제가 단점이며, 인덱스의 개수가 늘어날수록 InnoDB 버퍼 풀을 위한 메모리를 많이 사용한다.
• Filesort 정렬
  • 인덱스 생성 없이 정렬할 수 있다는 장점을 가지고 있다. 하지만 정렬해야 할 레코드가 많은 경우 속도가 느려진다는 단점이 있다.

▶︎ 소트 버퍼(SORT BUFFER)

레코드를 정렬할 때 필요한 별도의 메모리 공간(정렬 작업 공간)을 소트 버퍼(SORT BUFFER)라 부른다.

소트 버퍼는 정렬이 필요할 때만 할당받아 사용하며, 크기는 레코드 수에 따라 가변적이다. (설정된 소트 버퍼의 최대 크기는 sort_buffer_size로 설정)

이러한 소트 버퍼는 로컬 메모리로 분리된다.

• 사용자에게 소트 버퍼를 크게 할당하면, OOM(Out of memory)이 발생할 가능성이 있다.

📌 그룹화

• 인덱스 스캔을 이용한 GROUP BY
  인덱스를 사용하여 그루핑을 수행하는 방식, 그룹함수를 처리하기 위해 임시 테이블을 사용할 때가 있다.

• 루스(Loose) 인덱스 스캔을 이용한 GROUP BY
  인덱스 레코드를 건너뛰면서 필요한 부분만을 읽는 방식을 의미하며, 이러한 방식을 통해 그루핑하는 방식이다. 인덱스의 유니크한 값이 적을수록, 데이터 분포도가 좋지 않는 인덱스에서 좋은 결과를 나타낸다.
  임시 테이블을 사용하지 않는다.

• 임시 테이블을 사용한 GROUP BY
  인덱스를 사용하지 않는 방식의 그루핑을 의미한다.