공부를 위해 "개발자와 DBA를 위한 Real MySQL" 이라는 책을 보면서 정리하는 내용 입니다.
옵티마이저 - 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업을 담당
규칙 기반 최적화(Rule-based optimizer, RBO) 방법
- 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립
- 현재는 거의 사용하지 않음
비용 기반 최적화(Cost-based optimizer, CBO) 방법
- 쿼리를 처리하기 위해 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 각 실행 계획별 비용을 산출
- 산출된 각 실행 방법별로 최소 비용이 소요되는 처리 방식을 선택해 최종 쿼리를 실행
MySQL 서버에서 쿼리가 실행되는 과정
1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다.
- SQL 파싱(Parsing)
- 이 단계를 SQL 파싱(Parsing)이라고 하며, MySQL 서버의 SQL 파서 라는 모듈로 처리.
- 문법적으로 잘못됐다면 이 단계에서 걸러진다.
- SQL 파스 트리가 만들어진다.
2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
- 최적화 및 실행 계획 수립
- 불필요한 조건의 제거 및 복잡한 연산의 단순화
- 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
- 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정
- 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
- 이 단계가 완료되면 실행 계획이 만들어짐
3. 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
- 수립된 실행계획대로 스토리지 엔진에 레코드를 읽어오도록 요청
- MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업 수행
실행 순서는 위에서 아래로 순서대로 표시된다(UNION이나 상관 서브 쿼리와 같은 경우 순서대로 표시되지 않을 수도 있다).
1. id 컬럼
SELECT 키워드 단위로 구분한 것을 "단위(SELECT) 쿼리" 라고 표현한다.(책 내에서 사용)
id컬럼은 단위 SELECT 쿼리 별로 부여되는 식별자 값이다.
하나의 SELECT 문장 안에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id가 부여된다.
2. select_type 컬럼
각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼이다.
2.1. SIMPLE
UNION이나 서브 쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우(조인이 포함된 경우에도 마찬가지이다.)
쿼리 문장이 아무리 복잡하더라도 실행 계획에서 select_type이 SIMPLE인 단위 쿼리는 반드시 하나만 존재한다.
일반적으로 제일 바깥 SELECT 쿼리
2.2. PRIMARY
UNION이나 서브 쿼리가 포함된 SELECT 쿼리의 실행 계획에서 가장 바깥쪽(OUTER)에 있는 쿼리
PRIMARY인 단위 SELECT 쿼리는 하나만 존재
2.3. UNION
UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리
2.4. DEPENDENT UNION
UNION이나 UNION ALL로 집합을 결합하는 쿼리에서 표시
DEPENDENT는 UNION이나 UNION ALL로 결합된 단위 쿼리가 외부의 영향에 의해 영향을 받는 것을 의미
2.5. UNION RESULT
UNION 결과를 담아두는 테이블을 의미
MySQL에서 UNION ALL 이나 UNION (DISTINCT) 쿼리는 모두 UNION의 결과를 임시 테이블로 생성하게 되는데, 실행 계획상에서 이 임시 테이블을 가리키는 라인이 UNION RESULT다.
단위 쿼리가 아니기 때문에 id 값은 부여되지 않는다.
2.6. SUBQUERY
FROM 절 이외에서 사용되는 서브 쿼리만을 의미
FROM 절에서 사용된 SUBQUERY는 DERIVED라고 표시
중첩된 쿼리(Nested Query) - SELECT 컬럼에 사용된 서브쿼리를 네스티드 쿼리라고 한다.
서브 쿼리(Sub Query) - WHERE 절에 사용된 경우에는 일반적으로 그냥 서브 쿼리라고 한다.
파생 테이블(Derived) - FROM 절에 사용된 서브 쿼리를 MySQL에서는 파생 테이블이라고 하며, 일반적으로 RDBMS 전체적으로 인라인 뷰(inline View) 또는 서브 셀렉트(Sub Select)라고 부르기도 한다.
스칼라 서브 쿼리(Scalar SubQuery) - 하나의 값만(컬럼이 단 하나인 레코드 1건만) 반환하는 쿼리
로우 서브 쿼리(Row Sub Query) - 컬럼의 개수에 관계없이 하나의 레코드만 반환하는 쿼리
2.7. DEPENDENT SUBQUERY
서브 쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 컬럼을 사용하는 경우를 DEPENDENT SUBQUERY라고 표현한다.
DEPENDENT UNION과 같이 DEPENDENT SUBQUERY 또한 외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행되야 하므로 일반 서브 쿼리 보다는 처리 속도가 느릴 때가 많다.
2.8. DERIVED
서브 쿼리가 FROM 절에 사용된 경우이다.
DERIVED는 단위 SELECT 쿼리의 실행 결과를 메모리나 디스크에 임시 테이블을 생성하는 것을 의미 한다.
임시 테이블을 파생 테이블이라고도 한다.
MySQL은 FROM 절에 사용된 서브 쿼리를 제대로 최적화 하지 못할 때가 대부분이다.
파생 테이블에는 인덱스가 전혀 없으므로 다른 테이블과 조인할 때 성능상 불리할 때가 많다.
2.9. UNCACHEABLE SUBQUERY
하나의 쿼리 문장에서 서브 쿼리가 하나만 있더라도 실제 그 서브 쿼리가 한 번만 실행되는 것은 아니다.
그런데 조건이 똑같은 서브 쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브 쿼리의 결과를 내부적인 캐시 공간에 담아둔다. 여기서 언급하는 서브 쿼리 캐시는 쿼리 캐시나 파생 테이블(DERIVED)와는 전혀 무관한 기능이므로 혼동하지 않도록 주의하자.
캐시를 사용할 수 없는 경우
많이 접하지 않아 이정도 정리 자세한 내용은 책 참조
3. table 컬럼
MySQL의 샐힝 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시된다.
만약 테이블의 이름에 별칭이 부여된 경우에는 별칭이 표시된다.
테이블을 사용하지 않는 경우 table 컬럼에 null이 표시된다.
<derived> 또는 <union>과 같이 "<>"로 둘려싸인 이름이 명시된 경우는 임시 테이블을 의미 한다.
"<>" 안에 항상 표시되는 숫자는 단위 SELECT 쿼리의 id를 지칭한다.
4. type 컬럼
쿼리의 실행 계획에서 type 이후의 컬럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 의미한다. 여기서 방식이라 함은 인덱스를 사용해 레코드를 읽었는지 아니면 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔으로 레코드를 읽었는지 등을 의미한다. 일반적으로 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 실행 계획에서 type 컬럼은 반드시 체크해야 할 중요한 정보다.
MySQL의 메뉴얼에서는 type 컬럼을 "조인 타입"으로 소개한다. 또한 MySQL에서는 하나의 테이블로부터 레코드를 읽는 작업도 조인처럼 처리한다. 그래서 SELECT 쿼리의 테이블 개수에 관계없이 실행계획의 type 컬럼을 "조인 타입"이라고 명시하고 있다. 하지만 크게 조인과 연관 지어 생각하지 말고, 각 테이블의 접근 방식(Access type)으로 해석하면 된다.
실행 계획의 type 컬럼에 표시될 수 있는 값은 버전에 따라 조금씩 차이가 있을 수 있지만, 현재 많이 사용되는 MySQL 5.0과 5.1 버전에서는 다음과 같은 값이 표시된다.
system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL
위의 12가지 접근 방법 중에서 하단의 ALL을 제외한 나머지는 모두 인덱스를 사용하는 접근 방법이다. ALL은 인덱스를 사용하지 않고, 테이블을 처음부터 끝가지 읽어서 레코드를 가져오는 풀 테이블 스캔 접근 방식을 의미한다. 하나의 단위 SELECT 쿼리는 위의 접근 방법 중에서 단 하나만 사용할 수 있다. 또한 index_merge를 제외한 나머지 접근 방법은 반드시 하나의 인덱스만 사용한다. 그러므로 실행 계획의 각 라인에 접근 방법이 2개 이상 표시되지 않으며, index_merge 이외의 type에서는 인덱스 항목에도 단 하나의 인덱스 이름만 표시된다.
성능이 빠른 순서대로 나열된 것
4.1. system
레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법을 system이라고 한다. 이 접근 방식은 InnoDB 테이블에서는 나타나지 않고, MyISAM이나 MEMORY테이블에서만 사용되는 접근 방법이다.
4.2. const
테이블의 레코드의 건수에 관계없이 쿼리가 프라이머리 키나 유니크 키 컬럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식을 const라고 한다. 다른 DBMS에서는 이를 유니크 인덱스 스캔(UNIQUE INDEX SCAN)이라고도 표현한다.
다중 컬럼으로 구성된 프라이머리 키나 유니크 키 중에서 인덱스의 일부 컬럼만 조건으로 사용할 때는 const 타입의 접근 방법을 사용할 수 없다.
4.3. eq_ref
eq_ref 접근 방법은 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다. 조인에서 처음 읽은 테이블의 컬럼 값을, 그다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 컬럼의 검색 조건에 사용 할 때를 eq_ref라고 한다. 이때 두 번째 이후에 읽는 테이블의 type 컬럼에 eq_ref가 표시된다. 또한 두 번째 이후에 읽히는 테이블을 유니크 키로 검색할 때 그 유니크 인덱스는 NOT NULL이어야 하며, 다중 컬럼으로 만들어진 프라이머리 키나 유니크 인덱스라면 인덱스의 모든 컬럼이 비교 조건에 사용되야만 eq_ref 접근 방법이 사용될 수 있다. 즉, 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.
4.4. ref
ref 접근 방법은 eq_ref와는 달리 조인의 순서와 관계없이 사용되며, 또한 프라이머리 키나 유니크 키 등의 제약 조건도 없다. 인덱스의 종류와 관계없이 동등(Equal) 조건으로 검색할 때는 ref 접근 방법이 사용된다. ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref보다는 빠르지 않다. 하지만 동등한 조건으로만 비교되므로 매우 빠른 레코드 조회 방법의 하나이다.
4.5. fulltext
fulltext 접근 방법은 MySQL의 전문 검색(Fulltext) 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다. 전문 검색 인덱스는 통계 정보가 관리되지 않으며, 전문 검색 인덱스를 사용할 수 있는 SQL에서는 쿼리의 비용과는 관계없이 거의 매번 fulltext 접근 방법을 사용한다. 테이블에 전문 검색용 인덱스가 준비돼 있어야만 한다.
4.6. ref_or_null
이 접근 방법은 ref 접근 방식과 같은데, NULL 비교가 추가된 형태다. 접근 방식의 이름 그대로 ref 방식 또는 NULL 비교(IS NULL) 접근 방식을 의미한다.
4.7. unique_subquery
WHERE 조건절에서 사용될 수 있는 IN (subquery) 형태의 쿼리를 위한 접근 방식이다. unique_subquery의 의미 그대로 서브 쿼리에서 중복되지 않은 유니크한 값만 반환할 때 이 접근 방법을 사용한다.
4.8. index_subquery
IN 연산자의 특성상, IN (subquery) 또는 IN (상수 나열) 형태의 조건은 괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거돼야 한다. IN (subquery)에서 중복된 값을 반환할 수는 있지만 중복된 값을 인덱스를 이용해 제거할 수 있을 때 index_subqeury 접근 방법이 사용된다.
4.9. range
range는 우리가 익히 알고 있는 인덱스 레인지 스캔 형태의 접근 방법이다. range는 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미하는데, 주로 "<, >, IS NULL, BETWEEN, IN, LIKE" 등의 연산자를 이용해 인덱스를 검색할 때 사용된다.
4.10. index_merge
2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 그 결과를 병합하는 처리 방식이다.
4.11. index
index 접근 방식은 테이블을 처음부터 끝가지 읽는 풀 테이블 스캔 방식과 비교했을 때 비교하는 레코드 건수는 같다. 하지만 인덱스는 일반적으로 테이블 파일 전체보다는 크기가 작아서 풀 테이블 스캔보다는 효율적으로 풀 테이블 스캔보다는 빠르게 처리된다. 또한 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 풀 테이블 스캔보다는 훨씬 효율적으로 처리될 수도 있다.
4.12. ALL
풀 테이블 스캔 방식이다. 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거(체크 조건이 존재할 때)하고 반환한다. 지금까지 설명한 접근방법으로는 처리할 수 없을 때 가장 마지막에 선택되는 가장 비효율적인 방법이다.
5. possible_keys
MySQL 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방식에서 사용되는 인덱스의 목록일 뿐이다. 즉, 말 그대로 "사용될 법했던 인덱스의 목록"인 것이다. 실제로 실행계획을 보면 그 테이블의 모든 인덱스가 목록에 포함되어 나오는 경우가 허다하기에 쿼리를 튜닝하는 데 아무런 도움이 되지 않는다. 그래서 실행 계획을 확인할 때는 Possible_keys 컬럼은 그냥 무시하자. 절대 Possible_keys 컬럼에 인덱스 이름이 나열됐다고 해서 그 인덱스를 사용한다고 판단하지 않도록 주의하자.
6. key
최종 선택된 실행 계획에서 사용하는 인덱스를 의미한다. 그러므로 쿼리를 튜닝할 때는 Key 컬럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다. Key 컬럼에 표시되는 값이 PRIMARY인 경우에는 프라이머리 키를 사용한다는 의미이며, 그 이외의 값은 모두 테이블이나 인덱스를 생성할 때 부여했던 고유 이름이다.
7. key_len
key_len 컬럼은 많은 사용자가 쉽게 무시하는 정보이지만 사실은 매우 중요한 정보 중 하나다. 실제 업무에서 사용하는 테이블은 단일 컬럼으로만 만들어진 인덱스보다 다중 컬럼으로 만들어진 인덱스가 더 많다. 실행 계획의 key_len 컬럼의 값은, 쿼리를 처리하기 위해 다중 컬럼으로 구성된 인덱스에서 몇 개의 컬럼까지 사용했는지 우리에게 알려 준다. 더 정확하게는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값이다. 그래서 다중 컬럼 인덱스뿐 아니라 단일 컬럼으로 만들어진 인덱스에서도 같은 지표를 제공한다.
8. ref
접근 방법이 ref 방식이면 참조 조건 (Equal 비교 조건)으로 어떤 값이 제공됐는지 보여 준다. 만약 상수 값을 지정했다면 ref 컬럼의 값은 const로 표시되고, 다른 테이블의 컬럼값이면 그 테이블 명과 컬럼 명이 표시된다.
9. rows
실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다. 이 값은 각 스토리지 엔진별로 가지고 있는 통계 정보를 참조해 MySQL 옵티마이저가 산출해 낸 예상 값이라서 정확하지는 않다. 또한, rows 컬럼에 표시되는 값은 반환하는 레코드의 예측츠가 아니라, 쿼리를 처리하기 위해 얼마나 많은 레코드를 디스크로부터 읽고 체크해야 하는지를 의미한다. 그래서 실행 계획의 rows 컬럼에 출력되는 값과 실제 쿼리 결과 반환된 레코드 건수는 거의 일치하지 않는 경우가 많다.
10. Extra
성능에 관련된 중요한 내용이 Extra 컬럼에 자주 표시된다.
10.1. const row not found (MySQL 5.1부터)
const 접근 방식으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않으면 표시된다.
10.2. Distinct
쿼리의 DISTINCT를 처리하기 위해 조인하지 않아도 되는 항목은 모두 무시하고 꼭 필요한 것만 조인한다.
10.3 Full scan on NULL key
이 처리는 "col1 IN (SELECT col2 FROM ...)"과 같은 조건이 포함된 쿼리에서 자주 발생할 수 있는데, 만약 col1 의 값이 NULL이 된다면 결과적으로 조건은 "NULL IN (SELECT col2 FROM ...)"과 같이 바뀐다. SQL 표준에서는 NULL을 "알 수 없는 값"으로 정의하고 있으며, NULL에 대한 연산의 규칙까지 정의하고 있다. 그 정의대로 연산을 수행하기 위해 이 조건을 다음과 같이 비교돼야 한다.
- 서브 쿼리가 1건이라도 결과 레코드를 가진다면 최종 비교 결과는 NULL
- 서브 쿼리가 1건도 결과 레코드를 가지지 않는다면 최종 비교 결과는 FALSE
col1이 NULL이면 풀 테이블 스캔을 해야만 결과를 알아낼 수 있다. NULL 비교 규칙을 무시해도 된다면 "col1 IS NOT NULL" 이라는 조건을 지정하면 된다.
10.4. Impossible HAVING