PostgreSQL Antipatterns: የጥንቸሉ ጉድጓድ ምን ያህል ጥልቅ ነው? በተዋረድ እንሂድ

ውስብስብ የኢአርፒ ስርዓቶች ውስጥ ብዙ አካላት ተዋረዳዊ ተፈጥሮ አላቸው።ተመሳሳይ የሆኑ ነገሮች ሲሰለፉ የአያት-ዘር ግንኙነት ዛፍ - ይህ የድርጅቱ ድርጅታዊ መዋቅር ነው (እነዚህ ሁሉ ቅርንጫፎች, ክፍሎች እና የስራ ቡድኖች), እና የእቃዎች ካታሎግ, እና የስራ ቦታዎች, እና የሽያጭ ቦታዎች ጂኦግራፊ, ...

እንደውም አንድም የለም። የንግድ አውቶማቲክ አካባቢዎችበውጤቱ ምንም ዓይነት ተዋረድ በማይኖርበት ጊዜ። ነገር ግን "ለንግድ ስራ" ባይሰሩም, የተዋረድ ግንኙነቶችን በቀላሉ ማግኘት ይችላሉ. በጣም ቀላል ነው፣ በገበያ ማእከል ውስጥ ያለው የቤተሰብዎ ዛፍ ወይም የወለል ፕላን እንኳን ተመሳሳይ መዋቅር ነው።

በዲቢኤምኤስ ውስጥ እንዲህ ዓይነቱን ዛፍ ለማከማቸት ብዙ መንገዶች አሉ ፣ ግን ዛሬ በአንድ አማራጭ ላይ ብቻ እናተኩራለን-

CREATE TABLE hier(
  id
    integer
      PRIMARY KEY
, pid
    integer
      REFERENCES hier
, data
    json
);

CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK

እና የስልጣኑን ጥልቀት እየፈተሽክ ሳለ፣ ከእንደዚህ አይነት መዋቅር ጋር የመሥራትህ "የዋህ" መንገዶች ምን ያህል ውጤታማ እንደሚሆን ለማየት በትዕግስት እየጠበቀ ነው።

የሚከሰቱ ዓይነተኛ ችግሮችን እንመልከታቸው፣ አተገባበራቸውን በ SQL እና አፈጻጸማቸውን ለማሻሻል እንሞክር።

#1. የጥንቸሉ ጉድጓድ ምን ያህል ጥልቅ ነው?

እኛ, በእርግጠኝነት, ይህ መዋቅር በድርጅቱ መዋቅር ውስጥ ክፍሎችን የበታችነት የሚያንፀባርቅ መሆኑን እንቀበላለን: ክፍሎች, ክፍሎች, ዘርፎች, ቅርንጫፎች, የስራ ቡድኖች ... - የሚጠራቸው.

በመጀመሪያ፣ 10ሺህ ንጥረ ነገሮችን ‘ዛፍ’ እናምጣ

INSERT INTO hier
WITH RECURSIVE T AS (
  SELECT
    1::integer id
  , '{1}'::integer[] pids
UNION ALL
  SELECT
    id + 1
  , pids[1:(random() * array_length(pids, 1))::integer] || (id + 1)
  FROM
    T
  WHERE
    id < 10000
)
SELECT
  pids[array_length(pids, 1)] id
, pids[array_length(pids, 1) - 1] pid
FROM
  T;

በጣም ቀላል በሆነው ተግባር እንጀምር - በአንድ የተወሰነ ዘርፍ ውስጥ የሚሰሩትን ሁሉንም ሰራተኞች መፈለግ ወይም በተዋረድ - ሁሉንም የአንጓ ልጆች ያግኙ. እንዲሁም የዝርያውን "ጥልቀት" ማግኘት ጥሩ ይሆናል ... ይህ ሁሉ አስፈላጊ ሊሆን ይችላል, ለምሳሌ አንድ ዓይነት ለመገንባት. የእነዚህ ሰራተኞች መታወቂያዎች ዝርዝር ላይ በመመርኮዝ ውስብስብ ምርጫ.

