실행 계획 분석

실행계획분석

​

MariaDB에서 쿼리의 실행 계획을 확인하려면 EXPLAIN 명령을 사용하면 된다. 아무런 옵션 없이 EXPLAIN 명령만 사용하면 기본적인 쿼리 실행 계획만 보인다. 하지만 EXPLAIN EXTENDED나 EXPLAIN PARTITIONS명령을 이용해 더 상세한 실행 계획을 확인할 수도 있다.

​

EXPLAIN을 실행하면 쿼리 문장의 특성에 따라 표 형태로 된 1줄 이상의 결과가 표시된다. 표의 각 라인(레코드)은 쿼리 문장에서 사용된 테이블(서브쿼리로 임시 테이블을 생성한 경우 그 임시 테이블까지 포함)의 개수만큼 출력된다.

​

(다른 DBMS와 달리 MariaDB에서는 필요에 따라 실행 계획을 산출하기 위해 쿼리의 일부분을 직접 실행할 때도 있다. 때문에 쿼리 자체가 상당히 복잡하고 무거운 쿼리인 경우에는 실행 계획의 조회 또한 느려질 가능성이 있다.)

​

ID 컬럼

​

실행계획 가장 왼쪽에 표시되는 id 칼럼은 단위 SELECT 쿼리별로 부여되는 식별자 값이다.

​

SELECT_TYPE 컬럼

​

SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼이다.

​

표시되는 데이터 항목들

SIMPLE

단순한 SELECT 쿼리인 경우 SIMPLE로 표시된다.

PRIMARY

UNION이나 서브 쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽(outer)에 있는 단위 쿼리는 select_type이 PRIMARY로 표시된다.

UNION

UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리의 select_type은 UNION으로 표시된다.

DERIVED

단위 SELECT 쿼리의 실행 결과를 메모리나 디스크에 임시 테이블(파생 테이블)을 생성하는 것을 의미한다. (MariaDB 5.3 버전부터는 옵티마이저 옵션에 따라 쿼리의 특성에 맞게 임시 테이블에도 인덱스를 추가해서 만들 수 있도록 최적화 되었다.)

DEPENDENT UNION

UNION이나 UNION ALL로 집합을 결합하는 쿼리에서 표시된다. DEPENDENT는 UNION이나 UNION ALL로 결합된 단위 쿼리가 외부의 의해 영향을 받는 것을 의미한다.

UNION RESULT

UNION 결과를 담아두는 테이블을 의미한다.

SUBQUERY

SUBQUERY라고 하는 것은 FROM절 이외에서 사용되는 서브 쿼리만을 의미한다.

상황에 따라서 다른 이름을 지니고 있다.

중첩된 쿼리 (Nested Query)

SELECT 되는 칼럼에 사용된 서브 쿼리를 네스티드 쿼리라고 한다.

​

서브쿼리 (Sub Query)

WHERE 절에 사용된 경우에는 일반적으로 그냥 서브 쿼리라고 한다.

​

파생 테이블 (Derived)

FROM 절에 사용된 서브 쿼리를 MariaDB에서는 파생 테이블이라고 하며, 일반적으로 RDBMS에서는 인라인 뷰(inline View)또는 서브 셀렉트(Sub Select)라고 부르기도 한다.

​

스칼라 서브 쿼리 (Scala SubQuery)

하나의 값만(칼럼이 단 하나인 레코드 1건만)반환하는 쿼리

​

로우 서브 쿼리 (Row Sub Query)

칼럼의 개수에 관계없이 하나의 레코드만 반환하는 쿼리

DEPENDENT SUBQUERY

서브 쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 칼럼을 사용하는 경우를 DEPENDENT SUBQUERY라고 표현한다.

​

UNCACHEABLE SUBQUERY

일반적인 SubQuery는 캐시되어 사용되지만, 캐시되어 사용되지 못하는 경우 이렇게 표시된다.

​

UNCACHEABLE UNION

위와 비슷

MATERIALIZED

MariaDB 5.3 버전과 MySQL 5.6 버전부터 도입된 select_type으로, 주로 FROM 절이나 IN(subquery) 형태의 쿼리에 사용된 서브 쿼리의 최적화를 위해서 사용된다.

