最近因為一些項目需要大量插入數(shù)據(jù)，研究了下asp.net實現(xiàn)Postgresql快速寫入/讀取大量數(shù)據(jù)，所以留個筆記
環(huán)境及測試
使用.net驅(qū)動npgsql連接post數(shù)據(jù)庫。配置：win10 x64, i5-4590, 16G DDR3, SSD 850EVO.
postgresql 9.6.3，數(shù)據(jù)庫與數(shù)據(jù)都安裝在SSD上，默認(rèn)配置，無擴展。
CREATE TABLE public.mesh( x integer NOT NULL, y integer NOT NULL, z integer, CONSTRAINT prim PRIMARY KEY (x, y))
1. 導(dǎo)入
使用數(shù)據(jù)備份，csv格式導(dǎo)入，文件位于機械硬盤上，480MB，數(shù)據(jù)量2500w+。
使用COPY
copy mesh from 'd:/user.csv' csv
運行時間107s
使用insert
單連接，c# release any cpu 非調(diào)試模式。
class Program{ static void Main(string[] args) { var list = GetData("D://user.csv"); TimeCalc.LogStartTime(); using (var sm = new SqlManipulation(@"Strings", SqlType.PostgresQL)) { sm.Init(); foreach (var n in list) { sm.ExcuteNonQuery($"insert into mesh(x,y,z) values({n.x},{n.y},{n.z})"); } } TimeCalc.ShowTotalDuration(); Console.ReadKey(); } static List<(int x, int y, int z)> GetData(string filepath) { List<ValueTuple<int, int, int>> list = new List<(int, int, int)>(); foreach (var n in File.ReadLines(filepath)) { string[] x = n.Split(','); list.Add((Convert.ToInt32(x[0]), Convert.ToInt32(x[1]), Convert.ToInt32(x[2]))); } return list; }}Postgresql CPU占用率很低，但是跑了一年，程序依然不能結(jié)束，沒有耐性了...，這么插入不行。
multiline insert
使用multiline插入，一條語句插入約100條數(shù)據(jù)。
var bag = GetData("D://user.csv");//使用時，直接執(zhí)行stringbuilder的tostring方法。List<StringBuilder> listbuilder = new List<StringBuilder>();StringBuilder sb = new StringBuilder();for (int i = 0; i < bag.Count; i++){ if (i % 100 == 0) { sb = new StringBuilder(); listbuilder.Add(sb); sb.Append("insert into mesh(x,y,z) values"); sb.Append($"({bag[i].x}, {bag[i].y}, {bag[i].z})"); } else sb.Append($",({bag[i].x}, {bag[i].y}, {bag[i].z})");}Postgresql CPU占用率差不多27%，磁盤寫入大約45MB/S，感覺就是在干活，最后時間217.36s。
改為1000一行的話，CPU占用率提高，但是磁盤寫入平均來看有所降低，最后時間160.58s.
prepare語法
prepare語法可以讓postgresql提前規(guī)劃sql，優(yōu)化性能。
使用單行插入 CPU占用率不到25%，磁盤寫入63MB/S左右，但是，使用單行插入的方式，效率沒有改觀，時間太長還是等不來結(jié)果。
使用多行插入 CPU占用率30%，磁盤寫入50MB/S，最后結(jié)果163.02，最后的時候出了個異常，就是最后一組數(shù)據(jù)長度不滿足條件，無傷大雅。
static void Main(string[] args){ var bag = GetData("D://user.csv"); List<StringBuilder> listbuilder = new List<StringBuilder>(); StringBuilder sb = new StringBuilder(); for (int i = 0; i < bag.Count; i++) { if (i % 1000 == 0) { sb = new StringBuilder(); listbuilder.Add(sb); //sb.Append("insert into mesh(x,y,z) values"); sb.Append($"{bag[i].x}, {bag[i].y}, {bag[i].z}"); } else sb.Append($",{bag[i].x}, {bag[i].y}, {bag[i].z}"); } StringBuilder sbp = new StringBuilder(); sbp.Append("PREPARE insertplan ("); for (int i = 0; i < 1000; i++) { sbp.Append("int,int,int,"); } sbp.Remove(sbp.Length - 1, 1); sbp.Append(") AS INSERT INTO mesh(x, y, z) values"); for (int i = 0; i < 1000; i++) { sbp.Append($"(${i*3 + 1},${i* 3 + 2},${i*3+ 3}),"); } sbp.Remove(sbp.Length - 1, 1); TimeCalc.LogStartTime(); using (var sm = new SqlManipulation(@"string", SqlType.PostgresQL)) { sm.Init(); sm.ExcuteNonQuery(sbp.ToString()); foreach (var n in listbuilder) { sm.ExcuteNonQuery($"EXECUTE insertplan({n.ToString()})"); } } TimeCalc.ShowTotalDuration(); Console.ReadKey();}使用Transaction
在前面的基礎(chǔ)上，使用事務(wù)改造。每條語句插入1000條數(shù)據(jù)，每1000條作為一個事務(wù)，CPU 30%，磁盤34MB/S，耗時170.16s。
改成100條一個事務(wù)，耗時167.78s。
使用多線程
還在前面的基礎(chǔ)上，使用多線程，每個線程建立一個連接，一個連接處理100條sql語句，每條sql語句插入1000條數(shù)據(jù)，以此種方式進行導(dǎo)入。注意，連接字符串可以將maxpoolsize設(shè)置大一些，我機器上實測，不設(shè)置會報連接超時錯誤。
CPU占用率上到80%， 磁盤這里需要注意，由于生成了非常多個Postgresql server進程，不好統(tǒng)計，累積算上應(yīng)該有小100MB/S，最終時間，98.18s。
使用TPL，由于Parallel.ForEach返回的結(jié)果沒有檢查，可能導(dǎo)致時間不是很準(zhǔn)確（偏小）。
var lists = new List<List<string>>();var listt = new List<string>();for (int i = 0; i < listbuilder.Count; i++){ if (i % 1000 == 0) { listt = new List<string>(); lists.Add(listt); } listt.Add(listbuilder[i].ToString());}TimeCalc.LogStartTime();Parallel.ForEach(lists, (x) =>{ using (var sm = new SqlManipulation(@";string;MaxPoolSize=1000;", SqlType.PostgresQL)) { sm.Init(); foreach (var n in x) { sm.ExcuteNonQuery(n); } }});TimeCalc.ShowTotalDuration();
| 寫入方式 | 耗時（1000條/行） | |
|---|---|---|
| COPY | 107s | |
| insert | N/A | |
| 多行insert | 160.58s | |
| prepare多行insert | 163.02s | |
| 事務(wù)多行insert | 170.16s | |
| 多連接多行insert | 98.18s |
2. 寫入更新
數(shù)據(jù)實時更新，數(shù)量可能繼續(xù)增長，使用簡單的insert或者update是不行的，操作使用postgresql 9.5以后支持的新語法。
insert into mesh on conflict (x,y) do update set z = excluded.z
吐槽postgresql這么晚才支持on conflict，mysql早有了...
在表中既有數(shù)據(jù)2500w+的前提下，重復(fù)往數(shù)據(jù)庫里面寫這些數(shù)據(jù)。這里只做多行插入更新測試，其他的結(jié)果應(yīng)該差不多。
普通多行插入，耗時272.15s。
多線程插入的情況，耗時362.26s，CPU占用率一度到了100%。猜測多連接的情況下，更新互鎖導(dǎo)致性能下降。
3. 讀取
Select方法
標(biāo)準(zhǔn)讀取還是用select方法，ADO.NET直接讀取。
使用adapter方式，耗時135.39s；使用dbreader方式，耗時71.62s。
Copy方法
postgresql的copy方法提供stdout binary方式，可以指定一條查詢進行輸出，耗時53.20s。public List<(int x, int y, int z)> BulkIQueryNpg(){ List<(int, int, int)> dict = new List<(int, int, int)>(); using (var reader = ((NpgsqlConnection)_conn).BeginBinaryExport("COPY (select x,y,z from mesh) TO STDOUT (FORMAT BINARY)")) { while (reader.StartRow() != -1) { var x = reader.Read<int>(NpgsqlDbType.Integer); var y = reader.Read<int>(NpgsqlDbType.Integer); var z = reader.Read<int>(NpgsqlDbType.Integer); dict.Add((x, y, z)); } } return dict;}結(jié)論
總結(jié)測試結(jié)果，對于較多數(shù)據(jù)的情況下，可以得出以下結(jié)論：
P.S.
為什么不用mysql
沒有最好的，只有最合適的，講道理我也是挺喜歡用mysql的。使用postgresql的原因主要在于：
postgresql導(dǎo)入導(dǎo)出的sql指令“copy”直接支持Binary模式到stdin和stdout，如果程序想直接集成，那么用這個是比較方便的；相比較，mysql的sql語法（load data infile）并不支持到stdin或者stdout，導(dǎo)出可以通過mysqldump.exe實現(xiàn)，導(dǎo)入暫時沒什么特別好的辦法（mysqlimport或許可以）。
相較于mysql缺點
postgresql使用copy導(dǎo)入的時候，如果目標(biāo)表已經(jīng)有數(shù)據(jù)，那么在有主鍵約束的表遇到錯誤時，COPY自動終止，而且可能導(dǎo)致不完全插入的情況，換言之，是不支持導(dǎo)入的過程進行update操作；mysql的load語法可以顯式指定出錯之后的動作（IGNORE/REPLACE），不會打斷導(dǎo)入過程。
其他
如果需要使用mysql從程序?qū)霐?shù)據(jù)，可以考慮先通過程序?qū)С龅轿募缓蠼柚募M行導(dǎo)入，據(jù)說效率也要比insert高出不少。
以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持VeVb武林網(wǎng)。
新聞熱點
疑難解答
圖片精選
