Skip to content

Latest commit

 

History

History
525 lines (429 loc) · 26.9 KB

6-1.md

File metadata and controls

525 lines (429 loc) · 26.9 KB

6-1장. 개요 및 실행계획분석(id칼럼 ~ rows칼럼)

개요

쿼리 실행 절차

  1. 사용자로부터 요청된 SQL을 파싱한다.
  2. SQL의 파스트리를 확인하면서 읽을 테이블의 순서와 어떤 인덱스를 통할지 선택한다.
  • MySQL의 옵티마이저가 처리를 담당한다.
  • 최적화 및 실행계획 수립단계에 대표적인 행동
  • 불필요한 조건의 제거 및 복잡한 연산의 단순화
  • 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
  • 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정
  • 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
  1. 두 번째단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지엔진으로부터 데이터를 가져온다.

옵티마이저의 종류

  • 규칙 기반 최적화(Rule-based optimizer, RBO)
  • 대상 레코드의 건수나 선택도는 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행계획을 수립한다.
  • 통계 정보를 조사하지 않고 실행 계획이 수립되기 때문에 같은 쿼리에 대해 거의 같은 실행계획을 도출한다.
  • 비용 기반 최적화(Cost-based optimizer, CBO)
  • 쿼리를 처리하기 위한 여러가지 방법을 만들고, 비용 정보와 대상의 통계정보를 이용해 비용을 산출한다. 계산된 방법중 비용이 가장낮은 방법을 선택한다.

최근엔 대부분의 DBMS가 비용 기반 최적화를 채택하고있다. -> 과거엔 통계정보를 만드는 CPU비용이 더 부담스러웠기 때문이다.

통계정보

비용 기반 최적화에서 가장 중요한것은 통계정보이다. -> 통계정보가 잘못되면 실행계획이 잘못된다. 10만건인 레코드를 10건이라고 표시해 둔다면?

  • MySQL에서 관리되는 통계정보
  • 대략적인 레코드 수, 인덱스의 유니크한 값 개수(cardinality)
  • 사용자가 모르는 시점에 자동으로 함 -> 별도의 백업 필요X, 금방 갱신되서 의미없음
  • Oracle의 통계정보
  • MySQL보다 훨씬 많음
  • 수집이 정적이고 오래걸림 -> 별도의 백업을 하는 경우도 있음.

강제로 통계정보를 갱신 & 확인하는 법

// 테이블의 정보와 인덱스 확인하기
SHOW TABLE STATUS LIKE '{table_name}';
SHOW INDEX FROM {table_name}:

// ANALYZE 쿼리는 인덱스 키값의 분포도만 업데이트하며, 전체 테이블의 건수는 전체 테이블의 페이지 수를 이용해 예측한다.

// 파티션을 사용하지 않는 일반 테이블의 통계 정보 수집
ANALYZE TABLE {table_name};

// 파티션을 사용하는 테이블에서 특정 파티션의 통계 정보 수집
ANALYZE TABLE {table_name} ANALYZE PARTITION {partition};

ANALYZE 명령 같은 경우는 락이 걸리기 떄문에 서비스 중에는 실행하지 않는 것이 좋다. InnoDB의 경우 읽기/쓰기락이 MyISAM의 경우 쓰기락이 걸린다.

또한 InnoDB의 경우 인덱스 페이지 전체를 스캔하지 않고 8개 정도만 랜덤하게 선택해서 분석하고 인덱스의 통계 정보를 갱신한다. -> innodb_stats_sample_pages로 설정가능

실행 계획 분석

  • MySQL에선 EXPLAIN을 통해 실행 계획을 확인할 수 있다.
  • EXPLAIN
  • EXPLAIN EXTENDED
  • EXPLAIN PARTITIONS

EXPLAIN 사용 예시

EXPLAIN
SELECT e.emp_no, e.first_name, s.from_data, s.salary
FROM employees e, salaries s
WHERE e.emp_no=s.emp_no
LIMIT 10;

P.267 표 참고

  • 쿼리에 따라 한줄 이상의 결과가 표시된다.
  • 실행순서는 위에서 아래로 순서대로 표시된다.
  • 결과의 id가 작을 수록 쿼리의 바깥(Outer) 부분이거나 먼저 접근한 테이블
  • 결과의 id가 클수록 쿼리의 안쪽(Inner)이거나 나중에 접근한 테이블
  • UNION이나 서브쿼리같은 경우 순서대로 표시하지 않을 수 있다.