​

INSERT,DELETE, UPDATE

type 칼럼

​

쿼리 실행 계획에서 type 이후의 칼럼은 MariaDB 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타낸다. 여기서 방식이라 함은 인덱스를 사용해 레코드를 읽었는지 아니면 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔으로 레코드를 읽었는지 등을 의미한다.

​

system - 레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법을 system이라고 한다.

​

const - 테이블의 레코드의 건수에 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식을 const라고 한다. 다른 DBMS에서는 이를 유니크 인덱스 스캔(Unique Index Scan)이라고도 표현한다.

​

eq_ref

접근 방법은 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다. 조인에서 처음 읽은 테이블의 칼럼 값을, 그다음 읽어야 할 테이블의 primary key나 unique key 칼럼의 검색 조건에 사용할 때를 eq_ref라고 한다. (조인 조건에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.)

​

ref

eq_ref와는 달리 조인의 순서와 관계없이 사용되며, 또한 primary key나 unique key등의 제약 조건도 없다.

(ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로, const나 eq_ref보다는 빠르지 않다.)

​

가끔 쿼리의 실행 계획에서 ref 칼럼의 값이 “func”라고 표시될 때가 있다. 이는 “function”의 줄임말로 참조용으로 사용되는 값을 그대로 사용한 것이 아니라 콜레이션 변환이나 값 자체의 연산을 거쳐서 참조됐다는 것을 의미한다.

MariaDB 서버가 내부적으로 값을 변환해야 할 때도 ref 칼럼에는 “func”가 성립된다. 문자집합이 일치하지 않는 두 문자열 칼럼을 조인한다거나 숫자 타입의 칼럼과 문자열 타입의 칼럼으로 조인할 때가 대표적인 예다.

가능하다면 MariaDB서버가 이런 변환을 하지 않아도 되도록 조인 칼럼의 타입은 일치시키는 편이 좋다.

​

fulltext

fulltext 접근 방법은 MariaDB의 전문 검색(Fulltext) 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다.

type의 순서가 일반적으로 처리 성능의 순서이긴 하지만 실제로 데이터의 분포나 레코드의 건수에 따라 빠른 순서는 달라질 수 있다.

​

ref_or_null

이 접근 방법은 ref 접근 방식과 같은데. NULL 비교가 추가된 형태다. 접근 방식의 이름 그대로 ref 방식 또는 null 비교(is null) 접근 방식을 의미한다.

​

index_subquery

IN 연산자의 특성상 IN(subquery)또는 IN(상수 나열) 형태의 조건은 괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거돼야 한다.

​

unique_subquery

IN(subquery) 형태의 조건에서 subquery의 반환 값에는 중복이 없으므로 별도의 중복 제거 작업이 필요하지 않음.

​

range

인덱스 레인지 스캔 형태의 접근 방법이다. range는 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미하는데, 주로 “<, >, IS NULL, BETWEEN, IN, LIKE” 등의 연산자를 이용해 인덱스를 검색할 때 사용된다.

​

index_merge

2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 그 결과를 병합하는 처리 방식이다. 하지만 지금까지의 경험으로 보면 이름만큼 그렇게 효율적으로 작동하는 것 같지는 않았다. index_merge 접근 방식에는 다음과 같은 특징이 있다.

​

• 여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방식보다 효율성이 떨어진다.

• AND와 OR 연산이 복잡하게 연결된 쿼리에서는 제대로 최적화되지 못할 때가 많다.

• 전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않는다.

• index_merge 접근 방식으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.

​

index

index접근 방법은 많은 사람이 자주 오해하는 접근 방법이다. 접근 방식의 이름이 index라서 “효율적으로 인덱스를 사용하는구나”라고 생각하게 만드는거 같다. 하지만 index 접근 방식은 인덱스를 처음부터 끝까지 읽는 것을 의미하는 것은 아니라는 점을 잊지 말자.

​

