mysql 操作慢查询日志

1、mysql 批量插入300w数据

CREATE PROCEDURE test_insert_200w()
BEGINDECLARE i INT;SET i=1;WHILE i<=3000000 DOINSERT INTO shop_user (`password`, `telephone`, `username`) VALUES ('admin', '15510304125', concat('admin', i));SET i=i+1;END WHILE;
END;
//执行sql
call test_insert_200w();

2、慢查询日志的时间

-- 设置慢查询日志时间
show variables like 'long_query_time';-- 是否开启慢查询日志
show variables like 'slow_query%'-- 慢查询日志路径
show variables like 'slow_query_log_file%';
将 slow_query_log 全局变量设置为“ON”/OFF状态 
set global slow_query_log='ON';
设置慢查询⽇志存放的位置
set global slow_query_log_file='/op/slow.log/';二、修改时长，查询超过1秒就记录 
set global long_query_time=1;
如何通过set命令无效就有可能my.ini配置文件中已经配置了设置日志存放的路径
datadir=D:/developmentTool/Mysql-5.7/mysql-data/Data# The default character set that will be used when a new schema or table is
# created and no character set is defined
# character-set-server=# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB# The current server SQL mode, which can be set dynamically.
# Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This 
# makes it easier to use MySQL in different environments and to use MySQL together with other 
# database servers.
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"# General and Slow logging.
log-output=FILEgeneral-log=0general_log_file="PYRX_05.log"slow-query-log=1slow_query_log_file="PYRX_05-slow.log"long_query_time=2# Error Logging.
log-error="PYRX_05.err"

3、设置慢查询日志存放方式，分为文件和表两种形式

og_output参数是什么意思？表示慢日志输出到文件还是表中。

show global variables like 'log_output'；

默认参数，log_output='FILE'，表示慢日志输出到了文件中。

set global log_output='TABLE';

此时慢日志就输出到了mysql数据库的系统表中：select * from mysql.slow_log; 可以查看到。

SELECT
    start_time,
    user_host,
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    db,
    last_insert_id,
    insert_id,
    thread_id,
    CONVERT (sql_text USING utf8 )  sql_text
FROM
    mysql.slow_log 
    ORDER BY     UNIX_TIMESTAMP(start_time) DESC

java 代码读取日志

package com.example.rediscache;import java.sql.*;public class SlowLogTest {public static void main(String[] args) {String url = "jdbc:mysql://localhost:3306/mysql?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=UTC";String username = "root";String password = "pyrx123";try {// 1. 获取数据库连接Connection conn = DriverManager.getConnection(url, username, password);// 2. 创建一个Statement对象Statement stmt = conn.createStatement();// 3. 执行SQL查询，获取结果集ResultSet rs = stmt.executeQuery("SELECT start_time, CONVERT( sql_text USING utf8) sql_text, user_host, query_time, lock_time," +" rows_sent, rows_examined, db, last_insert_id, insert_id, thread_id FROM mysql.slow_log ORDER BY UNIX_TIMESTAMP( start_time ) DESC");// 4. 处理结果集while (rs.next()) {String start_time = rs.getString("start_time");String sql_text = rs.getString("sql_text");String user_host = rs.getString("user_host");String query_time = rs.getString("query_time");String lock_time = rs.getString("lock_time");String rows_sent = rs.getString("rows_sent");String rows_examined = rs.getString("rows_examined");String db = rs.getString("db");String last_insert_id = rs.getString("last_insert_id");String insert_id = rs.getString("insert_id");String thread_id = rs.getString("thread_id");// 其他属性...System.out.println("Column start_time: " + start_time+ ", Column sql_text: "  + sql_text+ ", Column user_host: "+ user_host+ ", Column query_time: " + query_time+ ", Column lock_time: " + lock_time+ ", Column rows_sent: " + rows_sent+ ", Column rows_examined: " + rows_examined+ ", Column db: " + db+ ", Column last_insert_id: " + last_insert_id+ ", Column insert_id: " + insert_id+ ", Column thread_id: " + thread_id);}// 5. 关闭连接和Statement对象rs.close();stmt.close();conn.close();} catch (SQLException e) {e.printStackTrace();}}
}