ProHoster > Π‘Π»ΠΎΠ³ > Maamulka > PostgreSQL Antipatterns: Socodsiinta Diiwaanka

PostgreSQL Antipatterns: Socodsiinta Diiwaanka

Maanta ma jiri doonaan kiisas adag iyo algorithms casri ah oo ku jira SQL. Wax kastaa waxay noqon doonaan kuwo aad u fudud, heerka Captain Cad - aynu samayno daawashada diiwaanka dhacdada lagu kala soocaa waqti.

Taasi waa, calaamad ayaa ku jirta kaydka xogta events, iyaduna beer bay leedahay ts - waqtiga saxda ah ee aan rabno inaan u soo bandhigno diiwaanadan si habsami leh:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

Way caddahay in aanaan ku haysan doonin diiwaanno daraasiin ah, markaa waxaan u baahan doonaa nooc ka mid ah bogga navigation.

#0. "Waxaan ahay hooyaday pogromist"

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

Ku dhawaad ​​maaha kaftan - waa dhif, laakiin duurka ayaa laga helaa. Mararka qaarkood, ka dib markaad la shaqeyso ORM, way adkaan kartaa in loo beddelo shaqada "toos ah" ee SQL.

Laakiin aan u gudubno dhibaatooyin badan oo caan ah oo aan muuqan.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записСй Π½Π° страницС, $1 - Π½Π°Ρ‡Π°Π»ΠΎ страницы

Halkee buu ka yimid lambarka 26? Tani waa tirada ku dhow ee la geliyey si loo buuxiyo hal shaashad. Si sax ah, 25 diiwaanno la soo bandhigay, oo lagu daray 1, oo tilmaamaya inay jiraan ugu yaraan wax kale oo dheeraad ah oo ku jira muunada oo ay macno samaynayso in la sii socdo.

Dabcan, qiimahan lama "toli karo" jirka codsiga, laakiin loo maro halbeeg. Laakiin kiiskan, jadwalka PostgreSQL ma awoodi doono inuu ku tiirsanaado aqoonta ah inay jiraan diiwaanno yar - oo si fudud u dooran doona qorshe aan waxtar lahayn.

Iyo inta lagu jiro isdhexgalka arjiga, daawashada diiwaanka waxaa loo fuliyaa sida u kala beddelashada "bogagga" muuqaal ah, qofna ma dareemo wax shaki leh muddo dheer. Sida saxda ah ilaa waqtiga, halganka ku habboonaanta, UI/UX waxay go'aansataa inay dib u habeyn ku sameeyaan interface si ay u "duubtaan aan dhammaadka lahayn" - taas oo ah, dhammaan gelitaanka diiwaangelinta waxaa lagu sawiray hal liis oo isticmaaluhu kor iyo hoos u rogi karo.

Markaa, inta lagu jiro baaritaanka soo socda, waa lagu qabtaa nuqul ka mid ah diiwaanka ee diiwaanka. Waa maxay sababta, sababtoo ah miisku wuxuu leeyahay tilmaame caadi ah (ts), su'aashaadu waa kuwee?

Dhab ahaan sababtoo ah ma aadan xisaabtamin taas ts ma aha fure gaar ah shaxdan. Dhab ahaantii, iyo qiimaheedu maaha kuwo gaar ah, sida "waqtiga kasta" ee xaaladaha dhabta ah - sidaas darteed, rikoodh isku mid ah oo ku jira laba su'aalood oo isku xigta ayaa si fudud "ka booda" bog ilaa bog sababtoo ah amar kama dambays ah oo ka duwan oo ku dhex jira qaabka kala soocida qiimaha muhiimka ah ee isku midka ah.

Dhab ahaantii, waxaa sidoo kale jira dhibaato labaad oo halkan ku qarsoon, taas oo aad u adag in la ogaado - gelitaannada qaarkood lama tusi doono haba yaraatee! Ka dib oo dhan, diiwaannada "koob-buuxa" waxay qaateen meel qof kale. Sharax faahfaahsan oo leh sawirro qurux badan ayaa la heli karaa halkan ka akhriso.

Fidinta tusmada