## 참고 ##
MySQL의 경우 EXPLAIN과정에서 쿼리를 실제로 실행시킬 수도 있다.
UPDATE / INSERT / DELETE의 EXPLAIN은 지원하지 않는다.
필요할 경우 SELECT에 같은 WHERE절로 대략적으로 확인할 수는 있다.

id 칼럼

SELECT ...
FROM (SELECT ... FROM tb_test1) tb1, tb_test2 tb2
WHERE tb1.id = tb2.id

-> 이 쿼리를 나눠서 생각해보자

SELECT ... FROM tb_test1
SELECT ... FROM tb1, tb_test2 tb2 WHERE tb1.id = tb2.id

P.269 표 참고

  • 하나의 SELECT 문장은 하위 SELECT문장을 포함할 수 있다.
  • 쿼리를 각 SELECT 키워드 단위로 나눈것을 단위 (SELECT) 쿼리라고 한다.

id 컬럼의 값은 단위 쿼리의 식별자이다 단, 한개의 SELECT 문장안에 여러테이블을 join해서 사용한 경우는 같은 id가 부여된다.

select_type 컬럼

단위 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다.

SIMPLE
  • UNION이나 서브 쿼리를 사용하지 않은 단순한 SELECT 쿼리
  • join이 포함된 경우도 SIMPLE로 표기한다.
  • select_type이 SIMPLE인 단위 쿼리는 한개만 존재할 수 있다.
PRIMARY
  • UNION이나 서브 쿼리를 포함한 SELECT 쿼리에서 가장 바깥쪽에 있는 단위 쿼리
  • select_type이 PRIMARY인 단위 쿼리는 한개만 존재할 수 있다.
UNION
  • UNION으로 결합하는 단위 쿼리들중 첫 번째를 제외한 단위 쿼리 JUNGLEJUNGLE P.270 예시
DEPENDENT UNION
  • 기본적으로 UNION과 같지만, UNION으로 결합된 단위 쿼리중 외부의 영향을 받은 단위 쿼리에 DEPENDENT가 표기된다.
EXPLAIN
SELECT e.first_name,
(
SELECT CONCAT('Salary change count : ', COUNT(1)) AS msg
FROM salaries s WHERE s.emp_no = e.emp_no
UNION
SELECT CONCAT('Department change count : ', COUNT(1)) AS msg
FROM dept_emp de WHERE de.emp_no = e.emp_no
) AS msg
FROM employees e
WHERE e.emp_no=10001;
## 참고 ##
단위 쿼리가 또 다른 단위 쿼리를 포함하고 있으면 그것을 서브 쿼리라고 부른다.
보통 서브 쿼리는 일반적으로 외부쿼리보다 먼저 실행되는데, 이경우가 더 빠르기 떄문이다.
단, DEPENDENT가 붙은 경우(서브 쿼리에서 외부의 영향을 받는 경우) 절대로 외부쿼리보다 먼저 실행될 수 없다.
보통 DEPENDENT가 붙으면 더 비효율적이다.
UNION RESULT

P.272 표 참고

  • UNION 결과를 담아두는 테이블을 의미한다.
  • UNION ALL이나 UNION 모두 결과를 임시 테이블로 생성하게된다.
  • UNION RESULT는 임시테이블로 단위쿼리가 아니기 때문에 id가 부여되지 않는다.
  • UNION RESULT의 테이블 이름은 <union N, M>으로 정해지는데, N과 M의 경우 유니온의 대상이 되는 단위쿼리의 id 값이다.
SUBQUERY
  • 일반적으로 이야기하는 서브 쿼리가 아닌 FROM 절 뒤에오는 서브 쿼리를 제외한 모든 서브 쿼리를 의미한다.
  • FROM 절에 사용되는 서브 쿼리는 DERIVED라고 표기된다. (MySQL메뉴얼 혹은 책의 파생 테이블과 같은 의미로 이해하면 된다.)
## 참고 ##
서브 쿼리는 사용되는 위치에 따라 각각 다른 이름을 지닌다.
- 중첩된 쿼리 (Nested Query)
- SELECT되는 칼럼에 사용된 서브 쿼리를 중첩된 쿼리라고 칭한다.
- 서브 쿼리 (Sub Query)
- WHERE 절에 사용된 경우 일반적으로 서브 쿼리라고 부른다.
- 파생 테이블 (Derived)
- FROM 절에 사용된 서브 쿼리를 MySQL에서는 파생 테이블이라고 부르며, 일반적으로 인라인 뷰(Inline View) 혹은 서브 셀렉트(Sub Select)라고 부르기도 한다.

