🥇 एक SQL अनुसन्धानको कथा

गत डिसेम्बरमा मैले VWO समर्थन टोलीबाट एउटा रोचक बग रिपोर्ट प्राप्त गरें। ठूला कर्पोरेट क्लाइन्टका लागि एनालिटिक्स रिपोर्टहरू मध्ये एकको लागि लोड हुने समय निषेधात्मक देखिन्थ्यो। र यो मेरो जिम्मेवारीको क्षेत्र भएकोले, मैले तुरुन्तै समस्या समाधानमा ध्यान दिए।

प्रागितिहास

म के कुरा गरिरहेको छु भनेर स्पष्ट पार्नको लागि, म तपाईंलाई VWO को बारेमा थोरै बताउनेछु। यो एक प्लेटफर्म हो जसको साथ तपाईं आफ्नो वेबसाइटहरूमा विभिन्न लक्षित अभियानहरू सुरु गर्न सक्नुहुन्छ: A/B प्रयोगहरू सञ्चालन गर्नुहोस्, आगन्तुकहरू र रूपान्तरणहरू ट्र्याक गर्नुहोस्, बिक्री फनेलको विश्लेषण गर्नुहोस्, ताप नक्साहरू प्रदर्शन गर्नुहोस् र भ्रमण रेकर्डिङहरू प्ले गर्नुहोस्।

तर प्लेटफर्मको बारेमा सबैभन्दा महत्त्वपूर्ण कुरा रिपोर्टिङ हो। माथिका सबै प्रकार्यहरू आपसमा जोडिएका छन्। र कर्पोरेट क्लाइन्टहरूका लागि, जानकारीको ठूलो मात्रा एक शक्तिशाली प्लेटफर्म बिना बेकार हुनेछ जसले यसलाई विश्लेषणात्मक रूपमा प्रस्तुत गर्दछ।

प्लेटफर्म प्रयोग गरेर, तपाईं ठूलो डेटा सेटमा अनियमित क्वेरी बनाउन सक्नुहुन्छ। यहाँ एउटा सरल उदाहरण हो:

पृष्ठ "abc.com" मा सबै क्लिकहरू देखाउनुहोस् <date d1> TO <date d2> Chrome को प्रयोग गर्ने वा (युरोपमा अवस्थित र 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 मुख्य विचारहरू विकास गरे:

सबक्वेरी 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-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-s छिटो छन् र सबक्वेरीहरू छिटो छन्, एक मात्र चीज बाँकी थियो && अपरेटर।

यो केवल एक प्रमुख कार्य हो। हामीले सधैं ढाँचा खोज्नको लागि URL को सम्पूर्ण अन्तर्निहित तालिका खोज्न आवश्यक छ, र हामीले सधैं प्रतिच्छेदनहरू फेला पार्न आवश्यक छ। हामी सीधै URL रेकर्डहरू खोज्न सक्दैनौं, किनभने यी केवल ID हरू हुन् urls.

समाधानको बाटोमा

&& ढिलो किनभने दुवै सेट विशाल छन्। मैले प्रतिस्थापन गरेमा अपरेशन अपेक्षाकृत छिटो हुनेछ urls मा { "http://google.com/", "http://wingify.com/" }.

मैले प्रयोग नगरी Postgres मा प्रतिच्छेदन सेट गर्ने तरिका खोज्न थाले &&तर धेरै सफलता बिना।

अन्तमा, हामीले एक्लै समस्या समाधान गर्ने निर्णय गर्यौं: मलाई सबै दिनुहोस् 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 array ताकि तपाइँ सिधै सर्त लागू गर्न सक्नुहुन्छ WHERE.

यहाँ सबैभन्दा महत्त्वपूर्ण कुरा यो हो && दिइएको प्रविष्टिमा मिल्दो URL समावेश छ कि छैन भनी जाँच गर्न प्रयोग गरिन्छ। यदि तपाइँ अलिकति squint गर्नुहुन्छ भने, तपाइँ यो अपरेशन एर्रे (वा तालिकाको पङ्क्तिहरू) को तत्वहरू मार्फत सरेको देख्न सक्नुहुन्छ र शर्त (मिलान) भेट्दा रोकिन्छ। के तपाईलाई केहि पनि सम्झाउँदैन? हो, 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 अनुसन्धान को कथा