PostgreSQL Antipatterns: නම අනුව සෙවීමේ පුනරාවර්තන පිරිපහදු කිරීමේ කතාව, හෝ "ආපසු සහ ඉදිරියට ප්‍රශස්තකරණය"

රට පුරා විකුණුම් කාර්යාලවලින් කළමනාකරුවන් දහස් ගණනක් වාර්තා කරයි අපගේ CRM පද්ධතිය දිනපතා සම්බන්ධතා දස දහස් ගණනක් - විභව හෝ පවතින ගනුදෙනුකරුවන් සමඟ සන්නිවේදනයේ කරුණු. මේ සඳහා, ඔබ මුලින්ම සේවාදායකයෙකු සොයා ගත යුතු අතර, වඩාත් ඉක්මනින් ඉතා ඉක්මනින්. තවද මෙය බොහෝ විට සිදුවන්නේ නමින්.

එමනිසා, වැඩිපුරම පටවා ඇති දත්ත සමුදායන්ගෙන් එකක් වන අපගේම “බර” විමසුම් නැවත වරක් විශ්ලේෂණය කිරීම පුදුමයක් නොවේ. VLSI ආයතනික ගිණුම, මට "ඉහළින්" හමු විය නම අනුව "ඉක්මන්" සෙවුමක් සඳහා ඉල්ලීම සංවිධාන කාඩ්පත් සඳහා.

එපමණක් නොව, වැඩිදුර විමර්ශනය රසවත් උදාහරණයක් අනාවරණය විය පළමු ප්රශස්තකරණය සහ පසුව කාර්ය සාධනය පිරිහීම කණ්ඩායම් කිහිපයක් විසින් එහි අනුක්‍රමික ශෝධනය සමඟ ඉල්ලීමක් කරන අතර, ඒ සෑම එකක්ම හොඳම අභිප්‍රායයන් සමඟ පමණක් ක්‍රියා කළේය.

0: පරිශීලකයාට අවශ්‍ය වූයේ කුමක්ද?

[KDPV මෙතනින්]

නම අනුව "ඉක්මන්" සෙවුමක් ගැන කතා කරන විට පරිශීලකයෙකු සාමාන්යයෙන් අදහස් කරන්නේ කුමක්ද? එය කිසි විටෙක පාහේ වැනි උප තන්තුවක් සඳහා "අවංක" සෙවීමක් බවට පත් නොවේ ... LIKE '%роза%' - මක්නිසාද යත් එවිට ප්‍රතිඵලයට ඇතුළත් වන්නේ පමණක් නොවේ 'Розалия' и 'Магазин Роза', නමුත් ඒවගේම 'Гроза' සහ පවා 'Дом Деда Мороза'.

ඔබ ඔහුට ලබා දෙන බව පරිශීලකයා එදිනෙදා මට්ටමින් උපකල්පනය කරයි වචනයේ ආරම්භය අනුව සොයන්න මාතෘකාව තුළ සහ එය වඩාත් අදාළ කරන්න සමඟ ආරම්භ වේ ඇතුල් විය. තවද ඔබ එය කරනු ඇත ක්ෂණිකව පාහේ - අන්තර් රේඛීය ආදානය සඳහා.

1: කාර්යය සීමා කරන්න

ඊටත් වඩා, පුද්ගලයෙකු විශේෂයෙන් ඇතුල් නොවනු ඇත 'роз магаз', එවිට ඔබට එක් එක් වචනය උපසර්ගයෙන් සෙවිය යුතුය. නැත, පරිශීලකයෙකුට කලින් වචන හිතාමතාම "අඩු කිරීමට" වඩා අවසාන වචනය සඳහා ඉක්මන් ඉඟියකට ප්‍රතිචාර දැක්වීම පහසුය - ඕනෑම සෙවුම් යන්ත්‍රයක් මෙය හසුරුවන්නේ කෙසේදැයි බලන්න.

සාමාන්යයෙන් හරි ගැටලුව සඳහා අවශ්‍යතා සකස් කිරීම විසඳුමෙන් අඩකට වඩා වැඩිය. සමහර විට ප්රවේශමෙන් භාවිතා කිරීමේ සිද්ධි විශ්ලේෂණය ප්රතිඵලය සැලකිය යුතු ලෙස බලපෑ හැකිය.

වියුක්ත සංවර්ධකයෙකු කරන්නේ කුමක්ද?

1.0: බාහිර සෙවුම් යන්ත්‍රය

ඔහ්, සෙවීම දුෂ්කර ය, මට කිසිවක් කිරීමට අවශ්‍ය නැත - අපි එය devops වෙත දෙමු! දත්ත සමුදායෙන් බාහිරව සෙවුම් යන්ත්‍රයක් යෙදවීමට ඔවුන්ට ඉඩ දෙන්න: Sphinx, ElasticSearch,...

