(1) Positioning of SQL Performance problems during mysql Optimization

(1) Positioning of SQL Performance problems during mysql Optimization
Overview

When we face a problem, we first discover the problem and then solve the problem. This article mainly addresses how to locate the problem.

Solution 1. Use show status to learn the frequency of SQL Execution

show status [like 'com_%'];

Com_xxx indicates the number of times each xxx statement is executed.
For specific parameters, see:
Http://lxneng.iteye.com/blog/451985
Http://www.sandzhang.com/blog/2010/04/07/mysql-show-status-explained-detail/


2. Analyze inefficient SQL statements through explain

explain sql_statement;


Parameter relationship

Parameters	Explanation	Value
Select_type	Indicates the query type.	Simple-simple table, primary-primary query, second query in union-union, subquery-subquery
Table	Queried table	-
Type	Access type	All-full table scan, index-index full scan, range-index range scan, ref-use non-unique index or (unique index prefix) scan, eq_ref-unique index scan, const/system-a single table can have a maximum of one row of matching, null-no need to access the table or index, you can directly get the result
Possible_keys	Indexes that may be used during Query	-
Key	Actually used Index	-
Key_len	Use index Field Length	-
Rows	Number of scanned rows	-
Extra	Description and description	-

3. explain extended and show warnings

explain extended sql_statement;show warnings;



Compared with explain, the explain extended output result has a filtered field (the number of all result lines/The number of query result lines * 100). The message field of show warning shows the optimization result of the SQL optimizer. <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4KCjxoMyBpZD0 = "4 Share SQL through-show-profile"> 4. Share the SQL statement with show profile

# Check whether mysql supports profileSELECT @ have_profiling; # Check whether profilingselect @ profiling is enabled; # view profileshow profiles; # view the profile of a specific query, n-query id; show profile for query n;




The Sending data status indicates that the mysql thread starts to access the data row and returns the result to the client, instead of returning the result to the client.

5. Analyze with the trace Analyzer

# Enable trace, set the format to json, and set the maximum memory size available for trace. Set optimizer_trace = "enabled = on", end_markers_in_json = on; set optimizer_trace_max_mem_size = 100000; # Check traceSELECT * FROM information_schema.OPTIMIZER_TRACE;

6. Slow query logs