快捷導(dǎo)航

一步步教你利用Mysql存儲過程造百萬級數(shù)據(jù)

更新時間：2023年03月18日 10:03:13 作者：自律的西瓜L

因工作需要維護(hù)一張中建表數(shù)據(jù)內(nèi)置,所以得造數(shù)據(jù)所以使用存儲過程來造數(shù)據(jù),下面這篇文章主要給大家介紹了關(guān)于如何一步步利用Mysql存儲過程造百萬級數(shù)據(jù)的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下

1.準(zhǔn)備工作

（1）由于是使用存儲過程，mysql從5.0版開始支持存儲過程，那么需要mysql的版本在5.0或者以上。如何查看mysql的版本，使用下面sql語句查看：

（2）創(chuàng)建兩張表，表結(jié)構(gòu)一致，但使用的存儲引擎不一樣，如下所示，普通表使用mysql5.5版本后默認(rèn)的INNODB存儲引擎，內(nèi)存表使用MEMORY存儲引擎。

由于MEMORY存儲不常用這里簡單說一下其特點：MEMORY引擎表結(jié)構(gòu)創(chuàng)建在磁盤上，數(shù)據(jù)全部放在內(nèi)存中，訪問速度較快，但是當(dāng)MySQL重啟后或者一旦系統(tǒng)奔潰的話，數(shù)據(jù)都會消失，結(jié)構(gòu)還存在。

# 創(chuàng)建普通表
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 '手機(jī)號',
    `status` TINYINT ( 1 ) NULL DEFAULT NULL COMMENT '用戶狀態(tài):停用0，啟動1',
    `create_time` datetime NOT NULL COMMENT '創(chuàng)建時間',
    PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB AUTO_INCREMENT = 10001 CHARACTER 
SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶信息表';
 
# 創(chuàng)建內(nèi)存表
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 '手機(jī)號',
    `status` TINYINT ( 1 ) NULL DEFAULT NULL COMMENT '用戶狀態(tài):停用0，啟動1',
    `create_time` datetime NOT NULL COMMENT '創(chuàng)建時間',
    PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = MEMORY AUTO_INCREMENT = 10001 CHARACTER 
SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶信息內(nèi)存表';

2.主要實現(xiàn)步驟

（1）創(chuàng)建自動生成數(shù)據(jù)的函數(shù)，插入時使用；

（2）創(chuàng)建插入內(nèi)存表數(shù)據(jù)存儲過程，調(diào)用已創(chuàng)建好的數(shù)據(jù)生成函數(shù)；

（3）創(chuàng)建內(nèi)存表數(shù)據(jù)插入普通表存儲過程；

（4）調(diào)用存儲過程。

（5）數(shù)據(jù)查看驗證

3.創(chuàng)建自動生成數(shù)據(jù)的函數(shù)

（1）生成n個隨機(jī)數(shù)字

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;

函數(shù)運行截圖：

腳本所用到的mysql函數(shù)及其功能如下：

a.concat()：將多個字符串連接成一個字符串。

b.Floor()：向下取整。

c.substring(string, position, length)

第一個參數(shù)：string指的是需要截取的原字符串。

第二個參數(shù)：position指的是從哪個位置開始截取子字符串，這里字符的位置編碼序號是從1開始，若position為負(fù)數(shù)則從右往左開始數(shù)位置。

第三個參數(shù)：length指的是需要截取的字符串長度，如果不寫，則默認(rèn)截取從position開始到最后一位的所有字符。

d.RAND()：只能生成0到1之間的隨機(jī)小數(shù)。

（2）創(chuàng)建隨機(jī)生成手機(jī)號函數(shù)

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;

函數(shù)運行截圖：

（3)創(chuàng)建隨機(jī)生成用戶名函數(shù)

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;

函數(shù)運行截圖：

（4）隨機(jī)生成用戶狀態(tài)函數(shù)

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;

函數(shù)運行截圖：

（5）查看數(shù)據(jù)庫中所有自定義函數(shù)信息

4.創(chuàng)建存儲過程

（1）創(chuàng)建插入內(nèi)存表數(shù)據(jù)存儲過程

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;

入?yún)是多少就表示往內(nèi)存表memory_user_info插入多少條數(shù)據(jù)

存儲過程運行截圖：

（2）創(chuàng)建內(nèi)存表數(shù)據(jù)插入普通表存儲過程

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;

這是最主要的存儲過程，也是入口，利用對內(nèi)存表的循環(huán)插入和刪除來實現(xiàn)批量生成數(shù)據(jù)，不需要更改mysql默認(rèn)的max_heap_table_size值（默認(rèn)值是16M），max_heap_table_size 的作用是配置用戶創(chuàng)建內(nèi)存臨時表的大小，配置的值越大，能存進(jìn)內(nèi)存表的數(shù)據(jù)就越多。

存儲過程運行截圖：

（3）查看存儲過程的狀態(tài)

-- 查看數(shù)據(jù)庫所有的存儲過程
SHOW PROCEDURE STATUS;
-- 模糊查詢存儲過程
SHOW PROCEDURE STATUS LIKE 'add%';

模糊查詢結(jié)果：

5.調(diào)用存儲過程

mysql稱存儲過程的執(zhí)行為調(diào)用，因此mysql執(zhí)行存儲過程的語句為CALL。CALL接受存儲過程的名字以及需要傳遞給它的任意參數(shù)。

通過調(diào)用add_user_info存儲過程，不斷循環(huán)插入內(nèi)存表memory_user_info，再從內(nèi)存表獲取數(shù)據(jù)插入普通表user_info，然后刪除內(nèi)存表數(shù)據(jù)，以此循環(huán)直至循環(huán)結(jié)束。循環(huán)100次，每次生成10000條數(shù)據(jù)，共生成一百萬條數(shù)據(jù)。

CALL add_user_info(100,10000);

6.數(shù)據(jù)查看驗證

在普通表數(shù)據(jù)達(dá)到6萬條時，已經(jīng)耗時大概在23分鐘左右，以這個時間推算，100萬數(shù)據(jù)生成預(yù)計需要6小時左右。耗時的點主要是在四個隨機(jī)生成字段數(shù)據(jù)的函數(shù)上。如果字段數(shù)據(jù)不要求隨機(jī)，那么將會快很多。