Mysql slow query log process _ MySQL

Mysql slow query log process bitsCN.com
Mysql slow query log process mysql slow query log is very useful for tracking problematic queries, you can analyze the resource-consuming SQL statements in code implementation, there is a high reference for our program optimization. This article mainly introduces the enabling and analyzing of slow query logs, which is also a crucial step in optimizing SQL programs. 1. mysql slow query log open mysql slow query log is very simple, only need to be in the mysql configuration file (windows system is my. ini, linux is my. under [mysqld] of cnf, add the Java code log-slow-queries = mysql_slow.log long_query_time = 3, where log-slow-queries is the log file name. you can specify a directory, for example, log-slow-queries = D:/mysql_slow.log; long_query_time indicates the length of the query. we define slow query and record it in the file specified by log-slow-queries, here we define a query record that exceeds 3 seconds. After the configuration is complete, restart the Mysql server and run show variables like '% slow %'. check whether the slow query log is enabled. if slow_query_log and log_slow_queries are displayed as on, this indicates that the server's slow query log has been enabled. The following shows the Java code mysql> show variables like '% slow % '; + metric + ---------------- + | Variable_name | Value | + metric + ---------------- + | metric | ON | metric | 2 | slow_query_log | ON | metric | mysql_slo1_log | + metric + -------------- + 4 rows in set (0.00 sec) slow_launch_time has nothing to do with slow query logs. it represents a threshold value of thread create. Ng_query_time, you can use the Java code mysql> show variables like '% long % '; + ----------------- + ---------- + | Variable_name | Value | + ----------------- + ---------- + | long_query_time | 3.000000 | + ----------------- + ---------- + 1 row in set (0.00 sec) 2. for example, do not add an index to text when creating a mysql slow query log analysis table. Otherwise, the query may not exceed 3 seconds. the data table is filled with 4194304 pieces of data. Let's execute a query that exceeds 3 seconds, as shown below: Java code mysql> select * from wei where text = 'Orange '; + --------- + -------- + | id | text | + --------- + -------- + | 4103519 | orange | + --------- + -------- + 1 row in set (3.79 sec) execute another one that exceeds 3 seconds and one that does not exceed 3 seconds: Java code mysql> select * from wei where text = 'xishizhaohua'; Empty set (3.82 sec) java code [mysql> select * from wei where id = 4564; + ------ + -------------------- + | id | text | + ------ + ---- ---------------- + | 4564 | yyyyyyyyyyyyyyyyyy | + ------ + -------------------- + 1 row in set (0.02 sec: now we can view the mysql_slow.log (win7 is under C:/ProgramData/MySQL Server 5.1/data by default). The content is as follows, which is clear, including the statement and time spent in the query, it also contains information such as the timestamp during Query. Rows_examined indicates the number of checked rows, which is helpful for our optimization # Time: 121017 17:38:54 # User @ Host: root [root] @ localhost [127.0.0.1] # Query_time: 3.794217 Lock_time: 0.000000 Ro Ws_sent: 1 Rows_examined: 4194304 SET timestamp = 1350466734; select * from wei where text = 'Orange '; # Time: 121017 17:46:22 # User @ Host: root [root] @ localhost [127.0.0.1] # Query_time: 3.819219 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 4194304 SET timestamp = 1350467182; select * from wei where text = 'xishizhaohua '; 3. in fact, when the optimization is located, the slow query statement has been successfully completed. execute the explain or desc command to view the slow query statement. for example, the problem is obvious, the solution is obvious, and the index is created. Java code mysql> create index text_index on wei (text); Query OK, 4194304 rows affected (1 min 58.07 sec) Records: 4194304 Duplicates: 0 Warnings: 0 then perform the Query operation, it is much less time. Java code mysql> select * from wei where text = 'Orange '; + --------- + -------- + | id | text | + --------- + -------- + | 4103519 | orange | + --------- + -------- + 1 row in set (0.33 sec) java code mysql> select * from wei where text = 'xishizhaohua'; Empty set (0.01 sec) bitsCN.com