2022-07-07-explain

---

layout : post
title : "Mysql Explain"

category : MySQL

MySQL 옵티마이저

MySQL 옵티마이저는 비용에 대하여 어떠한 실행 계획을 사용하여 쿼리 실행 시 발생하는 비용을 예측하여 가장 적은 비용이 발생하는 쿼리를 실행 하도록 한다.
추정 값이기 때문에 실행 시에 다른 결과가 나올 수 있다.

EXPLAIN

EXPLAIN은 MySQL의 실행 계획에 대하여 설멍하는 명령어이다.
JSON 형식의 출력이 가능하며 JSON 형식을 사용하는 경우 더 자세한 내용을 볼 수 있다.

explain
select employer0_.id                     as id1_16_0_,
       employer0_.company_id             as company22_16_0_,
       employer0_.name                   as name14_16_0_,
       company1_.id                      as id1_14_1_,
       company1_.name                    as name17_14_1_,
from employer employer0_
         left outer join company company1_ on employer0_.company_id = company1_.id
where employer0_.id = 1202

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	row	filtered	Extra
1	SIMPLE	employer0_	null	const	PRIMARY	PRIMARY	8	const	1	100	null
1	SIMPLE	company1_	null	const	PRIMARY	PRIMARY	8	const	1	100	null

id

• id는 SELECT에 붙은 번호이다. MySQL은 조인을 하나의 단위로 실행하기 때문에 id는 그 쿼리에 실행 단위를 식별하는 것이다. 따라서 조인만 수행하는 쿼리에서는 id는 항상 1이 된다.

select_type

• select_type은 항상 SIMPLE 이된다. 복잡한 조인을 해도 SIMPLE이 된다. 서브쿼리나 UNION이 있으면 id와 select_type이 변한다.

table

• 어떤 테이블에 접근하는를 나타낸다.

partitions

• 파티셔닝이 되어 있는 경우에 사용된다.

type

• type은 접근 방식을 나타낸다. 방식은 테이블에서 어떻게 row 데이터를 가져 오는지에 대한 것인데 이는 성능을 판단하는데 있어 중요한 요소이다.
• system은 레코드가 1건만 존재하거나 1 건도 존재하지 않는 테이블을 참조하는 접근 방법이다. (InnoDB 테이블에서는 나타나지 않는다.)
• const는 테이블 레코드의 건수에 관계없이 쿼리가 프라이머리 키나 유니크 키 컬럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리방식이다.
• eq_ref는 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다. 조인에서 처음 읽은 테이블의 컬럼 값을, 그다음 읽어야 할 테이블의 PK 컬럼의 검색 조건에 사용할 때를 eq_ref 라고 한다. 즉,
  조인에서 두번째 이후에 읽은 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.
• ref는 인덱스 종류와 관계없이 동등 조건으로 검색할때는 ref 접근 방법이 사용된다. ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref 보다는 빠르지 않다.
• ALL, eq_ref는 조인시 기본 키나 고유키를 사용하여 하나의 값으로 엑세스 하는 것을 말한다. (최대 1행만을 정확하게 패치)
• ref는 여러 개의 행을 패치할 가능성이 있는 접근을 의미한다.
• ALL, index는 테이블 이나 특정 인덱스가 전체 row 에 접근하기 때문에 테이블 크기가 크면 효율이 떨어진다.
• ref_or_null는 NUL인 경우 인덱스의 맨 앞에 모아서 저장하지만 그 건수가 많으면 부담이 커진다.
• 만약 접근 방식이 ALL 이나 index 일때는 해당 쿼리로 사용할 수 있는 적절한 인덱스가 없다고 판단하였을 가능성이 크다. 이러한 경우 속도에 있어 영향을 미칠수 밖에 없다.
• index 인덱스 스캔, 테이블의 특정 인덱스의 전체 엔트리에 접근한다.
• range 인덱스 특정 범위의 행에 접근한다
• fulltext fulltext 인덱스를 사용한 검색이다.
• index_merge 여러 개인스턴스를 사용해 행을 가져오고 그 결과를 통합한다.
• unique_subquery IN 서브쿼리 접근에서 기본 키 또는 고유 키를 사용한다. 이 방식은 쓸데 없는 오버헤드를 줄여 상당히 빠르다.
• index_subquery unique_sunquery와 거의 비슷하지만 고유한 인덱스를 사용하지 않는 점이 다르다. 이 접근 방식도 상당히 빠르다
• possible_keys는 이용 가능성있는 인덱스의 목록이다.

