整体参数配置

slow_query_log=ON
log_slow_extra = 1
slow_query_log_file = ssd/mysql/logs/slow.log
long_query_time = 10
log_output = 'TABLE';

SQL方式设置

SET GLOBAL  log_output = 'TABLE';
SET PERSIST log_output = 'TABLE';
SET GLOBAL  slow_query_log='ON' ;
SET GLOBAL  long_query_time = 10 ;
SET PERSIST slow_query_log='ON' ;
SET PERSIST long_query_time = 10 ;

查询SQL

# 按照查询时间倒叙展示慢SQL
SELECT
  CAST( sql_text AS CHAR ) AS text_col,
  query_time 
FROM
  mysql.slow_log 
ORDER BY
  query_time DESC

# 相同慢SQL统计以及次数
SELECT
  CAST( sql_text AS CHAR ) AS text_col,
  count( 1 ) 
FROM
  mysql.slow_log 
GROUP BY
  CAST( sql_text AS CHAR ) 
ORDER BY
  2 DESC

查看最近一小时的慢SQL

SELECT
  text_col,
  count( 1 ) 
FROM
  (
  SELECT
    CAST( sql_text AS CHAR ) AS text_col,
    query_time,
    start_time 
  FROM
    mysql.slow_log 
  WHERE
    start_time > date_add( sysdate(), INTERVAL - 1 HOUR ) 
  ORDER BY
    query_time DESC 
  ) AS a 
GROUP BY
  a.text_col 
ORDER BY
  2 DESC

MySQL慢SQL说明

# Time: 2020-12-15T10:04:48.000000 CST                              # log记录的时间
# User@Host: dbasy9kuser[dbasy9kuser] @  [10.19.67.56]              # SQL的执行主机
# Query_time: 3  Lock_time: 0  Rows_sent: 5  Rows_examined: 545599  # SQL 的执行信息（执行时间(单位：s)，锁时间，返回结果行数，查询总行数）
SET timestamp=1607997888                                            # SQL 执行发生的时间（时间戳格式）