SELECT 및 정렬 관련 상태 변수

SELECT 및 정렬 관련 상태 변수

• Select_scan
• Select_range
• Select_full_join
• Select_range_check
• Select_full_range_join
• Sort_scan
• Sort_range
• Sort_merge_passes
• Sort_rows

특별히, Select_scan 와 Select_range는 단일 테이블 쿼리 또는 여러 테이블 조인 쿼리에서 첫번째 테이블 접근과 관련된다.

Select_full_join, Select_range_check, 그리고 Select_full_range_join 은 여러 테이블의 조인에서 두번째 또는 뒤따르는 테이블 접근과 관련된다.

Sort 변수는 레코드 정렬과 관련한다.

언제 이 상태값들이 증가하는가 ?

단일 테이블 쿼리에서 유일하게 존재하는 테이블 또는 다중 테이블 쿼리의 실행 계획상 제일 상위에 표시되는 테이블을 드라이빙(Driving) 테이블이라고 한다.

Select_scan 와 Select_range 는 오로지 드라이빙(Driving) 테이블에만 적용된다.

다중 테이블 쿼리에서 Select_full_join, Select_range_check, 그리고 Select_full_range_join 는 두번째 테이블과 뒤따르는 테이블에만 적용된다.

Sort_scan, Sort_range, Sort_merge_passes, and Sort_rows apply to any query that uses ORDER BY or GROUP BY, regardless of how many tables.

Sort_scan, Sort_range, Sort_merge_passes, 그리고 Sort_rows 는 테이블의 수와 관계없이 ORDER BY 또는 GROUP BY를 사용하는 쿼리에 적용된다.

For example:

   mysql> EXPLAIN SELECT * FROM tbl2, tbl1 WHERE tbl1.col1 = tbl2.col2 ORDER BY tbl1.col2;

   +-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |

   +-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

   | SIMPLE      | tbl2  | ALL  | NULL          | NULL | NULL    | NULL |   18 | Using temporary; Using filesort |

   | SIMPLE      | tbl1  | ALL  | NULL          | NULL | NULL    | NULL |   27 | Using where                     |

   +-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

위의 조인 계획에서 tbl2는 드라이빙(Driving) 테이블이고 tbl1은 드라이브드(Drived) 테이블이 된다.

또한 이 쿼리는 ORDER BY를 사용하고 있으므로 Sort 변수에도 어느정도 영향을 미칠 것이다.

두 테이블 모두 동일하게 테이블 스캔을 유발하지만, tbl2는 Select_scan에 영향을 미치고, tbl1는 Select_full_join에 영향을 미칠 것이다.

즉, 9개의 Select 와 Sort 의 변수에 아래와 같이 적용된다.

1. 드라이빙(Driving) 테이블 또는 단일 테이블 : Select_scan 과 Select_range

2. 두번째 테이블 또는 뒤따르는 테이블들 : Select_full_join, Select_range_check, 그리고 Select_full_range_join

3. ORDER BY 와 GROUP BY를 사용하는 쿼리 : Sort_scan, Sort_range, Sort_merge_passes, 그리고 Sort_rows

Select_scan

Select_scan 은 디스크로부터 완전히 순서대로 테이블이 읽히는 것을 의미한다.

이런 경우에 실행 결과의 "type" 컬럼이 "ALL"로 표시된다.

디스크는 일반적으로 느리기 때문에 테이블 스캔은 바람직하지 않다.

Select_range

Select_range 는 조건을 만족하는 레코드를 찾기 위해서 디스크로부터 필요한 제한된 범위만 읽었음을 의미한다.

이러한 경우 실행 계획의 "type" 컬럼에는 range라고 표시된다.

MySQL은 읽어야 할 데이터의 위치를 인덱스를 통해서 알게되며, 이는 상당히 디스크의 검색 시간을 줄여준다.

그래서, Select_range는 Select_scan에 비해서 상당히 빠르게 처리된다.

SELECT * FROM tbl1 WHERE col1 BETWEEN 5 AND 13;

SELECT * FROM tbl1 WHERE col1 > 5 AND col1 < 13;