key

possible_keys는 이용 가능성있는 인덱스의 목록 중에서 실제로 옵티마이저가 선택한 인덱스가 key가 된다.

key_len

key_len 필드는 선택된 인덱스의 길이를 의미한다.
인덱스가 너무 긴 것도 비효율적이게 된다.

rows

rows는 이 접근 방식을 사용해 몇 row를 가져왔는가를 표시한다.
최초에 접근하는 테이블에 대해서 쿼리 전체에 의해 접근하는 row 수, 그 이후에 테이블에 대해서는 1 row의 조인으로 평균 몇 행에 접근했는가를 표시한다.
통계를 바탕으로 하였기 때문에 반드시 일치 하지 않는다.

filtered

filtered는 행 데이터를 가져와 거이에서 WHERE 구의 검색 조건이 적용되면 몇행이 남는지를 표시한다.
통계를 바탕으로 하였기 때문에 반드시 일치 하지 않는다.

extra

Extra 필드는 옵티마이저가 동작하는데 대해서 우리에게 알려주는 힌트다. 이 필드는 EXPLAN을 사용해 옵티마이저의 행동을 파악할때 아주 중요하다.

• Using where 접근 방식을 설명한 것으로, 테이블에서 행을 가져온 후 추가적으로 검색조건을 적용해 행의 범위를 축소한 것을 표시한다.
• Using index 테이블에는 접근하지 않고 인덱스에서만 접근해서 쿼티를 해결하는 것을 의미한다. 커버링 인덱스로 처리됨 index only scan이라고도 부른다
• Using index for group-by Using index와 유사하지만 GROUP BY가 포함되어 있는 쿼리를 커버링 인덱스로 해결할 수 있음을 나타낸다
• Using filesort ORDER BY 인덱스로 해결하지 못하고, filesort(MySQL의 quick sort)로 행을 정렬한 것을 나타낸다.
• Using temporary 암묵적으로 임시 테이블이 생성된 것을 표시한다.
• Using where with pushed 엔진 컨디션 pushdown 최적화가 일어난 것을 표시한다. 현재는 NDB만 유효
• Using index condition 인덱스 컨디션 pushdown(ICP) 최적화가 일어났음을 표시한다. ICP는 멀티 칼럼 인덱스에서 왼쪽부터 순서대로 칼럼을 지정하지 않는 경우에도 인덱스를 이용하는 실행
  계획이다.
• Using MRR 멀티 레인지 리드(MRR) 최적화가 사용되었음을 표시한다.
• Using join buffer(Block Nested Loop) 조인에 적절한 인덱스가 없어 조인 버퍼를 이용했음을 표시한다.
• Using join buffer(Batched Key Access) Batched Key Access(BKAJ) 알고리즘을 위한 조인 버퍼를 사용했음을 표시한다.

JSON EXPALIN

explain format = json
select employer0_.id                     as id1_16_0_,
       employer0_.company_id             as company22_16_0_,
       employer0_.name                   as name14_16_0_,
       company1_.id                      as id1_14_1_,
       company1_.name                    as name17_14_1_,
from employer employer0_
         left outer join company company1_ on employer0_.company_id = company1_.id
where employer0_.id = 1202

와 같이 사용하면 된다.

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "95.70"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "employer0_",
          "access_type": "const",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "8",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.00",
            "eval_cost": "0.10",
            "prefix_cost": "0.00",
            "data_read_per_join": "4K"
          },
          "used_columns": [
            "id",
            "name",
            "company_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "company1_",
          "access_type": "const",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "8",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.00",
            "eval_cost": "0.10",
            "prefix_cost": "0.00",
            "data_read_per_join": "8K"
          },
          "used_columns": [
            "id",
            "name"
          ]
        }
      }
    ]
  }
}

Visual Explain

※ MySQL Workbench을 사용하면 Visual Explain를 사용 할 수 있다.