Għaddew il-ġranet meta ma kellekx għalfejn tinkwieta dwar l-ottimizzazzjoni tal-prestazzjoni tad-database. Iż-żmien ma jieqafx. Kull intraprenditur teknoloġiku ġdid irid joħloq il-Facebook li jmiss, filwaqt li jipprova jiġbor id-dejta kollha li jistgħu jiksbu idejhom fuqha. In-negozji jeħtieġu din id-dejta biex iħarrġu aħjar mudelli li jgħinuhom jagħmlu l-flus. F'kundizzjonijiet bħal dawn, il-programmaturi jeħtieġ li joħolqu APIs li jippermettulhom jaħdmu malajr u b'mod affidabbli b'ammonti kbar ta 'informazzjoni.
Jekk kont ilek iddisinja l-backends tal-applikazzjoni jew tad-database għal kwalunkwe tul ta' żmien, probabbilment ktibt kodiċi biex tmexxi mistoqsijiet paġni. Per eżempju, bħal dan:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Il-mod kif inhi?
Imma jekk hekk għamilt il-paginazzjoni tiegħek, jiddispjaċini ngħid li ma għamiltx bl-aktar mod effiċjenti.
Trid toġġezzjona għalija? . , и Diġà qed jużaw it-tekniki li rrid nitkellem dwarhom illum.
Semmi mill-inqas żviluppatur backend wieħed li qatt ma uża OFFSET и LIMIT biex twettaq mistoqsijiet paġinati. Fl-MVP (Prodott Minimu Vijabbli) u fi proġetti fejn jintużaw ammonti żgħar ta’ dejta, dan l-approċċ huwa pjuttost applikabbli. Hija “taħdem biss,” biex ngħidu hekk.
Imma jekk għandek bżonn toħloq sistemi affidabbli u effiċjenti mill-bidu, għandek tieħu ħsieb bil-quddiem dwar l-effiċjenza li tikkonsulta d-databases użati f'sistemi bħal dawn.
Illum ser nitkellmu dwar il-problemi b'implimentazzjonijiet użati b'mod komuni (ħażina) ta' magni ta' query paġinati, u kif tinkiseb prestazzjoni għolja meta tesegwixxi mistoqsijiet bħal dawn.
X'hemm ħażin ma' OFFSET u LIMIT?
Kif diġà ntqal, OFFSET и LIMIT Huma jaħdmu tajjeb fi proġetti li m'għandhomx bżonn jaħdmu b'ammonti kbar ta 'dejta.
Il-problema tqum meta d-database tikber għal tali daqs li ma tibqax tidħol fil-memorja tas-server. Madankollu, meta taħdem ma 'din id-database, għandek bżonn tuża mistoqsijiet paġinati.
Biex din il-problema timmanifesta ruħha, irid ikun hemm sitwazzjoni li fiha d-DBMS jirrikorri għal operazzjoni Sħiħa ta' Skanjar tat-Tabella ineffiċjenti fuq kull mistoqsija paġnata (filwaqt li jistgħu jseħħu operazzjonijiet ta' inserzjoni u tħassir, u m'għandniex bżonn data skaduta!).
X'inhu "skan tal-mejda sħiħa" (jew "skannjar tal-mejda sekwenzjali", Skennjar Sekwenzjali)? Din hija operazzjoni li matulha d-DBMS jaqra b'mod sekwenzjali kull ringiela tat-tabella, jiġifieri, id-dejta li tinsab fiha, u jiċċekkjaha għall-konformità ma' kundizzjoni partikolari. Dan it-tip ta 'skan tal-mejda huwa magħruf li huwa l-aktar bil-mod. Il-fatt hu li meta tiġi esegwita, isiru ħafna operazzjonijiet ta 'input/output li jinvolvu s-subsistema tad-disk tas-server. Is-sitwazzjoni hija agħar mill-latency assoċjati max-xogħol ma 'data maħżuna fuq diski, u l-fatt li t-trasferiment tad-data minn disk għall-memorja hija operazzjoni intensiva fir-riżorsi.
Pereżempju, għandek rekords ta' 100000000 utent u tmexxi mistoqsija bil-kostruzzjoni OFFSET 50000000. Dan ifisser li d-DBMS se jkollu jgħabbi dawn ir-rekords kollha (u lanqas għandna bżonnhom!), ipoġġihom fil-memorja, u wara dan jieħu, ngħidu aħna, 20 riżultat irrappurtati f' LIMIT.
Ejja ngħidu li jista 'jidher bħal dan: "agħżel ringieli minn 50000 sa 50020 minn 100000". Jiġifieri, is-sistema l-ewwel trid tgħabbi 50000 ringiela biex tlesti l-mistoqsija. Tara kemm se jkollha tagħmel xogħol bla bżonn?
Jekk ma temminnix, agħti ħarsa lejn l-eżempju li ħloqt bl-użu tal-karatteristiċi .
Eżempju fuq db-fiddle.com
Hemm, fuq ix-xellug, fil-għalqa Schema SQL, hemm kodiċi li jdaħħal 100000 ringiela fid-database, u fuq il-lemin, fil-qasam Query SQL, jintwerew żewġ mistoqsijiet. L-ewwel waħda, bil-mod, tidher bħal din:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
U t-tieni, li hija soluzzjoni effettiva għall-istess problema, hija bħal din:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Sabiex tissodisfa dawn it-talbiet, ikklikkja fuq il-buttuna Run fin-naħa ta’ fuq tal-paġna. Wara li għamilna dan, inqabblu informazzjoni dwar il-ħin tal-eżekuzzjoni tal-mistoqsija. Jirriżulta li l-eżekuzzjoni ta’ mistoqsija ineffiċjenti tieħu mill-inqas 30 darba itwal mit-twettiq tat-tieni waħda (din id-darba tvarja minn ġirja għal oħra; pereżempju, is-sistema tista’ tirrapporta li l-ewwel mistoqsija ħadet 37 ms biex titlesta, iżda l-eżekuzzjoni tal- it-tieni - 1 ms).
U jekk ikun hemm aktar dejta, allura kollox jidher saħansitra agħar (biex tkun konvint minn dan, agħti ħarsa lejn tiegħi b'10 miljun ringieli).
Dak li għadna kif iddiskutejna għandu jagħtik ftit ħarsa dwar kif il-mistoqsijiet tad-database huma attwalment ipproċessati.
Jekk jogħġbok innota li iktar ma jkun għoli l-valur OFFSET — itwal it-talba se tieħu biex titlesta.
X'għandi nuża minflok il-kombinazzjoni ta' OFFSET u LIMIT?
Minflok kombinazzjoni OFFSET и LIMIT Ta 'min juża struttura mibnija skond l-iskema li ġejja:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Din hija l-eżekuzzjoni tal-mistoqsija b'paġnar ibbażat fuq il-cursor.
Minflok jaħżnu dawk kurrenti lokalment OFFSET и LIMIT u jittrasmettihom ma 'kull talba, għandek bżonn taħżen l-aħħar ċavetta primarja riċevuta (ġeneralment dan huwa ID) U LIMIT, bħala riżultat, mistoqsijiet simili għal dak ta 'hawn fuq se jinkisbu.
Għaliex? Il-punt hu li billi tispeċifika b'mod espliċitu l-identifikatur tal-aħħar ringiela li tinqara, tgħid lid-DBMS tiegħek fejn jeħtieġ li tibda tfittex id-dejta meħtieġa. Barra minn hekk, it-tfittxija, grazzi għall-użu taċ-ċavetta, se titwettaq b'mod effiċjenti; is-sistema ma jkollhiex għalfejn tkun distratt minn linji barra mill-firxa speċifikata.
Ejja nagħtu ħarsa lejn it-tqabbil tal-prestazzjoni li ġej ta 'mistoqsijiet varji. Hawnhekk hawn mistoqsija ineffettiva.
Talba bil-mod
U hawn verżjoni ottimizzata ta 'din it-talba.
Talba malajr
Iż-żewġ mistoqsijiet jirritornaw eżattament l-istess ammont ta’ dejta. Iżda l-ewwel wieħed jieħu 12,80 sekondi biex jitlesta, u t-tieni wieħed jieħu 0,01 sekondi. Tħoss id-differenza?
Problemi possibbli
Biex il-metodu ta' mistoqsija propost jaħdem b'mod effettiv, it-tabella għandu jkollha kolonna (jew kolonni) li jkun fihom indiċi uniċi u sekwenzjali, bħal identifikatur ta' numru sħiħ. F'xi każijiet speċifiċi, dan jista' jiddetermina s-suċċess tal-użu ta' tali mistoqsijiet biex tiżdied il-veloċità tal-ħidma mad-database.
Naturalment, meta tibni mistoqsijiet, trid tqis l-arkitettura speċifika tat-tabelli u tagħżel dawk il-mekkaniżmi li jaħdmu l-aħjar fuq it-tabelli eżistenti. Pereżempju, jekk għandek bżonn taħdem f'mistoqsijiet b'volumi kbar ta' data relatata, tista' ssibha interessanti artikolu.
Jekk niffaċċjaw il-problema li nitilfu ċavetta primarja, pereżempju, jekk ikollna tabella b'relazzjoni minn ħafna għal ħafna, allura l-approċċ tradizzjonali tal-użu OFFSET и LIMIT, huwa garantit li jixirqu lilna. Iżda l-użu tiegħu jista' jirriżulta fi mistoqsijiet potenzjalment bil-mod. F'każijiet bħal dawn, nirrakkomanda li tuża ċavetta primarja awto-inkrementata, anki jekk hija meħtieġa biss biex timmaniġġja mistoqsijiet paġinati.
Jekk inti interessat f'dan is-suġġett - , и - diversi materjali utli.
Riżultati ta '
Il-konklużjoni ewlenija li nistgħu nieħdu hija li, irrispettivament minn liema daqs ta 'databases qed nitkellmu, dejjem huwa meħtieġ li tanalizza l-veloċità tal-eżekuzzjoni tal-mistoqsija. Illum il-ġurnata, l-iskalabbiltà tas-soluzzjonijiet hija estremament importanti, u jekk kollox huwa ddisinjat b'mod korrett mill-bidu nett tax-xogħol fuq ċerta sistema, dan, fil-futur, jista 'jsalva lill-iżviluppatur minn ħafna problemi.
Kif tanalizza u tottimizza l-mistoqsijiet tad-database?
Sors: www.habr.com