一步步教你利用Mysql存儲(chǔ)過程造百萬(wàn)級(jí)數(shù)據(jù)
1.準(zhǔn)備工作
（1）由于是使用存儲(chǔ)過程，mysql從5.0版開始支持存儲(chǔ)過程，那么需要mysql的版本在5.0或者以上。如何查看mysql的版本，使用下面sql語(yǔ)句查看：
（2）創(chuàng)建兩張表，表結(jié)構(gòu)一致，但使用的存儲(chǔ)引擎不一樣，如下所示，普通表使用mysql5.5版本后默認(rèn)的INNODB存儲(chǔ)引擎，內(nèi)存表使用MEMORY存儲(chǔ)引擎。
由于MEMORY存儲(chǔ)不常用這里簡(jiǎn)單說一下其特點(diǎn)：MEMORY引擎表結(jié)構(gòu)創(chuàng)建在磁盤上，數(shù)據(jù)全部放在內(nèi)存中，訪問速度較快，但是當(dāng)MySQL重啟后或者一旦系統(tǒng)奔潰的話，數(shù)據(jù)都會(huì)消失，結(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ī)號(hào)',
`status` TINYINT ( 1 ) NULL DEFAULT NULL COMMENT '用戶狀態(tài):停用0，啟動(dòng)1',
`create_time` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間',
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ī)號(hào)',
`status` TINYINT ( 1 ) NULL DEFAULT NULL COMMENT '用戶狀態(tài):停用0，啟動(dòng)1',
`create_time` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間',
PRIMARY KEY ( `id` ) USING BTREE
) ENGINE = MEMORY AUTO_INCREMENT = 10001 CHARACTER
SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶信息內(nèi)存表';2.主要實(shí)現(xiàn)步驟
（1）創(chuàng)建自動(dòng)生成數(shù)據(jù)的函數(shù)，插入時(shí)使用；
（2）創(chuàng)建插入內(nèi)存表數(shù)據(jù)存儲(chǔ)過程，調(diào)用已創(chuàng)建好的數(shù)據(jù)生成函數(shù)；
（3）創(chuàng)建內(nèi)存表數(shù)據(jù)插入普通表存儲(chǔ)過程；
（4）調(diào)用存儲(chǔ)過程。
（5）數(shù)據(jù)查看驗(yàn)證
3.創(chuàng)建自動(dòng)生成數(shù)據(jù)的函數(shù)
（1）生成n個(gè)隨機(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ù)運(yùn)行截圖：
腳本所用到的mysql函數(shù)及其功能如下：
a.concat()：將多個(gè)字符串連接成一個(gè)字符串。
b.Floor()：向下取整。
c.substring(string, position, length)
第一個(gè)參數(shù)：string指的是需要截取的原字符串。
第二個(gè)參數(shù)：position指的是從哪個(gè)位置開始截取子字符串，這里字符的位置編碼序號(hào)是從1開始，若position為負(fù)數(shù)則從右往左開始數(shù)位置。
第三個(gè)參數(shù)：length指的是需要截取的字符串長(zhǎng)度，如果不寫，則默認(rèn)截取從position開始到最后一位的所有字符。
d.RAND()：只能生成0到1之間的隨機(jī)小數(shù)。
（2）創(chuàng)建隨機(jī)生成手機(jī)號(hào)函數(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ù)運(yùn)行截圖：
（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ù)運(yùn)行截圖：
（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ù)運(yùn)行截圖：
（5）查看數(shù)據(jù)庫(kù)中所有自定義函數(shù)信息
4.創(chuàng)建存儲(chǔ)過程
（1）創(chuàng)建插入內(nèi)存表數(shù)據(jù)存儲(chǔ)過程
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ù)
存儲(chǔ)過程運(yùn)行截圖：
（2）創(chuàng)建內(nèi)存表數(shù)據(jù)插入普通表存儲(chǔ)過程
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;這是最主要的存儲(chǔ)過程，也是入口，利用對(duì)內(nèi)存表的循環(huán)插入和刪除來實(shí)現(xiàn)批量生成數(shù)據(jù)，不需要更改mysql默認(rèn)的max_heap_table_size值（默認(rèn)值是16M），max_heap_table_size 的作用是配置用戶創(chuàng)建內(nèi)存臨時(shí)表的大小，配置的值越大，能存進(jìn)內(nèi)存表的數(shù)據(jù)就越多。
存儲(chǔ)過程運(yùn)行截圖：
（3）查看存儲(chǔ)過程的狀態(tài)
-- 查看數(shù)據(jù)庫(kù)所有的存儲(chǔ)過程 SHOW PROCEDURE STATUS; -- 模糊查詢存儲(chǔ)過程 SHOW PROCEDURE STATUS LIKE 'add%';
模糊查詢結(jié)果：
5.調(diào)用存儲(chǔ)過程
mysql稱存儲(chǔ)過程的執(zhí)行為調(diào)用，因此mysql執(zhí)行存儲(chǔ)過程的語(yǔ)句為CALL。CALL接受存儲(chǔ)過程的名字以及需要傳遞給它的任意參數(shù)。
通過調(diào)用add_user_info存儲(chǔ)過程，不斷循環(huán)插入內(nèi)存表memory_user_info，再?gòu)膬?nèi)存表獲取數(shù)據(jù)插入普通表user_info，然后刪除內(nèi)存表數(shù)據(jù)，以此循環(huán)直至循環(huán)結(jié)束。循環(huán)100次，每次生成10000條數(shù)據(jù)，共生成一百萬(wàn)條數(shù)據(jù)。
CALL add_user_info(100,10000);
6.數(shù)據(jù)查看驗(yàn)證
在普通表數(shù)據(jù)達(dá)到6萬(wàn)條時(shí)，已經(jīng)耗時(shí)大概在23分鐘左右，以這個(gè)時(shí)間推算，100萬(wàn)數(shù)據(jù)生成預(yù)計(jì)需要6小時(shí)左右。耗時(shí)的點(diǎn)主要是在四個(gè)隨機(jī)生成字段數(shù)據(jù)的函數(shù)上。如果字段數(shù)據(jù)不要求隨機(jī)，那么將會(huì)快很多。
數(shù)據(jù)記錄如下效果：
總結(jié)
到此這篇關(guān)于一步步教你利用Mysql存儲(chǔ)過程造百萬(wàn)級(jí)數(shù)據(jù)的文章就介紹到這了,更多相關(guān)Mysql存儲(chǔ)過程造百萬(wàn)級(jí)數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
- MySQL數(shù)據(jù)庫(kù)10秒內(nèi)插入百萬(wàn)條數(shù)據(jù)的實(shí)現(xiàn)
- MySQL 百萬(wàn)級(jí)數(shù)據(jù)的4種查詢優(yōu)化方式
- MySQL百萬(wàn)級(jí)數(shù)據(jù)量分頁(yè)查詢方法及其優(yōu)化建議
- MySQL百萬(wàn)級(jí)數(shù)據(jù)分頁(yè)查詢優(yōu)化方案
- java中JDBC實(shí)現(xiàn)往MySQL插入百萬(wàn)級(jí)數(shù)據(jù)的實(shí)例代碼
- MySQL單表百萬(wàn)數(shù)據(jù)記錄分頁(yè)性能優(yōu)化技巧
- MySQL使用MyFlash快速恢復(fù)誤刪除和修改的數(shù)據(jù)
- MySQL數(shù)據(jù)庫(kù)刪除數(shù)據(jù)后自增ID不連續(xù)的問題及解決
- MySQL BinLog如何恢復(fù)誤更新刪除數(shù)據(jù)
- 使用 SQL 快速刪除數(shù)百萬(wàn)行數(shù)據(jù)的實(shí)踐記錄
相關(guān)文章
MySQL 8.0中官方移除了proc表,MySQL 5.7版本中還是存在proc表的,本文就介紹MySQL 8.0的替代方案,具有一定的參考價(jià)值,感興趣的可以了解一下2023-10-10
MySQL數(shù)據(jù)庫(kù)自連接實(shí)例講解
針對(duì)相同的表進(jìn)行的連接被稱為"自連接"(self?join),下面這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫(kù)自連接實(shí)例講解的相關(guān)資料,文中通過圖文以及實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-06-06
詳解mysql 獲取某個(gè)時(shí)間段每一天、每一個(gè)小時(shí)的統(tǒng)計(jì)數(shù)據(jù)
這篇文章主要介紹了mysql 獲取某個(gè)時(shí)間段每一天、每一個(gè)小時(shí)的統(tǒng)計(jì)數(shù)據(jù)，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04
本文主要介紹了MySQL實(shí)現(xiàn)查詢分位值的示例代碼，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-04-04
這篇文章主要介紹了MySQL慢日志實(shí)踐小結(jié)，小編覺得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2019-01-01
這篇文章主要介紹了Windows平臺(tái)下安裝與配置MySQL9的方法,本文通過圖文并茂的形式給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧2024-09-09
MySQL創(chuàng)建定時(shí)任務(wù)實(shí)例(每天凌晨1點(diǎn)、每小時(shí)、每分鐘、某一時(shí)間點(diǎn))
在mysql中有時(shí)候要定時(shí)更新或者刪除一部分?jǐn)?shù)據(jù)需要用到mysql的定時(shí)任務(wù),下面這篇文章主要給大家介紹了關(guān)于MySQL創(chuàng)建定時(shí)任務(wù)的相關(guān)資料,包括每天凌晨1點(diǎn)、每小時(shí)、每分鐘、某一時(shí)間點(diǎn)等,需要的朋友可以參考下2023-03-03
這篇文章主要介紹了MySQL 字符串拆分操作(含分隔符的字符串截取)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-02-02
mysql和oracle默認(rèn)排序的方法 - 不指定order by
這篇文章主要介紹了mysql和oracle默認(rèn)排序的方法 - 不指定order by。具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-07-07
MySQL與PHP的基礎(chǔ)與應(yīng)用專題之索引
MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL?AB?公司開發(fā)，屬于?Oracle?旗下產(chǎn)品。MySQL?是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一，本系列將帶你掌握php與mysql的基礎(chǔ)應(yīng)用，本篇從索引開始2022-02-02