Detailed explanation of MySQL slow queries

show status -- Display all MySQL operation information show status like "com_insert%"; -- Get the number of MySQL insertions;

show status like "com_delete%"; -- Get the number of deletions in mysql;

show status like "com_select%"; -- Get the number of mysql queries;

show status like "uptime"; -- Get the MySQL server running time show status like 'connections'; -- Get the number of MySQL connections

show variables like "%slow%";-- Whether to enable slow query;
show status like "%slow%"; -- Query the slow query SQL status;
show variables like "long_query_time"; -- Slow query time

mysql> show variables like 'long_query_time'; -- By default, MySQL considers 10 seconds to be a slow query+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

mysql> set long_query_time=1; -- Modify the slow query time, only valid for the current session;
mysql> set global slow_query_log='ON';-- Enable slow query, add global, otherwise an error will be reported;

slow_query_log = on -- Enable logging;
slow_query_log_file = /data/f/mysql_slow_cw.log -- log file for recording logs; Note: the absolute path must be written on the window, such as D:/wamp/bin/mysql/mysql5.5.16/data/show-slow.log 
long_query_time = 2 -- the longest query time in seconds;
log-queries-not-using-indexes -- indicates that queries that do not use indexes are logged

mysql> select sleep(3);

mysql> show status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 1 |
+---------------------+-------+
-- Slow_queries There is a total of one slow query

CREATE TABLE dept(
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment 'Number', 
dname VARCHAR(20) NOT NULL DEFAULT "" comment 'name', 
loc VARCHAR(13) NOT NULL DEFAULT "" comment 'Location'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 comment 'Department table';

CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, 
ename VARCHAR(20) NOT NULL DEFAULT "" comment 'name', 
job VARCHAR(9) NOT NULL DEFAULT "" comment 'Work',
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment 'Superior number',
hiredate DATE NOT NULL comment 'Job entry date',
sal DECIMAL(7,2) NOT NULL comment 'Salary',
comm DECIMAL(7,2) NOT NULL comment 'Dividend',
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment 'Department number' 
)ENGINE=MyISAM DEFAULT CHARSET=utf8 comment 'Employee table';

