ProHoster > Блог > Nchịkwa > Akụkọ otu nyocha SQL

Akụkọ otu nyocha SQL

Disemba gara aga enwetara m akụkọ ahụhụ na-adọrọ mmasị site na ndị otu nkwado VWO. Oge ntinye maka otu n'ime akụkọ nyocha maka nnukwu onye ahịa ụlọ ọrụ dị ka ihe mgbochi. Ebe ọ bụ na nke a bụ mpaghara ọrụ m, etinyere m ozugbo na idozi nsogbu ahụ.

prehistory

Iji mee ka o doo anya ihe m na-ekwu, a ga m agwa gị ntakịrị gbasara VWO. Nke a bụ ikpo okwu nke ị nwere ike ịmalite mkpọsa dị iche iche ezubere iche na webụsaịtị gị: mee nnwale A/B, soro ndị ọbịa na ntughari, nyochaa oghere ahịa, gosipụta maapụ okpomọkụ ma kpọọ ndekọ nleta.

Mana ihe kachasị mkpa gbasara ikpo okwu bụ ịkọ akụkọ. Ọrụ niile ndị a dị n'elu jikọtara ọnụ. Na maka ndị ahịa ụlọ ọrụ, nnukwu ozi ga-abụ ihe na-abaghị uru na-enweghị ikpo okwu dị ike nke na-enye ya n'ụdị nyocha.

Iji ikpo okwu, ị nwere ike ịme ajụjụ na-enweghị usoro na nnukwu data data. Nke a bụ ọmụmaatụ dị mfe:

Gosi pịa niile dị na peeji "abc.com" SITE NA <date d1> RUO <date d2> maka ndị ji Chrome OR (nọ na Europe NA ejiri ya mee ihe) iPhone)

Lezienụ anya na ndị ọrụ Boolean. Ha dị ndị ahịa nọ na interface ajụjụ ka ha mee ajụjụ mgbagwoju anya n'ụzọ aka ike iji nweta sample.

Arịrịọ nwayọ

Onye ahịa a na-ajụ ajụjụ na-anwa ime ihe n'uche kwesịrị ịrụ ọrụ ngwa ngwa:

Gosi ndekọ nnọkọ niile maka ndị ọrụ gara na ibe ọ bụla nwere URL nwere "/ọrụ"

Saịtị a nwere ọtụtụ okporo ụzọ ma anyị na-echekwa ihe karịrị otu nde URL pụrụ iche naanị maka ya. Ma ha chọrọ ịchọta template URL dị mfe nke metụtara ụdị azụmahịa ha.

Nnyocha mbido

Ka anyị leba anya n'ihe na-eme na nchekwa data. N'okpuru bụ ajụjụ SQL mbụ dị nwayọ:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND sessions.referrer_id = recordings_urls.id 
    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   ) 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

Ma ebe a bụ oge:

Oge ahaziri: 1.480 ms Oge ime ihe: 1431924.650 ms

Ajụjụ ahụ gbara ahịrị puku narị na iri ise. Onye na-eme atụmatụ ajụjụ ahụ gosipụtara nkọwapụta ole na ole na-atọ ụtọ, mana enweghị ihe mgbochi doro anya.

Ka anyị mụọ arịrịọ a ọzọ. Dị ka ị pụrụ ịhụ, ọ na-eme ya JOIN tebụl atọ:

  1. sessions: iji gosipụta ozi nnọkọ: ihe nchọgharị, onye ọrụ, obodo, na ihe ndị ọzọ.
  2. ndekọ_dataURL ndị edekọrọ, ibe, ogologo oge nleta
  3. urls: Iji zere imegharị URL ndị buru ibu, anyị na-echekwa ha na tebụl dị iche.

Rịba ama na tebụl anyị niile ekewalarị site na account_id. Ya mere, a na-ewepụ ọnọdụ mgbe, n'ihi otu nnukwu akaụntụ, nsogbu bilitere maka ndị ọzọ.

Na-achọ akara

