快速导入mysql百万用户数据

1. 前言

随着互联网的发展,大数据已经是很普遍的一个现象,已不再是零几年时的神话。数据资源意为着就是信息资源,很好的使用起来说是财富资源一点也不夸张.所以无论当下是小的还是大的互联网公司都会遇到大数的情况。只不过不同的业务逻辑需求与使用情况不一样罢了。

大数据我这大致归类为两种,一种是静态的数据,比如说在一定时间段内的账号信息;另一种是动态数据,比如流量访问信息等。

2. 目标

以上简单的概述了大数量的背景,而本文不会展开叙述太多,而接下来要叙述的是就是如何快速导入百万账号。账号也就是我在上一章节说到的静态资源,这些静态资源有个特性就是在一段时间内是没有多大的变化的,并且与相关联性要求也很高,所以注定存储此类数据会使用选择使用关系型数据库(ACID特性:原子性,一致性,隔离性,持久性),即我们常见的如"MySQL、PostgreSQL、Oracle Database、Microsoft SQL Server等"。这几个典型的数据各有利弊,综合场景与成本等来考虑,大部分会选择使用开源的mysql数据库.所以下文实例都是以mysql数据为例。

3. 方法

常用的有两种方式:一种方法是,直接写sql语句插入数据;另一种方法是,数据文件导入。通常情况下，直接通过SQL语句将数据写入数据库比导入CSV文件数据更快。这里有几点考虑因素：

• 直接SQL语句写入数据库的优势：

  • 直接数据传输：通过SQL语句直接写入数据库时，数据可以直接传输到数据库的存储引擎中，跳过了文件读取、解析和加载的过程。
  • 少量数据时更快：特别是对于少量数据，直接使用SQL语句可以快速执行，避免了文件操作的额外开销和时间。
  • 即时处理：SQL语句的执行几乎是实时的，一旦语句提交，数据就可以立即在数据库中可用。

• 导入CSV文件数据的考虑：

  • 大数据量处理：对于大数据量，虽然导入CSV文件可能需要一些预处理步骤（如文件读取、解析和加载），但在某些情况下，使用数据库的批量导入工具或者专门的ETL（Extract, Transform, Load）工具可能会更高效。
  • 数据准备和格式化：如果数据已经以CSV文件的形式存在，并且需要进行大量的数据转换或者数据清洗，导入CSV文件可能更具实际可行性和操作便利性。
  • 导入工具的优化：一些数据库管理系统提供了优化的导入工具，可以有效地处理大量数据的导入，比如MySQL的LOAD DATA INFILE命令或PostgreSQL的COPY命令。

综合考虑：

• 数据量和频率：如果你经常需要导入大量数据，并且已经准备好了CSV文件，那么使用导入工具可能更为方便和高效。

• 实时性需求：如果你需要实时数据更新，并且数据量不是特别大，那么直接使用SQL语句写入数据库可能更合适。

综上所述，对于大多数情况，直接使用SQL语句写入数据库通常更快速和直接，特别是对于少量数据或需要即时处理的情况。然而，对于大量数据的导入，特别是需要预处理或者已经存在CSV文件的情况下，使用导入工具可能更有效率。

4.如何快速导入百万级账号

了解了背景与目的,又分析了方法,这里就是具体的操作实现了。研究了几种方式发现导入300百万以上的数据还是使用命令"load data infile"命令最快,一个表全量写入32个字段数据.使用python构造数据csv文件耗时18秒左右,csv文件写入库耗时不到6分钟;另一个全量表8个字段,构造数据文件耗时8秒,csv文件写入库耗时不到2分钟.经查看结果与写入的csv数据保持一致。

注:

1. 这里有个前提条件,那就是具有可修改my.cnf权限.或是"--secure-file-priv"已经做了配置.因为这涉及到安全性考虑没有配置打开"--secure-file-priv"那就没有办法快捷导入csv数据文件到库里。

2. 构造数据还需要有python环境。

3. 因不涉及到创建与获取组织架构信息,所以得提前知道组织的名称与组织id.这里简化了一下在构造时输入一个组织名称与id(org_auto,zdy_530853592895066112).实际情况可以写入到一个文件里,然后可以从文件中获取再写入到对应的csv数据表中。

下面以结合业务逻辑写入到三个库表数据为例的具体操作:

4.1 根据实际情况构造数据文件

首先要了解数据结构,那些字段是改写字段不能为空,那些是可以为空,那些是可以为空但实际情况下基本不为空.在此是在线上根据业务根据了真实值情况进行了信息虚拟化改造了一下。

-- base1.table1_user definition

CREATE TABLE `table1_user` (
  `userid` varchar(64) NOT NULL,
  `emid` varchar(32) DEFAULT '',
  `cname` varchar(64) DEFAULT '',
  `utype` int DEFAULT '0',
  `pswd` varchar(64) DEFAULT '',
  `onjob` tinyint(1) DEFAULT '1',
  `userstatus` varchar(16) DEFAULT '',
  `mobile` varchar(64) DEFAULT '',
  `idnumbermd5` varchar(32) DEFAULT '',
  `mail` varchar(256) DEFAULT '',
  `pin_errorcount` int DEFAULT '0',
  `pin_locktime` int DEFAULT '0',
  `deptname` text,
  `dept_path_id` text,
  `dept_path_name` text,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `expire_time` datetime DEFAULT NULL,
  `surname` varchar(64) DEFAULT '',
  `givenname` varchar(64) DEFAULT '',
  `resetpwd` tinyint(1) DEFAULT '0',
  `emp_type` varchar(256) DEFAULT '',
  `dept_id` text,
  `companynumber` varchar(256) DEFAULT '',
  `leader` varchar(64) DEFAULT '',
  `ptjs`