Query statistics in MySQL-Mysql Tutorial-php.cn

Query statistics in MySQL

WBOY

Release： 2023-08-29 14:49:05

forward

1210 people have browsed it

Important statistics collected for the executed query, including time, temporary tables, indexes, joins, etc., are collected in the "Query Statistics SQL Editor Results" tab (see the following two images).

Require

Enable queries and collect performance schema statistics.
performance_schema Statement detection enabled.

SQL Editor: Query Statistics

Visual Interpretation Plan

The Visual Explanation feature creates and presents a visual description of a MySQL EXPLAIN statement by leveraging additional data contained in the enhanced JSON format. All EXPLAIN formats, including standard format, raw extended JSON, and visual query plans, can be used to execute queries in MySQL Workbench.

Intuitive demonstration of use

Execute the query in the SQL Editor and select Execution Procedure from the query results menu to receive a visual description of the execution plan. In addition to the default visual explain view, the execution plan also contains a tabular explain view, similar to what you see when you run EXPLAIN in the MySQL client. For more information about how MySQL executes statements, see Optimizing Queries Using EXPLAIN.

Visual explanation convention

Visual explanation diagrams should be read from bottom to top, left to right. An overview of the graphical, textual, and informational conventions used to represent the various elements of a visual interpretation plan is given in the diagram example below. For more details, see -

Graphics Standard
Information and text conventions

Visual explanation of the first graph The graph provides a visual description of the following query.

select  * FROM employee_table

Copy after login

Visual explanation example

Graphic convention

Standard box: table
Rounded corner box: GROUP and SORT operations
Frame Box: Subquery
Diamond: Connection

Text and information conventions

Standard text below box: Table (or alias) name
box: Key/index used
The number in the upper right corner of the box: the number of rows used in the filtered table
The number in the upper left corner of the box: the relative cost of accessing the table (requires MySQL 5.7 or higher)
The number on the right side of the nested loop (or hash join) diamond: the number of rows generated by the JOIN
Number above diamond: relative cost of JOIN (requires MySQL 5.7 or higher)

The following table shows the relevant colors and descriptions used in visual interpretation diagrams. For more information about cost estimates, see Optimizer Cost Model.

Visual explanation chart information

system name	color	Text on visualization chart	Tooltip related information
system	blue	Single line: system constant	The cost is very low
constant	blue	Single line: constant	The cost is very low
EQ_REF	green	Unique key lookup	Low Cost - The optimizer is able to find an index that can be used to retrieve the required records. It's fast because the index search points directly to the page containing all rows of data
refer to	green	Non-unique key lookup	Low-Medium -- Low if there are fewer matching rows; higher as the number of rows increases
full text	yellow	Full text index search	Dedicated full-text search. Low - for this specialized search requirement
REF_OR_NULL	green	Search by key to get NULL value	Low-medium - if the number of matched rows is small; higher as the number of rows increases
INDEX_MERGE	green	Index merge	Medium - Looking for better index choices in queries to improve performance
UNIQUE_SUBQUERY	orange color	Unique key lookup in subquery table	Low -- for efficient subquery processing
INDEX_SUBQUERY	orange color	Non-unique key lookup in subquery table	Low -- for efficient subquery processing
scope	orange color	Index range scan	Medium -- Partial index scan
index	red	Full index scan	High - especially for large indexes
all	red	Full table scan	Very High - Very high cost for large tables, but less impact for small tables. No free index is found for the table, forcing the optimizer to search every row. It may also mean that the search is too broad and the index will be useless.
unknown	black	unknown	Note: This is the default value in case a match cannot be determined

in conclusion

In this article, we learned how to get query statistics in MySQL Workbench and what different characteristics it has at the same time.

The above is the detailed content of Query statistics in MySQL. For more information, please follow other related articles on the PHP Chinese website!