የእነዚህ ዘሮች ሁለት ደረጃዎች ብቻ ካሉ እና ቁጥሩ በደርዘን ውስጥ ከሆነ ሁሉም ነገር ጥሩ ይሆናል ነገር ግን ከ 5 በላይ ደረጃዎች ካሉ እና ቀድሞውኑ በደርዘን የሚቆጠሩ ዘሮች ካሉ ችግሮች ሊኖሩ ይችላሉ። ከታች-ከዛፍ ላይ ያሉ ባህላዊ የፍለጋ አማራጮች እንዴት እንደተፃፉ (እና እንደሚሰሩ) እንይ። በመጀመሪያ ግን የትኞቹ አንጓዎች ለጥናታችን በጣም አስደሳች እንደሆኑ እንወስን.

ብዙ "ጥልቅ" ንዑስ ዛፎች:

WITH RECURSIVE T AS (
  SELECT
    id
  , pid
  , ARRAY[id] path
  FROM
    hier
  WHERE
    pid IS NULL
UNION ALL
  SELECT
    hier.id
  , hier.pid
  , T.path || hier.id
  FROM
    T
  JOIN
    hier
      ON hier.pid = T.id
)
TABLE T ORDER BY array_length(path, 1) DESC;

 id  | pid  | path
---------------------------------------------
7624 | 7623 | {7615,7620,7621,7622,7623,7624}
4995 | 4994 | {4983,4985,4988,4993,4994,4995}
4991 | 4990 | {4983,4985,4988,4989,4990,4991}
...

ብዙ "ሰፊ" ንዑስ ዛፎች:

...
SELECT
  path[1] id
, count(*)
FROM
  T
GROUP BY
  1
ORDER BY
  2 DESC;

id   | count
------------
5300 |   30
 450 |   28
1239 |   27
1573 |   25

ለእነዚህ ጥያቄዎች የተለመደውን ተጠቀምን ተደጋጋሚ JOIN:


በዚህ የጥያቄ ሞዴል ግልጽ ነው። የድግግሞሽ ብዛት ከጠቅላላው የዘር ቁጥር ጋር ይዛመዳል (እና ከእነሱ ውስጥ ብዙ ደርዘን አሉ) እና ይህ በጣም ጠቃሚ ሀብቶችን ሊወስድ ይችላል ፣ እና በውጤቱም ፣ ጊዜ።

“ሰፊውን” ንዑስ ዛፍ እንፈትሽ፡-

WITH RECURSIVE T AS (
  SELECT
    id
  FROM
    hier
  WHERE
    id = 5300
UNION ALL
  SELECT
    hier.id
  FROM
    T
  JOIN
    hier
      ON hier.pid = T.id
)
TABLE T;

[ማብራሪያውን ይመልከቱ.tensor.ru]

እንደተጠበቀው, ሁሉንም 30 መዝገቦች አግኝተናል. ግን በዚህ ላይ ከጠቅላላው ጊዜ 60% አሳልፈዋል - ምክንያቱም በመረጃ ጠቋሚው ውስጥ 30 ፍለጋዎችንም አድርገዋል። ያነሰ ማድረግ ይቻላል?

የጅምላ ማረም በመረጃ ጠቋሚ

ለእያንዳንዱ መስቀለኛ መንገድ የተለየ የመረጃ ጠቋሚ መጠይቅ ማድረግ አለብን? አይሆንም - ከመረጃ ጠቋሚው ማንበብ እንችላለን በአንድ ጥሪ ውስጥ ብዙ ቁልፎችን በአንድ ጊዜ በመጠቀም በ እገዛ = ANY(array).

እና በእያንዳንዱ እንደዚህ አይነት መለያዎች ቡድን ውስጥ ባለፈው ደረጃ የተገኙትን ሁሉንም መታወቂያዎች በ "ኖዶች" መውሰድ እንችላለን. ማለትም በእያንዳንዱ ቀጣይ ደረጃ ላይ እናደርጋለን ሁሉንም የአንድ የተወሰነ ደረጃ ዘሮችን በአንድ ጊዜ ይፈልጉ.

