Back-End/DB

Real MySQL 8.0 [실행계획]

Meluu_ 2025. 7. 28. 14:38

대부분 DBMS는 많은 데이터를 안전하게 저장 및 관리
사용자가 원하는 데이터 빠르게 조회가 주 목적

이를 달성하기 위해 옵티마이저가 사용자의 쿼리를 최적으로 처리될 수 있게 하는 쿼리의 실행 계획을 수립할 수 있어야함

EXPLAIN 명령으로 옵티마이저가 수립한 실행 계획 확인

MYSQL 5.7버전까지 테이블과 인덱스에 대한 개괄적인 정보를 가지고 실행계획을 수립함

8.0부터 히스토그램 도입
히스토그램 : 인덱스되지 않은 칼럼들, 인덱스된 칼럼들의 데이터 분포도


통계 정보는 단순히 인덱스된 칼럼의 유니크한 값의 개수 정도만 가져 옵티마이저가 최적의 실행계획을 수립하기에는 많이 부족
실제 인덱스의 일부 페이지를 랜덤으로 가져와 참조하는 방식 사용

8.0부터 칼럼 데이터 분포도를 참조 가능한 히스토그램 정보 활용
히스토그램 정보는 수동으로 수집 및 관리됨

 

// 히스토그램 수동 갱신 쿼리 
ANALYZE TABLE [테이블 명] 
UPDATE HISTOGRAM ON [컬럼..];

// 삭제
[DROP]



히스토그램의 종류

SingleTone: 칼럼 값 개별로 레코드 건수를 관리하는 히스토그램 , Value-Based 히스토그램 or 도수 분포라고 불림
Equi-Height(높이 균형 히스토그램) : 칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램

히스토그램은 버킷(Bucket) 단위로 구분되어 레코드 건수나 칼럼값의 범위가 관리됨
싱글톤은 칼럼이 가지는 값별로 버킷 할당
- 각 버킷 칼럼의 값과 발생 빈도 비율 2개의 값을 가짐

높이 균형은 개수가 균등한 칼럼값의 범위별로 하나의 버킷 할당
- 각 버킷 범위 시작 값과 마지막 값, 발생빈도율, 각 버킷에 포함된 유니크한 값의 개수 등 4개의 값을 가짐

히스토그램 정보가 있으면 어느테이블을 먼저 읽어야 조인의 횟수를 줄일 수 있을 지 옵티마이저가 더 정확하게 판단 가능

 


히스토그램이 필요한 이유

옵티마이저가 실행계획을 수립할 때 결국에는 어떠한 정보를 바탕으로 수립할 것이다.
하지만 일반적인 통계정보는 정확하지 않고 자칫 잘못된 실행계획을 수립하여 성능이 저하될 수 있다.
히스토그램을 도입하여 더 정확한 정보를 제공함으로써 옵티마이저가 더 최적화된 실행계획을 수립하게 도와준다.

인덱스도 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 가장 나은 실행계획을 선택함
레코드 건수 예측을 위해 옵티마이저는 실제 인덱스의 B-Tree를 샘플링해서 살펴본다. (인덱스 다이브)

현재 인덱스된 칼럼을 검색 조건으로 사용 시 그 칼럼은 인덱스 다이브를 통해 정보 수집
8.0에서 히스토그램은 주로 인덱스되지 않은 칼럼에 대한 데이터 분포도를 참조하는 용도로 사용

코스트 모델

코스트 모델 : 전체 쿼리의 비용을 계산하는데 필요한 단위 작업들의 비용