සමමුහුර්තකරණය සහ වෙනස්කම්වල වේගය අනුව ශ්‍රම-දැඩි වුවද, වැඩ කරන විකල්පයක්. නමුත් අපගේ නඩුවේදී නොවේ, මන්දයත් එක් එක් සේවාදායකයා සඳහා සෙවීම ඔහුගේ ගිණුම් දත්ත රාමුව තුළ පමණක් සිදු කරනු ලැබේ. තවද දත්ත තරමක් ඉහළ විචල්‍යතාවයක් ඇත - සහ කළමනාකරු දැන් කාඩ්පතට ඇතුළු වී තිබේ නම් 'Магазин Роза', පසුව තත්පර 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;

[විස්තර කරන්න.tensor.ru බලන්න]

හොඳයි, ඒක... 26ms, 31MB දත්ත කියවීම සහ 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;

[විස්තර කරන්න.tensor.ru බලන්න]

මෙහිදී විමසුම් ක්‍රියාත්මක කිරීම සමාන්තරකරණය කිරීම අපට ටිකක් උපකාරී වූ අතර, කාලය අඩකින් අඩු කළේය 11ms. අපට 1.5 ගුණයකින් අඩුවෙන් කියවීමට සිදු විය - සමස්තයක් ලෙස 20MB. නමුත් මෙහිදී, අඩු, වඩා හොඳ, අපි කියවන පරිමාව විශාල වන නිසා, හැඹිලි මිස් ලබා ගැනීමේ අවස්ථාව වැඩි වන අතර, තැටියෙන් කියවන දත්තවල සෑම අමතර පිටුවක්ම ඉල්ලීම සඳහා විභව "තිරිංග" වේ.

1.3: තවමත් කැමතිද?

කලින් රික්වෙස්ට් එක හැමෝටම හොදයි ඒත් දවසකට ලක්ශ පාරක් ඇදල ගත්තොත් තමයි එන්නේ ඩී දත්ත කියවන්න. හොඳම අවස්ථාවේ දී, මතකයෙන්, නමුත් ඔබ අවාසනාවන්ත නම්, පසුව තැටියෙන්. ඒ නිසා අපි එය කුඩා කිරීමට උත්සාහ කරමු.

පරිශීලකයාට දැකීමට අවශ්‍ය දේ මතක තබා ගනිමු පළමු "ආරම්භ කරන...". එබැවින් මෙය එහි පිරිසිදු ස්වරූපයෙන් පවතී උපසර්ග සෙවීම උදව්වෙන් text_pattern_ops! අප සොයන වාර්තා 10 ක් දක්වා “ප්‍රමාණවත් නොමැති” නම් පමණක්, අපට ඒවා FTS සෙවුම භාවිතයෙන් කියවීම අවසන් කිරීමට සිදුවේ:

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

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

[විස්තර කරන්න.tensor.ru බලන්න]

විශිෂ්ට කාර්ය සාධනය - සම්පූර්ණ 0.05ms සහ 100KB ට වඩා ටිකක් වැඩි කියවන්න! අපට පමණක් අමතක විය නම අනුව වර්ග කරන්නපරිශීලකයා ප්‍රතිඵලවලින් අතරමං නොවන පරිදි:

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

