நோய்வாய்ப்பட்ட SQL வினவல்களுக்கான ரெசிபிகள்

மாதங்களுக்கு முன்பு நாங்கள் அறிவித்தோம் விளக்கவும்.tensor.ru - பொது வினவல் திட்டங்களை பாகுபடுத்துவதற்கும் காட்சிப்படுத்துவதற்கும் சேவை PostgreSQL க்கு.

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

அவற்றைக் கேளுங்கள், உங்கள் கோரிக்கைகள் "மென்மையானதாக மாறும்". 🙂

ஆனால் தீவிரமாக, கோரிக்கையை மெதுவாக மற்றும் வளங்களின் அடிப்படையில் "பெருந்தீனி" செய்யும் பல சூழ்நிலைகள், வழக்கமானவை மற்றும் திட்டத்தின் கட்டமைப்பு மற்றும் தரவு மூலம் அங்கீகரிக்கப்படலாம்.

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

இந்த நிகழ்வுகளை இன்னும் விரிவாகப் பார்ப்போம் - அவை எவ்வாறு வரையறுக்கப்படுகின்றன மற்றும் அவை என்ன பரிந்துரைகளுக்கு வழிவகுக்கும்.

தலைப்பில் சிறந்த மூழ்குவதற்கு, நீங்கள் முதலில் தொடர்புடைய தொகுதியைக் கேட்கலாம் PGConf.Russia 2020 இல் எனது அறிக்கை, பின்னர் ஒவ்வொரு உதாரணத்தின் விரிவான பகுப்பாய்விற்குச் செல்லவும்:

#1: குறியீட்டு "குறைப்படுத்தல்"

எப்போது எழுகிறது

கிளையன்ட் "எல்எல்சி கோலோகோல்சிக்" க்கான கடைசி விலைப்பட்டியலைக் காட்டு.

எப்படி அடையாளம் காண்பது

-> 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;

[explain.tensor.ru ஐப் பார்க்கவும்]

100 க்கும் மேற்பட்ட பதிவுகள் குறியீட்டால் கழிக்கப்பட்டதை நீங்கள் உடனடியாக கவனிக்கலாம், பின்னர் அவை அனைத்தும் வரிசைப்படுத்தப்பட்டன, பின்னர் ஒன்று மட்டுமே எஞ்சியிருந்தது.

நாங்கள் சரிசெய்கிறோம்:

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

[explain.tensor.ru ஐப் பார்க்கவும்]

அத்தகைய பழமையான மாதிரியில் கூட - 8.5x வேகமாகவும், 33x குறைவான வாசிப்புகளும். விளைவு தெளிவாக இருக்கும், ஒவ்வொரு மதிப்புக்கும் அதிகமான "உண்மைகள்" இருக்கும். fk.

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

#2: குறியீட்டு குறுக்குவெட்டு (BitmapAnd)

எப்போது எழுகிறது

"NJSC Lyutik" சார்பாக முடிக்கப்பட்ட "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); -- отбор по конкретной паре

[explain.tensor.ru ஐப் பார்க்கவும்]

நாங்கள் சரிசெய்கிறோம்:

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

[explain.tensor.ru ஐப் பார்க்கவும்]

பிட்மேப் ஹீப் ஸ்கேன் தானே மிகவும் பயனுள்ளதாக இருப்பதால், இங்கே ஆதாயம் சிறியது. எப்படி இருந்தாலும் 7x வேகமாகவும், 2.5x குறைவான வாசிப்புகளும்.

#3: ஒருங்கிணைந்த குறியீடுகள் (BitmapOr)

எப்போது எழுகிறது

செயலாக்கத்திற்கான முதல் 20 பழமையான "சொந்த" அல்லது ஒதுக்கப்படாத கோரிக்கைகளை முதன்மையாகக் காட்டு.

எப்படி அடையாளம் காண்பது

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

பரிந்துரைகளை

பயன் யூனியன் [அனைத்து] நிபந்தனை அல்லது தொகுதிகள் ஒவ்வொன்றிற்கும் துணை வினவல்களை இணைக்க.

உதாரணம்:

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;

