C#在MySQL大量数据下的高效读取、写入详解

2024-04-05 19:26:15

前言

C#操作MySQL大量数据最常见的操作便是 select 读取数据，然后在C#中对数据进行处理，完毕后再插入数据库中。简而言之就 select -> process -> insert 三个步骤。对于数据量小的情况下（百万级别 or 几百兆)可能

最多1个小时就处理完了。但是对于千万级数据可能几天，甚至更多。那么问题来了，如何优化？？

第一步解决读取的问题

跟数据库打交道的方式有很多，我来列举下吧：

1. 【重武器-坦克大炮】使用重型ORM框架，比如 EF,NHibernat 这样的框架。

2. 【轻武器-AK47】使用 Dapper,PetaPoco 之类，单cs文件。灵活高效，使用简单。居家越货必备（我更喜欢PetaPoco :)）

3. 【冷兵器？匕首？】使用原生的Connection、Command。然后写原生的SQL语句。。

分析：

【重武器】在我们这里肯定直接被PASS，他们应该被用在大型项目中。

【轻武器】 Dapper,PetaPoco 看过源码你会发现用到了反射，虽然使用 IL和缓存技术，但是还是会影响读取效率，PASS

好吧那就只有使用匕首，原生SQL 走起，利用 DataReader 进行高效读取，并且使用索引取数据（更快），而不是列名。

大概的代码如下：

using (var conn = new MySqlConnection("Connection String..."))
{
 conn.Open();
 //此处设置读取的超时，不然在海量数据时很容易超时
 var c = new MySqlCommand("set net_write_timeout=9999999; set net_read_timeout=9999999", conn);
 c.ExecuteNonQuery();

 MySqlCommand rcmd = new MySqlCommand();
 rcmd.Connection = conn;
 rcmd.CommandText = @"SELECT `f1`,`f2` FROM `table1`";
 //设置命令的执行超时
 rcmd.CommandTimeout = 99999999;
 var myData = rcmd.ExecuteReader();

 while (myData.Read())
 {
  var f1= myData.GetInt32(0);
  var f2= myData.GetString(1);
  //这里做数据处理....
 }
}

哈哈，怎么样，代码非常原始，还是使用索引来取数据，很容易出错。当然一切为了性能咱都忍了

第二步数据处理

其实这一步，根据你的业务需要，代码肯定不一，不过无非是一些字符串处理，类型转换的操作，这时候就是考验你的C#基础功底的时候了。以及如何高效编写正则表达式。。。

具体代码也没法写啊，先看完 CLR via C# 在来跟我讨论吧，O(∩_∩)O哈哈哈~ 跳过。。。。

第三部数据插入

如何批量插入才最高效呢？有同学会说，使用事务啊，BeginTransaction, 然后EndTransaction。恩，这个的确可以提高插入效率。但是还有更加高效的方法，那就是合并insert语句。

那么怎么合并呢？

insert into table (f1,f2) values(1,'sss'),values(2,'bbbb'),values(3,'cccc');

就是把values后面的全部用逗号，链接起来，然后一次性执行。

当然不能一次性提交个100MB的SQL执行，MySQL服务器对每次执行命令的长度是有限制的。通过 MySQL服务器端的 max_allowed_packet 属性可以查看，默认是 1MB

咱们来看看伪代码吧

 //使用StringBuilder高效拼接字符串
 var sqlBuilder = new StringBuilder();
 //添加insert 语句的头
 string sqlHeader = "insert into table1 (`f1`,`f2`) values";
 sqlBuilder.Append(sqlHeader);
 using (var conn = new MySqlConnection("Connection String..."))
 {
  conn.Open();
  //此处设置读取的超时，不然在海量数据时很容易超时
  var c = new MySqlCommand("set net_write_timeout=9999999; set net_read_timeout=9999999", conn);
  c.ExecuteNonQuery();

  MySqlCommand rcmd = new MySqlCommand();
  rcmd.Connection = conn;
  rcmd.CommandText = @"SELECT `f1`,`f2` FROM `table1`";
  //设置命令的执行超时
  rcmd.CommandTimeout = 99999999;
  var myData = rcmd.ExecuteReader();
  while (myData.Read())
  {
   var f1 = myData.GetInt32(0);
   var f2 = myData.GetString(1);
   //这里做数据处理....
   sqlBuilder.AppendFormat("({0},'{1}'),", f1,AddSlash(f2));
   if (sqlBuilder.Length >= 1024 * 1024 * 1024)//当然这里的1MB length的字符串并不等于 1MB的Packet。。。我知道：）
   {
    insertCmd.Execute(sqlBuilder.Remove(sqlBuilder.Length-1,1).ToString())//移除逗号，然后执行
    sqlBuilder.Clear();//清空
    sqlBuilder.Append(sqlHeader);//在加上insert 头
   }
  }
}

好了，到这里大概的优化后的高效查询、插入就完成了。

总结

