MySql || 快速創建100w條記錄

平時每個開發者都會討論數據量大時，sql的優化問題。但是並不是每個人都會有100w的數據量可以用來實戰，那麼今天我們就自己動手，模擬一個100w數據量的表。

• 創建原理

其實創建的方法有很多，有快的也有慢的。本博客中寫的當然不是最快的那個，但確實是比較好操作和理解的。那麼我先來說明一下它的原理：它是利用mysql中的在MEMORY引擎的特點，用於快速的插入100w的數據在記憶體中存放，然後再利用sql插入到目標的表當中。

• 操作步驟

1. 創建表t_user，這是用於存放數據的表。

 1 CREATE TABLE `t_user` (
 2   `id` int(11) NOT NULL AUTO_INCREMENT,
 3   `c_user_id` varchar(36) NOT NULL DEFAULT '',
 4   `c_name` varchar(22) NOT NULL DEFAULT '',
 5   `c_province_id` int(11) NOT NULL,
 6   `c_city_id` int(11) NOT NULL,
 7   `create_time` datetime NOT NULL,
 8   PRIMARY KEY (`id`) 9 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

　　2.創建記憶體表t_user_memory，這是用於快速插入數據的表。

1 CREATE TABLE `t_user_memory` (
2   `id` int(11) NOT NULL AUTO_INCREMENT,
3   `c_user_id` varchar(36) NOT NULL DEFAULT '',
4   `c_name` varchar(22) NOT NULL DEFAULT '',
5   `c_province_id` int(11) NOT NULL,
6   `c_city_id` int(11) NOT NULL,
7   `create_time` datetime NOT NULL,
8   PRIMARY KEY (`id`)
9 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

　　3.創建隨機字元串函數randStr()，用於在給c_name賦值時更加隨機。

delimiter $$
CREATE DEFINER=`root`@`%` FUNCTION `randStr`(n INT) RETURNS varchar(255) CHARSET utf8mb4
DETERMINISTIC
BEGIN
 DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
 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() * 62), 1));
        SET i=i+1;
END WHILE;
RETURN return_str;
END $$

這個函數的語法不用解釋也會看得懂，但是還是有幾個點需要註意的。

1. delimiter關鍵字，這是一個聲明結束符的關鍵字。簡單的來說就是，防止和BEGIN和END之間的語句衝突，因為我們希望這裡面的語句是連續執行的。
2. CONCAT(str,str)鏈接兩個字元串組成一個新的字元串。
3. FLOOR(num)函數，向下取整。例如floor(1.23) = 1 、floor(-1.23) = -2。

　　4.創建隨機時間函數randDataTime()，為了後期的時間更加隨機。

 1 delimiter $$
 2 CREATE DEFINER = `root`@`%` FUNCTION `randDataTime` (sd DATETIME, ed DATETIME) RETURNS datetime DETERMINISTIC
 3 BEGIN
 4     DECLARE
 5         sub INT DEFAULT 0 ; 
 6     DECLARE
 7             ret DATETIME ;
 8         SET sub = ABS(
 9             UNIX_TIMESTAMP(ed) - UNIX_TIMESTAMP(sd)
10         ) ;
11         SET ret = DATE_ADD(
12             sd,
13             INTERVAL FLOOR(1 + RAND() *(sub - 1)) SECOND
14         ) ; 
15     RETURN ret ; 
16 END $$

裡面的需要註意的是DATE_ADD(dateTime,INTERVAL  num  SECOND)函數，第一個參數是被添加的時間，第二個參數是一個組合，表明是添加的長度，如：（INTERVAL 1  YEAR）表示在原來的基礎上添加一年的時間間隔。

　　5.創建插入數據存儲過程add_t_user_memory (int)

 1 delimiter $$
 2 CREATE DEFINER=`root`@`%` PROCEDURE add_t_user_memory (IN n INT)
 3 BEGIN
 4     DECLARE
 5         i INT DEFAULT 1 ;
 6     WHILE i < n DO
 7         INSERT INTO t_user_memory (
 8             c_user_id,
 9             c_name,
10             c_province_id,
11             c_city_id,
12             create_time
13         )
14     VALUES
15         (
16             uuid(),
17             randStr (20),
18             FLOOR(RAND() * 1000),
19             RAND() * 100,
20             NOW()
21         );
22 SET i = i + 1 ;
23 END
24 WHILE ;
25 END $$

• 執行過程中的問題

在完成上面的聲明之後我們就可以用CALL  add_t_user_memory (1000000)來創建100w的數據啦，這大概需要20分鐘的時間。但是在調用的過程中卻發現了一個問題如下圖所示。

原來是't_user_memory' 已經滿了。它是我在上面定義的用記憶體存放數據的表，在MYSQL中預設的大小是16MB。這個大小隻能放4w多條數據，所以我們要想辦法把它擴大。那麼可以執行下麵語句

1 SET GLOBAL max_heap_table_size = 1024 * 1024 * 1024 * 1;
2 
3 #查看當前的設置的大小
4 select @@max_heap_table_size;

註意：修改需要重新鏈接mysql才能更新修改。這個修改自己機器上玩玩就可以了，在生產環境這麼改有什麼後果我可不負責。

• 完善資料庫

1. 執行下麵語句，大概需要10s就可以插入到t_user中。

INSERT INTO t_user SELECT * FROM t_user_memory;

　　2.打亂創建時間。

#更新年間隔
UPDATE t_user SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 4)) year);
#更新秒間隔
UPDATE t_user SET create_time=randDataTime(NOW(),create_time);

• 完成

這樣，我們的百萬級資料庫就創建完成啦！然後，我們可以將t_user_memory這個表清空，畢竟它是很占記憶體的，你數據有多少記憶體就占多少。下次再結合業務試試SQL優化怎麼玩。