내맘대로 정리하는 Real MySQL #6장) 실행계획 - 실행계획 읽기 편

<내맘대로 정리하는> 시리즈는, 책을 읽으며 몰랐던 내용을위주로 정리한 내용 그대로 포스팅하는 시리즈입니다 ^^

원문의 문맥이 궁금하면 (좋은 책이니) 이 참에 하나 장만하는 것은 어떤가요??

DBMS에서는 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하며, 그러한 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업이 필요하다. DBMS에서는 옵티마이저가 이러한 기능을 담당한다.

MySQL에서는 EXPLAIN이라는 명령으로 쿼리의 실행 계획을 확인할 수 있다.

옵티마이저

MySQL 서버에서 쿼리가 실행되는 과정은 크게 3가지로 나눌 수 있다.

1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다.
2. SQL의 파싱 정보(파싱 트리)를 확인하며 어떤 테이블로부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
3. 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

두 번째 단계(최적화 및 실행 계획 수립)를 옵티마이저가 처리한다.

옵티마이저 종류

• 규칙 기반 최적화 : 옵티마이저에 내장된 우선순위에 따라 실행계획을 수립. 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않음
• 비용 기반 최적화 : 쿼리를 처리하기 위한 여러 방법을 만들고 각 방법의 비용을 테이블 통계를 바탕으로 예측해, 최소 비용이 소요되는 처리 방식을 선택

통계 정보

MySQL은 비용 기반의 최적화를 사용하지만, 통계정보가 그리 다양하지는 않다.
대략의 레코드 건수와 인덱스의 유니크한 값의 개수 정도를 관리한다.

실행 계획

EXPLAIN 쿼리를 했을 때 나오는 표의 각 라인은 쿼리 문장에서 사용된 테이블의 개수만큼 출력된다. 실행 순서는 위에서 아래이다.

id 칼럼

SELECT 쿼리별로 부여되는 식별자 값으로, SELECT 문장은 하나인데 여러 개의 테이블이 조인되는 경우에는 id 값이 증가하지 않고 같은 id가 부여된다.

select_type 칼럼

• SIMPLE : UNION 및 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리
• PRIMARY : UNION 및 서브쿼리를 사용하는 쿼리에서 가장 바깥쪽 쿼리
• SUBQUERY : FROM 절에서 사용되지 않은 서브 쿼리
• DERIVED : FROM 절에 사용된 서브 쿼리

table 칼럼

테이블이 표시되며, 별칭이 부여된 경우는 별칭을 표시

< > 으로 둘러싸인 이름은 임시 테이블을 의미한다.

type 칼럼

반드시 체크해야 할 중요 정보로 '테이블의 접근 방식'으로 이해하면 된다. 인덱스를 사용해 레코드를 읽었는지 아니면 테이블을 처음부터 끝까지 읽었는지 등의 방식이다.

• system : 레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조 (MyISAM 혹은 MEMORY 에서만 보인다)
• const : 건수에 관계없이 쿼리가 PK나 unique column을 사용하는 WHERE 조건 절을 가지고 있으며, 반드시 1건만 반환하는 경우
• eq_ref : 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시되며, 조인에서 처음 읽은 테이블의 칼럼 값을 그 다음 읽어야 할 테이블의 PK나 Unique key의 검색 조건에 사용하는 경우
• ref : 인덱스의 종류와 관계없이 동등 조건으로 검색할 때 (인덱스를 하나만 가져오면 되는 경우)
• ref_or_null : ref와 같지만 NULL 비교가 추가된 형태
• range : 인덱스 레인지 스캔. 상당히 빠른 접근 방법이며, 모든 쿼리가 이 접근 방법만 사용해도 어느 정도의 성능은 보장된다.
• index_merge : 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만든 후, 결과를 병합처리하는 방식
• index : 인덱스 풀 스캔
  • range, const, ref를 사용할 수 없는데 인덱스에 포함된 칼럼으로만 처리할 수 있는 쿼리인 경우 사용
  • range, const, ref를 사용할 수 없는데 인덱스를 이용해 정렬이나 그룹핑 작업이 가능한 경우
• ALL : 풀 테이블 스캔

key 칼럼

최종 선택된 실행 계획에서 사용하는 인덱스

key_len 칼럼

인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값이다.

ref 칼럼

Function만 안나오면 크게 신경쓸 일이 없다.

rows 칼럼

옵티마이저가 비용을 산정하는 방법은 각 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지 예측해 보는 것이다. 대상 테이블에 얼마나 많은 레코드가 포함돼 있는지 또는 각 인덱스 값의 분포도가 어떤지를 통계 정보를 기준으로 조사해서 예측한다.

Extra 칼럼

• Distinct : 쿼리의 DISTINCT를 처리하기 위해 조인하지 않아도 되는 항목은 모두 무시하고 꼭 필요한 것만 조인했으며, 꼭 필요한 레코드만 읽었다
• Full scan on NULL key : col1 IN (SELECT ...) 쿼리 실행 도중 서브 쿼리가 NULL이 나오면 예비책으로 풀 테이블 스캔을 사용한다
• No matching min/max row : 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때 사용 (결과로는 NULL이 들어간다)
• Range checked for each record : 테이블의 인덱스를 사용할지 아니면 풀 테이블 스캔할지를 매번 판단한다
• Scanned N databases : INFORMATION_SCHEMA 를 조회할 때 나오는 값
  • 0 : 특정 테이블의 정보만 요청되어 DB 전체의 메타 정보를 읽지 않음
  • 1 : 특정 DB 내의 모든 스키마 정보가 요청되어 해당 DB의 모든 스키마 정보를 읽음
  • 2 : MySQL 서버 내의 모든 스키마 정보를 다 읽음
