ProHoster > බ්ලොග් > පරිපාලනය > අසනීප SQL විමසුම් සඳහා වට්ටෝරු

අසනීප SQL විමසුම් සඳහා වට්ටෝරු

මාසයකට කලින් අපි නිවේදනය කළා පැහැදිලි කරන්න.tensor.ru - පොදු විමසුම් සැලසුම් විග්‍රහ කිරීම සහ දෘශ්‍යකරණය සඳහා සේවාව PostgreSQL වෙත.

ඔබ දැනටමත් එය 6000 වාරයකට වඩා භාවිතා කර ඇත, නමුත් අවධානයට ලක් නොවී ඇති එක් පහසු විශේෂාංගයකි ව්යුහාත්මක ඉඟි, මේ වගේ දෙයක් පෙනෙන්නේ:

අසනීප SQL විමසුම් සඳහා වට්ටෝරු

ඒවාට සවන් දෙන්න, එවිට ඔබේ ඉල්ලීම් "සිනිඳු සහ සිනිඳු බවට පත් වනු ඇත." 🙂

නමුත් බැරෑරුම් ලෙස, ඉල්ලීමක් මන්දගාමී සහ සම්පත්-කුසගින්න කරන බොහෝ තත්වයන් සාමාන්‍ය වන අතර සැලැස්මේ ව්‍යුහය සහ දත්ත මගින් හඳුනාගත හැක.

මෙම අවස්ථාවෙහිදී, සෑම සංවර්ධකයෙකුටම ඔහුගේ අත්දැකීම් මත පමණක් විශ්වාසය තබමින් තමාගේම ප්‍රශස්තකරණ විකල්පයක් සෙවීමට අවශ්‍ය නොවේ - අපට ඔහුට මෙහි සිදුවන්නේ කුමක්ද, හේතුව කුමක් විය හැකිද, සහ විසඳුමකට පිවිසෙන්නේ කෙසේද යන්න. ඒක තමයි අපි කළේ.

අසනීප SQL විමසුම් සඳහා වට්ටෝරු

මෙම අවස්ථා දෙස සමීපව බලමු - ඒවා නිර්වචනය කරන්නේ කෙසේද සහ ඒවා කුමන නිර්දේශයන්ට මඟ පාදයි.

මාතෘකාව තුළ වඩා හොඳින් ගිලී යාමට, ඔබට මුලින්ම අනුරූප කොටසට සවන් දිය හැකිය PGConf.Russia 2020 හි මගේ වාර්තාව, පසුව පමණක් එක් එක් උදාහරණයේ සවිස්තරාත්මක විශ්ලේෂණයකට යන්න:

#1: දර්ශක "අඩු වර්ග කිරීම"

මතුවන විට

"LLC Kolokolchik" සේවාදායකයා සඳහා නවතම ඉන්වොයිසිය පෙන්වන්න.

හඳුනා ගන්නේ කෙසේද

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

නිර්දේශ

භාවිතා කරන ලද දර්ශකය වර්ග කිරීමේ ක්ෂේත්ර සමඟ පුළුල් කරන්න.

උදාහරණ:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 1 -- отбор по конкретной связи
ORDER BY
  pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;

අසනීප SQL විමසුම් සඳහා වට්ටෝරු
[විස්තර කරන්න.tensor.ru බලන්න]

දර්ශකයෙන් වාර්තා 100 කට වඩා අඩු කර ඇති බව ඔබට වහාම දැකිය හැකිය, පසුව ඒවා සියල්ලම වර්ග කර ඇති අතර පසුව එකම එක ඉතිරි විය.

නිවැරදි කිරීම:

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

අසනීප SQL විමසුම් සඳහා වට්ටෝරු
[විස්තර කරන්න.tensor.ru බලන්න]

එවැනි ප්‍රාථමික නියැදියක පවා - 8.5 ගුණයකින් වේගවත් සහ 33 ගුණයකින් අඩු කියවීම්. එක් එක් අගය සඳහා ඔබට ඇති "කරුණු" වැඩි වන තරමට බලපෑම වඩාත් පැහැදිලිය fk.

