PostgreSQL ആന്റിപാറ്റേണുകൾ: പേരിനാൽ തിരയലിന്റെ ആവർത്തന പരിഷ്കരണത്തിന്റെ ഒരു കഥ, അല്ലെങ്കിൽ "അങ്ങോട്ടും ഇങ്ങോട്ടും ഒപ്റ്റിമൈസേഷൻ"

രാജ്യത്തുടനീളമുള്ള സെയിൽസ് ഓഫീസുകളിൽ നിന്നുള്ള ആയിരക്കണക്കിന് മാനേജർമാരുടെ റെക്കോർഡ് ഞങ്ങളുടെ CRM സിസ്റ്റം പ്രതിദിനം പതിനായിരക്കണക്കിന് കോൺടാക്റ്റുകൾ - സാധ്യതയുള്ള അല്ലെങ്കിൽ നിലവിലുള്ള ക്ലയന്റുകളുമായുള്ള ആശയവിനിമയത്തിന്റെ വസ്തുതകൾ. ഇതിനായി, നിങ്ങൾ ആദ്യം ഒരു ക്ലയന്റ് കണ്ടെത്തണം, വെയിലത്ത് വളരെ വേഗത്തിൽ. ഇത് മിക്കപ്പോഴും സംഭവിക്കുന്നത് പേരിലാണ്.

അതിനാൽ, ഏറ്റവും കൂടുതൽ ലോഡുചെയ്ത ഡാറ്റാബേസുകളിലൊന്നിലെ “കനത്ത” അന്വേഷണങ്ങൾ വീണ്ടും വിശകലനം ചെയ്യുന്നതിൽ അതിശയിക്കാനില്ല - നമ്മുടെ സ്വന്തം VLSI കോർപ്പറേറ്റ് അക്കൗണ്ട്, ഞാൻ "മുകളിൽ" കണ്ടെത്തി പേര് പ്രകാരം "ദ്രുത" തിരയലിനായി അഭ്യർത്ഥിക്കുക ഓർഗനൈസേഷൻ കാർഡുകൾക്കായി.

കൂടാതെ, കൂടുതൽ അന്വേഷണം രസകരമായ ഒരു ഉദാഹരണം വെളിപ്പെടുത്തി ആദ്യം ഒപ്റ്റിമൈസേഷനും പിന്നെ പെർഫോമൻസ് ഡിഗ്രേഡേഷനും നിരവധി ടീമുകൾ അതിന്റെ തുടർച്ചയായ പരിഷ്കരണത്തോടെ അഭ്യർത്ഥിക്കുന്നു, അവ ഓരോന്നും മികച്ച ഉദ്ദേശ്യത്തോടെ മാത്രം പ്രവർത്തിച്ചു.

0: ഉപയോക്താവിന് എന്താണ് വേണ്ടത്?

[കെ.ഡി.പി.വി ഇവിടെ നിന്ന്]

പേര് ഉപയോഗിച്ച് ഒരു "ദ്രുത" തിരയലിനെ കുറിച്ച് സംസാരിക്കുമ്പോൾ ഒരു ഉപയോക്താവ് സാധാരണയായി എന്താണ് അർത്ഥമാക്കുന്നത്? ഒരു ഉപസ്‌ട്രിംഗിനായുള്ള “സത്യസന്ധമായ” തിരയലായി ഇത് ഒരിക്കലും മാറില്ല ... LIKE '%роза%' - കാരണം ഫലത്തിൽ മാത്രമല്ല ഉൾപ്പെടുന്നു 'Розалия' и 'Магазин Роза'പക്ഷേ 'Гроза' പോലും 'Дом Деда Мороза'.

നിങ്ങൾ അവനു നൽകുമെന്ന് ഉപയോക്താവ് ദൈനംദിന തലത്തിൽ അനുമാനിക്കുന്നു വാക്കിന്റെ തുടക്കമനുസരിച്ച് തിരയുക തലക്കെട്ടിൽ അത് കൂടുതൽ പ്രസക്തമാക്കുക ആരംഭിക്കുന്നു പ്രവേശിച്ചു. നിങ്ങൾ അത് ചെയ്യും ഏതാണ്ട് തൽക്ഷണം - ഇന്റർലീനിയർ ഇൻപുട്ടിനായി.

