MySQL导入上亿级的数据

一、背景

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

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

linux: cat UserBehavior.csv

二、导入数据

方法一: navicat客户端导入

1. 建立字段表

1. 右键导入

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

方法二：使用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%';

1. 找到文件my.ini，查看secure file priv路径是否与之相同，若不同则修改文件路径，或者直接把路径改成空，允许其他路径文件导入：

1. 重启MySQL服务

1. 使用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'; 

三、抽取数据

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

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

可能出现的问题

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

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

重启MySQL服务

• 通过sql语句修改

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

• Previous
  学会用Django框架实现功能(三)
• Next
  MySQL淘宝用户行为分析