ProHoster > Блог > басқарма > PostgreSQL антипаттерндері: "Біреуі ғана болуы керек!"

PostgreSQL антипаттерндері: "Біреуі ғана болуы керек!"

SQL тілінде сіз «қалай» орындалу керектігін емес, «неге» қол жеткізгіңіз келетінін сипаттайсыз. Сондықтан, SQL сұрауларын «естілетіндей, ол қалай жазылады» стилінде әзірлеу мәселесі құрметті орын алады. SQL тілінде шарттарды есептеу ерекшеліктері.

Бүгін өте қарапайым мысалдарды пайдалана отырып, бұл пайдалану контекстінде не әкелуі мүмкін екенін көрейік GROUP/DISTINCT и LIMIT олармен бірге.

Енді өтініште жазсаңыз «алдымен осы белгілерді қосыңыз, содан кейін барлық көшірмелерді тастаңыз, біреуі ғана қалуы керек әр кілт үшін көшіру» - қосылым мүлдем қажет болмаса да, ол дәл осылай жұмыс істейді.

Кейде сәттілікке жетеді және ол «жұмыс істейді», кейде ол өнімділікке жағымсыз әсер етеді, ал кейде әзірлеуші ​​тұрғысынан мүлдем күтпеген әсерлер береді.

PostgreSQL антипаттерндері: "Біреуі ғана болуы керек!"
Мүмкін соншалықты әсерлі емес шығар, бірақ...

«Тәтті жұп»: ҚОСЫЛЫҢЫЗ + АЙҚЫН

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

Олардың не қалайтыны түсінікті болар еді орындалған шартқа қатысты Y жазбалары бар X жазбаларын таңдаңыз. арқылы өтініш жазды JOIN — бірнеше рет pk мәндерін алды (Y-де қанша қолайлы жазба пайда болды). Қалай жоюға болады? Әрине DISTINCT!

Әрбір X-жазба үшін бірнеше жүздеген ұқсас Y-жазбалары болса, содан кейін көшірмелер ерлікпен жойылғанда, бұл әсіресе «қуанышты».

PostgreSQL антипаттерндері: "Біреуі ғана болуы керек!"

Қалай түзетуге болады? Бастау үшін мәселені өзгертуге болатынын түсініңіз “Y-де орындалған шартпен байланыстырылған КЕМІНДЕ БІР бар X жазбаларын таңдаңыз” - Өйткені, бізге Y-жазбасының өзінен ештеңе керек емес.

Кірістірілген EXISTS

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

PostgreSQL-тің кейбір нұсқалары EXISTS-те бірінші пайда болатын жазбаны табу жеткілікті екенін түсінеді, ал ескілері жоқ. Сондықтан мен әрқашан көрсетуді жөн көремін LIMIT 1 ішінде EXISTS.

ЖАҢАЛЫҚ ҚОСЫЛУ

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;

Дәл осындай опция қажет болған жағдайда табылған байланысты Y-жазбасынан кейбір деректерді дереу қайтаруға мүмкіндік береді. Ұқсас нұсқа мақалада талқыланады «PostgreSQL Antipatterns: сирек жазба JOIN ортасына жетеді».

«Неге көбірек төлеу керек»: DISTINCT [ON] + LIMIT 1

Мұндай сұрау түрлендірулерінің қосымша артықшылығы мына жағдайдағыдай олардың біреуі немесе бірнешеуі қажет болса, жазбаларды іздеуді оңай шектеу мүмкіндігі болып табылады:

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

Енді біз сұрауды оқып, ДҚБЖ не істеу ұсынылатынын түсінуге тырысамыз:

  • белгілерді байланыстыру
  • X.pk бойынша бірегей
  • қалған жазбалардан біреуін таңдаңыз

Сонымен не алдыңыз? «Бір ғана жазба» бірегейлерінен - ​​ал егер біз осы бірегей емес біреуін алсақ, нәтиже қандай да бір жолмен өзгере ме?.. «Ал егер айырмашылық болмаса, неге көбірек төлеу керек?»

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Және дәл сол тақырып GROUP BY + LIMIT 1.

«Мен жай ғана сұрауым керек»: жасырын GROUP + LIMIT

Ұқсас жағдайлар әр түрлі болады бос емес тексерулер Сұраныс барысында белгілер немесе CTE:

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

Жиынтық функциялар (count/min/max/sum/...) барлық жиынтықта, тіпті нақты нұсқауларсыз сәтті орындалады GROUP BY. Тек бірге LIMIT олар өте тату емес.

Әзірлеуші ​​ойлай алады «Егер ол жерде жазбалар болса, онда маған ШЕКтен артық қажет емес». Бірақ олай істеме! Өйткені база үшін ол:

  • не қалайтынын есептейді барлық жазбаларға сәйкес
  • қанша сұраса, сонша жол беріңіз

Мақсатты шарттарға байланысты келесі ауыстырулардың бірін жасау орынды:

  • (count + LIMIT 1) = 0 туралы NOT EXISTS(LIMIT 1)
  • (count + LIMIT 1) > 0 туралы EXISTS(LIMIT 1)
  • count >= N туралы (SELECT count(*) FROM (... LIMIT N))

«Граммен қанша салмақ»: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Аңғал әзірлеуші ​​сұраудың орындалуын тоқтататынына шын жүректен сенуі мүмкін. біз кездесетін алғашқы әртүрлі мәндердің 1 долларын тапқан кезде.

Болашақта бұл жаңа түйіннің арқасында жұмыс істей алады және жұмыс істейді Index Skip Scan, оны жүзеге асыру қазіргі уақытта пысықталуда, бірақ әлі жоқ.

Әзірге бірінші барлық жазбалар алынады, бірегей болып табылады және олардан ғана сұралған сома қайтарылады. Егер біз осындай нәрсені қаласақ, бұл өте өкінішті $ 1 = 4, және кестеде жүздеген мың жазбалар бар...

Бекер мұңаймас үшін рекурсивті сұрауды қолданайық PostgreSQL Wiki сайтындағы «DISTINCT — кедейлер үшін».:

PostgreSQL антипаттерндері: "Біреуі ғана болуы керек!"

Ақпарат көзі: www.habr.com

пікір қалдыру