반환하는 값의 특성에 따라 이름이 달라진다.
- 스칼라 서브 쿼리(Scalar SubQuery)
- 하나의 값(칼럼이 단 1개인 레코드 1건을 반환)만 반환하는 쿼리
- 로우 서브 쿼리 (Row SubQuery)
- 칼럼의 개수에 관계없이 하나의 레코드만 반환하는 쿼리
DEPENDENT SUBQUERY
  • DEPENDENT UNION과 마찬가지로 외부의 영향을 받은 서브 쿼리를 나타낼때 표기된다.
  • DEPENDENT가 붙었기 때문에 외부쿼리보다 먼저 실행될 수 없다.
DERIVED
  • 서브 쿼리가 FROM 절에 사용된 경우 DERIVED로 표기한다.
  • DERIVED는 단위 쿼리의 실행 결과를 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다.
  • DERIVED의 경우 임시 테이블로 생성되기 떄문에 인덱스가 없다.
  • 최적화 혹은 다른 테이블과의 조인에서 성능상 불리한 점이 많다.
## 참고 ##
쿼리를 튜닝할때 DERIVED 테이블이 있는지 확인해야한다.
서브 쿼리의 사용이 불가피 할때는 어쩔 수 없지만, 조인으로 해결가능한 상황에서는 조인을 통해 해결하는 것이 좋다.

MySQL 5.0/5.1 이상 버전은 조인이 최적화가 잘 되어있다.
MySQL 6.0 이상 버전에서는 FROM 절의 서브 쿼리에 대한 최적화가 개선될 것으로 알려져있다.
UNCACHEABLE SUBQUERY

  • 서브 쿼리 캐시

  • 쿼리 내부에 서브 쿼리가 한개만 있더라도 그 서브 쿼리가 한 번만 실행되는 것은 아니다.

  • 같은 쿼리가 여러번 실행되지 않도록 내부적으로 결과를 캐시에 담아둔다.

  • 서브 쿼리 캐시는 앞선 장에서 나왔던 쿼리 캐시나, DERIVED와는 무관한 기능이다.

  • SUBQUERY와 DEPENDENT SUBQUERY의 서브 쿼리 캐시 사용비교

  • SUBQUERY는 처음 한 번만 실행해서 그 결과를 캐시하고 필요할 때 캐시된 결과를 이용한다.

  • DEPENDENT SUBQUERY는 외부 쿼리의 값에 의존하기 떄문에 의존하는 값 단위로 결과를 캐시해두고 이용한다.

P.276 그림 6-3 참고

SUBQUERY와 UNCACHEABLE SUBQUERY의 차이는 서브 쿼리 캐시를 사용할 수 있느냐 없느냐의 차이다. ** 서브 쿼리 캐시를 사용하지 못하게 하는 대표적인 요소들**

  • 사용자 변수가 사용된 경우
  • NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브 쿼리 내에 사용된 경우
  • UUID나 RAND같이 결과값이 호출할 때마다 달라지는 함수를 사용한 경우
UNCACHEABLE UNION

UNCACHEABLE + UNION MySQL 5.1부터 추가된 타입이다.

table 칼럼

MySQL의 실행 계획은 단위 쿼리 기준이 아니라 테이블 기준으로 표시된다. P.278 표 참고

  • table 칼럼에 혹은 과 같이 <>로 둘러 싸인 이름이 명시되는 경우가 많다.
  • <>에 싸여있는 테이블의 경우 임시테이블을 의미한다.
  • <>안에 들어있는 숫자는 단위 쿼리의 id를 의미한다.

실행계획의 id, select_type, table 컬럼은 실행 계획이 어떤 순서로 실행되는지를 판단할 수 있게 해준다.

  1. 첫 번째 라인의 테이블이 <derived2>라는 것으로 보아 쿼리의 id가 2번인 라인이 먼저 실행되고 그결과로 파생 테이블을 준비해야 한다는 것을 알 수 있다.
  2. 세 번째 라인의 쿼리 id 2번을 보면 select_type의 값이 DERIVED로 표시되어 있다. 즉, 이 라인은 table 칼럼에 표시된 dept_emp테이블을 읽어서 파생테이블로 만드는 것을 알 수 있다.
  3. 첫번째 라인과 두번째 라인의 id값이 같은 것으로 보아 2개의 테이블을 join해서 사용함을 알 수 있다. <derived2>테이블이 e테이블보다 먼저 왔기때문에 <derived2>테이블을 먼저 읽어서 e 테이블로 조인을 실행했다는 것을 알 수 있다.
