Basic tutorial on using explain statement in MySQL

1. Overview

In MySQL, we can use slow query logs or show processlist commands to locate SQL statements that take a long time to execute. After that, we can use EXPLAIN or DESC commands to obtain information about how MySQL executes SELECT statements, including how tables are connected and the order in which they are connected during the execution of SELECT statements.

1. Explain statement test

explain+select statement is the usage of explain command

2. Description of each column in the explain result

Below, we will explain these columns one by one.

2. Explain ID column

1. Environmental preparation

2. Detailed explanation of the id column in explain

The id field is the serial number of the select query, which is a set of numbers that represents the order in which the select clauses are executed or the tables are operated in the query. There are three id situations: (1) The same id means the order of loading tables is from top to bottom.

(2) id The larger the id value, the higher the priority and the earlier it is executed.

(3) Some ids are the same, while others are different and exist at the same time. Items with the same id can be considered as a group and executed sequentially from top to bottom. Among all groups, the larger the id value, the higher the priority and the earlier it is executed.

3. select_type column of explain

1. select_type

2.

SIMPLE

3. PRIMARY, SUBQUERY

4. DERIVED

5. UNION, UNION RESULT

IV. Explain table and type columns

1. Table columns

The table column shows which table the row of data belongs to.

2. Type column

The type column shows the access type.

3. Example

(1) NULL

(2) system

(3) const

(4) eq_ref

(5) ref

(6) index

(7) ALL

5. Explain key, rows, and extra columns

1. key

2. rows

Number of scan lines

If there is an index, only one row is scanned

3. Extra

Summarize

This is the end of this article about the basic usage tutorial of explain statement in MySQL. For more relevant content on the use of explain in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!