Mysql Efficiency optimization location of the lower SQL two ways _mysql

About MySQL efficiency optimization typically uses the following two ways to locate execution-less efficient SQL statements.

With slow query logs locating those SQL statements that perform less efficiently, when started with the--log-slow-queries[=file_name] option, Mysqld writes a log file that contains all SQL statements that run for more than long_query_time seconds. View this log file to locate less efficient SQL.

Slow query log after the end of the query to record, so when the application reflects the implementation efficiency problems when the query slow query log does not locate the problem, you can use the show processlist command to see the current MySQL on the thread, including the status of the thread, whether the lock table, you can view the SQL in real time , while some of the lock table operations are optimized.

Here's an example of how to locate an SQL statement that performs an efficiency end through a slow query log:

Turn on the slow query log to configure the sample:

Log-slow-queries

Add the above configuration entry in the MY.CNF configuration file and restart the MySQL service, when the MySQL slow query function takes effect. The slow query log will write the parameter DataDir (data directory) under the path specified, the default filename is Host_name-slow.log.

As with error logs, query logs, the format of slow query logging is also plain text and can be read directly. The following example shows the setup and read procedures for slow query logs.

(1) First check the value of Long_query_time.

Mysql> Show variables like ' long% ';
+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| long_query_time
| +-----------------+-------+
1 row in Set (0.00 sec)

(2) In order to facilitate testing, will modify the slow query time of 5 seconds.

Mysql> set long_query_time=5;
Query OK, 0 rows affected (0.02 sec)

(3) Execute the following two query statements sequentially.

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 (*) |
+----------+
|
+----------+
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) Review the slow query log.

[Root@localhost mysql]# more Localhost-slow.log
# time:081026 19:46:34
# User@host: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 installs itself) to subtotal the slow query log. In the following example, the log file Mysql_master-slow.log is summarized to show only summarized results:

[Root@mysql_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 to be exactly the same, except for variables with different statements, Mysqldumpslow will automatically be counted as the same statement, and 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 system's SQL bottleneck.

Note: The slow query log is useful for discovering SQL with performance problems in the application, and it is recommended that you normally open this log and view the analysis frequently.

The above is to introduce you to the MySQL efficiency optimization positioning of the lower SQL two ways, I hope that the above mentioned to help everyone.