1: ചുമതല പരിമിതപ്പെടുത്തുക

അതിലുപരിയായി, ഒരു വ്യക്തി പ്രത്യേകമായി പ്രവേശിക്കില്ല 'роз магаз', അതിനാൽ നിങ്ങൾ ഓരോ വാക്കും പ്രിഫിക്‌സ് ഉപയോഗിച്ച് തിരയേണ്ടതുണ്ട്. ഇല്ല, മുമ്പത്തെ വാക്കിന്റെ വേഗത്തിലുള്ള സൂചനയോട് പ്രതികരിക്കുന്നത് ഒരു ഉപയോക്താവിന് മുമ്പത്തേതിനെ ലക്ഷ്യത്തോടെ "കുറച്ച്" കാണിക്കുന്നതിനേക്കാൾ വളരെ എളുപ്പമാണ് - ഏത് സെർച്ച് എഞ്ചിനും ഇത് എങ്ങനെ കൈകാര്യം ചെയ്യുന്നുവെന്ന് നോക്കുക.

ജനറൽ ശരി പ്രശ്നത്തിനുള്ള ആവശ്യകതകൾ രൂപപ്പെടുത്തുന്നത് പകുതിയിലധികം പരിഹാരമാണ്. ചിലപ്പോൾ ശ്രദ്ധാപൂർവമായ ഉപയോഗം കേസ് വിശകലനം ഫലത്തെ ഗണ്യമായി സ്വാധീനിക്കാൻ കഴിയും.

ഒരു അമൂർത്ത ഡെവലപ്പർ എന്താണ് ചെയ്യുന്നത്?

1.0: ബാഹ്യ തിരയൽ എഞ്ചിൻ

ഓ, തിരയൽ ബുദ്ധിമുട്ടാണ്, എനിക്ക് ഒന്നും ചെയ്യാൻ താൽപ്പര്യമില്ല - നമുക്ക് അത് devops-ന് നൽകാം! ഡാറ്റാബേസിന് പുറത്ത് ഒരു സെർച്ച് എഞ്ചിൻ വിന്യസിക്കാൻ അവരെ അനുവദിക്കുക: സ്ഫിൻക്സ്, ഇലാസ്റ്റിക് സെർച്ച്,...

സിൻക്രൊണൈസേഷനും മാറ്റങ്ങളുടെ വേഗതയും കണക്കിലെടുത്ത് അധ്വാനം-ഇന്റൻസീവ് ആണെങ്കിലും ഒരു പ്രവർത്തന ഓപ്ഷൻ. എന്നാൽ ഞങ്ങളുടെ കാര്യത്തിലല്ല, കാരണം ഓരോ ക്ലയന്റിനും അവന്റെ അക്കൗണ്ട് ഡാറ്റയുടെ ചട്ടക്കൂടിനുള്ളിൽ മാത്രമാണ് തിരയൽ നടത്തുന്നത്. ഡാറ്റയ്ക്ക് ഉയർന്ന വേരിയബിളിറ്റി ഉണ്ട് - മാനേജർ ഇപ്പോൾ കാർഡിൽ നൽകിയിട്ടുണ്ടെങ്കിൽ 'Магазин Роза', പിന്നീട് 5-10 സെക്കൻഡുകൾക്ക് ശേഷം അവൻ തന്റെ ഇമെയിൽ അവിടെ സൂചിപ്പിക്കാൻ മറന്നുവെന്നും അത് കണ്ടെത്തി ശരിയാക്കാൻ ആഗ്രഹിക്കുന്നുവെന്നും അദ്ദേഹം ഇതിനകം ഓർത്തിരിക്കാം.

അതിനാൽ - നമുക്ക് "നേരിട്ട് ഡാറ്റാബേസിൽ" തിരയുക. ഭാഗ്യവശാൽ, PostgreSQL ഇത് ചെയ്യാൻ ഞങ്ങളെ അനുവദിക്കുന്നു, ഒരു ഓപ്ഷൻ മാത്രമല്ല - ഞങ്ങൾ അവ നോക്കും.

1.1: "സത്യസന്ധമായ" ഉപസ്‌ട്രിംഗ്

