The Ultimate Guide to High Availability with MariaDB

EXPLAIN

Stai visualizzando una vecchia versione di questo article. Visualizza la versione più recente.

Sintassi

EXPLAIN nome_tab

Oppure:

EXPLAIN [EXTENDED | PARTITIONS] SELECT opzioni_select

Spiegazione

L'istruzione EXPLAIN può essere utilizzata come sinonimo di

DESCRIBE oppure per ottenere informazioni sul modo in cui MariaDB

esegue un'istruzione SELECT:

  • 'EXPLAIN nome_tab' è sinonimo di

'DESCRIBE nome_tab' o

'SHOW COLUMNS FROM nome_tab'.

  • Quando la parola chiave EXPLAIN precede una istruzione

SELECT, MariaDB mostra informazioni sul piano di esecuzione della

query, ottenute dall'ottimizzatore. In pratica, MariaDB spiega come intende eseguire

la SELECT, compreso il modo e l'ordine in cui intende concatenare

le tabelle. EXPLAIN EXTENDED serve a fornire maggiori

informazioni.
Per ulteriori informazioni su come usare EXPLAIN e EXPLAIN EXTENDED per conoscere

i piani di esecuzione delle query, si veda http://dev.mysql.com/doc/refman/5.1/en/using-explain.html.

  • EXPLAIN PARTITIONS è supportato a partire da MySQL 5.1.5. Serve solo quando si esaminano le query che coinvolgono le tabelle

partizionate.
Per i dettagli, si veda http://dev.mysql.com/doc/refman/5.1/en/partitioning-info.html.

E' disponibile online un EXPLAIN Analyzer che può essere

utilizzato per condividere con altre persone gli output di EXPLAIN e EXPLAIN

EXTENDED.

The columns in EXPLAIN ... SELECT

Nome ColonnaSpiegazione
idNumero di sequenza che indica in quale ordine vengono concatenate le tabelle.
select_typeDa che tipo di SELECT viene la tabella.
tableAlias dei nomi di tabella. Tabelle temporanee materializzate per le subquery chiamate <subquery#>
typeIn che modo le righe vengono lette (tipo di join).
possible_keyskeys in table that could be used to find rows in the table
keyThe name of the key that is used to retrieve rows. NULL is no

key was used.|

key_lenHow many bytes of the key that was used (shows if we are using

only parts of the multi-column key).|

refThe reference that is used to as the key value.
rowsAn estimate of how many rows we will find in the table for

each key lookup.|

ExtraExtra information about this join.

Here are descriptions of the values for some of the more complex columns in

EXPLAIN ... SELECT:

"select_type" column

The select_type column can have the following values:

ValueDescription
PRIMARYThe SELECT is a PRIMARY one.
SIMPLEThe SELECT is a SIMPLE one.
DERIVEDThe SELECT is DERIVED from the PRIMARY.
SUBQUERYThe SELECT is a SUBQUERY of the PRIMARY.
DEPENDENT SUBQUERYThe SUBQUERY is DEPENDENT.
UNCACHEABLE SUBQUERYThe SUBQUERY is UNCACHEABLE.
UNIONThe SELECT is a UNION of the PRIMARY.
UNION RESULTThe result of the UNION.
DEPENDENT UNIONThe UNION is DEPENDENT.
UNCACHEABLE UNIONThe UNION is UNCACHEABLE.

"Type" column

This column contains information on how the table is accessed.

ValueDescription
ALLA 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.

constThere 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_refA unique index is used to find the rows. This is the best

possible plan to find the row.

fulltextA fulltext index is used to access the rows.
index_mergeA 'range' access is done for for several index and the

found rows are merged. The key column shows which keys are used.

index_subqueryThis is similar as ref, but used for sub queries that are

transformed to key lookups.

indexA 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.

rangeThe table will be accessed with a key over one or more

value ranges.

ref_or_nullLike '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.

refA non unique index or prefix of an unique index is used to

find the rows. Good if the prefix doesn't match many rows.

systemThe table has 0 or 1 rows.
unique_subqueryThis is similar as eq_ref, but used for sub queries that

are transformed to key lookups

"Extra" column

This column consists of one or more of the following values, separated by ';'

Note that some of these values are detected after the optimization phase.

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.
ValueDescription
const row not foundThe table was a system table (a table with should exactly

one row), but no row was found.

DistinctIf distinct optimization (remove duplicates) was used. This is marked

only for the last table in the SELECT.

Full scan on NULL keyThe 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 HAVINGThe 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 WHEREThe used WHERE clause is always false so the SELECT will

return no rows. For example: WHERE 1=2

No matching min/max rowDuring 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 tableThe table was a const table (a

table with only one possible matching row), but no row was found.

No tables usedThe SELECT was a sub query that did not use any tables. For

example a there was no FROM clause or a FROM DUAL clause.

Not existsStop 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_onlyFor INFORMATION_SCHEMA tables. Only the frm (table definition

file was opened) was opened for each matching row.

Open_full_tableFor INFORMATION_SCHEMA tables. A full table open for each

matching row is done to retrieve the requested information. (Slow)

Open_trigger_onlyFor 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 databasesFor INFORMATION_SCHEMA tables. Shows how many times

we had to do a directory scan.

Select tables optimized awayAll 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_tableFor INFORMATION_SCHEMA tables. The queried table didn't need

to be opened.

unique row not foundThe 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 filesortFilesort 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 there

columns 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 indexOnly 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 conditionLike '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-byThe 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 bufferWe 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 temporaryA temporary table is created to hold the result. This

typically happens if you are using GROUP BY, DISTINCT or ORDER BY.

Using whereA 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 conditionLike 'Using where' but the where

condition is pushed down to the table engine for internal optimization at the row

level.

Examples

Example of ref_or_null optimization:

SELECT * FROM table_name
  WHERE key_column=expr OR key_column IS NULL;

ref_or_null is something that often happens when you use

subqueries with NOT IN as then one has to do an extra check for

NULL values if the first value didn't have a matching row.

Commenti

Sto caricando i commenti......
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.