1、mysql 批量插入300w數(shù)據(jù)

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;
//執(zhí)行sql
call test_insert_200w();

2、慢查詢?nèi)罩镜臅r(shí)間

-- 設(shè)置慢查詢?nèi)罩緯r(shí)間
show variables like 'long_query_time';

-- 是否開(kāi)啟慢查詢?nèi)罩?show variables like 'slow_query%'

-- 慢查詢?nèi)罩韭窂?show variables like 'slow_query_log_file%';
將 slow_query_log 全局變量設(shè)置為“ON”/OFF狀態(tài) 
set global slow_query_log='ON';
設(shè)置慢查詢?志存放的位置
set global slow_query_log_file='/op/slow.log/';

二、修改時(shí)長(zhǎng)，查詢超過(guò)1秒就記錄 
set global long_query_time=1;
如何通過(guò)set命令無(wú)效就有可能my.ini配置文件中已經(jīng)配置了

設(shè)置日志存放的路徑
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、設(shè)置慢查詢?nèi)罩敬娣欧绞剑譃槲募捅韮煞N形式

og_output參數(shù)是什么意思？表示慢日志輸出到文件還是表中。

show global variables like 'log_output'；

默認(rèn)參數(shù)，log_output='FILE'，表示慢日志輸出到了文件中。

set global log_output='TABLE';

此時(shí)慢日志就輸出到了mysql數(shù)據(jù)庫(kù)的系統(tǒng)表中：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 代碼讀取日志文章來(lái)源地址http://www.zghlxwxcb.cn/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. 獲取數(shù)據(jù)庫(kù)連接
            Connection conn = DriverManager.getConnection(url, username, password);

            // 2. 創(chuàng)建一個(gè)Statement對(duì)象
            Statement stmt = conn.createStatement();
            // 3. 執(zhí)行SQL查詢，獲取結(jié)果集
            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. 處理結(jié)果集
            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. 關(guān)閉連接和Statement對(duì)象
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

到了這里，關(guān)于mysql 操作慢查詢?nèi)罩镜奈恼戮徒榻B完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！