MySQL億級資料效能測試
經常有人說,資料庫單表不要超過500萬行,這有沒有道理呢?本文我們不談資料庫技巧也不談最佳化方式,只是用例項來給大家演示一下,資料表資料激增給查詢效能帶來的影響。
測試環境
之前阿里雲活動,剛好搞了1個“雲資料庫 RDS版”,本次剛好拿來測試,配置如下:
資料庫型別
:
MySQL
5
。
7
CPU
:
1
核
資料庫記憶體
:
1024
MB
模擬“
使用者資訊表
”來進行測試,建立以下資料表:
CREATE TABLE `user_info` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘使用者ID’,
`name` varchar(32) NULL COMMENT ‘姓名’,
`phone_number` varchar(32) NULL COMMENT ‘電話號碼’,
`email` varchar(32) NULL COMMENT ‘電子郵箱’,
`birthday` varchar(32) NULL COMMENT ‘生日’,
`constellation` varchar(32) NULL COMMENT ‘星座’,
`edu_back` varchar(32) NULL COMMENT ‘學歷’,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;
資料生成
得益於阿里雲DMS自帶的“
自動生成測試資料
”功能,使得可以批次生成測試資料,大大方便了測試流程。後續將直接使用該功能生成原始資料。
測試說明
本次測試內容是:測試行數的增加對檢索效能的影響,測試維度有2個,一是帶索引欄位,二是不帶索引欄位。
由於主鍵的id欄位預設帶唯一索引,因此不再額外增加索引。表中其他欄位均不帶索引。使用時分別使用 id 和 email 欄位進行測試。
測試資料
1000行
首先我們自動生成1000行測試資料,使用:
SELECT * FROM user_info where id=345;
進行檢索,測試結果為:
耗時
4
毫秒。
下面使用同一行的email值進行檢索:
SELECT * FROM user_info where email=‘h3l_q_iozoi@dmstest。com。cn’;
測試結果也是耗時
4
毫秒,兩者沒有差距。
接下去我們增加資料量。
10000行
我們增加9000行資料,使資料量達到10000行,同時為避免資料庫快取資料影響測試結果,後續均使用不同的id行來進行測試。(email也是隨機找到一個)
我們繼續測試:
(1)使用 id 進行檢索,耗時 2 毫秒(居然比1000行時還要少,有點不科學)
(2)使用 email 進行檢索,耗時 6 毫秒。
10萬行
我們再增加90000行資料,使資料量達到100000行。
測試結果為:
(1)使用 id 進行檢索,耗時
8
毫秒
(2)使用 email 進行檢索,耗時
78
毫秒。
100萬行
我們再增加900000行資料,是資料量達到1000000行。
測試結果為:
(1)使用 id 進行檢索,耗時
3
毫秒
(2)使用 email 進行檢索,耗時
387
毫秒。
200萬行
由於阿里雲DMS自帶的工具一次最多隻能生成100萬行記錄,遇到了瓶頸,後面為了能夠一次性插入更多的資料,這裡用了一個奇技淫巧,我們使用相同的表結構新增一箇中轉表(命名:temp_copy,以下簡稱“副表”),先將主表資料複製一份過去,再把資料複製回來,這相當於一來一回操作能夠將主表資料翻倍,後面我們就使用這個方法來增加資料。
先將資料從主表複製到副表(耗時
13338
毫秒):
INSERT INTO temp_copy (name,phone_number,email,birthday,constellation,edu_back) (SELECT name,phone_number,email,birthday,constellation,edu_back FROM user_info) ;
然後再將資料從副表複製一份至主表(耗時
11041
毫秒):
INSERT INTO user_info (name,phone_number,email,birthday,constellation,edu_back) (SELECT name,phone_number,email,birthday,constellation,edu_back FROM temp_copy) ;
繼續進行之前的測試,測試結果為:
(1)使用 id 進行檢索,耗時
5
毫秒
(2)使用 email 進行檢索,耗時
6739
毫秒。
第2項資料突變有點明顯,我再使用其他資料測試了幾遍,依然在這個範圍徘徊,因此這個資料還是可靠的。
500萬行
我們將資料繼續在主表和副表之間倒騰了一遍,資料量達到了5000000行,我們繼續測試:
(1)使用 id 進行檢索,耗時
5
毫秒
(2)使用 email 進行檢索,耗時
20289
毫秒。
1300萬行
後面繼續增加資料,可以很明顯的感覺到插入資料的時間越來越多,這一次從主表複製至副表,500萬行,耗時
61624
毫秒。我們繼續從副表複製至主表,800萬行,耗時
100028
毫秒。
繼續測試,測試結果為:
(1)使用 id 進行檢索,耗時
7
毫秒
(2)使用 email 進行檢索,耗時
57498
毫秒。
3400萬行
我們繼續,再“左右到右手”進行一次複製:
(1)主表複製至副表,插入1300萬行,耗時
169816
毫秒。
(2)副表複製至主表,插入2100萬行,耗時
263856
毫秒。
資料量達到3400萬行時,儲存容易已經達到了3G,非常恐怖。
繼續測試,測試結果為:
(1)使用 id 進行檢索,耗時
6
毫秒
(2)使用 email 進行檢索,耗時
153407
毫秒。
測試結果
我們整理一遍以上的測試結果:
從100萬行開始,email檢索項的時間開始激增,事實上,這個檢索時間已經大大超出能夠接受的範圍了。從以上的結果也可以看出建立索引的重要性。
後續我再測測同樣資料量在其他資料庫(Oracle、postgreSQL等)中的表現,敬請期待。
作者:賴一鳴
出自:孤竹說(微信公眾號ID:iam1ming)
原文:MySQL億級資料效能測試