MySQL 慢 SQL 查询过程

主要是记录 MySQL 慢 SQL 查询过程。

• 查看是否开启慢 SQL
  SHOW VARIABLES LIKE ‘slow_query%’;

• 设定时间阈值
  show VARIABLES LIKE ‘%long_query_time%’
  SET GLOBAL long_query_time = 1;

• 查看一共执行过几次慢查询
  show global status like ‘%slow%’;

• 指定日志保存形式，FILE，TABLE，NONE
  show VARIABLES like ‘%log_output%’
  参考 Selecting General Query Log and Slow Query Log Output Destinations

• 查看保存到 Table 中的 慢 SQL，仅日志保存形式为 Table 时生效
  SELECT * from mysql.slow_log

• 导出 mysql.slow_log 中的慢 SQL
  mysql -uxxx -pxxxx -h xxx -P 3306 -D mysql -s -r -e “SELECT CONCAT( ‘# Time: ‘, DATE_FORMAT(start_time, ‘%y%m%d %H%i%s’), ‘\n’, ‘# User@Host: ‘, user_host, ‘\n’, ‘# Query_time: ‘, TIME_TO_SEC(query_time), ‘ Lock_time: ‘, TIME_TO_SEC(lock_time), ‘ Rows_sent: ‘, rows_sent, ‘ Rows_examined: ‘, rows_examined, ‘\n’, sql_text, ‘;’ ) FROM mysql.slow_log” > /data/mysql_slow_log.log

• 导出 mysql.slow_log 中指定时间的慢 SQL
  mysql -h 139.224.24.174 -u tttc -p -P33066 -D mysql -s -r -e “SELECT CONCAT( ‘# Time: ‘, DATE_FORMAT(start_time, ‘%y%m%d %H%i%s’), ‘\n’, ‘# User@Host: ‘, user_host, ‘\n’, ‘# Query_time: ‘, TIME_TO_SEC(query_time), ‘ Lock_time: ‘, TIME_TO_SEC(lock_time), ‘ Rows_sent: ‘, rows_sent, ‘ Rows_examined: ‘, rows_examined, ‘\n’, sql_text, ‘;’ ) FROM mysql.slow_log where DATE_FORMAT(start_time, ‘%Y%m%d %H:%i:%s’) between ‘20210509 09:00:00’ and ‘20210509 10:00:00’ “ > /tmp/slow.log.log

• 传文件到指定服务器
  scp mysql_slow_log_temp.log root@dev.tongtongtingche.com.cn:/temp