程序员经验分享-hwhale

MySql 快速插入千万级大数据

2023-11-02

原文地址：http://blog.csdn.net/oldbai001/article/details/51693139

在数据分析领域，数据库是我们的好帮手。不仅可以接受我们的查询时间，还可以在这基础上做进一步分析。所以，我们必然要在数据库插入数据。在实际应用中，我们经常遇到千万级，甚至更大的数据量。如果没有一个快速的插入方法，则会事倍功半，花费大量的时间。

在参加阿里的天池大数据算法竞赛中（流行音乐趋势预测），我遇到了这样的问题，在没有优化数据库查询及插入之前，我花了不少冤枉时间，没有优化之前，1500万条数据，光插入操作就花费了不可思议的12个小时以上（使用最基本的逐条插入）。这也促使我思考怎样优化数据库插入及查询操作，提高效率。

在不断优化过程中，性能有大幅提升。在按时间序列从数据库查询并汇总生成2万6000多首歌曲的下载，播放，收藏数过程中，通过查询生成的操作速度提高从预估的40多小时降低到一小时多。在数据库插入方面，性能得到大幅提升；在新的数据集上测试，5490万+的数据，20分钟完成了插入。下面分享一下我的心得。


优化过程分为2步。第一步，实验静态reader从CSV文件读取数据，达到一定量时，开始多线程插入数据库程序；第二步，使用mysq批量插入操作。


第一步，读取文件，开始插入多线程

在这里，达到一定量的量是个需要斟酌的问题，在我的实验中，开始使用100w作为这个量，但是出现了新的问题，Java 堆内存溢出，最终采用了10W作为量的标准。

当然，可以有其他的量，看大家自己喜欢那个了。

[java]  view plain  copy
  1. mport java.io.BufferedReader;  
  2. import java.io.FileNotFoundException;  
  3. import java.io.FileReader;  
  4. import java.io.IOException;  
  5. import java.util.ArrayList;  
  6. import java.util.List;  
  7.   
  8. import preprocess.ImportDataBase;  
  9.   
  10. public class MuiltThreadImportDB {  
  11.   
  12.     /** 
  13.      * Java多线程读大文件并入库 
  14.      *  
  15.      * @param args 
  16.      */  
  17.     private static int m_record = 99999;  
  18.     private static BufferedReader br = null;  
  19.     private ArrayList<String> list;  
  20.     private static int m_thread = 0;  
  21.     static {  
  22.     try {  
  23.         br = new BufferedReader(  
  24.             new FileReader(  
  25.                 "E:/tianci/IJCAI15 Data/data_format1/user_log_format1.csv"),8192);  
  26.   
  27.     } catch (FileNotFoundException e) {  
  28.         e.printStackTrace();  
  29.     }  
  30.     try {  
  31.         br.readLine(); // 去掉CSV Header  
  32.     } catch (IOException e) {  
  33.         e.printStackTrace();  
  34.     }  
  35.     }  
  36.   
  37.     public void start() {  
  38.     String line;  
  39.     int count = 0;  
  40.     list = new ArrayList<String>(m_record + 1);  
  41.     synchronized (br) {  
  42.         try {  
  43.         while ((line = br.readLine()) != null) {  
  44.             if (count < m_record) {  
  45.             list.add(line);  
  46.             count++;  
  47.             } else {  
  48.             list.add(line);  
  49.             count = 0;  
  50.             Thread t1 = new Thread(new MultiThread(list),Integer.toString(m_thread++));  
  51.             t1.start();  
  52.             list = new ArrayList<String>(m_record + 1);  
  53.             }  
  54.         }  
  55.   
  56.         if (list != null) {  
  57.             Thread t1 = new Thread(new MultiThread(list),Integer.toString(m_thread++));  
  58.             t1.start();  
  59.         }  
  60.         } catch (IOException e) {  
  61.         e.printStackTrace();  
  62.         }  
  63.     }  
  64.     }  
  65.   
  66.     public static void main(String[] args) {  
  67.     new MuiltThreadImportDB().start();  
  68.     }  
  69.   
  70. }  
第二步，使用多线程，批量插入数据

[java]  view plain  copy
  1. class MultiThread implements Runnable {  
  2.     private ArrayList<String> list;  
  3.   
  4.     public MultiThread(ArrayList<String> list) {  
  5.     this.list = list;  
  6.     }  
  7.   
  8.     public void run() {  
  9.     try {  
  10.         ImportDataBase insert = new ImportDataBase(list);  
  11.         insert.start();  
  12.     } catch (FileNotFoundException e) {  
  13.         e.printStackTrace();  
  14.     }  
  15.     display(this.list);  
  16.     }  
  17.   
  18.     public void display(List<String> list) {  
  19.     // for (String str : list) {  
  20.     // System.out.println(str);  
  21.     // }  
  22.     System.out.print(Thread.currentThread().getName() + " :");  
  23.     System.out.println(list.size());  
  24.     }  
  25.   
  26. }  

