통계정보(2) - Type편

이어서 계속합시다.

type

MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타냅니다. 대표적인 방식으로는 인덱스를 사용해서 레코드를 읽는 방법과, 테이블 처음부터 끝까지 읽는 Full Table Scan 방식이 있습니다. 쿼리를 튜닝할 때 필수적으로 체크해야 하는 정보입니다.

 

대분류	소분류	설명
인덱스를 사용해 스캔	system	단 하나의 레코드를 가지는 테이블을 참조하는 형태의 접근 방법
	const	쿼리가 PK나 UK를 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환
	eq_ref	조인에서 첫 번째 읽은 테이블의 컬럼값을 이용해 두 번째 테이블을 PK나 UK로 동등조건 검색 (두번째 테이블은 반드시 1건의 레코드만 반환)
	ref	조인의 순서나 인덱스 종류에 상관없이 동등 조건으로 검색 (eq_ref처럼 1건의 레코드만 반환된다는 보장 없어도 됨)
	fulltext	전문 검색 인덱스(MATCH, AGAINST)를 사용하는 경우 비용 따지지 않고 사용
	ref_or_null	ref방식에서 null 비교연산이 추가
	unique_subquery	IN 서브쿼리에 대해 eq_ref 대체 (중복되지 않은 유니크한 값만 반환)
	index_subquery	unique_subquery와 기능은 같고, (유일하지 않은 값(=중복 가능한 값)들 반환)
	range	인덱스를 하나의 값이 아닌 범위로 설정해서 검색
	index_merge
	index
풀 테이블 스캔	ALL	인덱스를 사용하지 않고 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔 접근 방식 사용

1. system

The table has only one row (= system table). This is a special case of the const join type.
"하나의 행(= 시스템 테이블)만을 가지는 테이블을 의미합니다. 이것은 const 조인 타입의 특별한 경우입니다."

레코드가 단 하나만 존재하는 테이블을 참조하는 방식으로, InnoDB을 제외한 MyISAM, MEMORY 테이블에서만 사용되는 접근 방식입니다.

이 타입을 사용하는 테이블을 InnoDB 테이블로 변환하면, ALL 또는 index로 표시될 가능성이 큽니다.

레코드 건수에서 보다시피 실제 애플리케이션에서는 거의 보이지 않는 방식입니다.

 

 

2. const

The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. 
const tables are very fast because they are read only once.
"테이블에는 쿼리 시작 시 읽히는 일치하는 행이 최대 하나 있습니다. 행이 하나만 있기 때문에(Unique) 이 행의 열 값은 나머지 최적화 프로그램에서 상수로 간주될 수 있습니다. const 테이블은 한 번만 읽기 때문에 매우 빠릅니다."

레코드의 건수에 관계없이 쿼리가 Primary 키나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리방식입니다. 다른 DBMS에서는 '유니크 인덱스 스캔'이라고 합니다.

-- 기본 형태
SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;
  

-- where 절에 unique key를 사용해야 한다.
select * from user 
where id = 5


-- unique key를 사용하지 않는 경우에는 const 명시 X
select * from user 
where account = 'TestUser1'

 

그림1. const type

 

그림2. unique키 사용 안한 경우

단 하나의 값만 있기 때문에 각각의 값은 상수로 처리될 수 있습니다. 이를 이용해 옵티마이저가 쿼리를 최적화하는 단계에서 모든 컬럼 값들을 상수화 시켜 쿼리 실행기로 전달하기 때문에 type 값이 const로 표기됩니다.

단 한번만 읽기 때문에 매우 빠릅니다.

 

 

 

3. eq_ref

One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.

eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses columns from tables that are read before this table. In the following examples, MySQL can use an eq_ref join to process ref_table:

system 및 const 유형 외에 가능한 최상의 조인 유형입니다. 

인덱스의 모든 부분이 조인에서 사용되고 인덱스가 PRIMARY KEY 또는 UNIQUE NOT NULL 인덱스인 경우에 사용됩니다. 

 

여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시됩니다. 조인에서 처음 읽은 테이블 칼럼 값을, 그다음 읽어야 할 테이블의 PK나 유니크 키 칼럼의 검색 조건에 사용할 때를 eq_ref라고 합니다. 이때 두 번째 이후에 읽는 테이블의 type 칼럼에 eq_ref가 표기됩니다. 비교 값은 상수 또는 이 테이블보다 먼저 읽은 테이블의 열을 사용하는 표현식일 수 있습니다.

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

 

예시

그림3. eq_ref

이처럼 eq_ref는 '=' 연산자를 사용하여 비교되는 인덱싱 된 열에 사용할 수 있습니다.

조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근방법입니다.

 

 

 

4. ref

All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.

ref can be used for indexed columns that are compared using the = or <=> operator. In the following examples, MySQL can use a ref join to process ref_table:

eq_ref와 달리 조인의 순서와 관계없이 사용되며, PK나 UK 등의 제약조건도 없습니다. 인덱스의 종류와 관계없이 동등(equal) 조건으로 검색할 때는 ref 접근 방법이 사용됩니다. ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref 보다는 빠르지 않습니다. 하지만 동등 조건으로 비교되기 때문에 매우 빠른 조회 방법 중 하나입니다.

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

 

 

5. fulltext

The join is performed using a FULLTEXT index.

전문 검색(fulltext) 인덱스를 사용해 레코드를 읽는 접근 방식입니다. 우선순위가 상당히 높은 방식으로 전문 검색 인덱스를 사용할 수 있는 SQL에서는 비용이 어찌됬든 옵티마이저가 거의 매번 이 접근 방식을 사용합니다. const나 eq_ref 또는 ref 처럼 훨씬 빠른 접근 방법을 사용할 수 있는 경우에는 억지로 fulltext를 선택하지 않습니다.

 

