[MySQL] 실행계획

옵티마이저

쿼리를 최적으로 실행하기 위해 테이블의 데이터가 어떤 형태로 분포되어 저장되어 있는지 통계 정보를 참조하고 실행계획을 만들어 낸다.

대부분의 DBMS는 비용기반 최적화(Coast-based optimizer, CBO)방법을 이용한다.

아주 오래전 예전 오라클에서는 규칙기반 최적화(Rule-based optimizer, RBO)을 이용하기도 했다. (현재는 거의 사용하지 않는 방법)

​

통계정보

CBO에서 가장 중요한 것은 통계정보다.

오라클의 경우 통계정보가 상당히 정적이고 수집에 많은 시간이 소요되기 때문에 통계 정보만 따로 백업하기도 한다.

MySQL에서 관리되는 통계정보는 대략의 레코드 건수와 인덱스의 유니크한 값의 개수 정도이다.

Memory 테이블은 별도 통계 정보가 없으며, MyISAM과 InnoDB의 테이블과 인덱스 통계 정보는 아래와 같이 확인 할 수 있다.

SHOW TABLE STATUS LIKE 'user_ldap_list'; 
SHOW INDEX FROM user_ldap_list;

ANALYZE 명령어를 통해 통계 정보를 갱신할 수 있다.

ANALYZE TABLE user_ldap_list;  
ANALYZE TABLE user_ldap_list ANALYZE PARTITION p3; -- 파티션을 사용하는 경우

 

쿼리 실행 절차
1. SQL 파싱  
  SQL 을 MySQL 서버가 이해할 수 있는 수준으로 분리한다.

2. 최적화 및 실행 계획 수립  
  불필요한 조건 제거
  복잡한 연산의 단순화  
  어떤 순서로 테이블을 읽을지 결정
  인덱스 결정  
  가져온 데이터를 임시 테이블에 넣고 다시 한번 가공해야 하는지 여부 결정
  기타 등등의 작업

3. 레코드를 읽고 조인하거나 정렬  
  2번에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 엔진으로부터 데이터를 읽는다.

​실행 계획 분석
EXPLAIN명령어를 통해 실행계획을 확인할 수 있다.
보통 쿼리툴에서는 실행계획을 볼 수 있는 기능이 있으니 해당 기능을 이용해도 된다. (아래 그림은 QueryBox로 실행계획 보기)

위 실행계획을 보고 각 컬럼의 값들이 무엇을 의미하는지, 실행계획을 보고 어떻게 SQL을 튜닝하는지 살펴보자.

id 컬럼

하나의 SELECT 문장은 다시 1개 이상의 하위 SELECT 문장을 포함할 수 있다. 이렇게 SELECT 키워드 단위로 구분한 것을 단위쿼리라고 하며 하나의 SELECT 안에서 여러 테이블의 조인이 있을 경우 조인되는 테이블 수 만큼 레코드가 출력되지만 모두 같은 id가 부여된다.

id 값이 작을수록 쿼리의 Outer 부분이거나 먼저 접근한 테이블이며 id 값이 클수록 쿼리의 Inner 부분 또는 나중에 접근한 테이블이라고 보면 된다.

select_type

각 단위쿼리가 어떤 타입의 쿼리인지 표시된다.

- SIMPLE
UNION 이나 서브쿼리를 사용하지 않는 단순한 SELECT의 경우이며 일반적으로 제일 바깥 SELECT 쿼리의 select_type이 SIMPLE인 경우가 많다.

- PRIMARY
UNION이나 서브쿼리가 포함된 SELECT 쿼리의 실행계획에서 가장 바깥쪽에 있는 단위쿼리

- UNION
UNION으로 결합하는 단위쿼리 가운데 첫번째를 제외한 두번째 이후 단위쿼리의 경우

​- DEPENDENT UNION
UINON이나 UNION ALL로 집합을 결합하는 쿼리에서 표시되며 서브쿼리에서 외부에서 정의된 컬럼을 사용하는 경우 DEPENDENT 키워드가 표시된다.
select_type에 <span style="color:#e11d21">DEPENDENT</span> 키워드가 포함된 서브 쿼리의 경우 비효율적인 경우가 많다.​

- UNION RESULT
UNION 결과를 담아두는 테이블을 의미한다. UNION RESULT는 실제 쿼리에서 단위쿼리 가 아니기 때문에 id 값은 부여되지 않는다.

​- SUBQUERY
FROM절 이외에서 사용되는 서브쿼리를 의미한다.
MySQL의 실행계획에서 FROM절에 사용되는 서브쿼리는 DERVIED라고 표시되고, 그 밖의 위치에서 사용된 서브 쿼리는 전부 SUBQUERY로 표시된다.
파생테이블 이라는 표현은 DERVIED를 말한다.

