Shulou(Shulou.com)06/01 Report--

This paper gives an example of mysql slow query operation. Share with you for your reference, the details are as follows:

Some sql of mysql will execute very slowly, which may cause server load to soar.

First of all, the query determines that the load is affected by mysql, using top command, ps command, etc.

Second, enter MySQL, use the sql statement in the execution of the show full processlist query, look at the problem, and use the explain command to check the status

Finally, find out the sql statement to kill or optimize

Install mariadb service on centos7

Yum-y install mariadb-server mariadb-devel

Open slow query

More / etc/my.cnf.d/ server.cnf[mariadb] slow_query_log=ONslow_query_log_file=/usr/local/mysql/data/slow.loglong_query_time=1

Start the mariadb service

Systemctl start mariadb

Query whether the slow query of mysql is enabled, and how long is the slow query.

MariaDB [(none)] > show variables like'% slow_query%' +-+-+ | Variable_name | Value | +-+- -+ | slow_query_log | ON | | slow_query_log_file | / usr/local/mysql/data/slow.log | +-+-- + 2 rows in set (0.00 sec) MariaDB [(none)] > show variables like 'long_query_time' +-+-+ | Variable_name | Value | +-+-+ | long_query_time | 1.000000 | +-+-+ 1 row in set (0.00 sec) # start slow query if not used You can open mysql > set global slow_query_log=1 on the command line Query OK, 0 rows affected (0.00 sec)

Test slow queries and view logs

MariaDB [(none)] > select sleep (2); +-+ | sleep (2) | +-+ | 0 | +-+ 1 row in set (2.00 sec) [root@localhost] # more / usr/local/mysql/data/slow.log/usr/libexec/mysqld, Version: 5.5.60-MariaDB (MariaDB Server). Started with:Tcp port: 0 Unix socket: / var/lib/mysql/mysql.sockTime Id Command Argument# Time: 180930 23 No# Query_time 51 Lock_time: root [root] @ localhost [] # Thread_id: 2 Schema: QC_hit: No# Query_time: 2.001017 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0SET timestamp=1538322667;select sleep (2)

Confirm slow query

MariaDB [(none)] > show full processlist # check that state slow query is running on +-+-- + | Id | User | Host | db | Command | Time | | State | Info | Progress | +-+-- +-- + | 3 | root | localhost | NULL | Query | 9 | User sleep | select sleep (10) | 0.000 | | 4 | root | localhost | NULL | Query | 0 | NULL | show full processlist | 0.000 | +- -+-+ 2 rows in set (0.00 sec) MariaDB [(none)] > show full processlist # check that state slow query has been completed But the user logged in to +-+-- +-+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-+-- +-+ | 3 | root | localhost | NULL | Sleep | 1 | | NULL | 0.000 | | 4 | root | | localhost | NULL | Query | 0 | NULL | show full processlist | 0.000 | +-+-- + 2 rows in set (0.00 sec) |

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.