Mysql explain

Explain

쿼리 앞에 explain을 추가하면 SQL문에 대해서 어떻게 데이터를 가져올 것인지에 대한 계획, 경로를 나타낸다.

explain의 결과는 MySQL에서 실행된 쿼리의 실행 계획을 보여준다.

결과에는 아래와 같은 컬럼이 포함되어 있다.

1. id

   • id는 쿼리의 실행 순서를 나타낸다. id 값이 낮을수록 먼저 실행되고, 값이 높을수록 나중에 실행된다.

2. select_type

   • 어떤 종류의 SELECT 문인지 나타낸다.

   1. SIMPLE : 서브쿼리, UNION이 포함되지 않은 단순한 SELECT 문이다.

   2. PRIMARY : 최상위 SELECT 문을 나타낸다. 주로 서브쿼리나 UNION이 포함된 쿼리에서 이들을 감싸는 외부 쿼리이며, 최상위 레벨의 쿼리를 나타낸다.

   3. SUBQUERY : 독립적으로 수행되는 서브쿼리를 나타낸다. 외부 SELECT 문의 컨텍스트 내에서 데이터를 필터링하거나 집계하는 데 사용될 수 있으며, 스칼라 서브쿼리와 WHERE 절의 중첩 서브쿼리일 경우 표시된다.

   4. DERIVED : MySQL에서 서브쿼리의 결과를 일시적인 파생 테이블로 만들어 사용하는 경우를 나타내는 타입이다. 파생 테이블은 서브쿼리 또는 UNION의 작업 결과로 생성된 임시 테이블을 나타낸다. 즉, FROM 절에 서브쿼리가 포함된 경우도 해당한다.

   5. UNION : 두 쿼리를 UNION 연산할 때, 두 번째 SELECT 문이 해당하게 된다.

   6. UNION RESULT : UNION 연산의 최종 결과를 나타내는 가상의 테이블을 의미한다. 즉, 두 SELECT 문을 조합한 결과임을 의미한다.

   7. DEPENDENT SUBQUERY : 상위(외부) 쿼리의 열에 의존하는 서브쿼리이다. 서브쿼리가 독립적으로 실행되지 못하고 where과 같은 외부 테이블로부터 영향을 받는다. → 성능적으로 불리한 SQL문이라고 한다.

   여기 작성된 것 말고도 더 많은 타입들이 존재한다.

3. table

   • 현재 실행이 참조하는 table의 이름이나 as를 나타낸다. 서브쿼리나 파생 테이블을 참조할 경우 <subquery#>, <derived#>을 사용한다.

   • EXPLAIN SELECT * FROM ( SELECT id, name FROM users ) AS derived_table; 쿼리를 실행 할 경우 다음과 같은 결과를 보여준다.

     
4. partitions

   • 쿼리에 접근하는 파티션을 나타낸다.

5. type

   • 테이블에 접근하는 방법을 나타낸다. 쿼리 성능을 분석하고 최적화 하는데 중요한 정보이며, system, const, eq_ref, ref, range, index ALL 순서로 성능이 좋다.

   접근 유형

   1. system : 단 하나의 행만 있는 테이블에 대한 접근을 나타낸다. 가장 빠른 접근 방법이며, 행이 하나뿐인 테이블에서 SELECT를 실행하는 경우에 나타난다.

   1. const : 상수처럼 취급되는 테이블에 대한 접근을 나타낸다. 일반적으로 PK나 유니크 인덱스에 정확한 값을 조회할 때 사용된다. 즉, 인덱스나 PK를 사용해서 1건의 데이터만 얻은 경우 표시된다. ex) select * from users where id = 1;

   1. eq_ref : Join에서 PK나 유니크 index를 사용하여 정확히 하나의 행을 조회할 때 나타낸다. 조인 시, 드라이빙 테이블이 드리븐 테이블에 접근해서 PK나 인덱스를 사용하여 1건의 데이털 얻는 경우 표시된다. ex) select * from orders join customers on orders.customer_id = customers.id; (여기서 customers.id가 PK일 때)

   1. ref : non-clusterd index 또는 외래 키를 사용하여 여러 행을 조회할 때 나타낸다. → 인덱스 혹은 외래 키가 설정된 컬럼을 where 조건절에 사용하여 여러 행을 조회할 때 표현 ex) select * from 음식 where type = ‘과일’ (여기서 type은 index)

   1. range : 인덱스를 사용해서 범위 검색을 수행할 때 사용된다. BETWEENT, >, <, ≥, ≤ 등의 조건을 사용할 떄 표현한다. (인덱스가 생성되지 않은 컬럼으로 조회할 때, range로 표현되지 않음)

   1. index : 인덱스 전체를 스캔할 때 표현한다. 인덱스 전체 스캔은 테이블 전체 스캔보다 빠르지만 여전히 많은 행을 읽어야 한다.

   1. ALL : 테이블에 대해서 전체 스캔 할 경우 표현한다. 데이터가 많을 때 일반적으로 성능이 가장 좋지 않은 방법이다.

