前言

前段時間，以前的同事問我，Mysql能不能導入3億的數據。我腦子當時翁的一下，不太確定的說應該可以導入的吧！只是導入進去以后，可能會出現查詢過慢的情況。

于是馬上百度了一下，發現有人干過這種事情，在Mysql上面導入億級的數據（文章鏈接）。根據這篇文章的介紹，知道了原有的幾個坑。

第一個注意事項：索引

第一個注意事項就是索引。建表的時候除了主鍵不要給別的字段建立索引。因為索引也需要創建，當我們數據量過大的時候就要注意，這個時候創建索引會導致我們的數據導入時間無限拉長。只需要留下一個自增ID做主鍵即可，如果你的數據本來就有主鍵（絕對唯一），就用這個主鍵，不用自增ID了。當導入完成后，我們在添加索引。

第二個注意事項：存儲引擎

Mysql一般默認推薦使用InnoDB，但是這里由于我們沒有事務的需求，所以采用了查詢效率更高的MyISAM作為存儲引擎。需要注意的是MyISAM是沒有事務的，插入就插入了，沒有回滾的說法。需要注意的是在進行寫入操作的時候是鎖表的、所以在寫入的時候，不要同時去查詢表的數據。

第三個注意事項：磁盤空間

在進行插入之前一定要給自己的磁盤留下足夠的空間。需要注意的是導入存在較大事務，超過 binlog_cache_size，高并發下生成大量臨時文件（參考文章鏈接），如果我們不指定臨時文件目錄，那么如果你是windows系統，你的臨時文件目錄很可能在C盤。可以使用show variables like "tmpdir";命令查看你自己的臨時目錄在哪里。如果空間不夠是會報錯的：1598 - Binary logging not possible. Message: An error occurred during flush stage of the commit. 'binlog_error_action' is set to 'ABORT_SERVER'. Hence aborting the server.所以最好指定一下臨時文件的目錄tmpdir="D:/Program Files/db/mysql-8.0.20-winx64/tmp"

數據準備

好了、開始導入之旅。在導入之前我們需要準備好對應的數據，這里先將表結構貼出來：

