실행계획 사용

실행계획의 필요성

* SQL문 수행간 옵티마이져가 판단한 최적의 절차와 방법
* 최소의 일량인 COST를 통계정보를 바탕으로 결정함
* 튜닝의 기본적인 근거로 사용되며 사용자가 힌트를 통해 수정도 할 수 있음

통계정보 수집

* ANALYZE TABLE 테이블명;

실행계획의 확인

explain [ extended | partitions ] [ select | insert | update ]

수행중인 쿼리의 실행계획 확인

 show explain for <process_id> 

실행계획 구성 요소

기본적으로 실행계획은 위에서 아래로 읽는다. 쿼리의 문장과 비교해가면서 위부터 테이블과 매칭해서 보면 된다.

ID: SELECT 쿼리별로 부여되는 식별자
SELECT_TYPE: 어떤 유형의 쿼리인지 표시
TABLE: 액세스한 테이블로, alias가 쓰인경우 alias로 표시
TYPE: 테이블의 레코드를 어떤 방식으로 읽었는지 표시
KEYS: 최종 선택된 인덱스
ROWS: 옵티마이저가 산출해 낸 예상 건수
EXTRA: 성능에 관련된 정형화된 문장이 표시

SQL profiling

보다 자세히 sql을 분석하기 위해 profiling을 사용할 수 있다. 어느 부분에서 가장 많이 수행됐는지를 통해 튜닝포인트를 어디에 둘것인지 정할수 가 있다.

SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+

SET profiling = 1;

USE test;

DROP TABLE IF EXISTS t1;

CREATE TABLE T1 (id INT);

SHOW PROFILES;
+----------+------------+--------------------------+
| Query_ID | Duration   | Query                    |
+----------+------------+--------------------------+
|        1 | 0.00009200 | SELECT DATABASE()        |
|        2 | 0.00023800 | show databases           |
|        3 | 0.00018900 | show tables              |
|        4 | 0.00014700 | DROP TABLE IF EXISTS t1  |
|        5 | 0.24476900 | CREATE TABLE T1 (id INT) |
+----------+------------+--------------------------+

SHOW PROFILE;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000042 |
| checking permissions | 0.000044 |
| creating table       | 0.244645 |
| After create         | 0.000013 |
| query end            | 0.000003 |
| freeing items        | 0.000016 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000003 |
+----------------------+----------+

SHOW PROFILE FOR QUERY 4;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000126 |
| query end          | 0.000004 |
| freeing items      | 0.000012 |
| logging slow query | 0.000003 |
| cleaning up        | 0.000002 |
+--------------------+----------+

SHOW PROFILE CPU FOR QUERY 5;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000042 | 0.000000 |   0.000000 |
| checking permissions | 0.000044 | 0.000000 |   0.000000 |
| creating table       | 0.244645 | 0.000000 |   0.000000 |
| After create         | 0.000013 | 0.000000 |   0.000000 |
| query end            | 0.000003 | 0.000000 |   0.000000 |
| freeing items        | 0.000016 | 0.000000 |   0.000000 |
| logging slow query   | 0.000003 | 0.000000 |   0.000000 |
| cleaning up          | 0.000003 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+