MySQL 튜닝에서 가장 중요한 것은 쿼리와 스키마 최적화이다. 스키마 설계는 한번 진행되면 그 테이블을 사용하는 모든 쿼리에 영향을 주기 때문에 좀처럼 변경하기 힘들지만 쿼리는 해당 쿼리만 수정하면 되므로 변경하기 쉽다.
Slow Query를 줄이는 것은 성능 향상을 위한 중요한 과정이므로 애플리케이션의 성능을 향상 시키고 싶다면 우선적으로 쿼리 튜닝을 검토해 봐야 하는데 MySQL Explain 실행 계획을 통해서 진단해 볼 수 있다.
성능 진단의 가장 첫걸음은 실행한 SQL이 데이터베이스에서 어떻게 처리되는 지를 파악하는 것이다. 사람이 이해할 수 있는 언어로 구성된 SQL을 실행하도록 데이터베이스에 명령을 내리면 DB는 내부적으로 SQL 파싱 (문법 체크 및 DB에서 실행하기 위한 형태로 변환하는 단계)을 하고 옵티마이징 (데이터를 찾는 가장 빠른 방법을 찾아내는 단계)을 거친 후 실제로 데이터를 찾는다.
사용자가 느끼기에는 SQL 만으로 모든 조작이 이루어지는 것처럼 보이나 실제로는 조금이라도 빠르게 데이터를 찾아내기 위해 DB 내부적으로는 의외로 많은 단계를 거치기 때문에 MySQL Explain 실행계획을 통해서 파악을 할 수가 있다.
MySQL Explain 이란
MySQL Explain 이란 데이터베이스가 데이터를 찾아가는 일련의 과정을 사람이 알아보기 쉽게 DB 결과 셋으로 보여주는 것이다. MySQL Explain 실행계획 활용하여 기존의 쿼리 튜닝 할 수 있을 뿐만 아니라 성능 분석, 인덱스 전략 수립 등과 같이 성능 최적화에 대한 전반적인 업무를 처리할 수 있다.
MySQL Explain 사용방법
EXPLAIN SELECT ... FROM ... WHERE ...MySQL Explain 보는법 (항목별 의미)
| 구분 | 설명 |
| id | id는 SELECT에 붙은 번호를 말한다. MySQL은 조인을 하나의 단위로 실행하기 때문에 id는 그 쿼리에 실행 단위를 식별하는 것이다. 따라서 조인만 수행하는 쿼리에서는 id는 항상 1이 된다. |
| table | 어떤 테이블에 대한 접근을 표시하고 있는지는 table 필드에 표시되어 있다. |
| select_type | select_type은 항상 SIMPLE 이 되며, 복잡한 조인을 해도 SIMPLE이 된다. 서브 쿼리나 UNION이 있으면 id와 select_type이 변한다. |
| type | type은 접근 방식을 표시하는 필드다. 접근 방식은 테이블에서 어떻게 행이 데이터를 가져올 것인가를 가리킨다. 위 EXPLAIN에서는 ALL, eq_ref, ref가 있는데 ALL, eq_ref는 조인 시 기본 키나 고유키를 사용하여 하나의 값으로 접근(최대 1행만을 정확하게 패치), ref는 여러 개의 행을 패치할 가능성이 있는 접근을 의미한다. 접근 방식은 대상 테이블로의 접근이 효율적일지 여부를 판단하는 데 아주 중요한 항목이다. 이들 접근 방식 가운데도 주의가 필요한 것은 ALL, index, ref_or_null이다. ALL, index 두 가지는 테이블 또는 특정 인덱스가 전체 행에 접근하기 때문에 테이블 크기가 크면 효율이 떨어진다. ref_or_null의 경우 NUL이 들어있는 행은 인덱스의 맨 앞에 모아서 저장하지만 그 건수가 많으면 MySQL 서버의 작업량이 방대해진다. 다시 말해서 ALL 이외의 접근 방식은 모두 인덱스를 사용한다.접근 방식이 ALL 또는 index인 경우는 그 쿼리로 사용할 수 있는 적절한 인덱스가 없다는 의미일 수도 있다. |
| possible_keys | possible_keys 필드는 이용 가능성 있는 인덱스의 목록이다. |
| key | possible_keys 필드는 이용 가능성 있는 인덱스의 목록 중에서 실제로 옵티마이저가 선택한 인덱스가 key가 된다. 위 EXPLAN에서는 County 테이블(첫 번째 행)의 Key는 NULL인데 이는 행 데이터를 가져오기 위해 인덱스를 사용할 수 없다는 의미이다. |
| key_len | key_len 필드는 선택된 인덱스의 길이를 의미한다. 중요한 필드는 아니지만 인덱스가 너무 긴 것도 비효율적이므로 기억해두자. |
| ref | 키 칼럼에 나와 있는 인덱스에서 값을 찾기 위해 선행 테이블의 어떤 칼럼이 사용 되었는지를 나타낸다. |
| rows | rows는 이 접근 방식을 사용해 몇 행을 가져왔는가를 표시하며, 최초에 접근하는 테이블에 대해서 쿼리 전체에 의해 접근하는 행 수, 그 이후에 테이블에 대해서는 1행의 조인으로 평균 몇 행에 접근했는가를 표시한다. 단, 어디까지나 통계 값으로 계산한 값이므로 실제 행 수와 반드시 일치하지 않는다. |
| filtered | filtered는 행 데이터를 가져와 WHERE 구의 검색 조건이 적용되면 몇 행이 남는 지를 표시한다. 이 값도 통계 값 바탕으로 계산한 값이므로 현실의 값과 반드시 일치하지 않는다. |
| extra | Extra 필드는 옵티마이저가 동작하는데 대해서 우리에게 알려주는 힌트다. 이 필드는 EXPLAN을 사용해 옵티마이저의 행동을 파악할 때 아주 중요하다. |
id 칼럼
행이 어떤 SELECT 구문을 나타내는 지를 알려주는 것으로 구문에 서브 쿼리나 UNION이 없다면 SELECT는 하나밖에 없기 때문에 모든 행에 대해 1이란 값이 부여되지만 이외의 경우에는 원 구문에서 순서에 따라 각 SELECT 구문들에 순차적으로 번호가 부여된다.
table 칼럼
행이 어떤 테이블에 접근하는 지를 보여주는 것으로 대부분의 경우 테이블 이름이나 SQL에서 지정된 별명 같은 값을 나타낸다.
select_type 칼럼
다음 표에 표시된 유형 중 하나일 수 있는 SELECT 유형입니다. JSON 형식의 sql explain은 SIMPLE 또는 PRIMARY가 아닌 경우 SELECT 유형을 query_block의 속성으로 노출합니다. JSON 이름(해당되는 경우)도 표에 표시됩니다.
| 구분 | 설명 |
| SIMPLE | 단순 SELECT (Union 이나 Sub Query 가 없는 SELECT 문) |
| PRIMARY | Sub Query를 사용할 경우 Sub Query의 외부에 있는 쿼리(첫번째 쿼리) UNION 을 사용할 경우 UNION의 첫 번째 SELECT 쿼리.복수행의 mysql explain 실행계획에서 1행만 존재한다. |
| UNION | UNION 쿼리에서 Primary를 제외한 나머지 SELECT, UNION과 UNION ALL 절로 생성된 임시 테이블을 의미한다. |
| DEPENDENT_UNION | UNION 과 동일하나, 외부 결과에 의존할 때 표현된다. UNION 쿼리가 내부에서 사용되었을때 표현된다. |
| UNION_RESULT | UNION 쿼리의 결과물 |
| SUBQUERY | Sub Query 또는 Sub Query를 구성하는 여러 쿼리 중 첫 번째 SELECT문(FROM절 외에서 사용되는 서브 쿼리를 의미한다.) |
| DEPENDENT_SUBQUERY | Sub Query 와 동일하나, 외곽쿼리에 의존적임 (값을 공급 받음) |
| DERIVED | SELECT로 추출된 테이블 (FROM 절 에서의 서브쿼리 또는 Inline View) |
| MATERIALIZED | MySQL 5.6 버전에 추가된 셀렉트 타입이다. 그 이전의 버전에서는 IN 절 내에 서브쿼리가 존재할 경우 매 레코드마다 서브쿼리를 실행시키는 형태로 수행되었다. 생각만해봐도 비효율적임을 알 수 있다. 5.6 에서부터 추가된 MATERIALIZED는 IN 절 내의 서브쿼리를 임시테이블로 만들어 조인을 하는 형태로 최적화를 해준다. DERIVED와 비슷하다고 생각하면 될 것 같다. |
| UNCACHEABLE SUBQUERY | Sub Query와 동일하지만 공급되는 모든 값에 대해 Sub Query를 재처리. 외부쿼리에서 공급되는 값이 동이라더라도 Cache된 결과를 사용할 수 없음 서브쿼리는 종류에 따라 바깥쿼리 행만큼 수행되어야 하는 경우도있다. 실제로 그렇게 작동한다면 성능에 큰 영향을 끼치게되므로 경우에 따라 쿼리를 캐싱해놓고 캐싱된 데이터를 갖다쓰게끔 최적화가 되어있는데 그런 캐싱이 작동할 수 없는 경우에 표현된다. 즉 캐싱되지못하는 이유가 수정 가능하다면 캐싱되게끔 하는것이 성능에 좋다. |
| UNCACHEABLE UNION | UNION 과 동일하지만 공급되는 모든 값에 대하여 UNION 쿼리를 재처리 |
type 칼럼
| 구분 | 설명 |
| system | 레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태 |
| const | 테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건 절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식 |
| eq_ref | JOIN을 할 때 Primary Key |
| ref | JOIN을 할 때 Primary Key 혹은 Unique Key가 아닌 Key로 매칭 하는 경우 |
| ref_or_null | ref 와 같지만 null 이 추가되어 검색되는 경우 |
| index_merge | 두 개의 인덱스가 병합되어 검색이 이루어지는 경우 |
| unique_subquery | 다음과 같이 IN 절 안의 서브쿼리에서 Primary Key가 오는 특수한 경우 SELECT * FROM tab01 WHERE col01 IN (SELECT Primary Key FROM tab01); |
| index_subquery | unique_subquery와 비슷하나 Primary Key가 아닌 인덱스인 경우 SELECT * FROM tab01 WHERE col01 IN (SELECT key01 FROM tab02); |
| range | 특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출하는 경우로, 데이터가 방대하지 않다면 단순 SELECT 에서는 나쁘지 않음 |
| index | 인덱스를 처음부터 끝까지 찾아서 검색하는 경우로, 일반적으로 인덱스 풀스캔이라고 함 |
| all | 테이블을 처음부터 끝까지 검색하는 경우로, 일반적으로 테이블 풀스캔이라고 함 |
possible_keys 칼럼
실행계획에 있는 이 칼럼 또한 사용자의 오해를 자주 불러일으키는데 MySQL 옵티마이저는 쿼리를 처리하기 위해 여러 가지 처리 방법을 고려하고 그중에서 비용이 가장 낮을 것으로 예상하는 MySQL Explain 실행계획을 선택해 쿼리를 실행한다.
그런데 possible_keys 칼럼에 있는 내용은 옵티마이저가 최적의 MySQL Explain 실행계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록일 뿐이다. 즉, 말 그대로 “사용될법했던 인덱스 목록“인 것이다.
실제로 실행계획을 보면 그 테이블의 모든 인덱스가 목록에 포함되어 나오는 경우이고 있으며 쿼리 튜닝 하는 데 크게 도움이 되지 않는다. 그래서 MySQL Explain 실행계획을 확인할 때는 possible_keys 칼럼은 특별한 경우는 제외하고는 그냥 무시해도 된다.
key 칼럼
possible_keys 칼럼의 인덱스가 사용 후보였던 반면, key 칼럼에 표시되는 인덱스는 최종 선택된 MySQL Explain 실행계획 에서 사용하는 인덱스를 의미한다. 그래서 MySQL 쿼리 튜닝 할 때는 key 칼럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다. key 칼럼에 표시되는 값이 PRIMARY인 경우에는 프라이머리 키를 사용한다는 의미이며, 그 이외의 값은 모두 테이블이나 인덱스를 생성할 때 부여했던 고유 이름이다.
MySQL Explain 실행계획의 type 칼럼이 index_merge가 아닌 경우에는 반드시 테이블 하나 당 하나의 인덱스만 이용할 수 있다. 하지만 index_merge 실행계획이 사용될 때는 2개 이상의 인덱스가 사용되는데, 이 때는 key 칼럼에 여러 개의 인덱스가 “,”로 구분되어 표시된다.
key_len 칼럼
key_len 칼럼은 쉽게 지나칠 수 있지만 사실은 매우 중요한 정보 중 하나이며 실제 업무에서 사용하는 테이블은 단일 칼럼으로만 만들어진 인덱스보다 다중 칼럼으로 만들어진 인덱스가 더 많다.
MySQL Explain 실행 계획의 key_len 칼럼의 값은 쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼만 사용했는지 우리에게 알려주며 더 정확히는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값이다. 그래서 다중 칼럼 인덱스 뿐 아니라 단일 칼럼으로 만들어진 인덱스에서도 같은 지표를 제공해 준다.
ref 칼럼
접근 방법이 ref 이면 참조 조건(Equal 비교 조건)으로 어떤 값이 제공됐는지 보여준다. 상숫값을 지정했다면 ref 칼럼의 값은 const로 표시되고, 다른 테이블의 칼럼 값이면 그 테이블명과 칼럼명이 표시된다.
이 칼럼에 출력되는 내용은 크게 신경 쓰지 않아도 무방하지만 가끔 쿼리의 실행계획에서 ref 칼럼의 값이 func라고 표시될 때가 있는데 이는 “Function”의 줄임말로 참조용으로 사용되는 값을 그대로 사용한 것이 아니라 콜레이션 변환이나 값 자체의 연산을 거쳐서 참조되었다는 것을 의미한다.
rows 칼럼
MySQL 옵티마이저는 각 조건에 대해 가능한 처리 방식을 나열하고, 각 처리 방식의 비용을 비교해 최종적으로 하나의 MySQL Explain 실행계획을 수립한다. 이때 각 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지 예측해서 비용을 산정한다.
대상 테이블에 얼마나 많은 레코드가 포함돼 있는지 또는 각 인덱스 값의 분포도가 어떤지를 통계 정보를 기준으로 조사한 예측값을 의미한다.
filtered 칼럼
Optimizer는 각 테이블에서 일치하는 레코드 개수를 가능하면 정확히 파악해야 좋 더 효율적인 MySQL Explain 실행계획을 수립할 수 있다. mysql explain filtered 에서 rows 칼럼의 값은 인덱스를 사용하는 조건에만 일치하는 레코드 건수를 예측한 것이다.
하지만 대부분 쿼리에서 WHERE 절에 사용되는 조건이 모두 인덱스를 사용할 수 있는 것은 아니다. 특히 JOIN이 사용되는 경우에는 WHERE 절에서 인덱스를 사용할 수 있는 조건도 중요하지만 인덱스를 사용하지 못하는 조건에 일치하는 레코드 건수를 파악하는 것도 매우 중요하다.
extra 칼럼
| 구분 | 설명 |
| using index | 커버링 인덱스라고 하며 인덱스 자료 구조를 이용해서 데이터를 추출 |
| using where | where 조건으로 데이터를 추출. type이 ALL 혹은 Index 타입과 함께 표현되면 성능이 좋지 않다는 의미 |
| using filesort | 데이터 정렬이 필요한 경우로 메모리 혹은 디스크상에서의 정렬을 모두 포함. 결과 데이터가 많은 경우 성능에 직접적인 영향을 줌 |
| using temporary | 쿼리 처리 시 내부적으로 temporary table이 사용되는 경우를 의미함 |
MySQL Explain 결과에서 일반적으로 데이터가 많은 경우 Using Filesort 와 Using Temporary 상태는 좋지 않으며 MySQL 쿼리 튜닝 후 성능 최적화를 위한 모니터링이 필요하다.
- https://www.mysql.com
- Real MySQL 8.0