Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the knowledge points of the mysql implementation plan". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the knowledge points of the mysql implementation plan".

The DESCRIBE and EXPLAIN statements are synonyms, used either to obtain information about table structure or query execution plans.

The DESCRIBE and EXPLAIN statements are synonyms for obtaining table structure information and the execution plan of the SQL statement.

The DESCRIBE and EXPLAIN statements are synonyms. In practice, the DESCRIBE keyword is more often used to obtain information about table structure, whereas EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query). The following discussion uses the DESCRIBE and EXPLAIN keywords in accordance with those uses, but the MySQL parser treats them as completely synonymous.

DESCRIBE and EXPLAIN statements are synonyms. In fact, DESCRIBE is usually used to obtain the information of table structure, and then EXPLAIN is mostly used to obtain the execution plan of SQL statements. The MySQL parser treats these two statements entirely as synonyms.

Mysql > desc mysql.plugin

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | name | varchar (64) | NO | PRI | |

| | dl | varchar (128) | NO | |

+-+ +

2 rows in set (0.00 sec)

Mysql > explain mysql.plugin

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | name | varchar (64) | NO | PRI | |

| | dl | varchar (128) | NO | |

+-+ +

2 rows in set (0.00 sec)

Mysql > desc select * from mysql.plugin

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | plugin | system | NULL | NULL | NULL | NULL | 0 | const row not found |

+-- +

1 row in set (0.07 sec)

Mysql > explain select * from mysql.plugin

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | plugin | system | NULL | NULL | NULL | NULL | 0 | const row not found |

+-- +

1 row in set (0.00 sec)

Syntax for EXPLAIN and DESCRIBE (DESC is an abbreviation for DESCRIBE)

{EXPLAIN | DESCRIBE | DESC}

Tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}

[explain_type]

{explainable_stmt | FOR CONNECTION connection_id}

Explain_type: {

EXTENDED

| | PARTITIONS |

| | FORMAT = format_name |

}

Format_name: {

TRADITIONAL

| | JSON |

}

Explainable_stmt: {

SELECT statement

| | DELETE statement |

| | INSERT statement |

| | REPLACE statement |

| | UPDATE statement |

}

1) EXPLAIN and DESCRIBE can also view table fields

{EXPLAIN | DESCRIBE | DESC}

Tbl_name [col_name | wild]

Mysql > desc mysql.plugin name

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | name | varchar (64) | NO | PRI | |

+-+ +

1 row in set (0.00 sec)

2) parsing type

{EXPLAIN | DESCRIBE | DESC}

[explain_type]

{explainable_stmt | FOR CONNECTION connection_id}

Explain_type: {

EXTENDED

| | PARTITIONS |

| | FORMAT = format_name |

}

EXPLAIN EXTENDED: get additional information about the execution plan

EXPLAIN PARTITIONS: is used for statements involving partitioned tables

EXPLAIN FORMAT

Mysql > EXPLAIN FORMAT=JSON select * from mysql.user where user='root'

+- -+

| | EXPLAIN |

+- -+

| {

"query_block": {

"select_id": 1

"table": {

"table_name": "user"

"access_type": "ALL"

"rows": 6

"filtered":

"attached_condition": "(`mysql`.`user`.`User` = 'root')"

}

}

} |

+- -+

1 row in set, 1 warning (0.01sec)

Mysql > EXPLAIN FORMAT=TRADITIONAL select * from mysql.user where user='root'

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 6 | Using where |

+-- +

1 row in set (0.00 sec)

If you do not add for, it defaults to TRADITIONAL.

3) explainable_stmt

EXPLAIN supports SELECT DELETE INSERT REPLACE UPDATE statements

EXPLAIN Output Columns (columns of execution plan output)

ColumnJSON NameMeaningidselect_idThe SELECT identifierselect_typeNoneThe SELECT typetabletable_nameThe table for the output rowpartitionspartitionsThe matching partitionstypeaccess_typeThe join typepossible_keyspossible_keysThe possible indexes to choosekeykeyThe index actually chosenkey_lenkey_lengthThe length of the chosen keyrefrefThe columns compared to the indexrowsrowsEstimate of rows to be examinedfiltered (5.7) filteredPercentage of rows filtered by table conditionExtraNoneAdditional information

Id (JSON name: select_id)

Execute the sequence numbers of each subtask of the plan, which are in order. If the data row points to the federated result of another row, the value can be empty, and the data row to which it points is displayed.

Select_type (JSON name: none)

The types of subtasks that are scheduled to be executed. Here are all the types

Select_type ValueJSON NameMeaningSIMPLE

None

Simple queries, no federated queries and subqueries

PRIMARYNone outermost query UNIONNone the second or subsequent statement DEPENDENT UNION in a federated query

Dependent (true)

The second or subsequent statement in a federated query, depending on the external query

