Explain and optimizer_trace execution Plan of mysql 01/19 Update SLTechnology News&Howtos

Explain and optimizer_trace execution Plan of mysql

2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

1. Explain

Mysql > explain select host,user,plugin from user

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

1.id

1.SQL execution order: execution from large to small. If equal, execute the above first, then the following. (the id number is the sequence number that select appears from left to right in the sql code)

2. Select_type is the select type, which can be divided into the following types

(1) SIMPLE

Simple Select (without using UNION or subqueries, etc.) for example:

Program code

Mysql > explain select * from T3 where id=3952602

+-- +

+-- +

| | 1 | SIMPLE | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |

+-- +

(2)。 PRIMARY

The outermost select. For example:

Program code

Mysql > explain select * from (select * from T3 where id=3952602) a

+-- +

+-- +

| | 2 | DERIVED | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |

+-- +

(3) UNION

The second or subsequent Select statement in UNION. For example

Program code

Mysql > explain select * from T3 where id=3952602 union all select * from T3

+-- +

+-- +

| | 1 | PRIMARY | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |

| | 2 | UNION | T3 | ALL | NULL | NULL | NULL | NULL | 1000 |

+-- +

(4) DEPENDENT UNION

The second or subsequent Select statement in UNION, depending on the external query

Program code

Mysql > explain select * from T3 where id in (select id from T3 where id=3952602 union all select id from T3)

+- -+

+- -+

+- -+

(5) UNION RESULT

Results of UNION.

Program code

Mysql > explain select * from T3 where id=3952602 union all select * from T3

+-- +

+-- +

| | 1 | PRIMARY | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |

| | 2 | UNION | T3 | ALL | NULL | NULL | NULL | NULL | 1000 |

+-- +

(6). SUBQUERY

The first Select in the subquery.

Program code

Mysql > explain select * from T3 where id= (select id from T3 where id=3952602)

+-- +

+-- +

| | 1 | PRIMARY | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |

| | 2 | SUBQUERY | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | Using index |

+-- +

(7)。 DEPENDENT SUBQUERY

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

Program code

Mysql > explain select id from T3 where id in (select id from T3 where id=3952602)

+-+-

+-+-

+-+-

(8). DERIVED

The Select of the derived table (subquery of the FROM clause)

Program code

Mysql > explain select * from (select * from T3 where id=3952602) a

+-- +

+-- +

| | 2 | DERIVED | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |

+-- +

3.table

Show which table the data in this row is about.

Sometimes it's not the real table name, and you see derivedx (x is a number, the derived table id value referenced by the row record)

Program code

Mysql > explain select * from (select * from (select * from T3 where id=3952602) a) b

+-- +

+-- +

| | 3 | DERIVED | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |

+-- +

4.type

This column is important to show which category is used for the connection and whether or not indexes are used.

The best to worst connection types are const, eq_reg, ref, range, indexhe, and ALL

(1) system

This is a special case of the const join type. There is only one row in the table that meets the criteria. As follows (id on table T3 is primary key)

Program code

Mysql > explain select * from (select * from T3 where id=3952602) a

+-- +

+-- +

| | 2 | DERIVED | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |

+-- +

(2) const

The table has at most one matching row, which will be read at the beginning of the query. Because there is only one row, the column values in this row can be considered constant by the rest of the optimizer. Const tables are fast because they are only read once!

Const is used when comparing all parts of an PRIMARY KEY or UNIQUE index with constant values. In the following query, tbl_name can be used for the const table:

Program code

Select * from tbl_name Where primary_key=1

Select * from tbl_name Where primary_key_part1=1 and primary_key_part2=2

For example:

Program code

Mysql > explain select * from T3 where id=3952602

+-- +

+-- +

| | 1 | SIMPLE | T3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |

+-- +

(3)。 Eq_ref

For each combination of rows from the previous table, read a row from that table. This is probably the best join type, except for the const type. It is used by joins in all parts of an index and the index is UNIQUE or PRIMARY KEY.

Eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses the columns of the table read before the table.

In the following example, MySQL can use eq_ref joins to handle ref_tables:

Program code

Select * FROM ref_table,other_table

Where ref_table.key_column=other_table.column

Select * FROM ref_table,other_table