MySQL 서버가 쿼리를 처리하려면 다음과 같은 다양한 작업을 필요로 함
- 디스크로부터 데이터 페이지 읽기
- 메모리(InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
- 인덱스 키 비교
- 메모리 임시 테이블 작업
- 디스크 임시 테이블 작업

이러한 작업들이 얼마나 필요한지 예측하고 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾는다. 

코스트 모델의 설정 값
- server_cost : 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용관리
- engine_cost : 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리

두 테이블은 공통 5개 칼럼을 가짐
- cost_name : 코스트 모델의 각 단위 작업
- default_value : 각 단위 작업의 비용(기본 값)
- cost_value : DBMS 관리자가 설정한 값 (NULL 이면 default_value 칼럼 비용 사용)

정보성
- last_update : 단위 작업의 비용 변경된 시점
- comment : 비용에 대한 추가 설명


engine_cost는 여기에 2개 칼럼을 더 가짐
- engine_name : 비용이 적용된 스토리지 엔진 (스토리지엔진에 따라 비용 다르게 적용가능)
- device_type : 디스크 타입 (8.0에서는 아직 활용 X)

 

 

 

실행계획 확인

MySQL 서버의 실행 계획은 DESC 또는 EXPLAIN 명령으로 확인 가능

또한 3가지 포맷이 존재

 

EXPLAIN FORMAT=? 로 사용

 

FORMAT X : 테이블 포맷

FORMAT=TREE : 트리 포맷

FORMAT=JSON : JSON 포맷

 

 

쿼리 실행 시간 확인

 

EXPLAIN ANALYZE (항상 TREE FORMAT)


쿼리 실행 계획과 단계별 소요된 시간 정보를 확인 가능 (실제 퀄 가 실행되고 나서 실행계획과 소요 시간을 보여줌)


* SHOW PROFILE (어떤 부분에서 시간이 많이 소요되는지 확인, 단계별로 X)

 


TREE 포맷의 실행 계획에서 들여쓰기는 호출 순서를 의미

실제 실행 순서

  • 들여쓰기가 같은 레벨 : 상단에 위치한 라인이 먼저 실행
  • 들여쓰기가 다른 레벨 : 가장 안쪽에 위치한 라인이 먼저 실행



EXPLAIN ANALYZE 명령의 결과에는 단계별 실제 소용된 시간(actual time), 처리한 레코드 건수(rows), 반복 횟수(loops)가 표시

actual time : 테이블에서 일치하는 레코드를 검색하는데 걸린 시간(밀리초) 
- 2개의 값이 있으며 
  첫 번째는 첫째 레코드를 가져오는데 걸린 평균 시간, 
  두 번째는 마지막 레코드를 가져오는데 걸린 평균 시간

rows : 테이블에서 읽은 조건에 일치하는 테이블의 평균 레코드 건수
loops : 테이블의 레코드를 찾는 작업이 반복된 횟수

 

실행계획 분석

EXPLAIN 명령을 실행하면 쿼리 문장의 특성에 따라 표 형태로 된 1줄 이상의 결과가 표시

위쪽에 출력된 결과일수록(id칼럼의 값이 작을 수록) 쿼리의 바깥 부분 or 먼저 접근한 테이블

아래쪽에 출력된 결과일 수록 (id 칼럼 값이 클수록) 쿼리의 안쪽 부분 or 나중에 접근한 테이블

(UNION, 상관 서브쿼리와 같은 경우 순서대로 표시되지 않을 수 있다.)

 

 

id 칼럼

하나의  SELECT 문장은 다시 1개 이상의 하위 SELECT 문장을 포함 가능

SELECT 키워드 단위로 구분한 것을 단위(SELECT) 쿼리 라고 표현

id 칼럼은 단위 SELECT 쿼리 별로 부여되는 식별자 값

 

select_type 칼럼

각 단위 SELECT 쿼리가 어떤 타입인지 표시되는 칼럼

 

  • SIMPLE
    • UNION, 서브쿼리 사용하지 않는 단순 SELECT 쿼리 (조인 포함)
    • 일반저긍로 제일 바깥 SELECT 쿼리가 SIMPLE로 표시
  • PRIMARY
    • UNION, 서브쿼리를 가지는 SELECT 쿼리의 실행계획에서 가장 바깥쪽에 있는 단위 쿼리 (하나만 존재)
  • UNION
    • UNION 결합 단위 SELECT 쿼리 중 첫 번째를 제외한 나머지
    • 첫번째는 DERIVED
  • DEPENDENT UNION
    • UNION을 사용하면서 외부 쿼리에 의해 영향을 받음
    • 내부 쿼리가 외부 값을 참조해서 처리될때
  • UNION RESULT
    • UNION ALL은 8.0부터 임시 테이블을 사용하지 않도록 개선됨
    • 하지만 UNION은 여전히 임시테이블에 결과를 버퍼링
    • 즉, RESULT 는 임시테이블 (단위 쿼리가 아니기에 id값은 부여 X)
  • SUBQUERY
    • FROM 절 이외에서 사용된 서브쿼리
    • 참고로 SELECT 절에서 사용된 서브쿼리는 '중첩된 쿼리'
  • DEPENDENT SUBQUERY
    • 서브쿼리가 바깥쪽 SELECT 쿼리에서 정의된 칼럼 사용시
  • DERIVED (파생)
    • FROM 절에서 사용된 서브쿼리
    • 파생테이블에도 인덱스 추가되도록 최적화됨
  • DEPENDENT DERIVED
    • FROM 절에 서브쿼리가 외부 칼럼 참조
    • 래터럴 조인 (LATERAL)
  • UNCACHEABLE SUBQUERY
    • 서브쿼리의 캐시 사용 가능 여부 
    • 불가능한 경우
      • 사용자 변수가 서브쿼리에 사용
      • NOT-DETERMINISTIC 석성의 스토어드 루틴이 서브쿼리내 사용
      •  UUID()나 RAND()와 같이 결과값이 호출마다 달라지는 함수가 서브쿼리에 사용
  • UNCACHEABLE UNION
    • 마찬가지
  • MATERIALIZED
    • 주로 FROM 절이나 IN(subquery) 형태의 쿼리에서 사용된 서브쿼리의 최적화를 위해 사용
    • 서브 쿼리의 내용을 임시 테이블로 구체화 후 임시테이블과 조인하는 형태로 최적화

 

캐시 생성

  • SUBQUERTY는 처음 딱 한 번만 생성
  • DEPENDENT SUBQUERY는 외부 쿼리의 값 단위로 캐시 생성

 

table 칼럼

MySQL 서버의 실행 계획은 단위 SELECT 쿼리 기준이 아닌 테이블 기준으로 표시

 

  • 별도의 테이블 사용 X : NULL
  • < > 로 둘러싸인 테이블은 임시테이블
    • derived N, union M,N 등 이런식으로 id값을 뒤에 표시하면서 어떤 종류의 임시테이블인지 나타

 

실행계획의 id, select_type, table 칼럼은 각 라인에 명시된 테이블이 어떤 순서로 실행되는지에 대한 근거를 표시

 

partitions 칼럼

말그대로 파티션으로 레코드들을 나눠 저장한다.

어느 파티션을 필요한지(참조하는 지) 파티션 이름이 표시되며

파티션의 경우 실행계획의 type이 ALL 인데 

이는 진짜 풀 테이블 스캔이 아닌 해당 파티션에 대한 풀테이블  스캔을 했다는 의미이다.

 

대부분 RDBMS에서 지원하는 파티션은 물리적으로 개별 테이블 처럼 별도의 저장공간을 가지기때문

 

쿼리의 실행 계획에서 type 이후의 칼럼은
MySQL서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타냄

 

여기서 방식이라 함은 인덱스를 사용해서 읽었는지, 아니면 풀테이블 스캔으로 읽었는지 등을 의미

type 칼럼

일반적으로 쿼리 튜닝시 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 type 칼럼은 반드시 체크

type 칼럼은 쉽게 각 테이블의 접근 방법으로 해석하면 된다.

 

ALL 을 제외한 나머지는 모두 인덱스 사용하는 접근방법

하나의 단위 SELECT 쿼리는 하나의 접근방법만 사용 가능하며, 

index_merge를 제외한 나머지 접근 방법은 하나의 인덱스만 사용 (즉, 접근방법 2개이상 표시 X)

 

 

성능이 빠름============================================================================

  • system
    • 레코드 1건만 존재하는 테이블 or 한 건도 존재하지 않은 테이블 참조
    • MyISAM, MEMORY 테이블에서만 사용 (InnoDB로 변환시 ALL or Index)
  • const
    • 테이블의 레코드 건수와 관계없이 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건절, 반드시 1건 반환 쿼리
      • 동등 조건 검색
    • 다중 칼럼으로 구성된 프라이머리 키, 유니크 키 중에서 인덱스 일부 칼럼만 조건 사용시 ref 타입 사용
      • 이는 데이터를 읽어보지 않고서 1건이라는 것을 확신할 수 없기때문
  • eq_ref
    • 조인에서 첫 번째 읽은 칼럼을 이용해 두 번째 테이블을 프라이머리 키나 유니크 키로 동등 조건 검색
    • (두 번째 테이블은 반드시 1건의 레코드 반환) 
      • 때문에 이것도 다중 칼럼시 NOT NULL 이여야하며 모두 사용되어야함
  • ref
    • 조인의 순서와 인덱스 종류에 관계없이 동등 조건으로 검색 (1건 이상 가능)
  • fulltext
    • 전문 검색 인덱스 사용 접근 방법
    • 전문 검색 인덱스 + 일반 인덱스 사용시 일반 인덱스가 [const, eq_ref, ref] 가 아니면 fulltext를 사용
      • range가 더 나을때도 있다함
  • ref_or_null
    • ref 접근 방법과 같은데 NULL 비교가 추가됨
  • unique_subquery
    • WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리를 위한 접근 방법
    • 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 사용
    • 8.0버전에서는 실제로 더 최적화된 다른 실행계획을 보임 (semijoin)
    • 아래 사진 참고
  • index_subquery
    • IN 연산자의 특성상 IN(subquery) or IN(상수 나열) 형태의 조건은 괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거되어야 함
    • 서브쿼리의 결과의 중복된 값을 인덱스를 이용해 제거할 수 있을때 사용하는 접근 방법
  • range
    • 인덱스 레인지 스캔 형태의 접근 방법 (범위 검색)
    • * 일반적으로 인덱스 레인지 스캔은 range, const, ref를 묶어서 지칭
    • 범위 검색 [<, >, IS NULL, BETWEEN IN, LIKE] 등의 연산자를 이용해 인덱스 검색할 때 사용
  • index_merge
    • 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어 낸 후, 그 결과를 병합해서 처리하는 방식
    • 효율적이진 않음
      • 여러 인덱스 읽어야함 -> range보다 효율성 떨어짐
      • 전문 검색 인덱스 사용 쿼리에서는 index_merge 미적용 (fulltext가 더 우선순위 높음)
      • 처리된 결과는 항상 2개 이상 집합, 두 집합의 교,합집합, 중복제거 같은 부가적인 작업 더 필요
  • index
    • 인덱스 풀 스캔을 의미
    • index 접근 방법은 다음 조건 가운데 1+2, 1+3 조건을 충족하는 쿼리에서 사용됨
      1. range나 const, ref 같은 접근 방법으로 인덱스 사용불가 시
      2. 인덱스에 포함된 칼럼만으로 처리할 수 있는 경우(커버링 인덱스 등)
      3. 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우 (별도의 정렬 작업 피할 수 있는 경우)
  • ALL
    • 풀 테이블 스캔 방식
    • InnoDB에서는 리드 어헤드 기능을 제공
      • 리드 어헤드 : 대량의 디스크I/O를 유발하는 작업을 위해 한꺼번에 많은 페이지를 읽어들이는 기능

성능이 느림 ============================================================================

unique_subquery의 더 최적화된 다른 실행계획

 

 

 

저작자표시 비영리 변경금지 (새창열림)

'Back-End > DB' 카테고리의 다른 글

정렬 방식 실렬 방식 실습  (0) 2025.07.01
Real MySQL 8.0 [데이터 처리 방식 및 정렬]  (0) 2025.06.27
Real My Sql 8.0 [인덱스]  (1) 2025.06.20

'Back-End/DB'의 다른글

  • 현재글Real MySQL 8.0 [실행계획]

관련글

티스토리툴바