explain

EXPLAIN ... SELECT

참조 : https://mariadb.com/kb/en/library/explain/

         https://mariadb.com/kb/en/library/semi-join-materialization-strategy/

아래는 위의 사이트를 참조해서 내가 보기 좋게 수정한 것임.

EXPLAIN ... SELECT

Column name	Description
id	순서 테이블이 조인되는 순서 번호.  (Sequence number that shows in which order tables are joined.)
select_type	select 종류 . (What kind of SELECT the table comes from.) Value Description DEPENDENT SUBQUERY The SUBQUERY is DEPENDENT. DEPENDENT UNION The UNION is DEPENDENT. DERIVED The SELECT is DERIVED from the PRIMARY. MATERIALIZED The SUBQUERY is MATERIALIZED. PRIMARY The SELECT is a PRIMARY one. SIMPLE The SELECT is a SIMPLE one. SUBQUERY The SELECT is a SUBQUERY of the PRIMARY. UNCACHEABLE SUBQUERY The SUBQUERY is UNCACHEABLE. UNCACHEABLE UNION The UNION is UNCACHEABLE. UNION The SELECT is a UNION of the PRIMARY. UNION RESULT The result of the UNION
table	테이블의 별칭 이름입니다. 서브 쿼리의 구체화 된 임시 테이블은 <subquery #> Alias name of table. Materialized temporary tables for sub queries are named <subquery#>
type	테이블에 액세스하는 방법에 대한 정보를 포함. How rows are found from the table (join type). Value Description ALL 전체 테이블 스캔이 테이블에 대해 수행됩니다 (모든 행이 읽 t집니다). 테이블이 크고 테이블이 이전 테이블과 조인 된 경우에는 좋지 않습니다. 이는 옵티마이 저가 행에 액세스하기 위해 사용 가능한 색인을 찾을 수 없을 때 발생합니다. A full table scan is done for the table (all rows are read). This is bad if the table is large and the table is joined against a previous table! This happens when the optimizer could not find any usable index to access rows. const 표에는 일치하는 행이 하나만 있습니다. 행은 최적화 단계 전에 읽히고 테이블의 모든 열은 상수로 처리됩니다. There is only one possibly matching row in the table. The row is read before the optimization phase and all columns in the table are treated as constants. eq_ref 고유 인덱스가 행을 찾는 데 사용됩니다. 이것은 행을 찾는 최상의 계획입니다. A unique index is used to find the rows. This is the best possible plan to find the row. fulltext 전체 텍스트 색인은 행에 액세스하는 데 사용됩니다. A fulltext index is used to access the rows. index_merge '범위'액세스는 여러 인덱스에 대해 수행되고 발견 된 행은 병합됩니다. 키 열은 사용되는 키를 표시합니다. A 'range' access is done for for several index and the found rows are merged. The key column shows which keys are used. index_subquery 이는 ref와 유사하지만 키 조회로 변환 된 하위 쿼리에 사용됩니다. This is similar as ref, but used for sub queries that are transformed to key lookups. index 사용 된 색인에 대한 전체 검사. ALL보다 좋지만 인덱스가 크고 테이블이 이전 테이블과 조인 된 경우 여전히 나쁨. A full scan over the used index. Better than ALL but still bad if index is large and the table is joined against a previous table. range 하나 이상의 값 범위에서 키를 사용하여 테이블에 액세스합니다. The table will be accessed with a key over one or more value ranges. ref_or_null 'ref'와 비슷하지만 첫 번째 값을 찾을 수없는 경우 'null'값에 대한 다른 검색이 수행됩니다. 이것은 대개 하위 쿼리에서 발생합니다. Like 'ref' but in addition another search for the 'null' value is done if the first value was not found. This happens usually with sub queries. ref 고유 색인의 비 고유 색인 또는 접 두부가 행을 찾는 데 사용됩니다. 접두사가 많은 행과 일치하지 않으면 좋습니다. A non unique index or prefix of an unique index is used to find the rows. Good if the prefix doesn't match many rows. system 테이블은 0 또는 1 행을가집니다. The table has 0 or 1 rows. unique_subquery 이는 eq_ref와 유사하지만 키 조회로 변환 된 하위 조회에 사용됩니다 This is similar as eq_ref, but used for sub queries that are transformed to key lookups
possible_keys	테이블의 행을 찾는 데 사용할 수있는 테이블의 키 keys in table that could be used to find rows in the table
key	행을 검색하는 데 사용되는 키의 이름입니다. NULL키가 사용되지 않았습니다. The name of the key that is used to retrieve rows. NULL is no key was used.
key_len	사용 된 KEY의 바이트 (다중 열 키의 일부만 사용하는지 보여줍니다). 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.) The optimization phase can do the following changes to the WHERE clause: Add the expressions from the ON and USING clauses to the WHERE clause. Constant propagation: If there is column=constant, replace all column instances with this constant. Replace all columns from 'const' tables with their values. Remove the used key columns from the WHERE (as this will be tested as part of the key lookup). Remove impossible constant sub expressions. For example WHERE '(a=1 and a=2) OR b=1' becomes 'b=1'. Replace columns with other columns that has identical values: Example: WHERE a=b and a=c may be treated as 'WHERE a=b and a=c and b=c'. Add extra conditions to detect impossible row conditions earlier. This happens mainly with OUTER JOIN where we in some cases add detection of NULL values in the WHERE (Part of 'Not exists' optimization). This can cause an unexpected 'Using where' in the Extra column. For each table level we remove expressions that have already been tested when we read the previous row. Example: When joining tables t1 with t2 using the following WHERE 't1.a=1 and t1.a=t2.b', we don't have to test 't1.a=1' when checking rows in t2 as we already know that this expression is true. Value Description const row not found The table was a system table (a table with should exactly one row), but no row was found. Distinct If distinct optimization (remove duplicates) was used. This is marked only for the last table in the SELECT. Full scan on NULL key The table is a part of the sub query and if the value that is used to match the sub query will be NULL, we will do a full table scan. Impossible HAVING The used HAVING clause is always false so the SELECT will return no rows. Impossible WHERE noticed  after reading const tables. The used WHERE clause is always false so the SELECT will return no rows. This case was detected after we had read all 'const' tables and used the column values as constant in the WHERE clause. For example: WHERE const_column=5 and const_column had a value of 4. Impossible WHERE The used WHERE clause is always false so the SELECT will return no rows. For example: WHERE 1=2 No matching min/max row During early optimization of MIN()/MAX() values it was detected that no row could match the WHERE clause. The MIN()/MAX() function will return NULL. no matching row  in const table The table was a const table (a table with only one possible matching row), but no row was found. No tables used The SELECT was a sub query that did not use any tables. For example a there was no FROMclause or a FROM DUAL clause. Not exists Stop searching after more row if we find one single matching row. This optimization is used with LEFT JOIN where one is explicitly searching for rows that doesn't exists in the LEFT JOIN TABLE. Example: SELECT * FROM t1 LEFT JOIN t2 on (...) WHERE t2.not_null_column IS NULL. As t2.not_null_column can only be NULL if there was no matching row for on condition, we can stop searching if we find a single matching row. Open_frm_only For information_schema tables. Only the frm (table definition file was opened) was opened for each matching row. Open_full_table For information_schema tables. A full table open for each matching row is done to retrieve the requested information. (Slow) Open_trigger_only For information_schema tables. Only the trigger file definition was opened for each matching row. Range checked for each record  (index map: ...) This only happens when there was no good default index to use but there may some index that could be used when we can treat all columns from previous table as constants. For each row combination the optimizer will decide which index to use (if any) to fetch a row from this table. This is not fast, but faster than a full table scan that is the only other choice. The index map is a bitmask that shows which index are considered for each row condition. Scanned 0/1/all databases For information_schema tables. Shows how many times we had to do a directory scan. Select tables optimized away All tables in the join was optimized away. This happens when we are only using COUNT(*), MIN() and MAX() functions in the SELECT and we where able to replace all of these with constants. Skip_open_table For information_schema tables. The queried table didn't need to be opened. unique row not found The table was detected to be a const table (a table with only one possible matching row) during the early optimization phase, but no row was found. Using filesort Filesort is needed to resolve the query. This means an extra phase where we first collect all columns to sort, sort them with a disk based merge sort and then use the sorted set to retrieve the rows in sorted order. If the column set is small, we store all the columns in the sort file to not have to go to the database to retrieve them again. Using index Only the index is used to retrieve the needed information from the table. There is no need to perform an extra seek to retrieve the actual record. Using index condition Like 'Using where' but the where condition is pushed down to the table engine for internal optimization at the index level. Using index condition(BKA) Like 'Using index condition' but in addition we use batch key access to retrieve rows. Using index for group-by The index is being used to resolve a GROUP BY or DISTINCT query. The rows are not read. This is very efficient if the table has a lot of identical index entries as duplicates are quickly jumped over. Using intersect(...) For index_merge joins. Shows which index are part of the intersect. Using join buffer We store previous row combinations in a row buffer to be able to match each row against all of the rows combinations in the join buffer at one go. Using sort_union(...) For index_merge joins. Shows which index are part of the union. Using temporary A temporary table is created to hold the result. This typically happens if you are using GROUP BY, DISTINCT or ORDER BY. Using where A WHERE expression (in additional to the possible key lookup) is used to check if the row should be accepted. If you don't have 'Using where' together with a join type of ALL, you are probably doing something wrong! Using where with pushed condition Like 'Using where' but the where condition is pushed down to the table engine for internal optimization at the row level. Using buffer The UPDATE statement will first buffer the rows, and then run the updates, rather than do updates on the fly. See Using Buffer UPDATE Algorithm for a detailed explanation.

EXPLAIN SELECT * FROM employees e JOIN salaries s ON e.emp_no = s.emp_no;