MySQL EXPLAIN

참고

https://nomadlee.com/mysql-explain-sql/

https://cheese10yun.github.io/mysql-explian/

http://chongmoa.com/sql/8840

---

MYSQL Explain

사용해야 하는 이유

• 쿼리 실행문에 대한 정보를 알 수 있다. 예를 들면, select 쿼리를 실행했을 때 몇개의 row가 검색되는지, 어떤 테이블과 조인하는지에 대한 정보들

• 튜닝에서 가장 중요한 것은 쿼리와 스키마 최적화이다.
  • 스키마 : 데이터베이스의 구조와 제약조건에 관해 전반적인 명세를 기술한 것
    • 개체(Entity)의 속성(Attribute)의 관계(Relation)에 대한 정의와 이들을 유지해야 할 제약조건들을 기술한 것
• Slow Query를 없애는 것이 성능 향상에 도움이 된다.
  • 스키마는 한 번 정해지면 수정하기 쉽지 않으므로, 쿼리 최적화를 통해 성능을 향상시키는 것이 좋다.
• MySQL Explain
  • DB가 데이터를 찾아가는 일련의 과정을 사람이 알아보기 쉽게 DB 결과 셋으로 보여주는 것이다.
  • 기존의 쿼리 튜닝, 성능 분석,인덱스 전략 수립 등과 같이 성능 최적화에 대한 전반적인 업무를 처리할 수 있다.
  • 실행 계획이 무엇인지 알고 싶을 때 사용하는 기본적인 명령어
    • 5.6부터는 JSON 형식 출력 가능
  EXPLAIN [EXTENDED] SELECT ... FROM ... WHERE ...
• MySQL Explain 항목 별 의미

구분설명

id	select 아이디로 구분하는 번호
table	참조하는 테이블
select_type	select에 대한 타입
type	조인 혹은 조회 타입. 접근방식을 표시하는 필드 - 어떻게 행데이터를 가져올 것인가를 가리킨다.
possible_keys	데이터를 조회할 때 DB에서 사용할 수 있는 인덱스 리스트
key	실제로 사용할 인덱스
key_len	실제로 사용할 인덱스의 길이
ref	Key 안의 인덱스와 비교하는 컬럼(상수). 테이블에서 열을 선택하기 위해 key 컬럼 안에 명명되어 있는 인덱스를 어떤 컬럼 또는 Constant(상수)와 비교하는지 보여준다.
rows	쿼리 실행 시 조사하는 행 수립
extra	추가 정보. MySQL이 쿼리를 어떻게 해석하는지에 관한 추가적인 정보 제공

• select_type : select에 대한 타입
  • SIMPLE : 단순 SELECT
  • PRIMARY : Sub Query를 사용할 경우 Sub Query의 외부에 있는 쿼리(첫 번째 쿼리) UNION을 사용할 경우 UNION의 첫 번째 쿼리
  • UNION : UNION 쿼리에서 서 Primary를 제외한 나머지 SELECT
  • DEPENDENT_UNION : UNION과 동일하나, 외부쿼리에 의존적임 (값을 공급받음)
  • UNION_RESULT : UNION 쿼리의 결과물
  • SUBQUERY : Sub Query를 구성하는 여러 쿼리 중 첫 번째 SELECT문
  • DEPENDENT_SUBQUERY : Sub Query와 동일하지만, 외곽 쿼리에 의존적이디ㅏ.
  • DERIVED : SELECT로 추출된 테이블
  • UNCACHEABLE SUBQUERY
  • UNCACHEABLE UNION

