🥇 Մեկ SQL հետաքննության պատմություն

Անցյալ դեկտեմբերին ես VWO-ի աջակցման թիմից ստացա վրիպակի հետաքրքիր զեկույց: Խոշոր կորպորատիվ հաճախորդի համար վերլուծական հաշվետվություններից մեկի բեռնման ժամանակը արգելիչ էր թվում: Եվ քանի որ սա իմ պատասխանատվության ոլորտն է, ես անմիջապես կենտրոնացա խնդրի լուծման վրա։

նախապատմությանը

Որպեսզի հասկանալի լինի, թե ինչի մասին է խոսքը, մի փոքր կպատմեմ VWO-ի մասին: Սա հարթակ է, որի միջոցով դուք կարող եք սկսել տարբեր նպատակային արշավներ ձեր կայքերում՝ անցկացնել A/B փորձեր, հետևել այցելուներին և փոխակերպումներին, վերլուծել վաճառքի ձագարը, ցուցադրել ջերմային քարտեզներ և նվագարկել այցելությունների ձայնագրությունները:

Բայց ամենակարևորը հարթակի մասին հաշվետվությունն է: Վերոհիշյալ բոլոր գործառույթները փոխկապակցված են: Իսկ կորպորատիվ հաճախորդների համար հսկայական քանակությամբ տեղեկատվություն պարզապես անօգուտ կլիներ առանց հզոր հարթակի, որը ներկայացնում է այն վերլուծական տեսքով:

Օգտագործելով հարթակը, դուք կարող եք պատահական հարցում կատարել տվյալների մեծ հավաքածուի վրա: Ահա մի պարզ օրինակ.

Ցուցադրել բոլոր սեղմումները «abc.com» էջի վրա՝ <ամսաթիվ d1> ՄԻՆՉԵՎ <ամսաթիվ d2> այն մարդկանց համար, ովքեր օգտագործել են Chrome OR (գտնվում է Եվրոպայում և օգտագործել է iPhone)

Ուշադրություն դարձրեք բուլյան օպերատորներին: Դրանք հասանելի են հաճախորդներին հարցումների միջերեսում՝ նմուշներ ստանալու համար կամայականորեն բարդ հարցումներ կատարելու համար:

Դանդաղ խնդրանք

Խնդրո առարկա հաճախորդը փորձում էր անել մի բան, որը ինտուիտիվ կերպով պետք է արագ աշխատեր.

Ցուցադրել բոլոր աշխատաշրջանների գրառումները օգտվողների համար, ովքեր այցելել են «/jobs» պարունակող URL-ով ցանկացած էջ

Այս կայքը ուներ տոննա տրաֆիկ, և մենք պահում էինք ավելի քան մեկ միլիոն եզակի URL հենց դրա համար: Եվ նրանք ցանկանում էին գտնել բավականին պարզ URL ձևանմուշ, որը վերաբերում էր իրենց բիզնես մոդելին:

Նախաքննություն

Եկեք նայենք, թե ինչ է կատարվում տվյալների բազայում: Ստորև բերված է բնօրինակ դանդաղ SQL հարցումը.

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 ;

Եվ ահա ժամերը.

Պլանավորված ժամանակը` 1.480 ms Կատարման ժամանակը` 1431924.650 ms

Հարցումն անցել է 150 հազար տող։ Հարցման պլանավորողը ցույց տվեց մի քանի հետաքրքիր մանրամասներ, բայց ոչ ակնհայտ խոչընդոտներ:

Հետագայում ուսումնասիրենք խնդրանքը։ Ինչպես տեսնում եք, նա անում է JOIN երեք սեղան.