​- DEPENDENT SUBQUERY
서브쿼리가 바깥쪽 SELECT 에서 정의된 컬럼을 사용하는 경우
바깥쪽 SELECT 쿼리의 컬럼에 의존적이기 때문에 DEPENDENT 키워드가 붙는다.
DEPENDENT UNION와 같이 DEPENDENT SUBQUERY 는 외부 쿼리가 먼저 수행된 후 서브쿼리가 실행되어야 하기 때문에 DEPENDENT가 없는 서브쿼리보다는 처리 속도가 느릴때가 많다.

​- DERIVED
서브쿼리가 FROM절에 사용된 경우 MySQL은 항상 DERIVED 실행계획을 만든다.
DERIVED는 단위쿼리의 실행결과를 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다.
MySQL은 FROM절에 사용된 서브쿼리를 제대로 최적화하지 못할때가 많으며 (MySQL 6.0 이상부터는 많이 개선됨) 이러한 파상태에블에는 인덱스가 없기 때문에 다른 테이블과 조인할 때 성능상 불리할 때가 많다.
가능하다면 DERIVED 를 조인으로 해결할 수 있게 바꿔주는 것이 좋다.
쿼리를 튜닝하기 위해 실행 계획을 확인할 때 가장 먼저 select_type 이 DERIVED 인 것이 있는지 확인해야 한다. JOIN으로 해결할 수 있다면 SUBQUERY 보다는 JOIN을 사용하자.

- UNCACHEABLE SUBQUERY
보통 서브쿼리가 하나만 있더라도 실제 그 서브쿼리는 한번만 실행되는 것이 아니기 때문에 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 캐시를 이용한다.
SUBQUERY는 처음 한번만 실행해서 그 결과를 캐시하고 DEPENDENT SUBQUERY는 바깥쪽 쿼리의 컬럼 값 단위로 캐시한다.
UNCACHEABLE SUBQUERY는 캐시 자체가 불가능한 경우이며 아래와 같은 상황에서 발생한다.
사용자 변수가 서브쿼리에 사용된 경우
NOT-DETERMINISTIC 속성의 Stored 루틴이 서브쿼리 내에 사용된 경우
UUID()나 RAND()와 같이 결과값이 호출할 때마다 달라지는 함수가 서브쿼리에 사용된 경우

​- UNCACHEABLE UNION
위에서 설명한 UNION 과 UNCACHEABLE 내용 참고

 

