MySQL 插入 100万 条数据整理笔记

老王Plus 2020-11-04 17:51:01 ⋅ 551 阅读

多线程插入(单表)

问:为何对同一个表的插入多线程会比单线程快?同一时间对一个表的写操作不应该是独占的吗?

答:在数据里做插入操作的时候,整体时间的分配是这样的:

1、多链接耗时 (30%) 

2、多发送query到服务器 (20%) 

3、多解析query (20%) 

4、多插入操作 (10% * 词条数目) 

5、多插入index (10% * Index的数目)

6、多关闭链接 (10%)

从这里可以看出来,真正耗时的不是操作,而是链接,解析的过程。

MySQL插入数据在写阶段是独占的,但是插入一条数据仍然需要解析、计算、最后才进行写处理,比如要给每一条记录分配自增id,校验主键唯一键属性,或者其他一些逻辑处理,都是需要计算的,所以说多线程能够提高效率。

多线程插入(多表)

分区分表后使用多线程插入。

预处理SQL

普通SQL:即使用Statement接口执行SQL
预处理SQL:即使用PreparedStatement接口执行SQL

使用PreparedStatement接口允许数据库预编译SQL语句,以后只需传入参数,避免了数据库每次都编译SQL语句,因此性能更好。

String sql = "insert into testdb.tuser (name, remark, createtime, updatetime) values (?, ?, ?, ?)";

for(int i = 0; i < m; i++) {

//从池中获取连接

Connection conn = myBroker.getConnection();

PreparedStatement pstmt = conn.prepareStatement(sql);

for(int k = 0; k < n; k++) {

 pstmt.setString(1, RandomToolkit.generateString(12));

 pstmt.setString(2, RandomToolkit.generateString(24));

 pstmt.setDate(3, newDate(System.currentTimeMillis()));

 pstmt.setDate(4, newDate(System.currentTimeMillis()));

//加入批处理

 pstmt.addBatch();

}

 pstmt.executeBatch(); //执行批处理

 pstmt.close();

 myBroker.freeConnection(conn); //连接归池

}

多值插入SQL

普通插入SQL:INSERT INTO TBL_TEST (id) VALUES(1)
多值插入SQL:INSERT INTO TBL_TEST (id) VALUES (1), (2), (3)

使用多值插入SQL,SQL语句的总长度减少,即减少了网络IO,同时也降低了连接次数,数据库一次SQL解析,能够插入多条数据。

事务(N条提交一次)

在一个事务中提交大量INSERT语句可以提高性能。

1、将表的存储引擎修改为myisam 2、将 sql 拼接成字符串,每 1000 条左右提交事务。

  • 执行多条SQL语句,实现数据库事务。  

  • mysql数据库  

  • 多条SQL语句

public void ExecuteSqlTran(List<string> SQLStringList)

{

    using (MySqlConnection conn = new MySqlConnection(connectionString))

    {

        if (DBVariable.flag)

        {

            conn.Open();

            MySqlCommand cmd = new MySqlCommand();

            cmd.Connection = conn;

            MySqlTransaction tx = conn.BeginTransaction();

            cmd.Transaction = tx;

            try

            {

                for (int n = 0; n < SQLStringList.Count; n++)

                {

                    string strsql = SQLStringList[n].ToString();

                    if (strsql.Trim().Length > 1)

                    {

                        cmd.CommandText = strsql;

                        cmd.ExecuteNonQuery();

                    }

                    //后来加上的  

                    if (n > 0 && (n % 1000 == 0 || n == SQLStringList.Count - 1))

                    {

                        tx.Commit();

                        tx = conn.BeginTransaction();

                    }

                }

                //tx.Commit();//原来一次性提交  

            }

            catch (System.Data.SqlClient.SqlException E)

            {

                tx.Rollback();

                throw new Exception(E.Message);

            }

        }

    }

}

10w条数据大概用时10s!

作者:36691683

https://blog.csdn.net/qq_36691683/article/details/89297261


