Database/Mysql

[Real MySQL] 9-1. 옵티마이저와 힌트: 기본 데이터 처리

noahkim_ 2025. 3. 9. 10:24

백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.


1. 개요

쿼리 실행 절차

  1. SQL Parser
    • SQL 문장을 잘게 쪼개서 Parse Tree 생성 (MySQL 서버가 이해할 수 있음)
    • SQL Parser라는 모듈이 담당함
    • 문법 검사
  2. 최적화 및 실행 계획 수립
    • Parse Tree를 참조하여 결정함
    • 불필요한 조건 및 복잡한 연산의 단순화
    • 어떤 테이블을 먼저 읽을 것인지
    • 인덱스 통계 정보를 이용해 사용할 인덱스 결정
    • 가져온 레코드들을 임시 테이블에 넣고 추가로 가공해야 할지 여부 결정
  3. 스토리지 엔진으로부터 데이터를 가져옴

 

옵티마이저 종류

  • 데이터베이스 서버에서 두뇌와 같은 역할
  • 최적의 실행 계획을 수립함

 

비용 기반 최적화 
  • 쿼리를 처리하기 위한 여러 가지 방법을 만듬
  • 실행 계획별 비용을 산출 (각 단위 작업의 비용 정보, 대상 테이블의 통계 정보 활용)

 

2. 기본 데이터 처리

풀 테이블 스캔

  • 풀테이블 스캔을 실행할 때 한꺼번에 여러 개의 페이지를 읽어옴

 

선택하는 경우
  • 테이블의 레코드 건수가 너무 작아서 인덱스보다 풀 테이블 스캔이 더 빠를 경우
  • 조건절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우

 

read_ahead
  • 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 옺기 전에 미리 디스크에서 읽어 버퍼풀에 적재하는 기능
  • innodb_read_ahead_threshold: 리드 어헤드 기능을 시작할 지 임계값 설정 (연속된 페이지 수)

 

병렬 처리

  • 하나의 쿼리를 여러 스레드가 나누어 동시에 처리

 

예시
set session innodb_parallel_read_threads = 1;
select count(*) from salaries; # 0.419sec

set session innodb_parallel_read_threads = 2;
select count(*) from salaries; # 0.151sec

set session innodb_parallel_read_threads = 4;
select count(*) from salaries; # 0.122sec

set session innodb_parallel_read_threads = 8;
select count(*) from salaries; # 0.073sec
  • 조건 없이 전체 건수를 가져오는 쿼리만 병렬 처리가 가능함
  • innodb_parallel_read_threads: 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지 설정

 

ORDER BY 처리

인덱스
  • 이미 정렬이 돼있어서 순서대로 읽기만 하면 됨
  • 추가 및 갱신 시, 부가적인 추가 삭제 작업으로 인해 성능이 더 들음

 

Filesort
  • 인덱스를 이용하기 어려울 때 사용하는 방법
    • 정렬 기준이 너무 많아서 모두 인덱스를 생성하는 것이 불가능한 경우
    • GROUP BY 또는 DISTINCT 결과를 정렬해야 하는 경우
    • 임시 테이블의 결과를 다시 정렬해야 하는 경우
    • 랜덤하게 결과 레코드를 가져와야 하는 경우
  • 추가 작업이 필요하지 않음
  • 정렬 작업 시, 작업으로 인해 응답속도가 느림

 

소트 버퍼
  • 정렬을 수행하기 위해 할당받는 별도의 메모리 공간
    • 정렬해야 할 레코드의 크기에 따라 가변적으로 증가함
  • 세션 메모리 영역
    • 정렬 작업이 많거나 커넥션이 많을 경우, 운영체제에 점유되는 총 소트 버퍼 공간이 많아짐
    • 운영체제는 메모리 부족 현상을 겪을 수 있음
  • 멀티 머지
    • 정렬해야 할 레코드 크기가 소트 버퍼보다 클 경우, 레코드를 여러 조각으로 나누고, 정렬 결과를 임시로 디스크에 기록함
  • 시스템 변수
    • sort_buffer_size: 최대 사용 가능한 버퍼 공간 크기

 

정렬 알고리즘
  • 싱글 패스: 레코드 정렬 시, 레코드 전체를 소트 버퍼에 담음
  • 투 패스: 레코드 정렬 시, 레코드의 프라이머리 키 컬럼과 정렬 컬럼만 소트 버퍼에 담음

 

set optimizer_trace="enabled=on", end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

select * from employees order by last_name limit 100000, 1;
select * from information_schema.optimizer_trace;
"filesort_summary": {
  "memory_available": 262144,
  "key_size": 32,
  "row_size": 169,
  "max_rows_per_buffer": 1551,
  "num_rows_estimate": 299698,
  "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>"
}
  • <fixed_sort_key, packed_additional_fields>
    • 정렬 키와 레코드 전체를 가져와서 정렬하는 방식 (투트랙)
    • 컬럼들은 가변 사이즈로 메모리 저장

 

