Kuv xav kom koj nyeem cov ntawv sau tseg ntawm Vladimir Sitnikov thaum ntxov 2016 tsab ntawv ceeb toom "PostgreSQL thiab JDBC tab tom nyem tawm tag nrho cov kua txiv"
Nyob zoo tav su Kuv lub npe yog Vladimir Sitnikov. Kuv tau ua haujlwm rau NetCracker tau 10 xyoo. Thiab kuv feem ntau nyob rau hauv productivity. Txhua yam ntsig txog Java, txhua yam ntsig txog SQL yog qhov kuv nyiam.
Thiab hnub no kuv yuav tham txog qhov peb tau ntsib hauv lub tuam txhab thaum peb pib siv PostgreSQL ua database server. Thiab peb feem ntau ua haujlwm nrog Java. Tab sis qhov kuv yuav qhia rau koj hnub no tsis yog hais txog Java xwb. Raws li kev xyaum qhia, qhov no kuj tshwm sim ua lwm yam lus.
Peb yuav tham:
- hais txog cov ntaub ntawv sampling.
- Txog kev txuag cov ntaub ntawv.
- Thiab tseem hais txog kev ua haujlwm.
- Thiab hais txog cov underwater rakes uas faus rau ntawd.
Cia peb pib nrog ib lo lus nug yooj yim. Peb xaiv ib kab ntawm lub rooj raws li tus yuam sij tseem ceeb.
Lub database yog nyob rau tib lub party. Thiab tag nrho cov kev ua liaj ua teb no siv sijhawm 20 milliseconds.
Cov 20 milliseconds no ntau heev. Yog tias koj muaj 100 qhov kev thov, ces koj siv sij hawm ib pliag scrolling los ntawm cov kev thov no, piv txwv li peb nkim sij hawm.
Peb tsis nyiam ua qhov no thiab saib dab tsi lub hauv paus muab rau peb rau qhov no. Lub database muab peb ob txoj kev xaiv rau kev ua cov lus nug.
Thawj qhov kev xaiv yog qhov kev thov yooj yim. Dab tsi yog qhov zoo ntawm nws? Qhov tseeb tias peb coj nws thiab xa nws, thiab tsis muaj dab tsi ntxiv.
Lub database kuj muaj cov lus nug siab heev, uas yog qhov nyuaj dua, tab sis ua haujlwm ntau dua. Koj tuaj yeem xa ib daim ntawv thov rau parsing, execution, variable binding, thiab lwm yam.
Super ncua kev nug yog qee yam uas peb yuav tsis suav nrog hauv daim ntawv tshaj tawm tam sim no. Peb, tej zaum, xav tau ib yam dab tsi los ntawm cov ntaub ntawv thiab muaj cov npe xav tau uas tau tsim nyob rau hauv qee daim ntawv, piv txwv li qhov no yog qhov peb xav tau, tab sis nws tsis tuaj yeem tam sim no thiab xyoo tom ntej. Yog li peb cia li kaw nws thiab peb yuav mus ncig ua kom cov neeg tseem ceeb.
Thiab qhov peb tuaj yeem ua tau yog cov lus nug yooj yim thiab cov lus nug txuas ntxiv.
Dab tsi yog qhov tshwj xeeb ntawm txhua txoj hauv kev?
Ib qho lus nug yooj yim yog qhov zoo rau kev ua tiav ib zaug. Ib zaug ua thiab tsis nco qab lawm. Thiab qhov teeb meem yog tias nws tsis txhawb cov ntaub ntawv binary, piv txwv li nws tsis haum rau qee qhov kev ua haujlwm siab.
Cov lus nug txuas ntxiv - tso cai rau koj kom txuag lub sijhawm ntawm kev txheeb xyuas. Nov yog qhov peb tau ua thiab pib siv. Qhov no tiag tiag, pab tau peb tiag tiag. Tsis tsuas yog txuag ntawm kev sib cais. Muaj kev txuag ntawm kev hloov cov ntaub ntawv. Hloov cov ntaub ntawv nyob rau hauv binary hom yog ntau npaum li cas.
Cia peb mus xyaum. Qhov no yog li cas ib daim ntawv thov zoo li. Nws tuaj yeem yog Java, thiab lwm yam.
Peb tsim cov lus. Ua tiav cov lus txib. Tsim nyob ze. Qhov yuam kev no nyob qhov twg? Qhov teeb meem yog dab tsi? Tsis muaj teeb meem. Nov yog qhov nws hais hauv txhua phau ntawv. Qhov no yog li cas nws yuav tsum tau sau. Yog tias koj xav tau kev ua haujlwm siab tshaj plaws, sau li no.
Tab sis kev xyaum tau qhia tias qhov no tsis ua haujlwm. Vim li cas? Vim peb muaj txoj kev "kaw". Thiab thaum peb ua qhov no, los ntawm cov ntaub ntawv ntawm qhov pom nws hloov tawm tias nws zoo li tus neeg haus luam yeeb ua haujlwm nrog cov ntaub ntawv. Peb hais tias "PARSE EXECUTE DEALLOCATE".
Yog vim li cas tag nrho cov no ntxiv creation thiab unloading ntawm nqe lus? Tsis muaj leej twg xav tau lawv. Tab sis dab tsi feem ntau tshwm sim hauv Cov Ntawv Tshaj Tawm yog tias thaum peb kaw lawv, lawv kaw txhua yam ntawm cov ntaub ntawv. Qhov no tsis yog qhov peb xav tau.
Peb xav, zoo li cov neeg noj qab haus huv, ua haujlwm nrog lub hauv paus. Peb coj thiab npaj peb nqe lus ib zaug, ces peb ua nws ntau zaus. Qhov tseeb, ntau zaus - qhov no yog ib zaug nyob rau hauv tag nrho lub neej ntawm daim ntawv thov - lawv tau parsed. Thiab peb siv tib nqe lus id ntawm RESTs sib txawv. Nov yog peb lub hom phiaj.
Peb tuaj yeem ua tiav qhov no li cas?
Nws yooj yim heev - tsis tas yuav kaw cov lus. Peb sau nws li no: "npaj" "execute".
Yog tias peb tso ib yam dab tsi zoo li no, ces nws pom tseeb tias qee yam yuav dhau mus rau qhov chaw. Yog tias nws tsis meej, koj tuaj yeem sim nws. Cia peb sau ib qho piv txwv uas siv txoj kev yooj yim no. Tsim ib nqe lus. Peb tso nws rau ntawm ib co version ntawm tus neeg tsav tsheb thiab pom tias nws crashes sai heev nrog rau tag nrho cov nco uas nws muaj.
Nws yog qhov tseeb tias cov kev ua yuam kev no tau kho tau yooj yim. Kuv yuav tsis tham txog lawv. Tab sis kuv yuav hais tias qhov tshiab version ua haujlwm sai dua. Txoj kev yog ruam, tab sis tseem.
Yuav ua li cas kom raug? Peb yuav tsum ua li cas rau qhov no?
Hauv kev muaj tiag, cov ntawv thov ib txwm kaw cov lus. Hauv txhua phau ntawv lawv hais kom kaw, txwv tsis pub lub cim xeeb yuav xau.
Thiab PostgreSQL tsis paub yuav ua li cas cache cov lus nug. Nws yog qhov tsim nyog uas txhua qhov kev sib tham tsim cov cache no rau nws tus kheej.
Thiab peb tsis xav nkim sijhawm rau kev txheeb xyuas ib yam.
Thiab raws li niaj zaus peb muaj ob txoj kev xaiv.
Thawj qhov kev xaiv yog tias peb coj nws thiab hais tias cia peb qhwv txhua yam hauv PgSQL. Muaj ib lub cache nyob ntawd. Nws caches txhua yam. Nws yuav tig tawm zoo heev. Peb pom qhov no. Peb muaj 100500 thov. Tsis ua hauj lwm. Peb tsis pom zoo tig cov lus thov mus rau hauv cov txheej txheem manually. Tsis muaj.
Peb muaj kev xaiv thib ob - coj nws thiab txiav nws tus kheej. Peb qhib cov peev txheej thiab pib txiav. Peb pom thiab pom. Nws muab tawm hais tias nws tsis yog li ntawd nyuaj ua.
Qhov no tshwm sim thaum Lub Yim Hli 2015. Tam sim no muaj ib tug niaj hnub version. Thiab txhua yam zoo heev. Nws ua haujlwm zoo heev uas peb tsis hloov dab tsi hauv daim ntawv thov. Thiab peb txawm tso tseg kev xav hauv kev coj ntawm PgSQL, piv txwv li qhov no txaus rau peb kom txo tau tag nrho cov nqi nyiaj siv ua haujlwm yuav luag xoom.
Raws li, Server-npaj nqe lus tau qhib rau ntawm 5th ua tiav txhawm rau kom tsis txhob nkim lub cim xeeb hauv cov ntaub ntawv ntawm txhua qhov kev thov ib zaug.
Tej zaum koj yuav nug - tus lej nyob qhov twg? Koj tau txais dab tsi? Thiab ntawm no kuv yuav tsis muab tus lej, vim tias txhua qhov kev thov muaj nws tus kheej.
Peb cov lus nug yog qhov uas peb tau siv li 20 milliseconds ntawm kev txheeb xyuas ntawm OLTP cov lus nug. Muaj 0,5 milliseconds rau kev tua, 20 milliseconds rau parsing. Thov - 10 KiB ntawm cov ntawv nyeem, 170 kab ntawm txoj kev npaj. Qhov no yog qhov kev thov OLTP. Nws thov 1, 5, 10 kab, qee zaum ntau dua.
Tab sis peb tsis xav nkim 20 milliseconds txhua. Peb txo nws mus rau 0. Txhua yam zoo heev.
Koj tuaj yeem nqa dab tsi tawm ntawm no? Yog tias koj muaj Java, tom qab ntawd koj coj tus tsav tsheb niaj hnub thiab zoo siab.
Yog tias koj hais lwm hom lus, ces xav - tej zaum koj xav tau qhov no thiab? Vim tias los ntawm qhov pom ntawm cov lus kawg, piv txwv li, yog PL 8 lossis koj muaj LibPQ, ces nws tsis pom tseeb rau koj tias koj siv sijhawm tsis ua tiav, ntawm kev txheeb xyuas, thiab qhov no tsim nyog kuaj xyuas. Yuav ua li cas? Txhua yam yog dawb.
Tsuas yog tias muaj qhov yuam kev thiab qee qhov peculiarities. Thiab peb yuav tham txog lawv tam sim no. Feem ntau ntawm nws yuav yog hais txog kev lag luam archaeology, txog qhov peb pom, qhov peb tuaj hla.
Yog hais tias qhov kev thov yog generated dynamically. Nws tshwm sim. Ib tug neeg glues cov hlua ua ke, ua rau SQL lus nug.
Vim li cas nws phem? Nws yog qhov phem vim txhua zaus peb xaus nrog txoj hlua sib txawv.
Thiab tus hashCode ntawm txoj hlua sib txawv no yuav tsum tau nyeem dua. Qhov no yog ib txoj haujlwm CPU tiag tiag - nrhiav cov ntawv thov ntev hauv txawm tias tus hash uas twb muaj lawm tsis yooj yim li. Yog li ntawd, qhov xaus yog yooj yim - tsis txhob tsim kev thov. Khaws lawv rau hauv ib qho kev sib txawv. Thiab zoo siab.
Tom ntej no teeb meem. Hom ntaub ntawv tseem ceeb. Muaj ORMs uas hais tias nws tsis muaj teeb meem dab tsi ntawm NULL muaj, cia muaj qee yam. Yog Int, ces peb hais tias setInt. Thiab yog NULL, ces cia nws ib txwm yog VARCHAR. Thiab qhov txawv nws ua rau thaum kawg dab tsi NULL muaj? Lub database nws tus kheej yuav nkag siab txhua yam. Thiab daim duab no tsis ua haujlwm.
Hauv kev xyaum, cov ntaub ntawv database tsis tu txhua. Yog tias koj hais thawj zaug tias qhov no yog tus lej, thiab zaum ob koj hais tias nws yog VARCHAR, ces nws tsis tuaj yeem rov siv cov lus uas tau npaj los ntawm Server. Thiab qhov no, peb yuav tsum rov tsim peb cov lus.
Yog tias koj tab tom ua tib cov lus nug, xyuas kom meej tias cov ntaub ntawv hom hauv koj kem tsis meej pem. Koj yuav tsum tau saib xyuas rau NULL. Qhov no yog ib qho kev ua yuam kev uas peb muaj tom qab peb pib siv Cov Ntawv Tshaj Tawm
Okay, tig rau. Tej zaum lawv coj tus tsav tsheb. Thiab productivity poob. Tej yam tau phem.
Qhov no tshwm sim li cas? Qhov no puas yog kab laum lossis qhov tshwj xeeb? Hmoov tsis zoo, nws tsis tuaj yeem nkag siab seb qhov no puas yog kab laum lossis qhov tshwj xeeb. Tab sis muaj ib tug scenario yooj yim heev rau reproducing qhov teeb meem no. Nws kiag li npaj txhij txog ambushed peb. Thiab nws muaj xws li sampling lus los ntawm ib lub rooj. Peb, tau kawg, muaj ntau qhov kev thov. Raws li txoj cai, lawv suav nrog ob lossis peb lub rooj, tab sis muaj qhov xwm txheej zoo li no. Nqa ib qho version ntawm koj lub database thiab ua si nws.
Lub ntsiab lus yog tias peb muaj ob kab ntawv, txhua tus yog indexed. Muaj ib lab kab hauv ib kab NULL. Thiab kab thib ob tsuas muaj 20 kab xwb. Thaum peb ua haujlwm yam tsis muaj qhov sib txawv, txhua yam ua haujlwm zoo.
Yog tias peb pib ua tiav nrog cov kev sib txawv, piv txwv li peb ua qhov "?" lossis "$1" rau peb qhov kev thov, peb yuav tau txais dab tsi?
Thawj qhov kev ua tiav yog raws li qhov xav tau. Qhov thib ob yog ceev me ntsis. Ib yam dab tsi tau cached. Peb, plaub, tsib. Tom qab ntawd bang - thiab ib yam dab tsi zoo li ntawd. Thiab qhov phem tshaj plaws yog qhov no tshwm sim ntawm kev tua thib rau. Leej twg paub tias yuav tsum ua kom tiav rau qhov kev txiav txim kom nkag siab tias qhov kev txiav txim tiag tiag yog dab tsi?
Leej twg yog tus txhaum? Dab tsi tshwm sim? Lub database muaj optimization. Thiab zoo li yuav optimized rau cov ntaub ntawv generic. Thiab, raws li, pib ntawm qee lub sijhawm, nws hloov mus rau txoj kev npaj dav dav, uas, hmoov tsis, yuav tig tawm txawv. Tej zaum nws yuav zoo li qub, lossis nws yuav txawv. Thiab muaj qee yam ntawm tus nqi pib uas ua rau tus cwj pwm no.
Koj tuaj yeem ua li cas txog nws? Ntawm no, tau kawg, nws yog qhov nyuaj dua los xav tias txhua yam. Muaj ib qho kev daws teeb meem uas peb siv. Qhov no yog +0, OFFSET 0. Muaj tseeb koj paub cov kev daws teeb meem. Peb tsuas yog coj nws thiab ntxiv "+0" rau qhov kev thov thiab txhua yam zoo. Kuv mam qhia koj tom qab.
Thiab muaj lwm txoj kev xaiv - saib cov phiaj xwm kom zoo dua. Tus tsim tawm yuav tsum tsis tsuas yog sau ib daim ntawv thov, tab sis kuj hais tias "piav tshuaj ntsuam" 6 zaug. Yog tias nws yog 5, nws yuav tsis ua haujlwm.
Thiab muaj qhov kev xaiv thib peb - ββsau ntawv rau pgsql-hackers. Kuv tau sau, txawm li cas los xij, nws tseem tsis tau paub meej tias qhov no puas yog kab laum lossis qhov tshwj xeeb.
Thaum peb tab tom xav seb qhov no puas yog kab laum lossis qhov tshwj xeeb, cia peb kho nws. Cia peb muab peb qhov kev thov thiab ntxiv "+0". Txhua yam zoo. Ob lub cim thiab koj tsis tas yuav xav txog seb nws yog dab tsi lossis nws yog dab tsi. Yooj yim heev. Peb tsuas txwv tsis pub cov ntaub ntawv los ntawm kev siv qhov ntsuas ntawm kab ntawv no. Peb tsis muaj qhov ntsuas ntawm "+0" kem thiab qhov ntawd yog nws, database tsis siv qhov ntsuas, txhua yam zoo.
Qhov no yog txoj cai ntawm 6 piav qhia. Tam sim no nyob rau hauv tam sim no versions koj yuav tsum ua nws 6 lub sij hawm yog hais tias koj muaj bound variables. Yog tias koj tsis muaj qhov txawv txav, qhov no yog qhov peb ua. Thiab thaum kawg nws yog precisely no thov uas tsis. Nws tsis yog ib qho nyuaj.
Nws yuav zoo li, ntau npaum li cas tau? Ib kab ntawm no, kab nyob ntawd. Qhov tseeb, kab laum nyob txhua qhov chaw.
Cia wb mus saib ntxiv. Piv txwv li, peb muaj ob schemas. Scheme A nrog lub rooj S thiab daim duab B nrog lub rooj S. Lus nug - xaiv cov ntaub ntawv los ntawm lub rooj. Peb yuav muaj dab tsi hauv qhov no? Peb yuav muaj qhov yuam kev. Peb yuav muaj tag nrho cov saum toj no. Txoj cai yog - kab laum nyob txhua qhov chaw, peb yuav muaj tag nrho cov saum toj no.
Tam sim no lo lus nug yog: "Vim li cas?" Nws yuav zoo li tias muaj cov ntaub ntawv hais tias yog peb muaj schema, ces muaj qhov "search_path" sib txawv uas qhia peb qhov twg los nrhiav lub rooj. Nws yuav zoo li muaj qhov sib txawv.
Qhov teeb meem yog dab tsi? Qhov teeb meem yog tias server-npaj nqe lus tsis xav tias search_path tuaj yeem hloov pauv los ntawm ib tus neeg. Tus nqi no tseem nyob, raws li nws tau, tas li rau cov ntaub ntawv. Thiab qee qhov chaw yuav tsis khaws cov ntsiab lus tshiab.
Tau kawg, qhov no nyob ntawm qhov version koj tab tom sim. Nyob ntawm seb koj lub rooj sib txawv npaum li cas. Thiab version 9.1 yuav tsuas ua raws li cov lus thov qub. Cov ntawv tshiab tuaj yeem ntes cov kab mob thiab qhia koj tias koj muaj kab mob.
Yuav kho li cas? Muaj ib daim ntawv qhia yooj yim - tsis txhob ua. Tsis tas yuav hloov search_path thaum daim ntawv thov ua haujlwm. Yog tias koj hloov, nws yog qhov zoo dua los tsim kev sib txuas tshiab.
Koj tuaj yeem sib tham, piv txwv li qhib, sib tham, ntxiv. Tej zaum peb tuaj yeem ntseeg cov neeg tsim khoom lag luam tias thaum ib tus neeg hloov pauv tus nqi, cov ntaub ntawv yuav tsum qhia tus neeg siv khoom txog qhov no: "Saib, koj tus nqi tau hloov kho ntawm no. Tej zaum koj yuav tau rov pib dua cov nqe lus thiab rov tsim dua lawv? Tam sim no cov ntaub ntawv khaws cia tsis pub leej twg paub thiab tsis tshaj tawm txhua txoj hauv kev uas cov lus tau hloov pauv qhov chaw sab hauv.
Thiab kuv yuav hais ntxiv - qhov no yog ib yam dab tsi uas tsis raug rau Java. Peb yuav pom tib yam hauv PL/pgSQL ib rau ib qho. Tab sis nws yuav rov tsim dua muaj.
Cia peb sim xaiv cov ntaub ntawv ntxiv. Peb xaiv thiab xaiv. Peb muaj ib lub rooj nrog ib lab kab. Txhua kab yog ib kilobyte. Kwv yees li ib gigabyte ntawm cov ntaub ntawv. Thiab peb muaj lub cim xeeb ua haujlwm hauv Java tshuab ntawm 128 megabytes.
Peb, raws li pom zoo nyob rau hauv tag nrho cov phau ntawv, siv kwj ua. Ntawd yog, peb qhib ResultSet thiab nyeem cov ntaub ntawv los ntawm qhov ntawd me ntsis. Nws puas yuav ua haujlwm? Nws puas yuav poob los ntawm kev nco? Koj puas yuav nyeem me ntsis? Cia peb tso siab rau hauv cov ntaub ntawv, cia siab rau Postgres. Peb tsis ntseeg nws. Peb puas yuav poob OutOFMemory? Leej twg tau ntsib OutOfMemory? Leej twg tswj kho nws tom qab ntawd? Ib tug neeg tswj xyuas nws.
Yog tias koj muaj ib lab kab, koj tsis tuaj yeem xaiv thiab xaiv. Yuav tsum tau OFFSET/LIMIT. Leej twg yog rau qhov kev xaiv no? Thiab leej twg yog tus nyiam ua si nrog autoCommit?
Ntawm no, raws li niaj zaus, qhov kev xaiv uas tsis xav txog tshaj plaws hloov mus rau qhov tseeb. Thiab yog tias koj dheev tua autoCommit, nws yuav pab tau. Yog vim li cas? Science tsis paub txog qhov no.
Tab sis los ntawm lub neej ntawd, txhua tus neeg siv txuas mus rau Postgres database nqa tag nrho cov ntaub ntawv. PgJDBC tsis muaj qhov tshwj xeeb hauv qhov no; nws xaiv txhua kab.
Muaj qhov hloov pauv ntawm FetchSize lub ntsiab lus, piv txwv li koj tuaj yeem hais nyob rau theem ntawm cov lus sib cais tias ntawm no, thov xaiv cov ntaub ntawv los ntawm 10, 50. Tab sis qhov no tsis ua haujlwm kom txog thaum koj tua autoCommit. Tua tawm autoCommit - nws pib ua haujlwm.
Tab sis mus dhau qhov chaws thiab teeb tsa setFetchSize txhua qhov chaw tsis yooj yim. Yog li ntawd, peb tau ua qhov teeb tsa uas yuav hais tias tus nqi pib rau tag nrho kev sib txuas.
Yog li peb hais. Cov parameter tau teeb tsa. Thiab peb tau txais dab tsi? Yog tias peb xaiv cov me me, yog tias, piv txwv li, peb xaiv 10 kab ib zaug, ces peb muaj cov nqi siv nyiaj ntau heev. Yog li ntawd, tus nqi no yuav tsum tau teem rau ib puas.
Qhov zoo tshaj plaws, ntawm chav kawm, koj tseem yuav tsum kawm yuav ua li cas txwv nws hauv bytes, tab sis daim ntawv qhia yog qhov no: teeb defaultRowFetchSize rau ntau tshaj ib puas thiab zoo siab.
Cia peb mus rau ntxig cov ntaub ntawv. Insertion yog yooj yim dua, muaj ntau txoj kev xaiv. Piv txwv li, INSERT, VALUES. Qhov no yog ib qho kev xaiv zoo. Koj tuaj yeem hais tias "INSERT SELECT". Hauv kev xyaum nws yog tib yam. Tsis muaj qhov sib txawv ntawm kev ua haujlwm.
Phau ntawv hais tias koj yuav tsum tau ua raws li Batch nqe lus, cov phau ntawv hais tias koj tuaj yeem ua tiav cov lus txib nyuaj nrog ntau lub voj voog. Thiab Postgres muaj qhov zoo tshaj plaws - koj tuaj yeem ua COPY, piv txwv li ua nws sai dua.
Yog tias koj ntsuas nws, koj tuaj yeem rov ua qee qhov kev tshawb pom zoo. Peb xav kom qhov no ua haujlwm li cas? Peb xav kom tsis txhob txheeb xyuas thiab tsis ua cov lus txib tsis tsim nyog.
Hauv kev xyaum, TCP tsis tso cai rau peb ua qhov no. Yog tias tus neeg siv khoom tsis khoom xa ib qho kev thov, ces lub database tsis nyeem cov lus thov hauv kev sim xa peb cov lus teb. Qhov kawg tshwm sim yog tias tus neeg siv khoom tos rau cov ntaub ntawv los nyeem cov lus thov, thiab cov ntaub ntawv tos tus neeg siv los nyeem cov lus teb.
Thiab yog li ntawd tus neeg siv khoom raug yuam kom xa ib pob ntawv synchronization. Ntxiv kev sib cuam tshuam hauv network, nkim sijhawm ntxiv.
Thiab qhov ntau peb ntxiv lawv, qhov phem nws tau txais. Tus tsav tsheb yog qhov tsis zoo heev thiab ntxiv rau lawv ntau zaus, txog ib zaug txhua 200 kab, nyob ntawm qhov loj ntawm kab, thiab lwm yam.
Nws tshwm sim tias koj kho ib kab xwb thiab txhua yam yuav nrawm 10 zaug. Nws tshwm sim. Vim li cas? Raws li ib txwm muaj, qhov tsis tu ncua zoo li no twb tau siv rau qhov chaw. Thiab tus nqi "128" txhais tau tias tsis siv batching.
Nws yog qhov zoo uas qhov no tsis suav nrog hauv official version. Tshawb pom ua ntej qhov kev tso tawm pib. Txhua lub ntsiab lus kuv muab yog raws li niaj hnub versions.
Wb sim nws rau. Peb ntsuas InsertBatch yooj yim. Peb ntsuas InsertBatch ntau zaus, piv txwv li tib yam, tab sis muaj ntau yam nqi. Tricky txav. Tsis yog txhua tus tuaj yeem ua qhov no, tab sis nws yog qhov yooj yim txav, yooj yim dua li COPY.
Koj tuaj yeem ua COPY.
Thiab koj tuaj yeem ua qhov no ntawm cov qauv. Tshaj tawm hom neeg siv hom, hla array thiab INSERT ncaj qha mus rau lub rooj.
Yog tias koj qhib qhov txuas: pgjdbc/ubenchmsrk/InsertBatch.java, ces qhov chaws no nyob ntawm GitHub. Koj tuaj yeem pom tshwj xeeb tias qhov kev thov twg raug tsim muaj. Tsis muaj teeb meem.
Peb launched. Thiab thawj qhov uas peb pom tau hais tias tsis siv batch tsuas yog ua tsis tau. Txhua qhov kev xaiv batching yog xoom, piv txwv li lub sijhawm ua tiav yog xoom piv rau kev ua tiav ib zaug.
Peb ntxig cov ntaub ntawv. Nws yog ib lub rooj yooj yim heev. Peb kab. Thiab peb pom dab tsi ntawm no? Peb pom tias tag nrho peb ntawm cov kev xaiv no yog kwv yees sib piv. Thiab COPY yog, ntawm chav kawm, zoo dua.
Qhov no yog thaum peb ntxig cov khoom. Thaum peb hais tias ib tug VALUES tus nqi, ob VALUES qhov tseem ceeb, peb VALUES qhov tseem ceeb, los yog peb qhia 10 ntawm lawv sib cais los ntawm ib qho comma. Qhov no tsuas yog kab rov tav tam sim no. 1, 2, 4, 128. Nws tuaj yeem pom tias Batch Insert, uas yog kos rau hauv xiav, ua rau nws zoo siab heev. Ntawd yog, thaum koj ntxig ib zaug ib zaug lossis txawm tias thaum koj ntxig plaub zaug ib zaug, nws dhau los ua ob zaug zoo, tsuas yog vim tias peb crammed me ntsis ntxiv rau hauv VALUES. Tsawg dua EXECUTE cov haujlwm.
Kev siv COPY ntawm cov ntim me me yog qhov tsis muaj txiaj ntsig. Kuv tsis txawm kos rau thawj ob. Lawv mus saum ntuj, uas yog, cov lej ntsuab no rau COPY.
COPY yuav tsum tau siv thaum koj muaj tsawg kawg yog ib puas kab ntawm cov ntaub ntawv. Cov nyiaj siv ua haujlwm ntawm kev qhib qhov kev sib txuas no loj heev. Thiab, kom ncaj ncees, kuv tsis tau khawb rau qhov kev taw qhia no. Kuv optimized Batch, tab sis tsis COPY.
Peb yuav ua li cas ntxiv? Peb sim nws rau. Peb nkag siab tias peb yuav tsum siv cov qauv los yog ib lub bath ntse uas ua ke nrog ntau lub ntsiab lus.
Koj yuav tsum muab dab tsi tawm ntawm daim ntawv tshaj tawm hnub no?
- Npaj Cov Lus Qhia yog peb txhua yam. Qhov no muab ntau rau productivity. Nws tsim ib tug loj flop nyob rau hauv cov tshuaj pleev.
- Thiab koj yuav tsum ua TEEB MEEM TIV THAIV 6 zaug.
- Thiab peb yuav tsum tau dilute OFFSET 0, thiab ua kom yuam kev zoo li +0 txhawm rau kho qhov feem pua ββββntawm peb cov lus nug uas muaj teeb meem.
Tau qhov twg los: www.hab.com