Two ways of Mysql efficiency optimization positioning lower sql

Regarding mysql efficiency Optimization 1, the sql statement with low execution efficiency is generally located in two ways.

When started with the -- log-ES7en-ES8en [=file_name] option, mysqld writes a log file containing all SQL statements that take longer than long_query_time seconds to locate the less efficient SQL statements by looking at the log file.

Slow query log at the end of the query after the record, so when application problems reflect the execution efficiency of the query the slow query log is not positioning problem, you can use show processlist command to view the current MySQL on thread, including the state of the thread, whether to lock table, etc., can be the performance of the real-time view SQL to 1 at the same time some lock table operation optimization.

Here is an example of how to locate the SQL statement with low execution efficiency through slow query logs:

Turn on the slow query log and configure the sample:

log-slow-queries

Add the above configuration items to the ES35en.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 path specified by the parameter DATADIR (data directory). The default file name is ES40en_name-slow.log.

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

(1) First query the value of long_query_time under 1.

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 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 turn.

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 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 see that SQL has a query time of more than 5 seconds, while those less than 5 seconds do not appear in this log.
If the slow query log has a lot of entries, you can use the mysqldumpslow tool (which comes with the MySQL client installation) to categorize and summarize the slow query log. In the following example, the log file mysql_ES86en-ES87en.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;

For SQL statements whose text is completely 1 but with different variables, mysqldumpslow will automatically be counted as the same statement, and the variable value will be replaced by N. This statistic 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 SQL, which we found to have performance problems in our application. We recommend that you normally open this log and check the analysis frequently.

These are the two ways to optimize Mysql for lower efficiency. I hope the above can be helpful to you.