Mgbe anyị nyochachara nke ọma, anyị na-ahụ na ọ dị ihe na-adịghị mma na otu arịrịọ. Ọ dị mma ileru anya nke ọma na ahịrị a:

urls && array(
	select id from acc_{account_id}.urls 
	where url  ILIKE  '%enterprise_customer.com/jobs%'
)::text[]

Echiche mbụ bụ na ikekwe n'ihi na ILIKE na ogologo URL ndị a niile (anyị nwere ihe karịrị nde 1,4 pụrụ iche URL anakọtara maka akaụntụ a) arụmọrụ nwere ike ịta ahụhụ.

Ma mba, nke ahụ abụghị isi ihe!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Time: 5231.765 ms

Arịrịọ ọchụchọ template n'onwe ya na-ewe naanị 5 sekọnd. Ịchọ ụkpụrụ na otu nde URL pụrụ iche abụghị nsogbu.

Onye ọzọ a na-enyo enyo na ndepụta ahụ bụ ọtụtụ JOIN. Ikekwe iji ha na-emebiga ihe ókè emewo ka a kwụsịlata? Na-emekarị JOIN's bụ ndị kacha pụta ìhè maka nsogbu arụmọrụ, mana ekwenyeghị m na ikpe anyị bụ nke a na-ahụkarị.

analytics_db=# SELECT
    count(*)
FROM
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data_0 as recording_data,
    acc_{account_id}.sessions_0 as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND sessions.referrer_id = recordings_urls.id
    AND r_time > to_timestamp(1542585600)
    AND r_time < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

Nke a abụghịkwa ikpe anyị. JOIN' tụgharịrị bụrụ ngwa ngwa.

Na-ebelata okirikiri ndị a na-enyo enyo

Adị m njikere ịmalite ịgbanwe ajụjụ a iji nweta nkwalite arụmọrụ ọ bụla enwere ike. Mụ na ndị otu m mepụtara echiche abụọ bụ isi:

  • Jiri EXISTS maka URL subquery: Anyị chọrọ ịlele ọzọ ma enwere nsogbu ọ bụla na subquery maka URL. Otu ụzọ iji nweta nke a bụ naanị iji EXISTS. EXISTS ike na-eme ka arụmọrụ dịkwuo mma ebe ọ na-agwụ ozugbo ozugbo ọ chọtara naanị eriri nke dabara na ọnọdụ ahụ.

SELECT
	count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data as recording_data,
    acc_{account_id}.sessions as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND  (  1 = 1  )
    AND sessions.referrer_id = recordings_urls.id
    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    AND r_time > to_timestamp(1547585600)
    AND r_time < to_timestamp(1549177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

Ọfọn, ee. Subquery mgbe ọbọp na EXISTS, na-eme ka ihe niile dị oke ngwa ngwa. Ajụjụ ezi uche na-esote bụ ihe kpatara arịrịọ ahụ na JOIN-ami na subquery n'onwe ya na-adị ngwa ngwa n'otu n'otu, mana ọ na-adị ngwa ngwa ọnụ?

  • Bugharịa subquery na CTE : Ọ bụrụ na ajụjụ ahụ bụ ngwa ngwa n'onwe ya, anyị nwere ike ịgbakọ nsonaazụ ngwa ngwa na mbụ wee nye ya na ajụjụ bụ isi.

WITH matching_urls AS (
    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%'
)

SELECT 
    count(*) FROM acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions,
    matching_urls
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id
    AND (urls && array(SELECT id from matching_urls)::text[])
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

Ma ọ ka dị nnọọ nwayọọ.

Ịchọta onye mere mpụ

N’oge a niile, otu obere ihe na-egbu maramara n’anya m, bụ́ nke m na-ewepụ mgbe niile. Ma ebe ọ bụ na ọ dịghị ihe ọzọ fọdụrụ, ekpebiri m ile ya anya. Ana m ekwu maka ya && onye ọrụ. Daalụ EXISTS naanị emelitere arụmọrụ && bụ naanị ihe fọdụrụ na-ahụkarị n'ofe ụdị ajụjụ ọ bụla nke ngwa ngwa.

Na-ele anya akwụkwọ, anyị na-ahụ na && ejiri mee ihe mgbe ịchọrọ ịchọta ihe ndị a na-ahụkarị n'etiti nhazi abụọ.

N'ime arịrịọ mbụ nke a bụ:

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

Nke pụtara na anyị na-eme nyocha ụkpụrụ na URL anyị, wee chọta njikọ ahụ na URL niile nwere posts nkịtị. Nke a bụ ihe mgbagwoju anya n'ihi na "urls" ebe a anaghị ezo aka na tebụl nwere URL niile, kama na kọlụm "urls" dị na tebụl. recording_data.

Na-eto eto na-enyo enyo banyere &&, Agbalịrị m ịchọta nkwenye maka ha na atụmatụ ajụjụ ewepụtara EXPLAIN ANALYZE (Enwelarị m atụmatụ echekwara, mana ọ na-adịkarị m mma ịnwale na SQL karịa ịgbalị ịghọta enweghị isi nke ndị nhazi ajụjụ).

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0))
                           Rows Removed by Filter: 52710