ብቻ ፣ ችግሩ እዚህ አለ ፣ በተደጋገመ ምርጫ፣ በተሸፈነ መጠይቅ ውስጥ እራስዎን መድረስ አይችሉም, ግን በሆነ መንገድ በቀድሞው ደረጃ የተገኘውን ብቻ መምረጥ አለብን ... ለጠቅላላው ምርጫ የጎጆ መጠይቅ ማድረግ የማይቻል ነው, ነገር ግን ለተለየ መስክ ይቻላል. እና ይህ መስክ እንዲሁ ድርድር ሊሆን ይችላል - እኛ ልንጠቀምበት የሚገባን። ANY.

ትንሽ እብድ ይመስላል, ነገር ግን በስዕሉ ላይ ሁሉም ነገር ቀላል ነው.

WITH RECURSIVE T AS (
  SELECT
    ARRAY[id] id$
  FROM
    hier
  WHERE
    id = 5300
UNION ALL
  SELECT
    ARRAY(
      SELECT
        id
      FROM
        hier
      WHERE
        pid = ANY(T.id$)
    ) id$
  FROM
    T
  WHERE
    coalesce(id$, '{}') <> '{}' -- условие выхода из цикла - пустой массив
)
SELECT
  unnest(id$) id
FROM
  T;

[ማብራሪያውን ይመልከቱ.tensor.ru]

እና እዚህ በጣም አስፈላጊው ነገር እንኳን አይደለም በጊዜ 1.5 ጊዜ ያሸንፉእና ከ5 ይልቅ 30 ጥሪዎች ብቻ ስላሉን ጥቂት ማቋቋሚያዎችን እንደቀነስን!

ተጨማሪ ጉርሻ ከመጨረሻው አለመረጋጋት በኋላ መለያዎቹ በ "ደረጃዎች" እንደታዘዙ ይቆያሉ የሚለው እውነታ ነው።

የመስቀለኛ መንገድ ምልክት

አፈፃፀሙን ለማሻሻል የሚረዳው ቀጣዩ ግምት - "ቅጠሎች" ልጆች ሊወልዱ አይችሉምማለትም ለእነሱ "ወደ ታች" ማየት አያስፈልግም. በተግባራችን ቀረጻ ውስጥ፣ ይህ ማለት የዲፓርትመንቶችን ሰንሰለት ከተከተልን እና ሰራተኛ ከደረስን ፣ ከዚያ በዚህ ቅርንጫፍ ላይ የበለጠ መፈለግ አያስፈልግም ማለት ነው ።

ወደ ገበታችን እንግባ ተጨማሪ boolean- መስክበዛፋችን ውስጥ ያለው ልዩ ግቤት “መስቀለኛ መንገድ” መሆኑን ወዲያውኑ ይነግረናል - ማለትም ፣ በጭራሽ ዘሮች ሊኖሩት ይችላል።

ALTER TABLE hier
  ADD COLUMN branch boolean;

UPDATE
  hier T
SET
  branch = TRUE
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      hier
    WHERE
      pid = T.id
    LIMIT 1
);
-- Запрос успешно выполнен: 3033 строк изменено за 42 мс.

በጣም ጥሩ! ከሁሉም የዛፍ ንጥረ ነገሮች ውስጥ ከ 30% የሚበልጡት ጥቂት ዘሮች ብቻ ናቸው.

አሁን ትንሽ ለየት ያለ መካኒክ እንጠቀም - ከተደጋጋሚ ክፍል ጋር ግንኙነቶች LATERAL, ይህም ወዲያውኑ የድግግሞሹን "ጠረጴዛ" መስኮችን እንድንደርስ ያስችለናል, እና የቁልፎቹን ስብስብ ለመቀነስ በመስቀለኛ መንገድ ላይ የተመሰረተ የማጣሪያ ሁኔታ አጠቃላይ ተግባርን እንጠቀማለን.