批量操作中，使用mysql的prepareStatement类，当然也使用了statement类的批量操作，性能比不上前者。前者可以达到1w+每秒的插入速度，后者只有2000+；

[java]  view plain  copy
  1. public int insertUserBehaviour(ArrayList<String> sqls) throws SQLException {  
  2.   
  3.     String sql = "insert into user_behaviour_log (user_id,item_id,cat_id,merchant_id,brand_id,time_stamp,action_type)"  
  4.         + " values(?,?,?,?,?,?,?)";  
  5.     preStmt = conn.prepareStatement(sql);  
  6.     for (int i = 0; i < sqls.size(); i++) {  
  7.         UserLog log =new UserLog(sqls.get(i));  
  8.         preStmt.setString(1, log.getUser_id());  
  9.         preStmt.setString(2, log.getItem_id());  
  10.         preStmt.setString(3, log.getCat_id());  
  11.         preStmt.setString(4, log.getMerchant_id());  
  12.         preStmt.setString(5, log.getBrand_id());  
  13.         preStmt.setString(6, log.getTimeStamp());  
  14.         preStmt.setString(7, log.getActionType());  
  15.         preStmt.addBatch();  
  16.         if ((i + 1) % 10000 == 0) {  
  17.         preStmt.executeBatch();  
  18.         conn.commit();  
  19.         preStmt.clearBatch();  
  20.         }  
  21.     }  
  22.     preStmt.executeBatch();  
  23.     conn.commit();  
  24.     return 1;  
  25.     }  

当然，也实验了不同的mysql存储引擎，InnoDB和MyISM,实验结果发现，InnoDB更快（3倍左右），可能和mysq的新版本有关系，笔者的mysql版本是5.6。


最后总结一下，大数据量下，提高插入速度的方法。

Java代码方面，使用多线程插入，并且使用批处理提交。

数据库方面，表结构建立时不要使用索引，要不然插入过程过还要维护索引B+树；修改存储引擎，一般默认是InnoDB，（新版本就使用默认就可以，老版本可能需要）。


本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

数据库

mysql

线程

性能测试