Enwere ọtụtụ ahịrị nzacha naanị site na &&. Nke pụtara na ọrụ a abụghị naanị ọnụ, kamakwa ọ rụrụ ọtụtụ ugboro.

M nwalere nke a site na ikewapụ ọnọdụ ahụ

SELECT 1
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data_30 as recording_data_30, 
    acc_{account_id}.sessions_30 as sessions_30 
WHERE 
	urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

Ajụjụ a adịchaghị nwayọ. N'ihi na JOIN-s na-agba ọsọ na subqueries bụ ngwa ngwa, naanị ihe fọdụrụ bụ && onye ọrụ.

Nke a bụ naanị isi ọrụ. Anyị na-achọ mgbe niile na tebụl URL niile dị n'okpuru ka ịchọọ ụkpụrụ, anyị na-achọkwa ụzọ nhịahụ mgbe niile. Anyị enweghị ike ịchọ site na ndekọ URL ozugbo, n'ihi na ndị a bụ naanị NJ na-ezo aka urls.

Na ụzọ ngwọta

&& ngwa ngwa n'ihi na tent abụọ ahụ buru ibu. Ọrụ ahụ ga-adị ngwa ngwa ma ọ bụrụ na m dochie ya urls on { "http://google.com/", "http://wingify.com/" }.

Amalitere m ịchọ ụzọ m ga-esi mee njikọ njikọ na Postgres na-ejighị ya &&, ma enweghị nnukwu ihe ịga nke ọma.

N'ikpeazụ, anyị kpebiri iji dozie nsogbu ahụ naanị: nye m ihe niile urls ahịrị nke URL dabara na ụkpụrụ. Enweghị ọnọdụ ndị ọzọ ọ ga-abụ - 

SELECT urls.url
FROM 
	acc_{account_id}.urls as urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id AND
	urls.url  ILIKE  '%jobs%'

Kama nke JOIN syntax M ka ji subquery wee gbasaa recording_data.urls N'usoro ka ị nwee ike itinye ọnọdụ ahụ ozugbo WHERE.

Ihe kacha mkpa ebe a bụ nke ahụ && A na-eji elele ma ntinye enyere nwere URL dabara adaba. Ọ bụrụ na ị squint ntakịrị, ị nwere ike ịhụ na ọrụ a na-agafe na ihe dị n'usoro (ma ọ bụ ahịrị nke tebụl) ma kwụsị mgbe ọnọdụ (egwuregwu) zutere. Ọ naghị echetara gị ihe ọ bụla? Ee, EXISTS.

Kemgbe gawa recording_data.urls enwere ike ịkọwa ya site na mpụga mpaghara subquery, mgbe nke a mere anyị nwere ike ịdakwasị enyi ochie anyị EXISTS ma kechie subquery na ya.