전문 검색 조건(MATCH, AGAINST)같은 경우에는 주저 없이 fulltext를 사용합니다. 하지만 일반 검색 조건을 사용하는 range 접근 방식이 더 빨리 처리되는 경우도 있으니, 성능 확인을 해보시기 바랍니다.

 

 

 

6. ref_or_null

This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values.
This join type optimization is used most often in resolving subqueries. In the following examples, MySQL can use a ref_or_null join to process ref_table

ref 접근 방식과 같지만, NULL 비교가 추가된 형태입니다.

자주 사용되지는 않습니다.

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

Section 8.2.1.15, “IS NULL Optimization”.

 

 

7. index_merge

This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains a list of indexes used, and key_len contains a list of the longest key parts for the indexes used.

이 조인 유형은 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 그 결과를 병합하는 인덱스 병합 최적화가 사용됩니다. 이름만 들어서는 좋은 처리방법인듯 하나 단점이 몇몇 존재합니다.

• 여러 인덱스(범위가 아님)를 읽어야 하기 때문에, range 방식보다 효율성이 떨어짐.
• AND와 OR 연산이 복잡한 쿼리에서는 최적화가 쉽지 않음.
• 전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않음
• Index_merge 접근 방식으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에, 이를 합치거나, 중복을 제거하거나 등 후처리가 추가적으로 필요함.

이러한 단점 때문에, 접근 우선순위는 7로 중위권이나 저평가되고 있습니다.

추가적으로 key_len에는 사용된 인덱스에 대해 가장 긴 키 부분 목록이 포함됩니다.

주로 'OR' 연산에 사용이 잘 됩니다.

 

Section 8.2.1.3, “Index Merge Optimization”.

 

 

8. unique_subquery

This type replaces eq_ref for some IN subqueries of the following form:
unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.

where 조건절에서 사용될 수 있는 IN 형태의 쿼리를 위한 접근 방식입니다. (in 서브 쿼리에 대해 eq_ref 대체)

서브 쿼리에서 중복되지 않은 유니크한 값만 반환할 때 사용합니다.

value IN (SELECT primary_key FROM single_table WHERE some_expr)

효율성을 높이기 위해서브 쿼리를 완전히 대체하는 인덱스 조회 기능입니다.

 

 

 

9. index_subquery

This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form:

unique_subquery와 유사합니다. 

중복되는 값을 반환할 수 있기 때문에, 중복된 값을 인덱스를 이용해 제거할 수 있을 때 이 접근방식을 사용합니다.

value IN (SELECT key_column FROM single_table WHERE some_expr)

중복작업을 반드시 수행해야 합니다!

 

 

 

10. range

Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type.
range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators:

인덱스 레인지 스캔 형태의 접근방법입니다.  인덱스를 하나의 값이 아니라 범위로 설정해 지정된 범위에 있는 행만 검색됩니다. 출력 행의 키 열은 사용되는 인덱스를 나타냅니다. key_len은 사용된 가장 긴 키 부분을 포함합니다. 이 유형의 참조 열은 NULL입니다.

SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

범위는 [=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE 또는 IN() 연산자]를 사용하여 키 열을 상수와 비교할 때 사용할 수 있습니다.

애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법입니다.(우선순위 자체는 낮은 편이나, 상당히 빠름)

 

 

 

11. index

The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:
If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.

A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.

MySQL can use this join type when the query uses only columns that are part of a single index.

여기서 index 방식은 비효율적이라고 불리는 인덱스 풀 스캔을 의미하기 때문에 인덱스 트리가 스캔된다는 점을 제외하고 동작 방식은 ALL과 동일합니다. ('index'나 'All'이나 비교 레코드 건수도 같지만, 인덱스 자체 크기가 데이터 파일 전체보다 크기가 작아 풀 테이블 스캔(ALL) 보다 효율적이라고 말하는 것.)

 

세 가지 조건 중 (1+2, 1+3) 조건을 만족해야 수행됩니다.

 

1. range나 const 또는 ref와 같은 접근 방식으로 인덱스를 사용하지 못하는 경우

  - 쿼리가 단일 인덱스의 일부인 열만 사용(인덱스가 쿼리에 대한 커버링 인덱스가 아님)할 때 사용.

 

2. 인덱스에 포함된 행만으로 처리할 수 있는 쿼리인 경우 (= 데이터 파일을 읽지 않아도 되는 경우)

  - 인덱스가 쿼리에 대한 커버링 인덱스이고 테이블에서 필요한 모든 데이터를 충족하는 데 사용할 수 있는 경우를 뜻함

 

3. 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우 (별도의 정렬 작업을 피할 수 있는 경우)

 

전체 테이블 스캔은 인덱스에서 데이터 행을 인덱스 순서로 조회하는 읽기를 사용하여 수행됩니다.

이때 Extra 열에 Using index가 나타나지 않습니다.

 

12. All

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

이전 테이블의 각 행 조합에 대해 전체 테이블 스캔이 수행됩니다. 첫 번째 테이블은 const로 표시되어야 합니다. 일반적으로 이전 테이블의 상수 값 또는 열(row) 값을 기반으로 테이블에서 행 검색을 활성화하는 인덱스를 추가하여 ALL을 방지할 수 있습니다.

 

 

참고

MySQL :: MySQL 8.0 Reference Manual :: 8.8.2 EXPLAIN Output Format

MySQL :: MySQL 8.0 Reference Manual :: 8.8.2 EXPLAIN Output Format