બીમાર SQL પ્રશ્નો માટે વાનગીઓ

મહિનાઓ પેહ્લા અમે જાહેરાત કરી explain.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;

[explan.tensor.ru પર જુઓ]

તમે તરત જ નોંધ કરી શકો છો કે અનુક્રમણિકામાંથી 100 થી વધુ રેકોર્ડ્સ બાદ કરવામાં આવ્યા હતા, જે પછી બધા સૉર્ટ કરવામાં આવ્યા હતા, અને પછી માત્ર એક જ બાકી હતો.

સુધારી રહ્યું છે:

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

[explan.tensor.ru પર જુઓ]

આવા આદિમ નમૂના પર પણ - 8.5 ગણી ઝડપી અને 33 ગણી ઓછી વાંચન. દરેક મૂલ્ય માટે તમારી પાસે જેટલી વધુ "તથ્યો" હશે, તેટલી વધુ સ્પષ્ટ અસર fk.

હું નોંધું છું કે આવી અનુક્રમણિકા "ઉપસર્ગ" અનુક્રમણિકા તરીકે કામ કરશે જે અન્ય પ્રશ્નો માટે પહેલાં કરતાં વધુ ખરાબ નથી. fk, જ્યાં દ્વારા સૉર્ટ કરો pk ત્યાં ન હતું અને ત્યાં નથી (તમે આ વિશે વધુ વાંચી શકો છો બિનઅસરકારક સૂચકાંકો શોધવા વિશેના મારા લેખમાં). સહિત, તે સામાન્ય પ્રદાન કરશે સ્પષ્ટ વિદેશી કી આધાર આ ક્ષેત્ર પર.

#2: અનુક્રમણિકા આંતરછેદ (બીટમેપઅને)

જ્યારે ઉદભવે છે

“NAO બટરકપ” વતી સમાપ્ત થયેલ ક્લાયન્ટ “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); -- отбор по конкретной паре

[explan.tensor.ru પર જુઓ]

સુધારી રહ્યું છે:

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

[explan.tensor.ru પર જુઓ]

અહીં ચૂકવણી ઓછી છે, કારણ કે બીટમેપ હીપ સ્કેન તેના પોતાના પર ખૂબ અસરકારક છે. પરંતુ કોઈપણ રીતે 7 ગણી ઝડપી અને 2.5 ગણી ઓછી વાંચન.

#3: અનુક્રમણિકાઓને મર્જ કરો (બીટમેપઓર)

જ્યારે ઉદભવે છે

પ્રથમ 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;

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

[explan.tensor.ru પર જુઓ]

અમે એ હકીકતનો લાભ લીધો હતો કે પ્રથમ બ્લોકમાં તમામ 20 જરૂરી રેકોર્ડ્સ તરત જ પ્રાપ્ત થઈ ગયા હતા, તેથી વધુ "ખર્ચાળ" બીટમેપ હીપ સ્કેન સાથેનો બીજો એક પણ અમલમાં આવ્યો ન હતો - અંતે 22x ઝડપી, 44x ઓછા વાંચન!

આ ઓપ્ટિમાઇઝેશન પદ્ધતિ વિશે વધુ વિગતવાર વાર્તા ચોક્કસ ઉદાહરણોનો ઉપયોગ કરીને લેખોમાં વાંચી શકાય છે PostgreSQL એન્ટિપેટર્ન: હાનિકારક જોડાઓ અને ORs и પોસ્ટગ્રેએસક્યુએલ એન્ટિપેટર્ન: નામ દ્વારા શોધના પુનરાવર્તિત શુદ્ધિકરણની વાર્તા, અથવા "આગળ અને આગળ ઑપ્ટિમાઇઝ કરવું".