Horumariyaha khiyaanada leh wuxuu fahamsan yahay in furaha tusaha uu u baahan yahay in la sameeyo mid gaar ah, habka ugu fududna waa in lagu ballaariyo goob muuqata oo gaar ah, kaasoo PK ku habboon:

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

Codsiguna wuu is beddelayaa:

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. U beddel "cursors"

Muddo ka dib, DBA ayaa kuu imanaysa oo "ku faraxsan" codsiyadaada waxay ku shubtaan server-ka sida cadaabta oo kale xeerarkooda OFFSET, iyo guud ahaan, waa waqtigii loo bedeli lahaa navigation laga soo bilaabo qiimihii ugu dambeeyay ee muujiyay. Weydiintaadu mar kale ayaa isbedeleysa:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- послСдниС ΠΏΠΎΠ»ΡƒΡ‡Π΅Π½Π½Ρ‹Π΅ Π½Π° ΠΏΡ€Π΅Π΄Ρ‹Π΄ΡƒΡ‰Π΅ΠΌ шагС значСния
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Waxaad neefsatay neefta ilaa ay ka timid...

#3. Tilmaamaha nadiifinta

Sababtoo ah maalin maalmaha ka mid ah DBA-gaaga ayaa akhriyay maqaal ku saabsan helitaanka tusmooyinka aan waxtarka lahayn oo taas gartay "ma ahan kii ugu dambeeyay" waqti shaambad ma fiicna. Oo haddana mar kale ayaan kuu imid - hadda aniga oo ku fekeraya in tusmadani ay tahay in ay dib ugu noqoto (ts DESC).

Laakiin waxa la sameeyo dhibaatada bilowga ah ee "boodboodka" diiwaannada u dhexeeya boggaga? .. Wax walbana waa sahlan yihiin - waxaad u baahan tahay inaad doorato blocks oo leh tiro aan la cayimin oo diiwaanno ah!

Guud ahaan, yaa noo diidaya inaan akhrino "si sax ah 26", laakiin "aan ka yarayn 26"? Tusaale ahaan, si block soo socda ay jiraan diiwaano leh macnayaal cad oo kala duwan ts - markaa dhib ma yeelan doonto "boodboodka" diiwaannada u dhexeeya blocks!

Waa kan sida tan loo gaaro:

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

Maxaa halkan ka socda?

  1. Waxaan talaabsanay 25 diiwaanka "hoos" oo hel qiimaha "xuduuda". ts.
  2. Haddii aysan jirin wax horay u jiray, ka dibna ku beddel qiimaha NULL -infinity.
  3. Waxaan ka jaraynaa dhammaan qaybaha qiyamka ee u dhexeeya qiimaha la helay ts iyo $1-beegtida laga soo gudbiyay interface-ka (qiimihii hore ee "ugu dambeeyay" ee la sameeyay).
  4. Haddii baloog lagu soo celiyo wax ka yar 26 diiwaan, waa kan ugu dambeeya.

Ama sawir la mid ah:
PostgreSQL Antipatterns: Socodsiinta Diiwaanka

Sababtoo ah hadda waxaan haysanaa Muunadu ma laha "bilaw" gaar ah, markaas ma jiraan wax naga hor istaagaya in aan "ballaadhin" codsigan jihada ka soo horjeeda iyo hirgelinta loading firfircoon ee blocks xogta "point tixraac" ee labada jiho - hoos iyo kor labadaba.

Ogow

  1. Haa, kiiskan waxaan galeynaa tusmada laba jeer, laakiin wax walba waa "si cad oo index". Sidaa darteed, dib-u-soo-celinta waxay keeni kartaa oo kaliya Hal Tilmaan oo Dheeraad ah oo Kaliya Iskaan.
  2. Waa wax iska cad in farsamadan la isticmaali karo oo keliya marka aad leedahay qiimeyaal ts ka gudbi kara oo kaliya by fursad, iyo ma badna. Haddii kiiskaaga caadiga ah uu yahay "hal milyan oo diiwaan 00:00:00.000", waa inaadan samayn tan. Waxaan ula jeedaa, waa inaadan u oggolaan kiis noocan oo kale ah inuu dhaco. Laakiin haddii ay tani dhacdo, isticmaal ikhtiyaarka leh tusmooyin la fidiyay.

Source: www.habr.com

Add a comment