Detailed explanation of two ways to optimize and position lower SQL

By slowQueryThe log locates those SQL statements with low execution efficiency. When starting with the --log-slow-queries[=file_name] option, mysqld will write a log containing all execution times exceeding long_query_time seconds. Log files of SQL statements, by viewing this log file to locate SQL statements with low execution efficiency

About mysql efficiency optimization, generally locate SQL statements with low execution efficiency in the following two ways.

Locate SQL statements with low execution efficiency through slow query logs. When starting with the --log-slow-queries[=file_name] option, mysqld will write a SQL statement containing all SQL statements whose execution time exceeds long_query_time seconds. Log file, locate the less efficient SQL by viewing this log file.

The slow query log is recorded after the query is completed, so when the application reflects execution efficiency problems, querying the slow query log cannot locate the problem. You can use the show processlist command to view the current MySQL threads, including threads. status, whether to lock the table, etc., you can check the execution status of SQL in real time, and optimize some table lock operations at the same time.

Let’s give an example below to illustrate how to locate SQL statements with low execution efficiency through slow query logs:

Enable slow query logs, configuration example:

log -slow-queries

Add the above configuration items in my.cnf config file and restart the mysql service. At this time, the mysql slow query function takes effect. The slow query log will be written to the path specified by the parameter DATADIR (data directory). The default file name is host_name-slow.log.

Like error logs and query logs, the format of slow query log records is also plain text and can be read directly. The following example demonstrates the setting and reading process of the slow query log.

(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 testing, the slow query time will be modified 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 because the query time is greater than 5 seconds In the slow query log:

mysql> select count(*) from t_user;
+----------+
| count(*) |
+----------+
| 6552961 |
+----------+
1 row in set (11.07 sec)

(4) View 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 of SQL exceeds 5 seconds, while the query time of less than 5 seconds does not appear in this log.
If there are many records in the slow query log, you can use the mysqldumpslow tool (included in the MySQL client installation) to classify and summarize the slow query log. In the following example, the log file mysql_master-slow.log is classified and summarized, and only the summarized summary results are displayed:

[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;

is completely consistent with the SQL text, except for variables Different statements will be automatically treated as the same statement by mysqldumpslow for statistics, and the variable value is replaced by N. This statistical result will greatly increase the efficiency of users reading slow query logs and quickly locate the SQL bottleneck of the system.

Note: The slow query log is very helpful for us to find SQL with performance problems in the application. It is recommended that under normal circumstances, open this log and check the analysis frequently.

The above is the detailed content of Detailed explanation of two ways to optimize and position lower SQL. For more information, please follow other related articles on the PHP Chinese website!