我是風箏，公眾號「古時的風箏」，一個兼具深度與廣度的程式設計師鼓勵師，一個本打算寫詩卻寫起了程式碼的田園碼農！
文章會收錄在 JavaNewBee 中，更有 Java 後端知識圖譜，從小白到大牛要走的路都在裡面。
如果你打算好好學習一下 MySQL，效能優化肯定是繞不過去一個問題。當你擼起袖子準備開始的時候，突然發現一個問題擺在眼前，本地資料庫中沒那麼大的資料量啊，幾條資料優化個毛線啊。生產庫裡資料多，但誰敢直接在生產環境動手啊，想被提前優化嗎？
要知道，程式設計師從不輕言放棄，沒有資料我們就自己創造資料嘛，new 物件這種事情可是我們的拿手好戲，物件都能 new 出來，更別說幾百萬條資料了。
使用官方資料
官方顯然知道我們需要一些測試資料做個練習什麼的，所以準備了一份測試資料給我們。可以到 https://github.com/datacharmer/test_db 上去下載，這個資料庫包含約30萬條員工記錄和280萬個薪水條目，檔案大小為 167 M。
下載完成之後，直接使用 MySQL 客戶端執行 sql 檔案即可。
或者直接使用命令，然後輸入密碼匯入。
mysql -u root -p < employees.sql
這是最簡單的一種方法，只要你能把 sql 檔案下載下來就可以了。但是資料量不夠大，員工表才 30 萬條資料，還不夠百萬級別，而且欄位都是定義好的，不能靈活定製。
背景說明
建立百萬級資料的方式，要到達的目的有兩點：
- 定製比較靈活，不能只是一兩個欄位了事，那沒什麼實際意義。
- 速度快，不能說弄個幾百萬資料好幾個小時甚至更長，那不能接收。
本次目標是建立兩個表，一個使用者表，另外一個訂單表，當然沒有真實環境中的表欄位那麼多，但是對於學習測試來說差不多夠了。
兩個表的表結構如下：
# 使用者表
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）建立 500 萬條資料，id 使用 uuid，年齡從 1 到 120 隨機，電話號碼隨機 11 位，省份編碼和城市編碼隨機，建立時間和更新時間在某一時間範圍內隨機。
訂單表（order）根據使用者表生成，每個使用者隨機生成 0 到 3 個訂單，訂單編號採用 uuid，商品數量隨機 1 到 5 個，價格隨機，建立時間和更新時間在某一時間段內隨機。由於每個使用者產生 0 到 3 個訂單，所以，產生的訂單量應該大於 500 萬，我在本地跑的時候基本上在 700多萬左右。
建立總時間和表的欄位個數以及欄位的生成演算法有直接關係，欄位越多、演算法越複雜，需要的時間就越多，比如使用 uuid 就比使用自增 id 花費更長時間，隨機時間就比直接使用當前時間花費更長時間。
如果只插入 500 萬自增 id 這一個欄位，十幾秒就能完成，但是無論是模擬線上環境還是自學效能優化技巧都沒什麼意義。
下面就來介紹三種方式來快速建立 500 萬使用者資料以及大於 500 萬的訂單資料。
寫程式批量插入
作為一個開發人員，當你打算建立百萬條資料的時候，大多數時候首先相當的應該就是寫程式，畢竟 CURD 我們最拿手了。
用程式的方式插入也分兩種情況，第一種就是逐條插入，這也是平時開發中最常用到的方法，直覺上我們可能會認為這樣比較快。事實上並不是這樣，雖然比起手動一條一條插入是快的多，但是，很有可能你在等待了一段時間後失去耐心，然後結束程式，不管你用哪種資料庫連線池都一樣，在百萬數量級面前仍然慢的離譜。
第二種情況就是使用 MySQL 的批量插入方法，我們都知道 MySQL 支援一次性插入多條記錄，就是下面這樣的形式。
insert into `table_name` (id,column1) values (1,'value1'),(2,'value2'),(3,'value3');
這樣一來，比你一條一條語句執行要快很多，比如 1000 條記錄執行一次 insert，一共執行 5000 次即可，如果是一條一條插入呢，那就要執行 500 萬次。
由於後面兩種方式用到了 Python 生成檔案，所以這種方式也用了 Python 實現，例項程式碼如下。完整程式碼可在文末給出的 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()
經過一段時間時間的等待後，執行完成了，整個執行過程耗時 1823 秒，30分鐘。
最後成功生成使用者記錄 500 萬條，訂單記錄 749 萬多條。
速度還算能接受吧，馬馬虎虎吧。
再想速度快一點，可以開多執行緒，我用 5 個執行緒跑了一下，一個執行緒插入 100萬條，最終最長的執行緒耗時 1294秒，21分鐘，也沒快多少，執行緒個數對時間多少有些影響，但是我沒有試。
生成 SQL 指令碼
這種方式和上面的方式類似，只不過上面通過程式方式直接將拼接出來的 SQL 語句執行了，而這種方式是將拼接好的 SQL 語句寫入檔案中。當然還是以一條語句插入多行記錄的形式。
insert into `table_name` (id,column1) values (1,'value1'),(2,'value2'),(3,'value3');
寫 500 萬使用者資料，加上隨機的訂單資料， sql 檔案的過程耗時為 696 秒，11分鐘左右。
當然這麼大資料量拼接出來的指令碼檔案也很大，使用者表指令碼 680 多M，訂單表指令碼 1個G。
最後將寫好的這兩個檔案分別在 MySQL 中執行。
執行使用者表指令碼，耗時 3 分鐘左右。
mysql -uroot -p mast_slave < sql/insert_user_500w.sql
執行訂單表指令碼，耗時 7 分鐘左右，訂單量 750 多萬個。
mysql -uroot -p mast_slave < sql/insert_order_500w+.sql
一共耗時，20分鐘左右，加上中間的手工操作，感覺不如第一種方法中的多執行緒方式省事。
load data infile 方式
最後這種方式是使用 load data infile 方式，這是 MySQL 提供的一種從檔案快速匯入的方式。比如按照特定符號分隔，匯入對應的欄位中。
本文例子中我是按照逗號分隔的，欄位之間以逗號分隔，生成 500 萬條使用者行 和隨機訂單行。
依然是用 Python 指令碼生成檔案，生成檔案的過程耗時 779 秒，12分鐘左右。
兩個檔案大小分別是 560 多M 和 900 M。
最後執行 load data infile 將檔案匯入到對應的表中，在執行這個命令後可能會出現下面這個錯誤提示。
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
這是因為 MySQL 自身的安全配置所致，需要更改 my.cnf，在其中加入下面的配置，然後重啟服務。
secure_file_priv=
等於號後邊為空表示允許所有目錄下的檔案 load，如果要限定某個特定目錄，在等於號後邊填上對應的檔案目錄即可。
然後執行下面的語句，將使用者記錄匯入到 user 表。
load data infile '/Users/fengzheng/知識管理/技術寫作/mysql/建立測試資料/sql/load_user_txt_500w.txt' replace into table user FIELDS TERMINATED BY ',';
500萬條耗時 3分32秒。
將訂單記錄匯入到 order 表。
load data infile '/Users/fengzheng/知識管理/技術寫作/mysql/建立測試資料/sql/load_order_txt_500w+.txt' replace into table `order` FIELDS TERMINATED BY ',';
749 萬條記錄，耗時 8分31秒。
整個過程加起來 24 分鐘左右。
最後
好了，現在可以愉快的做各種測試和優化了。
有同學看完可能要說了，20多分鐘好像也不算快啊。因為資料量確實比較大，再有資料複雜度和匯入時間也有很大關係，如果你只是匯入一列自增id，別說 500 萬，1000萬都用不了一分鐘就完成了。
其實還有一點優化空間的，比如說把資料庫引擎改成 MYISAM 會更快一些，尤其是對於批量插入的情景，但是插入完成後還要再改回來，也需要耗費一些時間，而且來回切換也比較麻煩。
上面的幾種方法都配合了 Python 指令碼，當然你可以換成自己熟悉的語言，比如 Java，或者直接寫 bash 指令碼也可以。
指令碼已經放到了 github 上，需要的同學請自取。地址：
壯士且慢，先給點個贊吧，總是被白嫖，身體吃不消！
公眾號「古時的風箏」，Java 開發者，全棧工程師，bug 殺手，擅長解決問題。
一個兼具深度與廣度的程式設計師鼓勵師，本打算寫詩卻寫起了程式碼的田園碼農！堅持原創乾貨輸出，你可選擇現在就關注我，或者看看歷史文章再關注也不遲。長按二維碼關注，跟我一起變優秀！