## 참고 ##
일부 DBMS는 SELECT 문장이 반드시 FROM 절을 가져야하는 제약이 있다.
이를 위해 DUAL이라는 스칼라(컬럼 1개 로우 1개) 값을 가지는 테이블을 사용하곤 한다.

MySQL은 FROM 절이 없어도 실행되지만, 타 DBMS와의 호환을 위해 FROM DUAL을 붙여도 무방하다.

## 참고 ##
MySQL의 경우 FROM 절의 사용되는 서브 쿼리(DERIVED)의 경우 반드시 ALIAS를 지정해줘야한다.

## 참고 ##
Join에서 주도적인 역할을 하는 테이블을 `드라이빙(Driving) 테이블`이라고 하고, `드라이빙 테이블`에게 끌려가는 테이블(Join되는 테이블)을 `드리븐(Driven) 테이블`이라고 한다.

type 칼럼

실행계획에서 type 이후의 컬럼은 테이블을 어떤 방식으로 읽었는지를 의미한다. 여기서 어떤 방식이란 인덱스를 통해서 레코드를 읽었는지 테이블 풀 스캔으로 레코드를 읽었는지 등을 의미한다. -> type은 쿼리 튜닝시 반드시 확인해야하는 정보이다.

## 참고 ##
MySQL 메뉴얼에서는 type 컬럼을 조인 타입으로 소개를 한다.
내부적으로 하나의 테이블에서 레코드를 읽는 것도 조인처럼 처리하기 때문이다.
하지만 조인과 크게 연관지어 생각할 필요는 없고 접근 방식(Access Type)으로 해셕하면 된다.
system
  • 레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블에 접근하는 방식을 system이라 한다.
  • InnoDB에서는 나타나지 않고 주로 MyISAM이나 MEMORY 테이블에서 나타난다.
  • 레코드가 1건 미만일때만 나타나기 때문에 실제 서비스에서는 거의 볼수 없다.
EXPLAIN 
SELECT * FROM tb_dual

P.281 Dual MyISAM to InnoDB 표 참고

const
  • 테이블 레코드 수와 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 절을 가지고 있으며, 반드시 1건을 반환하는 방식을 const라고 한다.
  • 타 DBMS에서는 유니크 인덱스 스캔(Unique Index Scan)이라고도 표현한다.
  • 만약 다중 칼럼으로 구성된 프라이머리 키나 유니크 키가 있을때, 일부 컬럼만 조건으로 사용할때는 const방식을 사용할 수 없다.
  • 데이터를 읽지 않고는 실제로 1건인지 알 수가 없기 때문이다.
## 참고 ##
type이 const인 실행 계획은 옵티마이저가 쿼리를 최적화하는 단계에서 모두 상수화 한다.
그래서 type의 값이 const(상수)라고 표현되는 것이다.

SELECT COUNT(1) FROM employees e1 WHERE first_name=(SELECT first_name FROM employees e2 WHERE emp_no=10001);

To

SELECT COUNT(1) FROM employees e1 WHERE first_name='Jasminko';
eq_ref

P.284 상단 표 참고

  • 여러 테이블이 조인되는 쿼리에 표시된다.
  • 드라이빙 테이블의 값으로 드리븐 테이블의 프라이머리 키 혹은 유니크 키에 조인할때 표시된다.
  • 마찬가지로 다중 칼럼으로 구성된 키의 경우 칼럼 모두 equal 검색을 해야 eq_ref로 표시된다.
  • 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 방법이다.
ref
  • eq_ref와 달리 조인의 순서와 관계 없이 사용되며, 프라이머리 키 혹은 유니크 키에 대한 제약도 없다.
  • 인덱스의 종류와 관계없이 equal 검색을 할때 ref로 표시한다.
  • 레코드의 반환이 1건이라는 보장이 없어서 const, eq_ref보단 느리지만 equal 검색을 하기떄문에 빠른 방식중 하나이다.
// Primary(dept_no, emp_no)
EXPLAIN SELECT * FROM dept_emp WHERE dept_no='d005';