સામાન્યકૃત સંસ્કરણ અનેક કીના આધારે પસંદગીનો આદેશ આપ્યો (અને માત્ર 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 ઇન્ડેક્સનો ઉપયોગ કરવો વધુ સારું છે. વિવિધ બુલિયન/એનમ સ્ટેટસ આ કેટેગરીમાં સારી રીતે બંધબેસે છે.

જો ફિલ્ટરિંગ સ્થિતિ વિવિધ અર્થો લઈ શકે છે, તો પછી આ ક્ષેત્રો સાથે ઇન્ડેક્સને વિસ્તૃત કરવું વધુ સારું છે - જેમ કે Bitmap અને ઉપરની પરિસ્થિતિમાં.

ઉદાહરણ:

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;

[explan.tensor.ru પર જુઓ]

સુધારી રહ્યું છે:

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

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

ભલામણો

નિયમિતપણે જાતે હાથ ધરે છે વેક્યુમ [પૂર્ણ] અથવા પર્યાપ્ત વારંવાર તાલીમ પ્રાપ્ત કરો ઓટોવેક્યુમ તેના પરિમાણોને ફાઇન-ટ્યુનિંગ કરીને, સહિત ચોક્કસ ટેબલ માટે.

મોટા ભાગના કિસ્સાઓમાં, આવી સમસ્યાઓ નબળી ક્વેરી કમ્પોઝિશનને કારણે થાય છે જ્યારે બિઝનેસ લોજિકમાંથી કૉલ કરવામાં આવે છે જેમ કે પોસ્ટગ્રેએસક્યુએલ એન્ટિપેટર્ન: "મૃત" ની લડાઈ.

પરંતુ તમારે એ સમજવાની જરૂર છે કે 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;

[explan.tensor.ru પર જુઓ]

અનુક્રમણિકા મુજબ પણ બધું સારું લાગે છે, પરંતુ તે કોઈક રીતે શંકાસ્પદ છે - વાંચેલા 20 રેકોર્ડ્સમાંથી દરેક માટે, અમારે રેકોર્ડ દીઠ 4KB ડેટાના 32 પૃષ્ઠોને બાદ કરવો પડ્યો - શું તે બોલ્ડ નથી? અને ઇન્ડેક્સ નામ tbl_fk_org_fk_cli_idx વિચારપ્રેરક.

સુધારી રહ્યું છે:

CREATE INDEX ON tbl(fk_cli);

[explan.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 એન્ટિપેટર્ન: ડિક્શનરી હિટ હેવી જોઇન.

#8: ડિસ્ક પર સ્વેપ (ટેમ્પલ લખેલું)

જ્યારે ઉદભવે છે

મોટી સંખ્યામાં રેકોર્ડ્સનું વન-ટાઇમ પ્રોસેસિંગ (સૉર્ટિંગ અથવા યુનિકાઇઝેશન) આ માટે ફાળવવામાં આવેલી મેમરીમાં ફિટ થતું નથી.

કેવી રીતે ઓળખવું

-> *
   && temp written > 0

ભલામણો

જો ઓપરેશન દ્વારા ઉપયોગમાં લેવાતી મેમરીની માત્રા પેરામીટરના ઉલ્લેખિત મૂલ્ય કરતાં મોટા પ્રમાણમાં વધી નથી વર્ક_મેમ, તે સુધારવા વર્થ છે. તમે તરત જ દરેક માટે રૂપરેખામાં કરી શકો છો, અથવા તમે કરી શકો છો SET [LOCAL] ચોક્કસ વિનંતી/વ્યવહાર માટે.

ઉદાહરણ:

SHOW work_mem;
-- "16MB"

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

[explan.tensor.ru પર જુઓ]

સુધારી રહ્યું છે:

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

[explan.tensor.ru પર જુઓ]

સ્પષ્ટ કારણોસર, જો ફક્ત મેમરીનો ઉપયોગ કરવામાં આવે છે અને ડિસ્કનો ઉપયોગ થતો નથી, તો ક્વેરી વધુ ઝડપથી ચલાવવામાં આવશે. તે જ સમયે, એચડીડીમાંથી લોડનો ભાગ પણ દૂર કરવામાં આવે છે.

પરંતુ તમારે એ સમજવાની જરૂર છે કે તમે હંમેશા ઘણી બધી અને ઘણી બધી મેમરી ફાળવી શકશો નહીં - તે દરેક માટે પૂરતું નથી.

#9: અપ્રસ્તુત આંકડા

જ્યારે ઉદભવે છે

તેઓએ એક જ સમયે ડેટાબેઝમાં ઘણું બધું રેડ્યું, પરંતુ તેને દૂર કરવાનો સમય નહોતો ANALYZE.

કેવી રીતે ઓળખવું

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

ભલામણો

તેને બહાર લઈ જાઓ ANALYZE.

આ પરિસ્થિતિમાં વધુ વિગતવાર વર્ણવેલ છે PostgreSQL એન્ટિપેટર્ન: આંકડા બધું જ છે.

#10: "કંઈક ખોટું થયું"

જ્યારે ઉદભવે છે

સ્પર્ધાત્મક વિનંતિ દ્વારા લાદવામાં આવેલ લૉકની રાહ જોવાઈ રહી હતી, અથવા ત્યાં અપૂરતા CPU/હાયપરવાઈઝર હાર્ડવેર સંસાધનો હતા.

કેવી રીતે ઓળખવું

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

ભલામણો

બાહ્ય ઉપયોગ કરો મોનીટરીંગ સિસ્ટમ અવરોધિત અથવા અસામાન્ય સંસાધન વપરાશ માટે સર્વર. અમે સેંકડો સર્વર્સ માટે આ પ્રક્રિયાને ગોઠવવાના અમારા સંસ્કરણ વિશે પહેલેથી જ વાત કરી છે અહીં и અહીં.

સોર્સ: www.habr.com