总结下来，无非2个关键技术点， DataReader、SQL合并，都是一些老的技术啦。其实，上面的代码只能称得上高效，但是，却非常的不优雅。以上就是这篇文章的全部内容了，希望本文的内容对大家能有所帮助，如果有疑问大家可以留言交流。

C#操作mysql数据库的代码实例

直接看代码复制代码代码如下: using System;using System.Configuration;using MySql.Data.MySqlClient;/// <summary>/// TestDatebase 的摘要说明/// </summary>public class TestDatebase{ public TestDatebase() { // // TODO: 在此处添加构造函数逻辑 //
c#与mysql的连接

不只是c#,其实是说.NET下与mysql的连接,如果一般说来,因为.NET没有相应的驱动连接与其对应,是用odbc的连接,但这样的弊端是显而易见的,在sourceforge上有个开源的项目,具体下载地址是http://prdownloads.sourceforge.net/mysqldrivercs/MySQLDriverCS-n-EasyQueryTools-3.0.18.exe?use_mirror=jaist方法如下:using MySQLDriverCS; MySQLConnectio
C#连接MySql数据库的方法

1.要连接MySql数据库必须首先下载MySql官方的连接.net的文件,文件下载地址为http://dev.mysql.com/downloads/connector/net/6.6.html#downloads ,下载平台选择.Net&Mono,下载ZIP免安装版.2.解压缩刚才下载的mysql-connector-net-6.6.6-noinstall.zip文件,里面有几个版本选择,在这里我选V4, 选中这几个文件,然后添加到C#项目的引用中,然后就可以编写程序进行数据库的操作了. 3.
C#连接操作 MySQL 数据库实例(使用官方驱动)

MySQL 以其免费和足够的性能受到很大的青睐,当然对于国内小公司,甚至是大公司如果对版权看得薄,敢冒险的话,随便装个 SqlServer.DB2.Oracle 都行.对于 SqlServer 数据库,因其与 MS 是一家,自然在 .net 类库中有内建支持,假如 MySQL 就得找第三方的驱动了 -- .net 中多讲 Provider.在这里我也是作为一个预研专题,记录下 C# 连接 MySQL 的两种方法,分别使用 MySQL 官方的和 SourceForge 上一个开源的 MySQL 驱
C#实现操作MySql数据层类MysqlHelper实例

本文实例讲述了C#实现操作MySql数据层类MysqlHelper.分享给大家供大家参考.具体如下: using System; using System.Data; using System.Configuration; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml.Linq; using MySql.Data; using MySql.Data.MySqlCli
C#连接mysql数据库完整实例

本文实例讲述了C#连接mysql数据库的方法.分享给大家供大家参考.具体实现方法如下: using System; using System.Configuration; using MySql.Data.MySqlClient; /// <summary> /// TestDatebase 的摘要说明 /// </summary> public class TestDatebase { public TestDatebase() { // // TODO: 在此处添加构造函数逻辑
C# mysql 插入数据,中文乱码的解决方法

有两种解决办法: 一种是在执行语句前面设置,如: 复制代码代码如下: MySQLCommand mCommand = new MySQLCommand("set names gb2312", m_Connection); 另一种是直接加在连接字符串里,如: 复制代码代码如下: <add name="connection" connectionString="server=127.0.0.1;UId=root;PassWord=root;Persi
浅析C# web访问mysql数据库-整理归纳总结

基本对比使用方式使用场合优缺点是否需要安装需要的dll网址引用方式程序内引用程序初期确定使用MySql,前期添加引用大多数情况下使用在类文件内,多数使用于aspx,ashx等带有后置代码的类文件中可以安装,也可以直接引用dll 多数情况下直接引用即可 Connector/Net 6.7.4 web.config引用后期维护接口发现程序内需要使用,这时不方便更改原有程序引用多数时候使用于没有后置代码的aspx.ashx等文件上 ODBC方式配置系统内数据源程序创建初期,
C#处理MySql多个返回集的方法

本文实例讲述了C#处理MySql多个返回集的方法.分享给大家供大家参考.具体方法如下: 关于Mysql返回多个集java和Php的较多,但是C#的完整代码好像没见过,研究了一下做个封装以后用做一个Mysql的简单分页查询,有两个返回集 Sql语句如下复制代码代码如下: SELECT COUNT(*) from poster; select t.PosterID, t.PostTime, t.Title from app_us_poster t ORDER BY
C#在MySQL大量数据下的高效读取、写入详解

前言 C#操作MySQL大量数据最常见的操作便是 select 读取数据,然后在C#中对数据进行处理, 完毕后再插入数据库中. 简而言之就 select -> process -> insert 三个步骤. 对于数据量小的情况下(百万级别 or 几百兆)可能最多1个小时就处理完了.但是对于千万级数据可能几天,甚至更多. 那么问题来了,如何优化?? 第一步解决读取的问题跟数据库打交道的方式有很多,我来列举下吧: 1. [重武器-坦克大炮]使用重型ORM框架,比如 EF,NHibernat
C#如何在海量数据下的高效读取写入MySQL

