[스터디] 15. 옵티마이저와 힌트

목차

9.1 개요

---

9.1.1 쿼리 실행 절차

• MySQL 서버에서 쿼리가 실행되는 과정(3단계)
  
  1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)함.
     • 이 단계를 SQL 파싱(Parsing)이라고 함.
     • MySQL 서버의 SQL 파서라는 모듈로 처리함.
     • SQL 문장이 문법적으로 잘못됐다면 걸러짐.
     • SQL 파스 트리가 만들어짐.
     • MySQL 서버는 SQL 문장 그 자체가 아니라 SQL 파스 트리를 이용해 쿼리를 실행함. 
  2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블로부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택함.
     • SQL 파스 트리를 참조하면서 다음 내용을 처리함.
       • 불필요한 조건 제거 및 복잡한 연산의 단순화
       • 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
       • 각 테이블에 사용된 조건과 인덱스 통꼐 정보를 이용해 사용할 인덱스를 결정
       • 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
     • 이 단계는 최적화 및 실행 계획 수립 단계임.
     • MySQL 서버의 옵티마이저에서 처리함.
     • 완료되면 쿼리의 실행 계획이 만들어짐.
  3. 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져옴.
     
     • 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청함.
     • MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행함.
     • MySQL 엔진에서 거의 처리하는 첫 번째, 두번째 단계와는 다르게, MySQL 엔진과 스토리지 엔진이 동시에 참여해서 처리함.

9.1.2 옵티마이저 종류

• 옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 담당함.
• 비용 기반 최적화(Cost-based optimizer, CBO)
  • 현재 대부분의 DBMS가 선택하고 있는 방법
  • 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출함.
  • 산출된 실행 방법별로 비용이 최소로 소요되는 처리 방식을 선택해 최종적으로 쿼리를 실행함.
• 규칙 기반 최적화(Rule-based optimizer, RBO)
  • 예전 초기 버전의 오라클 DBMS에서 많이 사용했던 방법
  • 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식
  • 통계 정보(레코드 건수나 칼럼값의 분포도)를 조사하지 않고 실행 계획이 수립되기 때문에 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만듦.
  • 사용자의 데이터는 분포도가 매우 다양하기 때문에 RBO는 오래전부터 많은 DBMS에서 거의 사용되지 않음.

9.2 기본 데이터 처리

---

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

• 인덱스 처리에 대한 내용은 8장에서 확인할 수 있음.
• 풀 테이블 스캔
  • 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 방식
  • MySQL 옵티마이저가 풀 테이블 스캔을 선택하는 조건
    • 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우(일반적으로 테이블이 페이지 1개로 구성된 경우)
    • WHERE 절이나 ON 절에 인덱스를 이용할수 있는 적절한 조건이 없는 경우
    • 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우(인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준 )
  • 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요하기 때문에, 대부분 DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장함.
  • MySQL에는 풀 테이블 스캔을 실행할 때 한꺼번에 몇 개씩 페이지를 읽어올지 설정하는 시스템 변수가 없음.
  • MyISAM 스토리지 엔진에서는 풀 테이블 스캔을 실행할 때 디스크로부터 페이지를 하나씩 읽음.
  • InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작됨.
    • 리드 어헤드(Read ahead): 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것
      • 리드 어헤드는 풀 테이블 스캔에서만 사용되는 것이 아니라 풀 인덱스 스캔에서도 동일하게 사용됨.
    • 테이블 스캔이 실행되면 처음 몇 개의 데이터 페이지는 포그라운드 스레드(Foregroud thread, 클라이언트 스레드)가 페이지 읽기를 실행하지만, 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘김.
    • 따라서 포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 쿼리가 빨리 처리됨.
  • MySQL 서버에서는 innodb_read_ahead_threshold 시스템 변수를 이용해 InnoDB 스토리지 엔진이 언제 리드 어헤드를 시작할지 임계값을 설정할수 있음.
  • 포그라운드 스레드에 의해서 innodb_read_ahead_threshold 시스템 변수에 설정된 개수만큼 연속된 데이터 페이지가 읽히면, InnoDB 스토리지 엔진은 백그라운드 스레드를 이용해 대량으로 그다음 페이지들을 읽어서 버퍼 풀로 적재함.

9.2.2 병렬 처리

• MySQL 8.0 버전부터 쿼리의 병렬 처리가 가능해짐.
  • 병렬 처리: 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리하는 것
  • 여러 개의 스레드가 동시에 각각의 쿼리를 처리하는 것은 처음부터 가능했음.
• innodb_parallel_read_threads라는 시스템 변수를 이용해 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지를 변경할 수 있음.

