புரோஹோஸ்டர் > Блог > நிர்வாகம் > PostgreSQL எதிர்ப்பு வடிவங்கள்: தீங்கு விளைவிக்கும் சேர்ப்புகள் மற்றும் ORகள்

PostgreSQL எதிர்ப்பு வடிவங்கள்: தீங்கு விளைவிக்கும் சேர்ப்புகள் மற்றும் ORகள்

பஃபர்களைக் கொண்டுவரும் செயல்பாடுகளில் ஜாக்கிரதை...
ஒரு சிறிய வினவலை உதாரணமாகப் பயன்படுத்தி, PostgreSQL இல் வினவல்களை மேம்படுத்துவதற்கான சில உலகளாவிய அணுகுமுறைகளைப் பார்ப்போம். நீங்கள் அவற்றைப் பயன்படுத்துகிறீர்களா இல்லையா என்பது உங்களுடையது, ஆனால் அவற்றைப் பற்றி தெரிந்து கொள்வது மதிப்பு.

PG இன் சில அடுத்தடுத்த பதிப்புகளில், திட்டமிடுபவர் புத்திசாலியாக மாறும்போது நிலைமை மாறலாம், ஆனால் 9.4/9.6க்கு இங்கே உள்ள உதாரணங்களில் உள்ளதைப் போலவே தோராயமாகத் தெரிகிறது.

ஒரு உண்மையான கோரிக்கையை எடுத்துக் கொள்வோம்:

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

அட்டவணை மற்றும் புலப் பெயர்கள் பற்றிபுலங்கள் மற்றும் அட்டவணைகளின் "ரஷ்ய" பெயர்கள் வித்தியாசமாக நடத்தப்படலாம், ஆனால் இது சுவைக்குரிய விஷயம். ஏனெனில் இங்கே டென்சரில் வெளிநாட்டு டெவலப்பர்கள் யாரும் இல்லை, மேலும் ஹைரோகிளிஃப்களில் கூட பெயர்களைக் கொடுக்க PostgreSQL அனுமதிக்கிறது. மேற்கோள்களில் இணைக்கப்பட்டுள்ளது, பின்னர் பொருள்களை தெளிவற்றதாகவும் தெளிவாகவும் பெயரிட விரும்புகிறோம், இதனால் முரண்பாடுகள் எதுவும் இல்லை.
இதன் விளைவாக வரும் திட்டத்தைப் பார்ப்போம்:
PostgreSQL எதிர்ப்பு வடிவங்கள்: தீங்கு விளைவிக்கும் சேர்ப்புகள் மற்றும் ORகள்
[explain.tensor.ru ஐப் பார்க்கவும்]

144ms மற்றும் கிட்டத்தட்ட 53K இடையகங்கள் - அதாவது, 400MB க்கும் அதிகமான தரவு! எங்கள் கோரிக்கையின் போது அவை அனைத்தும் தற்காலிக சேமிப்பில் இருந்தால் நாம் அதிர்ஷ்டசாலியாக இருப்போம், இல்லையெனில் வட்டில் இருந்து படிக்கும்போது பல மடங்கு அதிக நேரம் எடுக்கும்.

அல்காரிதம் மிக முக்கியமானது!

எந்தவொரு கோரிக்கையையும் எப்படியாவது மேம்படுத்த, அது என்ன செய்ய வேண்டும் என்பதை நீங்கள் முதலில் புரிந்து கொள்ள வேண்டும்.
இந்த கட்டுரையின் எல்லைக்கு வெளியே தரவுத்தள கட்டமைப்பின் வளர்ச்சியை விட்டுவிடுவோம், மேலும் ஒப்பீட்டளவில் "மலிவாக" முடியும் என்பதை ஒப்புக்கொள்வோம். கோரிக்கையை மீண்டும் எழுதவும் மற்றும்/அல்லது நமக்கு தேவையான சில விஷயங்களை அடிப்படையாக உருட்டவும் குறியீடுகள்.

