Regarding mysql efficiency optimization, generally, locating SQL statements with low execution efficiency is located in the following two ways.
Locate those SQL statements with low execution efficiency through slow query logs. When started with the --log-slow-queries[=file_name] option, mysqld will write a log file containing all SQL statements with execution time exceeding long_query_time seconds, and locate SQL with low execution efficiency by viewing this log file.
The slow query log is recorded after the query is over. Therefore, when the application reflects the execution efficiency problems, querying the slow query log cannot locate the problem. You can use the show processlist command to view the current threads in MySQL, including the thread status, whether the table is locked, etc. You can view the execution status of SQL in real time, and optimize some lock table operations.
Let's give an example to illustrate how to locate SQL statements with low execution efficiency through slow query logs:
Turn on slow query logs, configuration example:
log-slow-queries
Add the above configuration items to the configuration file and restart the mysql service. At this time, the mysql slow query function takes effect. Slow query The log will be written to the path specified by the parameter DATADIR (data directory), and the default file name is host_name.
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 slow query logs.
(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 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 # 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 SQL with query time exceeding 5 seconds, while those with query time less than 5 seconds do not appear in this log.
If there is a lot of content recorded in the slow query log, you can use the mysqldumpslow tool (which is included with the MySQL client installation) to classify and summarize the slow query log. In the following example, the log file mysql_master is classified and summarized, and only the summary results are displayed:
[root@mysql_master mysql_data]# mysqldumpslow mysql_master Reading mysql slow query log from mysql_master 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 statements with SQL text that are completely consistent, but with different variables, mysqldumpslow will automatically be regarded as the same statement 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 system's SQL bottlenecks.
Note: Slow query logs are very helpful for SQL where we find performance issues in our application. It is recommended that under normal circumstances, open this log and view analysis frequently.
The above are two ways to optimize and position Mysql efficiency and position it is introduced to you. I hope the above will be helpful to you.