책너두 (Real MySQL 8.0 1권) 41일차 (~448p)

요약

• type 칼럼의 종류중 아래의 내용에 해당 하는 값을 이해하게 됨
  • ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL
  • 인덱스 레인지 스캔 = const, ref, range
• 실행 계획의 possible_keys 칼럼의 의미를 이해하게 됨.
  • 사용될 뻔했던 키 값들이 표시됨
• 실행 계획의 key 칼럼의 의미를 이해하게 됨.
  • 실제로 사용된 key 값들이 나열됨.

메모

ref_or_null

• ref 접근 방법과 같은데, NULL 비교(IS NULL)가 추가된 형태임.
• 실무에서 많이 활용되지는 않지만 만약 사용한다면 나쁘지 않은 접근 방법임.

unique_subquery

• WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리를 위한 접근 방법임.
• 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 이 접근 방법을 사용함.

8.0 버전에서는 WHERE 조건절에 IN(subquery) 형태의 세미 조인을 최적화하는 많은 기능이 도입되어 실제로는 더 최적화된 실행 계획을 보일 것임.

index_subquery

• IN 연산자 특성상 IN(subquery) 또는 IN(상수 나열) 형태의 조건은 괄호 안에 있는 값의 목록에서 중복 값이 먼저 제거돼야 함.
• 위 unique_subquery는 IN(subquery)의 subquery가 중복된 값을 만들어내지 않는다는 보장이 있어 별도의 중복 제거가 필요 없었음.
• 업무 특성상 IN(subquery) 에서 subquery 가 중복된 값을 반환할 수 있음.
  • 이때, 서브쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을 때, index_subquery 접근 방법이 사용됨.

range

• 우리가 알고 있는 인덱스 레인지 스캔 형태의 접근 방법임.
• range 는 인덱스 하나의 값이 아니라 범위로 검색하는 경우를 의미함.
  • “<, >, IS NULL, BETWEEN, IN, LIKE” 등 연산자를 이용해 인덱스를 검색할 때 사용됨.
• 일반적으로 애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법임.
• 얼마나 많은 레코드를 필요로 하느냐에 따라 차이는 있겠지만, range 접근 방법도 위 type 칼럼 순서에 비해서는 낮지만, 상당히 빠르며, 모든 쿼리가 이 접근 방법만 사용해도 최적의 성능이 보장된다고 볼 수 있음.

이 책에서 인덱스 레인지 스캔 이라고 하면 const, ref, range 세 접근 방법을 묶어서 지칭하는 것임.

index_merge

• 지금까지 설명한 다른 접근 방법과 달리, index_merge 접근 방법은 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식임.
  • 하지만 index_merge 접근 방법은 이름만큼 그렇게 효율적으로 작동하는 것은 아님.
  • index_merge 접근 방법은 다음과 같은 특징이 있음.
    • 여러 인덱스를 읽어야 하므로 range 접근 방법보다 효율성이 떨어짐
    • 전문 검색 인덱스를 사용하는 쿼리에서는 index_merge 가 적용되지 않음.
    • index_merge 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 되므로 그 두 집합의 교집합이나 합집합, 또는 중복 제거와 같은 부가적인 작업이 더 필요함.
• MySQL 매뉴얼에서 index_merge 접근 방법의 우선순위는 ref_or_null 바로 다음에 있음.
  • 하지만 이 책에서 위의 특성 때문에 우선순위 위치를 range 접근 방법 아래로 옮김. (p434 참고)
  • index_merge 접근 방법 사용될 때, 실행 계획에 좀 더 보완적인 내용이 표시됨
    • 실행 계획의 Extra 부분에서 더 자세히 살펴보자.

index

• index 접근 방법은 많은 사람이 자주 오해하는 접근 방법임
• 접근 방법이 index 라서 MySQL 서버에 익숙하지 않은 사람은 “효율적으로 인덱스를 사용하는구나” 라고 생각하게 만듦.
• 하지만 index 접근 방법은 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미함.
  • range 접근 방법과 같이 효율적으로 인덱스의 필요한 부분만 읽는 것이 아님.
• index 접근 방법은 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔 방식과 비교했을 때 비교하는 레코드 건수는 같음.
  • 하지만 인덱스는 일반적으로 데이터 파일 전체보다 크기가 작으므로 인덱스 풀 스캔시, 풀 테이블 스캔보다 빠르게 처리됨.
  • 또, 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 훨씬 효율적이라 할 수 있음.
  • index 접근 방법은 아래 조검에서 1,2 조건을 충족하거나 1,3 조건을 충족하는 쿼리에서 사용되는 읽기 방식임
    1. range나 const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
    2. 인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우(즉, 데이터 파일을 읽지 않아도 되는 경우)
    3. 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우(즉, 별도의 정렬 작업을 피할 수 있는 경우)

