ProHoster > Blog > fitantanan-draharaha > Ny tantaran'ny fanadihadiana SQL iray

Ny tantaran'ny fanadihadiana SQL iray

Tamin'ny volana Desambra lasa teo dia nahazo tatitra momba ny bibikely mahaliana avy amin'ny ekipa mpanohana VWO aho. Ny fotoana fandefasana ny iray amin'ireo tatitra analyse ho an'ny mpanjifan'ny orinasa lehibe dia toa voarara. Ary satria io no sehatra iandraiketako dia nifantoka avy hatrany tamin'ny famahana ny olana aho.

prehistory

Mba hanazavana ny zavatra resahiko dia holazaiko anao kely ny momba ny VWO. Ity dia sehatra ahafahanao manomboka fampielezan-kevitra isan-karazany amin'ny tranokalanao: manao andrana A/B, manara-maso ny mpitsidika sy ny fiovam-po, manadihady ny fantson'ny varotra, mampiseho sarintany hafanana ary milalao firaketana fitsidihana.

Fa ny zava-dehibe indrindra amin'ny sehatra dia ny tatitra. Ireo asa rehetra voalaza etsy ambony ireo dia mifamatotra. Ary ho an'ny mpanjifan'ny orinasa, fampahalalana be dia be dia tsy ilaina fotsiny raha tsy misy sehatra matanjaka manolotra azy amin'ny endrika analytics.

Amin'ny fampiasana ny sehatra, azonao atao ny manao fangatahana kisendrasendra amin'ny angon-drakitra lehibe. Ity misy ohatra tsotra:

Asehoy ny kitika rehetra amin'ny pejy "abc.com" manomboka amin'ny <daty d1> ka hatramin'ny <daty d2> ho an'ny olona nampiasa Chrome OR (any Eoropa SY nampiasa iPhone)

Tandremo ny mpandraharaha Boolean. Izy ireo dia azon'ny mpanjifa ao amin'ny seha-pifanakalozan-kevitra mba hanaovana fanontaniana saro-takarina tsy misy dikany mba hahazoana santionany.

Mangataka miadana

Niezaka nanao zavatra izay tokony hiasa haingana ilay mpanjifa resahina:

Asehoy ny firaketana fivoriana rehetra ho an'ireo mpampiasa nitsidika pejy misy URL misy "/asa"

Nanana fifamoivoizana an-taonina ity tranokala ity ary nitahiry URL tsy manam-paharoa mihoatra ny iray tapitrisa izahay ho an'izany. Ary te hahita mΓ΄dely URL tsotra izy ireo mifandraika amin'ny maodelin'ny orinasany.

Fanadihadiana mialoha

Andeha hojerentsika ny zava-mitranga ao amin'ny tahiry. Ity ambany ity ny fangatahana SQL miadana voalohany:

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 ;

Ary ireto ny fotoana:

Fotoana nomanina: 1.480 ms Fotoana hanatanterahana: 1431924.650 ms

Ny fanontaniana dia nandady andalana 150 arivo. Nampiseho pitsopitsony mahaliana roa ny drafitry ny fangataham-panontaniana, saingy tsy misy olana miharihary.

Andeha isika handinika bebe kokoa ny fangatahana. Araka ny hitanao dia manao izany izy JOIN latabatra telo:

  1. fivoriana: hanehoana fampahalalana momba ny fivoriana: navigateur, mpampiasa mpampiasa, firenena, sy ny sisa.
  2. recording_data: URL voarakitra, pejy, faharetan'ny fitsidihana
  3. URLs: Mba hisorohana ny fanaovana kopia URL be dia be, dia tehirizinay ao anaty latabatra mitokana izy ireo.

Mariho ihany koa fa ny latabatra rehetra dia efa voazarazara amin'ny account_id. Amin'izany fomba izany, ny toe-javatra iray izay miteraka olana ho an'ny hafa ny kaonty lehibe iray dia tsy tafiditra ao.

Mitady famantarana

Rehefa dinihina tokoa, dia hita fa misy zavatra tsy mety amin'ny fangatahana manokana. Tena ilaina ny mijery akaiky ity andalana ity:

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

Ny eritreritra voalohany dia angamba satria ILIKE amin'ireo URL lava rehetra ireo (manana mihoatra ny 1,4 tapitrisa izahay tsy manam-paharoa Ny URL voaangona ho an'ity kaonty ity) dia mety hijaly.

Saingy tsia, tsy izany no hevitra!

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