--- MySQL 8.0 버전에서는 아무런 WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있음.
--- 병렬 처리용 스레드 개수가 늘어날수록 쿼리 처리에 걸리는 시간이 줄어드는 것을 확인할 수 있음.
--- 하지만 서버의 장착된 CPU 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수 있음.
mysql> SET SESSION innodb_parallel_read_threads=1;
mysql> SELECT COUNT(*) FROM salaries;
1 row in set (0.32 sec)

mysql> SET SESSION innodb_parallel_read_threads=2;
mysql> SELECT COUNT(*) FROM salaries;
1 row in set (0.20 sec)

mysql> SET SESSION innodb_parallel_read_threads=4;
mysql> SELECT COUNT(*) FROM salaries;
1 row in set (0.18 sec)

mysql> SET SESSION innodb_parallel_read_threads=8;
mysql> SELECT COUNT(*) FROM salaries;
1 row in set (0.13 sec)

9.2.3 ORDER BY 처리(Using filesort)

• 대부분의 SELECT 쿼리에서 정렬은 필수적으로 사용됨.
• 정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 때 Filesort라는 별도의 처리를 이용하는 방법이 있음.

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

• 인덱스를 이용한 정렬은 8.3.6에서 살펴봄. 하지만 아래 이유로 모든 정렬에 인덱스를 이용하는 것은 불가능함.
  • 정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
  • GROUP BY의 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
  • UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
  • 랜덤하게 결과 레코드를 가져와야 하는 경우
• MySQL 서버에서 인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는 실행 계획의 Extra 칼럼에 "Using filesort" 메시지가 표시되는지 여부로 판단할 수 있음.

9.2.3.1 소트 버퍼

• MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 소트 버퍼(Sort buffer)라고 함.
• 소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size 시스템 변수로 설정할 수 있음.
• 소트 버퍼를 위한 메모리 공간은 쿼리의 실행이 완료되면 즉시 시스템으로 반납됨.
• 소트 버퍼로 정렬해도 빠르게 정렬이 되지 않는 경우가 있음.
  • 정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 크면, 정렬해야 할 레코드를 여러 조각으로 나눠서 처리하고 임시 저장을 위해 디스크를 사용함.
  • 즉, 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행하는 멀티 머지(Multi-merge) 작업을 수행함.
  • 이 작업은 모두 디스크의 쓰기와 읽기를 유발하며, 레코드 건수가 많을수록 반복 작업의 횟수가 많아지므로 속도 차이가 크게 보이지 않음.
  • 소트 버퍼는 여러 클라이언트가 공유해서 사용할 수 있는 영역이 아님. 커넥션이 많으면 많을수록, 정렬 작업이 많으면 많을수록 소트 버퍼로 소비되는 메모리 공간이 커짐.

9.2.3.2 정렬 알고리즘

• 레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 싱클 패스(Single-pass)와 투 패스(Two-pass)로 나눌 수 있음.(공식 명칭은 아님.)

--- 정렬을 수행하는 쿼리가 어떤 정렬 모드를 사용하는지 옵티마이저 트레이스 기능으로 확인할 수 있음.
--- 출력된 내용에서 filesort_summary 섹션의 sort_algorithm 필드에 정렬 알고리즘이 표시되고,
--- sort_mode 필드에는 <fixed_sort_key, packed_additional_fields>가 표시된 것을 확인할 수 있음.

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

--- // 쿼리 실행
mysql> SELECT * FROM employees ORDER BY last_name LIMIT 100000,1;

--- // 트레이스 내용 확인
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G
...
		"filesort_priority_queue_optimization": {
		"limit": 100001
		} /* filesort_priority_queue_optimization */,
        "filesort_execution": [
        ] /* filesort_execution */,
        "filesort_summary": {
        	"memory_available": 262144,
            "key_size": 32,
            "row_size": 169,
            "max_rows_per_buffer": 1551,
            "num_rows_estimate": 936530,
            "num_rows_found": 300024,
            "num_initial_chunks_spilled_to_disk": 82,
            "peak_memory_used": 262144,
            "sort_algorithm": "std::stable_sort",
            "sort_mode": "<fixed_sort_key, packed_additional_fields>"
         } /* filesort_summary */
...

• MySQL 서버의 정렬 방식 3가지
  1. <sort_key, rowid>: 정렬 키와 레코드의 로우 아이디(Row ID)만 가져와서 정렬하는 방식 → 투 패스 정렬 방식
  2. <sort_key, additional_fields>: 정렬 키와 레코드 전체를 가져와서 정렬하는 방식으로, 레코드의 칼럼들은 가변 사이즈로 메모리 저장 → 싱글 패스 정렬 방식
  3. <sort_key, packed_addtional_fileds>: 정렬 키와 레코드 전체를 가져와서 정렬하는 방식으로, 레코드의 칼럼들은 가변 사이즈로 메모리 저장 → 싱글 패스 정렬 방식

9.2.3.2.1 싱글 패스 정렬 방식

