Ndimagwiritsa ntchito funso la SQL lomwe limathandiza kuwunika kwa PostgreSQL (ndikutsimikiza kuti funso lofananalo likhoza kulembedwa kwa ma DBMS ena ambiri)
SQL funso kuti muyerekeze kukula kwa magulu a TOP-MEDIUM-TAIL
SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
SELECT CASE WHEN rn <= 20 THEN tt_percent ELSE 0 END AS time_top,
CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
CASE WHEN rn > 800 THEN tt_percent ELSE 0 END AS time_tail
FROM (
SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
FROM pg_stat_statements
ORDER BY total_time DESC
) AS t
)
AS ts
Zotsatira za funsoli ndi mizati itatu, iliyonse ili ndi kuchuluka kwa nthawi yomwe zimafunika kuti tiyankhe mafunso agululi. Mkati mwa pempholi muli manambala awiri (kwa ine ndi 20 ndi 800) omwe amalekanitsa zopempha kuchokera ku gulu lina kuchokera ku lina.
Chithunzichi chikuwonetsa kuti gawo la zopempha za TOP latsika kwambiri, koma "anthu apakati" awonjezeka.
Poyamba, zopempha za TOP zidaphatikizapo zolakwika zowonekera. M'kupita kwa nthawi, matenda a ubwana adatha, gawo la zopempha za TOP linachepa, ndipo kuyesetsa kwakukulu kunayenera kuchitidwa kuti afulumizitse zopempha zovuta.
Kuti tipeze malemba a zopempha timagwiritsa ntchito zotsatirazi
SELECT * FROM (
SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
FROM pg_stat_statements
ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800 -- TAIL
Kukonzanso dongosolo, mwachitsanzo, kukonzanso malingaliro a zidziwitso pogwiritsa ntchito meseji broker m'malo mofunsa nthawi ndi nthawi ku database.
Kuwonjezera kapena kusintha indexes
Kulembanso mafunso a ORM ku SQL yoyera
Kulembanso mfundo zaulesi zotsegula
Kusunga kudzera mu data denormalization. Mwachitsanzo, tili ndi kugwirizana kwa tebulo Kutumiza -> Invoice -> Pempho -> Ntchito. Ndiye kuti, kutumiza kulikonse kumalumikizidwa ndi pulogalamu kudzera pamagome ena. Kuti tisalumikizane ndi matebulo onse pa pempho lililonse, tidabwereza ulalo wa pempholo pa Delivery table.
Kusunga matebulo osasunthika okhala ndi mabuku ofotokozera komanso osasintha ma tebulo pamakumbukiro a pulogalamu.
Nthawi zina zosinthazo zidakhala kukonzanso kochititsa chidwi, koma zidapereka 5-10% yazinthu zamakina ndipo zidali zomveka. Mβkupita kwa nthaΕ΅i, utsiwo unakhala wawungβono ndi wocheperako, ndipo kukonzanso kwakukulu kunafunikira.
Kenako tinatembenukira ku gulu lachiwiri la zopempha - gulu la alimi apakati. Pali mafunso ena ambiri momwemo ndipo zikuwoneka kuti zingatenge nthawi yochuluka kuti tiwunike gulu lonselo. Komabe, mafunso ambiri adakhala osavuta kukhathamiritsa, ndipo mavuto ambiri amabwerezedwa kangapo mosiyanasiyana. Nazi zitsanzo za kukhathamiritsa komwe tidagwiritsa ntchito kumafunso ambiri ofanana ndipo gulu lililonse lamafunso okometsedwa lidatsitsa nkhokwe ndi 3-5%.
M'malo moyang'ana kukhalapo kwa zolemba pogwiritsa ntchito COUNT ndi kusanthula tebulo lonse, EXISTS inayamba kugwiritsidwa ntchito