"സബ്‌സ്ട്രിംഗ്" എന്ന വാക്ക് ഞങ്ങൾ മുറുകെ പിടിക്കുന്നു. എന്നാൽ സബ്‌സ്‌ട്രിംഗിലൂടെയുള്ള ഇൻഡെക്‌സ് തിരയലിന് (സാധാരണ എക്‌സ്‌പ്രഷനുകൾ വഴി പോലും!) ഒരു മികച്ചതുണ്ട് മൊഡ്യൂൾ pg_trgm! അപ്പോൾ മാത്രമേ ശരിയായി അടുക്കാൻ അത് ആവശ്യമായി വരികയുള്ളൂ.

മോഡൽ ലളിതമാക്കാൻ ഇനിപ്പറയുന്ന പ്ലേറ്റ് എടുക്കാൻ ശ്രമിക്കാം:

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

യഥാർത്ഥ സ്ഥാപനങ്ങളുടെ 7.8 ദശലക്ഷം റെക്കോർഡുകൾ ഞങ്ങൾ അവിടെ അപ്‌ലോഡ് ചെയ്യുകയും അവയെ സൂചികയിലാക്കുകയും ചെയ്യുന്നു:

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

ഇന്റർലീനിയർ തിരയലിനായി ആദ്യത്തെ 10 റെക്കോർഡുകൾ നോക്കാം:

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

[explain.tensor.ru-ൽ കാണുക]

ശരി, അത്... 26എംഎസ്, 31എംബി റീഡ് ഡാറ്റയും 1.7K-ൽ കൂടുതൽ ഫിൽട്ടർ ചെയ്ത റെക്കോർഡുകളും - 10 തിരഞ്ഞവയ്ക്ക്. ഓവർഹെഡ് ചെലവുകൾ വളരെ കൂടുതലാണ്, കൂടുതൽ കാര്യക്ഷമമായ എന്തെങ്കിലും ഇല്ലേ?

1.2: ടെക്സ്റ്റ് പ്രകാരം തിരയണോ? ഇത് FTS ആണ്!

തീർച്ചയായും, PostgreSQL വളരെ ശക്തമാണ് മുഴുവൻ ടെക്സ്റ്റ് തിരയൽ എഞ്ചിൻ (പൂർണ്ണമായ വാചക തിരയൽ), തിരച്ചിൽ പ്രിഫിക്‌സ് ചെയ്യാനുള്ള കഴിവ് ഉൾപ്പെടെ. ഒരു മികച്ച ഓപ്ഷൻ, നിങ്ങൾ വിപുലീകരണങ്ങൾ ഇൻസ്റ്റാൾ ചെയ്യേണ്ടതില്ല! നമുക്ക് ശ്രമിക്കാം:

CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));

SELECT
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;

[explain.tensor.ru-ൽ കാണുക]

ഇവിടെ അന്വേഷണ നിർവ്വഹണത്തിന്റെ സമാന്തരവൽക്കരണം ഞങ്ങളെ അൽപ്പം സഹായിച്ചു, സമയം പകുതിയായി കുറച്ചു 11 മി. ഞങ്ങൾക്ക് 1.5 മടങ്ങ് കുറവ് വായിക്കേണ്ടി വന്നു - മൊത്തത്തിൽ 20MB. എന്നാൽ ഇവിടെ, കുറവ്, നല്ലത്, കാരണം നമ്മൾ വായിക്കുന്ന വോളിയം വലുതായതിനാൽ, ഒരു കാഷെ നഷ്ടപ്പെടാനുള്ള സാധ്യത കൂടുതലാണ്, കൂടാതെ ഡിസ്കിൽ നിന്ന് വായിക്കുന്ന ഡാറ്റയുടെ ഓരോ അധിക പേജും അഭ്യർത്ഥനയ്ക്കുള്ള സാധ്യതയുള്ള "ബ്രേക്കുകൾ" ആണ്.

1.3: ഇപ്പോഴും ഇഷ്ടമാണോ?

മുമ്പത്തെ അഭ്യർത്ഥന എല്ലാവർക്കും നല്ലതാണ്, പക്ഷേ ഒരു ദിവസം നൂറായിരം തവണ വലിച്ചാൽ മാത്രമേ അത് വരൂ 2TB ഡാറ്റ വായിക്കുക. മികച്ച സാഹചര്യത്തിൽ, മെമ്മറിയിൽ നിന്ന്, പക്ഷേ നിങ്ങൾക്ക് ഭാഗ്യമില്ലെങ്കിൽ, ഡിസ്കിൽ നിന്ന്. അതുകൊണ്ട് ചെറുതാക്കാൻ ശ്രമിക്കാം.

