首先MySQL8號稱性能是mysql5的2倍以上，并且一直聽講單表500萬數(shù)據(jù)是一個瓶頸，那么我打算在最新版的mysql上導(dǎo)入一個1000萬數(shù)據(jù)，驗證看看mysql對于單表大數(shù)據(jù)的表現(xiàn)情況。
mysql數(shù)據(jù)導(dǎo)入測試
環(huán)境準(zhǔn)備
- 在虛擬機中安裝好mysql8.0.17，存儲引擎選擇innoDB，并新增一個study數(shù)據(jù)庫，并創(chuàng)建t_user表，為簡單起見，就2個字段，id和name；為減少網(wǎng)絡(luò)可能存在問題，執(zhí)行的sql放到本地執(zhí)行。不需要建立索引，畢竟導(dǎo)入大數(shù)據(jù)，有索引的話，還需而外維護，那性能更慢。
create table t_user(id int, name varchar(20));
- 虛擬機配置調(diào)整，為充分使用虛擬機的性能，調(diào)整虛機配置，4個cpu，4g內(nèi)存，10g的數(shù)據(jù)盤。
由于硬盤是固態(tài)盤，相信更多的瓶頸在cpu和內(nèi)存。限于配置，也只能評估個大概耗時。
生成sql測試文件
拼這個sql很簡單，隨便用什么語言，只要生產(chǎn)sql文件就行，單條的語句為：
insert into t_user values('1','程序不就是0和1');
這里我使用JAVA來生成sql文件，1000萬數(shù)據(jù)大概花了45秒左右，實現(xiàn)代碼截圖參考圖1，結(jié)果參考圖2
圖1:生成sql文件
圖2:1千萬條數(shù)據(jù)
這個文本文件比較大，在mac上有580多M，光打開都要花一定的時間，所以可以想象，如果通過遠程方法導(dǎo)入，對網(wǎng)絡(luò)延時要求肯定很高。
方案1:存儲過程導(dǎo)入
先來看看存儲過程導(dǎo)入千萬條數(shù)據(jù)耗時，存過就不需要文件了，直接在過程體中循環(huán)拼接insert語句即可。代碼如下：
begin DECLARE v_i int unsigned DEFAULT 0; WHILE v_i < 10000000 DO insert into t_user values(v_i,'程序不就是0和1'); SET v_i = v_i+1; END WHILE;
存儲過程耗時
總耗時大約3個小時3分鐘，還是比較慢的。
方案2:用mysql命令導(dǎo)入
mysql命令是mysql自帶的命令，位于bin目錄下,該命令比較簡單，為了避免網(wǎng)絡(luò)延遲，我們先把用java生成的sql文件傳到服務(wù)器上，使用rz命令進行上傳到mysql的bin目錄下，這樣執(zhí)行命令稍微簡單點，輸入用戶名和密碼即可執(zhí)行。
這里有個地方需要注意下：因為sql文件較大，默認的話會報一個錯誤：ERROR 2006 (HY000): MySQL server has gone away，意思是記錄數(shù)超過最大值。我們可以更改全局變量，只在本次mysql進程中有效，重啟mysql就失效了，如果想一直生效，可以在my.cnf中配置。更改后，就可以執(zhí)行導(dǎo)入命令了。
set global max_allowed_packet=1024*1024*1000;##設(shè)置為1G
上傳sql文件到服務(wù)器
./mysql -uroot -p study1 < t_user.sql
總共耗時大約3小時8分鐘，跟第一種方案區(qū)別不大。總結(jié)一下，對于大批量數(shù)據(jù)基本不能使用insert into 的方案，改用文本導(dǎo)入方案。所以我們重新生成數(shù)據(jù)文件。java代碼更改如下：
使用制表符號作為數(shù)據(jù)分隔符，方便下面命令使用。
方案3:用mysqlimport命令導(dǎo)入
mysql從安全考慮，默認對這種導(dǎo)入文件的方式是關(guān)閉的，可以用該命令查詢：
show variables like 'local_infile';
在全局環(huán)境中打開可以使用命令：
set global local_infile=ON;
如過沒有打開此參數(shù)，而執(zhí)行了mysqlimport命令會收到一個報錯：ERROR 1148 (42000): The used command is not allowed with this MySQL version。
mysqlimport命令的常用參數(shù)介紹：
- --fields-terminated-by=字符串：設(shè)置字段間的分隔符，可以為單個或多個字符。默認值為制表符“t”，這就是上述我們生成文件時候故意使用制表符。
- -L, --local：表示從客戶端任意路徑讀取文件導(dǎo)入表中，未設(shè)置該選項時，默認只從datadir下同名數(shù)據(jù)庫目錄下讀取文件導(dǎo)入。
- -p, --password[=name]：指定用戶密碼。
- -u, --user=name：指定登入MySQL用戶名。
我們現(xiàn)在執(zhí)行下面命令執(zhí)行導(dǎo)入：
./mysqlimport -uroot -p --local study2 t_user.txt
備注：
- 上面重新生成的數(shù)據(jù)文件（t_user.txt），同樣先上傳服務(wù)器。
- 文件名默認就是要導(dǎo)入的表名。
- 耗時大約1分18秒。
mysqlimport其實是load data infile 的命令行工具，理論講它們速度應(yīng)該是一樣的，所以就不再演示load data infile 命令了。
數(shù)據(jù)有了，看看查詢效率
因為默認沒有索引，所以隨便查一個數(shù)據(jù)，大約耗時14秒。
select * from t_user where id = 1
加上索引之后，耗時幾乎為0。忽略不計
可見千萬的單表數(shù)據(jù)在走索引情況下，mysql其實還是可以扛住的。
總結(jié)
直接看圖吧
基本上在大數(shù)據(jù)導(dǎo)入的情況下，mysqlimport命令耗時可以忽略不計了。只不過使用該命令需要打開local_infile參數(shù)。
