首頁 > 軟體

一步步教你利用Mysql儲存過程造百萬級資料

2023-03-19 06:03:48

1.準備工作

(1)由於是使用儲存過程,mysql從5.0版開始支援儲存過程,那麼需要mysql的版本在5.0或者以上。如何檢視mysql的版本,使用下面sql語句檢視:

(2)建立兩張表,表結構一致,但使用的儲存引擎不一樣,如下所示,普通表使用mysql5.5版本後預設的INNODB儲存引擎,記憶體表使用MEMORY儲存引擎。

由於MEMORY儲存不常用這裡簡單說一下其特點:MEMORY引擎表結構建立在磁碟上,資料全部放在記憶體中,存取速度較快,但是當MySQL重啟後或者一旦系統奔潰的話,資料都會消失,結構還存在。

# 建立普通表
CREATE TABLE `user_info` (
    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `name` VARCHAR ( 30 ) NOT NULL COMMENT '使用者名稱',
    `phone` VARCHAR ( 11 ) NOT NULL COMMENT '手機號',
    `status` TINYINT ( 1 ) NULL DEFAULT NULL COMMENT '使用者狀態:停用0,啟動1',
    `create_time` datetime NOT NULL COMMENT '建立時間',
    PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB AUTO_INCREMENT = 10001 CHARACTER 
SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '使用者資訊表';
 
# 建立記憶體表
CREATE TABLE `memory_user_info` (
    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `name` VARCHAR ( 30 ) NOT NULL COMMENT '使用者名稱',
    `phone` VARCHAR ( 11 ) NOT NULL COMMENT '手機號',
    `status` TINYINT ( 1 ) NULL DEFAULT NULL COMMENT '使用者狀態:停用0,啟動1',
    `create_time` datetime NOT NULL COMMENT '建立時間',
    PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = MEMORY AUTO_INCREMENT = 10001 CHARACTER 
SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '使用者資訊記憶體表';

2.主要實現步驟

(1)建立自動生成資料的函數,插入時使用;

(2)建立插入記憶體表資料儲存過程,呼叫已建立好的資料生成函數;

(3)建立記憶體表資料插入普通表儲存過程;

(4)呼叫儲存過程。

(5)資料檢視驗證

3.建立自動生成資料的函數

(1)生成n個亂數字

DELIMITER //
DROP FUNCTION
IF
    EXISTS randomNum // CREATE FUNCTION randomNum (
        n INT,
        chars_str VARCHAR ( 10 )) RETURNS VARCHAR ( 255 ) BEGIN
    DECLARE
        return_str VARCHAR ( 255 ) DEFAULT '';
    DECLARE
        i INT DEFAULT 0;
    WHILE
            i < n DO
            
            SET return_str = concat(
                return_str,
            substring( chars_str, FLOOR( 1 + RAND()* 10 ), 1 ));
        
        SET i = i + 1;
        
    END WHILE;
    RETURN return_str;
    
END // 
DELIMITER;

函數執行截圖:

指令碼所用到的mysql函數及其功能如下:

a.concat():將多個字串連線成一個字串。

b.Floor():向下取整。

c.substring(string, position, length)

第一個引數:string指的是需要擷取的原字串。

第二個引數:position指的是從哪個位置開始擷取子字串,這裡字元的位置編碼序號是從1開始,若position為負數則從右往左開始數位置。

第三個引數:length指的是需要擷取的字串長度,如果不寫,則預設擷取從position開始到最後一位的所有字元。

d.RAND():只能生成0到1之間的隨機小數。

(2)建立隨機生成手機號函數

DELIMITER //
DROP FUNCTION
IF
    EXISTS getPhone // CREATE FUNCTION getPhone () RETURNS VARCHAR ( 11 ) BEGIN
    DECLARE
        head CHAR ( 3 );
    DECLARE
        phone VARCHAR ( 11 );
    DECLARE
        bodys VARCHAR ( 65 ) DEFAULT "130 131 132 133 134 135 136 137 138 139 186 187 189 151 157";
    DECLARE
        STARTS INT;
    
    SET STARTS = 1+floor ( rand()* 15 )* 4;
    
    SET head = trim(
    substring( bodys, STARTS, 3 ));
    
    SET phone = trim(
        concat(
            head,
        randomNum ( 8, '0123456789' )));
    RETURN phone;
    
END // 
DELIMITER;

函數執行截圖:

(3)建立隨機生成使用者名稱函數

DELIMITER //
DROP FUNCTION
IF
    EXISTS randName // CREATE FUNCTION randName ( n INT ) RETURNS VARCHAR ( 255 ) CHARSET utf8mb4 DETERMINISTIC BEGIN
    DECLARE
        chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE
        return_str VARCHAR ( 30 ) DEFAULT '';
    DECLARE
        i INT DEFAULT 0;
    WHILE
            i < n DO
            
            SET return_str = concat(
                return_str,
            substring( chars_str, FLOOR( 1 + RAND() * 62 ), 1 ));
        
        SET i = i + 1;
        
    END WHILE;
    RETURN return_str;
 
END // 
DELIMITER;

函數執行截圖:

(4)隨機生成使用者狀態函數

DELIMITER //
DROP FUNCTION
IF
    EXISTS randStatus // CREATE FUNCTION randStatus ( ) RETURNS TINYINT ( 1 ) BEGIN
    DECLARE
        user_status INT ( 1 ) DEFAULT 0;
    
    SET user_status =
    IF
        ( FLOOR( RAND() * 10 ) <= 4, 1, 0 );
    RETURN user_status;
 
END // 
DELIMITER;

函數執行截圖:

(5)檢視資料庫中所有自定義函數資訊

4.建立儲存過程

(1)建立插入記憶體表資料儲存過程

DELIMITER //
DROP FUNCTION
IF
    EXISTS randStatus // CREATE FUNCTION randStatus ( ) RETURNS TINYINT ( 1 ) BEGIN
    DECLARE
        user_status INT ( 1 ) DEFAULT 0;
    
    SET user_status =
    IF
        ( FLOOR( RAND() * 10 ) <= 4, 1, 0 );
    RETURN user_status;
 
END // 
DELIMITER;

入參n是多少就表示往記憶體表memory_user_info插入多少條資料

儲存過程執行截圖:

(2)建立記憶體表資料插入普通表儲存過程

DELIMITER //
DROP PROCEDURE
IF
    EXISTS add_user_info // CREATE PROCEDURE `add_user_info` ( IN n INT, IN count INT ) BEGIN
    DECLARE
        i INT DEFAULT 1;
    WHILE
            ( i <= n ) DO
            CALL add_memory_user_info ( count );
        INSERT INTO user_info SELECT
        * 
        FROM
            memory_user_info;
        DELETE 
        FROM
            memory_user_info;
        
        SET i = i + 1;
        
    END WHILE;
 
END // 
DELIMITER;

這是最主要的儲存過程,也是入口,利用對記憶體表的迴圈插入和刪除來實現批次生成資料,不需要更改mysql預設的max_heap_table_size值(預設值是16M),max_heap_table_size 的作用是設定使用者建立記憶體臨時表的大小,設定的值越大,能存進記憶體表的資料就越多。

儲存過程執行截圖:

(3)檢視儲存過程的狀態

-- 檢視資料庫所有的儲存過程
SHOW PROCEDURE STATUS;
-- 模糊查詢儲存過程
SHOW PROCEDURE STATUS LIKE 'add%';

模糊查詢結果:

5.呼叫儲存過程

mysql稱儲存過程的執行為呼叫,因此mysql執行儲存過程的語句為CALL。CALL接受儲存過程的名字以及需要傳遞給它的任意引數。

通過呼叫add_user_info儲存過程,不斷迴圈插入記憶體表memory_user_info,再從記憶體表獲取資料插入普通表user_info,然後刪除記憶體表資料,以此迴圈直至迴圈結束。迴圈100次,每次生成10000條資料,共生成一百萬條資料。

CALL add_user_info(100,10000);

6.資料檢視驗證

在普通表資料達到6萬條時,已經耗時大概在23分鐘左右,以這個時間推算,100萬資料生成預計需要6小時左右。耗時的點主要是在四個隨機生成欄位資料的函數上。如果欄位資料不要求隨機,那麼將會快很多。

資料記錄如下效果:

總結

到此這篇關於一步步教你利用Mysql儲存過程造百萬級資料的文章就介紹到這了,更多相關Mysql儲存過程造百萬級資料內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!


IT145.com E-mail:sddin#qq.com