• type : 조인 혹은 조회 타입. 접근방식을 표시하는 필드 - 어떻게 행데이터를 가져올 것인가를 가리킨다.
  • system : 테이블에 단 한개의 데이터만 있는 경우
  • const : SELECT에서 Primary Key를 실수로 조회하는 경우로, 많아야 한 건의 데이터만 있다.
  • eq_ref : 조인할 때 Primary Key
  • ref : 조인할 때 Primary Key 혹은 Unique Key가 아닌 Key로 매칭한 경우
  • ref_or_null : ref의 동작 + null이 추가되어 검색
  • index_merge : 두 개의 인덱스가 병합되어 검색이 이루어지는 경우
  • unique_subquery : 서브쿼리에서 Primary Key가 오는 경우 => SELECT * FROM table1 WHERE col1 IN (SELECT Primary Key FROM table1);
  • index_subquery : unique_subquery와 비슷하지만, Primary Key가 아닌 인덱스인 경우 => SELECT * FROM table1 WHERE col1 IN (SELECT key1 FROM table1);
  • range : 특정범위 내에서 인덱스를 사용하여 원하는 데이터를 추출하는 경우
  • index : 인덱스를 처음부터 끝까지 찾아서 검색하는 경우, 일반적으로 인덱스 풀 스캔이라고 한다.
  • all : 테이블을 처음부터 끝까지 검색하는 경우, 일반적으로 테이블 풀 스캔이라고 한다.
• Extra
  • distinect: 조건을 만족하는 레코드를 찾았을 때 같은 조건을 만족하는 또 다른 레코드가 있는지 검사하지 않음
  • not exist : left join조건을 만족하는 하나의 레코드를 찾았을 때 다른 레코드의 조합은 더 이상 검사하지 않는다.
  • range checked for each record : 최적의 인덱스가 없는 차선의 인덱스를 사용한다.
  • using filesort : mysql이 정렬을 빠르게 하기 위해 부가적인 일을 한다.
  • using index : select 할 때 인덱스 파일만 사용
  • using temporary : 임시 테이블을 사용한다. order by나 group by 할때 주로 사용
  • where used : 조건(where)을 사용한다.

---

실습

• feed와 community를 모두 작성한 user의 feed id 값을 가져오는 예제
• feed 테이블과 community 테이블에서 user_id가 같은 경우를 INNER JOIN (교집합)으로 가져온다.

• SQL 쿼리 실행 - community, feed 확인

-- 테이블 확인
SELECT * FROM db.community;
SELECT * FROM db.feed;

community table

feed table

• 위의 스크린샷을 보면, community와 feed를 모두 작성한 user는 5번 id를 가진 user 한 명이다.
• 따라서, inner join을 실행하면, 5번 user가 작성한 feed를 불러온다. (2개 => id는 8번과 25번)

SELECT f.id FROM db.community c INNER JOIN db.feed f ON c.user_id = f.user_id;

select

• EXPLAIN을 이용하여 정보를 출력한다.

EXPLAIN SELECT * FROM db.community c INNER JOIN db.feed f ON c.user_id = f.user_id;

explain select

• select_type : SIMPLE - 단순히 Select를 이용했기 때문에 SIMPLE이 출력된다.
• type : 조인 혹은 조회 타입. 접근방식을 표시하는 필드 - 어떻게 행데이터를 가져올 것인가를 가리킨다.
  • index : 인덱스를 처음부터 끝까지 찾아서 검색하는 경우, 일반적으로 인덱스 풀 스캔이라고 한다.
    • feed 테이블(f)에서 id 값을 가져와야 하므로 index를 이용한 풀 스캔이 사용된다.
  • ref : 조인할 때 Primary Key 혹은 Unique Key가 아닌 Key로 매칭한 경우
    • join을 Primary Key(commuinity_id) 혹은 Unique Key가 아닌 user_id를 key로 사용했다.
• ref : Key 안의 인덱스와 비교하는 컬럼(상수). 테이블에서 열을 선택하기 위해 key 컬럼 안에 명명되어 있는 인덱스를 어떤 컬럼 또는 Constant(상수)와 비교하는지 보여준다.
  • community 테이블(c)은 feed테이블의 user_id의 컬럼과 비교한다.

쿠팡으로 연결 클릭

제주삼다수 그린

파트너스 활동을 통해 일정액의 수수료를 제공받을 수 있음