全部评论: 0

    我有话说:

    MySQL数据库开发需要注意的小细节整理

    尽量不在数据库做运算控制单表数据量 纯INT不超过10M,含Char不超过5M保持表身段苗条平衡范式和冗

    为什么说作为程序员分库分表的必要性一定要掌握?

      互联网大厂程序员必须掌握海量数据和高并发问题处理技能,期望进入大厂的程序员一定要仔细看这篇! MySQL 分库分表是做什么的? 相信很多程序员对 MySQL 都比较熟悉了,目前国内

    MySQL可视化管理后台,phpMyAdmin 4.9.3 和 5.0.0 发布,

    phpMyAdmin是一个非常受欢迎的基于web的MySQL数据库管理工具。

    「开源资讯」MySQL 8.0.22 GA发布,有变化

      MySQL 最新版本 8.0.22 于2020年10月19日正式发布。 主要新变化 改进审计日志:对于JSON格式的日志文件,MySQL企业审计支持使用audit_log_read

    Mysql优化---订单查询优化(1):视图优化+索引创建

    本文针对电商的订单业务进行的Mysql优化

    MySql实战篇:正确理解并使用MySql索引

    索引是存储引擎用于快速查找记录的一种数据结构,通过合理的使用数据库索引可以大大提高系统......

    MySql实战篇:建立高性能的Mysql技巧

    体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库......

    MySql 8 新特性 - CTE 通用表表达式(先睹为快)

    前言Mysql 8 正式发布了,新增了很多优秀特性,之后我会挑些重点来分享。下面和大家一起熟悉下 CTE......

    数据结构

    结构,简单的理解就是关系。严格点说,结构是指各个组成部分相互搭配和排列的方式。在现实世界中,不同数据元素之间不是独立的,而是存在特定的关系,我们将这些关系成为结构。 数据结构:是相互之间存在一种

    关于MySQL 通用查询日志和慢查询日志分析

    MySQL中的日志包括:错误日志、二进制日志、通用查询日志、慢查询日志等等。这里主要介绍下比较常用的两个功能:通用查询日志和慢查询日志。

    MySql实战篇:写一个简单的存储过程,完成订单定时任务

    前言之前我们分享了MySql的性能优化、索引详解等内容,本篇文章主要是针对想要入门MySql存储过程的读者,主要实现的业务是订单库里面的超过30分钟没有支付的订单全部置为失效订单......

    MongoDB系列---数据类型/插入文档(三)

    ;3. 数字;4. 字符串;5. 数据;6. 对象...

    「轻阅读」Mysql调优你不得不知的细节

    多数时候数据库会成为整个系统的瓶颈

    「转载」47 张图带你 MySQL 进阶!!!

    我们在  138 张图带你 MySQL 入门 中主要介绍了基本的 SQL 命令、数据类型和函数,在具备以上知识后,你就可以进行 MySQL 的开发工作了,但是如果要成为一个合格的开发人员

    你可能不知道的CRUD

      本系列旨在系统学习提升Mysql技能,更完整内容可以参考阿里新零售数据库设计与实战 DB引擎 你可能不知道的CRUD INSERT 情况一 Duplicate key 当批量更新,如果

    线性表 - 栈与队列

    1.栈 1.栈(stack)是限定仅在表尾进行插入和删除操作的线性表,(先进后出) 2.我们把允许插入和删除的一端成为栈顶(top) 另一端称为栈底(bottom),不含任何数据元素的栈称为空栈

    推荐一款前端数据源管理工具 algeb

    ALGEB 简介 这是一个比较抽象的库,一开始可能比较难理解。我写它的初衷,是创建可响应的数据请求管理。在传统数据请求中,我们只是把携带ajax代码的一堆函数放在一起,这样就可以调用接口。但是这种

    【开源资讯】phpMyAdmin 4.9.7 和 5.0.4 发布,可视化 MySQL 管理后台

    phpMyAdmin 4.9.7 和 5.0.4 发布了。phpMyAdmin 是一个非常受欢迎的 web MySQL 数据库管理工具。它能够创建和删除数据库,创建/删除/修改表格,删除/编辑/新增