• Select tables optimized away : MIN 또는 MAX만 SELECT 절에 사용되거나 또는 GROUP BY로 MIN, MAX를 조회하는 쿼리가 적절한 인덱스를 사용할 수 없을 때 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용
• Using filesort : 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 알고리즘을 수행한다. 많은 부하를 일으키므로 가능하다면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다
• Using index (커버링 인덱스)
  • 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있는 경우. 인덱스를 이용해 처리하는 쿼리에서 가장 큰 부하를 차지하는 부분은 인덱스를 검색해 일치하는 레코드의 나머지 칼럼 값을 가져오기 위해 데이터 파일을 찾아서 가져오는 작업이다. 최악의 경우에는 인덱스를 통해 검색된 결과 레코드 한 건 한 건마다 디스크를 읽어야 할 수 있다
  • 레코드 건수에 따라 차이는 있겠지만 쿼리를 커버링 인덱스로 처리할 수 있을 때와 그렇지 못할 때의 성능 차이는 수십 배에서 수백 배까지 날 수 있다. 하지만 무조건 커버링 인덱스로 처리하려고 인덱스에 많은 칼럼을 추가하면 더 위험한 상황이 초래될 수 있다. 너무 과도하게 인덱스의 칼럼이 많아지면 인덱스의 크기가 커져서 메모리 낭비가 심해지고 레코드를 저장하거나 변경하는 작업이 매우 느려질 수 있기 때문이다.
• Using index for group-by : group by 쿼리에서 루스 인덱스 스캔을 사용. WHERE 절의 조건이 검색하는데 사용했던 인덱스를 GROUP BY 처리가 다시 사용할 수 있을 때만 루스 인덱스 스캔을 쓸 수 있다. WHERE 조건 절의 인덱스와 GROUP BY 절의 인덱스가 다르다면, 옵티마이저는 WHERE 조건절이 인덱스를 사용하도록 실행 계획을 수립하는 경향이 있다. 때로는 전혀 작업 범위를 좁히지 못하는 WHERE 조건이라 하더라도 GROUP BY 보다는 WHERE 조건이 먼저 인덱스를 사용할 수 있게 실행 계획이 수립된다.
• Using join buffer : 조인 버퍼 사용. 드리븐 테이블에 검색을 위한 적절한 인덱스가 없다면 드라이빙 테이블로부터 읽은 레코드의 건수만큼 매번 드리븐 테이블을 풀 테이블 스캔이나 인덱스 풀 스캔해야 할 것이다. 이때 드리븐 테이블의 비효율적인 검색을 보완하기 위해 MySQL 서버는 드라이빙 테이블에서 읽은 레코드를 임시 공간에 보관해두고 필요할 때 재사용할 수 있게 해준다. 읽은 레코드를 임시로 보관해두는 메모리 공간을 '조인 버퍼'라 한다.
• Using temporary : MySQL이 쿼리를 처리하는 동안 중간 결과를 담아두기 위해 임시 테이블을 사용한다. 임시 테이블은 메모리상에 생성될 수도 있고 디스크상에 생성될 수도 있다.
• Using where : MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우 (작업 범위 제한 조건이 아닌 체크 조건은 MySQL 엔진 레이어에서 처리된다)
  • Using where가 표시되었다면, 스토리지 엔진에 쓸모 없는 일을 추가로 시켰다는 것을 의미한다. 이는 MySQL이 스토리지 엔진과 MySQL 엔진으로 이원화된 구조 때문에 발생하는 문제점이다.
  • MySQL 5.1의 InnoDB 플러그인 버전부터는 이원화된 구조의 불합리를 제거하기 위해 WHERE 절의 범위 제한 조건뿐 아니라 체크 조건까지 모두 스토리지 엔진으로 전달된다. 스토리지 엔진에서는 그 조건에 정확히 일치하는 레코드만 읽고 MySQL 엔진으로 전달하는 것이다. (이러한 기능을 Condition push down이라 한다)

추가적인 실행계획

EXPLAIN EXTENDED : Filtered라는 칼럼을 추가로 보여준다. filtered 칼럼에는 MySQL 엔진에 의해 필터링되어 제거된 레코드는 제외하고 최종적으로 레코드가 얼마나 남았는지 비율을 보여준다 (통계에 의해 예측된 값)

EXPLAIN EXTENDED를 사용하고 SHOW WARNINGS 명령을 보면, 옵티마이저가 쿼리를 어떻게 해석했고, 어떻게 쿼리를 변환했으며 어떤 특수한 처리가 수행되었는지 확인할 수 있다.

EXPLAIN PARTITIONS : 파티션 테이블의 실행 계획 정보를 더 자세히 알 수 있다.

주의사항

• Type 칼럼에 ALL, index
• Extra 칼럼에 Range checked for each record, Using filesort, Using join buffer, Using temporary, Using where
  • 쿼리의 실행 계획에서 이러한 문구가 사라질 수 있다면 최선이겠지만 그렇지 않더라도 성능상 허용 가능하다면 넘어가도 좋을 듯하다. 단, 반드시 자세히 검토해야 한다는 사실을 잊지 말자.