P.284 표 참고 -> 위 예제에서 프라이머리 키를 구성하는 칼럼중 일부만 equal 검색으로 명시됐기 때문에 일치하는 레코드가 1건이라는 보장이 없다. -> 따라서 type이 ref로 지정된다.

const, eq_ref, ref의 비교

  • const
  • 조인 순서에 관계없이 프라이머리 키나 유니크 키의 모든 칼럼에 대해 equal 검색(반드시 1건의 레코드 반환)
  • eq_ref
  • 조인에서 드라이빙 테이블의 칼럼값을 이용해서 드리븐 테이블의 프라이머리 키나 유니크 키로 equal 검색( 드리븐 테이블은 반드시 1건의 레코드 반환)
  • ref
  • 조인의 순서와 인덱스의 종류에 관계없이 equal 검색 (반드시 1건의 레코드 반환이라는 보장이 없어도 됨)

세가지 타입의 공통점은 where 절에 equal연산자로 검색을 한다는 것이다. 또한, 인덱스의 분포가 나쁘지 않다면 위 세 가지는 성능상의 문제를 일으키지 않는다.

## 참고 ##
equal연산자란 `=` 혹은 `<=>`를 뜻한다.
fulltext
  • 전문 검색 인덱스를 사용했을때 표시된다.
  • 전문 검색 인덱스의 경우 통계정보를 구성할 수 가 없기 때문에, 옵티마이저는 전문 검색 인덱스가 포함되어있는 경우 거의 전문 검색 인덱스로 레코드를 가져온다.
  • 단, const, eq_ref, ref 같은 접근 방법을 사용할 수 있음에도 강제로 전문 검색 인덱스를 사용하진 않는다.
EXPLAIN
SELECT * 
FROM employee_name
WHERE emp_no = 10001
AND emp_no BETWEEN 10001 AND 10005
AND MATCH(first_name, last_name) AGAINST('Facello' IN BOOLEAN MODE);