ഉപയോക്താവ് എന്താണ് കാണാൻ ആഗ്രഹിക്കുന്നതെന്ന് നമുക്ക് ഓർക്കാം ആദ്യം "ഇതിൽ തുടങ്ങുന്നു...". അതിനാൽ ഇത് അതിന്റെ ശുദ്ധമായ രൂപത്തിലാണ് പ്രിഫിക്സ് തിരയൽ സഹായത്തോടെ text_pattern_ops! ഞങ്ങൾ തിരയുന്ന 10 റെക്കോർഡുകൾ വരെ "മതിയായില്ലെങ്കിൽ" മാത്രം, FTS തിരയൽ ഉപയോഗിച്ച് ഞങ്ങൾ അവ വായിച്ച് പൂർത്തിയാക്കേണ്ടതുണ്ട്:

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

[explain.tensor.ru-ൽ കാണുക]

മികച്ച പ്രകടനം - ആകെ 0.05ms, 100KB-യിൽ അൽപ്പം കൂടുതലും വായിക്കുക! നമ്മൾ മാത്രം മറന്നു പേര് പ്രകാരം അടുക്കുകഫലങ്ങളിൽ ഉപയോക്താവ് നഷ്ടപ്പെടാതിരിക്കാൻ:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

[explain.tensor.ru-ൽ കാണുക]

ഓ, എന്തെങ്കിലും ഇപ്പോൾ അത്ര മനോഹരമല്ല - ഒരു സൂചിക ഉണ്ടെന്ന് തോന്നുന്നു, പക്ഷേ സോർട്ടിംഗ് അതിനെ മറികടക്കുന്നു ... തീർച്ചയായും ഇത് മുമ്പത്തെ ഓപ്ഷനേക്കാൾ പലമടങ്ങ് ഫലപ്രദമാണ്, പക്ഷേ ...

1.4: "ഒരു ഫയൽ ഉപയോഗിച്ച് പൂർത്തിയാക്കുക"

എന്നാൽ ശ്രേണി അനുസരിച്ച് തിരയാനും സാധാരണ രീതിയിൽ സോർട്ടിംഗ് ഉപയോഗിക്കാനും നിങ്ങളെ അനുവദിക്കുന്ന ഒരു സൂചികയുണ്ട് - സാധാരണ btree!

CREATE INDEX ON firms(lower(name));

അതിനുള്ള അഭ്യർത്ഥന മാത്രം "സ്വമേധയാ ശേഖരിക്കുക":

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

[explain.tensor.ru-ൽ കാണുക]

മികച്ചത് - സോർട്ടിംഗ് പ്രവർത്തിക്കുന്നു, കൂടാതെ വിഭവ ഉപഭോഗം "സൂക്ഷ്മമായി" തുടരുന്നു, "ശുദ്ധമായ" FTS നേക്കാൾ ആയിരക്കണക്കിന് മടങ്ങ് കൂടുതൽ ഫലപ്രദമാണ്! ഒരൊറ്റ അഭ്യർത്ഥനയായി ഇത് കൂട്ടിച്ചേർക്കുക മാത്രമാണ് അവശേഷിക്കുന്നത്:

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'роза' AND
    lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
  ORDER BY
     lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
    lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
  ORDER BY
    lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
  , lower(name)
  LIMIT 10
)
LIMIT 10;

രണ്ടാമത്തെ സബ്ക്വറി എക്സിക്യൂട്ട് ചെയ്തുവെന്നത് ശ്രദ്ധിക്കുക ആദ്യത്തേത് പ്രതീക്ഷിച്ചതിലും കുറവാണെങ്കിൽ മാത്രം അവസാനത്തെ LIMIT വരികളുടെ എണ്ണം. അന്വേഷണ ഒപ്റ്റിമൈസേഷന്റെ ഈ രീതിയെക്കുറിച്ചാണ് ഞാൻ സംസാരിക്കുന്നത് മുമ്പ് എഴുതിയിട്ടുണ്ട്.

