Batsamaisi ba likete ba tsoang liofising tsa thekiso ho pholletsa le naha ba rekota mashome a likete tsa mabitso letsatsi le letsatsi - lintlha tsa puisano le bareki ba ka bang teng kapa ba teng. Mme bakeng sa sena, o tlameha ho qala ho fumana moreki, mme ka ho khetheha ka potlako haholo. 'Me sena se etsahala hangata ka mabitso.
Ka hona, ha ho makatse hore ebe, re boetse re sekaseka lipotso tse "boima" ho e 'ngoe ea li-database tse laetsoeng haholo - tsa rona. , ke fumane "ka holimo" kopa ho batla “ka potlako” ka mabitso bakeng sa likarete tsa mokhatlo.
Ho feta moo, lipatlisiso tse eketsehileng li ile tsa senola mohlala o thahasellisang optimization pele ebe ho theoloa ha ts'ebetso kopo ka ntlafatso ea eona ea tatellano ke lihlopha tse 'maloa, e' ngoe le e 'ngoe ea tsona e sebelitseng feela ka sepheo se setle.
0: mosebelisi o ne a batla eng?
[KDPV ]
Hangata mosebelisi o bolela eng ha a bua ka patlo e "potlako" ka lebitso? Hoo e ka bang ha ho mohla e kileng ea e-ba "patlisiso e tšepahalang" bakeng sa substring e kang ... LIKE '%роза%' - hobane joale sephetho ha se akarelletse feela 'Розалия' и 'Магазин Роза'empa 'Гроза' esita le 'Дом Деда Мороза'.
Mosebelisi o nka boemo ba letsatsi le letsatsi boo o tla mo fa bona batla ka qalo ya lentsoe sehloohong le ho etsa hore e be e loketseng haholoanyane hore e qala ka kena. 'Me u tla e etsa hoo e ka bang hang hang - bakeng sa ho kenya li-interlinear.
1: fokotsa mosebetsi
Mme ho feta moo, motho a ke ke a kena ka ho toba 'роз магаз', kahoo o tlameha ho batla lentsoe ka leng ka sehlongwapele. Che, ho bonolo haholo hore mosebelisi a arabe temoso e potlakileng bakeng sa lentsoe la ho qetela ho feta ho "fokotsa" tse fetileng ka boomo - sheba hore na enjine efe kapa efe ea ho batla e sebetsana le sena.
Kakaretso ka ho le letona ho etsa litlhoko tsa bothata ho feta halofo ea tharollo. Ka linako tse ling ka hloko sebelisa tlhahlobo ea linyeoe .
Moetsi oa lintho tse bonahalang o etsa eng?
1.0: enjine ea ho batla ea kantle
Oh, ho batla ho thata, ha ke batle ho etsa letho ho hang - ha re e fe li-devops! E re ba kenye enjine ea ho batla kantle ho database: Sphinx, ElasticSearch,...
Khetho ea ho sebetsa, leha e le matla a mosebetsi o boima mabapi le ho hokahanya le lebelo la liphetoho. Empa eseng ho rona, kaha patlo e etsoa bakeng sa moreki e mong le e mong ka har'a moralo oa data ea akhaonto ea hae. Mme data e na le phapang e phahameng haholo - mme haeba mookameli o se a kentse karete 'Магазин Роза', ebe ka mor'a metsotsoana ea 5-10 a ka 'na a se a ntse a hopola hore o lebetse ho bontša imeile ea hae moo mme o batla ho e fumana le ho e lokisa.
Ka hona - ha re batla “ka ho toba polokelong ea litaba”. Ka lehlohonolo, PostgreSQL e re lumella ho etsa sena, eseng khetho e le 'ngoe feela - re tla e sheba.
1.1: "ts'epehi" substring
Re khomarela lentsoe "substring". Empa bakeng sa lipatlisiso tsa index ka substring (esita le ka lipolelo tse tloaelehileng!) ho na le tse ntle haholo ! Ke ka nako eo feela ho tla hlokahala ho hlophisa hantle.
Ha re leke ho nka poleiti e latelang ho nolofatsa mohlala:
CREATE TABLE firms(
id
serial
PRIMARY KEY
, name
text
);Re kenya lirekoto tse limilione tse 7.8 tsa mekhatlo ea 'nete moo ebe re li supa:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);Ha re batle lirekoto tse 10 tsa pele bakeng sa patlo ea li-interlinear:
SELECT
*
FROM
firms
WHERE
lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;
Che, ke... 26ms, 31MB bala lintlha le lirekoto tse hloekisitsoeng tse fetang 1.7K - bakeng sa tse 10 tse batlisisitsoeng. Litšenyehelo tse holimo li holimo haholo, na ha ho na ho hong ho sebetsang hantle?
1.2: batla ka mongolo? Ke FTS!
Ehlile, PostgreSQL e fana ka matla haholo (Full Text Search), ho kenyelletsa le bokhoni ba ho batla li-prefix. Khetho e ntle haholo, ha ho hlokahale le ho kenya li-extensions! Ha re leke:
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;
Mona parallelization ea query execution re thusitse hanyane, ho fokotsa nako ka halofo ho 11ms. Mme re ne re tlameha ho bala makhetlo a 1.5 ka tlase - ka kakaretso 20MB. Empa mona, ho fokotseha, ho molemo, hobane molumo o moholo oo re o balang, o eketsa menyetla ea ho fumana cache, 'me leqephe le leng le le leng la boitsebiso bo baloang ho tswa ho disk ke "brake" e ka khonehang bakeng sa kopo.
1.3: o ntse o LIKE?
Kopo e fetileng e ntle ho bohle, empa feela haeba u e hula ka makhetlo a likete tse lekholo ka letsatsi, e tla tla 2TB bala data. Boemong bo botle ka ho fetisisa, ho tloha mohopolong, empa haeba u se na lehlohonolo, joale ho tloha ho disk. Kahoo a re lekeng ho e etsa hore e be nyenyane.
Ha re hopole seo mosebelisi a batlang ho se bona ea pele "e qalang ka ...". Kahoo sena se ka sebopeho sa sona se hloekileng ka thuso text_pattern_ops! 'Me hafeela re "se na" ho fihlela ho lirekoto tse 10 tseo re li batlang, re tla tlameha ho qeta ho li bala re sebelisa lipatlisiso tsa FTS:
CREATE INDEX ON firms(lower(name) text_pattern_ops);SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
LIMIT 10;
Tshebetso e babatsehang - kakaretso 0.05ms le ho feta 100KB hanyane bala! Feela re lebetse hlopha ka mabitsoe le hore mosebelisi a se ke a lahleheloa ke liphetho:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
Oh, ntho e 'ngoe ha e sa le ntle haholo - ho bonahala eka ho na le index, empa ho hlophisa ho fofa ho feta ho eona ... Ha e le hantle, e se e ntse e sebetsa ka makhetlo a mangata ho feta khetho e fetileng, empa ...
1.4: "qeta ka faele"
Empa ho na le index e u lumellang hore u batlisise ka mefuta e mengata 'me u ntse u sebelisa ho hlopha ka mokhoa o tloaelehileng - btree e tloaelehileng!
CREATE INDEX ON firms(lower(name));Ke kopo ea eona feela e tla tlameha ho "bokelloa ka letsoho":
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
lower(name)
LIMIT 10;
E ntle haholo - ho hlophisa ho sebetsa, 'me tšebeliso ea lisebelisoa e lula e le "microscopic", makhetlo a likete ho feta FTS "e hloekileng".! Ho setseng ke ho e kopanya ka kopo e le 'ngoe:
(
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;
Hlokomela hore subquery ea bobeli e etsoa hafeela oa pele a khutla ka tlase ho kamoo ho neng ho lebelletsoe ea ho qetela LIMIT palo ea mela. Ke bua ka mokhoa ona oa ho ntlafatsa lipotso .
Ho joalo, hona joale re na le btree le gin tafoleng, empa ho ea ka lipalo-palo hoa etsahala ka tlase ho 10% ea likopo li fihla ts'ebetsong ea block ea bobeli. Ke hore, ka mefokolo e joalo e tloaelehileng e tsejoang esale pele bakeng sa mosebetsi, re khonne ho fokotsa tšebeliso ea kakaretso ea lisebelisoa tsa seva ka makhetlo a ka bang sekete!
1.5 *: re ka etsa ntle le faele
Holimo LIKE Re ile ra thibeloa ho sebelisa khetho e fosahetseng. Empa e ka "behoa tseleng e nepahetseng" ka ho hlakisa opareitara ea USING:
Ka ho sa feleng ho nahanoa
ASC. Ho feta moo, o ka hlakisa lebitso la mofuta o itseng oa opareitara polelongUSING. Mofuta o mong oa opareitara e tlameha ho ba setho sa ba ka tlase ho kapa ho feta ba lelapa le leng la basebetsi ba lifate tsa B.ASChangata e lekanaUSING <иDESChangata e lekanaUSING >.
Tabeng ea rona, "tlase" ke ~<~:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2: kamoo likopo li fetohang bolila kateng
Hona joale re tlohela kopo ea rona ea ho "bolela" ka likhoeli tse tšeletseng kapa selemo, 'me re maketse ho e fumana hape "ka holimo" ka matšoao a "pumping" ea mohopolo oa letsatsi le letsatsi (li-buffers tse arolelanoang) ho 5.5TB - ke hore, ho feta kamoo e neng e le ka teng qalong.
Che, ehlile, khoebo ea rona e holile mme mosebetsi oa rona o eketsehile, empa eseng ka tekanyo e lekanang! Sena se bolela hore ho na le ntho e ts'oanelang mona - ha re e utloisise.
2.1: tsoalo ea paging
Ka nako e 'ngoe, sehlopha se seng sa nts'etsopele se ne se batla ho etsa hore ho khonehe ho "tlola" ho tloha patlisisong e potlakileng ea ho ngolisa ho ea ho registry ka liphello tse tšoanang, empa tse atolositsoeng. Registry ke eng ntle le ho tsamaea ka maqephe? Ha re e qhekelle!
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;Joale ho ne ho ka khonahala ho bonts'a registry ea liphetho tsa lipatlisiso ka "leqephe-leqephe-leqephe" e kenyang ntle le khatello ea maikutlo bakeng sa moqapi.
Ehlile, ha e le hantle, bakeng sa leqephe le leng le le leng le latelang la data ho baloa haholoanyane (tsohle ho tloha nakong e fetileng, eo re tla e lahla, hammoho le "mohatla" o hlokahalang) - ke hore, ena ke antipattern e hlakileng. Empa ho tla ba ho nepahetseng ho qala patlo ka nako e latelang ho tsoa ho senotlolo se bolokiloeng sebopehong, empa hoo e ka bang nako e 'ngoe.
2.2: Ke batla ntho e makatsang
Ka nako e 'ngoe moqapi o ne a batla fapanyetsana lisampole se hlahisoang ka data ho tsoa tafoleng e 'ngoe, eo kopo eohle e fetileng e rometsoeng ho CTE:
WITH q AS (
...
LIMIT <N> + 10
)
SELECT
*
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
q
LIMIT 10 OFFSET <N>;Leha ho le joalo, ha e mpe, kaha subquery e hlahlojoa feela bakeng sa lirekoto tse 10 tse khutlisitsoeng, haeba ho se joalo ...
2.3: DIISTINANT ha e na kelello ebile ha e na mohau
Kae-kae ts'ebetsong ea ho iphetola ha lintho ho tsoa ho subquery ea 2 lahleha NOT LIKE boemo. Ho hlakile hore ka mor'a sena UNION ALL qala ho kgutla tse ling tse kenang habeli - pele e fumanoa qalong ea mola, hape hape - qalong ea lentsoe la pele la mola ona. Moeli o lekaneng, lirekoto tsohle tsa subquery ea 2 li ka lumellana le lirekoto tsa pele.
Moqapi o etsa eng ho e-na le ho batla sesosa?.. Ha ho potso!
- habeli boholo mehlala ea pele
- etsa kopo DISTINCTho fumana mohlala o le mong oa mola o mong le o mong
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>;Ke hore, ho hlakile hore sephetho, qetellong, se ts'oana hantle, empa monyetla oa ho "fofela" ho subquery ea 2 CTE e se e phahame haholo, esita le ntle le sena, ka ho hlaka ho feta ho baloa.
Empa sena ha se ntho e bohloko ka ho fetisisa. Kaha mohlahlami o ile a botsa ho khetha DISTINCT eseng bakeng sa tse itseng, empa bakeng sa masimo ohle hang-hang rekoto, ebe sebaka sa sub_query - sephetho sa subquery - se kenyellelitsoe moo ka bohona. Jwale, ho phetha DISTINCT, database e ne e se e ntse e sebetsa eseng lipotso tse 10, empa kaofela <2 * N> + 10!
2.4: tšebelisano ka holim'a tsohle!
Kahoo, bahlahisi ba ne ba phela - ha baa ka ba khathatseha, hobane ho hlakile hore mosebelisi o ne a se na mamello e lekaneng ea ho "fetola" ngoliso hore e be boleng ba bohlokoa ba N ka ho fokotseha ho hoholo ha ho amohela "leqephe" le leng le le leng le latelang.
Ho fihlela bahlahisi ba tsoang lefapheng le leng ba tla ho bona mme ba batla ho sebelisa mokhoa o bonolo joalo bakeng sa ho batla khafetsa - ke hore, re nka sengoathoana ho tloha sampuling e 'ngoe, re sefa ka maemo a eketsehileng, re hula sephetho, ebe sekhechana se latelang (seo ho rona se finyelloang ka ho eketsa N), joalo-joalo ho fihlela re tlatsa skrine.
Ka kakaretso, ka mohlala o tšoasitsoeng N e fihletse boleng ba hoo e ka bang 17K, 'me ka letsatsi le le leng feela bonyane 4K ea likōpo tse joalo li ile tsa etsoa "hammoho le ketane". Ba ho qetela ba bona ba ile ba hlahlojoa ka sebete ke 1GB ea memori ka ho pheta-pheta...
Kakaretso
Source: www.habr.com