MySql 快速插入千万级大数据 的相关文章

  • 导入 CSV 以更新表中的行

    大约有 26K 个产品 帖子 每个产品都有如下元值 post id 列是数据库中的产品 ID sku meta key 是每个产品的唯一 ID 我收到了一个新的 CSV 文件 该文件更新了每个产品的 sale price meta key

  • Mysql为简单频繁查询创建排序索引性能

    我正在处理一个包含大约 400 万条消息条目的 mysql 表 并尝试根据时间戳选择最新的 50 条消息 另一个要求是返回的消息不以固定前缀开头 问题是单个查询大约占用 25 的 cpu 并且最多需要 1 5 秒 该查询经常由多个客户端执行

  • 解码Json数据数组并插入到mysql

    这个问题可能已经在这里问过 但我尝试搜索找不到它 我有如下 Json 数据 CityInfo CityCode 5599 Name DRUSKININKAI CityCode 2003 Name KAUNAS CityCode 2573 N

  • UNIX时间记录时区吗？

    我想问一下UNIX时间 UNIX时间是否记录时区 我将托管从美国芝加哥移至 JST 问题是我的整个 MySQL 数据库都有 UNIX 时间 芝加哥 美国时区 的记录 我有一个 PHP 代码来显示之前的时间 例如 3 天前 昨天等 当我搬到新

  • 使用 pip3 安装 mysqlclient 时遇到问题

    我正在尝试使用 Django 设置 python 3 6 环境 安装说明说我应该安装 mysqlclient 才能连接到 mySQL 我明白了 dennis django sudo H pip3 install mysqlclient Co

  • mySQL 返回可能有重复项的随机行

    我正在尝试随机化一定数量的行 但假设数据库中只有 4 行 而我需要获得 6 个随机行 我希望有可能 即使表中有超过 6 行 产生重复的行行 这在 mySQL 中很容易实现吗 我当前的查询是这样的 SELECT FROM winners OR

  • 使用 PHP 代码和 HTML 表单将 Excel (.csv) 导入 MySQL

    我知道还有其他类似的帖子 但每个人都建议直接在 PHPMyAdmin 中将其导入 MySQL 这工作完美 但我需要通过 HTML 表单导入 PHP 到 MySQL 我想要一个收集文件的 HTML 表单 然后将该文件传递给 PHP 脚本 我想

  • 需要有关使用 PHP 在 mysql 数据库中插入逗号分隔数据的帮助

    数据库表中已有的演示数据 INSERT INTO csvtbl ID SKU Product Name Model Make Year From Year To VALUES 1 C2AZ 3B584 AR Power Steering P

  • 自动将所有mysql表转储到单独的文件中？

    我想将每个 mysql 表转储到单独的文件中 手册指出其语法是 mysqldump options db name tbl name 这表明您事先知道表名称 我现在可以设置知道每个表名称的脚本 但是假设我在路上添加了一个新表并且忘记更新转储

  • 是否可以使用“WHERE”子句来选择SQL语句中的所有记录？

    晚上好 我很好奇是否可以在 SQL 语句中创建一个 WHERE 子句来显示所有记录 下面一些解释 随机 SQL 语句 Java JSP示例 正常情况 String SqlStatement SELECT FROM table example

  • PDO fetch() 失败时会抛出异常吗？

    有没有方法PDO语句 fetch http php net manual en pdostatement fetch php如果 PDO 错误报告系统设置为抛出异常 则在失败时抛出异常 例如 如果我设置 PDO ATTR ERRMODE g

  • 选择MySql表数据放入数组中

    我尝试从 mysql 捕获数据并将它们全部放入数组中 认为 users table id name code 1 gorge 2132 2 flix ksd02 3 jasmen skaod2 sql mysql query select

  • RMySQL fetch - 找不到继承的方法

    使用 RMySQL 我想将数据从数据库加载到 R 中的数据帧中 为此 我使用以下代码 R连接数据库 con lt dbConnect MySQL user root password password dbname prediction h

  • SQL 大表中的随机行（使用 where 子句）

    我有一个网站 人们可以在其中对汽车进行投票 向用户展示 4 辆汽车 他 她可以投票选出他们最喜欢的汽车 桌子cars有重要的列 car id int 10 not auto increment so has gaps views int 7

  • PDO::commit() 成功或失败

    The PHP PDO 提交 http www php net manual en pdo commit php文档指出该方法成功时返回 TRUE 失败时返回 FALSE 这是指beginTransaction 和commit 之间的语句执

  • Sql：计算随时间的增长

    我几周前发布了这个问题 但我认为我没有清楚地提出这个问题 因为我得到的答案不是我想要的 我认为最好重新开始 我正在尝试查询数据库以检索一段时间内唯一条目的数量 数据看起来像这样 Day UserID 1 A 1 B 2 B 3 A 4 B

  • PHP 的 PDO 可以限制为单个查询吗？

    PHP 的 PDO 允许通过 query 方法或作为准备好的语句同时执行多个查询 以下两个示例均有效 Two SQL queries query SELECT FROM table DROP table Execute via query

  • Clojure MySQL 语法错误异常（“[...] 靠近 '???????????????' [...]”）

    除了建立连接之外 我在使用 clojure contrib sql 做任何事情时都遇到困难 我有一个 mysqld 在 localhost 3306 上运行 数据库名为clj db 用户 clj user localhost 和密码 clj

  • 我可以在一个查询中更新/选择表吗？

    我需要在查看页面时选择数据并更新 视图 列 有没有一种方法可以在一个查询中执行此操作 或者我是否必须使用不同的查询 如果您不想 不需要使用事务 则可以创建一个存储过程 该过程首先更新视图计数 然后选择值并将其返回给用户

  • 如何使用 PHP 从 MySQL 检索特定值？

    好吧 我已经厌倦了 过去一周我花了大部分空闲时间试图解决这个问题 我知道 SQL 中的查询已更改 但我无法弄清楚 我能找到的所有其他帖子似乎都已经过时了 如果有人能帮助我 我将非常感激 我想做的就是使用手动输入数据库的唯一 密码 来检索行的

