MySQL performance analysis-------profiling and explain

1. Performance analysis of profiling

MySQL5. Version 0.37 supports the profiling– official manual. This tool can be used to query how much time SQL executes, how much timeSystem lock and table lock spend, and so on, whichis important for locating i/o consumption and CPU consumption of a statement.
View profiling;
SELECT @ @profiling; start profiling:set @ @profiling=1 off profiling:set @ @profiling=0;
SQL statements; 1. View Profile Records
Show Profiles;

Duration: I need time;

Query: The SQL statement executed;

2. View Details:

Show profile for query 2;

3. View Cup and IO conditions

Show profile Cpu,block io for query 2;

2.explain Analysis

1.id: A set of numbers, the sequence of operations, if the ID is the same, then the execution order from top to bottom, if it is a subquery, the ID of the ordinal increment, the higher the value of the higher priority, the more the first is executed;

2.select_type: Indicates the type of each sentence, simple or complex, the value is as follows;

A>simple: Simple query, no sub-query or union, etc.;

B>primary: If the query contains complex sub-parts, the outermost layer is marked as primary;

C>subquery: If a subquery is included in a select or where, the subquery is marked as subquery;

The d>derived:from contains subqueries, which are marked as derived;

E>union: If select appears after union, it is marked as union;

F>union Result: The select that obtains the result from the Union table is marked as union result;

database table name for 3.table queries

Types used by 4.type federated queries

All: Full table scan

Index: Full table scan, just scan the table in the index order instead of the line. The main advantage is to avoid sorting, but the overhead is still very large.

　　Range: Index range scan

Ref: Non-unique index scan, intersection returns all rows that match a single value, common to lookups that are not unique prefixes that use a non-unique index or a unique index.

Eq_ref: Unique index Scan

Const, System: When MySQL optimizes a part of the query and converts it to a constant. If you place the primary key in the where list, MySQL can convert the query to a constant. System is a special case of const and can be used when querying a table with only one row.

5. Possible_keys: Indicates which index MySQL can use to find rows in the table, and if there are indexes on the fields involved in the query, the index will be listed. If blank, indicates that no indexes are available

6.key: Which index is used, which is the actual index used, and null if no index is used.

7.key_len: The length of the index used. Without loss of accuracy, the shorter the better.

8.ref: Shows which column of the index is used

9.rows:mysql the number of rows that must be checked to return the requested data. Find the required record, and the fewer rows you need to read, the better

10.Extra: Information that is not suitable for display in other columns, but is very important, the following values are common:
A) using index: Index retrieval is used.
b) where used: the Where limit is used, but the index is not enough.
c) Using temporary: you need to use temporary tables to store result sets, which are common in sorting and grouping queries. Poor performance.
d) using Filesoft: Use of file sorting, poor performance.

Reference: http://www.cnblogs.com/sybblogs/p/7999353.html

http://blog.csdn.net/asia_kobe/article/details/78683482

MySQL performance analysis-------profiling and explain