table
MySQL 실행계획은 단위쿼리 기준이 아니라 테이블 기준으로 표시 된다. 만약 테이블 이름에 Alias 가 부여된 경우는 해당 별칭이 표시 된다.
table 에 <derived> <union> 과 같이 `<>` 로 둘러싸인 경우는 임시 테이블을 의미한다. `<> 안에 표시되는 숫자는 단위쿼리의 id를 지칭한다.
즉 <derived2> 는 2번 단위쿼리로 인해 만들어진 파생테이블이라고 해석하면 된다.

​

type
인덱스를 사용해 레코드를 읽었는지 아니면 테이블을 처음부터 끝까지 풀스캔했는지 등을 알 수 있다.
아래 type 은 성능이 빠른 순서대로 나열된 것이다. (실제로는 데이터의 분포나 레코드의 건수에 따라 빠른 순서는 조금씩 달라질 수 있다.)​

- system
레코드가 1건만 존재하는 테이블 또는 한건도 존재하지 않는 테이블을 참조하는 경우
InnoDB에는 없고 MyISAM 이나 MEMORY 테이블에서만 사용되는 접근 방법 (ex. SELECT * FROM dual;)

​- const
테이블의 레코드 건수에 관계없이 Primary Key 또는 Unique key 컬럼을 이용하는 Where 절을 가지고 있으며 반드시 1건을 반환하는 경우
다른 DBMS 에서는 UNIQUE INDEX SCAN 이라고 표시하기도 한다.

​- eq_ref
조인에서 처음 읽는 컬럼값을 그 다음에 읽어야 할 테이블의 P.K 나 U.K 컬럼의 검색 조건에 사용하는 경우
조인에서 두번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.

​- ref
인덱스의 종류와 관계없이 동등 조건으로 검색하는 경우
const 나 eq_ref 보다 빠르지는 않지만 동등 조건으로만 비교되기 때문에 매우 빠른 조회 방법 중 하나이다.

​- fulltext
전문 검색 인덱스를 사용해 접근하는 경우

​- ref_or_null
ref 비교와 같으나 NULL 비교가 추가된 형태

​- unique_subquery
이름 그대로 서브쿼리에서 중복되지 않는 유니크한 값만 반환하는 경우

​- index_subquery
IN 연산자 특성상 IN (subquery) 또는 IN (상수 나열) 형태의 조건은 괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거되어야 한다.
위에서 살펴본 unique_subquery 는 중복이 없다는 보장이 있을경우 사용되며 중복이 발생할 수 있을 경우 index_subquery 접근 방법이 사용된다.

​- range
index range scan 형태의 접근 방법이다. 주로 '<', '>', IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용할 때 사용 된다.

​- index_merge
2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 그 결과를 병합하는 처리 방식
여러 인덱스를 읽어야 하기 때문에 일반적으로 range 보다 효율성이 떨어진다.
AND, OR 연산이 복잡하게 연결된 쿼리에서는 제대로 최적화되지 못할 때가 많다.

​- index
index 접근 방식은 index를 처음부터 끝까지 읽는 full scan을 의미한다. (range 는 인덱스의 필요한 부분만 효율적으로 읽는다.)
보통 인덱스는 데이터 크기보다 작아서 full table scan 보다는 효율적이고 빠르다. 또한 쿼리 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있다.

​- all
흔히 알고 있는 full table scan을 의미한다. 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거하고 반환하는 방식이다.

​

possible_keys
옵티마이저는 여러 처리 방법 중 가장 비용이 낮을 것으로 예상되는 실행 계획을 선택해서 쿼리를 실행한다.
possible_key 에 있는 내용은 옵티마이저가 최적의 실행계획을 만들기 위해 후보로 선정했던 접근 방식에서 사용되는 인덱스의 목록을 보여주는 것이다.
쿼리를 튜닝하는 데 아무런 도움이 되지 않는 내용이다. 실행 계획을 확인할 때 무시하면 된다.

 

key
최종 선택된 실행계획에서 사용하는 인덱스를 의미한다.
쿼리 튜닝 시 sql 작성자가 의도했던 대로 인덱스가 표시되는지 확인하는 것이 중요하다.
PRIMARY가 표시되는 경우 P.K를 사용한다는 것이고 그 이외의 값은 모두 테이블이나 인덱스를 생성할 때 부여했던 고유 이름이 표시된다.
인덱스를 전혀 사용하지 못하면 key 컬럼의 내용은 NULL로 표시된다.​

 

key_len
보통 테이블의 인덱스는 단일 컬럼보다는 다중 컬럼으로 만들어지는 경우가 많다.
쿼리를 처리하기 위해 다중 컬럼으로 구성된 인덱스에서 몇개의 컬럼까지 사용했는지를 표시하는 것이 key_len 이다.
더 정확하게는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려준다.
ex. char(4) 이고 utf8 인 경우 utf8은 1~3바이트로 가변적이지만 MySQL 은 3byte 로 계산해서 key_len 은 12가 표시되는 것, integer는 4 byte​

​

ref
참조조건으로 어떤 값이 제공되었는지 보여주는데 크게 신경쓰지 않아도 된다.
문자집합이 일치하지 않는 두 문자열 컬럼을 조인하거나 숫자 타입과 문자 타입의 컬럼을 조인할 때도 ref 로 표현될 때가 있는데 이런 경우에 대해서만 확인하고 조인 시 컬럼 타입을 일치시키는 것이 좋다.

​​

rows
쿼리를 처리하기 위해 얼마나 많은 레코드를 디스크로부터 읽고 체크해야 하는지 나타내는 값이다.
옵티마이저가 비용산정할 때 각 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지 예측(대상 테이블에 얼마나 많은 레코드가 포함되어 있는지, 각 인덱스 값이 분포도가 어떤지)해 보는 것이다.

​

​extra
실행계획에서 성능에 관련된 중요한 내용이 이곳에 자주 표시된다.

- const row not found
const 접근 방식으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않을 경우

​- Distinct
조인하지 않아도 되는 항목은 무시하고 필요한 것만 조인하고 필요한 레코드만 읽었다는 표시

​- Impossible HAVING
이 메시지가 나온다면 쿼리가 제대로 작성되지 못한 경우가 대부분이므로 쿼리의 내용을 다시 점검하는 것이 좋다.

​- Using filesort
ORDER BY 처리가 인덱스를 사용하지 못할 경우에 표시된다.
조회된 레코드를 정렬용 메모리 버퍼에 복사해 Quick Sort 알고리즘을 수행하게 된다.

​- Using index
데이터 파일을 읽지 않고 인덱스만 읽어서 쿼리를 처리할 수 있는 경우
인덱스 레인지 스캔(eq_ref, ref, range, index_merge..)을 사용하지만 쿼리의 성능이 만족스럽지 못한 경우라면 인덱스에 있는 컬럼만 사용하도록 쿼리를 변경하면 큰 성능 향상을 볼 수 있다.
하지만 무조건 위와 같이 처리하기 위해 인덱스에 많은 컬럼을 추가하면 더 위험한 상황이 초래될 수 있으니 무조건 적인 인덱스 추가는 삼가해야 한다.

위 내용 외에도 Using where, Using temporary, Using union, Using sort_union, 등등이 많은데 ... 이런건 필요할 때 검색해서 보는걸로... ^^