ʻO nā kaukani o nā mana hoʻokele mai nā keʻena kūʻai aku ma ka ʻāina he ʻumi kaukani o nā pilina i kēlā me kēia lā - nā ʻike o ke kamaʻilio ʻana me nā mea kūʻai aku a i ʻole nā mea kūʻai aku. A no kēia, pono ʻoe e ʻimi mua i kahi mea kūʻai aku, a ʻoi aku ka wikiwiki. A hana pinepine kēia ma ka inoa.
No laila, ʻaʻole ia he mea kupanaha, ke ʻimi hou nei i nā nīnau "kaumaha" ma kekahi o nā ʻikepili i hoʻouka ʻia - kā mākou ponoʻī. , loaʻa iaʻu "ma luna" noi no ka huli "wikiwiki" ma ka inoa no nā kāleka hui.
Eia kekahi, ua hōʻike ʻia kahi hoʻokolokolo hou i kahi laʻana hoihoi ʻo ka loiloi mua a laila ka hoʻohaʻahaʻa hana noi me kona hoʻomaʻamaʻa ʻana e kekahi mau hui, ua hana kēlā me kēia me ka manaʻo maikaʻi loa.
0: he aha ka mea hoʻohana i makemake ai?
[KDPV ]
He aha ka manaʻo o ka mea hoʻohana ke kamaʻilio e pili ana i kahi hulina "wikiwiki" ma ka inoa? Aneane ʻaʻole ia he hulina "ʻoiaʻiʻo" no kahi substring like ... LIKE '%роза%' - no ka mea, ʻaʻole wale ka hopena 'Розалия' и 'Магазин Роза'aka, 'Гроза' a pēlā pū 'Дом Деда Мороза'.
Manaʻo ka mea hoʻohana i ka pae o kēlā me kēia lā āu e hāʻawi ai iā ia huli ma ka hoomaka ana o ka olelo ma ke poʻo inoa a e hoʻopili pono i kēlā hoʻomaka ma komo. A e hana ʻoe kokoke koke - no ka hoʻokomo waena.
1: kaupalena i ka hana
A ʻoi aku ka nui, ʻaʻole e komo kikoʻī ke kanaka 'роз магаз', no laila pono ʻoe e ʻimi i kēlā me kēia huaʻōlelo ma ka prefix. ʻAʻole, ʻoi aku ka maʻalahi o ka mea hoʻohana i ka pane ʻana i kahi hōʻailona wikiwiki no ka huaʻōlelo hope loa ma mua o ka "hoʻohaʻahaʻa ʻana" i nā mea ma mua - e nānā i ke ʻano o ka ʻimi ʻana i kēia.
Nui pololei ʻO ka hoʻokumu ʻana i nā koi no ka pilikia ma mua o ka hapalua o ka hopena. I kekahi manawa e hoʻohana pono i ka loiloi hihia .
He aha ka hana a ka mea hoʻomohala abstract?
1.0: ʻenekini huli waho
ʻAe, paʻakikī ka ʻimi ʻana, ʻaʻohe oʻu makemake e hana i kekahi mea - e hāʻawi mākou iā devops! E hoʻokuʻu iā lākou i kahi ʻenekini huli ma waho o ka waihona: Sphinx, ElasticSearch,...
ʻO kahi koho hana, ʻoiai ʻoi aku ka hana ma ke ʻano o ka synchronization a me ka wikiwiki o nā loli. ʻAʻole naʻe i kā mākou hihia, no ka mea, ua hana ʻia ka ʻimi no kēlā me kēia mea kūʻai aku i loko o ke ʻano o kāna ʻikepili moʻokāki. A he ʻano kiʻekiʻe loa ka ʻikepili - a inā ua hoʻokomo ka luna i ke kāleka 'Магазин Роза', a laila ma hope o 5-10 kekona e hoʻomanaʻo paha ʻo ia ua poina ʻo ia e hōʻike i kāna leka uila ma laila a makemake ʻo ia e ʻimi a hoʻoponopono.
No laila - e huli "pololei ma ka waihona". ʻO ka mea pōmaikaʻi, ʻae ʻo PostgreSQL iā mākou e hana i kēia, ʻaʻole hoʻokahi wale nō koho - e nānā mākou iā lākou.
1.1: "ʻoiaʻiʻo" substring
Hoʻopili mākou i ka huaʻōlelo "substring". Akā, no ka huli kuhikuhi ma ka substring (a me nā ʻōlelo maʻamau!) aia kahi maikaʻi loa ! Ma laila wale nō e pono e hoʻokaʻawale pololei.
E ho'āʻo kāua e lawe i kēia pāpaʻi e hoʻomaʻamaʻa i ke kumu hoʻohālike:
CREATE TABLE firms(
id
serial
PRIMARY KEY
, name
text
);Hoʻouka mākou i 7.8 miliona mau moʻolelo o nā hui maoli ma laila a kuhikuhi iā lākou:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);E ʻimi kākou i nā moʻolelo he 10 mua no ka huli ʻana i waena:
SELECT
*
FROM
firms
WHERE
lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;
ʻAe, ʻo ia ... 26ms, 31MB heluhelu ʻikepili a ʻoi aku ma mua o 1.7K mau moʻolelo kānana - no 10 mau mea i ʻimi ʻia. He kiʻekiʻe loa nā kumukūʻai o luna, ʻaʻole anei he mea ʻoi aku ka maikaʻi?
1.2: huli ma ka kikokikona? ʻO FTS!
ʻOiaʻiʻo, hāʻawi ʻo PostgreSQL i kahi mana ikaika loa (Full Text Search), me ka hiki ke hana i ka huli mua. He koho maikaʻi loa, ʻaʻole pono ʻoe e hoʻokomo i nā hoʻonui! E ho'āʻo kākou:
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;
Eia ka hoʻohālikelike o ka hoʻokō ʻana i nā nīnau i kōkua iki iā mākou, e ʻoki ana i ka manawa i ka hapalua 11ms. A pono mākou e heluhelu i 1.5 mau manawa liʻiliʻi - i ka huina 20MB. Eia naʻe, ʻoi aku ka liʻiliʻi, ʻoi aku ka maikaʻi, no ka mea ʻoi aku ka nui o ka leo a mākou e heluhelu ai, ʻoi aku ka kiʻekiʻe o ka loaʻa ʻana o kahi huna huna, a ʻo kēlā me kēia ʻaoʻao o ka ʻikepili i heluhelu ʻia mai ka diski he "brakes" no ka noi.
1.3: LIKE LIKE LIKE
He maikaʻi ka noi mua no nā mea a pau, akā inā ʻoe e huki iā ia i hoʻokahi haneli tausani manawa i ka lā, e hiki mai ana 2TB heluhelu ʻikepili. Ma ka hihia maikaʻi loa, mai ka hoʻomanaʻo, akā inā ʻaʻole ʻoe i laki, a laila mai ka disk. No laila e ho'āʻo kākou e hoʻoemi.
E hoʻomanaʻo kākou i ka mea a ka mea hoʻohana e makemake ai e ʻike ʻO ka mua "e hoʻomaka me ka ...". No laila, aia kēia i kona ʻano maʻemaʻe me ke kōkuaʻana o text_pattern_ops! A inā "ʻaʻole lawa" mākou a hiki i nā moʻolelo 10 a mākou e ʻimi nei, a laila pono mākou e hoʻopau i ka heluhelu ʻana iā lākou me ka hoʻohana ʻana i ka huli FTS:
CREATE INDEX ON firms(lower(name) text_pattern_ops);SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
LIMIT 10;
Hana maikaʻi loa - huina 0.05ms a ʻoi aku ma mua o 100KB heluhelu! Ua poina wale makou hoʻokaʻawale ma ka inoai ʻole e nalowale ka mea hoʻohana i nā hopena:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
ʻAe, ʻaʻole maikaʻi loa kekahi mea - me he mea lā aia kahi index, akā lele ka hoʻokaʻawale ʻana ma mua o ia ...
1.4: "hoʻopau me kahi faila"
Akā aia kahi papa kuhikuhi e hiki ai iā ʻoe ke ʻimi ma ka laulā a hoʻohana mau i ka hoʻonohonoho maʻamau - btree mau!
CREATE INDEX ON firms(lower(name));ʻO ka noi wale nō e "ohi lima ʻia":
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
lower(name)
LIMIT 10;
Maikaʻi - nā hana hoʻokaʻawale, a ʻo ka hoʻohana ʻana i nā kumuwaiwai e mau ana "microscopic", ʻoi aku ka maikaʻi ma mua o ka FTS "maʻemaʻe".! ʻO nā mea a pau i koe e hoʻohui i kahi noi hoʻokahi:
(
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;
E hoʻomaopopo ua hoʻokō ʻia ka subquery ʻelua ina wale no ka mea mua i hoi mai malalo o ka mea i manaoia ka hope loa LIMIT helu o na laina. Ke kamaʻilio nei wau e pili ana i kēia ʻano o ka huli ʻana i ka nīnau .
No laila ʻae, loaʻa iā mākou ka btree a me ka gin ma ka papaʻaina, akā ma ka helu helu ʻike ʻia kēlā liʻiliʻi ma mua o 10% o nā noi i hiki i ka hoʻokō ʻana i ka poloka lua. ʻO ia hoʻi, me nā palena maʻamau i ʻike mua ʻia no ka hana, ua hiki iā mākou ke hōʻemi i ka nui o ka hoʻohana ʻana i nā kumuwaiwai kikowaena ma kahi o hoʻokahi kaukani manawa!
1.5*: hiki iā mākou ke hana me ka ʻole o kahi faila
I luna LIKE Kāohi ʻia mākou mai ka hoʻohana ʻana i ka hoʻokaʻina hewa. Akā hiki ke "hoʻonoho ʻia ma ke ala pololei" ma ke kuhikuhi ʻana i ka mea hoʻohana USING:
Ma ka paʻamau ua manaʻo ʻia
ASC. Eia hou, hiki iā ʻoe ke kuhikuhi i ka inoa o kahi mea hoʻohana kikoʻī i loko o kahi paukūUSING. Pono ka mea hoʻonohonoho ʻano he lālā o ka liʻiliʻi a ʻoi aku paha ma mua o kekahi ʻohana o nā mea hoʻokele B-lāʻau.ASClike maʻamauUSING <иDESClike maʻamauUSING >.
I kā mākou hihia, "emi" ʻo ia ~<~:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2: pehea ka huli ʻana o nā noi
I kēia manawa ke waiho nei mākou i kā mākou noi e "simmer" no ʻeono mahina a i ʻole makahiki, a kāhāhā mākou i ka ʻike hou ʻana iā ia "ma luna" me nā hōʻailona o ka nui o kēlā me kēia lā "pumping" o ka hoʻomanaʻo (pākuʻi like pū) in 5.5TB - ʻo ia hoʻi, ʻoi aku ka nui ma mua o ka mua.
ʻAʻole, ʻoiaʻiʻo, ua ulu kā mākou ʻoihana a ua hoʻonui kā mākou hana, akā ʻaʻole i ka nui like! ʻO ia hoʻi, he iʻa kekahi mea ma ʻaneʻi - e noʻonoʻo kākou.
2.1: ka hānau ʻana o ka paging
I kekahi manawa, makemake kekahi pūʻulu hoʻomohala e hiki ke "lele" mai kahi hulina subscript wikiwiki i ke kākau inoa me nā hopena like, akā i hoʻonui ʻia. He aha ka papa inoa me ka ʻole o ka hoʻokele ʻaoʻao? E hoʻopau kāua!
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;I kēia manawa ua hiki ke hōʻike i ka hoʻopaʻa inoa o nā hopena hulina me ka hoʻouka ʻana i ka "page-by-page" me ke koʻikoʻi ʻole no ka mea hoʻomohala.
ʻOiaʻiʻo, ʻoiaʻiʻo, no kēlā me kēia ʻaoʻao o ka ʻikepili i heluhelu ʻia a ʻoi aku ka nui (ʻo nā mea a pau mai ka manawa ma mua, e hoʻolei mākou, me ka "huelo") pono) - ʻo ia hoʻi, he antipattern maopopo kēia. Akā ʻoi aku ka maikaʻi o ka hoʻomaka ʻana i ka ʻimi ma ka ʻike hou mai ke kī i mālama ʻia i loko o ka interface, akā e pili ana i kēlā manawa ʻē aʻe.
2.2: Makemake au i kahi mea ʻokoʻa
I kekahi manawa makemake ka mea hoʻomohala e hoʻokaʻawale i ka hāpana hopena me ka ʻikepili mai ka papa ʻaina ʻē aʻe, kahi i hoʻouna ʻia ai ka noi holoʻokoʻa mua iā CTE:
WITH q AS (
...
LIMIT <N> + 10
)
SELECT
*
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
q
LIMIT 10 OFFSET <N>;A no laila, ʻaʻole maikaʻi, no ka mea ke loiloi ʻia ka subquery no 10 mau moʻolelo i hoʻihoʻi ʻia, inā ʻaʻole ...
2.3: ʻAʻole noʻonoʻo a aloha ʻole ʻo DISTINCT
Ma kahi o ke kaʻina hana o ia evolution mai ka subquery 2nd ua nalowale NOT LIKE kūlana. Ua maopopo ma hope o kēia UNION ALL hoʻomaka e hoʻi ʻelua mau helu - loaʻa mua ma ka hoʻomaka o ka laina, a laila hou - ma ka hoʻomaka o ka huaʻōlelo mua o kēia laina. Ma ka palena, hiki i nā moʻolelo āpau o ka subquery 2 ke hoʻohālikelike i nā moʻolelo o ka mua.
He aha ka hana a ka mea hoʻomohala ma mua o ka ʻimi ʻana i ke kumu?.. ʻAʻohe nīnau!
- pālua ka nui kumu hoohalike
- hoʻopili DISTINCTe kiʻi i hoʻokahi wale nō manawa o kēlā me kēia laina
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>;ʻO ia hoʻi, ua maopopo ka hopena, i ka hopena, ua like loa ia, akā ʻo ka manawa o ka "lele" i loko o ka 2nd CTE subquery ua lilo i mea kiʻekiʻe loa, a me ka ʻole o kēia, maopopo loa hiki ke heluhelu.
Akā ʻaʻole kēia ka mea kaumaha loa. Mai ka mea hoʻomohala i noi e koho DISTINCT ʻaʻole no nā mea kikoʻī, akā no nā kahua āpau i ka manawa hoʻokahi nā moʻolelo, a laila ua hoʻokomo koke ʻia ke kahua sub_query - ka hopena o ka subquery. I kēia manawa, e hoʻokō DISTINCT, pono e ho'okō 'ia ka waihona ʻaʻole 10 subqueries, akā pau <2 * N> + 10!
2.4: ka launa pū ʻana ma luna o nā mea a pau!
No laila, noho nā mea hoʻomohala - ʻaʻole lākou i pilikia, no ka mea ʻaʻole lawa ka hoʻomanawanui o ka mea hoʻohana e "hoʻoponopono" i ka hoʻopaʻa inoa i nā waiwai nui N me ka lohi mau i ka loaʻa ʻana o kēlā me kēia "ʻaoʻao".
A hiki i nā mea hoʻomohala mai kahi keʻena ʻē aʻe i hele mai iā lākou a makemake lākou e hoʻohana i kahi ala kūpono no ka huli hou ana - ʻo ia hoʻi, lawe mākou i kahi ʻāpana mai kahi laʻana, kānana iā ia e nā kūlana hou aʻe, e huki i ka hopena, a laila ʻo ka ʻāpana aʻe (i kā mākou hihia e hoʻokō ʻia ma ka hoʻonui ʻana i ka N), a pēlā aku a hoʻopiha mākou i ka pale.
Ma keʻano laulā, i ka specimen i hopuʻia Loaʻa ʻo N i nā waiwai kokoke i 17K, a i ka lā hoʻokahi wale nō i hoʻokō ʻia ma kahi o 4K o ia mau noi "ma ke kaulahao". ʻO ka hope o lākou i nānā wiwo ʻole ʻia e 1GB o ka hoʻomanaʻo no kēlā me kēia ʻike...
Hōʻuluʻulu
Source: www.habr.com