教你幾招,快速制造五百萬級(jí) MySQL 數(shù)據(jù)

如果你打算好好學(xué)習(xí)一下 MySQL，性能優(yōu)化肯定是繞不過去一個(gè)問題。當(dāng)你擼起袖子準(zhǔn)備開始的時(shí)候，突然發(fā)現(xiàn)一個(gè)問題擺在眼前，本地?cái)?shù)據(jù)庫中沒那么大的數(shù)據(jù)量啊，幾條數(shù)據(jù)優(yōu)化個(gè)毛線啊。生產(chǎn)庫里數(shù)據(jù)多，但誰敢直接在生產(chǎn)環(huán)境動(dòng)手啊，想被提前優(yōu)化嗎？

要知道，程序員從不輕言放棄，沒有數(shù)據(jù)我們就自己創(chuàng)造數(shù)據(jù)嘛，new 對(duì)象這種事情可是我們的拿手好戲，對(duì)象都能 new 出來，更別說幾百萬條數(shù)據(jù)了。

使用官方數(shù)據(jù)

官方顯然知道我們需要一些測試數(shù)據(jù)做個(gè)練習(xí)什么的，所以準(zhǔn)備了一份測試數(shù)據(jù)給我們。可以到 https://github.com/datacharmer/test_db 上去下載，這個(gè)數(shù)據(jù)庫包含約30萬條員工記錄和280萬個(gè)薪水條目，文件大小為 167 M。

下載完成之后，直接使用 MySQL 客戶端運(yùn)行 sql 文件即可。

或者直接使用命令，然后輸入密碼導(dǎo)入。

mysql?-u?root?-p?

這是最簡單的一種方法，只要你能把 sql 文件下載下來就可以了。但是數(shù)據(jù)量不夠大，員工表才 30 萬條數(shù)據(jù)，還不夠百萬級(jí)別，而且字段都是定義好的，不能靈活定制。

背景說明

創(chuàng)建百萬級(jí)數(shù)據(jù)的方式，要到達(dá)的目的有兩點(diǎn)：

1. 定制比較靈活，不能只是一兩個(gè)字段了事，那沒什么實(shí)際意義。
2. 速度快，不能說弄個(gè)幾百萬數(shù)據(jù)好幾個(gè)小時(shí)甚至更長，那不能接收。

本次目標(biāo)是創(chuàng)建兩個(gè)表，一個(gè)用戶表，另外一個(gè)訂單表，當(dāng)然沒有真實(shí)環(huán)境中的表字段那么多，但是對(duì)于學(xué)習(xí)測試來說差不多夠了。

兩個(gè)表的表結(jié)構(gòu)如下：

# 用戶表
CREATE TABLE `user` (
  `id` varchar(36) NOT NULL,
  `user_name` varchar(12) DEFAULT NULL,
  `age` tinyint(3) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `province` varchar(10) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 訂單表
CREATE TABLE `order` (
  `id` varchar(36) NOT NULL,
  `user_id` varchar(36) DEFAULT NULL,
  `product_count` int(11) DEFAULT NULL,
  `price` decimal(10,0) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

用戶表（user）創(chuàng)建 500 萬條數(shù)據(jù)，id 使用 uuid，年齡從 1 到 120 隨機(jī)，電話號(hào)碼隨機(jī) 11 位，省份編碼和城市編碼隨機(jī)，創(chuàng)建時(shí)間和更新時(shí)間在某一時(shí)間范圍內(nèi)隨機(jī)。

訂單表（order）根據(jù)用戶表生成，每個(gè)用戶隨機(jī)生成 0 到 3 個(gè)訂單，訂單編號(hào)采用 uuid，商品數(shù)量隨機(jī) 1 到 5 個(gè)，價(jià)格隨機(jī)，創(chuàng)建時(shí)間和更新時(shí)間在某一時(shí)間段內(nèi)隨機(jī)。由于每個(gè)用戶產(chǎn)生 0 到 3 個(gè)訂單，所以，產(chǎn)生的訂單量應(yīng)該大于 500 萬，我在本地跑的時(shí)候基本上在 700多萬左右。

創(chuàng)建總時(shí)間和表的字段個(gè)數(shù)以及字段的生成算法有直接關(guān)系，字段越多、算法越復(fù)雜，需要的時(shí)間就越多，比如使用 uuid 就比使用自增 id 花費(fèi)更長時(shí)間，隨機(jī)時(shí)間就比直接使用當(dāng)前時(shí)間花費(fèi)更長時(shí)間。

如果只插入 500 萬自增 id 這一個(gè)字段，十幾秒就能完成，但是無論是模擬線上環(huán)境還是自學(xué)性能優(yōu)化技巧都沒什么意義。

下面就來介紹三種方式來快速創(chuàng)建 500 萬用戶數(shù)據(jù)以及大于 500 萬的訂單數(shù)據(jù)。

寫程序批量插入

作為一個(gè)開發(fā)人員，當(dāng)你打算創(chuàng)建百萬條數(shù)據(jù)的時(shí)候，大多數(shù)時(shí)候首先相當(dāng)?shù)膽?yīng)該就是寫程序，畢竟 CURD 我們最拿手了。