[විස්තර කරන්න.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;

[විස්තර කරන්න.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 පේළි ගණන. මම කතා කරන්නේ මෙම විමසුම් ප්‍රශස්තකරණයේ ක්‍රමය ගැන ය දැනටමත් කලින් ලියා ඇත.

ඉතින් ඔව්, දැන් අපි මේසය මත btree සහ gin දෙකම ඇත, නමුත් සංඛ්යා ලේඛන අනුව එය හැරෙනවා ඉල්ලීම් වලින් 10% කට වඩා අඩු ප්‍රමාණයක් දෙවන කොටස ක්‍රියාත්මක කිරීමට ළඟා වේ. එනම්, කාර්යය සඳහා කල්තියා දන්නා එවැනි සාමාන්‍ය සීමාවන් සමඟ, සේවාදායක සම්පත්වල මුළු පරිභෝජනය දහස් ගුණයකින් පමණ අඩු කිරීමට අපට හැකි විය!

1.5*: අපට ගොනුවක් නොමැතිව කළ හැකිය

ඉහළ LIKE වැරදි ලෙස වර්ග කිරීම භාවිතා කිරීමෙන් අපි වැළකුණා. නමුත් භාවිතා කරන ක්‍රියාකරු නියම කිරීමෙන් එය "නිවැරදි මාර්ගයට" සැකසිය හැක:

පෙරනිමියෙන් එය උපකල්පනය කෙරේ ASC. අතිරේකව, ඔබට වගන්තියක නිශ්චිත වර්ග කිරීමේ ක්රියාකරුගේ නම සඳහන් කළ හැකිය USING. වර්ග කිරීමේ ක්‍රියාකරු B-tree ක්‍රියාකරුවන්ගේ සමහර පවුලකට වඩා අඩු හෝ වැඩි සාමාජිකයෙකු විය යුතුය. ASC සාමාන්යයෙන් සමාන වේ USING < и DESC සාමාන්යයෙන් සමාන වේ USING >.

අපගේ නඩුවේදී, "අඩු" වේ ~<~:

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

[විස්තර කරන්න.tensor.ru බලන්න]

2: ඉල්ලීම් ඇඹුල් වන්නේ කෙසේද?

දැන් අපි අපගේ ඉල්ලීම මාස හයක් හෝ අවුරුද්දක් "ගිම්ම" කිරීමට තබමු, සහ මතකයේ මුළු දෛනික "පොම්ප කිරීමේ" දර්ශක සමඟ එය නැවත "ඉහළින්" සොයා ගැනීම ගැන අපි පුදුම වෙමු (buffers share hit) තුළ ඩී - එනම්, එය මුලින් තිබුනාට වඩා වැඩි ය.

නැත, ඇත්ත වශයෙන්ම, අපගේ ව්‍යාපාරය වර්ධනය වී ඇති අතර අපගේ වැඩ බර වැඩි වී ඇත, නමුත් එම ප්‍රමාණයෙන් නොවේ! මෙයින් අදහස් කරන්නේ මෙහි යමක් මාළු ඇති බවයි - අපි එය හදුනා ගනිමු.

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: DISTINCT තේරුමක් නැති සහ අනුකම්පා විරහිත ය

2 වන උප ප්‍රශ්නාවලියෙන් එවැනි පරිණාමයේ ක්‍රියාවලියක කොහේ හරි අතරමං වුණා NOT LIKE තත්වය. මෙයින් පසු බව පැහැදිලිය UNION ALL ආපසු පැමිණීමට පටන් ගත්තේය සමහර ඇතුළත් කිරීම් දෙවරක් - මුලින්ම පේළියේ ආරම්භයේ දී හමු වූ අතර, පසුව නැවතත් - මෙම පේළියේ පළමු වචනයේ ආරම්භයේ දී. සීමාව තුළ, 2 වන උප විමසුමේ සියලුම වාර්තා පළමු එකෙහි වාර්තා සමඟ සැසඳිය හැක.

හේතුව සොයනවා වෙනුවට සංවර්ධකයෙකු කරන්නේ කුමක්ද?.. ප්රශ්නයක් නැත!

• විශාලත්වය දෙගුණයක් මුල් සාම්පල
• 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>;

එනම්, ප්‍රති result ලය අවසානයේ හරියටම සමාන බව පැහැදිලිය, නමුත් 2 වන CTE උප විමසුමට “පියාසර කිරීමේ” අවස්ථාව බෙහෙවින් වැඩි වී ඇති අතර මෙය නොමැතිව වුවද, පැහැදිලිවම වඩාත් කියවිය හැකිය.

නමුත් කණගාටුදායකම දෙය මෙය නොවේ. සංවර්ධකයා තෝරා ගැනීමට ඉල්ලා සිටි බැවින් DISTINCT විශේෂිත ඒවා සඳහා නොව, සියලුම ක්ෂේත්ර සඳහා එකවර වාර්තා, පසුව sub_query ක්ෂේත්‍රය - උප විමසුමේ ප්‍රතිඵලය - එහි ස්වයංක්‍රීයව ඇතුළත් විය. දැන්, ක්රියාත්මක කිරීමට DISTINCT, දත්ත සමුදාය දැනටමත් ක්රියාත්මක කිරීමට සිදු විය උප විමසුම් 10 ක් නොව, සියල්ල <2 * N> + 10!

2.4: සියල්ලටම වඩා සහයෝගීතාව!

එබැවින්, සංවර්ධකයින් ජීවත් විය - ඔවුන් කරදර වූයේ නැත, මන්ද පරිශීලකයාට පැහැදිලිවම ලේඛනය සැලකිය යුතු N අගයන් වෙත “ගැලපීම” කිරීමට ප්‍රමාණවත් ඉවසීමක් නොතිබූ අතර පසුව එක් එක් “පිටුව” ලැබීමේ නිදන්ගත මන්දගාමිත්වයක් ඇත.

වෙනත් දෙපාර්තමේන්තුවකින් සංවර්ධකයින් ඔවුන් වෙත පැමිණෙන තෙක් සහ එවැනි පහසු ක්රමයක් භාවිතා කිරීමට අවශ්ය විය පුනරාවර්තන සෙවීම සඳහා - එනම්, අපි යම් නියැදියකින් කෑල්ලක් ගෙන, අතිරේක කොන්දේසි මගින් එය පෙරීම, ප්රතිඵලය අඳින්න, ඊළඟ කෑල්ල (අපගේ නඩුවේ N වැඩි කිරීමෙන් ලබා ගත හැකි) සහ අපි තිරය පුරවන තුරු.

පොදුවේ ගත් කල, අල්ලා ගත් නියැදිය තුළ N 17K ආසන්න අගයන් කරා ළඟා විය, සහ එක් දිනක් තුළ අවම වශයෙන් එවැනි ඉල්ලීම් 4K ක් "දාමය දිගේ" ක්‍රියාත්මක කරන ලදී. ඔවුන්ගෙන් අන්තිමයා නිර්භීතව ස්කෑන් කරන ලදී පුනරාවර්තනයකට 1GB මතකය...

එකතුව

මූලාශ්රය: www.habr.com