Time: 5231.765 ms

Ny fangatahana fitadiavana mΓ΄dely dia 5 segondra ihany. Ny fitadiavana lamina amin'ny URL tokana tokana dia mazava ho azy fa tsy olana.

Ny voarohirohy manaraka ao amin'ny lisitra dia maromaro JOIN. Angamba ny fampiasana tafahoatra azy ireo no nahatonga ny fihemorana? matetika JOINI's no kandida miharihary indrindra amin'ny olana momba ny fampisehoana, saingy tsy nino aho fa mahazatra ny raharahanay.

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

Ary tsy izany koa no zava-nitranga taminay. JOIN's nivadika ho tena haingana.

Famaritana ny faribolan'ny ahiahiana

Vonona ny hanomboka hanova ny fangatahana aho mba hanatrarana ny fanatsarana mety hitranga. Namolavola hevitra fototra 2 izaho sy ny ekipako:

  • Ampiasao ny EXISTS ho an'ny URL subquery: Te-hijery indray izahay raha misy olana amin'ny subquery ho an'ny URL. Ny fomba iray hanatanterahana izany dia ny fampiasana tsotra EXISTS. EXISTS afaka manatsara ny fampandehanana satria mifarana avy hatrany izy raha vao mahita ny tady tokana mifanaraka amin'ny fepetra.

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

Eny, eny. Subquery rehefa nofonosina EXISTS, manao ny zava-drehetra haingana be. Ny fanontaniana lojika manaraka dia ny antony ny fangatahana amin'ny JOIN-ami sy ny subquery mihitsy no haingana, fa tena miadana miaraka?

  • Mamindra ny subquery mankany amin'ny CTE : Raha haingana ho azy ny fanontaniana dia azonao atao ny manao kajy ny vokatra haingana aloha ary avy eo manome izany ho an'ny fangatahana lehibe.

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;

Mbola niadana be anefa izany.

Mitady ilay meloka

Nandritra izany fotoana izany, dia nisy zavatra kely iray nipoitra teo anoloan'ny masoko, izay nesoriko tsy tapaka. Koa satria tsy nisy na inona na inona tavela, dia nanapa-kevitra ny hijery azy koa aho. miresaka momba ny && mpandraharaha. veloma EXISTS nohatsaraina fotsiny ny fampisehoana && no hany anton-javatra mahazatra sisa amin'ny dikan-teny rehetra amin'ny fangatahana miadana.

Mijery tahirin-kevitra, hitantsika izany && ampiasaina rehefa mila mitady singa iraisana eo anelanelan'ny array roa ianao.

Amin'ny fangatahana voalohany dia izao:

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

Midika izany fa manao fikarohana lamina ao amin'ny URL-nay izahay, avy eo mahita ny fihaonan-dalana miaraka amin'ny URL rehetra misy lahatsoratra mahazatra. Somary mampisafotofoto ihany izany satria ny "urls" eto dia tsy manondro ny latabatra misy ny URL rehetra, fa ny "urls" ao amin'ny latabatra. recording_data.

Miaraka amin'ny ahiahy mitombo momba ny &&, niezaka nitady fanamafisana ho azy ireo aho tao amin'ny drafitry ny fangatahana natao EXPLAIN ANALYZE (Efa nanana drafitra voatahiry aho, saingy matetika aho no mahazo aina kokoa amin'ny fanandramana SQL noho ny manandrana mahatakatra ny tsy fahampian'ny mpandinika fanontaniana).

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

Nisy andalana maromaro tamin'ny sivana ihany &&. Midika izany fa tsy lafo ihany ity fandidiana ity fa efa im-betsaka ihany koa.

Nanandrana izany aho tamin'ny fanavahana ny fepetra

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[]

Niadana ity fanontaniana ity. Satria ny JOIN-s dia haingana ary ny subqueries dia haingana, ny hany sisa tavela && mpandraharaha.

Hetsika fototra fotsiny izany. Mila mikaroka ny latabatra URL manontolo isika mba hikaroka lamina iray, ary mila mahita sampana. Tsy afaka mikaroka mivantana amin'ny alΓ lan'ny firaketana URL izahay, satria ID resahina fotsiny ireo urls.

Teny an-dalana ho amin'ny vahaolana

&& miadana satria lehibe ny seta roa. Haingana ny fandidiana raha soloiko urls amin'ny { "http://google.com/", "http://wingify.com/" }.

Nanomboka nitady fomba hanaovana ny fifanenjehana ao amin'ny Postgres aho nefa tsy mampiasa &&, nefa tsy nahomby.