• 소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 정렬 방식

mysql> SELECT emp_no, first_name, last_name
	FROM employees
    ORDER BY first_name;

• 최신 버전에서 일반적으로 사용하지만, 아래의 경우 투 패스 정렬 방식을 사용함.
  • 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
  • BLOB이나 TEXT 타입의 칼럼이 SELECT 대상에 포함될 때

9.2.3.2.2 투 패스 정렬 방식

• 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 칼럼을 가져오는 정렬 방식
• 싱글 패스 정렬 방식이 도입되기 이전부터 사용하던 방식

9.2.3.3 정렬 처리 방법

• 쿼리에 ORDER BY가 사용되면 반드시 아래 3가지 방법 중 하나로 정렬이 처리됨.

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

• 인데스를 사용할 수 있다면, 별도의 Filesort 과정 없이 인덱스를 순서대로 읽어서 결과를 반환함.
• 인덱스를 사용할 수 없다면, WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 처리(Filesort)함. 이때, 정렬 대상 레코드를 최소화하기 위해 다음 2가지 방법 중 하나를 선택함.
  1. 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행함.
  2. 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행함.
• 처리 속도: 첫 번째 방법 > 두 번째 방법 > 세번째 방법

9.2.3.3.1 인덱스를 이용한 정렬

• 인데스를 이용한 정렬에는 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 함.

--- last_name 컬럼에 인덱스가 생성되어 있어, ORDER BY last_name을 사용할 때 인덱스를 통해 정렬이 최적화됨.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- last_name 컬럼에 인덱스 생성
CREATE INDEX idx_last_name ON employees (last_name);

-- 인덱스를 이용한 정렬 쿼리
SELECT * FROM employees
ORDER BY last_name;

• WHERE 절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면, 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 함.

--- customer_id와 order_date에 대한 복합 인덱스를 생성하여 WHERE과 ORDER BY 모두 인덱스를 활용하도록 최적화함.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

-- customer_id와 order_date에 대해 복합 인덱스 생성
CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

-- WHERE와 ORDER BY가 동일한 인덱스를 사용하는 쿼리
SELECT * FROM orders
WHERE customer_id = 123
ORDER BY order_date;

• B-Tree 계열의 인덱스가 아닌 해시 인덱스나 전문 검색 인덱스 등에서는 인덱스를 이용한 정렬을 사용할 수 없음. 예외적으로 R-Tree도 사용할 수 없음.

--- MySQL에서는 해시 인덱스(hash index)나 전문 검색(full-text) 인덱스는 정렬에 사용할 수 없음.

CREATE TABLE product_catalog (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    description TEXT,
    price DECIMAL(10, 2),
    FULLTEXT(description)  -- 전문 검색 인덱스
);

SELECT * FROM product_catalog
ORDER BY description; -- 불가능한 정렬

• 여러 테이블이 조인되는 경우에는 네스티드-루프(Nested-loop) 방식의 조인에서만 이 방식을 사용할 수 있음.

--- employees 테이블의 last_name 컬럼에 인덱스가 있고, 조인된 상태에서도 이 인덱스를 통해 정렬을 수행할 수 있음.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    last_name VARCHAR(50),
    first_name VARCHAR(50)
);

-- 조인을 사용한 쿼리에서 인덱스를 활용한 정렬
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY e.last_name;

• 인덱스를 이용해 정렬이 처리되는 경우에는 실제 인덱스의 값이 정렬되어 있기 때문에 인덱스의 순서대로 읽기만 하면 됨.
  • 실제로 MySQL 엔진에서 별도의 정렬을 위한 추가 작업을 수행하지 않음.
  • ODERY BY가 있는 없든 같은 인덱스를 레인지 스캔해서 나온 결과는 같은 순서로 출력되는 것을 확인할 수 있음.

9.2.3.3.2 조인의 드라이빙 테이블만 정렬

• 조인이 수행되면 결과 레코드의 건수가 몇 배로 불어나고, 레코드 하나하나의 크기도 늘어남.
• 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하려면, 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 ORDER BY 절을 작성해야 함.
• WHERE 절이 아래 2가지 조건을 갖추고 있기 때문에 옵티마이저는 employees 테이블을 드라이빙 테이블로 선택할 것임.
  • WHERE 절의 검색 조건("emp_no BETWEEN 100002 AND 100010")은 employees 테이블의 프라이머리 키를 이용해 검색하면 작업량을 줄일 수 있음.
  • 드리븐 테이블(salaries)의 조인 칼럼인 emp_no 칼럼에 인덱스가 있음.

• 검색은 인덱스 레인지 스캔으로 처리할 수 있지만, ORDER BY 절에 명시된 칼럼은 employees 테이블의 프라이머리 키와 전혀 연관이 없으므로 인덱스를 이용한 정렬은 불가능함.
• 옵티마이저는 드라이빙 테이블만 검색해서 정렬을 먼저 수행하고, 그 결과와 salaries 테이블을 조인함.

