Real MySQL

2 minute read

[DB] Real MySQL - 6장 실행 계획

DBMS에서 쿼리를 최적으로 실행하기 위해 데이터를 비교해 최적의 실행 계획을 Optimizer(비용기반, 규칙 기반)가 수행

  1. 요청 쿼리를 쪼개서 mysql이 이해할 수 있도록 분리

  2. sql 파싱 정보를 확인해 어떤 테이블, 어떤 인덱스를 읽을지 선택

    불필요한 조건 제거, 테이블 조인할 순서 결정, 인덱스 결정, 임시 테이블 결정

  3. 테이블 읽기 순서와 인덱스를 이용해 데이터 수집

레코드 건수와 인덱스 유니크한 값의 개수 등을 통계 정보로 관리, analyze로도 갱신 가능

select 쿼리 앞에 explain 달아서 실행계획 조회 가능

  • id 칼럼

    select 쿼리별로 부여되는 식별자, 하나의 select에서 여러 테이블 조인하면 테이블 수만큼 실행 계획 레코드 출력되지만 id는 하나

  • Select_type 칼럼

    simple: 단순 select

    Primary: 가장 바깥쪽에 있는 단위 쿼리

    union: select 쿼리 중 첫번째를 제외한 두번째 이후 단위 select 쿼리

    dependent union: 외부의 영향에 의해 영향 받는 union 쿼리

    union result: union 결과 담아두는 테이블

    Subquery: from절 이외에서 사용하는 서브 쿼리(nested query: select, sub query: where)

    dependent subquery: 바깥쪽 select 쿼리에서 정의된 컬럼 사용하는 경우

    derived: 서브쿼리가 from절에서 사용되는 경우

    -> 조인으로 풀어서 사용할 수 없는지 확인한다.

    uncacheable union: 두 개의 키워드 속성이 혼합된 select_type

  • table 칼럼

    별칭 있으면 별칭, <>에 쌓여있으면 임시 테이블

  • type 컬럼

    레코드를 어떠한 방식으로 읽었는지, join type으로도 설명하는데 인덱스로 읽었는지 풀 테이블 스캔으로 읽었느니와 같은 테이블 접근 방식으로 생각하면 된다.

    all: 처음부터 끝까지 읽어서 가져오는 풀 테이블 스캔

    system: 레코드 하나거나 0개인 테이블

    const: pk나 unique 키 칼럼 사용하는 where 조건절

    eq_ref: 여러 테이블 조인되는 쿼리에서 처음 읽은 테이블 칼럼을 다음 테이블의 pk unique로 사용할 때

    ref: 조인의 순서와 관계없이 인덱스 종류와 무관하게 동등 조건으로 검색할 때, ref_or_null

    fulltext: fulltext 인덱스를 이용해 레코드 읽을 때

    unique_subquery: where 조건절에서 in (subquery), 중복 제거 불필요

    index_subquery: in (subquery), 중복 제거 필요

    range: 인덱스 레인지 스캔, 빠름

    index_merge: 2개 이상의 인덱스로 나온 결과를 병합

    index: 인덱스 풀 스캔, 풀 테이블 스캔과 레코드 건수는 같지만 데이터 크기가 더 작아서 빠르게 처리된다.

  • possible_keys 칼럼

    사용할 뻔 했던 인덱스 목록

  • key 칼럼

    최종 선택된 인덱스

  • Key_len 칼럼

    인덱스의 각 레코드에서 몇 바이트까지 사용했는지

  • ref 칼럼

    equal 비교 조건으로 뭐가 제공됐는지

  • rows 칼럼

    예측했던 레코드 건수, 얼마나 많은 레코드를 디스크로부터 읽고 체크해야 하는지

  • extra 칼럼

    const row not found: const로 읽었는지 레코드가 없는 경우

    distinct: 중복 제거를 위해 사용했다는 의미

    full scan on NULL key: null을 만나면 풀 테이블 스캔 사용할 것이라는 의미

    impossible ~~: ~~ 절 레코드 없을 때

    기타 중요한 정보 포함

6.3 MySQL의 주요 처리 방식

  • 풀 테이블 스캔

    레코드 건수가 너무 작아서 인덱스를 통하는 것보다 풀 테이블 스캔이 빠를 때

    조건이 없는 경우

    조건 일치 레코드 건수가 너무 많은 경우

    innoDB에서는 필요해질 것 같은 값을 read ahead 해와서 빠른 처리 한다.

  • order by

    인덱스를 이용한 정렬, 드라이빙 테이블만 정렬, 임시 테이블을 이용한 정렬이 이루어진다.

    정렬 기준이 많거나, group by / distinct 처리, union과 같이 임시 테이블 재정렬, 랜덤하게 결과 렉코드 반환하면 인덱스 정렬이 안돼 Filesort 라는 별도 처리를 이용하고 정렬을 위한 별도의 메모리 공간을 sort buffer라고 한다.

    레코드가 소량일 때는 sort buffer를 이용해 빠른 정렬을 할 수 있지만 레코드 건수가 버퍼보다 커지면 여러 조각으로 나눠서 처리한다. 이때 임시저장에 디스크가 필요해지고 정렬된 레코드를 다시 병합하며서 정렬을 수행하는 멀티 머지가 발생한다.

  • group by

    인덱스를 이용하는 group by: 모든 인덱스를 다 읽는 경우

    loose 인덱스 스캔을 이용하는 group by: 인덱스를 필요한 부분만 읽는 경우로 인덱스를 차례로 스캔하면서 필요한 것을 찾아내고 유니크 값을 가져와 처리

    임시 테이블을 이용하는 group by: 인덱스 풀 스캔하며 검색해 임시 테이블에 저장, 조인 완료까지 유니크 키 생성하며 반복

  • distinct

  • 임시 테이블

  • 테이블 조인

    inner join(join), outer join, natural join