위와 같은 쿼리는 col1인 인덱싱이 되어 있으면 MySQL은 실행 계획상에 range라고 표시하며, 인덱스가 없으면 테이블 스캔이 유발될 것이다.

Select_full_join

Select_full_join 은 Select_scan과 동일하지만, 다중 테이블 조인에서 두번째 테이블과 뒤따르는 테이블에만 적용된다는 것이 다르다.

이러한 경우 실행 계획의 "type" 컬럼에 "ALL" 로 표시된다.

Select_full_join 은 테이블을 조인을 위해서 적절히 사용될 수 있는 인덱스가 없을 때 발생한다.

SELECT * FROM tbl1, tbl2 WHERE tbl1.col1 = tbl2.col1;

위와 같은 쿼리에서 아무 인덱스가 없다면, 드라이빙(Driving) 테이블은 Select_scan을 드라이브드(Drived) 테이블은 Select_full_join을 증가시키게 된다.

Select_full_join 은 Select_scan보다 더 나쁜 방식으로 처리되며, 이 둘이 동시에 증가하면 더 안좋은 상황이다.

실행 계획에서 조인의 각 테이블이 "ALL"이라면, 이는 모든 테이블의 카테시안 곱을 만들어 내고 있는 것을 의미한다.

일반적으로 실제 서비스되는 어플리케이션에서는 최소 1000건 이상의 레코드를 가지며, 카테시안 곱은 상당히 많은 결과를 만들어내게 된다.

Select_range_check

Select_range_check는 Select_range와 동일한 범위에 대해서 비교하는 방식을 사용하므로 Select_full_join 보다는 조금 나은 실행 방식이다.

Select_range_check 은 테이블을 조인하기 위해서 범위를 사용할 수 있을지 보장하지 못하기 때문에 조인 대상 레코드를 찾으면 계속 정확히 매치된 레코드인지 비교를 해야 하는 것이 다른 점이다.

Select_range에서 단지 하나의 테이블만 있다면 MySQL은 조인을 실행해보기 전에 확신할 수 있다.

다중 테이블에서는 앞의 테이블이 범위 조건을 변경할 수도 있기 때문에 MySQL은 조인을 실행해보기 전에 확신할 수 없게 된다.

이러한 실행 계획에는 "type" 컬럼에 여전히 "ALL"이라고 표시되는데, MySQL이 range 형태를 적용할 수 있을 지 알수 없기 때문이다.

또한 이런 경우에는 실행 계획의 "Extra" 컬럼에 "Range checked for each record (index map: #)" 라는 내용이 표시된다.

Select_range과 같이 이 최적화를 위해서는 이 테이블에 적어도 하나의 인덱스가 필요하며, 그렇지 않으면 Select_full_join을 유발하게 될 것이다.

SELECT * FROM tbl1, tbl2 WHERE tbl1.col1 > tbl2.col1;

위의 쿼리에서 tbl2.col1 컬럼에 인덱스가 생성되어 있아면 MySQL은 Select_range_check를 증가시킬 것이다.

   mysql> EXPLAIN SELECT * FROM tbl1, tbl2 WHERE tbl1.col1 > tbl2.col1;

   +-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+

   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                          |

   +-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+

   | SIMPLE      | tbl1  | ALL  | NULL          | NULL | NULL    | NULL |   27 |                                                |

   | SIMPLE      | tbl2  | ALL  | col1          | NULL | NULL    | NULL |   18 | Range checked for each record (index map: 0x1) |

   +-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+

이 간단한 쿼리에서 MySQL은 tbl1 테이블의 각 레코드에 대해서 tbl2.col1 인덱스를 27번 정도 검사할 것이다.

tbl1.col1 의 각 값에 대해서 MySQL은 tbl2.col1 < tbl1.col1 조건을 만족하는 tbl2의 레코드만을 읽을 것이다.

MySQL이 이 테이블의 조인에 range를 사용한다 하더라도 Select_range를 증가시키지 않고 여전히 Select_range_check를 증가시킬 것이다.

Select_full_range_join

Select_full_range_join 는 MySQL이 이 테이블에 대해서 range를 사용할 수 있다는 것을 확신하는 것을 제외하고는 Select_range_check와 동일한다.