CREATE TABLE salgrade(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 comment 'Grade',
losal DECIMAL(17,2) NOT NULL comment 'Minimum wage',
hisal DECIMAL(17,2) NOT NULL comment 'Highest salary'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 comment 'Salary level table';

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

delimiter $
create function rand_num() 
returns tinyint(6) READS SQL DATA 
begin 
 declare return_num tinyint(6) default 0;
 set return_num = floor(1+rand()*30);
 return return_num;
end $

delimiter $
create function rand_string(n INT) 
returns varchar(255) READS SQL DATA 
begin 
 declare chars_str varchar(100) default
 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do 
  set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
  set i = i + 1;
 end while;
 return return_str;
end $

delimiter $
create procedure insert_emp(in start int(10),in max_num int(10))
begin
 declare i int default 0; 
 #set autocommit =0 Set autocommit to 0 and turn off automatic submission;
 set autocommit = 0; 
 repeat
  set i = i + 1;
  insert into emp values ​​((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
 commit;
end $

call insert_emp(1,4000000);
SELECT * FROM `emp` where ename like '%mQspyv%'; -- 1.163s

# Time: 150530 15:30:58 -- The query occurred at 2015-5-30 15:30:58
# User@Host: root[root] @ localhost [127.0.0.1] -- Who is the queryer and on what host? # Query_time: 1.134065 Lock_time: 0.000000 Rows_sent: 8 Rows_examined: 4000000 
-- Query_time: the total time taken for the query, Lock_time: the time the table is locked during the query, Rows_sent: the number of rows returned, Rows_examined: the result obtained after scanning 4 million rows of data;
SET timestamp=1432971058; -- The timestamp when the slow query occurred;
SELECT * FROM `emp` where ename like '%mQspyv%';

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] -- followed by parameters and the absolute address of the log file;

 -s what to sort by (al, at, ar, c, l, r, t), 'at' is default 
        al: average lock time 
        ar: average rows sent
        at: average query time
        c: count 
        l: lock time
        r: rows sent
        t: query time

 -r reverse the sort order (largest last instead of first)
 -t NUM just show the top n queries
 -a don't abstract all numbers to N and strings to 'S'
 -n NUM abstract numbers with at least n digits within names
 -g PATTERN grep: only consider stmts that include this string
 -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
        The default is '*', ie match all
 -i NAME name of server instance (if using mysql.server startup script)
 -l don't subtract lock time from total time

mysqldumpslow -sc -t 10 /var/run/mysqld/mysqld-slow.log # Get the top 10 slow queries mysqldumpslow -st -t 3 /var/run/mysqld/mysqld-slow.log # Get the 3 slowest queries mysqldumpslow -st -t 10 -g "left join" /database/mysql/slow-log # Get the top 10 queries with left joins sorted by time mysqldumpslow -sr -t 10 -g 'left join' /var/run/mysqld/mysqld-slow.log # Get the top 10 slow queries with left joins sorted by time mysqldumpslow -sr -t 10 -g 'left join' /var/run/mysqld/mysqld-slow.log #

Count: 1 Time=1.91s (1s) Lock=0.00s (0s) Rows=1000.0 (1000), vgos_dba[vgos_dba]@[10.130.229.196]
SELECT * FROM sms_send WHERE service_id=N GROUP BY content LIMIT N, N;

wget http://www.percona.com/get/pt-query-digest 
chmod +x pt-query-digest
# Note that this is a Linux script, you need to specify the absolute or relative path to use it - or download the entire tool set wget percona.com/get/percona-toolkit.rpm
rpm -ivh percona-toolkit-2.2.13-1.noarch.rpm

wget percona.com/get/percona-toolkit.tar.gz
tar -zxvf percona-toolkit-2.2.13.tar.gz 
cd percona-toolkit-2.2.13
perl Makefile.PL
make && make install

pt-query-digest [OPTIONS] [FILES] [DSN]

--create-review-table When the --review parameter is used to output the analysis results to a table, it will be created automatically if it does not exist.
--create-history-table When the --history parameter is used to output the analysis results to a table, it will be created automatically if the table does not exist.
--filter matches and filters the input slow queries according to the specified string before analyzing them. --limit limits the percentage or number of output results. The default value is 20, which means that the 20 slowest statements are output. If it is 50%, they are sorted from large to small according to the proportion of total response time, and the output ends when the total reaches 50%.
--host mysql server address --user mysql username --password mysql user password --history Save the analysis results to the table. The analysis results are relatively detailed. The next time you use --history, if the same statement exists and the time interval of the query is different from that in the history table, it will be recorded in the data table. You can compare the historical changes of a certain type of query by querying the same CHECKSUM.
--review saves the analysis results to the table. This analysis is just parameterizing the query conditions, one record for one type of query, which is relatively simple. The next time you use --review, if the same statement analysis exists, it will not be recorded in the data table.
--output The output type of the analysis results. The values ​​can be report (standard analysis report), slowlog (MySQL slow log), json, or json-anon. Report is generally used for easier reading.
--since The time to start the analysis. The value is a string. It can be a specified time point in the format of "yyyy-mm-dd [hh:mm:ss]" or a simple time value: s (seconds), h (hours), m (minutes), d (days). For example, 12h means that the statistics start from 12 hours ago.
--until deadline, combined with --since, can analyze slow queries over a period of time.

pt-query-digest slow.log > slow_report.log

pt-query-digest --since=12h slow.log > slow_report2.log

pt-query-digest slow.log --since '2014-05-17 09:30:00' --until '2014-06-17 10:00:00' >> slow_report3.log

pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log

pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log

pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review --create-review-table slow.log

pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history --create-review-table slow.log_20140401

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log

pt-query-digest --type=genlog localhost.log > slow_report11.log