ALL

• 흔히 아는 풀 테이블 스캔을 의미하는 접근 방법임.
• 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거(체크 조건이 존재할 때)하고 반환함.
• 풀 테이블 스캔은 지금까지 설명한 접근 방법으로 처리할 수 없을 때 가장 마지막에 선택하는 가장 비효율 적인 방법임.
• 다른 DBMS와 같이 InnoDB 도 풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 작업을 위해 한꺼번에 많은 페이지를 읽어 들이는 기능을 제공함.
  • InnoDB 에서는 이 기능을 “리드 어헤드(Read Ahead)” 라고 함.
    • 한번에 여러 페이지를 읽어서 처리할 수 있음.
    • 데이터 웨어하우스(Data Warehouse)나 배치 프로그램처럼 대용량의 레코드를 처리하는 쿼리에서는 잘못 튜닝된 쿼리(억지로 인덱스를 사용하게 튜닝된 쿼리)보다 더 나은 접근 방법이기도 함.
    • 쿼리를 튜닝한다는 것이 무조건 인덱스 풀 스캔이나 테이블 풀 스캔을 사용하지 못하게 하는 것이 아니다.
• 일반적으로 index 와 ALL 접근 방법은 작업 범위를 제한하는 조건이 아니므로 빠른 응답을 사용자에게 보내야 하는 웹 서비스 등과 같은 온라인 트랜잭션 처리 환경에 적합하지 않음.
  • 테이블이 매우 작지 않다면 실제로 테이블에 데이터를 어느 정도 저장한 상태에서 쿼리 성능을 확인해 보고 적용하는 것이 좋음.

MySQL 서버에서 인접한 페이지가 연속해서 몇 번 읽히면 백그라운드로 작동하는 읽기 스레드가 최대 64개의 페이지씩 한꺼번에 디스크로부터 읽어 들이기 때문에 한 번에 페이지 하나씩 읽어 들이는 작업보다는 상당히 빠르게 레코드를 읽을 수 있음. 이러한 작동 방식을 리드 어헤드(Read Ahead) 라고 함.

또, MySQL 8.0 버전에서는 병렬 쿼리(Parellel Query) 기능이 도입됐는데, 아직 초기 구현 상태여서 조건 없이 전체 테이블 건수를 가져오는 쿼리 정도만 병렬로 실행될 수 있음.

 

possible_keys 칼럼

• 실행 계획에 있는 이 칼럼 또한 사용자 오해를 자주 불러 일으킴
• MySQL 옵티마이저는 쿼리를 처리하기 위해 여러 가지 처리 방법을 고려하고, 그중에서 비용이 가장 낮을 것으로 예상하는 실행 계획을 선택해 쿼리를 실행함.
  • 그런데 possible_keys 칼럼에 있는 내용은 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록일 뿐임.
  • 말그대로 “사용될 법했던 인덱스 목록” 인 것임.
  • 실제로 실행 계획을 보면, 그 테이블의 모든 인덱스가 목록에 포함되어 나오는 경우가 허다하기에 특별한 경우를 제외하면 그냥 무시해도 됨.
  • 따라서 절대 possible_keys 칼럼에 인덱스 이름이 나열됐다고 해서 그 인덱스를 사용한다고 판단하지 않도록 주의하자.

key 칼럼

• possible_keys 칼럼이 인덱스가 사용 후보였던 반면, key 칼럼에 표시되는 인덱스는 최종 선택된 실행 계획에서 사용하는 인덱스를 의미함.
• 그러므로 쿼리 튜닝 시 key 칼럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요함
• key 칼럼에 표시되는 값이 PRIMARY 인 경우, 프라이머리 키를 사용한다는 의미임.
  • 그 이외의 값은 모두 테이블이나 인덱스를 생성할 떄 부여했던 고유 이름임.
• 실행 계획의 type 칼럼이 index_merge가 아닌 경우, 반드시 테이블 하나당 하나의 인덱스만 이요할 수 있음.
  • 하지만 index_merge 실행 계획이 사용될 때는 2개 이상의 인덱스가 사용되는데, 이때는 key 칼럼에 여러 개의 인덱스가 “,” 로 구분되어 표시됨.
• 실행 계획의 type 이 ALL 일 때와 같이 인덱스를 전혀 사용하지 못하는 경우, key 칼럼은 NULL 로 표시됨.