MySQL Performance analysis method

1. Explain analysis method

explain {SQL}; Parsing query statements

(1) The greater the number of ID column, the first execution, if the number is the same size, then from the top down to execute
(2) Select_type query type: Simple, primary, dependent subquery, etc.
(3) Table name
(4) Type
Performance down from top to bottom:
System: There is only one row of data or empty table in the table and can only be used for MyISAM and memory tables
Const: When a unique index or primary key is used, the return record must be an equivalent where condition of 1 rows of records, usually the type is const
Eq_ref: For each row combination from the preceding table, read a row from the table, which is probably the best type of join
Ref: For each row combination from the previous table, read multiple rows from the table, and if the key used only matches a small number of rows, the join type is good
Range: Index range scan, commonly used in queries of &LT, between, in, and like operators
All: Scan data files in full table and then filter back to the server layer to return the records that meet the requirements
(5) Possible_keys: Possible index
(6) Key: The actual index used
(7) Key_len
The length of the index used to process the query, if it is a single-column index, then the entire index length, and if it is a multi-column index, then the query may not be able to use all the columns, the specific number of columns to use the index, here will be calculated, no use of the column, here is not counted in. Key_len only calculates the index length used by the Where condition, and sorting and grouping are not evaluated to Key_len, even if the index is used.
(8) Ref: If the constant equivalent query is used, the const is shown here, and if it is a connection query, the execution plan of the driver table shows the associated fields of the driver table.
(9) Rows: Estimated number of scanned rows
(Ten) Extra
Using index: This shows that MySQL uses an overlay index to avoid accessing the table's data rows, which is very efficient!
Using where: Indicates that the server will filter after the storage engine receives the row. Some of the conditions in the where will have columns that belong to the index, and when it reads using the index, it is filtered, so some where statements do not appear in the extra column with a using where such a description
The new feature after using index condition:mysql5.6 will conditionally filter the index, filter the index to find all rows of data that match the index criteria, and then filter the data rows with other conditions in the WHERE clause
Using temporary:using Temporary: This means that MySQL uses a temporary table when the query results are sorted
Using Filesort: Cannot use index when sorting, need to use memory or disk to sort

2. Profiling Analysis Method

MySQL's query Profiler is an easy-to-use Query diagnostic analysis tool that allows you to get a query that consumes a variety of resources throughout the execution, such as Cpu,io,ipc,swap, and the PAGE faults,c that occurs. Ontext Switche and so on, you can also get the position of each function in the source file called by MySQL during the Query execution.

The operation is as follows:

Set profiling=1; Open Profiling Analysis
SELECT @ @profiling; Confirm Profiling analysis Opens
{SQL}; Execute Query statement
Show Profiles; Query the query_id of the statement that was just executed
Show profile for query {query_id}; Parsing SQL
Set profiling=0; Turn off profiling analysis (closing sessions will automatically close)

MySQL Performance analysis method