Where ref_table.key_column_part1=other_table.column

AND ref_table.key_column_part2=1

For example

Program code

Mysql > create unique index idx_t3_id on T3 (id)

Query OK, 1000 rows affected (0.03 sec)

Records: 1000 Duplicates: 0 Warnings: 0

Mysql > explain select * from t3, T4 where t3.id=t4.accountid

+-+-

+-+-

| | 1 | SIMPLE | T4 | ALL | NULL | NULL | NULL | NULL | 1000 |

| | 1 | SIMPLE | T3 | eq_ref | PRIMARY,idx_t3_id | idx_t3_id | 4 | dbatest.t4.accountid | 1 | |

+-+-

(4) ref

For each combination of rows from the previous table, all rows with matching index values are read from this table. Use ref if the join uses only the leftmost prefix of the key, or if the key is not UNIQUE or PRIMARY KEY (in other words, if the join cannot select a single row based on the keyword). This join type is good if the keys used match only a small number of rows.

Ref can be used for indexed columns that use the = or operator.

In the following example, MySQL can use ref joins to handle ref_tables:

Program code

Select * FROM ref_table Where key_column=expr

Select * FROM ref_table,other_table

Where ref_table.key_column=other_table.column

Select * FROM ref_table,other_table

Where ref_table.key_column_part1=other_table.column

AND ref_table.key_column_part2=1

For example:

Program code

Mysql > drop index idx_t3_id on T3

Query OK, 1000 rows affected (0.03 sec)

Records: 1000 Duplicates: 0 Warnings: 0

Mysql > create index idx_t3_id on T3 (id)

Query OK, 1000 rows affected (0.04 sec)

Records: 1000 Duplicates: 0 Warnings: 0

Mysql > explain select * from t3, T4 where t3.id=t4.accountid

+-+-

+-+-

| | 1 | SIMPLE | T4 | ALL | NULL | NULL | NULL | NULL | 1000 |

| | 1 | SIMPLE | T3 | ref | PRIMARY,idx_t3_id | idx_t3_id | 4 | dbatest.t4.accountid | 1 | |

+-+-

2 rows in set (0.00 sec)

(5)。 Ref_or_null

The join type is like ref, but MySQL is added to specifically search for rows that contain null values. The optimization of this join type is often used in solving subqueries.

In the following example, MySQL can use ref_or_null joins to handle ref_tables:

Select * FROM ref_table

Where key_column=expr or key_column IS NULL

(6)。 Index_merge

The join type indicates that the index merge optimization method is used. In this case, the key column contains a list of the indexes used, and the key_len contains the longest key elements of the index used.

For example:

Program code

Mysql > explain select * from T4 where id=3952602 or accountid=31754306

+- -+

+- -+

+- -+

1 row in set (0.00 sec)

(7)。 Unique_subquery

This type replaces the ref of the in subquery in the following form:

Value IN (Select primary_key FROM single_table Where some_expr)

Unique_subquery is an index lookup function that can completely replace subqueries and is more efficient.

(8). Index_subquery

The join type is similar to unique_subquery. In subqueries can be replaced, but only for non-unique indexes in the following forms of subqueries:

Program code

Value IN (Select key_column FROM single_table Where some_expr)

(9). Range

Only a given range of rows are retrieved, using an index to select rows. The key column shows which index is used. Key_len contains the longest key element of the index used. In this type, ref is listed as NULL.

When using =, >, > =, show variables like 'optimizer_trace'

+-- +

| | Variable_name | Value |

+-- +

| | optimizer_trace | enabled=off,one_line=off |

+-- +

1 row in set (0.05sec)

two。 Demonstrate the simple process of using optimizer_trace:

2.1 session level is temporarily open

Mysql > set session optimizer_trace= "enabled=on", end_markers_in_json=on

2.2 execute your SQL

Select host,user,plugin from user

2.3 query the information_schema.optimizer_ trace table

Mysql > SELECT trace FROM information_schema.OPTIMIZER_TRACE\ G

Import into a file named xx.trace and view it with a JSON reader

SELECT TRACE INTO DUMPFILE "xx.trace" FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE

Add: permanently open optimizer_trace (restart failure)

Mysql > set optimizer_trace= "enabled=on"

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.