🥇ఒక SQL పరిశోధన యొక్క కథ

గత డిసెంబర్‌లో నాకు VWO సపోర్ట్ టీమ్ నుండి ఒక ఆసక్తికరమైన బగ్ రిపోర్ట్ వచ్చింది. పెద్ద కార్పొరేట్ క్లయింట్ కోసం అనలిటిక్స్ రిపోర్ట్‌లలో ఒకదానికి లోడింగ్ సమయం నిషేధించబడింది. మరియు ఇది నా బాధ్యత ప్రాంతం కాబట్టి, నేను వెంటనే సమస్యను పరిష్కరించడంపై దృష్టి పెట్టాను.

పూర్వచరిత్ర

నేను దేని గురించి మాట్లాడుతున్నానో స్పష్టంగా చెప్పడానికి, నేను మీకు VWO గురించి కొంచెం చెబుతాను. ఇది మీరు మీ వెబ్‌సైట్‌లలో వివిధ లక్ష్య ప్రచారాలను ప్రారంభించగల ప్లాట్‌ఫారమ్: A/B ప్రయోగాలను నిర్వహించడం, సందర్శకులు మరియు మార్పిడులను ట్రాక్ చేయడం, విక్రయాల గరాటును విశ్లేషించడం, హీట్ మ్యాప్‌లను ప్రదర్శించడం మరియు సందర్శన రికార్డింగ్‌లను ప్లే చేయడం.

కానీ ప్లాట్‌ఫారమ్ గురించి చాలా ముఖ్యమైన విషయం రిపోర్టింగ్. పైన పేర్కొన్న అన్ని విధులు పరస్పరం అనుసంధానించబడి ఉన్నాయి. మరియు కార్పొరేట్ క్లయింట్‌ల కోసం, విశ్లేషణల రూపంలో అందించే శక్తివంతమైన ప్లాట్‌ఫారమ్ లేకుండా భారీ మొత్తంలో సమాచారం నిరుపయోగంగా ఉంటుంది.

ప్లాట్‌ఫారమ్‌ని ఉపయోగించి, మీరు పెద్ద డేటా సెట్‌లో యాదృచ్ఛిక ప్రశ్నను చేయవచ్చు. ఇక్కడ ఒక సాధారణ ఉదాహరణ:

Chromeని ఉపయోగించిన లేదా (యూరోప్‌లో ఉండి iPhoneని ఉపయోగించిన) వ్యక్తుల కోసం <date d1> నుండి <date d2> వరకు "abc.com" పేజీలో అన్ని క్లిక్‌లను చూపు

బూలియన్ ఆపరేటర్లకు శ్రద్ధ వహించండి. నమూనాలను పొందేందుకు ఏకపక్షంగా సంక్లిష్టమైన ప్రశ్నలను చేయడానికి అవి ప్రశ్న ఇంటర్‌ఫేస్‌లో క్లయింట్‌లకు అందుబాటులో ఉంటాయి.

నెమ్మదిగా అభ్యర్థన

ప్రశ్నలోని క్లయింట్ అకారణంగా త్వరగా పని చేసే పనిని చేయడానికి ప్రయత్నిస్తున్నారు:

"/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 లు: చాలా పెద్ద 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 ప్రధాన ఆలోచనలను అభివృద్ధి చేసాము:

సబ్‌క్వెరీ URL కోసం EXISTSని ఉపయోగించండి: మేము 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-అమీ మరియు సబ్‌క్వెరీ వ్యక్తిగతంగా వేగంగా ఉంటాయి, కానీ కలిసి చాలా నెమ్మదిగా ఉన్నాయా?

సబ్‌క్వెరీని 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లతో కూడలిని కనుగొనండి. ఇది కొంచెం గందరగోళంగా ఉంది ఎందుకంటే ఇక్కడ "url" అనేది అన్ని URLలను కలిగి ఉన్న పట్టికను సూచించదు, కానీ పట్టికలోని "url" నిలువు వరుసను సూచిస్తుంది. 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 ప్రశ్నలు ఎలా అమలు చేయబడతాయి, డేటాబేస్ క్వెరీ ప్లాన్‌లను ఎలా రూపొందిస్తుంది, సూచికలు ఎలా పని చేస్తాయి మరియు మీరు డీల్ చేస్తున్న డేటా పరిమాణం గురించి మంచి అవగాహన కలిగి ఉండటం ద్వారా, మీరు ప్రశ్నలను ఆప్టిమైజ్ చేయడంలో చాలా విజయవంతమవుతారు. అయినప్పటికీ, విభిన్న విధానాలను ప్రయత్నించడం కొనసాగించడం మరియు సమస్యను నెమ్మదిగా విచ్ఛిన్నం చేయడం, అడ్డంకులను కనుగొనడం కూడా అంతే ముఖ్యం.

ఇలాంటి ఫలితాలను సాధించడంలో ఉత్తమమైన భాగం గమనించదగ్గ, కనిపించే వేగం మెరుగుదల - ఇక్కడ గతంలో లోడ్ చేయని నివేదిక ఇప్పుడు దాదాపు తక్షణమే లోడ్ అవుతుంది.

ప్రత్యేక ధన్యవాదాలు నా సహచరులు ఆదిత్య మిశ్రా ఆదేశాల మేరకు, ఆదిత్య గౌరు и వరుణ్ మల్హోత్రా మెదడును కదిలించడం కోసం మరియు దినకర్ పందిర్ మేము చివరకు వీడ్కోలు చెప్పే ముందు మా చివరి అభ్యర్థనలో ముఖ్యమైన లోపాన్ని కనుగొన్నందుకు!

మూలం: www.habr.com

ఒక SQL పరిశోధన యొక్క కథ