Ku saabsan sida aan u wanaajin lahaa weydiinta PostgreSQL iyo waxa ka soo baxay dhammaantood.
Maxaa ku kalifay? Haa, sababtoo ah 4-tii sano ee hore wax walba waxay u shaqeynayeen si degan, si deggan, sida saacadda.
Sida epigraph.
Iyada oo ku saleysan dhacdooyinka dhabta ah.
Dhammaan magacyada waa la bedelay, shilalka waa random.
Marka aad gaarto natiijo gaar ah, had iyo jeer waa mid xiiso leh in la xasuusto waxa dhiirigeliyay bilawga, halkaas oo ay ka bilaabmeen.
Haddaba, wixii ka dhashay waxa si kooban loogu qeexay maqaalka “".
Waxay u badan tahay inay xiiso yeelan doonto in dib loo abuuro silsiladda dhacdooyinkii hore.
Taariikhdu waxay kaydisay taariikhda saxda ah ee bilawga ah - 2018-09-10 18:02:48.
Sidoo kale, sheekadu waxa ku jira codsi ka soo bilaabmay:
Codsiga dhibaatadaDooro
p. "PARAMETER_ID" sida parameter_id,
PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" Sida macmiilka_lambarka,
w. "LRM" SIDA LRM,
w. "LOTID" sida lotid,
w. "RTD_VALUE" AS RTD_qiimaha,
w."LOWER_SPEC_LIMIT" Sida hoose_spec_limit,
w. "UPPER_SPEC_LIMIT" Sida sare_spec_limit,
p."TYPE_CALCUL"Sida nooca_calcul,
s."SPENT_NAME" AS ku bixisay_name,
s. "SPENT_DATE" SIDA la qaatay_taariikhda,
soosaar (sanad laga bilaabo "SPENT_DATE") AS sanadka,
soosaar (bil laga bilaabo "SPENT_DATE") bishii ahaan,
s."REPORT_NAME" AS warbixinta_magaca,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" Sida macmiilka param_name
Laga soo bilaabo wdata w,
kharash gareeyay s,
pmtr p,
qaatay_pd sp,
pd pd
HALKEE s. "SPENT_ID" = w. "SPENT_ID"
IYO p."PARAMETER_ID" = w."PARAMETER_ID"
IYO s. "SPENT_ID" = sp. "SPENT_ID"
IYO pd."PD_ID" = sp."PD_ID"
IYO s. "SPENT_DATE" >= '2018-07-01' IYO s. "SPENT_DATE" <= '2018-09-30'
iyo s. "SPENT_DATE" = (Xulashada MAX(s2. "SPENT_DATE")
Laga soo bilaabo s2,
wdata w2
HALKEE s2. "SPENT_ID" = w2. "SPENT_ID"
IYO w2.“LRM” = w.“LRM”);
Sharaxaadda dhibaatadu waa heer la saadaalin karo - "Wax walba waa xun yihiin. Ii sheeg waxa dhibku yahay.”
Waxaan isla markiiba xusuustay sheeko-xilliyeedka 3 inch iyo badh wadista:
Curyaanku wuxuu u yimaadaa hackerka.
-Waxba iima shaqeeyo, ii sheeg meesha dhibku ka jiro.
-DNA ku jira...
Laakiin dabcan, tani maaha habka lagu xalliyo dhacdooyinka waxqabadka. "Waxaa laga yaabaa inay na fahmaan"(oo leh). Waxaan u baahanahay inaan ogaano.
Waa hagaag, aynu qodno. Waxaa laga yaabaa inay wax ku ururaan natiijada.
Baaritaan ayaa bilaabmay
Haddaba, maxaa isla markiiba isha lagu arki karaa, iyada oo aan xitaa la isticmaalin SHARAX.
1) JOINs lama isticmaalo. Tani waa xun, gaar ahaan haddii tirada isku xirnaanta ay ka badan tahay hal.
2) Laakiin waxa ka sii daran waa is-hoosaysiin la xidhiidha, weliba, oo leh isugeyn. Tani waa mid aad u xun.
Tani waa wax xun dabcan. Laakiin tani waa hal dhinac oo keliya. Dhanka kale, tani waa mid aad u wanaagsan, sababtoo ah dhibaatadu waxay si cad u leedahay xal iyo codsi la hagaajin karo.
Ha u tegin faaliyaha (C).
Qorshaha weydiintu ma aha mid dhib badan, laakiin waa mid tilmaan leh:
Qorshaha fulinta
Midka ugu xiisaha badan iyo waxtarka leh, sida caadiga ah, waa bilowga iyo dhammaadka.
Loop Nsted (qiimaha=935.84..479763226.18 saf=3322 width=135) (waqtiga dhabta ah=31.536..8220420.295 saf=8111656 loops=1)
Waqtiga qorsheynta: 3.807 ms
Waqtiga fulinta: 8222351.640 ms
Wakhtiga dhamaystirku waa in ka badan 2 saacadood.
Mala-awaal been ah oo waqti qaatay
Mala-awaal 1 - Hagaajintu khalad buu sameeyaa oo wuxuu dhisaa qorshe khaldan.
Si loo sawiro qorshaha fulinta, waxaan isticmaali doonaa goobta . Si kastaba ha ahaatee, goobta ma muujin wax xiiso leh ama faa'iido leh. Jaleecada koowaad iyo labaad, ma jiraan wax run ahaantii caawin kara. Suurtagal ma tahay in Sawirka Buuxa uu yar yahay. Horey u soco
Mala-awaal 2-Saamaynta salka dhinaca autovacuum, waxaad u baahan tahay inaad ka takhalusto biriiga.
Laakiin daemn-yada autovacuum si fiican ayey u dhaqmaan, ma jiraan habab dheer oo laalaada. Ma jiro rar culus. Waxaan u baahanahay inaan raadino wax kale.
Mala-awaal 3 - Tirakoobku waa duugoobay, wax walba waxay u baahan yihiin in dib loo xisaabiyo
Mar labaad, taasi maaha. Tirakoobku waa ilaa hadda. Taas oo, marka loo eego dhibaatooyinka la'aanta autovacuum, maaha wax la yaab leh.
Aan bilowno hagaajinta
Shaxda ugu weyn ee 'wdata' xaqiiqdii ma yara, ku dhawaad 3 milyan oo diiwaan.
Oo waa shaxdan ka Scan buuxa raacaya.
Hash Cond: ((w"SPENT_ID" = s"SPENT_ID") IYO ((Plan 1) = s."SPENT_DATE")
-> Sawirka Seq on wdata w (qiimaha=0.00..574151.49 saf=26886249 width=46) (waqtiga dhabta ah=0.005..8153.565 saf=26873950 loops=1)
Waxaan sameynaa shayga caadiga ah: "kaalay, aan sameyno index wax walbana way duuli doonaan."
Wuxuu ka sameeyay tusaha goobta "SPENT_ID".
Natiijo ahaan:
Qorshaha fulinta weydiinta iyadoo la isticmaalayo index
Hagaag, ma caawisay?
Waxay ahayd: 8 222 351.640 ms (wax ka yar 2 saacadood)
Waxay noqotay: 6 985 431.575 ms (ku dhawaad 2 saacadood)
Guud ahaan, tufaax isku mid ah, aragtida dhinaca.
Aynu xasuusanno fasallada qadiimiga ah:
"Ma waxaad leedahay mid la mid ah, laakiin aan baalal lahayn? Doon doona".
Mabda 'ahaan, tan waxaa loogu yeeri karaa natiijo wanaagsan, si fiican, ma fiicna, laakiin la aqbali karo. Ugu yaraan, u gudbi macmiilka warbixin ballaadhan oo ku sifaynaysa inta la qabtay iyo sababta waxa la qabtay u wanaagsanaa.
Laakiin weli, go'aanka kama dambaysta ah ayaa weli fog. Aad u fog.
Oo hadda waxa ugu xiisaha badan - waxaan sii wadeynaa hagaajinta, waxaanu nadiifin doonaa codsiga
Tallaabada Koowaad - Isticmaal ku biir
Codsiga dib loo qoray hadda wuxuu u egyahay sidan (si fiican ugu yaraan ka qurux badan):
Weydii adigoo isticmaalaya JOINDooro
p. "PARAMETER_ID" sida parameter_id,
PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" Sida macmiilka_lambarka,
w. "LRM" SIDA LRM,
w. "LOTID" sida lotid,
w. "RTD_VALUE" AS RTD_qiimaha,
w."LOWER_SPEC_LIMIT" Sida hoose_spec_limit,
w. "UPPER_SPEC_LIMIT" Sida sare_spec_limit,
p."TYPE_CALCUL"Sida nooca_calcul,
s."SPENT_NAME" AS ku bixisay_name,
s. "SPENT_DATE" SIDA la qaatay_taariikhda,
soosaar (sanad laga bilaabo "SPENT_DATE") AS sanadka,
soosaar (bil laga bilaabo "SPENT_DATE") bishii ahaan,
s."REPORT_NAME" AS warbixinta_magaca,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" Sida macmiilka param_name
Laga soo bilaabo wdata iyo ku biirista gudaha waxa lagu kharash gareeyay w."SPENT_ID"=s.""SPENT_ID"
GUDAHA KU BIIR pmtr p ON p. "PARAMETER_ID" = w."PARAMETER_ID"
KU BIIR GUDAHA Kharashgareeyay_pd sp ON s. "SPENT_ID" = sp. "SPENT_ID"
GUDAHA KU BIIR pd pd ON pd. "PD_ID" = sp. "PD_ID"
HALKEE
s. "SPENT_DATE" >= '2018-07-01' IYO s. "SPENT_DATE" <= '2018-09-30'Iyo
s. "SPENT_DATE" = (Xulashada MAX(s2. "SPENT_DATE")
Laga soo bilaabo wdata w2 ku biirista gudaha waxa ay ku kharash garaysay s2 w2. "SPENT_ID"=s2."SPENT_ID"
GUDAHA KU BIIR wdata w
ON w2. "LRM" = w."LRM" );
Waqtiga qorsheynta: 2.486 ms
Waqtiga fulinta: 1223680.326 ms
Markaa, natiijada ugu horreysa.
Waxay ahayd: 6 ms (ku dhawaad 985 saacadood).
Waxay noqotay: 1 223 680.326 ms (in ka badan 20 daqiiqo).
Natiijo wanaagsan. Mabda 'ahaan, mar labaad, waan ku joojin karnaa halkaas. Laakiin waa wax aan xiiso lahayn, ma joojin kartid.
FOR
Talaabada XNUMX-aad - ka takhalusi subquery-ga la xidhiidha
Beddelay qoraalka codsiga:
Iyada oo aan lahayn hoosaadyo xidhiidhsanDooro
p. "PARAMETER_ID" sida parameter_id,
PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" Sida macmiilka_lambarka,
w. "LRM" SIDA LRM,
w. "LOTID" sida lotid,
w. "RTD_VALUE" AS RTD_qiimaha,
w."LOWER_SPEC_LIMIT" Sida hoose_spec_limit,
w. "UPPER_SPEC_LIMIT" Sida sare_spec_limit,
p."TYPE_CALCUL"Sida nooca_calcul,
s."SPENT_NAME" AS ku bixisay_name,
s. "SPENT_DATE" SIDA la qaatay_taariikhda,
soosaar (sanad laga bilaabo "SPENT_DATE") AS sanadka,
soosaar (bil laga bilaabo "SPENT_DATE") bishii ahaan,
s."REPORT_NAME" AS warbixinta_magaca,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" Sida macmiilka param_name
Laga soo bilaabo wdata w ku biirista gudaha waxa lagu kharash gareeyay s s. "SPENT_ID" = w."SPENT_ID"
GUDAHA KU BIIR pmtr p ON p. "PARAMETER_ID" = w."PARAMETER_ID"
KU BIIR GUDAHA Kharashgareeyay_pd sp ON s. "SPENT_ID" = sp. "SPENT_ID"
GUDAHA KU BIIR pd pd ON pd. "PD_ID" = sp. "PD_ID"
KU BIIR GUDAHA (Dooro w2. "LRM", MAX(s2. "SPENT_DATE")
Laga soo bilaabo s2 GUDAHA KU BIIR wdata w2 ON s2. "SPENT_ID" = w2. "SPENT_ID"
KOOXDA w2.“LRM”
md on w.“LRM” = md.“LRM”
HALKEE
s." SPENT_DATE" >= '2018-07-01' IYO s." SPENT_DATE" <= '2018-09-30';
Waqtiga qorsheynta: 2.291 ms
Waqtiga fulinta: 165021.870 ms
Waxay ahayd: 1 223 680.326 ms (in ka badan 20 daqiiqo).
Waxay noqotay: 165 021.870 ms (wax ka yar 2 daqiiqo).
Tani mar horeba aad ayay u wanaagsan tahay.
Si kastaba ha ahaatee, sida Ingiriisku yidhi "Laakiin, had iyo jeer waxaa jira laakiin" Natiijada aad u wanaagsan waa inay si toos ah u kicisaa shaki. Wax baa halkan ka khaldan.
Male-awaalka ku saabsan sixitaanka weydiinta si meesha looga saaro su'aasha hoose ee xidhiidhsan waa sax. Laakiin waxaad u baahan tahay inaad wax yar ka beddesho si natiijada ugu dambeysa ay sax u noqoto.
Natiijo ahaan, natiijada dhexdhexaadka ah ee ugu horreysa:
Weydiinta la tafatiray iyada oo aan la xidhiidhin su'aal hoosaadyoDooro
p. "PARAMETER_ID" sida parameter_id,
PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" Sida macmiilka_lambarka,
w. "LRM" SIDA LRM,
w. "LOTID" sida lotid,
w. "RTD_VALUE" AS RTD_qiimaha,
w."LOWER_SPEC_LIMIT" Sida hoose_spec_limit,
w. "UPPER_SPEC_LIMIT" Sida sare_spec_limit,
p."TYPE_CALCUL"Sida nooca_calcul,
s."SPENT_NAME" AS ku bixisay_name,
s. "SPENT_DATE" SIDA la qaatay_taariikhda,
soosaar (sanad laga bilaabo s. "SPENT_DATE") Sida sanadka,
soosaar (bisha laga bilaabo s. "SPENT_DATE") ahaan bishii,
s."REPORT_NAME" AS warbixinta_magaca,
p."STPM_NAME" AS stpm_name,
p. "CUSTOMERPARAM_NAME" Sida macmiilka param_name
Laga soo bilaabo wdata w ku biirista gudaha waxa lagu kharash gareeyay s s. "SPENT_ID" = w."SPENT_ID"
GUDAHA KU BIIR pmtr p ON p. "PARAMETER_ID" = w."PARAMETER_ID"
KU BIIR GUDAHA Kharashgareeyay_pd sp ON s. "SPENT_ID" = sp. "SPENT_ID"
GUDAHA KU BIIR pd pd ON pd. "PD_ID" = sp. "PD_ID"
KU BIIR GUDAHA ( SELECT w2. "LRM", MAX(s2. "SPENT_DATE") SIDA "SPENT_DATE"
Laga soo bilaabo s2 GUDAHA KU BIIR wdata w2 ON s2. "SPENT_ID" = w2. "SPENT_ID"
KOOXDA w2.“LRM”
md ON md. "SPENT_DATE" = s. "SPENT_DATE" IYO md. "LRM" = w. "LRM"
HALKEE
s." SPENT_DATE" >= '2018-07-01' IYO s." SPENT_DATE" <= '2018-09-30';
Waqtiga qorsheynta: 3.192 ms
Waqtiga fulinta: 208014.134 ms
Markaa, waxa aan ku dhamayno waa natiijada ugu horreysa ee la aqbali karo, taas oo aan ceeb ahayn in la tuso macmiilka:
Ku bilaabay: 8 222 351.640 ms (in ka badan 2 saacadood)
Waxaan ku guuleysanay inaan gaarno: 1 ms (wax ka yar 223 daqiiqo).
Natiijada (ku meel gaadh): 208 014.134 ms (in ka badan 3 daqiiqo).
Natiijo heer sare ah.
Natiijada
Halkaas ayaan ku joojin karnay.
LAAKIIN…
Rabitaanka cuntada ayaa la socota cunto cunid. Kii socdaa jidka wuu hagi doonaa. Natiija kastaa waa dhexdhexaad Joogsaday oo dhintay. IWM.
Aan sii wadno hagaajinta
Fikrad fiican. Gaar ahaan iyadoo la tixgelinayo in macaamiishu uusan xitaa maskaxda ku hayn. Oo xataa si xoog leh loogu talagalay.
Markaa, waa waqtigii dib u habeynta xogta macluumaadka. Qaab dhismeedka weydiinta lafteeda lama hagaajin karo (inkasta oo, sida ay gadaal u noqotay, waxaa jira ikhtiyaar lagu hubinayo in wax walba ay dhab ahaantii fashilmaan). Laakiin si aad u bilowdo hagaajinta iyo horumarinta naqshadaynta xogta xogta ayaa horeyba u ah fikrad rajo leh. Iyo tan ugu muhiimsan ee xiisaha leh. Mar labaad, xusuuso dhallinyaranimadaada. Isla markiiba ma aan noqon DBA, waxaan ku koray barnaamij-sameeyaha (BASIC, assembler, C, double-plus C, Oracle, plsql). Mawduuc xiiso leh, dabcan, xusuus-qor gooni ah ;-).
Si kastaba ha ahaatee, yeynan ku mashquulin.
Sidaas
Mise waxaa laga yaabaa in qaybintu ay ina caawin doonto?
Qaswadayaasha - "Haa, way caawisay, oo ay ku jirto hagaajinta waxqabadka."
Laakiin taasi waa sheeko gabi ahaanba ka duwan...
In la sii wado…
Source: www.habr.com