അതെ, ഞങ്ങൾക്ക് ഇപ്പോൾ മേശപ്പുറത്ത് ബിട്രീയും ജിന്നും ഉണ്ട്, പക്ഷേ സ്ഥിതിവിവരക്കണക്കനുസരിച്ച് ഇത് മാറുന്നു 10% ൽ താഴെ അഭ്യർത്ഥനകൾ രണ്ടാമത്തെ ബ്ലോക്കിന്റെ നിർവ്വഹണത്തിലേക്ക് എത്തുന്നു. അതായത്, ടാസ്‌ക്കിനായി മുൻകൂട്ടി അറിയാവുന്ന അത്തരം സാധാരണ പരിമിതികൾ ഉള്ളതിനാൽ, സെർവർ വിഭവങ്ങളുടെ മൊത്തം ഉപഭോഗം ഏകദേശം ആയിരം മടങ്ങ് കുറയ്ക്കാൻ ഞങ്ങൾക്ക് കഴിഞ്ഞു!

1.5*: നമുക്ക് ഒരു ഫയൽ ഇല്ലാതെ ചെയ്യാൻ കഴിയും

മുകളിൽ LIKE തെറ്റായ സോർട്ടിംഗ് ഉപയോഗിക്കുന്നതിൽ നിന്ന് ഞങ്ങളെ തടഞ്ഞു. എന്നാൽ, USING ഓപ്പറേറ്റർ വ്യക്തമാക്കിക്കൊണ്ട് ഇത് "ശരിയായ പാതയിൽ സജ്ജമാക്കാൻ" കഴിയും:

സ്ഥിരസ്ഥിതിയായി അത് അനുമാനിക്കപ്പെടുന്നു ASC. കൂടാതെ, ഒരു ക്ലോസിൽ ഒരു നിർദ്ദിഷ്ട സോർട്ട് ഓപ്പറേറ്ററുടെ പേര് നിങ്ങൾക്ക് വ്യക്തമാക്കാൻ കഴിയും USING. സോർട്ട് ഓപ്പറേറ്റർ ചില ബി-ട്രീ ഓപ്പറേറ്റർമാരുടെ കുടുംബത്തേക്കാൾ കുറവോ വലുതോ ആയ അംഗമായിരിക്കണം. ASC സാധാരണയായി തുല്യമാണ് USING < и DESC സാധാരണയായി തുല്യമാണ് USING >.

ഞങ്ങളുടെ കാര്യത്തിൽ, "കുറവ്" ആണ് ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

[explain.tensor.ru-ൽ കാണുക]

2: അഭ്യർത്ഥനകൾ എങ്ങനെ ചീത്തയാകുന്നു

ഇപ്പോൾ ഞങ്ങൾ ഞങ്ങളുടെ അഭ്യർത്ഥന ആറ് മാസത്തേക്കോ ഒരു വർഷത്തേക്കോ "അരക്കുക" ഉപേക്ഷിക്കുന്നു, കൂടാതെ മെമ്മറിയുടെ മൊത്തം ദൈനംദിന "പമ്പിംഗിന്റെ" സൂചകങ്ങൾക്കൊപ്പം അത് വീണ്ടും "മുകളിൽ" കണ്ടെത്തുന്നതിൽ ഞങ്ങൾ ആശ്ചര്യപ്പെടുന്നു (ബഫറുകൾ പങ്കിട്ട ഹിറ്റ്) ൽ 5.5TB - അതായത്, യഥാർത്ഥത്തിൽ ഉണ്ടായിരുന്നതിനേക്കാൾ കൂടുതൽ.

ഇല്ല, തീർച്ചയായും, ഞങ്ങളുടെ ബിസിനസ്സ് വളർന്നു, ഞങ്ങളുടെ ജോലിഭാരം വർദ്ധിച്ചു, പക്ഷേ അതേ അളവിൽ അല്ല! ഇതിനർത്ഥം ഇവിടെ എന്തോ മത്സ്യം ഉണ്ടെന്നാണ് - നമുക്ക് അത് കണ്ടെത്താം.

2.1: പേജിംഗിന്റെ ജനനം