--- ORDER BY 절의 정렬 기준 칼럼이 드라이빙 테이블(employees)에 포함된 칼럼임.
mysql> SELECT *
	FROM employees e, salries s
    WHERE s.emp_no=e.emp_no
    AND e.emp_no BETWEEN 100002 AND 100010
    ORDER BY e.last_name;

• 조인의 첫 번째(드라이빙) 테이블만 정렬 실행 과정
  1. 인덱스를 이용해 "emp_no BETWEEN 100002 AND 100010" 조건을 만족하는 9건을 검색
  2. 검색 결과를 last_name 칼럼으로 정렬을 수행(Filesort)
  3. 정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인을 수행해 86건의 최종 결과를 가져옴

9.2.3.3.3 임시 테이블을 이용한 정렬

• 쿼리가 여러 테이블을 조인하지 않고, 하나의 테이블로부터 SELECT해서 정렬하는 경우라면 임시 테이블이 필요하지 않음.
• 하지만 2개이상의 테이블을 조인해서 그 결과를 정렬해야 한다면 임시 테이블이 필요할 수 있음.
• 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거침.
• 정렬해야 할 레코드 건수가 가장 많기 때문에 가장 느린 정렬 방법임.

--- ORDER BY 절의 정렬 기준 칼럼이 드리븐 테이블(salaries)에 있는 칼럼임.
--- 정렬이 수행되기 전에 salaries 테이블을 읽어야 하므로 이 쿼리는 조인된 데이터를 가지고 정렬할 수밖에 없음.
mysql> SELECT *
	FROM employees e, salries s
    WHERE s.emp_no=e.emp_no
    AND e.emp_no BETWEEN 100002 AND 100010
    ORDER BY s.salary;

9.2.3.3.4 정렬 처리 방법의 성능 비교

• 웹 서비스용 쿼리에서는 ORDER BY와 함께 LIMIT이 거의 필수로 사용되는 경향이 있음.
• 일반적으로 LIMIT은 테이블이나 처리 결과의 일부만 가져오기 때문에 MySQL 서버가 처리해야 할 작업량을 줄이는 역할을 함.
• 그런데 ORDER BY나 GROUP BY 같은 작업은 WHERE 조건을 만족하는 레코드를 LIMIT 건수 만큼만 가져와서는 처리할 수 없음.
• 조건을 만족하는 레코드를 모두 가져와서 정렬을 수행하거나 그루핑 작업을 실행해야만 비로소 LIMIT 건수를 제한할 수 있음.
• WHERE 조건이 아무리 인덱스를 잘 활용하도록 튜닝해도 잘못된 ORDER BY나 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생함.
• 쿼리가 처리되는 방법은 스트리밍 처리와 버퍼링 처리라는 2가지 방식으로 구분함.

9.2.3.3.4.1 스트리밍 방식

• 서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식
• 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받음.
• 스트리밍 방식으로 처리되는 쿼리는 쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간을 보장해줌.
• 스트리밍 방식으로 처리되는 쿼리에서 LIMIT처럼 결과 건수를 제한하는 조건들은 쿼리의 전체 실행 시간을 줄여줄 수 있음.

9.2.3.3.4.2 버퍼링 방식

• ORDER BY나 GROUP BY 같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능하게 함.
• WHERE 조건에 일치하는 모든 레코드를 가져온 후, 정렬하거나 그루핑해서 차례대로 보내야 함.
• MySQL 서버에서는 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느림.
• 버퍼링 방식으로 처리되는 쿼리는 LIMIT처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에 별로 도움이 되지 않음.
• 9.2.3.3의 정렬 처리 방법 중에서 인덱스를 사용한 정렬 방식만 스트리밍 형태의 처리이며, 나머지는 모두 버퍼링된 후에 정렬됨.

9.2.3.4 정렬 관련 상태 변수

• MySQL 서버는 처리하는 주요 작업에 대해서는 해당 작업의 실행 횟수를 상태 변수로 저장함.

--- 지금까지 몇 건의 레코드나 정렬 처리를 수행했는지,
--- 소트 버퍼 간의 병합 작업(멀티 머지)은 몇 번이나 발생했는지 확인
mysql> FLUSH STATUS;
mysql> SHOW STATUS LIKE 'Sort%';

• 상태값
  • Sort_merge_passes: 멀티 머지 처리 횟수
  • Sort_range: 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
  • Sort_scan: 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수. Sort_scan과 Sort_range는 둘 다 정렬 작업 횟수를 누적하고 있는 상태 값임.
  • Sort_rows: 지금까지 정렬한 전체 레코드 건수

이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.