ለታመመ SQL መጠይቆች የምግብ አዘገጃጀት መመሪያዎች

ከወራት በፊት አስታወቀን። አብራራ.tensor.ru - የህዝብ የጥያቄ ዕቅዶችን ለመተንተን እና ለመመልከት አገልግሎት ወደ PostgreSQL

ከዚያን ጊዜ ጀምሮ ከ6000 ጊዜ በላይ ተጠቀምክበት፣ ነገር ግን ከጠቃሚ ባህሪያቱ አንዱ ሳይስተዋል ሊሆን ይችላል መዋቅራዊ ፍንጮችይህን የሚመስል ነገር፡-

እነሱን ያዳምጡ እና ጥያቄዎችዎ "ሐር ለስላሳ ይሆናሉ"። 🙂

ነገር ግን በቁም ነገር፣ ጥያቄን ከሀብት አንፃር አዝጋሚ እና “ሆዳም” የሚያደርጉ ብዙ ሁኔታዎች፣ የተለመዱ ናቸው እና በእቅዱ መዋቅር እና መረጃ ሊታወቁ ይችላሉ.

በዚህ ሁኔታ, እያንዳንዱ ግለሰብ ገንቢ በራሱ የማመቻቸት አማራጭ መፈለግ አይኖርበትም, በራሱ ልምድ ላይ ብቻ ተመርኩዞ - እዚህ ምን እየተፈጠረ እንዳለ ልንነግረው እንችላለን, ምክንያቱ ምን ሊሆን ይችላል, እና እንዴት መፍትሄ ማምጣት እንደሚቻል. ያደረግነው ነው።

እነዚህን ጉዳዮች በዝርዝር እንመልከታቸው - እንዴት እንደተገለጹ እና ወደ ምን ምክሮች እንደሚመሩ።

በርዕሱ ላይ ለተሻለ ጥምቀት፣ መጀመሪያ ከ ተጓዳኝ ብሎክ ማዳመጥ ይችላሉ። የእኔ ዘገባ በ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;

[ማብራሪያውን ይመልከቱ.tensor.ru]

ወዲያውኑ ከ 100 በላይ መዝገቦች በመረጃ ጠቋሚው እንደተቀነሱ ማስተዋል ይችላሉ, ከዚያም ሁሉም ተስተካክለው ነበር, ከዚያም ብቸኛው ቀርቷል.

እኛ እናስተካክላለን:

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

[ማብራሪያውን ይመልከቱ.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); -- отбор по конкретной паре

[ማብራሪያውን ይመልከቱ.tensor.ru]

እኛ እናስተካክላለን:

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

[ማብራሪያውን ይመልከቱ.tensor.ru]

Bitmap Heap Scan በራሱ በጣም ውጤታማ ስለሆነ እዚህ ትርፉ ትንሽ ነው። ግን ለማንኛውም 7x ፈጣን እና 2.5x ያነሱ ንባብ.

#3፡ ኢንዴክሶችን በማጣመር (BitmapOr)

ሲነሳ

የመጀመሪያዎቹን 20 በጣም የቆዩ "የራስ" ወይም ያልተመደቡ የማስኬጃ ጥያቄዎችን ያሳዩ፣ ቅድሚያ በመስጠት።

እንዴት መለየት እንደሚቻል

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

ምክሮች

ተጠቀም UNION [ሁሉም] ለእያንዳንዱ ሁኔታ 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;