위 예제에서 emp_no = 10001이 조건에 있을때는 type이 const로 설정되지만, emp_no = 10001을 제거하게되면, type이 range가 아닌 fulltext로 잡힌다. fulltext의 경우 옵티마이저에서 우선순위가 높은 것을 알 수 있다. (하지만 이는 통계정보가 없어서 우선순위가 높게 된것이지, range보다 더 빠른지는 쿼리의 시간을 실제로 측정해봐야한다.

ref_or_null
  • ref와 같은 접근 방법에 null비교가 추가된 형태이다.
EXPLAIN
SELECT * FROM titles WHERE to_date='1985-03-01' OR to_date IS NULL;
unique_subquery
  • WHERE 조건절에서 사용될 수 있는 IN (subquery) 형태의 쿼리를 위한 접근 방식이다.
  • 서브 쿼리에서 유니크한 칼럼만 반환할때 사용할 수 있는 방법이다.
// Primary(dept_no, emp_no)
EXPLAIN
SELECT * FROM departments WHERE dept_no IN (
SELECT dept_no FROM dept_emp WHERE emp_no=10001
);

P.287 참고

index_subquery
  • IN (subquery)에서 서브 쿼리가 중복된 값을 반환 할 수도 있지만 인덱스를 이용해서 제거할 수 있을때 사용되는 방식이다.

unique_subquery 와 index_subquery의 차이

  • unique_subquery
  • IN (subquery) 형태의 조건에서 서브 쿼리의 반환 값에는 중복이 없으므로 중복제거 작업이 필요하지 않음
  • index_subquery
  • IN (subquery) 형태의 조건에서 서브 쿼리의 반환 값에는 중복이 있을 수 있지만 인덱스를 이용해 중복된 값을 제거할 수 있음.

둘다 중복된 값을 제거함에 있어서 비용이 크진 않다.

## 참고 ##
IN 연산자의 특성상, IN (subquery) 혹은 IN (상수) 형태의 조건은 괄호 안에 있는 목록에서 중복 값을 제거해야 한다.
range
  • range는 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미한다.
  • 우리가 익히 알고 있는 인덱스 레인지 스캔 형태의 방식이다.
  • 주로 >, <, IS NULL, BETWEEN, IN, LIKE같은 연산자에 사용된다.
  • 우선순위는 낮게 설정되어 있지만, 이 접근 방법도 빠르며 모든 쿼리가 range를 사용해도 성능상에는 크게 문제되지 않는다.
## 참고 ##
책(Real MySQL)에서 인덱스 레인지 스캔이라고 하면 const, ref, range라는 세가지 접근 방법을 모두 포함해서 말한다.
"인덱스를 효율적으로 사용한다", "범위 제한 조건으로 인덱스를 사용한다"도 포함한다.

업무상으로도 DBA와 개발자의 커뮤니케이션중 const, ref와 range를 구분해서 언급하지는 않는다.
index_merge
  • 두 개의 인덱스를 이용해 각각의 결과를 만든 후 그 결과를 병합하는 처리방식이다.
  • 이전에 나왔던 방식들과는 다르게 여러개의 키를 동시에 사용한다.

index_merge방식에는 4가지의 특징이 있다.

  1. 여러 인덱스를 읽어야 하므로 일반적인 range보다 느리다.
  2. AND와 OR 연산이 복잡하게 연결된 쿼리에서는 제대로 최적화하지 못한다.
  3. fulltext를 사용하는 쿼리에서는 index_merge가 적용되지 않는다.
  4. index_merge 접근 방식으로 처리된 결과는 항상 2개 이상의 잡합이 되기 때문에 부가적인작업이 필요하다.

index_merge방식의 경우 Extra칼럼을 통해 보완적인 내용이 추가된다. -> 나중에 나옴

EXPLAIN
SELECT * FROM employees
WHERE emp_no BETWEEN 10001 AND 11000
OR first_name='Smith';

P.290 참고

index
  • 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔 방식을 의미한다.
  • 이름이 index라서 사람들이 index를 잘 사용하고 있다고 오해한다..
  • index방식은 아래조건 가운데 두가지를 만족하면 사용되는 읽기 방식이다. (1,2, 혹은 1,3)
  1. range나 const, ref와 같은 접근 방식으로 인덱스를 사용하지 못하는 경우
  2. 인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우 (데이터 파일을 읽지 않아도 되는 경우)
  3. 인덱스를 이용해 정렬이나 그룹핑 작업이 가능한 경우 (별도의 정렬 작업을 피할 수 있는 경우)
EXPLAIN
SELECT * FROM departments ORDER BY dept_name DESC LIMIT 10;

P.291 참고

ALL
  • 테이블 풀 스캔을 방식을 의미한다.
  • 테이블을 처음부터 끝까지 읽고 불필요한레코드를 제거하고 반환한다.
  • 앞선 방식들보다 가장 마지막에 선택되는 비효율적인 방식이다.
  • ALL과 index방식은 작업 범위를 제한하는 조건이 아니므로 빠른 응답이 필수인 서비스에는 어울리지 않는다.

쿼리 튜닝이 강제로 인덱스를 사용하도록 하는 것이 아님을 기억하자 대용량 데이터를 처리하는 프로그램의 경우 index나 ALL방식이 더 빠르게 동작할 수도 있다.

## 참고 ##
InnoDB에서는 인덱스 풀 스캔 혹은 테이블 풀 스캔같은 대량의 디스크 I/O를 유발하는 작업을 위해 한번에 많은 페이지를 읽어오는 기능을 제공한다.
연속적으로 인접한 페이지가 읽히게 되면 백그라운드에서 자동으로 읽기쓰레드가 한 번에 최대 64개 페이지씩 읽어들인다.
이를 리드 어헤드(Read Ahead)라고 한다

possible_keys

  • 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방식에서 사용되는 인덱스의 목록이다.
  • 조건에 걸린 키들이 모두 나열되어있는 경우가 많기 때문에 튜닝에 도움되지 않는다.

key

  • 옵티마이저에 의해 선택된 최종 실행 계획에 사용되는 인덱스를 의미한다.
  • 쿼리를 튜닝할때 key에 선택된 칼럼이 의도한 인덱스가 표시되는지 확인하는 것이 중요하다.
  • key에 PRIMARY라고 표시되면 프라이머리 키를 사용한다는 의미이며, 나머지는 인덱스의 이름으로 표시된다.

레코드에 접근하는 방식들(type)중에 index_merge를 제외한 방식들은 key가 한개다. index_merge의 경우 키를 여러개가질 수 있다. 그리고 ALL과 같이 인덱스를 전혀 사용하지 못하면 NULL로 표기된다.

## 참고 ##
MySQL에서 프라이머리 키는 별도의 이름을 부여할 수 없다.
기본적으로 PRIMARY라는 이름을 가진다.

key_len

  • 쿼리를 처리하기 위해 사용하는 키값의 크기를 알려준다. (다중칼럼이라면 칼럼들의 크기의 합으로 보여준다.)
  • 사람들이 쉽게 무시하는 정보지만 실제로는 매우 중요한 정보중 하나이다.
  • 키를 사용하기 위한 메모리를 할당할때는 키의 최대 가용크기로 계산한다.
  • 예를 들어. utf8에서는 1개의 문자가 1바이트~3바이트를 가질 수 있다.
  • 이때 메모리를 할당할때는 3바이트를 할당한다.

MySQL의 버전에 따라서 key_len이 보이는 방식이 다르다.

EXPLAIN
SELECT * FROM dept_emp WHERE dept_no='d005' AND emp_no <> 10001;

// <> == !=

  • MySQL 5.0이하 (P.294 표 참고)

  • 실제 사용되는 인덱스의 길이만 표기되기때문에 dept_no의 크기인 12바이트가 표기된다.

  • 여기서 실제 사용되는 인덱스란 범위 제한 조건으로 사용되는 인덱스를 의미한다.

  • MySQL 5.1이상 (P.295 표 참고)

  • 범위 제한 조건뿐 아니라 체크 조건의 길이까지 함께 포함하기 때문에 16바이트로 표기된다.

  • type 칼럼의 값이 ref에서 range로 변경됐다.

  • 체크 조건임에도 불구하고 범위 조건에 포함하여 계산했다.

  • 결과적으로 MySQL 5.1이상에서는 범위 제한 조건으로 몇바이트까지 사용됐는지 정확한 수치를 알수가 없다.

MySQL 5.0에는 범위 제한 조건만 스토리지엔진으로 전달한 반면 MySQL 5.1부터 인덱스를 이용할 수만 있으면 전부다 스토리지엔진으로 넘기도록 바뀌었다. 이를 “컨디션 푸시 다운”이라고 하는데 나중에 나온다.

ref

  • type이 ref 방식이면 참조 조건으로 어떤 값이 제공 됐는지 보여준다.
  • 상수를 지정했다면 const
  • 다른 테이블의 칼럼값이면 {table_name}.{column_name}으로 표시된다.
  • 내용이 크게 중요하진 않지만 func로 나오는 경우에만 주의하면 된다.
EXPLAIN 
SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no=(de.emp_no - 1);

P.296 참고

  • 사용자가 명시적으로 값을 변환하지 않아도, MySQL에서 내부적으로 값을 변환해야 할떄 func로 표기하는 경우가 있다.
  • 예시
  • 문자 집합이 일치하지 않는 두 문자열 칼럼을 조인한다.
  • 숫자 타입의 칼럼과 문자열 타입의 칼럼으로 조인한다.
  • 서버에서 변환하지 않도록 타입은 맞춰주는것이 좋다.

rows

  • 옵티마이저에서 처리 방식을 나열하고, 각 처리방식의 비용을 비교해서 하나의 실행계획을 수립한다.
  • 비용을 계산할때 각 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지 예측해보는 것이다.
  • 실행 계획에서 rows 칼럼의 값은 옵티마이저가 실행 계획의 비용을 판단하기위해 예측했던 레코드의 건수다.
  • 쿼리의 반환값이 아니다.
  • LIMIT가 포함된 쿼리의 겨우 rows의 오차가 심해서 도움이 되지 않는다.
EXPLAIN
SELECT * FROM dept_emp WHERE from_date >= '1985-01-01';
-> 전체 로우의 대부분을 스캔해야 하므로 ALL타입으로 지정됨

EXPLAIN
SELECT * FROM dept_emp WHERE from_date >= '2002-07-01';
-> 전체 로우중 지극히 일부만 스캔해도 되기때문에 range타입으로 지정함.
-> 또한, ix_from_date를 사용하기 떄문에 key와 key_len에 값이 명시된다.

P.297, P.298 참고

코멘트

  • type칼럼의 index_subquery의 경우 인덱스를 통해 중복된 값을 제거한다는데 그 의미가 무엇일까.
  • InnoDB에서 ANALYZE돌릴때 읽기락까지 걸리는 이유는 무엇일까
  • DEPENDENT에서 말하는 외부의 영향은 어떤게 있을까
  • UNION의 경우 임시테이블로 만들어두는데 Cache의 유무가 의미가 있는 것 인가?
  • LIMIT가 있으면 왜 rows의 오차가 심한이유가 뭘까.