PostgreSQL Antipatterns៖ តើប្រហោងទន្សាយមានជម្រៅប៉ុន្មាន? ចូរយើងឆ្លងកាត់ឋានានុក្រម

នៅក្នុងប្រព័ន្ធ ERP ស្មុគស្មាញ អង្គភាពជាច្រើនមានលក្ខណៈឋានានុក្រមនៅពេលដែលវត្ថុដូចគ្នាតម្រង់ជួរ មែកធាងនៃទំនាក់ទំនងដូនតា - កូនចៅ - នេះគឺជារចនាសម្ព័ន្ធអង្គភាពរបស់សហគ្រាស (សាខា នាយកដ្ឋាន និងក្រុមការងារទាំងអស់នេះ) និងកាតាឡុកទំនិញ និងតំបន់ការងារ និងភូមិសាស្ត្រនៃចំណុចលក់...

តាមការពិតមិនមានទេ។ តំបន់ស្វ័យប្រវត្តិកម្មអាជីវកម្មដែលជាកន្លែងដែលនឹងមិនមានឋានានុក្រមណាមួយជាលទ្ធផល។ ប៉ុន្តែទោះបីជាអ្នកមិនធ្វើការ "សម្រាប់អាជីវកម្ម" អ្នកនៅតែអាចជួបប្រទះទំនាក់ទំនងតាមឋានានុក្រមយ៉ាងងាយស្រួល។ វា​ជា​រឿង​មិន​ល្អ​ទេ សូម្បី​តែ​មែកធាង​គ្រួសារ​របស់​អ្នក ឬ​ផែនការ​ជាន់​នៅ​ក្នុង​មជ្ឈមណ្ឌល​លក់ទំនិញ​ក៏​មាន​រចនាសម្ព័ន្ធ​ដូចគ្នា​ដែរ។

មានវិធីជាច្រើនក្នុងការរក្សាទុកដើមឈើបែបនេះនៅក្នុង DBMS ប៉ុន្តែថ្ងៃនេះយើងនឹងផ្តោតលើជម្រើសតែមួយគត់៖

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

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

ហើយខណៈពេលដែលអ្នកកំពុងសម្លឹងមើលទៅជម្រៅនៃឋានានុក្រម វាកំពុងរង់ចាំដោយអត់ធ្មត់ ដើម្បីមើលថាតើវិធី "ឆោតល្ងង់" របស់អ្នកក្នុងការធ្វើការជាមួយរចនាសម្ព័ន្ធបែបនេះនឹងមានប្រសិទ្ធភាពយ៉ាងណា។

សូមក្រឡេកមើលបញ្ហាធម្មតាដែលកើតឡើង ការអនុវត្តរបស់ពួកគេនៅក្នុង SQL និងព្យាយាមកែលម្អការអនុវត្តរបស់ពួកគេ។

#1. តើរន្ធទន្សាយមានជម្រៅប៉ុន្មាន?

អនុញ្ញាតឱ្យយើងយល់ច្បាស់ថា រចនាសម្ព័ន្ធនេះនឹងឆ្លុះបញ្ចាំងពីផ្នែករងនៃនាយកដ្ឋាននៅក្នុងរចនាសម្ព័ន្ធរបស់អង្គការ៖ នាយកដ្ឋាន ការបែងចែក វិស័យ សាខា ក្រុមការងារ ... - អ្វីក៏ដោយដែលអ្នកហៅពួកគេ។

ជាដំបូង ចូរយើងបង្កើត 'ដើមឈើ' របស់យើងនៃធាតុ 10K

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;

ចូរចាប់ផ្តើមជាមួយនឹងកិច្ចការដ៏សាមញ្ញបំផុត - ការស្វែងរកបុគ្គលិកទាំងអស់ដែលធ្វើការនៅក្នុងវិស័យជាក់លាក់មួយ ឬនៅក្នុងលក្ខខណ្ឌនៃឋានានុក្រម - ស្វែងរកកូនទាំងអស់នៃ node. វាជាការល្អផងដែរក្នុងការទទួលបាន "ជម្រៅ" នៃកូនចៅ... ទាំងអស់នេះអាចជាការចាំបាច់ ឧទាហរណ៍ ដើម្បីកសាងប្រភេទមួយចំនួន។ ការជ្រើសរើសស្មុគស្មាញដោយផ្អែកលើបញ្ជីលេខសម្គាល់បុគ្គលិកទាំងនេះ.

អ្វីៗនឹងល្អប្រសិនបើមានកូនចៅទាំងនេះតែពីរបីកម្រិត ហើយចំនួនមានក្នុងខ្ទង់ដប់ ប៉ុន្តែប្រសិនបើមានលើសពី 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

សម្រាប់សំណួរទាំងនេះ យើងបានប្រើធម្មតា។ ចូលរួមឡើងវិញ:


ជាក់ស្តែងជាមួយនឹងគំរូសំណើនេះ។ ចំនួននៃការធ្វើម្តងទៀតនឹងដូចគ្នាទៅនឹងចំនួនសរុបនៃកូនចៅ (ហើយមានរាប់សិបនៃពួកគេ) ហើយវាអាចត្រូវការធនធានសំខាន់ៗ ហើយជាលទ្ធផល ពេលវេលា។

