ProHoster > Blog > Rêveberî > Çîroka yek lêpirsîna SQL

Çîroka yek lêpirsîna SQL

Kanûna paşîn min raporek xeletiyek balkêş ji tîmê piştevaniya VWO wergirt. Dema barkirinê ji bo yek ji raporên analîtîk ji bo xerîdarek pargîdaniyek mezin qedexe xuya bû. Û ji ber ku ev qada berpirsiyariya min e, min tavilê bal kişand ser çareserkirina pirsgirêkê.

pêşdîrok

Ji bo ku ez eşkere bikim ka ez qala çi dikim, ez ê ji we re hinekî qala VWO bikim. Ev platformek e ku hûn pê re dikarin kampanyayên cihêreng ên armanckirî li ser malperên xwe bidin destpêkirin: ceribandinên A/B bikin, mêvanan û veguheztinê bişopînin, kavilê firotanê analîz bikin, nexşeyên germê nîşan bidin û tomarên serdanê bilîzin.

Lê di derbarê platformê de tiştê herî girîng ragihandin e. Hemî fonksiyonên jorîn bi hev ve girêdayî ne. Û ji bo xerîdarên pargîdanî, hejmareke mezin a agahdarî bêyî platformek hêzdar a ku wê di forma analîtîk de pêşkêş dike dê bi hêsanî bêkêr be.

Bi karanîna platformê, hûn dikarin li ser komek daneya mezin pirsek rasthatî bikin. Li vir mînakek hêsan e:

Hemû klîkên li ser rûpela "abc.com" JI <date d1> HETA <date d2> ji bo kesên ku Chrome bikar anîne AN (li Ewropayê bûn Û bikar anîne) nîşan bide iPhone)

Bala xwe bidin operatorên Boolean. Ew ji xerîdaran re di navgîniya pirsê de peyda dibin da ku ji bo wergirtina nimûneyan pirsên bêkêmasî tevlihev bikin.

Daxwaza hêdî

Xerîdarê navborî hewl dida ku tiştek bike ku divê bi lez û bez bixebite:

Hemî tomarên danişînê ji bo bikarhênerên ku serdana her rûpelek bi URL-ya ku tê de "/ jobs" heye nîşan bide

Vê malperê tonek seyrûsefer bû û me tenê ji bo wê zêdetirî mîlyon URL-yên bêhempa hilanîn. Û wan dixwest ku şablonek URL-ya hêsan a ku bi modela karsaziya wan ve girêdayî ye bibînin.

Lêpirsîna pêşîn

Werin em binihêrin ka di databasê de çi diqewime. Li jêr pirsa SQL-ya hêdî ya orîjînal heye:

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 ;

Û li vir dem hene:

Dema plankirî: 1.480 ms Dema darvekirinê: 1431924.650 ms

Lêpirsîn 150 hezar rêz girt. Plansazkerê pirsê çend hûrguliyên balkêş nîşan da, lê ne tengasiyên eşkere.

Ka em daxwazê ​​bêtir lêkolîn bikin. Wekî ku hûn dibînin, ew dike JOIN sê tablo:

  1. danişînên: ji bo nîşandana agahdariya danişînê: gerok, nûnerê bikarhêner, welat, û hwd.
  2. recording_data: URLên tomarkirî, rûpel, dema serdanê
  3. urls: Ji bo ku em URL-yên pir mezin dubare nekin, em wan di tabloyek cihê de hilînin.

Di heman demê de bala xwe bidin ku hemî tabloyên me ji berê ve hatine dabeş kirin account_id. Bi vî rengî, rewşek ku yek hesabek bi taybetî mezin ji yên din re dibe sedema pirsgirêkan tê derxistin.

Li şaneyan digere

Li ser çavdêriya nêzîk, em dibînin ku tiştek bi daxwazek taybetî re xelet e. Hêja ye ku meriv ji nêz ve li vê rêzê binêre:

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

Fikra yekem ev bû ku dibe ku ji ber ILIKE li ser van hemî URLên dirêj (me zêdetirî 1,4 mîlyon hene bêhempa URLên ku ji bo vê hesabê hatine berhev kirin) dibe ku performansa zirarê bibîne.

Lê na, ew ne xal e!

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

Time: 5231.765 ms

Daxwaza lêgerîna şablonê bixwe tenê 5 saniye digire. Lêgerîna nimûneyek di mîlyon URL-yên bêhempa de eşkere ne pirsgirêk e.