随机推荐

  • mac下Android Studio打不开了怎么办？

    描述 就是跳了一下 然后躺平了 解决办法 排查 打开 应用程序 gt 右键android studio gt 显示包内容 gt Contents gt MacOS gt 双击 studio 查看错误 错误可能是各种各样的 但基本都能显示出来

  • 基于最小二乘支持向量机（LS-SVM）进行分类、函数估计、时间序列预测和无监督学习（Matlab代码实现）

    欢迎来到本博客 博主优势 博客内容尽量做到思维缜密 逻辑清晰 为了方便读者 座右铭 行百里者 半于九十 目录 1 概述 2 运行结果 3 参考文献 4 Matlab代码及详细文章阅读 1 概述 很多力学 工程等学科中的问题都可以归结为积分方

  • 关于力控7.2的问题及解决方案整理（持续更新）

    建议 如果还没用力控 如果还不确定要不要用力控 一定不要 1 很多BUG 开发不完善 2 功能较为单一 目录 1 专家报表关系数据库表头问题 2 智能单元用多了会变卡 3 连接数据库失败啊 可以看报错哦 4 数据库组态改地址报错 4 网络发

  • 服务器运维常用命令

    一 linux 1 下载文件 wget O filename url 简单输出下载 wget nv O filename url 2 查看文件前几行 head n 20 file txt 3 查看目录下文件夹的大小 du d 1 h 4 c

  • 政务区块链电子证照应用场景

    政务区块链对于电子证照共享的应用场景 区块链电子证照系统场景 所解决的是证照共享的问题 在预防各部门自己的证照被批量的被盗用或被篡改 采用区块链证照模式 将各个部门的证照共享 解决的问题 证件被批量盗取 证件被他方恶意修改 证件共享难 实现

  • Linux power supply framwork & drvs

    转自 http www wowotech net pm subsystem psy class overview html 按照自己的习惯改了下排版 博主表打我 0 涉及文件 framwork drivers power power sup

  • macOS如何查看pkg安装包中的内部文件

    目录 写在前面 安装App 使用 pkg 信息面板 脚本查看 写在前面 macOS如何查看 pkg 安装包中的内部文件 我们在整系统的时候 有的时候需要查看 pkg 的内部文件 本文就教一教大家macOS如何查看 pkg 安装包中的内部文件

  • 设置锚点

    导航栏的定位 document scroll function if document scrollTop gt 442 nav css position fixed background ffffff top 0px z index 10

  • LinuxC文件操作接口

    LinuxC文件操作接口 创建与删除 创建文件 FILE fopen const char filename const char mode int open const char pathname int flags mode t mod

  • python入门之逻辑判断

    目录 一 判断 if 语句 二 逻辑运算 三 if语句进阶 四 综合应用 石头剪刀布 五 循环 一 判断 if 语句 1 判断语句演练 判断年龄 需求 1 定义一个整数变量记录年龄 2 判断是否满18岁 gt 3 如果满18岁 允许进网吧嗨

  • IDEA中测试代码覆盖率(Run with Coverage)插件出错的解决方式

    在进行实验时第一步要求安装测试代码覆盖率的插件时 发现idea上自带了可以直接使用的功能 我们在写好或者导入junit测试代码之后idea会自动帮我们下载junit 配置好相关设置之后就可以运行 正常的直接运行测试代码都可以直接进行但是这个

  • JQuery

    公式 a href 点我 a

  • 基于Node.js的NoSQL产品：FileDB V3.0开发完毕

    FileDB前两版是基于Java和Servlet容器的 且只能现实简单的Key Value数据存取 V3 0版使用了Javascript语言重写代码 并进行了重新设计 运行环境改为了Node js V3 0版功能有所增强 支持建任意多个表

  • SpringBoot在一定时间内限制接口请求次数-接口防刷拦截

    前一篇文字写了springboot的注册登录接口 并且这两个接口是开放的 特别是注册接口为了防止恶意注册 需要设置拦截 需要用到的知识 注解 AOP ExpiringMap 带有有效期的映射 需要自定义注解 把注解添加到我们的接口上 定义一

  • Qt插件机制及加载流程

    简介 插件实际上就是一个个动态库 动态库在不同平台下后缀名不一样 比如在 Windows下以 dll结尾 Linux 下以 so结尾 那么开发插件其实就是开发一个动态库 该动态库能够很好的加载进主程序 访问主程序资源 和主程序之间进行通信

  • k8s-核心实战

    一 资源创建方式 使用命令行 使用yum 二 NameSpace 名称空间 用来对集群资源进行隔离划分 默认只隔离资源 不隔离网络 例如创建开发 测试 生产等命令空间 可以保证一个应用引用配置只能读取自己名称空间内的资源 但是可以访问不同名

  • 悬镜安全宣布完成数千万元Pre-A轮融资

    榜样的力量 数据猿公益策划活动 寻找新冠战 疫 中国数据智能产业先锋力量 申报项目 提交文章 或深度采访 即可参与此次活动最终推出的榜单 勋章 思想者合集以及人物条漫等内容的评选 并有全网超过100家媒体同步扩散传播 丨点击 这里 了解详情

  • 基于微信小程序的医院挂号预约系统

    末尾获取源码 开发语言 Java Java开发工具 JDK1 8 后端框架 SSM 前端 Vue 数据库 MySQL5 7和Navicat管理工具结合 服务器 Tomcat8 5 开发软件 IDEA Eclipse 是否Maven项目 是

  • 双系统安装Win10+Ubuntu18.04超详细教程

    双系统安装Win10 Ubuntu18 04超详细教程 本教程主要内容包括 准备工作 制作U盘 磁盘分区和安装过程 文章目录 双系统安装Win10 Ubuntu18 04超详细教程 一 准备工作 1 1 确认BIOS模式 1 2 确认硬盘数

  • MySql 快速插入千万级大数据

    原文地址 http blog csdn net oldbai001 article details 51693139 在数据分析领域 数据库是我们的好帮手 不仅可以接受我们的查询时间 还可以在这基础上做进一步分析 所以 我们必然要在数据库插

热门标签