Mysql 导入3亿数据

亦凉 2022-10-11 13:43 160阅读 0赞

# 前言 #

前端时间，以前的同事问我，Mysql能不能导入3亿的数据。我脑子当时翁的一下，不太确定的说应该可以导入的吧！只是导入进去以后，可能会出现查询过慢的情况。

于是马上百度了一下，发现有人干过这种事情，在Mysql上面导入亿级的数据（[文章链接][Link 1]）。根据这篇文章的介绍，知道了原有的几个坑。

### 第一个注意事项：索引 ###

第一个注意事项就是索引。建表的时候除了主键不要给别的字段建立索引。因为索引也需要创建，当我们数据量过大的时候就要注意，这个时候创建索引会导致我们的数据导入时间无限拉长。只需要留下一个自增ID做主键即可，如果你的数据本来就有主键（绝对唯一），就用这个主键，不用自增ID了。当导入完成后，我们在添加索引。

### 第二个注意事项：存储引擎 ###

Mysql一般默认推荐使用InnoDB，但是这里由于我们没有事务的需求，所以采用了查询效率更高的MyISAM作为存储引擎。需要注意的是MyISAM是没有事务的，插入就插入了，没有回滚的说法。需要注意的是在进行写入操作的时候是锁表的、所以在写入的时候，不要同时去查询表的数据。

### 第三个注意事项：磁盘空间 ###

在进行插入之前一定要给自己的磁盘留下足够的空间。需要注意的是导入存在较大事务，超过 binlog\_cache\_size，高并发下生成大量临时文件（[参考文章链接][Link 2]），如果我们不指定临时文件目录，那么如果你是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生成数据 ###

生成代码仓库：[https://gitee.com/gzsjd/test-data-generation][https_gitee.com_gzsjd_test-data-generation]

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;

如此这版就需要开始等待了

![2021070110451644.png][]

导入完毕后、表的大小：

索引长度（4.03GB）、数据长度（29.13GB）、最大数据长度（256TB）

笔记本配置：

内存：16G

CPU：AMD RYZEN 5 3500U 4核

固态硬盘：500G

SQL测试：Count全表之所以能辣么快、是因为MyISAM引擎会存储全表的数据条数，Count全表的时候会直接从存储好的count去拿。

![20210701105500538.png][]

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条件的字段添加索引才行。

[Link 1]: https://blog.csdn.net/qq_21108311/article/details/82559119?utm_medium=distribute.pc_relevant.none-task-blog-2~default~BlogCommendFromMachineLearnPai2~default-3.base&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~BlogCommendFromMachineLearnPai2~default-3.base
[Link 2]: https://blog.csdn.net/ActionTech/article/details/104434465/
[https_gitee.com_gzsjd_test-data-generation]: https://gitee.com/gzsjd/test-data-generation
[2021070110451644.png]: /images/20221005/6b746906c51f492ebf5c447a98f0b787.png
[20210701105500538.png]: /images/20221005/88ed6d93009b4e428bf044d0516500f9.png