6. possible_keys

   • 쿼리에서 사용할 수 있는 인덱스 목록을 나타낸다.

   • <auto_key0>는 MySQL이 내부적으로 자동 생성한 임시 인덱스를 타나낸다.

7. key

   • 실제로 사용된 인덱스를 나타낸다.

   • possible_key는 쿼리에서 사용할 수 있는 인덱스를 나타내고, key는 실제 사용된 인덱스를 나타내기 때문에 possible_key가 null 이더라도, key는 null이 아닐 수 있다.

8. key_len

   • 사용된 인덱스의 길이를 나타낸다. 인덱스의 크기는 byte 단위로 표현한다.

   • 인덱스를 생성한 컬럼의 스키마가 varchar(50)으로 정의 되었다면 50 * 4 + 오버헤드 = 202. 즉, key_len = 202 로 표현할 수 있다.

9. ref

   • 조인 조건에 사용된 컬럼을 나타낸다.

   • 직접적으로 조인하지 않더라도 서브 쿼리같은 경우 where 조건절에서 외부 테이블을 참조하고 있으면 ref에 표현될 수 있다. (서브 쿼리가 외부 쿼리를 참조하고 있다면 표현 가능)

10. rows

    • 해당 단계에서 MySQL이 처리할 수 있는 예상 row를 표현한다. (해당 쿼리가 접근하는 row)

11. filtered

    • 실행 될 SQL문이 스토리지 엔진 → MySQL 엔진을 거치면서 조건에 대해서 필터링 된 비율을 백분율로 나타낸다.

    • 만약 1,000개의 row 중 200개의 row가 조건을 충족한다고 예상되면 20.00으로 표현된다. 따라서 값이 높다면 조건을 만족하는 행이 많음을 의미한다.

    • 전체 스캔에서 이 값이 낮다면 where 절을 개선하여 더 많은 행을 필터링 할 수 있도록 성능 개선을 고려할 수 있다.

12. Extra

    • Extra는 쿼리 실행에 대해 추가 정보를 제공한다. 이 정보는 최적화 및 성능 개선에 중요한 역할을 할 수 있다.

    Extra 세부 사항 (Using ~)

    1. where : 쿼리에서 where 절이 사용되었음을 의미한다.

    1. index : 쿼리가 인덱스만을 사용하여 데이터를 조회하는 것을 의미한다. (커버링 인덱스 방식 이라고도 한다.)

    1. temporary : 쿼리를 처리하는 동안 임시 테이블이 사용됨을 의미한다. 임시 테이블은 쿼리 성능을 저하시키는 원인이 될 수 있다. distinct, group by, order by 구문이 포함된 경우 표시한다.

    1. filesort : 결과를 정렬하기 위해 정렬이 사용되었음을 의미한다. distinct, group by, order by, union 구문이 사용될 때 표시될 수 있다. order by를 사용하고 있다면, orber by 처리가 인덱스를 사용하지 못할 때 표시되고, 죄회된 레코드를 메모리 버퍼에 복사해 퀵 소트 또는 힙 소트 알고리즘을 이용해 정렬을 수행한다는 의미이다.

    1. distinct : 중복이 제거되어 유일한 값을 찾은 경우를 의미한다. distinct, union을 사용할 때 표시될 수 있다.

    1. index condition : 조건에 대해서 인덱스 레벨에서 먼저 평가하고, 해당 조건을 만족하는 데이터만 실제 테이블에서 읽어오고 있음을 의미한다. 이것은 MySQL이 효율적으로 인덱스를 사용하고 있음을 의미할 수 있고 별도의 성능 튜닝이 필요하지 않을 가능성이 높다.

    1. Backward index scan (Using X) : 이는 MySQL이 index를 역순으로 스캔하여 order by를 처리하고 있음을 나타내기 때문에 별도의 파일 정렬(filesort)가 필요하지 않다.