index 접근 방식은 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔 방식과 비교했을 때 비교하는 레코드 건수는 같다. 하지만 인덱스는 일반적으로 데이터 파일 전체보다는 크기가 작아서 풀 테이블 스캔보다는 효율적으로 풀 테이블 스캔보다는 빠르게 처리된다. 또한 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 풀 테이블 스캔보다는 훨씬 효율적으로 처리될 수도 있다.

​

(인덱스 생성시 ASC, DESC등으로 정렬을 할 수 있는데 이와 같은 이유로 설명하는 듯 하다)

​

ALL

우리가 흔히 알고 있는 풀 테이블 스캔을 의미하는 접근 방식이다. 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거(체크 조건이 존재할 때)하고 반환한다. 풀 테이블 스캔은 지금까지 설명한 접근 방법으로는 처리할 수 없을 때 가장 마지막에 선택되는 가장 비효율적인 방법이다.

​

다른 DBMS와 같이 InnoDB도 풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 작ㅇ버을 위해 한꺼번에 많은 페이지를 읽어들이는 기능을 제공한다. InnoDB에서는 이 기능을 “리드 어헤드(Read Ahead)”라고 하며, 한번에 여러 페이지를 읽어서 처리할 수 있다.

​

쿼리를 튜닝한다는 것이 무조건 인덱스 풀 스캔이나 테이블 풀 스캔을 사용하지 못하게 하는 것은 아니라는 점을 기억하자.

​

일반적으로 index와 ALL 접근 방법은 작업 범위를 제한하는 조건이 아니므로 빠른 응답을 사용자에게 보내 줘야 하는 웹 서비스 등과 같은 OLTP환경에서는 적합하지 않다. 테이블이 매우 작지 않다면 실제로 테이블에 데이터를 어느 정도 저장한 상태에서 쿼리의 성능을 확인해 보고 적용하는 것이 좋다.

​

OLTP (Online Transaction Processing) - 온라인 트랜잭션 처리 네트워크상의 여러 이용자가 실시간으로 데이터베이스의 데이터를 갱신하거나 조회하는 등의 단위 작업을 처리하는 방식을 말한다. 주로 신용카드 조회 업무나 자동 현금 지급 등 금융 전산 관련 부문에서 많이 발생하기 때문에 ‘온라인 거래처리’라고도 한다. 이 방식의 특징은 기존 컴퓨터 통신에서 이용해 온 온라인 방식과 달리 다수의 이용자가 거의 동시에 이용할 수 있도록 송수신 자료를 트랜잭션(데이터 파일의 내용에 영향을 미치는 거래 ·입출고 ·저장 등의 단위 행위) 단위로 압축, 비어 있는 공간을 다른 사용자들이 함께 쓸 수 있도록 한 점이다. OLAP (OnLine Analytical Processing) - 온라인 분석 처리 OLAP는 사용자가 다양한 각도에서 직접 대화식으로 정보를 분석하는 과정을 말한다. OLAP 시스템은 단독으로 존재하는 정보 시스템이 아니며, 데이터 웨어하우스나 데이터 마트와 같은 시스템과 상호 연관된다. 데이터 웨어하우스가 데이터를 저장하고 관리한다면, OLAP은 데이터 웨어하우스의 데이터를 전략적인 정보로 변환시키는 역할을 한다. OLAP은 기본적인 접근과 조회·계산·시계열·복잡한 모델링까지도 가능하다. OLAP은 최근의 정보 시스템과 같이 중간매개체 없이 이용자들이 직접 컴퓨터를 이용하여 데이터에 접근하는 데 있어 필수적인 시스템이라 할 수 있다. OLAP 와 OLTP 의 차이점 OLTP : 현재 업무의 효율적인 처리에만 관심이 있음 OLAP : 의사결정에 도움되는 데이터 분석에 관심이 있음 출처 : <https://unabated.tistory.com/entry/OLTP-OLAP>

​

possible_keys 칼럼

실행 계획에 있는 이 칼럼 또한 사용자의 오해를 자주 불러일으키곤 한다. MariaDB 옵티마이저 쿼리를 처리하기 위해 여러 가지 처리 방법을 고려하고 그중에서 비용이 가장 낮을 것으로 예상하는 실행 계획을 선택해서 쿼리를 실행한다. 그런데 possible_keys 칼럼에 있는 내용은 MariaDB 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방식에서 사용되는 인덱스의 목록일 뿐이다. 즉, 말 그대로 “사용될 법했던 인덱스의 목록”이다.

