Mysql 导入3亿数据

前言

前段时间，以前的同事问我，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 ',' -- 字段分隔符，每个字段(列)以什么字符分隔，默认是 \t
 OPTIONALLY 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条件的字段添加索引才行。