ProHoster > Π‘Π»ΠΎΠ³ > Maamulka > PostgreSQL Antipatterns: "Waa inuu jiraa hal kaliya!"

PostgreSQL Antipatterns: "Waa inuu jiraa hal kaliya!"

SQL, waxaad ku qeexday "waxa" aad rabto inaad hesho, ma aha "sida" loo sameeyo. Haddaba, mushkiladda soo saarista weydiimaha SQL ee qaabka ah β€œsida la maqlo waa sida loo qoraa” waxay qaadataa meesheeda sharafta, iyadoo ay weheliso. sifooyinka gaarka ah ee qiimaynta xaalada ee SQL.

Maanta, annagoo adeegsanayna tusaalooyin aad u fudud, aan aragno waxa ay tani u horseedi karto macnaha guud ee isticmaalka GROUP/DISTINCT ΠΈ LIMIT iyaga la.

Taasi waa haddii aad ku qortay codsiga "Marka hore isku xidh kaniiniyadan, ka dibna iska tuur dhammaan nuqullada, waa in ay ahaataa mid kaliya tusaale ahaan fure kasta" - sidaan waa sida saxda ah ee ay u shaqeyn doonto, xitaa haddii xiriirka aan loo baahnayn gabi ahaanba.

Mararka qaarkoodna waad nasiib badan tahay oo "kaliya way shaqeysaa", mararka qaarkood waxay saameyn xun ku leedahay waxqabadka, mararka qaarkoodna waxay ku siinaysaa saameyn aan la filayn oo aan laga fileynin aragtida horumariyaha.

PostgreSQL Antipatterns: "Waa inuu jiraa hal kaliya!"
Hagaag, laga yaabee inaysan ahayn mid cajiib ah, laakiin…

"Lammaane macaan": KU BIIR + CAD

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

Sidee ku cadaan lahayd waxay rabeen dooro diiwaannadaas X, kuwaas oo Y ay ku jiraan shuruudo la dhammaystiray. Gudbi codsi iyada oo loo marayo JOIN - waxay heshay qaar ka mid ah qiyamka pk dhowr jeer (sida saxda ah inta diiwaan ee ku habboon ayaa noqotay Y). Sidee meesha looga saaraa? Hubaal DISTINCT!

Gaar ahaan aad bay u β€œwanaagsan tahay” marka diiwaanka-x kasta uu jiro dhowr boqol oo diiwaan-yo ah oo la xidhiidha, ka dibna nuqullada si geesinimo leh ayaa meesha looga saaray.

PostgreSQL Antipatterns: "Waa inuu jiraa hal kaliya!"

Sidee loo hagaajiyaa? Si aad u bilawdo, garwaaqso in hawsha wax laga beddeli karo "dooro diiwaannadaas X oo ay ku jiraan UGU YARAAN MID Y oo la xidhiidha shuruudaha la dhammaystiray" - ka dib oo dhan, waxba ugama baahnid diiwaanka Y laftiisa.

EXISTS

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

Noocyada qaarkood ee PostgreSQL waxay fahmeen in EXISTS ay ku filan tahay in la helo diiwaanka ugu horreeya ee soo baxa, kuwa da'da ah ma sameeyaan. Sidaa darteed, waxaan doorbidayaa inaan had iyo jeer tilmaamo LIMIT 1 gudaha EXISTS.

KU BIIR DANBE

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

Xulashada la mid ah waxay u oggolaaneysaa, haddii loo baahdo, in isla markiiba soo celiso xogta qaar ka mid ah diiwaanka Y ee la helay isla waqti isku mid ah. Doorasho la mid ah ayaa looga hadlay maqaalka "PostgreSQL Antipatterns: rikoor naadir ah ayaa gaari doona bartamaha JOIN".

"Maxaa wax badan u bixinaya": DISTINCT [ON] + LIMIT 1

Faa'iidada dheeraadka ah ee isbeddelka su'aalaha noocan oo kale ah ayaa ah awoodda si fudud loo xaddido tirinta diiwaannada haddii hal / wax yar oo iyaga ka mid ah loo baahan yahay, sida kiisaska soo socda:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Hadda waxaan akhrinay codsiga oo aan isku daynay inaan fahanno waxa DBMS laga rabo inuu sameeyo:

  • waxaan isku xireynaa taarikada
  • gaar ah by X.pk
  • dooro mid ka mid ah diiwaanada hadhay

Haddaba maxaa helay? "Hal rikoor ah" laga soo bilaabo kuwa gaarka ah - oo haddii aad qaadato mid ka mid ah kuwa aan gaarka ahayn, natiijadu si uun bay isu beddeli doontaa?

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда ΠΌΠΎΠΆΠ½ΠΎ ΠΏΠΎΠ΄ΡΡƒΠ½ΡƒΡ‚ΡŒ подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Oo runtii isla mawduuca GROUP BY + LIMIT 1.

"Kaliya waa inaan weydiiyaa": GROUP + LIMIT qarsoon

Waxyaabo la mid ah ayaa u dhaca siyaabo kala duwan hubinta marannimada calaamado ama CTE-yada marka codsigu sii socdo:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

Hawlaha guudcount/min/max/sum/...) si guul leh ayaa loo fuliyay dhammaan qaybaha, xitaa iyada oo aan si cad loo sheegin GROUP BY. Kaliya halkan leh LIMIT Aad uma saaxiibo.

Horumariyuhu wuu fikiri karaa "Hadda, haddii ay jiraan diiwaanno halkaas, markaa uma baahni wax ka badan LIMIT". Laakiin maahan inaad! Sababtoo ah saldhigga waa:

  • tiriya waxay rabaan dhammaan diiwaannada
  • sii inta sadar ee ay weydiiyaan

Iyadoo ku xiran shuruudaha bartilmaameedka, waxaa habboon in la sameeyo mid ka mid ah beddelka soo socda:

  • (count + LIMIT 1) = 0 on NOT EXISTS(LIMIT 1)
  • (count + LIMIT 1) > 0 on EXISTS(LIMIT 1)
  • count >= N on (SELECT count(*) FROM (... LIMIT N))

"Intee in le'eg ayaa lagu dhejiyaa garaam": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Horumariyaha caajiska ah ayaa laga yaabaa inuu si daacad ah u rumaysto in fulinta codsigu joogsanayo, isla marka aan helno $1 ugu horreeya qiyamka kala duwan ee soo gala.

Mararka qaarkood mustaqbalka, tani waa laga yaabaa oo shaqayn doonta iyada oo ay ugu wacan tahay noodh cusub Tusmada Skip Scan, fulintiisa oo hadda la guda galayo, balse aan weli dhicin.

Ilaa hadda marka hore dhammaan diiwaanada waa la soo ceshan doonaa, waa kuwo gaar ah, oo kaliya inta badan oo iyaga ka mid ah ayaa la soo celin doonaa. Gaar ahaan waa murugo haddii aan rabno wax la mid ah $ 1 = 4, waxaana miiska ku yaal boqollaal kun oo diiwaan...

Si aynaan u murugoon micnela'aan, waxaan isticmaali doonaa weydiimo soo noqnoqda "DISTINCT for the miskiin" ka PostgreSQL Wiki:

PostgreSQL Antipatterns: "Waa inuu jiraa hal kaliya!"

Source: www.habr.com

Add a comment