එවැනි දර්ශකයක් වෙනත් විමසුම් සඳහා පෙරට වඩා නරක නොවන “උපසර්ග” දර්ශකයක් ලෙස ක්‍රියා කරන බව මම සටහන් කරමි. fk, එහිදී වර්ග කරන්න pk එහි නොතිබූ අතර නැත (ඔබට මේ ගැන වැඩිදුර කියවිය හැකිය අකාර්යක්ෂම දර්ශක සොයා ගැනීම ගැන මගේ ලිපියේ) ඇතුළුව, එය සාමාන්ය ලබා දෙනු ඇත පැහැදිලි විදේශ යතුරු සහාය මෙම ක්ෂේත්රයේ.

#2: දර්ශක ඡේදනය (BitmapAnd)

මතුවන විට

"NAO Buttercup" වෙනුවෙන් අවසන් කරන ලද "LLC Kolokolchik" සේවාදායකයා සඳහා සියලුම ගිවිසුම් පෙන්වන්න.

හඳුනා ගන්නේ කෙසේද

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

නිර්දේශ

නිර්මාණය සංයුක්ත දර්ශකය මුල් දෙකෙන්ම ක්ෂේත්‍ර මගින් හෝ පවතින ඒවායින් එකක් දෙවන ක්ෂේත්‍ර සමඟ පුළුල් කරන්න.