用程序的方式插入也分兩種情況，第一種就是逐條插入，這也是平時(shí)開發(fā)中最常用到的方法，直覺上我們可能會(huì)認(rèn)為這樣比較快。事實(shí)上并不是這樣，雖然比起手動(dòng)一條一條插入是快的多，但是，很有可能你在等待了一段時(shí)間后失去耐心，然后結(jié)束程序，不管你用哪種數(shù)據(jù)庫連接池都一樣，在百萬數(shù)量級(jí)面前仍然慢的離譜。

第二種情況就是使用 MySQL 的批量插入方法，我們都知道 MySQL 支持一次性插入多條記錄，就是下面這樣的形式。

insert into `table_name` (id,column1) values (1,'value1'),(2,'value2'),(3,'value3');

這樣一來，比你一條一條語句執(zhí)行要快很多，比如 1000 條記錄執(zhí)行一次 insert，一共執(zhí)行 5000 次即可，如果是一條一條插入呢，那就要執(zhí)行 500 萬次。

由于后面兩種方式用到了 Python 生成文件，所以這種方式也用了 Python 實(shí)現(xiàn)，實(shí)例代碼如下。完整代碼可在文末給出的 github 上獲取。

def?insert_data(self):
??cursor?=?self.conn.cursor()
??for?x?in?range(5000):
????insert_user_sql?=?"""
????????????insert?into?`user`?(?`id`,`user_name`,`phone`,`age`,?`province`,?`city`,?`create_time`,`update_time`?)
????????????????????VALUES(%s,%s,%s,%s,%s,%s,%s,%s)
????????????????"""
????insert_order_sql?=?"""?insert?into?`order`?(?`id`,?`product_count`,?`user_id`,?`price`,?`create_time`,?`update_time`)?
???????????????????????????????values(%s,%s,%s,%s,%s,%s)
???????????????????????????????"""
????user_values,?order_values?=?[],?[]
????for?i?in?range(1000):
??????timestamp?=?self.randomTimestamp()
??????time_local?=?time.localtime(timestamp)
??????createTime?=?time.strftime("%Y-%m-%d?%H:%M:%S",?time_local)
??????user_id?=?str(uuid.uuid4())
??????user_values.append(
????????(user_id,?"名字"?+?str(x)?+?str(i),?self.createPhone(),?random.randint(1,?120),
?????????str(random.randint(1,?26)),
?????????str(random.randint(1,?1000)),?createTime,?createTime))

??????random_order_count?=?random.randint(0,?3)
??????if?random_order_count?>?0:
????????for?c?in?range(random_order_count):
??????????timestamp?=?self.randomTimestamp()
??????????time_local?=?time.localtime(timestamp)
??????????order_create_time?=?time.strftime("%Y-%m-%d?%H:%M:%S",?time_local)
??????????order_values.append((str(uuid.uuid4()),?random.randint(1,?5),?user_id,
???????????????????????????????random.randint(10,?2000),?order_create_time,?order_create_time))
??????????cursor.executemany(insert_user_sql,?user_values)
??????????cursor.executemany(insert_order_sql,?order_values)
??????????self.conn.commit()

??????????cursor.close()

經(jīng)過一段時(shí)間時(shí)間的等待后，運(yùn)行完成了，整個(gè)運(yùn)行過程耗時(shí) 1823 秒，30分鐘。

最后成功生成用戶記錄 500 萬條，訂單記錄 749 萬多條。

速度還算能接受吧，馬馬虎虎吧。