이 경우 실행 계획의 type은 range로 표시되며, Select_range와 Select_full_range_join과 같이 인덱스가 필요하고, Select_full_join 보다 상당히 빠르며 Select_range_check보다는 빠르다.

Select_range에 적용되는 범위 최적화 방식이 적용되어 테이블이 조인된다.

   mysql> EXPLAIN SELECT * FROM tbl1, tbl2 WHERE tbl1.col1 = 10 AND tbl2.col1 > 13;

   +-------------+-------+-------+---------------+------+---------+-------+------+-------------+

   | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra       |

   +-------------+-------+-------+---------------+------+---------+-------+------+-------------+

   | SIMPLE      | tbl1  | ref   | col1          | col1 | 5       | const |    1 | Using where |

   | SIMPLE      | tbl2  | range | col1          | col1 | 5       | NULL  |    1 | Using where |

   +-------------+-------+-------+---------------+------+---------+-------+------+-------------+

범위 조건이 변하지 않기 때문에 MySQL은 tbl2의 레코드를 읽기 위해서 tbl2.col1 인덱스의 이용을 확신할 수 있다. 그리고 이 결과가 tbl1에 조인한다.

Sort_scan 와 Sort_range

ORDER BY 또는 GROUP BY (GROUP BY ... ORDER BY NULL 제외)를 이용하여 레코드를 정렬하는 쿼리는 아래와 같은 3가지 단계를 거쳐서 실행된다.

1. WHERE 조건에 기반해서 레코드 검색

2. 검색된 레코드 정렬

3. 정렬된 순서대로 레코드 읽기

데이터는 임의의 순서대로 저장되어 있으며, 쿼리는 리턴되어야 할 순서대로 레코드를 찾지 않을 것이기 때문에 위의 3가지 단계는 논리적으로 당연한 절차들이다.

두번째 스텝을 건너뛸수 없는 다면, 세번째 단계에 의해서 Sort_scan과 Sort_range는 영향을 받게 된다.

만약, 첫번째 단계가 Select_scan이면 세번째 단계는 Sort_scan을 증가시킬 것이며,

만약, 첫번째 단계가 Select_range이면, 세번째 단계는 Sort_range가 될 것이다.

기본적으로 Sort_scan과 Sort_range는 기능적인 차이는 없으며, 두 경우 모두 필요한 레코드만 정렬된 순서대로 읽히게 된다.

그래서, Sort_scan 과 Sort_range의 성능적 영향은 차이가 없으며, 두 가지 경우 성능은 일반적으로 눈에 띄지 않는다.

Sort_merge_passes

Sort_merge_passes 는 정렬 처리의 두번 째 단계에 관여한다.

우선 MySQL은 모든 레코드를 메모리에서 정렬하려고 시도할 것이며, sort_buffer_size 설정에 의해서 메모리상에서의 정렬 레코드 수가 제한된다.

sort_buffer_size가 모든 레코드의 사이즈보다 크지 않으면, MySQL은 디스크에 임시 파일을 만들어서 정렬된 레코드를 저장한다. 그러나 첫번째 단계에서 모든 레코드가 검색된 이후 임시 파일도 정렬되어야 한다.

임시 파일의 재 정렬은 Sort_merge_passes에 카운터된다.

사실 MySQL은 보조 임시 파일을 만들어서 첫번째 파일의 정렬된 내용을 저장한다.

그래서, 거의 대부분 정확히 Sort_merge_passes 값의 2배 만큼 임시 파일이 생성되는 것이 일반적이다.

성능상의 영향도는 일반적으로 눈에 띄지 않지만 기술적으로 이는 느린 처리이다.

sort_buffer_size를 증가시키면 Sort_merge_passes의 회수와 임시 파일의 생성 회수를 줄일 수 있다.

Sort_rows

Sort_rows 는 단순히 두번째 단계에서 정렬된 레코드의 수를 카운트한다.

어떤 경우에는 두번째 단계는 건너 띌수 있기 때문에, Sort_rows 는 모든 경우 처리된 레코드 수를 표현하는 것은 아니다.

또한, 두번째 단계의 Sort_scan과 Sort_range는 기본적으로 다르지 않기 때문에, Sort_rows 값이 특별히 뭔가를 시사하진 않는다.