DB

[MySQL]MySQL 벼락치기(7) - 실행계획(1)

강씨아저씨 2018. 9. 10. 00:30

이번 포스팅은 사내에서 MySQL 관련 내용 발표를 위해 Real MySQL(http://wikibook.co.kr/real-mysql/) 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 포스팅에서는 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다. 

MySQL 역시 내용이 많기 때문에 시리즈로 나눠서 정리할 예정이다.

실행 계획 분석

MySQL 에서 쿼리의 실행 계획을 확인하려면 EXPLAIN 명령을 사용하면 된다. EXPLAIN 명령은 다음과 같이 EXPLAIN 키워드 뒤에 확인하고 싶은 SELECT 쿼리 문장을 적으면 된다. 실행계획의 결과로 여러 가지 정보가 표 형태로 표시된다. 실행 계획중에는 possible_keys 항목과 같이 내용은 길지만 거의 쓸모가 없는 항목도 있다.

다른DBMS 와는 달리 MySQL 에서는 필요에 따라 실행 계획을 산출하기 위해 쿼리의 일부분을직접 실행할 때도 있다. 때문에 쿼리 자체가 상당히 복잡하고 무거운쿼리인 경우에는 실행 계획의 조회 또한 느려질 가능성이 있다. 그리고 UPDATE, INSERT, DELETE 문장에 대해서는 실행 계획을확인 할 방법이 없다. UPDATE, INSERT, DELETE 문장의 실행 계획을 확인하려면 WHERE 조건절만 같은 SELECT 문장을 만들어서 대략적으로 계획을 확인해 볼 수 있다. 

이제부터는 실행계획에 표시되는 각 컬럼이 어떤 것을 의미하는지 그리고 각 컬럼에 어떤 값들이 출력될 수 있는지를 알아보자.

id 컬럼

실행 계획에서 가장 왼쪽에 표시되는 id 컬럼은 SELECT 쿼리별로 부여되는 식별자 값이다. 만약 하나의 SELECT 문장 안에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id가 부여된다. 

하지만 다음 쿼리의 실행 계획에서는 쿼리 문장이 3개 단위 SELECT 쿼리로 구성돼 있으므로 실행 계획의 각 레코드가 각기 다른 id를 지닌 것을 확인 할 수 있다.

select_type 컬럼

SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼이다. 

  • SIMPLE
    UNION 이나 서브 쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우, 해당 쿼리 문장의 select_type 은 SIMPLE로 표시된다. 쿼리 문장이 아무리 복잡하더라도 실행 계획에서 select_type이 SIMPLE인 쿼리는 반드시 하나만 존재한다. 일반적으로는 제일 바깥 SELECT 쿼리의 select_type이 SIMPLE로 표시된다. 
  • PRIMARY
    UNION 이나 서브 쿼리가 포함된 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리는 select_type이 PRIMARY 로 표시된다. 
  • UNION
    UNION 으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후의 단위 SELECT 쿼리의 select_type은 UNION 으로 표시된다. 
  • DEPENDENT UNION
    DEPENDENT UNION select_type 또한 UNION select_type 과 같이 쿼리에 UNION 이나 UNION ALL로 집합을 결합하는 쿼리에서 표시된다. 여기서 DEPENDENT는 UNION 이나 UNION ALL로 결합된 단위 쿼리가 외부의 영향에 의해 영향을 받는것을 의미 한다. 이렇게 내부 쿼리가 외부의 값을 참조해서 처리 될 때 DEPENDENT 키워드가 select_type에 표시된다. 
  • UNION RESULT
    UNION 결과를 담아두는 테이블을 의미한다. MySQL 에서 UNION ALL 이나 UNION 쿼리는 모두 UNION의 결과를 임시 테이블로 생성하게 되는데 실행계획상에서 이 임시테이블을 가리키는 라인의 select_type이 UNION RESULT 이다. 
    EXPLAIN
SELECT zc1.city_kr FROM zipcode AS zc1 WHERE zc1.code_serial_num = 101
UNION ALL
SELECT zc2.city_kr FROM zipcode2 AS zc2 WHERE zc2.code_serial_num = 101;

    위 실행 계획의 마지막 "UNION RESULT" 라인의 table 컬럼은 "<union1,2>" 로 표시돼 있는데, 이것은 id가 1번인 단위 쿼리의 조회 결과와 id가 2번인 쿼리의 조회 결과를 UNION 했다는 것을 의미한다.

  • SUBQUERY
    일반적으로 서브 쿼리라고 하면 여러 가지를 통틀어서 이야기 할 때가 많은데, 여기서 SUBQUERY 라고 하는 것은 FROM 절 이외에서 사용되는 서브 쿼리만을 의미한다.
    서브 쿼리는 사용되는 위치에 따라 각각 다른 이름을 지니고 있다.
      • 중첩된 쿼리(Nested Query)
        SELECT 되는 컬럼에 사용된 서브 쿼리를 네스티드 쿼리라고 한다. 
      • 서브 쿼리(Sub Query)
        WHERE 절에 사용된 경우에는 일반적으로 그냥 서브 쿼리라고 한다.
      • 파생 테이블(Derived)
        FROM 절에 사용된 서브 쿼리를 MySQL 에서는 파생 테이블이라고 한다.
  • DEPENDENT SUBQUERY
    서브쿼리가 바깥쪽 SELECT 쿼리에서 정의된 컬럼을 사용하는 경우릴 DEPENDENT SUBQUERY 라고 표현한다. 
  • DERIVED
    서브 쿼리가 FROM 절에 사용된 경우 MySQL은 항상 select_type 이 DERIVED 인 실행계획을 만든다.
    select_type이 DERIVED 인 경우에는 생성되는 임시 테이블이 파생 테이블이며 파생테이블에는 인덱스가 전혀 없으므로 다른 테이블과 조인할 때 성능상 불리할 때가 많다. 때문에 조인으로 해결할 수 있는 경우라면 서브 쿼리보다는 조인을 사용할 것을 강력히 권장한다. 
  • UNCACHEABLE SUBQUERY
    하나의 쿼리 문장에서 서브 쿼리가 하나만 있더라도 실제 그 서브 쿼리가 한 번만 실행되는 것은 아니다. 그런데 조건이 똑같은 서브 쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브 쿼리의 결과를 내부적인 캐시 공간에 담아둔다. UNCACHEABLE SUBQUERY 는 서브 쿼리에 포함된 요소에 의해 캐시 자체가 불가능 할때 표시된다. 
  • UNCACHEABLE UNION
    UNION에 포함된 요소에 의해 캐시가 불가능 할 경우 UNCACHEABLE UNION이 표시된다. 

table 컬럼

MySQL 의 실행 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시된다. 만약 테이블의 이름에 별칭이 부여된 경우에는 별칭이 표시된다. 테이블 컬럼이 <derived> 또는 <union> 과 같이 "< >" 로 둘러싸인 이름이 명시되는 경우가 많은데 이 테이블은 임시테이블을 의미한다. 또한 "< >" 안에 항상 표시되는 숫자는 단위 SELECT 쿼리의 id를 지칭한다. 

type 컬럼

쿼리의 실행 계획에서 type 이후의 컬럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 의미한다. 여기서 방식이라 함은 인덱스를 사용해 레코드를 읽었는지 아니면 테이블을 처음부터 끝가지 읽는 테이블 풀 스캔으로 레코드를 읽었는지 등을 의미한다.
type 컬럼에 표시될 수 있는 값을 확인해 보자.

  • system
    레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법을 system이라고 한다.(InnoDB 테이블에서는 나타나지 않는다.)
  • const
    테이블 레코드의 건수에 관계없이 쿼리가 프라이머리 키나 유니크 키 컬럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리방식이다. 
  • eq_ref
    eq_ref 접근 방법은 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다. 조인에서 처음 읽은 테이블의 컬럼 값을, 그다음 읽어야 할 테이블의 PK 컬럼의 검색 조건에 사용할 때를 eq_ref 라고 한다. 즉, 조인에서 두번째 이후에 읽은 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다. 
  • ref
    eq_ref 와는 달리 인덱스 종류와 관계없이 동등 조건으로 검색할때는 ref 접근 방법이 사용된다. ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref 보다는 빠르지 않다. 
  • fulltext
    MySQL의 전문검색(Fulltext) 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다. 
  • ref_or_null
    ref 와 같은데 NULL 비교가 추가된 형태이다. 
  • unique_subquery
    WHERE 조건절에서 사용될 수 있는IN(subquery) 형태의 쿼리를 위한 접근 방법이다. 또한 서브 쿼리에서 중복되지 않은 유니크한 값만 반환할 떄 이 접근 방법을 사용 한다.
  • index_subquery
    IN(subquery) 에서 subquery 가 중복된 값을 반환할 수는 있지만 중복된 값을 인덱스를 이용해 제거할 수있을때 index_subquery 접근 방법이 사용된다. 
  • range
    range는 우리가 익히 알고 있는 인덱스 레인지 스캔 형태의 접근 방법이다. 주로 "< , > , IS NULL, BETWEEN, IN, LIKE " 등의 연산자를 이용해 인덱스를 검색할 때 사용된다. 모든 쿼리가 이 접근 방법만 사용해도 어느정도의 성능은 보장된다고 볼 수 있다. 
  • index_merge
    index_merge 접근 방식은 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 그 결과를 병합하는 처리 방식이다. 
  • index
    인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다. 테이블을 처음부터 끝까지 읽는 테이블 풀 스캔과 비교했을때 읽는 레코드 건수는 같지만 테이블 풀 스캔보다는 빠르게 처리된다.  
  • ALL
    테이블을 처음부터 끝까지 읽는 테이블 풀 스캔을 의미한다. 테이블 풀 스캔은 지금까지 설명한 접근 방법으로는 처리 할 수 없을 때 가장 마지막에 선택되는 가장 비효율적인 방법이다. 

possible_keys 컬럼

possible_keys 컬럼에 있는 내용은 MySQL 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방식에서 사용되는 인덱스 목록일 뿐이다. 즉, 말 그대로 "사용될 법했던 인덱스" 목록이다. 

key 컬럼

Key 컬럼에 표시되는 인덱스는 최종 선택된 실행 계획에서 사용하는 인덱스를 의미한다.

key_len 컬럼

실제 업무에서 사용하는 테이블은 단일 컬럼으로만 만들어진 인덱스보다 다중 컬럼으로 만들어진 인덱스가 더 많다. 실행 계획의 key_len 커럼의 값은, 쿼리를 처리하기 위해 다중 컬럼으로 구성된 인덱스에서 몇개의 컬럼까지 사용했는지를 우리에게 알려준다. 더 정확하게는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값이다. 

EXPLAIN
SELECT * FROM dept_emp WHERE s_no = 's005';

위의 사진을 보면 key_len 이 12로 표시된다. 이는 s_no 컬럼의 타입이 CHAR(4) 이기 때문에 인덱스에서 앞쪽 12바이트만 유효하게 사용했다는 의미다.

ref 컬럼

접근 방법이 ref 방식이면 참조 조건 으로 어떤 값이 제공됐는지 보여준다. 만약 상수값을 지정했다면 ref 컬럼의 값은 const로 표시되고 다른 테이블의 컬럼값이면 그 테이블 명과 컬럼명이 표시된다. 

EXPLAIN
SELECT * 
FROM zipcode zc1, zipcode2 zc2
WHERE zc1.id = zc2.id;

가끔 쿼리의 실행 계획에서 ref 컬럼의 값이 "func" 라고 표시될 때가 있다. 이는 "Function" 의 줄임말로 참조용으로 사용되는 값을 그대로 사용한것이 아니라, 콜레이션 변환이나 값 자체의 연산을 거쳐서 참조됐다는 것을 의미한다.

EXPLAIN
SELECT * 
FROM zipcode zc, zipcode2 zc2
WHERE zc1.id = (zc2.id - 1)

rows 컬럼

rows 컬럼의 값은 실행 계획의 효율성판단을 위해 예측했던 레코드 건수를 보여준다. 이 값은 각 스토리지 엔진별로 가지고 있는 통계정보를 참조해 MySQL 옵티마이저가 산출해 낸 예상 값이라서 정확하지는 않다. 또한, rows 컬럼에 표시되는 값은 반환하는 레코드의 예측치가 아니라, 쿼리를 처리하기 위해 얼마나 많은 레코드를 디스크로부터 읽고 체크해야 하는지를 의미한다. 
LIMIT 가 포함되는 쿼리는 rows 컬럼에 표시되는 값이 오차가 너무 심해서 별로 도움이 되지 않는다.

extra 컬럼

쿼리 실행계획에서 성능에 관련된 중요한 내용이 Extra 컬럼에 자주 표시된다.

  • const row not found
    type 컬럼이 const 인데 해당 테이블에 1건도 존재하지 않으면 이 내용이 표시된다.
  • Distinct
    DISTINCT를 처리하기 위해 조인하지 않아도되는 항목은 모두 무시하고 꼭 필요한 것만 조인해서 꼭 필요한 레코드만 읽었다는 것을 표현한다
  • Full scan on NULL key
    column1 IN ( SELECT column2 FROM ... ) 조건을 가진 쿼리에서 자주 발생하는데 만약 column1 값이 NULL 이라면 테이블을 풀 스캔하겠다는 사실을 알려주는 키워드다. column1 이 NOT NULL로 정의되어있다면 이러한 키워드는 나타나지 않는다.
  • Impossible HAVING
    쿼리에 HAVING 절의 조건을 만족하는 레코드가 없을 때 나타나는 키워드이다. 
  • Impossible WHERE
    테이블 구조상 WHERE 조건이 항상 FALSE 가 될 수 밖에 없는 경우에 이 키워드가 표시된다.
  • Impossible WHERE noticed after reading const table
    데이터를 스캔해서 실제로 데이터를 읽어보고 WHERE 조건에 일치하는 데이터가 없다는것을 알았을때 이 키워드가 표시된다.
  • No matching min/max row
    MAX( ), MIN( ) 와 같은 집합 합수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을때 나오는 키워드이다. 
  • no matching row in const table
    조인에 사용된 테이블에서 const 방식으로 접근할 때, 일치하는 레코드가 없다면 이 키워드를 표시한다.
  • No tables used
    FROM 절이 없는 쿼리문장을 실행할때 이 키워드를 표시한다.
  • Not exists
    프로그램을 개발하다보면 A테이블에는 존재하지만 B테이블에는 없는 값을 조회해야 할때 주로 NOT IN(subquery), NOT EXISTS 연산자를 주로 사용한다. 그러나 이러한 형태의 조인을 안티 조인이라고 한다. 이와 동일한 처리를 아우터 조인을 이용해서도 구현할 수 있다. 레코드가 많을경우 안티 조인보다는 아우터 조인을 이용하면 빠른 성능을 낼 수 있다.  
    SELECT * 
FROM dept_emp de
LEFT JOIN departments d ON de.dept_no = d.dept_no
WHERE d.dept_no IS NULL

    아우터 조인을 이용해서 안티조인과 동일한 수행을 하는 쿼리에는 이 키워드가 표시된다. 

  • Range checked for each record
    조인 조건에서 상수가 없고 둘다 변수(dept_emp.s_no 와 dept_emp2.s_no) 인 경우 MySQL 옵티마이저는 dept_emp 테이블을 먼저 읽고 dept_emp2 테이블을 읽을 때, 인덱스 레인지 스캔과 테이블 풀 스캔중에서 어느것이 효율적일지 판단할 수 없게 된다. 

    SELECT *
FROM dept_emp, dept_emp2
WHERE dept_emp.s_no >= dept_emp2.s_no

    예를들어 사번이 1번부터 1억까지 있다고 가정했을때 dept_emp 테이블을 처음부터 끝까지 스캔하면서 dept_emp2 테이블에서 dept_emp.s_no >= dempt_emp2.s_no 조건을 만족하는 레코드를 찾아야 해서 dept_emp.s_no 가 1이 인 경우에는 dept_emp2테이블의 1억건을 전부 읽어야 한다는 것이다. 하지만 dept_emp.s_no가 100000000 인 경우에는 dept_emp2 테이블을 단 한건만 읽으면 된다.

    이렇듯 매 레코드마다 인덱스 레인지 스캔을 체크할때 이 키워드가 표시된다. 

  • Scanned N databases
    MySQL 5.0 부터는 기본적으로 MySQL 서버내에 존재하는 DB의 메타정보(테이블, 컬럼, 인덱스 등의스키마 정보)를 모아둔 INFORMATION_SCHEMA라는 DB를 제공한다. 그리고 쿼리를 통해 이러한 메타정보를 검색할때에는 쿼리 실행계획에 이 키워드가 표시된다.
  • Select tables optimized away
    MIN( ) 또는 MAX( ) 만 SELECT 절에 사용되거나 또는 GROUP BY로 MIN( ), MAX( )를 조회하는 쿼리가 적절한 인덱스를 사용할 수 없을 때 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용된다면 이 키워드가 표시된다.  
  • unique row not found
    두개의 테이블이 각각 유니크 컬럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 이 키워드가 표시된다. 
  • Using filesort
    ORDER BY 를 처리하기 위해 인덱스를 이용할 수도 있지만 적절한 인덱스를 사용하지 못할 때는 MySQL 서버가 조회된 레코드를 다시 한 번 정렬해야 한다. ORDER BY 처리가 인덱스를 사용하지 못할 때만 이 키워드가 표시되며 이는 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 알고리즘을 수행하게 된다. 
  • Using index(커버링 인덱스)
    데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을때 이 키워드가 표시된다. 
  • Using index for group-by
    GROUP BY 처리가 인덱스를 이용할 때 쿼리의 실행 계획에서는 이 키워드를 표시한다. GROUP BY 처리를 위해 인덱스를 읽는 방법을 "루스 인덱스 스캔" 이라고 하고 이는 지난번 포스팅에 나와있다.(https://idea-sketch.tistory.com/43)
  • Using join buffer
    일반적으로 빠른 쿼리 실행을 위해 조인이 되는 컬럼은 인덱스를 생성한다. 실제로 조인에 필요한 인덱스는 조인되는 양쪽 테이블 컬럼 모두가 필요한 것이 아니라 조인에서 뒤에 읽는 테이블의 컬럼에만 필요하다. MySQL 옵티마이저도 조인되는 두 테이블에 있는 각 컬럼에서 인덱스를 조사하고 인덱스가 없는 테이블이 있으면 그 테이블을 먼저 읽어서 조인을 실행한다. 
    조인이 수행될 때 드리븐(뒤에 읽는) 테이블의 조인 컬럼에 적절한 인덱스가 없다면 드라이빙(앞에 읽는) 테이블로 부터 읽은 레코드의 건수만큼 매번 드리븐 테이블을 풀 스캔이나 인덱스 풀 스캔해야 할 것이다. 이 때 드리븐 테이블의 비효율적인 검색을 보완하기 위해 MySQL 서버는 드라이빙 테이블에서 읽는 레코드를 임시 공간에 보관해두고 필요할 때 재사용할 수 있게 해준다. 읽은 레코드를 임시로 보관해두는 메모리 공간을 "조인 버퍼"라고 하며 조인 버퍼가 사용되는 실행계획의 Extra 컬럼에는 이 키워드가 표시된다. 
  • Using sort_union, Using union, Using intersect
    쿼리가 Index_merge 접근 방식으로 실행되는 경우에는 2개 이상의 인덱스가 동시에 사용될 수 있다. 이때 실행계획의 Extra 컬럼에는 두 인덱스로부터 읽은 결과를 어떻게 병합했는지 조금 더 상세하게 설명하기 위해 다음 3개중에 하나의 메시지를 선택적으로 출력한다. 
      • Using intersect(...)
        각각의 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우 각 처리 결과에서 교집합을 추출해내는 작업을 수행했다는 의미이다.
      • Using union(...)
        각 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우 각 처리 결과에서 합집합을 추출해내는 작업을 수행했다는 의미이다. 
      • Using sort_union(...)
        Using union 과 같은 작업을 수행하지만 상대적으로 대량의 range 조건들이 OR로 연결되었을경우 Using sort_union을 사용한다. 레코드 건수와 관계없이 WHERE 조건에 사용된 비교 조건이 모두 동등조건이면 Using union이 사용되며 그렇지 않으면 Using sort_union이 사용된다. 
  • Using temporary
    쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블을 사용하면 이 키워드가 표시된다. 
  • Using where
    MySQL은 작업범위 제한 조건은 스토리지엔진에서 처리하고 체크조건은 MySQL 엔진레이어에서 처리된다. (https://idea-sketch.tistory.com/43) 그런데 스토리지 엔진에서 전달받은 데이터를 MySQL 엔진레이어에서 체크조건으로 필터링 할경우 이 키워드가 표시된다. Using where 키워드는 Extra 컬럼에 가장 흔하게 표시되는 키워드이다. 

오늘은 여기까지~

누군가에게 도움이 되었길 바라면서 오늘의 포스팅 끝~