DEPENDENT SUBQUERYdependent (true), the first query in the subquery of the result of the UNION RESULTunion_result federated query

The first query in the subquery, depending on the external query

The subquery MATERIALIZEDmaterialized_from_subqueryMaterialized subqueryUNCACHEABLE SUBQUERYcacheable (false) A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer queryUNCACHEABLE UNIONcacheable (false) The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) after DERIVEDNoneFROM

Table (JSON name: table_name)

The name of the table that outputs the row, or it can be the following value

-: The row refers to the union of the rows with id values of M and N.

: The row refers to the derived table result for the row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause.

: The row refers to the result of a materialized subquery for the row with an id value of N. See Section 9.2.2.2, "Optimizing Subqueries with Materialization".

Partitions (JSON name: partitions)

The name of the partition matched by the query. If the value is NULL, no partition table is involved.

Type (JSON name: access_type)

The types of federated join are as follows:

System connects to the system table with only one row of data

Const reads constants, and there will be at most one piece of data, usually matching constants using primary keys or unique indexes (very fast)

Eq_ref will only have at most one matching result, which is usually accessed or connected through a primary key or a unique key index (except for the fastest connection of system and const)

Driven table index reference query in ref Join statement

Fulltext uses fulltext index

The only difference between ref_or_null and ref is that there are more null value queries

Use two (or more) indexes at the same time in an index_merge query, and then merge the index results before reading table data

The combination of the returned result fields in the unique_subquery subquery is the primary key or the only constraint

The returned result field combination in an index_subquery subquery is an index (or index combination), but not a primary key or unique index

Range index range scan

Index full index scan (1 overrides the full table query of the index, 2 full table query, by checking the index first and then the data)

ALL full table scan

Possible_keys (JSON name: possible_keys)

The possible_ Keys column indicates which index MySQL can use to find rows in the table. Note that this column is completely independent of the order of the tables shown in the EXPLAIN output. This means that some keys in possible_keys cannot actually be used in the order of the generated table.

If the column is NULL, there is no associated index. In this case, you can improve your query performance by checking the WHERE clause to see if it references certain columns or columns that are suitable for the index. If so, create an appropriate index and check the query again with EXPLAIN

Key (JSON name: key)

The key column shows the key (index) that MySQL actually decides to use. If no index is selected, the key is NULL. To force MySQL to use or ignore indexes in possible_keys columns, use FORCE INDEX, USE INDEX, or IGNORE INDEX in the query.

Key_len (JSON name: key_length)

The key_len column shows the key length that MySQL decides to use. If the key is NULL, the length is NULL.

The length of the index used. The shorter the length, the better without losing accuracy.

Ref (JSON name: ref)

The ref column shows which column or constant is used to select rows from the table with key.

If the value of the column is func, additional information exists and can be viewed using SHOW WARNINGS.

Rows (JSON name: rows)

MySQL estimates the number of rows that the query needs to execute.

For InnoDB tables, this value is not necessarily accurate.

Filtered (JSON name: filtered) (5.7)

The percentage of estimated amount of data obtained in the table

Extra (JSON name: none)

This column contains additional information about how MYSQL handles the solution for statements.

Child of 'table' pushed join@1

Const row not found

Deleting all rows

Distinct

FirstMatch (tbl_name)

Full scan on NULL key

Impossible HAVING

Impossible WHERE

Impossible WHERE noticed after reading const tables

LooseScan (m.. n)

No matching min/max row

No matching row in const table

No matching rows after partition pruning

No tables used

Not exists

Plan isn't ready yet

Range checked for each record

Scanned N databases

Select tables optimized away

Skip_open_table, Open_frm_only, Open_full_table

Start temporary, End temporary

Unique row not found

Using filesort when our Query contains ORDER BY operations and cannot use the index to complete the sorting operation, MySQL Query Optimizer has to choose the corresponding sorting algorithm to implement

All the data needed by Using index can be obtained in Index instead of fetching data from the table.

Using index condition

Using index forgroup-by data access is the same as Using index, the required data only needs to read the index. When GROUPBY or DISTINCT clause is used in Query, if the grouping field is also in the index, the information in Extra will be Using index forgroup-by.

Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)

Using MRR

Using sort_union (...), Using union (...), Using intersect (...)

Using temporary Using temporary appears in the Extra information when MySQL must use temporary tables in certain operations. It is mainly used in GROUP BY and ORDER BY operations.

Using where if we don't read all the data from the table, or if we can't get all the data we need through the index alone, then Using where information will appear.

Using where with pushed condition this is a message that appears only in the NDBCluster storage engine and can only be used by turning on the ConditionPushdown optimization feature. The control parameter is engine_condition_pushdown

Zero limit

Thank you for your reading, the above is the content of "what are the knowledge points of the mysql implementation plan". After the study of this article, I believe you have a deeper understanding of what the knowledge points of the mysql implementation plan are, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.