எனவே கோரிக்கை:
- குறைந்தது சில ஆவணங்களின் இருப்பை சரிபார்க்கிறது
- நமக்குத் தேவையான மற்றும் ஒரு குறிப்பிட்ட வகையின் நிலையில்
- ஆசிரியர் அல்லது நடிகரே நமக்குத் தேவையான பணியாளர்

சேரவும் + வரம்பு 1

அதிக எண்ணிக்கையிலான அட்டவணைகள் முதலில் இணைக்கப்பட்ட கேள்வியை டெவலப்பர் எழுதுவது பெரும்பாலும் எளிதானது, பின்னர் இந்த முழு தொகுப்பிலிருந்தும் ஒரே ஒரு பதிவு மட்டுமே உள்ளது. ஆனால் டெவலப்பருக்கு எளிதானது என்பது தரவுத்தளத்திற்கு மிகவும் திறமையானது என்று அர்த்தமல்ல.
எங்கள் விஷயத்தில் 3 அட்டவணைகள் மட்டுமே இருந்தன - மற்றும் விளைவு என்ன ...

முதலில் "ஆவண வகை" அட்டவணையுடனான தொடர்பை அகற்றுவோம், அதே நேரத்தில் தரவுத்தளத்திடம் சொல்லுங்கள் எங்கள் வகை பதிவு தனித்துவமானது (இது எங்களுக்குத் தெரியும், ஆனால் திட்டமிடுபவருக்கு இதுவரை எதுவும் தெரியாது):

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

ஆம், டேபிள்/சிடிஇ ஒரே பதிவின் ஒற்றைப் புலத்தைக் கொண்டிருந்தால், பிஜியில் இப்படி எழுதலாம்.

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

PostgreSQL வினவல்களில் சோம்பேறி மதிப்பீடு

BitmapOr vs UNION

சில சந்தர்ப்பங்களில், பிட்மேப் ஹீப் ஸ்கேன் எங்களுக்கு நிறைய செலவாகும் - எடுத்துக்காட்டாக, எங்கள் சூழ்நிலையில், நிறைய பதிவுகள் தேவையான நிபந்தனையை பூர்த்தி செய்யும் போது. ஏனென்றால் எங்களுக்கு கிடைத்தது அல்லது நிலை BitmapOr ஆக மாறியது- திட்டத்தில் செயல்பாடு.
அசல் சிக்கலுக்குத் திரும்புவோம் - அதற்கான பதிவை நாம் கண்டுபிடிக்க வேண்டும் எந்தவொரு நிபந்தனைகளிலிருந்து - அதாவது, இரண்டு நிபந்தனைகளின் கீழும் அனைத்து 59K பதிவுகளையும் தேட வேண்டிய அவசியமில்லை. ஒரு நிபந்தனையை நிறைவேற்ற ஒரு வழி உள்ளது, மற்றும் முதலாவதாக எதுவும் கிடைக்காதபோது மட்டுமே இரண்டாவது இடத்திற்குச் செல்லுங்கள். பின்வரும் வடிவமைப்பு எங்களுக்கு உதவும்:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

"வெளிப்புற" வரம்பு 1 முதல் பதிவைக் கண்டறிந்ததும் தேடல் முடிவடைவதை உறுதி செய்கிறது. இது ஏற்கனவே முதல் தொகுதியில் காணப்பட்டால், இரண்டாவது தொகுதி செயல்படுத்தப்படாது (ஒருபோதும் செயல்படுத்தப்படவில்லை சம்பந்தமாக).

"கேஸ் கீழ் கடினமான சூழ்நிலைகளை மறைத்தல்"