N'ịkọkọta ihe niile, anyị ga-enweta ajụjụ kachasị nke ikpeazụ:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0
    AND EXISTS(
        SELECT urls.url
        FROM 
            acc_{account_id}.urls as urls,
            (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) 
            AS unrolled_urls
        WHERE
            urls.id = unrolled_urls.rec_url_id AND
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

Na oge ndu ikpeazụ Time: 1898.717 ms Oge ime ememe?!?

Ọ bụghị ngwa ngwa! Mbụ ịkwesịrị ịlele izi ezi. Enwere m enyo nke ukwuu EXISTS njikarịcha ka ọ na-agbanwe mgbagha iji kwụsị na mbụ. Anyị kwesịrị ijide n'aka na anyị etinyebeghị njehie na-edoghị anya na arịrịọ ahụ.

Nnwale dị mfe ga-agba ọsọ count(*) na ma ajụjụ ngwa ngwa na ngwa ngwa maka ọnụ ọgụgụ dị ukwuu nke nhazi data dị iche iche. Mgbe ahụ, maka ntakịrị nkeji nke data ahụ, ejiri m aka m gosi na nsonaazụ niile ziri ezi.

Nnwale niile nyere nsonaazụ dị mma mgbe niile. Anyị doziri ihe niile!

Ihe Amụtara

Enwere ọtụtụ nkuzi a ga-amụta na akụkọ a:

  1. Atụmatụ ajụjụ anaghị akọ akụkọ ahụ dum, mana ha nwere ike ịnye ihe ngosi
  2. Ndị isi a na-enyo enyo abụghị mgbe niile bụ ndị na-eme ihe ọjọọ
  3. Enwere ike imebi ajuju ajuju iji weputa ihe di nkpa
  4. Ọ bụghị njikarịcha niile na-ebelata na okike
  5. Jiri EXIST, ebe enwere ike, nwere ike ibute mmụba dị egwu na nrụpụta

nkwubi

Anyị si na oge ajụjụ ~ nkeji 24 gaa na sekọnd 2 - nnukwu mmụba arụmọrụ! Ọ bụ ezie na isiokwu a bịara buru ibu, nyocha niile anyị mere mere n'otu ụbọchị, a na-eme atụmatụ na ha were n'etiti 1,5 na 2 awa maka nyocha na nyocha.

SQL bụ asụsụ magburu onwe ya ma ọ bụrụ na ị naghị atụ egwu ya, ma gbalịa ịmụta ma jiri ya. Site n'inwe nghọta nke ọma ka esi eme ajụjụ SQL, ka nchekwa data si ewepụta atụmatụ ajụjụ, ka ndeksi si arụ ọrụ, yana naanị nha data ị na-eme, ị nwere ike ime nke ọma n'ịkwalite ajụjụ. Otú ọ dị, ọ dịkwa mkpa ka ị nọgide na-anwale ụzọ dị iche iche ma jiri nwayọọ nwayọọ mebie nsogbu ahụ, na-achọta ihe mgbochi.

Akụkụ kachasị mma maka ị nweta nsonaazụ dị otú a bụ nkwalite ọsọ ọsọ a na-ahụ anya - ebe akụkọ nke na-agaghị adịbu ibu ugbu a na-ebu ihe fọrọ nke nta ka ọ bụrụ ozugbo.

Ekele pụrụ iche ndị enyi m n'iwu nke Aditya Mishra, Aditya Gauru и Varun Malhotra maka ntughari uche na Dinkar Pandir maka ịchọta njehie dị mkpa na arịrịọ ikpeazụ anyị tupu anyị emechaa kwuo nke ọma na ya!

isi: www.habr.com

Zụta nnabata ntụkwasị obi maka saịtị nwere nchekwa DDoS, sava VPS VDS 🔥 Zụta ebe nrụọrụ weebụ a pụrụ ịtụkwasị obi na nchekwa DDoS, sava VPS VDS | ProHoster