해당 컬럼은 무시해도 된다고 안내하고 있다.

​

key 컬럼

key칼럼에 표시되는 인덱스는 최종 선택된 실행 계획에서 사용하는 인덱스를 의미한다. 그러므로 쿼리를 튜닝할 때는 Key 칼럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다. key 칼럼에 표시되는 값이 PRIMARY인 경우에는 프라이머리키를 이용한다는 의미이며, 그 외의 값은 모두 테이블이나 인덱스를 생성할 때 부여했던 고유 이름이다.

​

key_len 컬럼

key_len 칼럼은 많은 사용자가 쉽게 무시하는 정보지만 사실은 매우 중요한 정보 중 하나다. 실제 업무에서 사용하는 테이블은 단일 칼럼으로만 만들어진 인덱스보다 다중 칼럼으로 만들어진 인덱스가 더 많다. 실행 계획에서 해당 칼럼의 값은 쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇개의 칼럼까지 사용했는지 우리에게 알려준다.

​

rows 칼럼

MariaDB 옵티마이저는 각 조건에 대해 가능한 처리 방식을 나열하고, 각 처리 방식의 비용을 비교해 최종적으로 하나의 실행 계획을 수립한다.

​

Extra 칼럼

쿼리의 실행 계획에서 성능에 관련된 중요한 내용이 Extra 칼럼에 자주 표시된다. 고정된 몇개의 문장이 표시되는데, 일반적으로 2~3개씩 같이 표시된다.

​

• const_row not found = 이런 메시지가 발생되면 const 접근 방식으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않으면 Extra 칼럼에 이 내용이 표시된다.

​

• Distict = 결과를 중복 없이 유니크하게 가져오기 위한 쿼리이다.

​

• Full scan on Null key

이 처리는 “cal1 IN (SELECT col2 FROM …)”과 같은 조건을 가진 쿼리에서 자주 발생할 수 있는데, 만약 col1의 값이 NULL이 된다면 결과적으로 조건은 “NULL IN (SELECT col2 FROM …)”과 같이 바뀐다.

• 서브 쿼리가 1건이라도 결과 레코드를 가진다면 최종 비교 결과는 NULL

• 서브 쿼리가 1건도 결과 레코드를 가지지 않는다면 최종 비교 결과는 FALSE

​

• impossible HAVING

쿼리에 사용된 HAVING 절의 조건을 만족하는 레코드가 없을 때 실행 계획의 Extra칼럼에는 “Impossible HAVING” 키워드가 표시된다.

​

• impossible WHERE

“Impossible HAVING” 과 비슷하며, WHERE 조건이 항상 FALSE가 될 수 밖에 없는 경우 “Impossible WHERE”가 표시된다.

​

• Impossible WHERE noticed after reading const tables

쿼리에서 const 접근 방식이 필요한 부분은 실행 계획 수립 단계에서 옵티마이저가 직접 쿼리의 일부를 실행하고, 실행된 결과 값을 원본 쿼리의 상수로 대체한다.

​

• No matching min/max row

where 쿼리 조건절을 만족하는 레코드가 한 건도 없는 경우 일반적으로 “Impossible WHERE…” 문장이 Extra 칼럼에 표시된다. 만약 MIN()이나 MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한건도 없을 때는 Extra 컬럼에 “No matching min/max row”라는 메시지가 출력된다.

​

• No matching row in const table

const 방식으로 접근할 때 일치하는 레코드가 없다면 발생된다.

​

• No tables used

​

FROM 절이 없는 쿼리 문장이나 “FROM DUAL” 형태의 쿼리 실행 계획에서는 Extra 컬럼에 “No tables used”라는 메시지가 출력된다.

​

• Not exists

​

• Range checked for each record(index map: N)

매 레코드마다 인덱스 레인지 스캔을 체크한다.

​

• Scanned N database

