[MySQL Optimizer]--how to locate less efficient SQL

Typically, the following two ways of locating inefficient SQL statements are performed.

• When you use the slow query log to locate those SQL statements that perform less efficiently, and start with the--log-slow-queries[=file_name] option, Mysqld writes a log file that contains all SQL statements that have been executed for more than long_query_time seconds. View this log file to locate less efficient SQL.
• The slow query log is not recorded until after the query is over, so querying the slow query log does not locate the problem when the application reflects the problem of execution efficiency, and you can use the show processlist command to view the current threads in progress, including the status of the thread, whether the table is locked, and so on, to view the SQL in real time , while optimizing some of the lock table operations.

Here's an example of how to locate an inefficient SQL statement using a slow query log:
To turn on the slow query log, configure the sample:
[Mysqld]
Log-slow-queries
Add the above configuration item in the MY.CNF configuration file and restart the MySQL service, when the MySQL slow query function takes effect. The slow query log will be written to the parameter DataDir (data directory) specified by the path, the default file name is Host_name-slow.log.
As with the error log and query log, the format of slow query logging is also plain text and can be read directly. The following example shows the settings and the read process for the slow query log.
(1) First check the value of Long_query_time.
Mysql> Show variables like ' long% ';
+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| Long_query_time | 10 |
+-----------------+-------+
1 row in Set (0.00 sec)
(2) For the convenience of testing, the modified slow query time is 5 seconds.
Mysql> set long_query_time=5;
Query OK, 0 rows affected (0.02 sec)
(3) Execute the following two query statements in turn.
The first query does not appear in the slow query log because the query time is less than 5 seconds:
Mysql> Select COUNT (*) from order2008;
+----------+
| COUNT (*) |
+----------+
| 208 |
+----------+
1 row in Set (0.00 sec)
The second query should appear in the slow query log because the query time is greater than 5 seconds:
Mysql> Select COUNT (*) from T_user;
+----------+
| COUNT (*) |
+----------+
| 6552961 |
+----------+
1 row in Set (11.07 sec)
(4) View the slow query log.
[Email protected] mysql]# more Localhost-slow.log
# time:081026 19:46:34
# [email protected]: root[root] @ localhost []
# query_time:11 lock_time:0 rows_sent:1 rows_examined:6552961
Select COUNT (*) from T_user;
From the above log, you can find that the query time is more than 5 seconds of SQL, and less than 5 seconds does not appear in this log.
If you have a lot of records in the slow query log, you can use the Mysqldumpslow tool (the MySQL client installation comes with) to subtotal the slow query log. The following example summarizes the log file Mysql_master-slow.log, showing only summary results:
[Email protected]_master mysql_data]# Mysqldumpslow Mysql_master-slow.log
Reading MySQL slow query log from Mysql_master-slow.log
Count:2 time=11.00s (22s) lock=0.00s (0s) rows=1.0 (2), Root[root] @mysql_master
Select COUNT (N) from T_user;
For SQL text exactly the same, only the variable statements, Mysqldumpslow will be automatically treated as the same statement to be counted, the value of the variable is substituted with N. This statistic will greatly increase the efficiency of the user reading the slow query log and quickly locate the SQL bottleneck of the system.
Note: The slow query log is useful for finding SQL that has performance problems in your app, and it is recommended that you open this log and view the analysis frequently, normally.



This article comes from Chinaunix blog, if you look at the original point:Http://blog.chinaunix.net/u3/93470/showart_2001472.html

[MySQL Optimizer]--how to locate less efficient SQL