我是風筝，公衆号「古時的風筝」，一個兼具深度與廣度的程式員鼓勵師，一個本打算寫詩卻寫起了代碼的田園碼農！
文章會收錄在 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 殺手，擅長解決問題。
一個兼具深度與廣度的程式員鼓勵師，本打算寫詩卻寫起了代碼的田園碼農！堅持原創幹貨輸出，你可選擇現在就關注我，或者看看曆史文章再關注也不遲。長按二維碼關注，跟我一起變優秀！
人生沒有回頭路，珍惜當下。