모든 일에는 계획이 필요하다. 일을 하든… 여행을 가든…
그리고 우리는 그 일을 처리하기 위한 여러가지 계획을 세우고, 그 중에서 어떤 방식이 최적이고 최소의 비용이 소모되는지를 결정하게 된다.
이는 DBMS도 마찬가지이다. 옵티마이저는 쿼리를 실행하기전 여러가지 통계정보를 참조하여 최적의 계획을 세우고, 그 계획대로 쿼리를 실행한다.
옵티마이저
SQL을 가장 빠르고 효율적으로 수행할 최적의 경로를 생성하는 DBMS 내부 핵심엔진이다.
즉 데이터베이스 서버에서 두뇌와 같은 역할을 담당한다.
옵티마이저의 최적화 방법으로는규칙 기반 최적화(RBO)와비용 기반 최적화(CBO)가 있는데, 현재는 거의 대부분의 DBMS에서 비용 기반 최적화를 사용하고 있다.
MySQL 또한 마찬가지이다.
통계 정보
비용 기반 최적화에서 실행계획 수립 시 가장 중요하게 사용되는 정보이다.
통계 정보가 정확하지 않으면 전혀 엉뚱한 방향으로 쿼리를 실행해 버릴 수 있기 때문이다.
이 통계정보는ANALYZE라는 명령어를 사용해 직접 갱신할 수 있는데, MySQL의 경우 사용자가 알아채지 못하는 사이에 자동으로 계속 변경되기 때문에 직접 수동으로 갱신할 일은 별로 없다.
(하지만 레코드 건수가 작으면 통계 정보가 부정확할 때가 많음)
InnoDB의 경우ANALYZE를 실행하는 동안 읽기와 쓰기가 모두 불가능하므로 서비스 도중에는 실행하지 않는것이 좋다.
쿼리 실행절차
MySQL 서버에서 쿼리가 실행되는 과정은 크게 3가지로 나눌 수 있다.
- 요청받은 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다.
- 위에서 생성된 SQL 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽을지, 어떤 인덱스를 이용할지 선택한다.
- 위에서 선택된 순서와 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
1번 단계를 SQL 파싱이라고 하고, MySQL 서버의 SQL 파서라는 모듈로 처리한다.
이 단계에서 만들어진 것을 SQL 파스 트리라고 한다.
2번 단계에서는 1번 단계에서 생성된 SQL 파스 트리를 참조하여 옵티마이저에서 다음 내용을 처리한다.
- 불필요한 조건 제거 및 복잡한 연산 단순화
- 조인이 있는 경우, 어떤 순서로 읽을 지 결정
- 조건과 통계정보를 참조해 사용할 인덱스 결정
- 임시테이블을 통해 다시 가공해야 하는지 결정
3번 단계에서는 2번 단계에서 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고,
받은 레코드를 MySQL 엔진이 조인하거나 정렬하는 작업을 수행한다.
MySQL은 스토리지 엔진과 MySQL 엔진으로 구분된다.
스토리지 엔진은 디스크나 메모리상에서 필요한 레코드를 읽거나 저장하는 역할을 하며,
MySQL 엔진은 스토리지 엔진으로부터 받은 레코드를 가공/연산하는 작업을 수행한다.
보다시피 1,2번 단계는 거의 MySQL 엔진에서 처리하며, 3번 단계는 MySQL 엔진과 스토리지 엔진이 동시에 참여해서 처리한다.
실행계획 분석
쿼리에 EXPLAIN이라는 명령어를 추가로 사용하면 MySQL이 수립한 실행계획을 직접 볼수있다.
아래는 실행계획의 예시이다.
표의 각 라인은 사용된 테이블의 개수(임시 테이블 포함)이고,
실행순서는 대체적으로 위에서 아래로 진행된다.
참고로 실행계획은 SELECT 문만 확인 가능하며, DML 문장의 실행계획을 확인하고 싶으면 WHERE 조건절만 같은 SELECT 문을 만들어서 대략적으로 확인해보는 수 밖에 없다.
id
SELECT 단위 쿼리별로 부여되는 식별자 값이다.
하지만 만약 JOIN을 했을 경우, 레코드는 테이블의 개수만큼 출력되지만 id는 동일하게 부여된다.
JOIN 시 먼저(윗 라인)에 표시된 테이블이 드라이빙 테이블, 이후에 표시된 테이블이 드리븐 테이블이 된다.
select_type
각 단위 SELECT가 어떤 타입의 쿼리인지 표시하는 칼럼이다.
SIMPLE
UNION이나 서브 쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우 표시된다.
PRIMARY
UNION이나 서브 쿼리가 포함된 SELECT 쿼리의 실행계획에서 가장 바깥쪽에 있는 단위쿼리인 경우 표시된다.
UNION
UNION이나 UNION ALL로 결합하는 단위 SELECT 쿼리들 중 첫번째를 제외한 두번째 이후부터 표시된다.
첫번째 레코드에는 UNION 대신 DERIVED가 표시된다.
조회된 결과를 UNION으로 결합해 임시테이블을 만들어 사용하기 떄문이다.
DEPENDENT UNION
UNION/UNION ALL을 사용하는 단위쿼리가 Outer 쿼리에 의해 영향을 받을 경우 표시된다.
1 | EXPLAIN |
예외가 조금 억지스럽긴 하다…
보다시피 UNION에서 Outer 쿼리의 emp_no 칼럼을 이용했기 때문에 DEPENDENT UNION이 표시되고 있다.
위와 같은 형태를
서브 쿼리라고 하는데, 일반적으로 서브 쿼리는 Outer 쿼리보다 먼저 실행되며, 속도도 빠르게 처리된다.
하지만 위와 같이 Outer 쿼리에 의존적인 서브쿼리, 즉DEPENDENT형태의 경우 절대 Outer 쿼리보다 먼저 실행될 수 없다.
그래서DEPENDENT실행계획이 포함된 쿼리는 비효율적인 경우가 많다.
SUBQUERY
하나의 단위쿼리가 다른 단위쿼리를 포함했을 경우 이를 서브쿼리 라고 하는데, SUBQUERY select_type은 FROM절 이외에서 사용되는 서브쿼리만을 의미한다.
FROM절에 사용된 서브쿼리는 select_type이 DERIVED로 표시된다.
DEPENDENT SUBQUERY
DEPENDENT UNION과 같이 서브쿼리가 Outer 쿼리에 정의된 컬럼을 사용하는 경우 표시된다.
이 또한 일반 서브쿼리보다 처리속도가 느린 경우가 많다.
DERIVED
단위 SELECT 쿼리의 실행 결과를 메모리나 디스크의 임시 테이블을 생성하여 저장할 때 표시된다.
MySQL은 FROM절에 사용된 서브쿼리를 제대로 최적화하지 못할 경우가 대부분이다.(인덱스가 전혀 없으므로)
그에 비해 MySQL 5.0 이후로는 조인이 상당히 최적화 된 편이므로, FROM 서브쿼리 대신 조인을 사용하는 것이 좋다.
UNCACHEABLE SUBQUERY
옵티마이저는 조건이 똑같은 서브쿼리의 실행결과는 내부적인 캐시 공간에 담아둔 뒤 다시 사용하며 성능을 향상시킨다.
SUBQUERY와 DEPENDENT SUBQUERY가 캐시를 사용하는 방법은 다음과 같다.
SUBQUERY: Outer 쿼리의 영향을 받지 않으므로 처음 한번만 실행해서 결과를 캐시하고, 필요할 떄 이용한다.DEPENDENT SUBQUERY: Outer 쿼리 컬럼의 값 단위로 캐시해두고 사용한다.
UNCACHEABLE SUBQUERY의 경우 캐시를 하지 못하는 경우 표시되는데, 이유는 다음과 같다.
- 시용자 변수가 서브쿼리에 포함된 경우
- NOT_DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리에 사용된 경우
UUID()나RAND()같이 호출할 때 마다 달라지는 함수가 서브쿼리에 사용된 경우
UNCACHEABLE UNION
위와 동일하게 UNION 결과를 캐시할 수 없을 경우 사용된다.
table
MySQL의 실행계획은 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시된다.
alias(별칭)를 사용했을 경우 alias가 표시되고, 테이블을 사용하지 않았을 경우 NULL이 표시된다.
그리고 테이블 이름이 < >같이 둘러싸였을 경우, 임시테이블을 의미한다.
type
MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 표시해준다.
이 컬럼을 통해 인덱스를 사용했는지, 테이블을 풀 스캔했는지 등을 확인할 수 있다.
인덱스를 효율적으로 사용하는 것은 매우 중요하므로, 이 컬럼은 꼭 확인해야 할 정보이다.
아래는 MySQL에서 부여한 접근속도 순위이다. ALL 타입만 빼고 모두 인덱스를 사용하는 방식이다.
system
레코드가 1건만 존재하거나 1건도 존재하지 않는 테이블을 참조할 떄 표시된다.
이는 InnoDB에서는 나타나지 않고, MyISAM이나 MEMORY 테이블에서 사용되는 접근 방식이다.
const
테이블 레코드 건수에 관계없이 WHERE 조건절에서 프라이머리 키나 유니크 키 컬럼을 사용하며, 반드시 1건만 반환할 경우 표시된다.
1 | EXPLAIN |
다중컬럼으로 구성된 프라이머리 키나 유니크 키의 일부 컬럼만 사용할 경우 const 타입의 접근 방법을 사용할 수 없다.
eq_ref
여러 테이블이 조인되는 쿼리의 실행계획에서만 표시된다.
조인에서 처음 읽은 테이블의 컬럼 값을 그 다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 컬럼 검색 조건에 사용하고,
그로 인해 두번쨰 테이블에서 출력되는 레코드가 반드시 1건이라는 보장이 있을 경우 표시된다.
1 | EXPLAIN |
ref
인덱스를 Equal 조건으로 검색할 때 사용된다.
조인의 순서와 관계없고, 프라이머리 키나 유니크 키 등의 제약조건도 없다.
반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref 보다는 느리나, 기본적으로 매우 빠른 조회방법 중 하나이다.
ref_or_null
ref와 같은데 NULL 비교가 추가된 형태이다.
1 | EXPLAIN |
실무에서 별로 사용되지 않으므로 이 정도만 기억해도 된다.
unique_subquery
WHERE 조건절에서 사용될 수 있는 IN (subquery) 형태의 쿼리를 위한 접근 방식이다.
서브쿼리에서 중복되지 않은 유니크한 값만 반환될 때 표시된다.
index_subquery
IN 연산자의 특성상 괄호안에 있는 값의 목록에는 중복이 먼저 제거되어야 한다.
index_subquery의 경우 서브쿼리가 중복된 값을 반환할 수 있지만, 인덱스를 이용해 중복을 제거할 수 있을때 표시된다.
range
인덱스를 하나의 값이 아니라 범위로 검색하는 경우에 표시된다.
범위 검색 연산자의 경우 <, >, IS NULL, BETWEEN, IN, LIKE 등이 있다.
index_merge
2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어 낸 후, 이를 병합하여 처리하는 방식이다.
1 | EXPLAIN |
하지만 index_merge의 경우 이름처럼 효율적으로 작동하는 경우가 그렇게 많지는 않다.
index
이름만 보면 아주 좋아보이나, 실제로는 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다.
풀 테이블 스캔과 읽는 레코드 수는 같으나, 인덱스가 일반적으로 데이터 파일 전체보다는 크기도 작고 정렬도 되어있으므로 풀 테이블 스캔보다는 빨리 처리된다.
이 방식은 다음의 조건을 충족할 떄 표시된다.
range,const,ref와 같은 방식으로 인덱스를 이용하지 못하는 경우- 인덱스에 포함된 컬럼으로만 처리할 수 있는 경우
- 인덱스를 이용해 정렬이나 그룹핑이 가능할 경우
ALL
풀 테이블 스캔을 의미한다.
테이블을 처음부터 끝까지 다 읽는 방식으로, 가장 비효율적인 방법이다.
possible_keys
옵티마이저가 최적화 된 실행계획을 만들기 위해 후보로 선정했던 인덱스의 목록이다.
즉, “사용될 뻔 했던 인덱스 목록” 이므로, 아무 도움도 되지 않는다. 그냥 무시하자.
key
possible_keys와 달리 최종 실행계획에서 선택된 인덱스를 의미한다.
그러므로 쿼리 튜닝 시 의도했던 인덱스가 표시되는지 이곳을 통해 확인하는 것이 중요하다.
2개 이상의 인덱스가 사용될 경우 ,로 구분되어 표시된다.
프라이머리 키의 경우 PRIMARY KEY라는 이름으로 표시된다.
key_len
실제 업무에서는 단일 컬럼 인덱스보다 다중 컬럼으로 만들어진 인덱스가 더 많은데, key_len은 쿼리를 처리하기 위해 다중 컬럼으로 구성된 인덱스에서 몇 개의 컬럼까지 사용했는지 알려준다.
정확히는 몇 바이트까지 사용했는지 알려준다.
1 | EXPLAIN |
PRIMARY KEY의 4바이트만을 이용했다고 표시되고 있다.
emp_no은 INTEGER 타입으로써 저장공간으로 4바이트를 사용한다.
즉, 복합컬럼 인덱스 중 emp_no 컬럼만을 사용했음을 나타낸다.
ref
Equal 비교 조건으로 어떤 값이 제공되었는지 표시해준다.
일반적으로 이 컬럼은 크게 신경쓰지 않아도 되는데, 컬럼에 func라고 표시될때는 조금 주의해서 살펴봐야 한다.
이는 Function의 줄임말으로 값을 그대로 사용한게 아니라 변환이나 연산을 거친 뒤 값을 사용했다는 뜻이다.
1 | EXPLAIN |
근데 중요한 점은, 위처럼 명시적으로 변환할 때 뿐만 아니라 MySQL 서버가 내부적으로 값을 변경할떄도 func가 출력된다는 점이다.
타입이 일치하지 않는 두 컬럼을 비교할때가 대표적이다.
가능하다면 이런 내부 연산이 발생하지 않도록 타입을 맞춰주는 것이 좋다.
rows
해당 쿼리를 처리하기 위해 얼마나 많은 레코드를 디스크로부터 읽고 체크해야 하는지를 의미한다.
이는 통계 정보를 참조해 옵티마이저가 산출한 값이라서 정확하지는 않다.
아래는 rows 컬럼이 실행계획에 영향을 끼친 예시이다.
1 | EXPLAIN |
보다시피 해당 쿼리를 처리하기 위해서는 331,143 개의 레코드를 읽어야 한다고 예측했다.
하지만 dept_emp 테이블의 전체 레코드 개수가 331,603개로, 거의 차이가 나지 않는다.
그래서 옵티마이저는 풀 테이블 스캔이 낫다고 판단하여 ALL로 처리된것을 볼 수 있다.
1 | EXPLAIN |
예측되는 rows를 줄였을 경우 range가 출력됨을 볼 수 있다.
Extra
이름과는 달리 실행계획에서 성능에 중요한 내용이 여기 자주 표시된다.
여기에 표출되는 고정된 몇개의 문장들이 있고, 일반적으로 2~3개씩 같이 표시된다.
Distinct
아래는 departments 테이블과 dept_emp 테이블에 모두 존재하는 dept_no을 중복없이 가져오기 위한 쿼리이다.
1 | EXPLAIN |
위처럼 Distinct가 출력되면 실제로 아래와 같이 효율적으로 처리됨을 의미한다.
DISTINCT 처리를 위해 조인하지 않아도 되는 항목은 무시하고 꼭 필요한 레코드만 읽고 있다.
Full scan on NULL key
col1 IN(SELECT col2 FROM ...) 형태의 쿼리에서 자주 발생할 수 있는 형태이다.
만약 col1의 값이 NULL이 된다면 결과적으로 NULL IN(SELECT col2 FROM ...)의 형태가 되게 되는데,
이 때 서브쿼리에 대해 풀 테이블 스캔이 발생하게 되고(이유를 정확히 모르겠다…),
이로 인해 상당한 성능저하가 발생하게 된다.
즉 이 메세지는 col1이 NULL을 만나면 풀 테이블 스캔을 사용할 것이라고 알려주는 키워드인 것이다.
만약 col1이 NOT NULL로 정의되었다면 이 메세지는 표시되지 않을 것이다.
Impossible HAVING
HAVING절의 조건을 만족하는 레코드가 없을 때 표시된다.
쿼리를 잘못 작성한 경우가 대부분이지만, 실제 저장된 데이터 때문에 발생하는 경우도 종종 있다.
쿼리와 데이터를 다시 확인해 보는것이 좋다.
Impossible WHERE
WHERE절의 조건이 항상 FALSE가 될 수 밖에 없을 때 표시된다.
Impossible WHERE noticed after reading const tables
WHERE절의 조건이 항상 FALSE가 될 수 밖에 없는데, 테이블을 읽어본 뒤 알았다는 의미이다.
아래와 같은 쿼리가 이에 해당한다.
1 | EXPLAIN |
이를 통해 실행계획을 만드는 과정에서 옵티마이저가 쿼리의 일부분을 실행해 본다는 사실을 알 수 있다.
No matching min/max row
MIN()이나 MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 하나도 없을 때 표시된다.
1 | EXPLAIN |
No tables used
FROM절 자체가 없거나, 상수 테이블을 의미하는 DUAL테이블을 사용할 때 표시된다.
Not exists
Outer Join을 이용해서 Anti-Join을 수행할 경우 표시된다.
Anti-Join
A 테이블에는 존재하지만 B 테이블에는 존재하지 않는 값을 조회할 떄 사용하는 기법이다.
일반적으로NOT IN,NOT EXIST,Outer Join을 통해서 처리하는데 레코드의 건수가 많을 때는Outer Join이 빠르다.
1 | EXPLAIN |
Range checked for each record (index map: N)
다음과 같은 쿼리가 있다고 하자.
1 | EXPLAIN |
레코드를 하나씩 읽을 때 마다 e1.emp_no의 값이 변경되기 때문에 옵티마이저 입장에서는 e2를 인덱스 레인지 스캔으로 읽을지, 풀 테이블 스캔으로 읽을 지 판단하지 못한다.
만약 employees 테이블의 레코드가 1억건이라고 가정했을때,
e1.emp_no이 1인 경우는 e2의 모든 레코드를 읽어야하지만, e1.emp_no이 100,000,000인 경우에는 e2의 레코드를 1건만 읽으면 된다.
즉 e1.emp_no이 작은 값일 때는 풀 테이블 스캔이 좋고, 큰 값일 때는 인덱스 레인지 스캔이 좋다.
이 현상을 줄여서 얘기하면 매 레코드 마다 인덱스 레인지 스캔을 체크한다 라고 할 수 있는데, 이게 바로 Range checked for each record 문구인것이다.
참고로 이 문구가 표시될 때 ref 컬럼의 값이 ALL로 표출되는데, 이는 인덱스 사용여부를 검토하고 풀 테이블 스캔을 할 수 있기 때문에 ALL로 표시된것이지 실제로 풀 테이블 스캔을 의미하는 것은 아니다.
그리고 뒤에 (index map: N) 이라는 문구가 추가로 출력되는데, 이는 사용할 인덱스의 후보를 나타내준다.
만약 (index map: 0x19)라는 문구가 표시되었다고 가정하자.
일단 16진수인 0x19를 2진수로 변경해줘야 한다. 11001이 된다.
그리고 생성된 인덱스의 순서를 보기위해 SHOW CREATE TABLE table_name을 입력한다.
그러면 테이블이 가지고 있는 인덱스를 순서대로 확인이 가능한데, 이 순서를 위의 2진수로 체크하면 된다.
즉 현재 11001이라는 값을 얻었으므로, 1번째, 2번째, 5번째 순서에 나열된 인덱스가 사용후보가 된다는 것이다.
매 레코드를 돌면서 위 3개의 사용후보들 가운데 어떤 인덱스를 사용할지 결정하게 되는데, 실제로 어떤 인덱스를 사용했는지는 알 수 없다.
Sanned N databases
MySQL에는 서버 내에 존재하는 DB의 메타정보를 담은 INFORMATION_SCHEMA라는 DB가 제공되는데, 이 데이터를 읽었을 떄 표시된다.
Select table optimized away
MIN()이나 MAX() 사용 시 인덱스를 다 읽지 않고 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용될 때 표시된다.
1 | EXPLAIN |
emp_no과 from_date로 복합 프라이머리 키가 설정되어 있으므로 emp_no이 10001인 레코드를 찾은 뒤 첫 행과 마지막 행만을 읽으면 된다.
unique row not found
Not exists의 반대라고 볼 수도 있겠다.
Outer Join을 수행하는 쿼리에서 아우터 테이블에 조건 조건에 일치하는 레코드가 없을 때 표시된다.
Using filesort
ORDER BY가 사용된 쿼리에서만 나타날 수 있다.
ORDER BY 처리에 인덱스를 사용하지 못했을 경우 나타난다.
인덱스를 사용하지 못할 경우 조회된 레코드를 메모리 버퍼에 복사한 뒤 퀵 소트 알고리즘을 수행한다.
이는 많은 부하를 일으키므로 가능하면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다.
Using index
데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 처리할 수 있을 떄 표시된다.
참고로 InnoDB의 테이블은 모두 클러스터링 인덱스로 구성되어 있어서 모든 보조 인덱스들은 데이터의 레코드 주소 값으로 프라이머리 키 값을 가진다.
즉 아래와 같은 쿼리도 Using Index로 처리되는 효과를 낼 수 있다.
1 | EXPLAIN |
현재 first_name 컬럼에 대해서만 인덱스가 생성되어 있지만 클러스터링 인덱스의 특징으로 Using index로 처리 가능하게 된다.
Using index for group-by
GROUP BY 처리를 위해서는 그룹핑 기준 컬럼을 이용해 정렬 작업을 수행한 뒤 그 결과를 그룹핑하는 고부하 작업을 필요로 한다.
하지만 GROUP BY 처리에 인덱스를 사용할 수 있으면 정렬된 인덱스 컬럼을 읽으면서 그룹핑 작업만 수행하면 된다.
이는 상당히 빠르게 처리되고, 이처럼 GROUP BY에 인덱스가 사용되었을 때 이 메세지가 표시된다.
Using join buffer
일반적으로 조인이 되는 컬럼은 인덱스를 생성해야 빠른 처리를 할 수 있다.
이는 MySQL이 조인을 Nested loop 방식으로만 처리하기 때문이다.
Nested loop join
FROM절에 아무리 테이블이 많아도 조인을 수행할 때 반드시 2개의 테이블이 비교되는 방식으로 처리하는 것이다.
먼저 읽히는 테이블이드라이빙 테이블이 되고, 뒤에 읽히는 테이블이드리븐 테이블이 된다.
즉 드라이빙 테이블의 건수만큼 드리븐 테이블이 스캔되므로 드라이빙 테이블이 어떤 테이블이냐가 성능을 많이 좌우한다.
옵티마이저는 두 테이블을 조인할때 각 테이블의 조인 기준 컬럼에 인덱스가 있는지 조사하고, 인덱스가 없는 테이블이 있다면 그 테이블을 드라이빙 테이블로 지정하여 실행한다.
위에서 언급했듯이 드리븐 테이블은 계속해서 탐색되므로 인덱스가 없으면 성능에 영향을 많이 미치기 때문이다.
근데 만약 드리븐 테이블에도 인덱스가 없다면 매번 드리븐 테이블을 풀 테이블 스캔해야 하는데, MySQL에서는 이러한 비효율적인 검색을 보안하기 위해 조인 버퍼라는 것을 사용한다.
드라이빙 테이블에서 읽은 데이터를 조인 버퍼에 저장해두고, 필요할 때 마다 재사용할 수 있게 해준다.
조인 버퍼가 사용되는 실행계획에 Using join buffer 메세지가 표시된다.
Using intersect, Using sort_union, Using union
index_merge 방식이 사용될 때 두 인덱스의 결과를 어떻게 병합했는지 조금 더 상세하게 설명하기 위한 메세지이다.
출력되는 메세지는 아래의 3개이다.
- Using intersect : 각각의 인덱스를 사용하는 조건이
AND로 연결될 때 - Using union : 각각의 인덱스를 사용하는 조건이
OR로 연결될 때 - Using sort_union :
Using union으로 처리하기 힘들 정도로 대량의 조건들이OR로 연결될 때
Using temporary
MySQL은 쿼리를 처리하는 동안 중간 결과를 담아두기 위해 임시 테이블을 사용한다.
이 메세지가 표시되면 임시 테이블을 사용했다는 의미인데, 사용된 임시테이블이 메모리에 생성되었는지 디스크에 생성되었는지는 알 수 없다.
Using where
Using where 메세지는 MySQL 엔진에서 별도의 가공을 해서 필터링 작업을 거쳤을 경우 표시된다.
실제로 가장 흔하게 표시되는 메세지이다.
그러나 이 메세지만으로 정확하게 성능 이슈를 판단하긴 어렵고, MySQL 5.1부터 추가된 Filtered 컬럼과 함께 보아야 성능샹 이슈를 쉽게 체크할 수 있다.
Filtered
스토리지 엔진에서 받은 레코드가 MySQL 엔진을 거친 뒤 얼마나 남았는가를 체크해줄 수 있는 컬럼이다.
일반 실행계획에선 볼 수 없고, EXPLAIN EXTENDED라는 명령어를 사용해야 한다.
1 | EXPLAIN EXTENDED |
현재는 스토리지 엔진에서 전달받은 299,113건의 레코드에 대해 MySQL 엔진에서 필터링 된 것 없이 100% 출력되고 있음을 볼 수 있다.
EXPLAIN EXTENDED의 추가 기능
EXPLAIN EXTENDED를 실행한 뒤SHOW WARNINGS명령을 실행하면 옵티마이저가 다시 재조합한 쿼리 문장을 확인 가능하다.
옵티마이저가 어떻게 쿼리를 해석하고 변환했는지 직접 확인할 수 있으므로 알아두면 도움이 된다.
스토리지 엔진과 mysql 엔진에서 읽어오는 데이터 양 차이의 기준은 뭘까?
참고 : 이성욱, 『Real MySQL』, 위키북스(2012)