4.3.10 Extra 컬럼(1)

쿼리의 실행계획에서 성능에 관련된 중요한 내용이 Extra컬럼에 자주 표시됩니다. 

 

const row not found

쿼리의 실행계획에서 const 접근 방식으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않으면 표시됩니다. 이런 메세지가 표시되는 경우에는 테이블에 적절히 테스트용 데이터를 저장하고 다시 한번 쿼리의 실행 계획을 확인해 보는 것이 좋습니다.

 

Distinct

MariaDB [employees]> explain
    -> select distinct d.dept_no from departments d,dept_emp de where de.dept_no=d.dept_no;
+------+-------------+-------+-------+---------------+---------+---------+---------------------+-------+------------------------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref                 | rows  | Extra                        |
+------+-------------+-------+-------+---------------+---------+---------+---------------------+-------+------------------------------+
|    1 | SIMPLE      | d     | index | PRIMARY       | PRIMARY | 12      | NULL                | 9     | Using index; Using temporary |
|    1 | SIMPLE      | de    | ref   | PRIMARY       | PRIMARY | 12      | employees.d.dept_no | 20879 | Using index; Distinct        |
+------+-------------+-------+-------+---------------+---------+---------+---------------------+-------+------------------------------+
2 rows in set (0.012 sec)

위 쿼리에서 실제로 조회하려는 값은 dept_no인데 departments 테이블과 dept_emp 테이블에 모두 존재하는 dept_no만 중복없이 유니크하게 가져오기 위한 쿼리입니다.

 

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이면 풀 테이블 스캔을 해야만 결과를 알아낼 수 있습니다. 해당 키워드는 MariaDB가 쿼리를 실행하는 중 col1이 NULL을 만나면 예비책으로 풀 테이블 스캔을 사용할 것이라는 사실을 알려주는 키워드입니다.

 

Impossible HAVING

쿼리에 사용된 HAVING 절의 조건을 만족하는 레코드가 없을 때 실행 계획의 Extra 칼럼에는 해당 키워드가 표시 됩니다.

쿼리 중 Extra 컬럼에 Impossible HAVING메시지가 출력된다면 쿼리가 제대로 작성되지 못한 경우가 대부분이므로 쿼리의 내용을 다시 점검하는 것이 좋습니다.

 

Impossible WHERE

Impossible HAVING과 비슷하며 WHERE 조건이 항상 FALSE가 될 수 밖에 없는 경우 표시됩니다.

 

Impossible WHERE noticed after reading const tables

Impossible WHERE의 경우 실제 데이터를 읽어보지 않고도 바로 테이블의 구조상으로 불가능한 조건이라고 판단할 수 있었지만 아래 쿼리는 어떤지 확인하겠습니다. 

MariaDB [employees]> explain
    -> select * from employees where emp_no=0;
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.001 sec)

이 쿼리는 실제로 싱핼되지 않으면 emp_no=0인 레코드가 있는지 없는지 판단할 수 없습니다. 그런데 이 쿼리으 ㅣ실행 계획만 확인했을 뿐인데, 옵티마이저는 사번이 0인 사원이 없다는 것까지 확인한 것입니다.

이를 토대로 MariaDB가 실행 계획을 만드는 과정에서 쿼리의 일부분을 실행해 본다는 사실을 알 수 있습니다.

쿼리에서 const 접근 방식이 필요한 부분은 실행 계획 수립단계에서 옵티마이저가 직접 쿼리의 일부를 실행하고, 실행된 결과 값을 원본 쿼리의 상수로 대체합니다.

 

No matching min/max row

쿼리의 WHERE 조건절을 만족하는 레코드가 한 건도 없는 경우 일반적으로 "Impossible WHERE"문장이 표시됩니다. 만약 MIN()이나 MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때 표시됩니다.

MariaDB [employees]> explain
    -> select min(dept_no),max(dept_no) from dept_emp where dept_no='';
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                   |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No matching min/max row |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------+

 

no matching row in const table

다음 쿼리와 같이 조인에 사용된 테이블에서 const 방식으로 접근할 때 일치하는 레코드가 없다면 표시됩니다.

MariaDB [employees]> explain
    -> select * from dept_emp de,(select emp_no from employees where emp_no=0) tb1 where tb1.emp_no=de.emp_no and de.dept_no='d005';
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.002 sec)