[ማብራሪያውን ይመልከቱ.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, больше и не надо

[ማብራሪያውን ይመልከቱ.tensor.ru]

በመጀመሪያው ብሎክ ሁሉም 20 አስፈላጊ መዝገቦች ወዲያውኑ መገኘታቸውን ተጠቅመንበታል ፣ ስለሆነም ሁለተኛው ፣ “ውድ በሆነው” የቢትማፕ ክምር ቅኝት እንኳን አልተገደለም - በውጤቱም ። 22x ፈጣን፣ 44x ያነሱ ንባብ!

ስለዚህ የማመቻቸት ዘዴ የበለጠ ዝርዝር ታሪክ በተጨባጭ ምሳሌዎች ላይ በጽሁፎች ውስጥ ሊነበብ ይችላል PostgreSQL Antipatterns፡ ጎጂ JOINs እና ORs и PostgreSQL Antipatterns፡ የፍለጋ ተደጋጋሚ ማሻሻያ ታሪክ፣ ወይም "ወደ ኋላ እና ወደ ፊት ማመቻቸት".

አጠቃላይ ስሪት በብዙ ቁልፎች የታዘዘ ምርጫ (እና ለኮንስት / NULL ጥንድ ብቻ አይደለም) በአንቀጹ ውስጥ ተብራርቷል SQL HowTo፡ በጥያቄው ውስጥ የተወሰነ ጊዜ-loop በቀጥታ ይፃፉ ወይም “አንደኛ ደረጃ ባለሶስት መንገድ”.

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

[ማብራሪያውን ይመልከቱ.tensor.ru]

እኛ እናስተካክላለን:

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

[ማብራሪያውን ይመልከቱ.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 Antipatterns፡ የ"ሙታን" ጭፍሮችን መዋጋት.

ነገር ግን VACUUM FULL እንኳን ሁልጊዜ መርዳት እንደማይችል መረዳት አለብን። ለእንደዚህ አይነት ጉዳዮች, ከጽሁፉ ውስጥ በአልጎሪዝም እራስዎን ማወቅ አለብዎት. ዲቢኤ: 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;

[ማብራሪያውን ይመልከቱ.tensor.ru]

በመረጃ ጠቋሚው ውስጥ እንኳን ሁሉም ነገር ጥሩ ይመስላል ፣ ግን በሆነ መንገድ አጠራጣሪ ነው - ለእያንዳንዱ 20 መዛግብት ፣ 4 ገጾች ውሂብ መቀነስ ነበረበት ፣ በአንድ መዝገብ 32 ኪባ - ድፍረት አይደለም? አዎ እና የመረጃ ጠቋሚ ስም tbl_fk_org_fk_cli_idx ወደ አስተሳሰብ ይመራል.

እኛ እናስተካክላለን:

CREATE INDEX ON tbl(fk_cli);

[ማብራሪያውን ይመልከቱ.tensor.ru]

በድንገት - 10 ጊዜ ፈጣን እና ለማንበብ 4 ጊዜ ያነሰ!

ኢንዴክሶችን ውጤታማ ባልሆነ አጠቃቀም ላይ ተጨማሪ ምሳሌዎችን ለማግኘት ጽሑፉን ይመልከቱ DBA: የማይጠቅሙ ኢንዴክሶችን ያግኙ.

#7፡ CTE × ሲቲ

ሲነሳ

በጥያቄ "ስብ" CTE አስቆጥሯል። ከተለያዩ ጠረጴዛዎች, እና ከዚያም በመካከላቸው ለማድረግ ወሰነ JOIN.

ጉዳዩ ከv12 በታች ለሆኑ ስሪቶች ወይም በጥያቄዎች ላይ ተገቢ ነው። WITH MATERIALIZED.

እንዴት መለየት እንደሚቻል

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

ምክሮች

ጥያቄውን በጥንቃቄ ይተንትኑ እዚህ ሁሉም CTE ዎች ያስፈልጋሉ።? አዎ ከሆነ፣ እንግዲያውስ በ hstore/json ውስጥ "መዝገበ-ቃላት" ተግብር በተገለጸው ሞዴል መሠረት PostgreSQL Antipatterns፡ መዝገበ ቃላት ከባድ ይቀላቀሉ.

#8፡ ወደ ዲስክ መለዋወጥ (የሙቀት መጠን ተጽፏል)

ሲነሳ

ብዙ ቁጥር ያላቸውን መዝገቦች የአንድ ጊዜ ሂደት (መደርደር ወይም ልዩ ማድረግ) ለዚህ የተመደበው ማህደረ ትውስታ ውስጥ አይገቡም።

እንዴት መለየት እንደሚቻል

-> *
   && temp written > 0

ምክሮች

በቀዶ ጥገናው ጥቅም ላይ የሚውለው የማህደረ ትውስታ መጠን የመለኪያውን ስብስብ ዋጋ በእጅጉ የማይበልጥ ከሆነ የስራ_ሜም፣ መስተካከል አለበት። ወዲያውኑ ለሁሉም ሰው ማዋቀር ይችላሉ ፣ ወይም ማለፍ ይችላሉ። SET [LOCAL] ለአንድ የተወሰነ ጥያቄ / ግብይት.

ለምሳሌ:

SHOW work_mem;
-- "16MB"

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

[ማብራሪያውን ይመልከቱ.tensor.ru]

እኛ እናስተካክላለን:

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

[ማብራሪያውን ይመልከቱ.tensor.ru]

ግልጽ በሆኑ ምክንያቶች, ማህደረ ትውስታ ብቻ ጥቅም ላይ የሚውል እና ዲስክ ካልሆነ, ጥያቄው በጣም ፈጣን ይሆናል. በተመሳሳይ ጊዜ የጭነቱ ክፍል ከኤችዲዲ ይወገዳል.

ግን ብዙ ማህደረ ትውስታን መመደብ ሁል ጊዜም እንደማይሰራ መረዳት ያስፈልግዎታል - በቀላሉ ለሁሉም ሰው በቂ አይሆንም።

#9: ተዛማጅነት የሌላቸው ስታቲስቲክስ

ሲነሳ

በአንድ ጊዜ ብዙ ወደ መሠረቱ ፈሰሰ, ነገር ግን እሱን ለማባረር ጊዜ አልነበራቸውም ANALYZE.

እንዴት መለየት እንደሚቻል

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

ምክሮች

ተመሳሳይ ወጪ ANALYZE.

ይህ ሁኔታ በበለጠ ዝርዝር ውስጥ ተገልጿል PostgreSQL Antipatterns፡ ስታቲስቲክስ የሁሉም ነገር ራስ ነው።.

#10: "አንድ ችግር ተፈጥሯል"

ሲነሳ

ተፎካካሪ ጥያቄን የሚጠብቅ መቆለፊያ ነበር፣ ወይም በቂ የሲፒዩ/ከፍተኛ የሃርድዌር ግብዓቶች አልነበሩም።

እንዴት መለየት እንደሚቻል

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

ምክሮች

ውጫዊ ተጠቀም የክትትል ስርዓት ለማገድ ወይም ያልተለመደ የንብረት ፍጆታ አገልጋይ. ይህንን ሂደት በመቶዎች ለሚቆጠሩ አገልጋዮች ስለማደራጀት ስሪታችን አስቀድመን ተናግረናል። እዚህ и እዚህ.

ምንጭ: hab.com