​

N은 몇 개의 DB 정보를 읽었는지 보여주는 것인데, N은 0과 1 또는 all의 값을 가지며 각각의 의미는 다음과 같다.

• 0 : 특정 테이블 정보만 요청되어 데이터베이스 전체의 메타 정보를 읽지 않음

• 1 : 특정 데이터베이스내의 모든 스키마 정보가 요청되어 해당 데이터베이스의 모든 스키마 정보를 읽음

• ALL : MariaDB 서버 내의 모든 스키마 정보를 다 읽음

​

• Select tables optimized away

Min(), Max()를 조회하는 쿼리가 적절한 인덱스를 사용할 수 없을 때 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용된다면 칼럼에 표시된다.

​

• Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table

이 코멘트 또한 “Scannced N databases”와 같이 INFORMATION_SCHEMA DB의 메타 정보를 조회하는 SELECT 쿼리의 실행 계획에서만 표시되는 내용이다.

​

• unique row not found

두 개의 테이블이 각각 유니크(프라이머리 키 포함) 칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하지 않는 레코드가 존재하지 않을 때 Extra 컬럼에 이 코멘트가 표시된다.

​

• using filesort

ORDER BY를 처리하기 위해 인덱스를 이용할 수도 있지만 적절한 인덱스를 사용하지 못할 때는 MariaDB 서버가 조회된 레코드를 다시 한 번 정렬해야 한다. ORDER BY 처리가 인덱스를 사용하지 못할 때만 실행 계획의 Extra 컬럼에 “Using filesort” 코멘트가 표시되며, 이는 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 알고리즘을 수행하게 된다는 의미다.

​

• using Index(커버링 인덱스)

데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 Extra 칼럼에 “Using Index”가 표시된다.

​

• using index for group-by

GROUP BY 처리를 위해 MariaDB 서버는 그룹핑 기준 칼럼을 이용해 정렬 작업을 수행하고 다시 정렬된 결과를 그룹핑하는 형태의 고부하 작업을 필요로 한다. 하지만 GROUP BY 처리가 인덱스(B-Tree 인덱스에 한해서) 이용하면 정렬된 인덱스 칼럼을 순서대로 읽으면서 그룹핑 작업만 수행한다. 이렇게 GROUP BY 처리에 인덱스를 이용하면 레코드의 정렬이 필요하지 않고 인덱스의 필요한 부분만 읽으면 되기 때문에 상당히 효율적으로 빠르게 처리된다.

​

Group By가 인덱스를 사용될 때 해당 메시지가 나타난다.

​

• using join buffer(Block Nested Loop), Using join buffer(Batched Key Access)

일반적으로 빠른 쿼리 실행을 위해 조인되는 칼럼은 인덱스를 생성한다. 실제로 조인에 필요한 인덱스는 조인되는 양쪽 테이블 칼럼 모두가 필요한 것이 아니라 조인에서 뒤에 읽는 테이블의 칼람에만 필요하다.

​

뒤에 읽는 테이블은 검색 위주로 사용되기 때문에 인덱스가 없으면 성능에 미치는 영향이 매우 크기 때문이다.

조인이 수행될 때 드리븐 테이블의 조인 칼럼에 적절한 인덱스가 있다면 아무런 문제가 되지 않는다.

하지만 드리븐 테이블에 검색을 위한 적절한 인덱스가 없다면 드라이빙 테이블(조인에서 먼저 읽어야 하는 테이블)로부터 읽은 레코드의 건수만큼 매번 드리븐 테이블을 풀 테이블 스캔이나 인덱스 풀 스캔을 해야할 것이다. 이때 사용되는 드리븐 테이블의 비효율적인 검색을 보완하기 위해 MariaDB서버는 드라이빙 테이블에서 읽은 레코드를 임시 공간에 보관해두고 필요할 때 재사용 할 수 있게 해준다. 이때 저장되는 메모리 공간을 “조인 버퍼”라고 하며 extra 칼럼에 위 메시지가 표시된다.

​

​

​

(너무 많잖어 -_-?) 나머진 필요한대로 추가

​

​

​

Real Maria DB 참고.