Taifead na mílte bainisteoir ó oifigí díolacháin ar fud na tíre na mílte teagmhála laethúil — fíricí cumarsáide le cliaint ionchasacha nó cliaint atá ann cheana. Agus seo, ní mór duit a aimsiú ar dtús le cliant, agus b'fhearr go han-tapa. Agus is minic a tharlaíonn sé seo de réir ainm.
Mar sin, ní haon ionadh é, agus anailís á déanamh arís ar fhiosrúcháin “troma” ar cheann de na bunachair shonraí is lódáilte - ár gcuid féin. , fuair mé "sa mhullach" iarratas ar chuardach “tapa” de réir ainm le haghaidh cártaí eagraíochta.
Ina theannta sin, léirigh imscrúdú breise sampla suimiúil leas iomlán a bhaint ar dtús agus ansin díghrádú feidhmíochta iarratas lena mionchoigeartú seicheamhach ag roinnt foirne, gach ceann acu ag gníomhú leis na hintinn is fearr amháin.
0: cad a bhí an t-úsáideoir ag iarraidh?
[KDPV ]
Cad a chiallaíonn úsáideoir de ghnáth nuair a labhraíonn siad faoi chuardach “tapa” de réir ainm? Is beag nach mbíonn sé riamh ina chuardach “macánta” ar fhotheaghrán cosúil leis ... LIKE '%роза%' - mar gheall ar ansin folaíonn an toradh ní amháin 'Розалия' и 'Магазин Роза'Ach 'Гроза' agus fiú 'Дом Деда Мороза'.
Glacann an t-úsáideoir leis ag an leibhéal laethúil go gcuirfidh tú ar fáil dó cuardaigh de réir tús an fhocail sa teideal agus é a dhéanamh níos ábhartha go ag tosú ar isteach. Agus déanfaidh tú é beagnach láithreach - le haghaidh ionchur idirlíneach.
1: teorainn a chur leis an tasc
Agus níos mó fós ná sin, ní rachaidh duine isteach go sonrach 'роз магаз', ionas go mbeidh ort cuardach a dhéanamh ar gach focal de réir réimír. Ní hea, tá sé i bhfad níos éasca d’úsáideoir freagra a thabhairt ar leid thapa don fhocal deiridh ná na cinn roimhe seo a “undershonrú” d’aon ghnó - féach ar an gcaoi a láimhseálann inneall cuardaigh é seo.
Go ginearálta, i gceart Tá níos mó ná leath an réiteach ag baint le foirmiú na gceanglas maidir leis an bhfadhb. Uaireanta úsáid chúramach anailís cáis .
Cad a dhéanann forbróir teibí?
1.0: inneall cuardaigh seachtrach
Ó, tá an cuardach deacair, níl mé ag iarraidh aon rud a dhéanamh ar chor ar bith - a ligean ar é a thabhairt do devops! Lig dóibh inneall cuardaigh a imscaradh lasmuigh den bhunachar sonraí: Sphinx, ElasticSearch,...
Rogha oibre, cé go bhfuil sé dian ar shaothar maidir le sioncrónú agus luas na n-athruithe. Ach ní inár gcás, ós rud é go ndéantar an cuardach do gach cliant ach amháin laistigh de chreat a shonraí cuntais. Agus tá éagsúlacht sách ard ag na sonraí - agus má tá an cárta curtha isteach ag an mbainisteoir anois 'Магазин Роза', ansin tar éis 5-10 soicind b'fhéidir go gcuimhneoidh sé cheana féin go ndearna sé dearmad a ríomhphost a chur in iúl ann agus gur mhaith leis é a aimsiú agus é a cheartú.
Dá bhrí sin - a ligean ar cuardaigh "go díreach sa bhunachar sonraí". Go fortunately, ligeann PostgreSQL dúinn é seo a dhéanamh, agus ní rogha amháin - féachfaimid orthu.
1.1: fotheideal "macánta".
Cloímis leis an bhfocal “substring”. Ach do chuardach innéacs trí fhotheaghrán (agus fiú trí nathanna rialta!) tá sármhaith ann ! Is ansin amháin a bheidh sé riachtanach sórtáil i gceart.
Déanaimis iarracht an pláta seo a leanas a ghlacadh chun an tsamhail a shimpliú:
CREATE TABLE firms(
id
serial
PRIMARY KEY
, name
text
);Uaslódálann muid 7.8 milliún taifead d’eagraíochtaí fíor ansin agus déanaimid iad a innéacsú:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);Breathnaímid ar na chéad 10 dtaifead le haghaidh cuardach idirlíneach:
SELECT
*
FROM
firms
WHERE
lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;
Bhuel, sin é... 26ms, 31MB sonraí a léamh agus níos mó ná 1.7K taifead scagtha - le haghaidh 10 gcinn a cuardaíodh. Tá na forchostais ró-ard, nach bhfuil rud éigin níos éifeachtaí?
1.2: cuardach de réir téacs? Tá sé FTS!
Go deimhin, soláthraíonn PostgreSQL an-chumhachtach (Cuardach Téacs Iomlán), lena n-áirítear an cumas cuardaigh a réamhshocrú. Rogha iontach, ní gá duit fiú síntí a shuiteáil! Déanaimis iarracht:
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;
Anseo chabhraigh comhthreomharú ar fhorghníomhú fiosrúcháin linn beagán, ag gearradh an t-am ina dhá leath 11ms. Agus bhí orainn léamh 1.5 uair níos lú - san iomlán 20MB. Ach anseo, is amhlaidh is lú, is amhlaidh is fearr, mar dá mhéad an méid a léifimid, is amhlaidh is airde an seans go bhfaighidh tú taisce, agus is “coscáin” féideartha don iarratas gach leathanach breise sonraí a léitear ón diosca.
1.3: fós mar?
Tá an t-iarratas roimhe seo go maith do gach duine, ach amháin má tharraingíonn tú é céad míle uair sa lá, tiocfaidh sé 2TB sonraí a léamh. Sa chás is fearr, ó chuimhne, ach má tá tú mí-ádh, ansin ón diosca. Mar sin déanaimis iarracht é a dhéanamh níos lú.
A ligean ar cuimhneamh cad ba mhaith leis an úsáideoir a fheiceáil ar dtús "a thosaíonn le ...". Mar sin tá sé seo ina fhoirm íon leis an gcabhair text_pattern_ops! Agus mura bhfuil “go leor againn” suas le 10 dtaifead atá á lorg againn, beidh orainn iad a léamh trí chuardach FTS a úsáid:
CREATE INDEX ON firms(lower(name) text_pattern_ops);SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
LIMIT 10;
Feidhmíocht den scoth - iomlán 0.05ms agus beagán níos mó ná 100KB léigh! Ach rinneamar dearmad sórtáil de réir ainmionas nach gcailltear an t-úsáideoir sna torthaí:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
Ó, níl rud éigin chomh hálainn anois - is cosúil go bhfuil innéacs ann, ach go n-eitiltíonn an sórtáil anuas air... Tá sé, ar ndóigh, i bhfad níos éifeachtaí cheana féin ná an rogha roimhe seo, ach ...
1.4: "Críochnaigh le comhad"
Ach tá innéacs ann a ligeann duit cuardach a dhéanamh de réir raoin agus úsáid a bhaint as sórtáil de ghnáth - rialta btree!
CREATE INDEX ON firms(lower(name));Ní gá ach an t-iarratas air a “bhailiú de láimh”:
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
lower(name)
LIMIT 10;
Sármhaith - oibríonn an sórtáil, agus tá tomhaltas acmhainní fós "micreascópach", na mílte uair níos éifeachtaí ná FTS “íon”.! Níl fágtha ach é a chur le chéile in aon iarratas amháin:
(
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;
Tabhair faoi deara go bhfuil an dara subquery forghníomhaithe ach amháin má tháinig an chéad cheann ar ais níos lú ná mar a ceapadh seo caite LIMIT líon na línte. Táim ag caint faoin modh seo maidir le leas iomlán a bhaint ceisteanna .
Mar sin, tá, tá btree agus gin againn anois ar an tábla, ach go staitistiúil tharla sé sin sroicheann níos lú ná 10% de na hiarratais ar fhorghníomhú an dara bloc. Is é sin, le teorainneacha tipiciúil den sórt sin ar eolas roimh ré don tasc, bhíomar in ann tomhaltas iomlán na n-acmhainní freastalaí a laghdú beagnach míle uair!
1.5*: is féidir linn a dhéanamh gan comhad
Os cionn LIKE Cuireadh cosc orainn sórtáil mhícheart a úsáid. Ach is féidir é a “shocrú ar an gcosán ceart” tríd an oibreoir ÚSÁID a shonrú:
De réir réamhshocraithe glactar leis
ASC. Ina theannta sin, is féidir leat ainm oibreora sórtála ar leith a shonrú i gclásalUSING. Caithfidh an t-oibreoir sórtála a bheith ina bhall den líon is lú nó níos mó ná de theaghlach éigin oibreoirí B-crann.ASCde ghnáth comhionannUSING <иDESCde ghnáth comhionannUSING >.
Is é ár gcás, "níos lú". ~<~:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2: conas a iompaíonn iarratais géar
Anois fágaimid ár n-iarratas “suanbhruith” ar feadh sé mhí nó bliain, agus tá ionadh orainn é a fháil arís “ag an mbarr” le táscairí ar “phumpáil” iomlán laethúil na cuimhne (maoláin roinnte buailte) isteach 5.5TB - is é sin, fiú níos mó ná mar a bhí sé ar dtús.
Ní hea, ar ndóigh, tá fás tagtha ar ár ngnó agus tá méadú tagtha ar ár n-ualach oibre, ach ní ar an méid céanna! Ciallaíonn sé seo go bhfuil rud éigin iascach anseo - déanaimis é a dhéanamh amach.
2.1: breith na glaoireachta
Ag am éigin, bhí foireann forbartha eile ag iarraidh go bhféadfaí “léim” a dhéanamh ó chuardach tapa síntiús chuig an gclár leis na torthaí céanna ach leathnaithe. Cad is clárlann gan nascleanúint leathanach? A ligean ar scriú sé suas!
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;Anois bhíothas in ann clárlann na dtorthaí cuardaigh a thaispeáint le luchtú “leathanach ar leathanach” gan strus ar bith don fhorbróir.
Ar ndóigh, i ndáiríre, le haghaidh gach leathanach sonraí ina dhiaidh sin léitear níos mó agus níos mó (go léir ón am roimhe seo, a bheidh muid a shábháil, móide an "eireaball" riachtanach") - is é sin, tá sé seo antipattern soiléir. Ach bheadh sé níos ceart an cuardach a thosú ag an gcéad atriall eile ón eochair atá stóráilte sa chomhéadan, ach thart ar sin uair eile.
2.2: Ba mhaith liom rud éigin coimhthíocha
Ag pointe éigin theastaigh ón bhforbróir an sampla mar thoradh air a éagsúlú le sonraí ó thábla eile, ar seoladh an t-iarratas iomlán roimhe seo chuig CTE ina leith:
WITH q AS (
...
LIMIT <N> + 10
)
SELECT
*
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
q
LIMIT 10 OFFSET <N>;Agus mar sin féin, níl sé go dona, ós rud é nach ndéantar an subquery a mheas ach amháin le haghaidh 10 dtaifead a sheoltar ar ais, mura bhfuil ...
2.3: Tá DISTINCT gan chiall agus gan trócaire
Áit éigin i bpróiseas na héabhlóide sin ón 2ú subquery Fuair caillte NOT LIKE coinníoll. Tá sé soiléir go bhfuil tar éis seo UNION ALL thosaigh sé ag filleadh roinnt iontrálacha faoi dhó - le fáil ar dtús ag tús na líne, agus ansin arís - ag tús an chéad fhocail den líne seo. Sa teorainn, d'fhéadfadh gach taifead den dara focheist teacht le taifid an chéad fhocheist.
Cad a dhéanann forbróir in ionad an chúis a chuardach?.. Gan cheist!
- dúbailte an méid samplaí bunaidh
- iarratas a dhéanamh DISTINCTa fháil ach cásanna aonair de gach líne
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>;Is é sin, tá sé soiléir go bhfuil an toradh, sa deireadh, díreach mar an gcéanna, ach tá an seans "eitilt" isteach sa 2ú CTE subquery i bhfad níos airde, agus fiú gan é seo, níos inléite go soiléir.
Ach ní hé seo an rud is brónach. Ós rud é d'iarr an forbróir a roghnú DISTINCT ní le haghaidh réimsí sonracha, ach le haghaidh gach réimse ag an am céanna taifid, ansin cuireadh an réimse sub_query - toradh an tsubquery - san áireamh go huathoibríoch ann. Anois, a fhorghníomhú DISTINCT, bhí ar an mbunachar sonraí a fhorghníomhú cheana féin ní 10 bhfocheist, ach go léir <2 * N> + 10!
2.4: comhar thar aon rud eile!
Mar sin, bhí cónaí ar na forbróirí - níor bhac siad, mar is léir nach raibh a dhóthain foighne ag an úsáideoir an chlár a “choigeartú” chuig luachanna suntasacha N le moilliú ainsealach maidir le gach “leathanach” a fháil ina dhiaidh sin.
Go dtí gur tháinig forbróirí ó roinn eile chucu agus theastaigh uathu modh chomh áisiúil a úsáid le haghaidh cuardaigh atriallach - is é sin, tógann muid píosa ó roinnt sampla, é a scagadh de réir coinníollacha breise, tarraing an toradh, ansin an chéad phíosa eile (atá inár gcás trí N a mhéadú), agus mar sin de go dtí go líonfaimid an scáileán.
Go ginearálta, san eiseamal gafa Shroich N luachanna beagnach 17K, agus in aon lá amháin cuireadh i gcrích ar a laghad 4K de na hiarratais sin “ar feadh an tslabhra”. Bhí an ceann deireanach acu scanraithe go dána ag 1GB cuimhne in aghaidh an atriallta...
Ar an iomlán
Foinse: will.com