Tamin'ny farany dia nanapa-kevitra izahay fa hamaha ny olana irery ihany: omeo ahy ny zava-drehetra urls andalana izay mifanaraka amin'ny lamina ny URL. Raha tsy misy fepetra fanampiny dia ho - 

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%'

raha tokony ho JOIN syntax dia nampiasa subquery aho ary nanitatra recording_data.urls array mba hahafahanao mampihatra mivantana ny fepetra ao WHERE.

Ny zava-dehibe indrindra eto dia izany && ampiasaina hijerena raha misy URL mifanandrify ny fidirana nomena. Raha manjavozavo kely ianao dia ho hitanao fa mandeha amin'ny singa iray (na andalana amin'ny latabatra) ity hetsika ity ary mijanona rehefa feno ny fepetra iray. Tsy mampahatsiahy anao na inona na inona? eny, EXISTS.

Satria amin'ny recording_data.urls azo lazaina avy any ivelan'ny contexte subquery, rehefa mitranga izany dia afaka miverina amin'ny namantsika taloha isika EXISTS ary afeno miaraka aminy ny subquery.

Mampitambatra ny zava-drehetra, mahazo ny fangatahana farany natao tsara:

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%'
    );

Ary ny fotoam-pivoriana farany Time: 1898.717 ms Fotoana hankalazana?!?

Tsy dia haingana! Voalohany dia mila manamarina ny marina ianao. Tena niahiahy aho EXISTS optimization satria manova ny lojika ho vita teo aloha. Mila mahazo antoka isika fa tsy nampiana hadisoana tsy hita maso tamin'ny fangatahana.

Fitsapana tsotra no natao count(*) amin'ny fanontaniana miadana sy haingana ho an'ny angon-drakitra maro samihafa. Avy eo, ho an'ny ampahany kely amin'ny angon-drakitra, nanamarina tamin'ny tanana aho fa marina ny valiny rehetra.

Ny fitsapana rehetra dia nanome vokatra tsara tsy tapaka. Namboarinay ny zava-drehetra!

Lesona nianarana

Betsaka ny lesona azo tsoahina avy amin'ity tantara ity:

  1. Tsy mitantara ny tantara manontolo ny drafitry ny fanontaniana, fa afaka manome fanazavana
  2. Tsy ireo tena voarohirohy hatrany no tena tompon’antoka
  3. Ny fanontaniana miadana dia azo zaraina mba hanavahana ny tavoahangin-javatra
  4. Tsy ny optimizations rehetra dia mampihena ny natiora
  5. ny fampiasana ny EXIST, raha azo atao, dia mety hitarika amin'ny fisondrotry ny vokatra

famaranana

Nandeha tamin'ny fotoana fangataham-panontaniana ~ 24 minitra ka hatramin'ny 2 segondra izahay - fitomboana zava-bita lehibe! Na dia nivoaka lehibe aza ity lahatsoratra ity, ny andrana rehetra nataontsika dia nitranga tao anatin'ny iray andro, ary tombanana fa naharitra 1,5 ka hatramin'ny 2 ora ho an'ny fanatsarana sy fitiliana.

Ny SQL dia fiteny mahafinaritra raha tsy matahotra azy ianao, fa miezaha mianatra sy mampiasa azy. Amin'ny fahafantaranao tsara ny fomba fanatanterahana ny fangatahana SQL, ny fomba famoahan'ny angon-drakitra drafitry ny fangatahana, ny fomba fiasan'ny fanondroana, ary ny haben'ny angona ifampiraharahanao, dia mety hahomby ianao amin'ny fanatsarana ny fangatahana. Zava-dehibe ihany koa anefa ny manohy manandrana fomba fiasa samihafa ary manapaka tsikelikely ny olana, mahita ireo bottlenecks.

Ny ampahany tsara indrindra amin'ny fanatrarana vokatra toy izany dia ny fanatsarana ny hafainganam-pandeha hita maso sy hita maso - izay misy tatitra iray izay tsy nety nampitondraina akory teo aloha izao dia mipetaka eo no ho eo.

Isaorana manokana ny ry namako araka ny baikon'i Aditya Mishra, Aditya Gauru ΠΈ Varun Malhotra ho an'ny atidoha sy Dinkar Pandir noho ny fitadiavana fahadisoana lehibe tamin'ny fangatahanay farany alohan'ny nanaovany veloma azy!

Source: www.habr.com

Add a comment