asp.net实现Postgresql快速写入/读取大量数据实例
最近因为一些项目需要大量插入数据，研究了下asp.net实现Postgresql快速写入/读取大量数据，所以留个笔记
环境及测试
使用.net驱动npgsql连接post数据库。配置：win10x64,i5-4590,16GDDR3,SSD850EVO.
postgresql9.6.3，数据库与数据都安装在SSD上，默认配置，无扩展。
CREATETABLEpublic.mesh ( xintegerNOTNULL, yintegerNOTNULL, zinteger, CONSTRAINTprimPRIMARYKEY(x,y) )
1.导入
使用数据备份，csv格式导入，文件位于机械硬盘上，480MB，数据量2500w+。
使用COPY
copymeshfrom'd:/user.csv'csv
运行时间107s
使用insert
单连接，c#releaseanycpu非调试模式。
classProgram
{
staticvoidMain(string[]args)
{
varlist=GetData("D:\\user.csv");
TimeCalc.LogStartTime();
using(varsm=newSqlManipulation(@"Strings",SqlType.PostgresQL))
{
sm.Init();
foreach(varninlist)
{
sm.ExcuteNonQuery($"insertintomesh(x,y,z)values({n.x},{n.y},{n.z})");
}
}
TimeCalc.ShowTotalDuration();
Console.ReadKey();
}
staticList<(intx,inty,intz)>GetData(stringfilepath)
{
List>list=newList<(int,int,int)>();
foreach(varninFile.ReadLines(filepath))
{
string[]x=n.Split(',');
list.Add((Convert.ToInt32(x[0]),Convert.ToInt32(x[1]),Convert.ToInt32(x[2])));
}
returnlist;
}
}
PostgresqlCPU占用率很低，但是跑了一年，程序依然不能结束，没有耐性了...，这么插入不行。
multilineinsert
使用multiline插入，一条语句插入约100条数据。
varbag=GetData("D:\\user.csv");
//使用时，直接执行stringbuilder的tostring方法。
Listlistbuilder=newList();
StringBuildersb=newStringBuilder();
for(inti=0;i
PostgresqlCPU占用率差不多27%，磁盘写入大约45MB/S，感觉就是在干活，最后时间217.36s。
改为1000一行的话，CPU占用率提高，但是磁盘写入平均来看有所降低，最后时间160.58s.
prepare语法
prepare语法可以让postgresql提前规划sql，优化性能。
使用单行插入CPU占用率不到25%，磁盘写入63MB/S左右，但是，使用单行插入的方式，效率没有改观，时间太长还是等不来结果。
使用多行插入CPU占用率30%，磁盘写入50MB/S，最后结果163.02，最后的时候出了个异常，就是最后一组数据长度不满足条件，无伤大雅。
staticvoidMain(string[]args)
{
varbag=GetData("D:\\user.csv");
Listlistbuilder=newList();
StringBuildersb=newStringBuilder();
for(inti=0;i
使用Transaction
在前面的基础上，使用事务改造。每条语句插入1000条数据，每1000条作为一个事务，CPU30%，磁盘34MB/S，耗时170.16s。
改成100条一个事务，耗时167.78s。
使用多线程
还在前面的基础上，使用多线程，每个线程建立一个连接，一个连接处理100条sql语句，每条sql语句插入1000条数据，以此种方式进行导入。注意，连接字符串可以将maxpoolsize设置大一些，我机器上实测，不设置会报连接超时错误。
CPU占用率上到80%，磁盘这里需要注意，由于生成了非常多个Postgresqlserver进程，不好统计，累积算上应该有小100MB/S，最终时间，98.18s。
使用TPL，由于Parallel.ForEach返回的结果没有检查，可能导致时间不是很准确（偏小）。
varlists=newList>();
varlistt=newList();
for(inti=0;i();
lists.Add(listt);
}
listt.Add(listbuilder[i].ToString());
}
TimeCalc.LogStartTime();
Parallel.ForEach(lists,(x)=>
{
using(varsm=newSqlManipulation(@";string;MaxPoolSize=1000;",SqlType.PostgresQL))
{
sm.Init();
foreach(varninx)
{
sm.ExcuteNonQuery(n);
}
}
});
TimeCalc.ShowTotalDuration();
写入方式
耗时（1000条/行）
COPY
107s
insert
N/A
多行insert
160.58s
prepare多行insert
163.02s
事务多行insert
170.16s
多连接多行insert
98.18s
2.写入更新
数据实时更新，数量可能继续增长，使用简单的insert或者update是不行的，操作使用postgresql9.5以后支持的新语法。
insertintomeshonconflict(x,y)doupdatesetz=excluded.z
吐槽postgresql这么晚才支持onconflict，mysql早有了...
在表中既有数据2500w+的前提下，重复往数据库里面写这些数据。这里只做多行插入更新测试，其他的结果应该差不多。
普通多行插入，耗时272.15s。
多线程插入的情况，耗时362.26s，CPU占用率一度到了100%。猜测多连接的情况下，更新互锁导致性能下降。
3.读取
Select方法
标准读取还是用select方法，ADO.NET直接读取。
使用adapter方式，耗时135.39s；使用dbreader方式，耗时71.62s。
Copy方法
postgresql的copy方法提供stdoutbinary方式，可以指定一条查询进行输出，耗时53.20s。
publicList<(intx,inty,intz)>BulkIQueryNpg()
{
List<(int,int,int)>dict=newList<(int,int,int)>();
using(varreader=((NpgsqlConnection)_conn).BeginBinaryExport("COPY(selectx,y,zfrommesh)TOSTDOUT(FORMATBINARY)"))
{
while(reader.StartRow()!=-1)
{
varx=reader.Read(NpgsqlDbType.Integer);
vary=reader.Read(NpgsqlDbType.Integer);
varz=reader.Read(NpgsqlDbType.Integer);
dict.Add((x,y,z));
}
}
returndict;
}
结论
总结测试结果，对于较多数据的情况下，可以得出以下结论：
- 向空数据表导入或者没有重复数据表的导入，优先使用COPY语句（为什么有这个前提详见P.S.）；
- 使用一条语句插入多条数据的方式能够大幅度改善插入性能，可以实验确定最优条数；
- 使用transaction或者prepare插入，在本场景中优化效果不明显；
- 使用多连接/多线程操作，速度上有优势，但是把握不好容易造成资源占用率过高，连接数太大也容易影响其他应用；
- 写入更新是postgresql新特性，使用会造成一定的性能消耗（相对直接插入）；
- 读取数据时，使用COPY语句能够获得较好的性能；
- ado.netdbreader对象由于不需要fill的过程，读取速度也较快（虽然赶不上COPY)，也可优先考虑。
P.S.
为什么不用mysql
没有最好的，只有最合适的，讲道理我也是挺喜欢用mysql的。使用postgresql的原因主要在于：
postgresql导入导出的sql指令“copy”直接支持Binary模式到stdin和stdout，如果程序想直接集成，那么用这个是比较方便的；相比较，mysql的sql语法（loaddatainfile）并不支持到stdin或者stdout，导出可以通过mysqldump.exe实现，导入暂时没什么特别好的办法（mysqlimport或许可以）。
相较于mysql缺点
postgresql使用copy导入的时候，如果目标表已经有数据，那么在有主键约束的表遇到错误时，COPY自动终止，而且可能导致不完全插入的情况，换言之，是不支持导入的过程进行update操作；mysql的load语法可以显式指定出错之后的动作（IGNORE/REPLACE），不会打断导入过程。
其他
如果需要使用mysql从程序导入数据，可以考虑先通过程序导出到文件，然后借助文件进行导入，据说效率也要比insert高出不少。
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。