정렬 처리 방법
  • 인덱스를 이용한 정렬
    • ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블에 속해야 함
    • ORDER BY 순서대로 생성된 인덱스가 있어야 함
    • WHERE 절과 ORDER BY가 동일한 인덱스를 사용해야 함
    • B-Tree 인덱스 계열만 사용 가능
    • 여러 테이블이 조인되는 경우, nested-loop 방식의 조인만 사용 가능
  • 조인의 드라이빙 테이블만 정렬
    • 조인을 실행하기 전, 첫번째 테이블의 레코드부터 먼저 정렬하고 조인하는 것이 효율적
    • 첫번째로 읽히는 테이블의 칼럼만으로 ORDER BY 절을 작성해야 함
  • 임시 테이블을 이용한 정렬
    • 2개 이상의 테이블을 조인해서 그 결과를 정렬해야 한다면 임시 테이블이 필요할 수 있음
    • 드리븐 테이블의 컬럼으로 정렬할 경우 조인 후, 정렬해야 함
    • 즉, 조인의 결과를 임시 테이블에 저장하고 다시 정렬해야 함

 

전송 방식
  • 스트리밍 방식
    • 조건에 일치하는 레코드가 검색될 때마다 클라이언트에 바로 전송해주는 방식
    • ex) 인덱스
  • 버퍼링 방식
    • 결과를 모아 버퍼링하여 전송해주는 방식
    • ORDER BY나 GROUP BY는 쿼리의 결과가 스트리밍되는 것을 불가능하게 함
    • ORDER BY나 GROUP BY는 WHERE의 LIMIT으로 건수를 미리 제한할 수 없음 (모두 수행한 후 필터링)
    • ex) 조인의 드라이빙 테이블만 정렬, 임시 테이블을 이용한 정렬

 

상태 변수
  • Sort_merge_passes 상태 변수: 수행된 멀티 머지 횟수
  • Sort_range: 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
  • Sort_scan: 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
  • Sort_rows: 지금까지 정렬한 전체 레코드 건수

 

GROUP BY 처리

Having 절
  • Group By 결과에 대해 필터링 역할 수행
  • 인덱스 사용 불가

 

인덱스 스캔
  • 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 그룹핑 컬럼이 이미 인덱스가 있다면, 인덱스를 읽어 그룹핑 수행
  • 인덱스가 있다 하더라도 그룹함수 등의 그룹 값을 처리해야 해서 임시 테이블이 필요할 때도 있음

 

루스 인덱스 스캔
  • 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것
  • 유니크한 값의 수가 적을수록 성능이 좋음

 

임시 테이블 사용
  • 인덱스를 전혀 사용할 수 없을 경우 쓰이는 방식
  • 내부적으로 GROUP BY 컬럼들로 구성된 유니크 인덱스를 가진 임시테이블을 만들어서 중복 제거와 집합 함수 연산 수행

 

DISTINCT 처리

  • 특정 칼럼 조합의 유니크한 값만 조회할 때 사용

 

SELECT DISTINCT
  • GROUP BY와 같은 형식으로 처리됨

 

집합 함수와 함께 사용된 DISTINCT
  • 집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 컬럼값이 유니크한 것들만 가져옴

 

내부 임시 테이블 활용

  • MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 내부적인 임시테이블을 사용함
  • 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨짐
  • 쿼리의 처리가 완료되면 자동으로 삭제됨

 

메모리 임시 테이블
  • 기본적으로 TempTable 스토리지 엔진을 사용함
    • 가변 길이 타입 제공
  • internal_tmp_mem_storage_engine: 메모리용 임시 테이블 선택 (MEMORY, TempTable)
  • temptable_max_ram: 최대한 사용 가능한 메모리 공간 (기본값: 1GB)
  • temptable_use_mmap: 메모리의 TempTable 크기가 1GB를 넘으면 메모리의 TempTable을 MMAP 파일로 전환

 

 디스크 임시 테이블
  • 임시 테이블이 처음부터 디스크에 저장됨
  • 기본적으로 InnoDB 스토리지 엔진을 사용함
    • 트랜잭션 지원
  • internal_tmp_disk_storage_engine: 디스크 임시테이블 스토리지 엔진 선택

 

임시 테이블이 필요한 쿼리
  • ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리
  • ORDER BY와 GROUP BY에 명시된 컬럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
  • DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우
  • DISTINCT가 인덱스로 처리되지 못하는 쿼리
  • UNION이나 UNION DISTINCT가 사용된 쿼리
  • select_type이 DERIVED인 쿼리

 

임시 테이블이 디스크에 생성되는 경우
  • UNION이나 UNION ALL에서 SELECT되는 컬럼 중에서 길이가 512 바이트 이상인 크기의 칼럼이 있는 경우
  • GROUP BY나 DISTINCT 컬럼에서 길이가 512 바이트 이상인 크기의 칼럼이 있는 경우
  • 메모리 임시 테이블 크기가 temptable_max_ram보다 큰 경우

 

상태 변수
  • Created_tmp_tables: 내부 임시 테이블의 누적 개수 (메모리, 디스크 구분없이 모두 누적함)
  • Created_tmp_disk_tables: 내부 임시 테이블이 만들어진 개수만 누적해서 가지고 있는 상태 값

 

저작자표시 (새창열림)

'Database > Mysql' 카테고리의 다른 글

[Real MySQL] 9-3. 옵티마이저와 힌트: 힌트  (0) 2025.03.11
[Real MySQL] 9-2. 옵티마이저와 힌트: 고급 최적화  (0) 2025.03.10
[Real MySQL] 7-2. 데이터 암호화: 테이블 & 로그  (0) 2025.03.09
[Real MySQL] 7-1. 데이터 암호화: MySQL 서버  (1) 2025.03.09
[Real MySQL] 6. 데이터 압축  (1) 2025.03.08

'Database/Mysql'의 다른글

  • 현재글[Real MySQL] 9-1. 옵티마이저와 힌트: 기본 데이터 처리

관련글

티스토리툴바