උදාහරණ:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  (fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре

අසනීප SQL විමසුම් සඳහා වට්ටෝරු
[විස්තර කරන්න.tensor.ru බලන්න]

නිවැරදි කිරීම:

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

අසනීප SQL විමසුම් සඳහා වට්ටෝරු
[විස්තර කරන්න.tensor.ru බලන්න]

බිට්මැප් හීප් ස්කෑන් ස්වයංක්‍රීයව බෙහෙවින් ඵලදායී වන බැවින් මෙහි ගෙවීම කුඩා වේ. නමුත් කොයි හැටි වුවත් 7 ගුණයකින් වේගවත් සහ 2.5 ගුණයකින් අඩු කියවීම්.

#3: දර්ශක ඒකාබද්ධ කරන්න (BitmapOr)

මතුවන විට

සැකසීම සඳහා පළමු පැරණිතම “අපට” හෝ නොපවරන ලද ඉල්ලීම් 20 පෙන්වන්න, ඔබේ ප්‍රමුඛතාවයෙන්.

හඳුනා ගන්නේ කෙසේද

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

නිර්දේශ

භාවිතා කරන්න සංගමය [සියල්ල] කොන්දේසි වල එක් එක් OR-බ්ලොක් සඳහා උප විමසීම් ඒකාබද්ධ කිරීමට.

උදාහරණ:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own;

CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой

SELECT
  *
FROM
  tbl
WHERE
  fk_own = 1 OR -- свои
  fk_own IS NULL -- ... или "ничьи"
ORDER BY
  pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;

අසනීප SQL විමසුම් සඳහා වට්ටෝරු
[විස්තර කරන්න.tensor.ru බලන්න]

නිවැරදි කිරීම:

(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own = 1 -- сначала "свои" 20
  ORDER BY
    pk
  LIMIT 20
)
UNION ALL
(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own IS NULL -- потом "ничьи" 20
  ORDER BY
    pk
  LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо

අසනීප SQL විමසුම් සඳහා වට්ටෝරු
[විස්තර කරන්න.tensor.ru බලන්න]

අවශ්‍ය වාර්තා 20ම පළමු කොටසේ වහාම ලැබුණු බව අපි ප්‍රයෝජනයට ගත්තෙමු, එබැවින් දෙවැන්න වඩාත් “මිල අධික” Bitmap Heap Scan සමඟ ක්‍රියාත්මක නොවීය - අවසානයේ 22x වේගවත්, 44x අඩු කියවීම්!

මෙම ප්‍රශස්තිකරණ ක්‍රමය පිළිබඳ වඩාත් සවිස්තරාත්මක කතාවකි නිශ්චිත උදාහරණ භාවිතා කරමින් ලිපි වලින් කියවිය හැක PostgreSQL Antipatterns: හානිකර JOINs සහ ORs и PostgreSQL Antipatterns: නම අනුව සෙවීමේ පුනරාවර්තන පිරිපහදු කිරීමේ කතාව, හෝ "ආපසු සහ ඉදිරියට ප්‍රශස්තකරණය".

සාමාන්යකරණය කළ අනුවාදය යතුරු කිහිපයක් මත පදනම්ව තෝරා ගැනීම ඇණවුම් කළේය (සහ const/NULL යුගලය පමණක් නොවේ) ලිපියේ සාකච්ඡා කෙරේ SQL HowTo: විමසුම තුළ කෙලින්ම ලූපයක් ලියන්න, නැතහොත් "මූලික තුන්-මාර්ග".

#4: අපි අනවශ්‍ය දේවල් ගොඩක් කියවනවා

මතුවන විට

රීතියක් ලෙස, දැනටමත් පවතින ඉල්ලීමකට "වෙනත් පෙරහනක් ඇමිණීමට" අවශ්ය විට එය පැන නගී.

“ඔබට එකම එකක් නැත, නමුත් මව්-මුතු බොත්තම් සමඟ? » චිත්රපටය "දියමන්ති ආම්"

උදාහරණයක් ලෙස, ඉහත කාර්යය වෙනස් කිරීම, ඒවායේ අරමුණ කුමක් වුවත්, සැකසීම සඳහා පළමු පැරණිතම "විවේචනාත්මක" ඉල්ලීම් 20 පෙන්වන්න.

හඳුනා ගන්නේ කෙසේද

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

නිර්දේශ

විශේෂිත [වැඩිදුර] සාදන්න WHERE කොන්දේසිය සහිත දර්ශකය හෝ දර්ශකයේ අතිරේක ක්ෂේත්ර ඇතුළත් කරන්න.

ඔබේ අරමුණු සඳහා පෙරහන තත්ත්වය "ස්ථිතික" නම් - එනම් ප්‍රසාරණය අදහස් නොකරයි අනාගතයේ අගයන් ලැයිස්තුව - WHERE දර්ශකයක් භාවිතා කිරීම වඩා හොඳය. විවිධ boolean/enum තත්ව මෙම කාණ්ඩයට හොඳින් ගැලපේ.

පෙරීමේ තත්ත්වය නම් විවිධ අර්ථයන් ගත හැකිය, එවිට මෙම ක්ෂේත්‍ර සමඟ දර්ශකය පුළුල් කිරීම වඩා හොඳය - ඉහත BitmapAnd සමඟ තත්වයේ දී මෙන්.

උදාහරණ:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"

CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);

SELECT
  *
FROM
  tbl
WHERE
  critical
ORDER BY
  pk
LIMIT 20;

අසනීප SQL විමසුම් සඳහා වට්ටෝරු
[විස්තර කරන්න.tensor.ru බලන්න]

නිවැරදි කිරීම:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

අසනීප SQL විමසුම් සඳහා වට්ටෝරු
[විස්තර කරන්න.tensor.ru බලන්න]

ඔබට පෙනෙන පරිදි, පෙරහන සැලැස්මෙන් සම්පූර්ණයෙන්ම අතුරුදහන් වී ඇති අතර, ඉල්ලීම බවට පත් වී ඇත 5 ගුණයකින් වේගවත්.

#5: විරල වගුව

මතුවන විට

ඔබගේම කාර්ය සැකසුම් පෝලිමක් නිර්මාණය කිරීමට විවිධ උත්සාහයන්, මේසය මත වාර්තා විශාල යාවත්කාලීන කිරීම් / මකාදැමීම් විශාල සංඛ්යාවක් "මියගිය" වාර්තා විශාල සංඛ්යාවක් තත්ත්වයට හේතු වන විට.

හඳුනා ගන්නේ කෙසේද

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

නිර්දේශ

නිතිපතා අතින් සිදු කරන්න රික්තකය [සම්පූර්ණ] හෝ ප්රමාණවත් තරම් නිරන්තර පුහුණුවක් ලබා ගැනීම autovacuum ඇතුළුව එහි පරාමිතීන් මනාව සකස් කිරීමෙන් නිශ්චිත වගුවක් සඳහා.

බොහෝ අවස්ථාවන්හිදී, එවැනි ගැටළු ඇති වන්නේ සාකච්ඡා කර ඇති පරිදි ව්‍යාපාරික තර්කයෙන් ඇමතීමේදී දුර්වල විමසුම් සංයුතිය හේතුවෙනි PostgreSQL Antipatterns: "මළවුන්ගේ" කණ්ඩායම් සමඟ සටන් කිරීම.

නමුත් VACUUM FULL පවා සැමවිටම උදව් නොකරන බව ඔබ තේරුම් ගත යුතුය. එවැනි අවස්ථාවන් සඳහා, ලිපියෙන් ඇල්ගොරිතම සමඟ ඔබ හුරුපුරුදු වීම වටී DBA: VACUUM අසමත් වූ විට, අපි මේසය අතින් පිරිසිදු කරමු.

#6: දර්ශකයේ "මැද" සිට කියවීම

මතුවන විට

අපි ටිකක් කියවා ඇති බවත්, සෑම දෙයක්ම සුචිගත කර ඇති බවත්, අපි කිසිවෙකු වැඩිපුර පෙරහන් නොකළ බවත් පෙනේ - නමුත් තවමත් අපි කැමති ප්‍රමාණයට වඩා සැලකිය යුතු පිටු ප්‍රමාණයක් කියවමු.

හඳුනා ගන්නේ කෙසේද

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

නිර්දේශ

භාවිතා කරන ලද දර්ශකයේ ව්‍යුහය සහ විමසුමේ දක්වා ඇති ප්‍රධාන ක්ෂේත්‍ර දෙස සමීපව බලන්න - බොහෝ විට දර්ශකයේ කොටසක් සකසා නැත. බොහෝ විට ඔබට සමාන දර්ශකයක් නිර්මාණය කිරීමට සිදුවනු ඇත, නමුත් උපසර්ග ක්ෂේත්ර නොමැතිව හෝ ඔවුන්ගේ වටිනාකම් නැවත නැවත කිරීමට ඉගෙන ගන්න.

උදාහරණ:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;

අසනීප SQL විමසුම් සඳහා වට්ටෝරු
[විස්තර කරන්න.tensor.ru බලන්න]

දර්ශකයට අනුව පවා සියල්ල හොඳින් ඇති බව පෙනේ, නමුත් එය කෙසේ හෝ සැක සහිතයි - කියවන ලද වාර්තා 20 න් එක් එක් දත්ත සඳහා, අපට දත්ත පිටු 4 ක් අඩු කිරීමට සිදු විය, වාර්තාවකට 32KB - එය නිර්භීත නොවේ ද? සහ දර්ශක නම tbl_fk_org_fk_cli_idx සිතුවිලි අවුස්සන.

නිවැරදි කිරීම:

CREATE INDEX ON tbl(fk_cli);

අසනීප SQL විමසුම් සඳහා වට්ටෝරු
[විස්තර කරන්න.tensor.ru බලන්න]

හදිසියේම - 10 ගුණයක් වේගවත්, සහ කියවීමට 4 ගුණයක් අඩුය!

දර්ශකවල අකාර්යක්ෂම භාවිතය පිළිබඳ වෙනත් උදාහරණ ලිපියෙහි දැකිය හැකිය DBA: නිෂ්ඵල දර්ශක සොයා ගැනීම.

#7: CTE × CTE

මතුවන විට

ඉල්ලීම පරිදි ලකුණු "මේද" CTE විවිධ වගු වලින්, පසුව ඔවුන් අතර එය කිරීමට තීරණය කළා JOIN.

මෙම නඩුව v12 ට අඩු අනුවාද හෝ ඉල්ලීම් සඳහා අදාළ වේ WITH MATERIALIZED.

හඳුනා ගන්නේ කෙසේද

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

නිර්දේශ

ඉල්ලීම ප්රවේශමෙන් විශ්ලේෂණය කරන්න - සහ මෙතැනට CTE අවශ්‍යද?? එසේ නම්, එසේ නම් hstore/json හි "ශබ්දකෝෂය" යොදන්න විස්තර කර ඇති ආකෘතියට අනුව PostgreSQL Antipatterns: අපි ශබ්දකෝෂයකින් බර JOIN පහර දෙමු.

#8: තැටියට මාරු කරන්න (තාවකාලික ලියා ඇත)

මතුවන විට

වාර්තා විශාල සංඛ්‍යාවක් එක් වරක් සැකසීම (වර්ග කිරීම හෝ අනන්‍යකරණය) මේ සඳහා වෙන් කර ඇති මතකයට නොගැලපේ.

හඳුනා ගන්නේ කෙසේද

-> *
   && temp written > 0

නිර්දේශ

මෙහෙයුම මගින් භාවිතා කරන මතක ප්රමාණය පරාමිතියෙහි නිශ්චිත අගය ඉක්මවා නොගියහොත් වැඩ_මෙම්, එය නිවැරදි කිරීම වටී. ඔබට වහාම සෑම කෙනෙකුටම වින්‍යාසගත කළ හැකිය, නැතහොත් ඔබට එය කළ හැකිය SET [LOCAL] නිශ්චිත ඉල්ලීමක්/ගනුදෙනුවක් සඳහා.

උදාහරණ:

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

අසනීප SQL විමසුම් සඳහා වට්ටෝරු
[විස්තර කරන්න.tensor.ru බලන්න]

නිවැරදි කිරීම:

SET work_mem = '128MB'; -- перед выполнением запроса

අසනීප SQL විමසුම් සඳහා වට්ටෝරු
[විස්තර කරන්න.tensor.ru බලන්න]

පැහැදිලි හේතු නිසා, මතකය පමණක් භාවිතා කරන්නේ නම් සහ තැටිය නොවේ නම්, විමසුම වඩා වේගයෙන් ක්රියාත්මක වේ. ඒ සමගම, HDD වෙතින් පැටවීමේ කොටසක් ද ඉවත් කරනු ලැබේ.

නමුත් ඔබට සෑම විටම බොහෝ මතකයන් වෙන් කිරීමට නොහැකි බව ඔබ තේරුම් ගත යුතුය - සෑම කෙනෙකුටම ප්‍රමාණවත් නොවනු ඇත.

#9: අදාළ නොවන සංඛ්‍යාලේඛන

මතුවන විට

ඔවුන් එකවරම දත්ත සමුදායට බොහෝ දේ වත් කළ නමුත් එය ඉවත් කිරීමට කාලය නොතිබුණි ANALYZE.

හඳුනා ගන්නේ කෙසේද

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

නිර්දේශ

එය ඉටු කරන්න ANALYZE.

මෙම තත්වය වඩාත් විස්තරාත්මකව විස්තර කෙරේ PostgreSQL Antipatterns: සංඛ්‍යාලේඛන සියල්ල වේ.

#10: "යමක් වැරදී ඇත"

මතුවන විට

තරඟකාරී ඉල්ලීමක් මගින් පනවන ලද අගුලක් සඳහා රැඳී සිටීමක් හෝ ප්‍රමාණවත් නොවන CPU/hypervisor දෘඪාංග සම්පත් තිබුණි.

හඳුනා ගන්නේ කෙසේද

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

නිර්දේශ

බාහිර භාවිතා කරන්න නිරීක්ෂණ පද්ධතිය අවහිර කිරීම හෝ අසාමාන්ය සම්පත් පරිභෝජනය සඳහා සේවාදායකය. සේවාදායකයන් සිය ගණනක් සඳහා මෙම ක්‍රියාවලිය සංවිධානය කිරීමේ අපගේ අනුවාදය ගැන අපි දැනටමත් කතා කර ඇත්තෙමු මෙහි и මෙහි.

අසනීප SQL විමසුම් සඳහා වට්ටෝරු
අසනීප SQL විමසුම් සඳහා වට්ටෝරු

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

අදහස් එක් කරන්න