MySQL导入上亿级的数据

一、背景

最近在天池下载了一份淘宝用户行为数据准备用作数据分析，不过这份数据上亿级别的，所以通过excel或者editplus++是没办法打开的，因此我准备将该数据导入MySQL数据库中，进行后续处理和分析。

查看数据情况
windows 电脑: type UserBehavior.csv
linux: cat UserBehavior.csv

二、导入数据

方法一: navicat客户端导入

1. 建立字段表

   
   image.png

2. 右键导入

   
   image.png

不过这种方法对于几万，几十万的数据量来说还可以考虑，上亿级别的数据真的是太难了，特别慢

方法二：使用load data infile语句导入

直接运行load data infile语句，会报错：The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

需要修改secure-file-priv 路径

1. 首先查看文件允许导入导出的路径
   show variables like '%secure%';
   image.png
2. 找到文件my.ini，查看secure file priv路径是否与之相同，若不同则修改文件路径，或者直接把路径改成空，允许其他路径文件导入：

image.png

3. 重启MySQL服务

   
   image.png

4. 使用load data infile语句导入，具体的时间其实还是要看电脑的(我花了快两个小时的时间)，不过速度确实比navicat有了质的飞跃

# tb 是数据库中的表名，要先建好表，并添加对应的字段
load data infile 'D:\UserBehavior.csv'   
into table tb fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\n'; 

Snipaste_2019-09-16_18-22-08.png

三、抽取数据

为什么需要抽取数据呢？
因为在实际的操作中数据量真的是太大了，语句执行缓慢，且存储空间容易被临时文件占满，所以可以对数据进行分层抽样， 抽取5%数据存入新表

CREATE TABLE tb2 SELECT * FROM tb WHERE MOD(tb.`用户id`,20) = 0;

可能出现的问题

image.png
在数据的抽取过程中，可能会表溢出， 这个时候需要修改size,
查看默认值: show variables like "%_buffer%";
image.png

• 修改配置文件my.ini
  (C:\ProgramData\MySQL\MySQL Server 8.0目录中)，修改innodb_buffer_pool_size=128M, 有些资料都是修改到64M，试了多次后发现还是太小了，所以建议改成128M

  
  image.png

重启MySQL服务

• 通过sql语句修改

show variables like "%_buffer%";
set global innodb_buffer_pool_size=134,217,728;
# 重启MySQL服务