Mysql 성능 조회 - Explain 실행 계획으로 쿼리 성능 최적화하기

 

 

Mysql 에서 쿼리 성능을 조회하는 방법에대해서 기록합니다.

 

 

---

 

SQL 성능 조회 방법

MySql 에서는 쿼리 실행계획을 봄으로써, 쿼리의 성능을 예측할 수 있습니다.

쿼리 실행계획 조회 방법은 굉장히 간단합니다.

explain 쿼리문~~~

 

이렇게 간단하게 쿼리문 앞에 "EXPLAIN" 이라는 키워드만 붙혀준 후 실행시켜주면 됩니다.

sql explain

 

 

 

Query Plan

이런걸, 쿼리 플랜(Query Plan)이라고 하는데,

Query Plan이란, SQL 관계형 데이터베이스 관리 시스템의 데이터 접근에 사요외는 순서에 대한 순서 집합

💡 즉, 쿼리가 데이터에 접근하는 동작과정과 접근 계획이라고 볼 수 있을 것 같습니다.

 

쿼리 실행계획에는 다음과 같은 정보들을 보여주는데 각각의 실행계획이 의미하는 바는 다음과 같습니다.

항목	설명
id	쿼리 안에 있는 select 문에 대한 순차적인 식별자
select_type	select 문의 유형  ( SIMPLE, PRIMARY, SUBQUERY, UNION )
table	참조되는, 참조하고있는 table
type	어떤식으로 table 이 join 되는지 알려주게 된다. system: 1개 이하의 row 를 가진 table const: 테이블에 조건을 만족하는 row 가 1개일 때 eq_ref: primary key 나 unique key 로 검색하는 경우 ref: 인덱스로 지정된 컬럼끼리의 '=' '<=' 와 같은 조건문 검색일 때 unique_subquery: 오직 하나의 결과만 반환하는 'IN' 이 포함된 sub query의 경우 index_subquery: unique subquery 와 성격은 동일하지만 여러 개의 결과를 반환하는 경우 range: 특정한 범위의 키를 매칭할 때 사용하는 경우. BETWEEN IN '>=' ... all: 모든 row 를 스캔하는 경우
possible_keys	테이블에서 row 를 매핑시키기 위한 key 목록을 나열
key	실제적으로 쿼리 실행에 사용된 key 의 목록
ref	key column 에 지정된 인덱스와 비교되는 column 또는 constant
rows	결과 산출에 있어서 접근되는 record 의 수 (SQL문이 탐색하는 예상치이며 정확하지 않습니다. ex_offset)
extra	실행계획에 있어서 SQL이 해석되어지는 부가적인 정보

 

---

빨갛게 강조한 부분이, 쿼리를 최적화하는데 주요한게 보는 부분입니다.

 

select_type

1. SIMPLE: 단순 select ( union이나 서브쿼리를 사용하지 않음 )
2. PRIMARY: 가장 외곽에 있는 select문
3. UNION: union에서의 두번째 혹은 나중에 따라오는 select문
4. DEPENDENT UNION: union에서의 두번째 혹은 나중에 따라오는 select문, 외곽 쿼리에 의존적이다.
5. UNION RESULT: union의 결과물
6. SUBQUERY: 서브쿼리의 첫번째 select
7. DEPENDENT SUBQUERY: 서브쿼리의 첫번째 select, 바깥 쪽 쿼리에 의존적이다.
8. DERIVED: from절의 서브쿼리

 

type

조인타입이며 속도와 아주 밀접한 항목이다. 좋은 순서대로 아래로 갈수록 안좋은 형태입니다.

1. system : 테이블에 단 하나의 행만 존재(=시스템 테이블). const 조인의 특별한 형태이다.
2. const : 하나의 매치되는 행만 존재하는 경우. 하나의 행이기 때문에 상수로 간주되며, 한번만 읽어들이기 때문에 무척 빠르다.
3. eq_ref : 조인수행을 위해 각 테이블에서 하나의 행만이 읽혀지는 형태. const 타입 외에 가장 훌륭한 조인타입이다.
4. ref : ref조인에서 키의 가장 왼쪽 접두사 만 사용하거나 키가 a PRIMARY KEY또는 UNIQUE인덱스 가 아닌 경우 (즉, 조인이 키 값을 기반으로 단일 행을 선택할 수없는 경우) 사용된다. 사용되는 키가 몇 개의 행과 만 일치하는 경우 이는 좋은 조인 유형이다.
5. fulltext : fulltext 색인을 사용하여 수행된다.
6. ref_or_null : 이 조인 유형은 비슷 ref하지만 MySQL이 NULL값 을 포함하는 행을 추가로 검색한다는 점이 다르다. 이 조인 유형 최적화는 하위 쿼리를 해결하는 데 가장 자주 사용된다.
7. index_merge : 인덱스 병합 최적화가 적용되는 조인타입. 이 경우, key컬럼은 사용된 인덱스의 리스트를 나타내며 key_len 컬럼은 사용된 인덱스중 가장 긴 key명을 나타낸다.
8. range : 인덱스를 사용하여 주어진 범위 내의 행들만 추출된다. key 컬럼은 사용된 인덱스를 나타내고 key_len은 사용된 가장 긴 key부분을 나타낸다. ref 컬럼은 이 타입의 조인에서 NULL 이다. range 타입은 키 컬럼이 상수와 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN 또는 IN 연산에 사용될때 적용된다.
9. index : 이 타입은 인덱스가 스캔되는걸 제외하면 ALL과 같다. 보통 인덱스 파일이 데이터 파일보다 작기 때문에 ALL보다 빠르다.
10. ALL : 이전 테이블과의 조인을 위해 풀스캔이 된다. 만약 조인에 쓰인 첫 테이블이 고정이 아니라면 비효율적이다. 그리고 대부분의 경우 아주 느리며, 보통 상수값이나 상수인 컬럼값으로 row를 추출하도록 인덱스를 추가하여 ALL 타입을 피할 수 있다.

 

 

---

 

Data Grip - Actual Rows

젯브레인의 Data Grip을 사용하면, 쿼리 실행 계획의 조금 더 자세한 분석이 가능합니다.

 

 

쿼리문을 선택한 후, Explain Analyse 를 실행하면, 실제 조회되는 row (Actual row) 와, 그 과정에서의 실제 탐색속도(Actual Total Time) 등을 파악할 수 있습니다.

 

 

 

 

---

 

 

*참고

https://huisam.tistory.com/entry/mysql-plan-query

https://gradle.tistory.com/4