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

This article mainly introduces the relevant knowledge of how to use the EXPLAIN sentence in MySQL, the content is detailed and easy to understand, the operation is simple and fast, and it has a certain reference value. I believe you will gain something after reading this article on how to use the EXPLAIN statement in MySQL. Let's take a look.

Preface

In MySQL, DESCRIBE and EXPLAIN statements have the same meaning. The DESCRIBE statement is mostly used to get the table structure, while the EXPLAIN statement is used to obtain the query execution plan (used to explain how MySQL executes the query statement).

The EXPLAIN statement can help us find out which fields of the table need to be indexed to speed up the query. You can also use EXPLAIN to check whether the optimizer joins the tables in the optimal order.

The EXPLAIN syntax is as follows: {EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] {EXPLAIN | DESCRIBE | DESC} [explain_type] {explainable_stmt | FOR CONNECTION connection_id} {EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statementexplain_type: {FORMAT = format_name} format_name: {TRADITIONAL | JSON | TREE} explainable_stmt: {SELECT statement | TABLE statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE statement} 1. Get the table structure.

DESCRIBE is a shortcut to SHOW COLUMNS and can also be used to display information about View. SHOW COLUMNS provides more column information.

SHOW CREATE TABLE, SHOW TABLE STATUS, SHOW INDEX are also used to provide table information.

DESCRIBE displays information for all columns by default, and if col_name is specified, only that column will be displayed. Wild is used to specify the pattern string, and you can use SQL wildcards% and _. If wild is specified, column information that matches the pattern string is displayed. If there are no special characters (spaces or other special characters), the pattern string does not need to be enclosed in quotation marks.

DESCRIBE City;# is equivalent to SHOW COLUMNS FROM City;2, get execution plan information

DESCRIBE can query the execution information of SELECT, DELETE, INSERT, REPLACE and UPDATE. MySQL 8.0.19 can also query TABLE statements.

MySQL gets the execution plan information of interpretable statements from the optimizer, including participating tables, order, and so on.

If you use the FOR CONNECTION connection_id statement, MySQL displays the execution plan for the named connection.

The execution plan information generated by the EXPLAIN statement can be displayed through SHOW WARNINGS.

The FORMAT option is used to specify the output format, and TRADITIONAL is the default output format, which is displayed as a table.

EXPLAIN requires the same permissions as the interpretation statement to be executed, and SHOW VIEW permission is required to interpret view statements, EXPLAIN. FOR CONNECTION requires PROCESS permission.

You can use SELECT STRAIGHT_JOIN to tell the optimizer to use the connection order specified by SELECT.

3. Use EXPLAIN ANALYZE to get information

MySQL 8.0.18 recommends using EXPLAIN ANALYZE, which outputs the execution time of the statement and the following information

Estimated execution time

Estimated number of rows returned

Time to return to the first row

Execution time of the iterator in milliseconds

Number of rows returned by the iterator

Number of times to execute the loop

The query information is output in the form of TREE, with each node representing an iterator. EXPLAIN ANALYZE can be used for SELECT statements, as well as UPDATE and DELETE statements for multiple tables, and MySQL 8.0.19 can also be used for TABLE statements later. EXPLAIN ANALYZE cannot use FOR CONNECTION.

After MySQL 8.0.20, the execution of this statement can be terminated through KILL QUERY or CTRL-C.

Mysql > EXPLAIN ANALYZE SELECT * FROM T1 JOIN T2 ON (t1.c1 = t2.c2)\ row * * EXPLAIN:-> Inner hash join (t2.c2 = t1.c1) (cost=4.70 rows=6) (actual time=0.032..0.035 rows=6 loops=1)-> Table Scan on T2 (cost=0.06 rows=6) (actual time=0.003..0.005 rows=6 loops=1)-> Hash-> Table scan on T1 (cost=0.85 rows=6) (actual time=0.018..0.022 rows=6 loops=1) mysql > EXPLAIN ANALYZE SELECT * FROM T3 WHERE I > 8\ FROM * 1. Row * * EXPLAIN:-> Filter: (T3.I > 8) (cost=1.75 rows=5) (actual time=0.019..0.021 rows=6 loops=1)-> Table scan on T3 (cost=1.75 rows=15) (actual time=0.017..0.019 rows=15 loops=1) mysql > EXPLAIN ANALYZE SELECT * FROM T3 WHERE Competition > 17\ FROM * 1. Row * * EXPLAIN:-> Filter: (t3.pk > 17) (cost=1.26 rows=5) (actual time=0.013..0.016 rows=5 loops=1)-> Index range scan on T3 using PRIMARY (cost=1.26 rows=5) (actual time=0.012..0.014 rows=5 loops=1) this is the end of the article on "how to use EXPLAIN statements in MySQL" Thank you for reading! I believe you all have a certain understanding of the knowledge of "how to use EXPLAIN sentences in MySQL". If you want to learn more, you are welcome to follow the industry information channel.

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.