Example of MySQL slow query

show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------+----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /mysql/data/localhost-slow.log |
+---------------------------+----------------------------------+

show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

--Set the slow_query_log global variable to "ON" state set global slow_query_log='ON'; 

--Set the location where the slow query log is stored set global slow_query_log_file='/usr/local/mysql/data/slow.log';

--Record if the query exceeds 1 second set global long_query_time=1;

  slow_query_log = ON
  slow_query_log_file = /usr/local/mysql/data/slow.log
  long_query_time = 1

--s: indicates the sorting method--c: number of visits--l: lock time--r: returned records--t: query time--al: average lock time--ar: average number of returned records--at: average query time--t: how many records are returned in front--g: followed by a regular matching pattern, case-insensitive

--1. Get the 10 SQL statements that return the most records
  mysqldumpslow -s -r -t 10 /logs/mysql-slow.log

 --2. Get the top 10 SQLs with the highest number of visits
  mysqldumpslow -s -c -t 10 /logs/mysql-slow.log

 --3. Get the first 10 query statements containing links sorted by time mysqldumpslow -st -t 10 -g "left join" /logs/mysql-slow.log
 
 --4. In addition, it is recommended to use | and more when using these commands, otherwise the screen may explode. mysqldumpslow -sr -t 10 /logs/mysql-slow.log | more

show variables like "profiling";--The default is closed+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+

--enable set profiling=1;

show profiles;--will list all SQL statements executed during this opening period, with the QUERY ID attached
+----------+------------+----------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------+
| 1 | 0.00168025 | select * from vhr.employee |
| 2 | 0.06573200 | select * from vhr.hr |
+----------+------------+----------------------------+
--We can see two statements showing the most recent operations. The list size is controlled by the profiling_history_size session variable. The default value is 15 and the maximum value is 100.

show profile cpu,block io for queryid -- corresponds to query_id in 2

SHOW PROFILE CPU FOR QUERY 1;--Query the specific information of query_id 1+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000194 | 0.000000 | 0.000000 |
| checking permissions | 0.000012 | 0.000000 | 0.000000 |
| Opening tables | 0.000030 | 0.000000 | 0.000000 |
| init | 0.000053 | 0.000000 | 0.000000 |
| System lock | 0.000011 | 0.000000 | 0.000000 |
| optimizing | 0.000003 | 0.000000 | 0.000000 |
| statistics | 0.000014 | 0.000000 | 0.000000 |
| preparing | 0.000010 | 0.000000 | 0.000000 |
| executing | 0.000001 | 0.000000 | 0.000000 |
| Sending data | 0.001213 | 0.000000 | 0.000000 |
| end | 0.000014 | 0.000000 | 0.000000 |
| query end | 0.000012 | 0.000000 | 0.000000 |
| closing tables | 0.000019 | 0.000000 | 0.000000 |
| freeing items | 0.000070 | 0.000000 | 0.000000 |
| cleaning up | 0.000025 | 0.000000 | 0.000000 |
+----------------------+----------+----------+------------+