How to optimize MySQL statements

How to optimize the MySQL sentence, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Enable slow query log and configure sample:

[mysqld] log-slow-queries

Add the above configuration item to the my.cnf configuration file and restart the mysql service, when the mysql slow query feature takes effect. The slow 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 log and query log, the format of slow query log record is plain text and can be read directly. The following example demonstrates the setup and reading process of the slow query log.

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)

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)

Execute the following two query statements in turn.

Mysql > select count (*) from order2008; +-+ | count (*) | +-+ | 208 | +-+ 1 row in set (0.00 sec)

Mysql > select count (*) from tweeter user; +-+ | count (*) | +-+ | 6552961 |

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 log above, you can find SQL with query time of more than 5 seconds, while those less than 5 seconds do not appear in this log.

If there are a lot of records in the slow log, you can use the mysqldumpslow tool (included with the MySQL client installation) to classify and summarize the slow log. In the following example, the log file mysql_master-slow.log is classified and summarized, and only the 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 statements with identical SQL text but different variables, mysqldumpslow will automatically count them as the same statement, and the variable value will be replaced by N. This statistical result will greatly increase the efficiency of reading slow query logs and quickly locate the SQL bottleneck of the system.

After reading the above, have you mastered how to optimize MySQL statements? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!