create table test_user_tab( id bigint comment '主鍵', now_date varchar(20) comment '插入時間', tel varchar(11) comment '電話', card_num varchar(18) comment '身份證號', sex int comment '性別：1男，0女', mz varchar(20) comment '名族', user_name varchar(20) comment '姓名', address_str varchar(50) comment '地址', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 comment '用戶測試表';

我們可以使用代碼來生成對應的數據，隨機生成電話、身份證號、性別、民族、姓名、地址。一次生成三個億的數據。

java生成數據

生成代碼倉庫：測試數據生成: 生成測試數據

import com.hzw.code.util.IDCardUtil; import com.hzw.code.util.RandomValue; import com.hzw.code.util.WeightRandomMz; import org.apache.commons.lang3.time.DateFormatUtils;import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.nio.ByteBuffer; import java.nio.channels.FileChannel; import java.util.Date;public class Test {public static void main(String[] args) {int sex = 0;String card = null;int c = 1;for(int k=1;k<=3000; k++) {StringBuffer sbText = new StringBuffer();for (int i = 1; i <= 100000; i++) {card = IDCardUtil.generateID();if (Integer.parseInt(card.substring(16, 17)) % 2 == 1) {sex = 1;} else {sex = 0;}sbText.append(c++).append(",").append(DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss")).append(",").append(RandomValue.getTel()).append(",").append(card).append(",").append(sex).append(",").append(WeightRandomMz.getMz()).append(",").append(RandomValue.getChineseName()).append(",").append(RandomValue.getRoad()).append("\n");System.out.println("當前條數："+c);}write("D:\\app\\ld\\users_data.txt", sbText.toString());}}/*** 追加寫入文件* @param file 文件路徑* @param text 追加寫入內容*/public static void write(String file,String text) {FileOutputStream fos = null;FileChannel channel = null;try {fos = new FileOutputStream(file,true);channel = fos.getChannel();byte[] array = text.getBytes();ByteBuffer buffer = ByteBuffer.wrap(array);channel.write(buffer);} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {channel.close();fos.close();} catch (IOException e) {e.printStackTrace();}}} }

導入數據

根據上面的java代碼，我們得到了3億數據，單純的txt文件大小為：27.7GB。這個時候可以開始導入了。表結構在上面已經貼出來了，按照上面的結構進行導入。

load data local infile 'D:/app/ld/users_data.txt' into table test_user_tab CHARACTER SET utf8 -- 可選，避免中文亂碼問題 FIELDS TERMINATED BY ',' -- 字段分隔符，每個字段(列)以什么字符分隔，默認是 \tOPTIONALLY ENCLOSED BY '' -- 文本限定符，每個字段被什么字符包圍，默認是空字符ESCAPED BY '\\' -- 轉義符，默認是 \ LINES TERMINATED BY '\n' -- 記錄分隔符，如字段本身也含\n，那么應先去除，否則load data 會誤將其視作另一行記錄進行導入 (id, now_date, tel, card_num, sex,mz,user_name,address_str) -- 每一行文本按順序對應的表字段，建議不要省略

在執行上面的代碼過后可能會報錯Loading local data is disabled； this must be enabled on both the，需要開啟全局本地文件設置

set global local_infile=on;

如此這版就需要開始等待了

導入完畢后、表的大小：

????????索引長度（4.03GB）、數據長度（29.13GB）、最大數據長度（256TB）

筆記本配置：

????????內存：16G

????????CPU：AMD RYZEN 5 3500U 4核

????????固態硬盤：500G

SQL測試：Count全表之所以能辣么快、是因為MyISAM引擎會存儲全表的數據條數，Count全表的時候會直接從存儲好的count去拿。

mysql> select * from test_user_tab order by id desc limit 0,10; +-----------+---------------------+-------------+--------------------+-----+--------+-----------+----------------------+ | id | now_date | tel | card_num | sex | mz | user_name | address_str | +-----------+---------------------+-------------+--------------------+-----+--------+-----------+----------------------+ | 300000000 | 2021-06-30 10:28:34 | 13501545216 | 410101196604012797 | 1 | 獨龍 | 胡策棟 | 市場二大廈15號-15-8 | | 299999999 | 2021-06-30 10:28:34 | 13203729048 | 140101201101183795 | 1 | 布依 | 閭誠 | 閩江二廣場50號-1-6 | | 299999998 | 2021-06-30 10:28:34 | 13107976886 | 220101201409017312 | 1 | 布依 | 胥琰 | 漳平路65號-11-5 | | 299999997 | 2021-06-30 10:28:34 | 13405130128 | 530101198901051473 | 1 | 毛南 | 宮國翰 | 大港緯四街122號-5-10 | | 299999996 | 2021-06-30 10:28:34 | 13500788582 | 310101195005203737 | 1 | 漢 | 郭芳 | 澳門廣場118號-16-1 | | 299999995 | 2021-06-30 10:28:34 | 13500938547 | 540101200801079200 | 0 | 納西 | 公韻 | 濰縣廣場46號-6-7 | | 299999994 | 2021-06-30 10:28:34 | 15301675632 | 810101195409066773 | 1 | 塔塔爾 | 車倩 | 福山大廈13號-15-5 | | 299999993 | 2021-06-30 10:28:34 | 15502277286 | 120101200506094119 | 1 | 毛南 | 軒言光 | 小港二街111號-17-2 | | 299999992 | 2021-06-30 10:28:34 | 15307933706 | 620101199804149598 | 1 | 漢 | 蘇啟 | 吳淞路101號-20-4 | | 299999991 | 2021-06-30 10:28:34 | 13808373641 | 320101199212197480 | 0 | 藏 | 范姬華 | 武定路142號-9-5 | +-----------+---------------------+-------------+--------------------+-----+--------+-----------+----------------------+ 10 rows in set (0.19 sec) mysql> select * from test_user_tab where id in (569866,5656,23565,44,6,6467,8979); +--------+---------------------+-------------+--------------------+-----+------+-----------+---------------------+ | id | now_date | tel | card_num | sex | mz | user_name | address_str | +--------+---------------------+-------------+--------------------+-----+------+-----------+---------------------+ | 6 | 2021-06-30 09:14:24 | 13704555009 | 110101199102178730 | 1 | 回 | 許梁 | 新昌街148號-1-2 | | 44 | 2021-06-30 09:14:24 | 15101051329 | 430101200205272631 | 1 | 僳僳 | 儲慧佳 | 海陽路76號-11-6 | | 5656 | 2021-06-30 09:14:24 | 15604920061 | 440101201702236208 | 0 | 漢 | 汝勇天 | 吳縣一街119號-5-3 | | 6467 | 2021-06-30 09:14:24 | 15505384583 | 520101196903158281 | 0 | 朝鮮 | 羊廣盛 | 新湛路133號-8-5 | | 8979 | 2021-06-30 09:14:24 | 13007372278 | 110101202107103898 | 1 | 滿 | 龔云 | 惠民南路65號-9-6 | | 23565 | 2021-06-30 09:14:24 | 13103003467 | 440101197306079977 | 1 | 傣 | 葛楓婷 | 澳門四街51號-17-9 | | 569866 | 2021-06-30 09:14:33 | 15603042860 | 32010119830925634X | 0 | 漢 | 容娟露 | 大港緯一路52號-14-9 | +--------+---------------------+-------------+--------------------+-----+------+-----------+---------------------+ 7 rows in set (0.21 sec)

只要使用id作為條件，因為有索引的關系，挺快的。其他字段就沒有辣么快了。需要對要做where條件的字段添加索引才行。

總結

以上是生活随笔為你收集整理的Mysql 导入3亿数据的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。