쿼리 실행 계획

실행 계획의 각 column들의 의미

 

Column name	Description
id	Sequence number that shows in which order tables are joined.
select_type	What kind of SELECT the table comes from.
table	Alias name of table. Materialized temporary tables for sub queries are named <subquery#>
type	How rows are found from the table (join type).
possible_keys	keys in table that could be used to find rows in the table
key	The name of the key that is used to retrieve rows. NULL is no key was used.
key_len	How many bytes of the key that was used (shows if we are using only parts of the multi-column key).
ref	The reference that is used to as the key value.
rows	An estimate of how many rows we will find in the table for each key lookup.
Extra	Extra information about this join.

 

세부 설명은 아래 링크에서..

https://mariadb.com/kb/en/mariadb/explain/

 

 

 

 

 

id 값이 작을 수록 query의 바깥 부분이거나 먼저 접근한 테이블.

하나의 select 문장 안에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행계획 레코드가 출력되지만 같은 id가 부여됨.

 

 

http://oniondev.egloos.com/9680161

http://oniondev.egloos.com/9680300

http://choko11.tistory.com/entry/%EC%8B%A4%ED%96%89%EA%B3%84%ED%9A%8D-%EB%B6%84%EC%84%9D-1-id-selecttype-%EC%BB%AC%EB%9F%BC

 

 

 

 

 

 

논외. QUERY 실행 순서

-문법 순서-

 

SELECT                            - 1

FROM                               - 2

WHERE                             - 3

GROUP BY                        - 4

HAVING                            - 5

ORDER BY                        - 6

 

-실행 순서-

 

FROM                               - 1

WHERE                             - 2

GROUP BY                        - 3

HAVING                             - 4

SELECT                            - 5

ORDER BY                         - 6

 

 

해당 데이터가 있는 곳을 찾아가서                   (FROM)

조건에 맞는 데이터만 가져와서                       (WHERE)

원하는 데이터로 가공                                    (GROUP BY)

가공한 데이터에서 조건에 맞는 것만                (HAVING)

뽑아내서                                                     (SELECT)

정렬                                                           (ORDER BY)

 

http://police84.tistory.com/69

 

icarus8050.tistory.com/45

[MySQL] Explain 실행 계획

https://nomadlee.com/mysql-explain-sql/

MySQL Explain 실행계획 사용법 및 분석 - Useful Guide