նիստերը: Ցուցադրել սեսիայի մասին տեղեկությունները` բրաուզեր, օգտվողի գործակալ, երկիր և այլն:
ձայնագրման_տվյալներգրանցված URL-ներ, էջեր, այցելությունների տևողությունը
հղումներՉափազանց մեծ URL-ների կրկնօրինակումից խուսափելու համար մենք դրանք պահում ենք առանձին աղյուսակում:

Նաև նշեք, որ մեր բոլոր աղյուսակներն արդեն բաժանված են ըստ account_id. Այսպիսով, բացառվում է իրավիճակը, երբ առանձնապես խոշոր հաշիվներից մեկը խնդիրներ է առաջացնում մյուսների համար:

Թելադրանքների որոնում

Ավելի ուշադիր զննելուց հետո մենք տեսնում ենք, որ որոշակի հարցում ինչ-որ բան այն չէ: Արժե ավելի մոտիկից նայել այս տողին.

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

Առաջին միտքն այն էր, որ գուցե այն պատճառով ILIKE այս բոլոր երկար URL-ների վրա (մենք ունենք ավելի քան 1,4 միլիոն եզակի Այս հաշվի համար հավաքված URL-ները կարող են տուժել:

Բայց ոչ, դա չէ իմաստը:

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

Time: 5231.765 ms

Կաղապարի որոնման հարցումն ինքնին տևում է ընդամենը 5 վայրկյան: Մի միլիոն եզակի URL-ներում օրինակ փնտրելը ակնհայտորեն խնդիր չէ:

Ցուցակի հաջորդ կասկածյալը մի քանիսն են JOIN. Միգուցե դրանց չափից ավելի օգտագործումը դանդաղեցրեց: Սովորաբար JOIN-ը կատարողական խնդիրների ամենաակնհայտ թեկնածուներն են, բայց ես չէի հավատում, որ մեր դեպքը բնորոշ է:

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

Եվ սա նույնպես մեր դեպքը չէր։ JOIN-ը բավականին արագ ստացվեց:

Նեղացնելով կասկածյալների շրջանակը

Ես պատրաստ էի սկսել հարցումը փոխել՝ կատարողականի հնարավոր բարելավման հասնելու համար: Ես և իմ թիմը մշակեցինք 2 հիմնական գաղափար.

Օգտագործեք EXISTS ենթհարցման URL-ի համարՄենք ուզում էինք նորից ստուգել՝ արդյոք URL-ների ենթահարկերի հետ կապված խնդիրներ կան: Դրան հասնելու ուղիներից մեկը պարզապես օգտագործելն է EXISTS. EXISTS կարող զգալիորեն բարելավում է կատարումը, քանի որ այն անմիջապես ավարտվում է հենց որ գտնում է պայմանին համապատասխանող միակ տողը:

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

Դե, այո: Ենթհարցում, երբ փաթաթված է EXISTS, ամեն ինչ սուպեր արագ է դարձնում: Հաջորդ տրամաբանական հարցն այն է, թե ինչու է խնդրանքը JOIN-ami և subquery-ն առանձին-առանձին արագ են, բայց ահավոր դանդաղ են միասին:

Ենթահարկը տեղափոխելով CTE Եթե հարցումն ինքնին արագ է, մենք կարող ենք նախ հաշվարկել արագ արդյունքը, այնուհետև տրամադրել այն հիմնական հարցմանը:

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;

Բայց այն դեռ շատ դանդաղ էր ընթանում։

Մեղավորին գտնելը

Այս ամբողջ ընթացքում աչքիս առաջ մի փոքրիկ բան փայլատակեց, որը ես անընդհատ մի կողմ էի տալիս։ Բայց քանի որ ուրիշ բան չէր մնացել, ես էլ որոշեցի նայել նրան։ Ես խոսում եմ && օպերատոր. Ցտեսություն EXISTS պարզապես բարելավվել է կատարումը && դանդաղ հարցման բոլոր տարբերակներում միակ մնացած ընդհանուր գործոնն էր:

Նայելով փաստաթղթեր, մենք դա տեսնում ենք && օգտագործվում է, երբ անհրաժեշտ է գտնել ընդհանուր տարրեր երկու զանգվածների միջև:

Բնօրինակ հարցումում սա հետևյալն է.

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

Սա նշանակում է, որ մենք կատարում ենք օրինակների որոնում մեր URL-ների վրա, այնուհետև գտնում ենք խաչմերուկը բոլոր URL-ների հետ ընդհանուր գրառումներով: Սա մի փոքր շփոթեցնող է, քանի որ այստեղ «urls»-ը վերաբերում է ոչ թե բոլոր URL-ները պարունակող աղյուսակին, այլ աղյուսակի «urls» սյունակին: recording_data.

վերաբերյալ աճող կասկածներով &&, ես փորձեցի գտնել դրանց հաստատումը ստեղծված հարցման պլանում EXPLAIN ANALYZE (Ես արդեն ունեի պահված պլան, բայց ես սովորաբար ավելի հարմարավետ եմ փորձարկում SQL-ում, քան փորձում եմ հասկանալ հարցումների պլանավորողների անթափանցիկությունը):

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

Կային մի քանի տող զտիչներ միայն &&. Ինչը նշանակում էր, որ այս վիրահատությունը ոչ միայն թանկ արժեր, այլեւ մի քանի անգամ կատարվեց։

Ես փորձարկեցի սա՝ մեկուսացնելով վիճակը

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

Այս հարցումը դանդաղ էր: Քանի որ JOIN-ները արագ են, իսկ ենթհարցերը՝ արագ, մնում էր միայն && օպերատոր.

Սա պարզապես առանցքային գործողություն է: Մենք միշտ պետք է որոնենք URL-ների ամբողջ հիմքում ընկած աղյուսակը՝ օրինակ փնտրելու համար, և մենք միշտ պետք է գտնենք խաչմերուկներ: Մենք չենք կարող ուղղակիորեն որոնել URL-ի գրառումներով, քանի որ դրանք ուղղակի ID-ներ են, որոնք վերաբերում են urls.

Լուծման ճանապարհին

&& դանդաղ, քանի որ երկու հավաքածուները հսկայական են: Վիրահատությունը համեմատաբար արագ կլինի, եթե փոխարինեմ urls մասին { "http://google.com/", "http://wingify.com/" }.

Ես սկսեցի փնտրել ճանապարհ՝ առանց օգտագործելու Պոստգրեսում սահմանված խաչմերուկը &&, բայց առանց մեծ հաջողության։

Ի վերջո, մենք որոշեցինք խնդիրը լուծել միայն առանձին՝ ինձ ամեն ինչ տվեք urls տողեր, որոնց URL-ը համապատասխանում է օրինակին: Առանց լրացուցիչ պայմանների կլինի.

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

Փոխարենը JOIN շարահյուսություն Ես պարզապես օգտագործեցի ենթահարկ և ընդլայնեցի recording_data.urls զանգված, որպեսզի կարողանաք ուղղակիորեն կիրառել պայմանը WHERE.

Այստեղ ամենակարեւորն այն է && օգտագործվում է ստուգելու համար, թե արդյոք տվյալ գրառումը պարունակում է համապատասխան URL: Եթե մի փոքր աչք գցեք, կտեսնեք, որ այս գործողությունը շարժվում է զանգվածի (կամ աղյուսակի տողերի) տարրերի միջով և կանգ է առնում պայմանի (համապատասխանության) դեպքում: Ձեզ ոչինչ չի՞ հիշեցնում։ Այո, EXISTS.

սկսած recording_data.urls կարելի է հղում կատարել ենթհարցման համատեքստից դուրս, երբ դա տեղի ունենա, մենք կարող ենք վերադառնալ մեր հին ընկերոջը EXISTS և դրանով փաթաթել ենթահարկը։

Ամեն ինչ միասին դնելով, մենք ստանում ենք վերջնական օպտիմիզացված հարցումը.

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

Եվ վերջնական ժամկետը Time: 1898.717 ms Ժամանակն է նշելու?!?

Ոչ այդքան արագ! Նախ անհրաժեշտ է ստուգել ճիշտությունը: Ես չափազանց կասկածամիտ էի EXISTS օպտիմիզացում, քանի որ այն փոխում է ավելի վաղ ավարտելու տրամաբանությունը: Մենք պետք է վստահ լինենք, որ հարցումին ոչ ակնհայտ սխալ չենք ավելացրել:

Պարզ թեստ էր առաջադրվելու count(*) ինչպես դանդաղ, այնպես էլ արագ հարցումների վրա՝ մեծ թվով տարբեր տվյալների հավաքածուների համար: Այնուհետև տվյալների փոքր ենթաբազմության համար ես ձեռքով ստուգեցի, որ բոլոր արդյունքները ճիշտ են:

Բոլոր թեստերը տվել են հետևողականորեն դրական արդյունքներ: Մենք ամեն ինչ շտկեցինք!

Քաղված դասերը

Այս պատմությունից կարելի է շատ դասեր քաղել.

Հարցման պլանները չեն պատմում ամբողջ պատմությունը, բայց դրանք կարող են հուշումներ տալ
Հիմնական կասկածյալները միշտ չէ, որ իրական մեղավորներն են
Դանդաղ հարցումները կարելի է բաժանել՝ մեկուսացնելու խցանումները
Ոչ բոլոր օպտիմիզացիաներն են նվազեցնող բնույթ
Օգտագործում EXIST, որտեղ հնարավոր է, կարող է հանգեցնել արտադրողականության կտրուկ աճի

Արտադրողականություն

Մենք ~24 րոպե հարցման ժամանակից անցանք 2 վայրկյանի, ինչը բավականին զգալի կատարողականի աճ է: Թեև այս հոդվածը մեծ էր, սակայն մեր կատարած բոլոր փորձերը տեղի ունեցան մեկ օրում, և գնահատվում էր, որ դրանք 1,5-ից 2 ժամ տևեցին օպտիմալացման և փորձարկման համար:

SQL-ը հիանալի լեզու է, եթե չես վախենում դրանից, այլ փորձում ես սովորել և օգտագործել այն: Լավ հասկանալով, թե ինչպես են կատարվում SQL հարցումները, ինչպես է տվյալների բազան ստեղծում հարցումների պլանները, ինչպես են աշխատում ինդեքսները և պարզապես ձեր առնչվող տվյալների չափը, դուք կարող եք շատ հաջողակ լինել հարցումների օպտիմալացման հարցում: Նույնքան կարևոր է, սակայն, շարունակել փորձել տարբեր մոտեցումներ և կամաց-կամաց քանդել խնդիրը՝ գտնելով խոչընդոտները:

Նման արդյունքների հասնելու լավագույն մասը նկատելի, տեսանելի արագության բարելավումն է, որտեղ հաշվետվությունը, որը նախկինում նույնիսկ չէր բեռնվում, այժմ բեռնվում է գրեթե անմիջապես:

Հատուկ շնորհակալություն իմ ընկերները Ադիտյա Միշրայի հրամանատարությամբ, Ադիտյա Գաուրու и Վարուն Մալհոտրա մտագրոհի համար և Դինկար Փանդիր մեր վերջնական հարցումում կարևոր սխալ գտնելու համար, նախքան մենք վերջապես հրաժեշտ տվեցինք դրան:

Source: www.habr.com

Մեկ SQL հետաքննության պատմություն