1、mysql 批量插入300w数据
CREATE PROCEDURE test_insert_200w()
BEGIN
DECLARE i INT;
SET i=1;
WHILE i<=3000000 DO
INSERT 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=FILE
general-log=0
general_log_file="PYRX_05.log"
slow-query-log=1
slow_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文章来源:https://www.toymoban.com/news/detail-715905.html
java 代码读取日志文章来源地址https://www.toymoban.com/news/detail-715905.html
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();
}
}
}
到了这里,关于mysql 操作慢查询日志的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!