តោះពិនិត្យមើលមែកធាង "ធំទូលាយបំផុត"៖

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!

ប្រាក់រង្វាន់បន្ថែមគឺជាការពិតដែលថាបន្ទាប់ពីការ unnest ចុងក្រោយ ការកំណត់អត្តសញ្ញាណនឹងនៅតែបញ្ជាដោយ "កម្រិត" ។

សញ្ញាថ្នាំង

ការពិចារណាបន្ទាប់ដែលនឹងជួយកែលម្អការអនុវត្តគឺ − "ស្លឹក" មិនអាចមានកូនបានទេ។នោះគឺសម្រាប់ពួកគេមិនចាំបាច់មើល "ចុះក្រោម" ទាល់តែសោះ។ នៅក្នុងការបង្កើតភារកិច្ចរបស់យើង នេះមានន័យថា ប្រសិនបើយើងធ្វើតាមខ្សែសង្វាក់នៃនាយកដ្ឋាន ហើយឈានដល់បុគ្គលិក នោះមិនចាំបាច់រកមើលបន្ថែមទៀតតាមសាខានេះទេ។

តោះចូលទៅក្នុងតារាងរបស់យើង។ បន្ថែម 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 ដងក្នុងបរិមាណ អានឡើងវិញ។

#២. ចូរយើងត្រលប់ទៅឫស

ក្បួនដោះស្រាយនេះនឹងមានប្រយោជន៍ប្រសិនបើអ្នកត្រូវការប្រមូលកំណត់ត្រាសម្រាប់ធាតុទាំងអស់ "ឡើងលើដើមឈើ" ខណៈពេលដែលរក្សាព័ត៌មានអំពីសន្លឹកប្រភពណាមួយ (និងជាមួយសូចនាករអ្វីខ្លះ) ដែលបណ្តាលឱ្យវាត្រូវបានបញ្ចូលក្នុងគំរូ - ឧទាហរណ៍ ដើម្បីបង្កើតរបាយការណ៍សង្ខេប ជាមួយនឹងការប្រមូលផ្តុំទៅជាថ្នាំង។

អ្វី​ដែល​បន្ទាប់​មក​គួរ​តែ​ត្រូវ​បាន​យក​ទៅ​ជា​ភស្តុតាង​នៃ​គំនិត​តែ​មួយ​គត់ ចាប់​តាំង​ពី​សំណើ​នេះ​ក្លាយ​​​ទៅ​ជា​ការ​លំបាក​ខ្លាំង​ណាស់​។ ប៉ុន្តែប្រសិនបើវាគ្របដណ្តប់លើមូលដ្ឋានទិន្នន័យរបស់អ្នក អ្នកគួរតែគិតអំពីការប្រើប្រាស់បច្ចេកទេសស្រដៀងគ្នា។

ចូរចាប់ផ្តើមជាមួយសេចក្តីថ្លែងការណ៍សាមញ្ញមួយចំនួន៖

• កំណត់ត្រាដូចគ្នាពីមូលដ្ឋានទិន្នន័យ វាជាការល្អបំផុតក្នុងការអានវាតែម្តង.
• កំណត់ត្រាពីមូលដ្ឋានទិន្នន័យ វាកាន់តែមានប្រសិទ្ធភាពក្នុងការអានជាបាច់ជាងនៅម្នាក់ឯង។

ឥឡូវនេះ ចូរយើងព្យាយាមបង្កើតសំណើដែលយើងត្រូវការ។

បោះជំហាន 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. ផ្នែក "subrecursive" នៃសំណួរមិនអាចមានមុខងារសរុបជាមួយ GROUP BY.
2. សេចក្តីយោងទៅ "តារាង" ដែលអាចប្រើឡើងវិញបាន មិនអាចស្ថិតនៅក្នុងសំណួរបន្ទាប់បន្សំបានទេ។
3. សំណើនៅក្នុងផ្នែកដែលប្រើឡើងវិញមិនអាចមាន CTE បានទេ។

ជាសំណាងល្អ បញ្ហាទាំងអស់នេះពិតជាងាយស្រួលក្នុងការដោះស្រាយ។ ចូរចាប់ផ្តើមពីទីបញ្ចប់។

CTE នៅក្នុងផ្នែក recursive

នៅទីនេះ មិនមាន ធ្វើការ៖

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

ហើយដូច្នេះវាដំណើរការ វង់ក្រចកធ្វើឱ្យមានភាពខុសគ្នា!

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

សំណួរ​ដែល​បាន​ភ្ជាប់​ជាមួយ​នឹង "តារាង" ដដែលៗ

ហ៊ឺ... មិនអាចចូលប្រើ CTE ឡើងវិញនៅក្នុងសំណួររងបានទេ។ ប៉ុន្តែវាអាចនៅខាងក្នុង CTE! ហើយ​សំណើ​ដែល​ជាប់​គាំង​អាច​ចូល​ប្រើ CTE នេះ​បាន​ហើយ!

GROUP BY នៅខាងក្នុងការហៅឡើងវិញ

វា​មិន​សប្បាយ​ចិត្ត​ទេ ប៉ុន្តែ... យើង​មាន​វិធី​សាមញ្ញ​មួយ​ដើម្បី​ត្រាប់តាម​ក្រុម​ដោយ​ការ​ប្រើ 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]

ប្រភព: www.habr.com