ചില ഘട്ടങ്ങളിൽ, മറ്റൊരു ഡെവലപ്‌മെന്റ് ടീം, ദ്രുത സബ്‌സ്‌ക്രിപ്‌റ്റ് തിരയലിൽ നിന്ന് രജിസ്‌ട്രിയിലേക്ക് അതേ, എന്നാൽ വിപുലീകരിച്ച ഫലങ്ങൾ ഉപയോഗിച്ച് "ചാടി" സാധ്യമാക്കാൻ ആഗ്രഹിച്ചു. പേജ് നാവിഗേഷൻ ഇല്ലാത്ത ഒരു രജിസ്ട്രി എന്താണ്? നമുക്ക് അത് പൊളിക്കാം!

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

ഡെവലപ്പർക്ക് യാതൊരു സമ്മർദ്ദവുമില്ലാതെ "പേജ്-ബൈ-പേജ്" ലോഡിംഗ് ഉപയോഗിച്ച് തിരയൽ ഫലങ്ങളുടെ രജിസ്ട്രി കാണിക്കാൻ ഇപ്പോൾ സാധിച്ചു.

തീർച്ചയായും, വാസ്തവത്തിൽ, ഓരോ തുടർന്നുള്ള ഡാറ്റ പേജുകൾക്കും കൂടുതൽ കൂടുതൽ വായിക്കുന്നു (എല്ലാം മുമ്പത്തെ സമയം മുതൽ, ഞങ്ങൾ ഉപേക്ഷിക്കും, കൂടാതെ ആവശ്യമായ “വാലും”) - അതായത്, ഇതൊരു വ്യക്തമായ ആന്റിപാറ്റേൺ ആണ്. എന്നാൽ ഇന്റർഫേസിൽ സംഭരിച്ചിരിക്കുന്ന കീയിൽ നിന്ന് അടുത്ത ആവർത്തനത്തിൽ തിരയൽ ആരംഭിക്കുന്നത് കൂടുതൽ ശരിയായിരിക്കും, എന്നാൽ അതിനെക്കുറിച്ച് മറ്റൊരു സമയം.

2.2: എനിക്ക് വിചിത്രമായ എന്തെങ്കിലും വേണം

ചില ഘട്ടങ്ങളിൽ ഡെവലപ്പർ ആഗ്രഹിച്ചു തത്ഫലമായുണ്ടാകുന്ന സാമ്പിൾ ഡാറ്റ ഉപയോഗിച്ച് വൈവിധ്യവൽക്കരിക്കുക മറ്റൊരു പട്ടികയിൽ നിന്ന്, മുമ്പത്തെ മുഴുവൻ അഭ്യർത്ഥനയും CTE-ലേക്ക് അയച്ചു:

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

അങ്ങനെയാണെങ്കിലും, ഇത് മോശമല്ല, കാരണം മടങ്ങിയ 10 റെക്കോർഡുകൾക്കായി മാത്രമാണ് സബ്ക്വറി വിലയിരുത്തുന്നത്, ഇല്ലെങ്കിൽ ...

2.3: വ്യതിരിക്തത അർത്ഥശൂന്യവും കരുണയില്ലാത്തതുമാണ്

2-ആം സബ്ക്വറിയിൽ നിന്ന് അത്തരം പരിണാമ പ്രക്രിയയിൽ എവിടെയോ നഷ്ടപ്പെട്ടു NOT LIKE അവസ്ഥ. ഇതിന് പിന്നാലെയാണ് ഇക്കാര്യം വ്യക്തമായത് UNION ALL തിരിച്ചുവരാൻ തുടങ്ങി ചില എൻട്രികൾ രണ്ടുതവണ - ആദ്യം വരിയുടെ തുടക്കത്തിൽ കണ്ടെത്തി, തുടർന്ന് വീണ്ടും - ഈ വരിയുടെ ആദ്യ വാക്കിന്റെ തുടക്കത്തിൽ. പരിധിയിൽ, രണ്ടാമത്തെ സബ്ക്വറിയുടെ എല്ലാ റെക്കോർഡുകളും ആദ്യത്തേതിന്റെ റെക്കോർഡുകളുമായി പൊരുത്തപ്പെടാം.

കാരണം അന്വേഷിക്കുന്നതിന് പകരം ഒരു ഡെവലപ്പർ എന്താണ് ചെയ്യുന്നത്?.. ചോദ്യമില്ല!

• ഇരട്ടി വലിപ്പം യഥാർത്ഥ സാമ്പിളുകൾ
• DISTINCT പ്രയോഗിക്കുകഓരോ വരിയുടെയും ഒരൊറ്റ ഉദാഹരണങ്ങൾ മാത്രം ലഭിക്കാൻ