Gumanbarên din ên di lîsteyê de gelek in JOIN. Dibe ku zêde karanîna wan bûye sedema hêdîbûnê? Fêrane JOIN's ji bo pirsgirêkên performansê berendamên herî eşkere ne, lê min bawer nedikir ku doza me tîpîk bû.

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

Û ev jî ne doza me bû. JOIN's pir zû derket.

Tengkirina xeleka gumanbaran

Ez amade bûm ku dest bi guheztina pirsê bikim da ku bigihîjim pêşkeftinên performansê yên gengaz. Tîmê min û min 2 ramanên sereke pêşve xistin:

  • Ji bo URL-ya jêrpirsînê EXISTS bikar bînin: Me xwest em careke din kontrol bikin ka ji bo URLan di binê pirsnameyê de pirsgirêk hene. Yek rê ji bo gihîştina vê yekê bi tenê bikar anîn e EXISTS. EXISTS dikare performansê pir çêtir dike ji ber ku ew tavilê biqede gava ku ew tenê rêzika ku bi şertê re têkildar dibîne bibîne.

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

Belê, belê. Dema ku di nav de tê pêçandin jêrpirsîn EXISTS, her tiştî zû zû dike. Pirsa mentiqî ya din ev e ku çima daxwaz bi JOIN-ami û jêrpirsîn bixwe bi ferdî bilez in, lê bi hev re pir hêdî ne?

  • Veguheztina jêrpirsînê berbi CTE : Ger pirs bi serê xwe bilez be, em dikarin bi hêsanî pêşî encamek bilez hesab bikin û dûv re wê ji pirsa sereke re peyda bikin.

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;

Lê dîsa jî pir hêdî bû.

Dîtina sûcdar

Hemî vê demê, tiştek piçûk li ber çavên min dibiriqe, ku min bi berdewamî firçe kir. Lê ji ber ku tiştekî din ne mabû, min biryar da ku li wê jî binêrim. Ez behsa wê dikim && makînevan. Xatirê te EXISTS tenê performansa çêtir kir && yekane faktora hevpar a mayî di hemî guhertoyên pirsa hêdî de bû.

Digerin li belgekirin, em dibînin && dema ku hûn hewce ne ku di navbera du rêzan de hêmanên hevpar bibînin tê bikar anîn.

Di daxwaznameya bingehîn de ev e:

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

Ev tê vê wateyê ku em li ser URLên xwe lêgerînek nimûneyê dikin, dûv re bi hemî URLên bi postên hevpar re hevberdanê bibînin. Ev hinekî tevlihev e ji ber ku "urls" li vir ne tabloya ku hemî URLan vedihewîne, lê ji stûna "urls" ya di tabloyê de vedibêje. recording_data.

Bi zêdebûna gumanên di derbarê &&, Min hewl da ku di plana lêpirsînê ya hatî çêkirin de ji bo wan piştrast bibînim EXPLAIN ANALYZE (Min berê plansaziyek hilandî hebû, lê ez bi gelemperî di ceribandina SQL-ê de ji ceribandina nezelaliya plansazên pirsê rehettir im).

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

Gelek rêzikên parzûnan tenê ji hebûn &&. Ev tê wê wateyê ku ev emeliyat ne tenê buha bû, lê di heman demê de çend caran hate kirin.

Min ev bi îzolekirina rewşê ceriband

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

Ev pirs hêdî bû. Ji ber ku JOIN-s bi lez in û jêrpirsîn zû ne, tenê tiştek mabû && makînevan.

Ev tenê operasyonek sereke ye. Em her gav hewce ne ku li tevahiya tabloya bingehîn a URL-an bigerin da ku li nimûneyekê bigerin, û em her gav hewce ne ku em hevbendan bibînin. Em nekarin rasterast li gorî tomarên URL-ê bigerin, ji ber ku ev tenê nasnameyên ku têne vegotin urls.

Li ser rêya çareseriyê

&& hêdî ji ber ku her du kom mezin in. Ger ez biguhêzim dê operasyon bi lez be urls li ser { "http://google.com/", "http://wingify.com/" }.

Min dest bi lêgerîna rêyek kir ku ez li Postgres bêyî karanîna hevberdanê bikim &&, lê bê serkeftinek mezin.

