Zviuru zvemamaneja kubva kumahofisi ekutengesa munyika yose rekodhi
Naizvozvo, hazvishamise kuti, zvakare kuongorora "inorema" mibvunzo pane imwe yeakanyanya kuremerwa dhatabhesi - yedu pachedu.
Uyezve, kumwe kuongorora kwakaratidza muenzaniso unofadza kutanga optimization uye ipapo kuderedzwa kwekuita kukumbira nekukwenenzverwa kwayo nezvikwata zvakati wandei, chimwe nechimwe chakaita nevavariro dzakanakisa.
0: mushandisi aidei?
[KDPV
Ko mushandisi anowanzo kurevei kana vachitaura nezve "kukurumidza" kutsvaga nemazita? Iyo inenge isingambozove "yakatendeseka" yekutsvaga substring senge ... LIKE '%ΡΠΎΠ·Π°%'
- nokuti ipapo chigumisiro chinosanganisira kwete chete 'Π ΠΎΠ·Π°Π»ΠΈΡ'
ΠΈ 'ΠΠ°Π³Π°Π·ΠΈΠ½ Π ΠΎΠ·Π°'
asi 'ΠΡΠΎΠ·Π°'
uye kunyange 'ΠΠΎΠΌ ΠΠ΅Π΄Π° ΠΠΎΡΠΎΠ·Π°'
.
Mushandisi anotora padanho remazuva ese rauchazomupa tsvaga nekutanga kweshoko mumusoro uye ita kuti zvive zvakakosha izvo inotanga na akapinda. Uye uchazviita pakarepo - yekupinda mukati.
1: kuderedza basa
Uye zvakatonyanya, munhu haazopindi zvakananga 'ΡΠΎΠ· ΠΌΠ°Π³Π°Π·'
, kuitira kuti utsvage izwi rega rega neprefix. Kwete, zviri nyore kuti mushandisi apindure kune inokurumidza zano rekupedzisira izwi pane kuti nechinangwa "kutsanangudza" zvakapfuura - tarisa kuti chero injini yekutsvaga inobata sei izvi.
Zvachose, zvakarurama kugadzira zvinodiwa padambudziko iri inopfuura hafu yemhinduro. Dzimwe nguva nyatsoshandisa kuongorora nyaya
Chii chinoita abstract developer?
1.0: injini yekutsvaga yekunze
Ah, kutsvaga kwakaoma, ini handidi kuita chero chinhu - ngatizvipe kune devops! Rega vatumire injini yekutsvaga kunze kune dhatabhesi: Sphinx, ElasticSearch,...
Sarudzo yekushanda, kunyangwe yekushanda-yakanyanya maererano nekuyananisa uye nekukurumidza kwekuchinja. Asi kwete kwatiri, sezvo kutsvaga kunoitwa kune mumwe nemumwe mutengi chete mukati meiyo yeakaundi data data. Uye iyo data ine mutsauko wakanyanya - uye kana maneja atopinda kadhi 'ΠΠ°Π³Π°Π·ΠΈΠ½ Π ΠΎΠ·Π°'
, zvino mushure memasekonzi 5-10 angave atorangarira kuti akanganwa kuratidza email yake ipapo uye anoda kuiwana uye kuigadzirisa.
Naizvozvo - ngatiitei tsvaga "zvakananga mudura". Neraki, PostgreSQL inotitendera kuti tiite izvi, uye kwete sarudzo imwe chete - tichavatarisa.
1.1: "kutendeseka" substring
Isu tinonamatira kune izwi rekuti "substring". Asi yekutsvaga index ne substring (uye kunyangwe nemataurirwo enguva dzose!) pane yakanaka
Ngatiedzei kutora ndiro inotevera kurerutsa modhi:
CREATE TABLE firms(
id
serial
PRIMARY KEY
, name
text
);
Isu tinorodha 7.8 miriyoni marekodhi emasangano chaiwo ipapo uye tinoaratidza:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);
Ngatitsvagei marekodhi gumi ekutanga ekutsvaga mukati memitsara:
SELECT
*
FROM
firms
WHERE
lower(name) ~ ('(^|s)' || 'ΡΠΎΠ·Π°')
ORDER BY
lower(name) ~ ('^' || 'ΡΠΎΠ·Π°') DESC -- ΡΠ½Π°ΡΠ°Π»Π° "Π½Π°ΡΠΈΠ½Π°ΡΡΠΈΠ΅ΡΡ Π½Π°"
, lower(name) -- ΠΎΡΡΠ°Π»ΡΠ½ΠΎΠ΅ ΠΏΠΎ Π°Π»ΡΠ°Π²ΠΈΡΡ
LIMIT 10;
Zvakanaka, ndizvo ... 26ms, 31MB verenga data uye anopfuura 1.7K marekodhi akasefa - kune gumi akatsvaga. Mutengo wepamusoro wakakwirisa, hapana here chimwe chinhu chinoshanda?
1.2: kutsvaga nemavara? iri FTS!
Chokwadi, PostgreSQL inopa ine simba kwazvo
CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));
SELECT
*
FROM
firms
WHERE
to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'ΡΠΎΠ·Π°:*')
ORDER BY
lower(name) ~ ('^' || 'ΡΠΎΠ·Π°') DESC
, lower(name)
LIMIT 10;
Pano kufanana kwekuita kwemubvunzo kwakatibatsira zvishoma, kucheka nguva nepakati kusvika 11ms. Uye isu taifanira kuverenga ka1.5 zvishoma - pamwe chete 20MB. Asi pano, zvishoma zviri nani, nokuti iyo yakakura yakawanda yatinoverenga, inowedzera mikana yekuwana cache miss, uye imwe neimwe peji ye data yakaverengwa kubva ku diski inogona "mabhureki" ekukumbira.
1.3: uchiri KUDA?
Chikumbiro chekare chakanaka kune wese munhu, asi chete kana iwe uchikwevera iyo zana rezviuru pazuva, ichauya 2TB verenga data. Muchiitiko chakanakisa, kubva mundangariro, asi kana iwe usina rombo rakanaka, ipapo kubva kudhisiki. Saka ngatiedzei kuita diki.
Ngatirangarirei izvo mushandisi anoda kuona kutanga "izvo zvinotanga ne...". Saka izvi zviri muchimiro chayo chakachena text_pattern_ops
! Uye chete kana isu "tisina zvakakwana" kusvika kune gumi zvinyorwa zvatiri kutsvaga, saka tichazopedza kuaverenga tichishandisa FTS kutsvaga:
CREATE INDEX ON firms(lower(name) text_pattern_ops);
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('ΡΠΎΠ·Π°' || '%')
LIMIT 10;
Kuita kwakanakisa - zvachose 0.05ms uye zvishoma kudarika 100KB verenga! Isu chete takakanganwa ronga nemazitakuitira kuti mushandisi asarasikirwe mumhedzisiro:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('ΡΠΎΠ·Π°' || '%')
ORDER BY
lower(name)
LIMIT 10;
Oo, chimwe chinhu hachina kunaka zvakare - zvinoita sekunge pane index, asi kurongedza kunobhururuka kwakapfuura ... Iko, hongu, yatove yatove yakawanda kakawanda kupfuura sarudzo yapfuura, asi ...
1.4: "pedza nefaira"
Asi pane index inobvumidza iwe kutsvaga nemhando uye uchiri kushandisa kurongedza zvakajairika - nguva dzose btree!
CREATE INDEX ON firms(lower(name));
Chete chikumbiro chayo chichafanirwa "kuunganidzwa nemaoko":
SELECT
*
FROM
firms
WHERE
lower(name) >= 'ΡΠΎΠ·Π°' AND
lower(name) <= ('ΡΠΎΠ·Π°' || chr(65535)) -- Π΄Π»Ρ UTF8, Π΄Π»Ρ ΠΎΠ΄Π½ΠΎΠ±Π°ΠΉΡΠΎΠ²ΡΡ
- chr(255)
ORDER BY
lower(name)
LIMIT 10;
Yakanakisa - kurongedza kunoshanda, uye kushandiswa kwezviwanikwa kunoramba kuri "microscopic", zviuru zvenguva zvakanyanya kushanda pane "yakachena" FTS! Chasara kuzviisa pamwechete muchikumbiro chimwe chete:
(
SELECT
*
FROM
firms
WHERE
lower(name) >= 'ΡΠΎΠ·Π°' AND
lower(name) <= ('ΡΠΎΠ·Π°' || chr(65535)) -- Π΄Π»Ρ UTF8, Π΄Π»Ρ ΠΎΠ΄Π½ΠΎΠ±Π°ΠΉΡΠΎΠ²ΡΡ
ΠΊΠΎΠ΄ΠΈΡΠΎΠ²ΠΎΠΊ - chr(255)
ORDER BY
lower(name)
LIMIT 10
)
UNION ALL
(
SELECT
*
FROM
firms
WHERE
to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'ΡΠΎΠ·Π°:*') AND
lower(name) NOT LIKE ('ΡΠΎΠ·Π°' || '%') -- "Π½Π°ΡΠΈΠ½Π°ΡΡΠΈΠ΅ΡΡ Π½Π°" ΠΌΡ ΡΠΆΠ΅ Π½Π°ΡΠ»ΠΈ Π²ΡΡΠ΅
ORDER BY
lower(name) ~ ('^' || 'ΡΠΎΠ·Π°') DESC -- ΠΈΡΠΏΠΎΠ»ΡΠ·ΡΠ΅ΠΌ ΡΡ ΠΆΠ΅ ΡΠΎΡΡΠΈΡΠΎΠ²ΠΊΡ, ΡΡΠΎΠ±Ρ ΠΠ ΠΏΠΎΠΉΡΠΈ ΠΏΠΎ btree-ΠΈΠ½Π΄Π΅ΠΊΡΡ
, lower(name)
LIMIT 10
)
LIMIT 10;
Ziva kuti yechipiri subquery inoitwa chete kana wekutanga akadzoka zvishoma pane zvaitarisirwa chekupedzisira LIMIT
nhamba yemitsara. Ndiri kutaura nezve iyi nzira yekubvunza optimization
Saka hongu, isu ikozvino tine zvese btree uye gin patafura, asi nenhamba zvinobuda izvo isingasviki 10% yezvikumbiro zvinosvika pakuitwa kwechipiri block. Ndiko kuti, nemaitiro akadaro akajairika anozivikanwa pamberi pebasa racho, takakwanisa kuderedza kushandiswa kwese kwesevha zviwanikwa nekanenge chiuru!
1.5 *: tinogona kuita pasina faira
Pamusoro LIKE
Takatadziswa kushandisa kusarura kusiri iko. Asi inogona "kuiswa munzira chaiyo" nekutsanangura iyo USING opareta:
By default zvinofungidzirwa
ASC
. Pamusoro pezvo, iwe unogona kudoma zita remumwe munhu anoshanda mumutsaraUSING
. Mushandisi wemhando anofanirwa kunge ari nhengo yevashoma pane kana mukuru pane yeimwe mhuri yeB-muti vanoshanda.ASC
kazhinji yakaenzanaUSING <
ΠΈDESC
kazhinji yakaenzanaUSING >
.
Muchiitiko chedu, "zvishoma" ndizvo ~<~
:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('ΡΠΎΠ·Π°' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2: zvikumbiro zvinovava sei
Iye zvino tinosiya chikumbiro chedu kuti "simmer" kwemwedzi mitanhatu kana gore, uye tinoshamiswa kuiwana zvakare "pamusoro" nezviratidzo zvemazuva ose "kupomba" kwekuyeuka (buffers yakagovaniswa hit) mukati 5.5TB - ndiko kuti, kunyange kupfuura zvayaiva pakutanga.
Kwete, chokwadi, bhizinesi redu rakakura uye basa redu rakawedzera, asi kwete nehuwandu hwakafanana! Izvi zvinoreva kuti pane chinhu chine hove pano - ngatizviongororei.
2.1: kuzvarwa kwepeji
Pane imwe nguva, chimwe chikwata chekusimudzira chaida kuita kuti zvikwanise "kusvetuka" kubva pakukurumidza kunyorera kutsvaga kune registry nezvakafanana, asi zvakawedzerwa mhedzisiro. Chii chinonzi registry pasina peji navigation? Ngatikurukurei!
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;
Zvino zvainge zvave kuita kuratidza registry yezviwanikwa zvekutsvaga ne "peji-ne-peji" kurodha pasina kushushikana kune mugadziri.
Chokwadi, chokwadi, kune rimwe nerimwe peji rinotevera re data yakawanda uye yakawanda inoverengwa (zvese kubva panguva yapfuura, iyo yaticharasa, pamwe neinodiwa "muswe") - ndiko kuti, iyi inoratidzira yakajeka antipattern. Asi zvingave zvakanyanya kunaka kutanga kutsvaga pane inotevera iteration kubva kukiyi yakachengetwa muinterface, asi pane imwe nguva.
2.2: Ini ndinoda chimwe chinhu chinoshamisa
Pane imwe nguva mugadziri aida siyanisa sampuli inoguma nedata kubva kune imwe tafura, iyo yese chikumbiro chekare chakatumirwa kuCTE:
WITH q AS (
...
LIMIT <N> + 10
)
SELECT
*
, (SELECT ...) sub_query -- ΠΊΠ°ΠΊΠΎΠΉ-ΡΠΎ Π·Π°ΠΏΡΠΎΡ ΠΊ ΡΠ²ΡΠ·Π°Π½Π½ΠΎΠΉ ΡΠ°Π±Π»ΠΈΡΠ΅
FROM
q
LIMIT 10 OFFSET <N>;
Uye kunyangwe zvakadaro, hazvina kushata, sezvo subquery ichiongororwa chete gumi rekodhi rekodhi, kana zvisiri ...
2.3: DISTINCT haina musoro uye haina tsitsi
Pane imwe nzvimbo mukuita kwekushanduka kwakadaro kubva ku2nd subquery akarasika NOT LIKE
mamiriro. Zviri pachena kuti mushure meizvi UNION ALL
akatanga kudzoka mamwe anopinda kaviri - kutanga kuwanikwa pakutanga kwemutsara, uye zvakare - pakutanga kwezwi rekutanga remutsara uyu. Mumuganho, marekodhi ese e2nd subquery anogona kuenderana nemarekodhi ekutanga.
Mugadziri anoita sei pane kutsvaga chikonzero?.. Hapana mubvunzo!
- kaviri saizi yekutanga samples
- shandisa DISTINCTkuti uwane mienzaniso imwe chete yemutsara wega wega
WITH q AS (
( ... LIMIT <2 * N> + 10)
UNION ALL
( ... LIMIT <2 * N> + 10)
LIMIT <2 * N> + 10
)
SELECT DISTINCT
*
, (SELECT ...) sub_query
FROM
q
LIMIT 10 OFFSET <N>;
Ndiko, zviri pachena kuti mugumisiro, pakupedzisira, wakangofanana, asi mukana we "kubhururuka" muchikamu chechipiri cheCTE chave chakakwirira zvikuru, uye kunyange pasina izvi, zvinonyatso verengwa.
Asi ichi hachisi chinhu chinosuwisa. Sezvo mugadziri akakumbira kusarudza DISTINCT
kwete kune chaiwo, asi kune ese minda kamwechete marekodhi, ipapo iyo sub_query ndima - mhedzisiro yeiyo subquery - yakaverengerwa ipapo. Zvino, kuita DISTINCT
, database yaifanira kuita kare kwete gumi subqueries, asi ese <10 * N> + 2!
2.4: kubatana pamusoro pezvose!
Saka, vagadziri vakararama - havana kunetseka, nekuti mushandisi zvakajeka akange asina kushivirira kwakakwana "kugadzirisa" registry kune yakakosha N kukosha nekudzikira kusingaperi mukugamuchira imwe neimwe inotevera "peji".
Kusvikira vagadziri verimwe dhipatimendi vauya kwavari uye vaida kushandisa nzira iri nyore kudaro kutsvaga kwekudzokorora - ndiko kuti, isu tinotora chidimbu kubva kune imwe sampuli, kuisefa nemamiriro ekuwedzera, dhirowa mhedzisiro, ipapo chidimbu chinotevera (iyo mune yedu kesi inowanikwa nekuwedzera N), uye zvichingodaro kusvika tazadza skrini.
Kazhinji, mumuenzaniso wakabatwa N yakasvika hunhu hunosvika 17K, uye muzuva rimwe chete zvishoma 4K yezvikumbiro zvakadaro zvakaurayiwa "pamwe chete neketani". Vekupedzisira vavo vakashinga vakaongororwa na 1GB yekuyeuka pane imwe iteration...
Total
Source: www.habr.com