Mysql efficiency optimization: two ways to locate low SQL _ MySQL

Use the slow query log to locate SQL statements with low execution efficiency. when the -- log-slow-queries [file_name] option is enabled, mysqld will write a log file containing all SQL statements whose execution time exceeds long_query_time. by viewing this log file, mysql efficiency optimization is usually located in the following two ways.

Use the slow query log to locate SQL statements with low execution efficiency. when the -- log-slow-queries [= file_name] option is enabled, mysqld writes a log file that contains all SQL statements whose execution time exceeds long_query_time. you can view the log file to locate the SQL statements with lower efficiency.

The slow query log is recorded only after the query is completed. Therefore, when the application reports an execution efficiency error, the slow query log cannot be queried, you can use the show processlist command to view the current MySQL thread, including the thread status and whether to lock the table. you can view the SQL execution status in real time, optimize some lock table operations.

The following is an example of how to locate the SQL statement with low execution efficiency through the slow query log:

Enable slow query log. configuration example:

Log-slow-queries

Add the preceding configuration item in the my. cnf configuration file and restart the mysql service. The mysql slow query function takes effect. Slow query logs write to the path specified by the parameter DATADIR (data directory), with the default file name being a host_name-slow.log.

Like error logs and query logs, slow query log records are in plain text format and can be directly read. The following example shows how to set and read slow query logs.

(1) first query 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) to facilitate the test, set the slow query time to 5 seconds.

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

(3) execute the following two query statements in sequence.

The first query will 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 slow query logs.

[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: 6552961select count(*) from t_user;

From the above log, you can find that the SQL query time exceeds 5 seconds, but less than 5 seconds does not appear in this log.
If the slow query log contains many records, you can use the mysqldumpslow tool (which is included in the MySQL client installation) to classify and summarize the slow query logs. In the following example, the log file mysql_master-slow.log is classified and summarized, and only the summary result is displayed:

[root@mysql_master mysql_data]# mysqldumpslow mysql_master-slow.logReading mysql slow query log from mysql_master-slow.logCount: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql_masterselect count(N) from t_user;

For SQL texts that are completely consistent, only statements with different variables, mysqldumpslow is automatically regarded as the same statement for statistics, and the variable value is replaced by N. This statistical result will greatly increase the efficiency of reading slow query logs and quickly locate the system's SQL bottleneck.

Note: slow query logs are very helpful for SQL statements that find performance problems in applications. we recommend that you enable this log and view the analysis frequently under normal circumstances.

The above are two ways to optimize Mysql efficiency and locate low SQL. I hope the above will be helpful to you.