前提由于工作的原因,经常需要对海量数据进行处理,做的数据爬虫相关,动辄千万级别的数据,单表几十个G都是都是家常便饭. 主要开发语言是C#,数据库使用的是MySQL. 最常见的操作便是 select 读取数据,然后在C#中对数据进行处理, 完毕后再插入数据库中. 简而言之就 select -> process -> insert三个步骤. 对于数据量小的情况下(百万级别 or 几百兆)可能最多1个小时就处理完了.但是对于千万级数据可能几天,甚至更多. 那么问题来了,如何优化?? (数据库的
MySQL为数据表建立索引的原则详解

目录 1.索引是什么? 2.索引的优点? 3.索引的缺点? 4.在建立索引的时候,都有哪些需要考虑的因素呢? 1.只为用于搜索.排序.分组的列创建索引 2.索引列的类型尽量小 3.为列前缀建立索引 4.覆盖索引 5.让索引列以列名的形式在搜索条件中单独出现 6.新插入记录时主键大小对效率的影响 7.冗余和重复索引总结面试题: 索引是什么? 索引的优点? 索引的缺点? 在建立索引的时候都有哪些需要考虑的因素呢? 为数据表建立索引的原则有哪些? 什么是索引覆盖? 非聚簇索引一定会回表查询吗? 1
MySQL数据备份之mysqldump的使用详解

mysqldump常用于MySQL数据库逻辑备份. 1.各种用法说明 A. 最简单的用法: mysqldump -uroot -pPassword [database name] > [dump file] 上述命令将指定数据库备份到某dump文件(转储文件)中,比如: mysqldump -uroot -p123 test > test.dump 生成的test.dump文件中包含建表语句(生成数据库结构哦)和插入数据的insert语句. B. --opt 如果加上--opt参数则生成的du
mysql下的max_allowed_packet参数设置详解

目录修改方法 1.修改配置文件 2.在mysql命令行中修改 MySQL根据配置文件会限制Server接受的数据包大小.有时候大的插入和更新会受 max_allowed_packet 参数限制,导致写入或者更新失败.(比方说导入数据库,数据表) 查看目前配置: 代码如下: mysql> show VARIABLES like '%max_allowed_packet%'; +--------------------------+------------+ | Variable_name
Linux下安装mysql 5.7.17.tar.gz的教程详解

1.创建MySQL组和mysql用户 groupadd mysql useradd -r -g mysql mysql 2.拷贝: cp -R mysql-5.7.16-linux-glibc2.5-i686 /usr/local/mysql 3.创建data目录 mkdir /usr/local/mysql/data 3.安装数据库切换到/usr/loca/mysql目录下 cd /usr/local/mysql 修改当前目录的所有者为mysql用户 chown -R mysql:mysql
MySql批量插入优化Sql执行效率实例详解

MySql批量插入优化Sql执行效率实例详解 itemcontractprice数量1万左右,每条itemcontractprice 插入5条日志. updateInsertSql.AppendFormat("UPDATE itemcontractprice AS p INNER JOIN foreigncurrency AS f ON p.ForeignCurrencyId = f.ContractPriceId SET p.RemainPrice = f.RemainPrice * {0},
MySql 5.7.17免安装配置教程详解

1.下载mysql-5.7.17-winx64.zip安装包(链接:https://dev.mysql.com/downloads/mysql/) 2.解压安装包. D:\DevelopTool\mysql-5.7.17-winx64 #解压目录 3.在解压目录下创建一个名为data的文件夹,用来存放数据 D:\DevelopTool\mysql-5.7.17-winx64\data 4.配置启动文件把 D:\DevelopTool\mysql-5.7.17-winx64\my-defau
mybatis实现对数据的增删查改实例详解

前期准备新建java工程或java wweb工程,需要导入以下的包, 基本工作已经完成,接下来开始进入正题. 新建实体类新建与数据库表对应的实体类 package com.edu.hpu.domain; /** * @author Administrator *user表所对应的实体类 */ public class User { //实体类的属性和表的字段名称一一对应 private int id; private String name; private int age; //对属性进行
MySQL 8.0.13 下载安装教程图文详解

MySQL是使用最多的数据库,自己电脑上肯定要装一个来多加学习,自己搞不懂的一些东西要多写一些 sql 语句练习. 首先去 mysql 官网下载,地址: https://dev.mysql.com/downloads/mysql/ 选择上图的这个版本就可以把 windows 版的 mysql 最新版本下载下来,点进去之后并不会立刻下载,而是会跳到下图这个页面,只要点击No thanks, just start my download.即可接下来就开始下载啦.下载完成后可以解压到你想放的地方,一

C#在MySQL大量数据下的高效读取、写入详解

相关推荐

随机推荐