이 메세지 또한 "Impossible WHERE ..."와 같은 종류로 실행계획을 만들기 위한 기초 자료가 없음을 의미합니다.

 

No tables used

FROM 절이 없는 쿼리 문장이나 "FROM DUAL"형태의 쿼리 실행 계획에서 해당 메시지가 출력됩니다.

다른 DBMS와 다르게 FROM절이 없는 쿼리도 허용되는데 이때 표시됩니다.

MariaDB [employees]> explain select 1;
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.002 sec)

 

Not exists

A테이블에는 존재하지만 B테이블에는 없는 값을 조회해야 하는 쿼리가 사용될 때, NOT IN(subquery) 형태나 NOT EXISTS 연산자를 주로 이용합니다. 이러한 형태의 조인을 안티-조인(Anti-JOIN)이라고 합니다. 똑같은 처리를 아우터 조인을 이용해서도 구현할 수 있습니다.

MariaDB [employees]> explain
    -> select * from dept_emp de
    -> left join departments d on de.dept_no=d.dept_no
    -> where d.dept_no is null;
+------+-------------+-------+--------+---------------+---------+---------+----------------------+--------+-------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows   | Extra                   |
+------+-------------+-------+--------+---------------+---------+---------+----------------------+--------+-------------------------+
|    1 | SIMPLE      | de    | ALL    | NULL          | NULL    | NULL    | NULL                 | 334064 |                         |
|    1 | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY | 12      | employees.de.dept_no | 1      | Using where; Not exists |
+------+-------------+-------+--------+---------------+---------+---------+----------------------+--------+-------------------------+
2 rows in set (0.001 sec)

이렇게 아우터 조인을 이용해 안티-조인 쿼리를 수행할 때 Not exists 메시지가 표시됩니다. 이 쿼리를 NOT EXISTS 형태로 변환해서 처리했음을 의미합니다.

 

Range checked for each record(index map: N)

두 개의 테이블을 조인하는 쿼리가 있을 때, 조인조건에 둘다 변수(a.emp_no>=b.emp_no)로 조인하는 경우, b.emp_no가 작으면 풀 테이블 스캔으로 접근하고 b.emp_no가 클 때는 인덱스 레인지 스캔으로 접근하는 형태를 수행하게 됩니다. 즉, " 매 레코드마다 인덱스 레인지 스캔을 체크한다"고 할 수 있습니다.

 

Scanned N databases

INFORMATION_SCHEMA DB를 조회할 때 개선된 성능으로 사용할 수 있습니다. 그때 해당 메시지가 표시됩니다.

0 : 특정 테이블의 정보만 요청되어 데이터베이스 전체의 메타 정보를 읽지 않음

1 : 특정 데이터베이스내의 모든 스키마 정보가 요청되어 해당 데이터베이스의 모든 스키마 정보를 읽음

NULL : MariaDB 서버 내의 모든 스키마 정보를 다 읽음

 

Select tables optimized away

MIN()또는 MAX()만 SELECT절에 사용되거나 또는 GROUP BY로 MIN(),MAX()를 조회하는 쿼리가 적절한 인덱스를 사용할 수 없을 때 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용된다면 해당 메시지가 표시됩니다.

 

Skip_open_table,Open_table,Open_frm_only,Open_trigger_only,Open_full_table

Scanned N databases와 같이 INFORMATION_SCHEMA DB의 메타 정보를 조회할때 표시되는 내용입니다.

Skip_open_table : 테이블의 메타 정보가 저장된 파일을 별도로 읽을 필요가 없음

Open_frm_only :  테이블의 메타 정보가 저장된 파일만 열어서 읽음

Open_trigger_only : 트리거 정보가 저장된 파일만 열어서 읽음

Open_full_table : 최적화되지 못해서 테이블 메타정보파일과 데이터 및 인덱스파일까지 모두 읽음

 

unique row not found

두 개의 테이블이 각각 유니크(프라이머리 키 포함)칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 해당 메시지가 표시됩니다.

 

Using filesort

ORDER BY 처리가 인덱스를 사용하지 못할 때만 해당 메시지가 표시되며 이는 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 알고리즘을 수행하게 된다는 의미입니다.

해당 메시지가 출력되는 것은 많은 부하를 일으키므로 가능하다면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋습니다.

 

출처 : Real MariaDB