WITH RECURSIVE T AS (
  SELECT
    array_agg(id) id$
  , array_agg(id) FILTER(WHERE branch) ns$
  FROM
    hier
  WHERE
    id = 5300
UNION ALL
  SELECT
    X.*
  FROM
    T
  JOIN LATERAL (
    SELECT
      array_agg(id) id$
    , array_agg(id) FILTER(WHERE branch) ns$
    FROM
      hier
    WHERE
      pid = ANY(T.ns$)
  ) X
    ON coalesce(T.ns$, '{}') <> '{}'
)
SELECT
  unnest(id$) id
FROM
  T;

[ማብራሪያውን ይመልከቱ.tensor.ru]

አንድ ተጨማሪ የመረጃ ጠቋሚ ጥሪ እና መቀነስ ችለናል። በድምፅ ከ 2 ጊዜ በላይ አሸንፏል ማረም.

#2. ወደ ሥሩ እንመለስ

ይህ ስልተ ቀመር “ከዛፉ ላይ” ለሁሉም ንጥረ ነገሮች መዝገቦችን መሰብሰብ ከፈለጉ ፣ የትኛውን ምንጭ ሉህ (እና ከየትኞቹ አመላካቾች ጋር) በናሙናው ውስጥ እንዲካተት እንዳደረገ መረጃ ሲይዙ ጠቃሚ ይሆናል - ለምሳሌ ፣ ማጠቃለያ ሪፖርት ለማመንጨት። ወደ አንጓዎች ከመደመር ጋር.

ጥያቄው በጣም አስቸጋሪ ስለሆነ የሚከተለው እንደ ማስረጃ ብቻ መወሰድ አለበት። ነገር ግን የውሂብ ጎታዎን የሚቆጣጠር ከሆነ, ተመሳሳይ ዘዴዎችን ስለመጠቀም ማሰብ አለብዎት.

በሁለት ቀላል መግለጫዎች እንጀምር፡-

• ከመረጃ ቋቱ ተመሳሳይ መዝገብ አንድ ጊዜ ብቻ ቢያነቡት ጥሩ ነው።.
• ከውሂብ ጎታ መዝገቦች በቡድን ለማንበብ የበለጠ ቀልጣፋ ነው።ብቻውን ሳይሆን.

አሁን የምንፈልገውን ጥያቄ ለመገንባት እንሞክር.

1 ደረጃ

በግልጽ እንደሚታየው ፣ ድግግሞሽን ስንጀምር (ያለ እኛ የት እንሆን ነበር!) በመጀመሪያ መለያዎች ስብስብ ላይ በመመርኮዝ የቅጠሎቹን መዛግብት እራሳቸው መቀነስ አለብን።