再想速度快一點(diǎn)，可以開多線程，我用 5 個(gè)線程跑了一下，一個(gè)線程插入 100萬條，最終最長的線程耗時(shí) 1294秒，21分鐘，也沒快多少，線程個(gè)數(shù)對(duì)時(shí)間多少有些影響，但是我沒有試。

生成 SQL 腳本

這種方式和上面的方式類似，只不過上面通過程序方式直接將拼接出來的 SQL 語句執(zhí)行了，而這種方式是將拼接好的 SQL 語句寫入文件中。當(dāng)然還是以一條語句插入多行記錄的形式。

insert into `table_name` (id,column1) values (1,'value1'),(2,'value2'),(3,'value3');

寫 500 萬用戶數(shù)據(jù)，加上隨機(jī)的訂單數(shù)據(jù)， sql 文件的過程耗時(shí)為 696 秒，11分鐘左右。

當(dāng)然這么大數(shù)據(jù)量拼接出來的腳本文件也很大，用戶表腳本 680 多M，訂單表腳本 1個(gè)G。

最后將寫好的這兩個(gè)文件分別在 MySQL 中執(zhí)行。

執(zhí)行用戶表腳本，耗時(shí) 3 分鐘左右。

mysql?-uroot?-p?mast_slave?

執(zhí)行訂單表腳本，耗時(shí) 7 分鐘左右，訂單量 750 多萬個(gè)。

mysql?-uroot?-p?mast_slave?

一共耗時(shí)，20分鐘左右，加上中間的手工操作，感覺不如第一種方法中的多線程方式省事。

load data infile 方式

最后這種方式是使用 load data infile 方式，這是 MySQL 提供的一種從文件快速導(dǎo)入的方式。比如按照特定符號(hào)分隔，導(dǎo)入對(duì)應(yīng)的字段中。

本文例子中我是按照逗號(hào)分隔的，字段之間以逗號(hào)分隔，生成 500 萬條用戶行 和隨機(jī)訂單行。

依然是用 Python 腳本生成文件，生成文件的過程耗時(shí) 779 秒，12分鐘左右。

兩個(gè)文件大小分別是 560 多M 和 900 M。

最后執(zhí)行 load data infile 將文件導(dǎo)入到對(duì)應(yīng)的表中，在執(zhí)行這個(gè)命令后可能會(huì)出現(xiàn)下面這個(gè)錯(cuò)誤提示。

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

這是因?yàn)?MySQL 自身的安全配置所致，需要更改 my.cnf，在其中加入下面的配置，然后重啟服務(wù)。

secure_file_priv=

等于號(hào)后邊為空表示允許所有目錄下的文件 load，如果要限定某個(gè)特定目錄，在等于號(hào)后邊填上對(duì)應(yīng)的文件目錄即可。

然后執(zhí)行下面的語句，將用戶記錄導(dǎo)入到 user 表。

load data infile '/Users/fengzheng/知識(shí)管理/技術(shù)寫作/mysql/創(chuàng)建測試數(shù)據(jù)/sql/load_user_txt_500w.txt' replace into table user FIELDS TERMINATED BY ',';

500萬條耗時(shí) 3分32秒。

將訂單記錄導(dǎo)入到 order 表。

load data infile '/Users/fengzheng/知識(shí)管理/技術(shù)寫作/mysql/創(chuàng)建測試數(shù)據(jù)/sql/load_order_txt_500w+.txt' replace into table `order` FIELDS TERMINATED BY ',';

749 萬條記錄，耗時(shí) 8分31秒。

整個(gè)過程加起來 24 分鐘左右。

最后

好了，現(xiàn)在可以愉快的做各種測試和優(yōu)化了。

有同學(xué)看完可能要說了，20多分鐘好像也不算快啊。因?yàn)閿?shù)據(jù)量確實(shí)比較大，再有數(shù)據(jù)復(fù)雜度和導(dǎo)入時(shí)間也有很大關(guān)系，如果你只是導(dǎo)入一列自增id，別說 500 萬，1000萬都用不了一分鐘就完成了。

其實(shí)還有一點(diǎn)優(yōu)化空間的，比如說把數(shù)據(jù)庫引擎改成 MYISAM 會(huì)更快一些，尤其是對(duì)于批量插入的情景，但是插入完成后還要再改回來，也需要耗費(fèi)一些時(shí)間，而且來回切換也比較麻煩。

點(diǎn)個(gè)在看支持我吧，轉(zhuǎn)發(fā)就更好了