[explain.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, больше и не надо

[explain.tensor.ru ஐப் பார்க்கவும்]

தேவையான அனைத்து 20 பதிவுகளும் முதல் தொகுதியில் உடனடியாகப் பெறப்பட்டன என்ற உண்மையை நாங்கள் பயன்படுத்திக் கொண்டோம், எனவே இரண்டாவது, மிகவும் "விலையுயர்ந்த" பிட்மேப் ஹீப் ஸ்கேன் மூலம், செயல்படுத்தப்படவில்லை - இதன் விளைவாக 22 மடங்கு வேகமாக, 44 மடங்கு குறைவான வாசிப்பு!

இந்த தேர்வுமுறை முறையைப் பற்றிய விரிவான கதை உறுதியான எடுத்துக்காட்டுகளில் கட்டுரைகளில் படிக்கலாம் PostgreSQL எதிர்ப்பு வடிவங்கள்: தீங்கு விளைவிக்கும் சேர்ப்புகள் மற்றும் ORகள் и PostgreSQL எதிர்ப்பு வடிவங்கள்: பெயரின் மூலம் தேடலை மீண்டும் மீண்டும் மேம்படுத்துதல் அல்லது "முன்னும் பின்னுமாக மேம்படுத்துதல்".

பொதுவான பதிப்பு பல விசைகள் மூலம் தேர்வு செய்ய உத்தரவிட்டார் (மற்றும் const/NULL ஜோடிக்கு மட்டும் அல்ல) கட்டுரையில் விவாதிக்கப்படுகிறது SQL எப்படி: வினவலில் நேரடியாக ஒரு நேர வளையத்தை எழுதவும் அல்லது "எலிமெண்டரி த்ரீ-வே".

#4: நாங்கள் அதிகமாக படிக்கிறோம்

எப்போது எழுகிறது

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

"உங்களிடம் அதே இல்லை, ஆனால் முத்து பொத்தான்களுடன்? ' படம் "டயமண்ட் ஹேண்ட்"

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

எப்படி அடையாளம் காண்பது

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

பரிந்துரைகளை

சிறப்பு [மேலும்] உருவாக்கவும் WHERE விதியுடன் குறியீட்டு அல்லது குறியீட்டில் கூடுதல் புலங்களைச் சேர்க்கவும்.

உங்கள் பணிகளுக்கு வடிகட்டுதல் நிலை "நிலையானதாக" இருந்தால் - அதாவது விரிவாக்கம் சேர்க்கவில்லை எதிர்காலத்தில் மதிப்புகளின் பட்டியல் - WHERE குறியீட்டைப் பயன்படுத்துவது நல்லது. பல்வேறு பூலியன்/என்யூம் நிலைகள் இந்த வகைக்கு நன்கு பொருந்துகின்றன.

வடிகட்டுதல் நிலை என்றால் வெவ்வேறு மதிப்புகளை எடுக்க முடியும், இந்த புலங்களுடன் குறியீட்டை விரிவுபடுத்துவது நல்லது - 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;

[explain.tensor.ru ஐப் பார்க்கவும்]

நாங்கள் சரிசெய்கிறோம்:

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

[explain.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

பரிந்துரைகளை

கைமுறையாக தவறாமல் செயல்படுத்தவும் வெற்றிடம் [முழு] அல்லது போதுமான அளவு அடிக்கடி செயலாக்கத்தை அடையலாம் தன்னியக்க வெற்றிடம் அதன் அளவுருக்களை நன்றாகச் சரிசெய்வதன் மூலம் ஒரு குறிப்பிட்ட அட்டவணைக்கு.

பெரும்பாலான சந்தர்ப்பங்களில், வணிக தர்க்கத்தில் விவாதிக்கப்பட்டவை போன்ற மோசமான வினவல் தளவமைப்பினால் இத்தகைய சிக்கல்கள் ஏற்படுகின்றன. PostgreSQL ஆன்டிபேட்டர்ன்கள்: "இறந்தவர்களின்" சண்டைக் கூட்டங்கள்.

ஆனால் VACUUM FULL கூட எப்போதும் உதவ முடியாது என்பதை நாம் புரிந்து கொள்ள வேண்டும். இதுபோன்ற சந்தர்ப்பங்களில், கட்டுரையிலிருந்து வழிமுறைகளை நீங்கள் அறிந்திருக்க வேண்டும். DBA: வெற்றிடத்தை கடக்கும்போது, ​​மேசையை கைமுறையாக சுத்தம் செய்கிறோம்.

#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;

[explain.tensor.ru ஐப் பார்க்கவும்]

குறியீட்டின் அடிப்படையில் கூட எல்லாம் நன்றாக இருப்பதாகத் தெரிகிறது, ஆனால் எப்படியோ சந்தேகத்திற்குரியது - படித்த 20 பதிவுகளில் ஒவ்வொன்றிற்கும் 4 பக்க தரவுகளை கழிக்க வேண்டும், ஒரு பதிவுக்கு 32KB - இது தைரியமாக இல்லையா? ஆம் மற்றும் குறியீட்டு பெயர் tbl_fk_org_fk_cli_idx சிந்தனைக்கு வழிவகுக்கிறது.

நாங்கள் சரிசெய்கிறோம்:

CREATE INDEX ON tbl(fk_cli);

[explain.tensor.ru ஐப் பார்க்கவும்]

திடீரென்று - 10 மடங்கு வேகமாகவும், படிக்க 4 மடங்கு குறைவாகவும் இருக்கும்!

குறியீடுகளின் திறமையற்ற பயன்பாட்டின் கூடுதல் எடுத்துக்காட்டுகளுக்கு, கட்டுரையைப் பார்க்கவும் DBA: பயனற்ற குறியீடுகளைக் கண்டறியவும்.

#7: CTE × CTE

எப்போது எழுகிறது

கோரிக்கையில் "கொழுப்பு" CTE அடித்தார் வெவ்வேறு அட்டவணைகள் இருந்து, பின்னர் அவர்களுக்கு இடையே செய்ய முடிவு JOIN.

கேஸ் வி12க்குக் கீழே உள்ள பதிப்புகள் அல்லது கோரிக்கைகளுக்குப் பொருத்தமானது WITH MATERIALIZED.

எப்படி அடையாளம் காண்பது

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

பரிந்துரைகளை

கோரிக்கையை கவனமாக பகுப்பாய்வு செய்யுங்கள் CTE கள் இங்கு தேவைப்படுகின்றன? ஆம் எனில் hstore/json இல் "அகராதி" பயன்படுத்தவும் விவரிக்கப்பட்டுள்ள மாதிரியின் படி PostgreSQL ஆன்டிபேட்டர்ன்கள்: டிக்ஷனரி ஹிட் ஹெவி ஜாயின்.

#8: வட்டுக்கு மாற்றவும் (டெம்ப் எழுதப்பட்ட)

எப்போது எழுகிறது

அதிக எண்ணிக்கையிலான பதிவுகளின் ஒரு முறை செயலாக்கம் (வரிசைப்படுத்துதல் அல்லது தனித்துவமாக்குதல்) இதற்காக ஒதுக்கப்பட்ட நினைவகத்திற்கு பொருந்தாது.

எப்படி அடையாளம் காண்பது

-> *
   && temp written > 0

பரிந்துரைகளை

செயல்பாட்டின் மூலம் பயன்படுத்தப்படும் நினைவகத்தின் அளவு அளவுருவின் செட் மதிப்பை விட அதிகமாக இல்லை என்றால் வேலை_மேம், அதை சரி செய்ய வேண்டும். நீங்கள் உடனடியாக அனைவருக்கும் உள்ளமைவில் செய்யலாம், அல்லது உங்களால் முடியும் SET [LOCAL] ஒரு குறிப்பிட்ட கோரிக்கை/பரிவர்த்தனைக்கு.

உதாரணம்:

SHOW work_mem;
-- "16MB"

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

[explain.tensor.ru ஐப் பார்க்கவும்]

நாங்கள் சரிசெய்கிறோம்:

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

[explain.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 -- читали мало, но слишком долго

பரிந்துரைகளை

வெளிப்புறத்தைப் பயன்படுத்தவும் கண்காணிப்பு அமைப்பு தடுப்பு அல்லது அசாதாரண வள நுகர்வுக்கான சேவையகம். நூற்றுக்கணக்கான சேவையகங்களுக்கு இந்த செயல்முறையை ஒழுங்கமைப்பதற்கான எங்கள் பதிப்பைப் பற்றி நாங்கள் ஏற்கனவே பேசினோம். இங்கே и இங்கே.

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