WITH q AS (
  ( ... LIMIT <2 * N> + 10)
  UNION ALL
  ( ... LIMIT <2 * N> + 10)
  LIMIT <2 * N> + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET <N>;

അതായത്, ഫലം, അവസാനം, കൃത്യമായി സമാനമാണെന്ന് വ്യക്തമാണ്, എന്നാൽ 2nd CTE സബ്ക്വറിയിലേക്ക് "പറക്കാനുള്ള" സാധ്യത വളരെ ഉയർന്നതാണ്, കൂടാതെ ഇത് കൂടാതെ, വ്യക്തമായി കൂടുതൽ വായിക്കാൻ കഴിയും.

എന്നാൽ ഇത് ഏറ്റവും സങ്കടകരമായ കാര്യമല്ല. ഡവലപ്പർ തിരഞ്ഞെടുക്കാൻ ആവശ്യപ്പെട്ടതിനാൽ DISTINCT നിർദ്ദിഷ്ടവയ്‌ക്കല്ല, എല്ലാ ഫീൽഡുകൾക്കും ഒരേസമയം റെക്കോർഡുകൾ, തുടർന്ന് sub_query ഫീൽഡ് - സബ്ക്വറിയുടെ ഫലം - അവിടെ യാന്ത്രികമായി ഉൾപ്പെടുത്തി. ഇപ്പോൾ, എക്സിക്യൂട്ട് ചെയ്യാൻ DISTINCT, ഡാറ്റാബേസ് ഇതിനകം തന്നെ എക്സിക്യൂട്ട് ചെയ്യേണ്ടതുണ്ട് 10 സബ്ക്വറികളല്ല, എല്ലാം <2 * N> + 10!

2.4: എല്ലാറ്റിനുമുപരിയായി സഹകരണം!

അതിനാൽ, ഡവലപ്പർമാർ ജീവിച്ചു - അവർ വിഷമിച്ചില്ല, കാരണം ഓരോ തുടർന്നുള്ള “പേജും” സ്വീകരിക്കുന്നതിലെ വിട്ടുമാറാത്ത മന്ദതയോടെ, പ്രധാനപ്പെട്ട N മൂല്യങ്ങളിലേക്ക് രജിസ്ട്രി “ക്രമീകരിക്കാൻ” ഉപയോക്താവിന് മതിയായ ക്ഷമ ഇല്ലായിരുന്നു.

മറ്റൊരു ഡിപ്പാർട്ട്മെന്റിൽ നിന്നുള്ള ഡവലപ്പർമാർ അവരുടെ അടുത്ത് വന്ന് അത്തരമൊരു സൗകര്യപ്രദമായ രീതി ഉപയോഗിക്കാൻ ആഗ്രഹിക്കുന്നതുവരെ ആവർത്തന തിരയലിനായി - അതായത്, ഞങ്ങൾ ചില സാമ്പിളിൽ നിന്ന് ഒരു കഷണം എടുക്കുന്നു, അധിക വ്യവസ്ഥകൾ ഉപയോഗിച്ച് അത് ഫിൽട്ടർ ചെയ്യുന്നു, ഫലം വരയ്ക്കുന്നു, തുടർന്ന് അടുത്ത കഷണം (ഞങ്ങളുടെ കാര്യത്തിൽ ഇത് N വർദ്ധിപ്പിക്കുന്നതിലൂടെ നേടാം), അങ്ങനെ ഞങ്ങൾ സ്‌ക്രീൻ പൂരിപ്പിക്കുന്നതുവരെ.

പൊതുവേ, പിടിക്കപ്പെട്ട മാതൃകയിൽ N ഏതാണ്ട് 17K മൂല്യങ്ങളിൽ എത്തി, ഒരു ദിവസത്തിനുള്ളിൽ അത്തരം അഭ്യർത്ഥനകളിൽ 4K എങ്കിലും "ചങ്ങലയിൽ" നടപ്പിലാക്കി. അവയിൽ അവസാനത്തേത് ധൈര്യത്തോടെ സ്കാൻ ചെയ്തു ഓരോ ആവർത്തനത്തിനും 1GB മെമ്മറിപങ്ക് € |

ആകെ

അവലംബം: www.habr.com