WITH RECURSIVE tree AS (
  SELECT
    rec -- это цельная запись таблицы
  , id::text chld -- это "набор" приведших сюда исходных листьев
  FROM
    hier rec
  WHERE
    id = ANY('{1,2,4,8,16,32,64,128,256,512,1024,2048,4096,8192}'::integer[])
UNION ALL
  ...

ለአንድ ሰው “ስብስቡ” እንደ ሕብረቁምፊ እንጂ እንደ ድርድር ሳይሆን መቀመጡ እንግዳ መስሎ ከታየ ለዚህ ቀላል ማብራሪያ አለ። ለሕብረቁምፊዎች አብሮ የተሰራ የማጠቃለያ "ማጣበቅ" ተግባር አለ። string_agg, ግን ለድርድር አይደለም. እሷ ቢሆንም በእራስዎ ለመተግበር ቀላል.

2 ደረጃ

አሁን የበለጠ ማንበብ ያለባቸው የክፍል መታወቂያዎች ስብስብ እናገኛለን። ሁልጊዜ ማለት ይቻላል በዋናው ስብስብ በተለያዩ መዝገቦች ውስጥ ይባዛሉ - እኛ እናደርጋለን ይቧድቧቸው, ስለ ምንጭ ቅጠሎች መረጃን በማቆየት ላይ.

ግን እዚህ ሶስት ችግሮች ይጠብቁናል.

1. የጥያቄው ክፍል "ንዑስ ምሁር" ከ ጋር ድምር ተግባራትን ሊይዝ አይችልም። GROUP BY.
2. የድግግሞሽ "ጠረጴዛ" ማመሳከሪያ በአንድ ጎጆ ውስጥ መሆን አይችልም.
3. በተደጋጋሚ ክፍል ውስጥ ያለ ጥያቄ CTE ሊይዝ አይችልም።

እንደ እድል ሆኖ, እነዚህ ሁሉ ችግሮች ለመሥራት በጣም ቀላል ናቸው. ከመጨረሻው እንጀምር።

CTE በተደጋጋሚ ክፍል

እዚህ አለ አይደለም ይሰራል፡

WITH RECURSIVE tree AS (
  ...
UNION ALL
  WITH T (...)
  SELECT ...
)

እና ስለዚህ ይሰራል, ቅንፍ ልዩነቱን ያመጣል!

WITH RECURSIVE tree AS (
  ...
UNION ALL
  (
    WITH T (...)
    SELECT ...
  )
)

በተደጋገመ "ጠረጴዛ" ላይ የተካተተ መጠይቅ

እም... ተደጋጋሚ CTE በአንድ ንዑስ መጠይቅ ውስጥ ሊደረስበት አይችልም። ግን በ CTE ውስጥ ሊሆን ይችላል! እና አንድ የተጠጋ ጥያቄ አስቀድሞ ይህንን CTE መድረስ ይችላል!

ቡድን በውስጥ ተደጋጋሚነት

ደስ የማይል ነው፣ ግን... በመጠቀም GROUPን ለመኮረጅ ቀላል መንገድ አለን። DISTINCT ON እና የመስኮት ተግባራት!

SELECT
  (rec).pid id
, string_agg(chld::text, ',') chld
FROM
  tree
WHERE
  (rec).pid IS NOT NULL
GROUP BY 1 -- не работает!

እና እንደዚህ ነው የሚሰራው!

SELECT DISTINCT ON((rec).pid)
  (rec).pid id
, string_agg(chld::text, ',') OVER(PARTITION BY (rec).pid) chld
FROM
  tree
WHERE
  (rec).pid IS NOT NULL

አሁን የቁጥር መታወቂያው ለምን ወደ ጽሁፍ እንደተቀየረ አይተናል - በነጠላ ሰረዞች አንድ ላይ እንዲጣመሩ!

3 ደረጃ

ለመጨረሻ ጊዜ ምንም የቀረን ነገር የለም፡-

• በቡድን መታወቂያዎች ስብስብ ላይ በመመስረት "ክፍል" መዝገቦችን እናነባለን
• የተቀነሱትን ክፍሎች ከመጀመሪያዎቹ ሉሆች “ስብስቦች” ጋር እናነፃፅራለን
• የስብስብ ሕብረቁምፊውን በመጠቀም “ዘርጋ” unnest(string_to_array(chld, ',')::integer[])

WITH RECURSIVE tree AS (
  SELECT
    rec
  , id::text chld
  FROM
    hier rec
  WHERE
    id = ANY('{1,2,4,8,16,32,64,128,256,512,1024,2048,4096,8192}'::integer[])
UNION ALL
  (
    WITH prnt AS (
      SELECT DISTINCT ON((rec).pid)
        (rec).pid id
      , string_agg(chld::text, ',') OVER(PARTITION BY (rec).pid) chld
      FROM
        tree
      WHERE
        (rec).pid IS NOT NULL
    )
    , nodes AS (
      SELECT
        rec
      FROM
        hier rec
      WHERE
        id = ANY(ARRAY(
          SELECT
            id
          FROM
            prnt
        ))
    )
    SELECT
      nodes.rec
    , prnt.chld
    FROM
      prnt
    JOIN
      nodes
        ON (nodes.rec).id = prnt.id
  )
)
SELECT
  unnest(string_to_array(chld, ',')::integer[]) leaf
, (rec).*
FROM
  tree;

[ማብራሪያውን ይመልከቱ.tensor.ru]

ምንጭ: hab.com