Di dawiyê de, me biryar da ku em pirsgirêkê tenê bi tenê çareser bikin: her tiştî bidin min urls xêzên ku URL bi qalibê re têkildar e. Bê şert û mercên zêde ew ê bibe - 

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

Li şûna JOIN hevoksazî Min tenê jêrpirsek bikar anî û berfireh kir recording_data.urls array da ku hûn rasterast dikarin şertê tê de bicîh bikin WHERE.

Li vir tişta herî girîng ew e && tê bikar anîn da ku kontrol bikin ka navnîşek diyar URL-ya hevaheng heye an na. Ger hûn piçekî bizivirin, hûn dikarin bibînin ku ev operasyon di nav hêmanên rêzek (an rêzên tabloyek) de dimeşe û dema ku şertek (lihevhatinek) pêk tê disekine. Tiştek nayê bîra te? Erê, EXISTS.

Ji ber ku recording_data.urls dikare ji derveyî çarçoweya jêrpirsînê were referans kirin, gava ku ev çêbibe em dikarin dîsa li hevalê xwe yê kevn vegerin EXISTS û binerxetê pê bipêçin.

Her tiştî bi hev re bicivînin, em pirsa paşîn a xweşbînkirî digirin:

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

Û dema rêberiya dawîn Time: 1898.717 ms Dem dema pîrozkirinê ye?!?

Ne ewqas zû! Pêşî hûn hewce ne ku rastdariyê kontrol bikin. Ez pir bi guman bûm EXISTS optimîzasyon ji ber ku ew mantiqê diguhezîne ku berê temam bibe. Pêdivî ye ku em pê ewle bin ku me xeletiyek ne-eşkere li daxwazê ​​zêde nekiriye.

Testek hêsan bû ku were meşandin count(*) hem li ser pirsên hêdî û hem jî bilez ji bo hejmareke mezin ji daneyên cihêreng. Dûv re, ji bo komek piçûk a daneyê, min bi destan verast kir ku hemî encam rast bûn.

Hemî kontrolên bi domdarî encamên erênî dane. Me her tişt rast kir!

Dersên hînbûyî

Ji vê çîrokê gelek ders hene:

  1. Planên lêpirsînê tevahiya çîrokê nabêjin, lê ew dikarin nîşanan peyda bikin
  2. Gumanbarên sereke her tim ne sûcdarên rastîn in
  3. Pirsên hêdî dikarin werin hilweşandin da ku kelûpelan veqetînin
  4. Ne hemî optimîzasyon di xwezayê de kêmker in
  5. Bikar bînin EXIST, li cihê ku gengaz be, dikare bibe sedema zêdebûnek berbiçav di hilberînê de

encamê

Em ji dema pirsê ya ~ 24 hûrdeman çûn 2 çirkeyan - zêdebûnek performansa pir girîng! Her çend ev gotar mezin derket jî, hemî ceribandinên ku me kirin di rojekê de qewimîn, û hate texmîn kirin ku wan di navbera 1,5 û 2 demjimêran de ji bo xweşbînkirin û ceribandinê girt.

SQL zimanek ecêb e heke hûn jê netirsin, lê hewl bidin ku fêr bibin û bikar bînin. Bi têgihiştinek baş a ku pirsên SQL çawa têne darve kirin, databas çawa plansaziyên pirsê diafirîne, ka îndeks çawa dixebitin, û bi tenê mezinahiya daneyên ku hûn pê re mijûl dibin, hûn dikarin di xweşbînkirina pirsan de pir serfiraz bin. Lêbelê, bi heman rengî girîng e ku meriv ceribandina nêzîkatiyên cihêreng bidomîne û hêdî hêdî pirsgirêkê hilweşîne, tengasiyan bibîne.

Di derbarê bidestxistina encamên mîna van de çêtirîn çêtirîn çêtirîna leza berbiçav û xuya ye - li cihê ku raporek ku berê nedihat barkirin naha hema di cih de bar dike.

Spas taybetî ji bo rêhevalên min bi fermana Aditya Mishra, Aditya Gauru и Varun Malhotra ji bo mêjî û Dinkar Pandir ji bo dîtina xeletiyek girîng di daxwaza meya dawî de berî ku em di dawiyê de jê xatir xwestin!

Source: www.habr.com

Ji bo malperên bi parastina DDoS, serverên VPS VDS mêvandariya pêbawer bikirin 🔥 Hostinga malperê ya pêbawer bi parastina DDoS, serverên VPS VDS bikirin | ProHoster