அசல் வினவலில் மிகவும் சிரமமான தருணம் உள்ளது - தொடர்புடைய அட்டவணை “ஆவண நீட்டிப்பு”க்கு எதிராக நிலையைச் சரிபார்க்கிறது. வெளிப்பாட்டின் மற்ற நிபந்தனைகளின் உண்மையைப் பொருட்படுத்தாமல் (உதாரணமாக, d.“நீக்கப்பட்டது” என்பது உண்மையல்ல), இந்த இணைப்பு எப்போதும் செயல்படுத்தப்படுகிறது மற்றும் "வளங்கள் செலவுகள்". அவற்றில் அதிகமாகவோ அல்லது குறைவாகவோ செலவிடப்படும் - இந்த அட்டவணையின் அளவைப் பொறுத்தது.
ஆனால் நீங்கள் வினவலை மாற்றியமைக்கலாம், அதனால் தொடர்புடைய பதிவைத் தேடுவது உண்மையில் தேவைப்படும்போது மட்டுமே நடக்கும்:

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

எங்களிடம் இணைக்கப்பட்ட அட்டவணையில் இருந்து ஒருமுறை முடிவுகளுக்கு புலங்கள் எதுவும் தேவையில்லை, பின்னர் ஒரு துணை வினவலில் JOIN ஐ நிபந்தனையாக மாற்றும் வாய்ப்பு உள்ளது.
அட்டவணைப்படுத்தப்பட்ட புலங்களை “CASE அடைப்புக்குறிகளுக்கு வெளியே” விட்டுவிடுவோம், பதிவிலிருந்து WHEN தொகுதிக்கு எளிய நிபந்தனைகளைச் சேர்ப்போம் - இப்போது “கனமான” வினவல் THEN க்கு செல்லும் போது மட்டுமே செயல்படுத்தப்படும்.

எனது கடைசி பெயர் "மொத்தம்"

மேலே விவரிக்கப்பட்ட அனைத்து இயக்கவியலுடனும் பெறப்பட்ட வினவலை நாங்கள் சேகரிக்கிறோம்:

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

குறியீடுகளை [க்கு] சரிசெய்தல்

UNION துணைத் தொகுதிகளில் உள்ள அட்டவணைப்படுத்தப்பட்ட நிலைமைகள் சற்று வித்தியாசமாக இருப்பதைப் பயிற்சி பெற்ற கண் கவனித்தது - இதற்குக் காரணம், ஏற்கனவே அட்டவணையில் பொருத்தமான குறியீடுகள் எங்களிடம் உள்ளது. அவை இல்லை என்றால், அதை உருவாக்குவது மதிப்புக்குரியது: ஆவணம்(நபர்3, ஆவண வகை) и ஆவணம்(ஆவண வகை, பணியாளர்).
ROW நிலைகளில் உள்ள புலங்களின் வரிசை பற்றிதிட்டமிடுபவரின் பார்வையில், நிச்சயமாக, நீங்கள் எழுதலாம் (A, B) = (constA, constB)மற்றும் (B, A) = (constB, constA). ஆனால் பதிவு செய்யும் போது குறியீட்டில் உள்ள புலங்களின் வரிசையில், அத்தகைய கோரிக்கை பின்னர் பிழைத்திருத்தத்திற்கு மிகவும் வசதியானது.
திட்டத்தில் என்ன இருக்கிறது?
PostgreSQL எதிர்ப்பு வடிவங்கள்: தீங்கு விளைவிக்கும் சேர்ப்புகள் மற்றும் ORகள்
[explain.tensor.ru ஐப் பார்க்கவும்]

துரதிர்ஷ்டவசமாக, நாங்கள் துரதிர்ஷ்டவசமாக இருந்தோம், முதல் UNION தொகுதியில் எதுவும் கிடைக்கவில்லை, எனவே இரண்டாவது இன்னும் செயல்படுத்தப்பட்டது. ஆனால் அப்படியும் - மட்டும் 0.037ms மற்றும் 11 இடையகங்கள்!
கோரிக்கையை விரைவுபடுத்தியுள்ளோம் மற்றும் நினைவகத்தில் டேட்டா பம்ப் செய்வதைக் குறைத்துள்ளோம் பல ஆயிரம் முறை, மிகவும் எளிமையான நுட்பங்களைப் பயன்படுத்துதல் - ஒரு சிறிய நகல்-பேஸ்ட் மூலம் நல்ல முடிவு. 🙂

ஆதாரம்: www.habr.com

கருத்தைச் சேர்