教你幾招,快速建立 MySQL 五百萬級資料,愉快的學習各種優化技巧

風的姿態發表於2020-08-07

我是風箏,公眾號「古時的風箏」,一個兼具深度與廣度的程式設計師鼓勵師,一個本打算寫詩卻寫起了程式碼的田園碼農!
文章會收錄在 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 萬條資料,還不夠百萬級別,而且欄位都是定義好的,不能靈活定製。

背景說明

建立百萬級資料的方式,要到達的目的有兩點:

  1. 定製比較靈活,不能只是一兩個欄位了事,那沒什麼實際意義。
  2. 速度快,不能說弄個幾百萬資料好幾個小時甚至更長,那不能接收。

本次目標是建立兩個表,一個使用者表,另外一個訂單表,當然沒有真實環境中的表欄位那麼多,但是對於學習測試來說差不多夠了。

兩個表的表結構如下:

# 使用者表
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分鐘

image-20200805103601928

最後成功生成使用者記錄 500 萬條,訂單記錄 749 萬多條。

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

再想速度快一點,可以開多執行緒,我用 5 個執行緒跑了一下,一個執行緒插入 100萬條,最終最長的執行緒耗時 1294秒,21分鐘,也沒快多少,執行緒個數對時間多少有些影響,但是我沒有試。

image-20200805115418647

生成 SQL 指令碼

這種方式和上面的方式類似,只不過上面通過程式方式直接將拼接出來的 SQL 語句執行了,而這種方式是將拼接好的 SQL 語句寫入檔案中。當然還是以一條語句插入多行記錄的形式。

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

寫 500 萬使用者資料,加上隨機的訂單資料, sql 檔案的過程耗時為 696 秒,11分鐘左右。

當然這麼大資料量拼接出來的指令碼檔案也很大,使用者表指令碼 680 多M,訂單表指令碼 1個G。

image-20200803235112353

最後將寫好的這兩個檔案分別在 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分鐘左右。

image-20200804111127619

兩個檔案大小分別是 560 多M 和 900 M。

image-20200804112826430

最後執行 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秒。

image-20200804135355209

將訂單記錄匯入到 order 表。

load data infile '/Users/fengzheng/知識管理/技術寫作/mysql/建立測試資料/sql/load_order_txt_500w+.txt' replace into table `order` FIELDS TERMINATED BY ',';

749 萬條記錄,耗時 8分31秒。

image-20200804140459790

整個過程加起來 24 分鐘左右。

最後

好了,現在可以愉快的做各種測試和優化了。

有同學看完可能要說了,20多分鐘好像也不算快啊。因為資料量確實比較大,再有資料複雜度和匯入時間也有很大關係,如果你只是匯入一列自增id,別說 500 萬,1000萬都用不了一分鐘就完成了。

其實還有一點優化空間的,比如說把資料庫引擎改成 MYISAM 會更快一些,尤其是對於批量插入的情景,但是插入完成後還要再改回來,也需要耗費一些時間,而且來回切換也比較麻煩。

上面的幾種方法都配合了 Python 指令碼,當然你可以換成自己熟悉的語言,比如 Java,或者直接寫 bash 指令碼也可以。

指令碼已經放到了 github 上,需要的同學請自取。地址:

點選獲取原始碼


壯士且慢,先給點個贊吧,總是被白嫖,身體吃不消!

公眾號「古時的風箏」,Java 開發者,全棧工程師,bug 殺手,擅長解決問題。
一個兼具深度與廣度的程式設計師鼓勵師,本打算寫詩卻寫起了程式碼的田園碼農!堅持原創乾貨輸出,你可選擇現在就關注我,或者看看歷史文章再關注也不遲。長按二維碼關注,跟我一起變優秀!

相關文章