🥇 የአንድ SQL ምርመራ ታሪክ

ባለፈው ታህሳስ ወር ከVWO ድጋፍ ቡድን አንድ አስደሳች የሳንካ ሪፖርት ደርሶኛል። ለአንድ ትልቅ የድርጅት ደንበኛ የአንዱ የትንታኔ ዘገባዎች የመጫኛ ጊዜ የሚከለክል ይመስላል። እና ይህ የእኔ የኃላፊነት ቦታ ስለሆነ ወዲያውኑ ችግሩን በመፍታት ላይ አተኮርኩ.

prehistory

ስለምን እንደማወራ ግልጽ ለማድረግ፣ ስለ VWO ትንሽ እነግርዎታለሁ። ይህ በድረ-ገጾችዎ ላይ የተለያዩ ኢላማ የተደረጉ ዘመቻዎችን የሚከፍቱበት መድረክ ነው፡ የA/B ሙከራዎችን ማካሄድ፣ ጎብኝዎችን እና ልወጣዎችን መከታተል፣ የሽያጭ ፍንጭውን መተንተን፣ የሙቀት ካርታዎችን ማሳየት እና የጉብኝት ቀረጻዎችን መጫወት።

ነገር ግን በመድረክ ውስጥ በጣም አስፈላጊው ነገር ሪፖርት ማድረግ ነው. ከላይ ያሉት ሁሉም ተግባራት እርስ በርስ የተያያዙ ናቸው. እና ለድርጅታዊ ደንበኞች በትንታኔ መልክ የሚያቀርበው ኃይለኛ መድረክ ከሌለ ከፍተኛ መጠን ያለው መረጃ በቀላሉ ከንቱ ይሆናል።

መድረኩን በመጠቀም፣ በትልቅ የውሂብ ስብስብ ላይ የዘፈቀደ መጠይቅ ማድረግ ይችላሉ። አንድ ቀላል ምሳሌ ይኸውና፡-

Chrome ወይም (በአውሮፓ ውስጥ እና አይፎን ለተጠቀሙ) ሰዎች በገጽ "abc.com" ላይ ያሉትን ሁሉንም ጠቅታዎች ከ<date d1> እስከ <date d2> አሳይ

ለቦሊያን ኦፕሬተሮች ትኩረት ይስጡ. ናሙናዎችን ለማግኘት በዘፈቀደ ውስብስብ ጥያቄዎችን ለማቅረብ በመጠይቁ በይነገጽ ውስጥ ለደንበኞች ይገኛሉ።

ቀርፋፋ ጥያቄ

በጥያቄ ውስጥ ያለው ደንበኛ በፍጥነት መስራት ያለበትን አንድ ነገር ለማድረግ እየሞከረ ነበር፡-

"/ ስራዎች" የያዘ ዩአርኤል ያለው ማንኛውንም ገጽ ለጎበኙ ተጠቃሚዎች ሁሉንም የክፍለ ጊዜ መዝገቦች አሳይ

ይህ ጣቢያ ብዙ ትራፊክ ነበረው እና ለእሱ ብቻ ከአንድ ሚሊዮን በላይ ልዩ የሆኑ ዩአርኤሎችን እያከማችን ነበር። እና ከንግድ ሞዴላቸው ጋር የሚዛመድ ቀላል የዩአርኤል አብነት ማግኘት ፈለጉ።

የመጀመሪያ ምርመራ

በዳታቤዝ ውስጥ ምን እየተካሄደ እንዳለ እንይ። ከታች ያለው የመጀመሪያው ቀርፋፋ የ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 ሶስት ጠረጴዛዎች;

ክፍለ-የክፍለ ጊዜ መረጃን ለማሳየት፡ አሳሽ፣ የተጠቃሚ ወኪል፣ ሀገር እና የመሳሰሉት።
የመቅዳት_ውሂብየተመዘገቡ ዩአርኤሎች ፣ ገጾች ፣ የጉብኝቶች ቆይታ
ዩ አር ኤሎች: እጅግ በጣም ትልቅ የሆኑ ዩአርኤሎችን ማባዛትን ለማስወገድ በተለየ ሠንጠረዥ ውስጥ እናከማቻቸዋለን።

እንዲሁም ሁሉም የእኛ ጠረጴዛዎች ቀድሞውኑ የተከፋፈሉ መሆናቸውን ልብ ይበሉ account_id. በዚህ መንገድ አንድ በተለይ ትልቅ መለያ በሌሎች ላይ ችግር የሚፈጥርበት ሁኔታ አይካተትም።

ፍንጮችን በመፈለግ ላይ

በቅርበት ስንመረምር፣ በአንድ የተወሰነ ጥያቄ ላይ የሆነ ችግር እንዳለ እናያለን። ይህንን መስመር በጥልቀት መመልከት ተገቢ ነው፡-

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

የመጀመሪያው ሐሳብ ምናልባት ምክንያቱም ILIKE በእነዚህ ሁሉ ረጅም ዩአርኤሎች (ከ1,4 ሚሊዮን በላይ አለን። ልዩ ለዚህ መለያ የተሰበሰቡ ዩአርኤሎች) አፈጻጸም ሊሰቃዩ ይችላሉ።

ግን አይሆንም, ነጥቡ ይህ አይደለም!

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

Time: 5231.765 ms

የአብነት ፍለጋ ጥያቄ ራሱ 5 ሰከንድ ብቻ ይወስዳል። በሚሊዮን ልዩ ዩአርኤሎች ውስጥ ስርዓተ ጥለት መፈለግ ችግር እንዳልሆነ ግልጽ ነው።

በዝርዝሩ ላይ ያለው ቀጣይ ተጠርጣሪ ብዙ ነው። 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 ተጠቀምበዩአርኤሎች ንዑስ መጠይቁ ላይ ምንም ችግሮች ካሉ እንደገና ማረጋገጥ እንፈልጋለን። ይህንን ለማሳካት አንዱ መንገድ በቀላሉ መጠቀም ነው። 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[]   )

ይህም ማለት በእኛ ዩአርኤሎች ላይ የስርዓተ-ጥለት ፍለጋን እናደርጋለን፣ ከዚያ መገናኛውን ከሁሉም ዩአርኤሎች ጋር የጋራ ልጥፎችን እናገኛለን። ይሄ ትንሽ ግራ የሚያጋባ ነው ምክንያቱም እዚህ ያለው "urls" ሁሉንም ዩአርኤሎች የያዘውን ሠንጠረዥ አያመለክትም ነገር ግን በሰንጠረዡ ውስጥ ያለውን "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-ዎች ፈጣን ናቸው እና ንዑስ መጠይቆች ፈጣን ናቸው፣ የቀረው ብቸኛው ነገር ነበር። && ኦፕሬተር.

ይህ ቁልፍ ተግባር ብቻ ነው። ስርዓተ-ጥለትን ለመፈለግ ሁል ጊዜ ሙሉውን የዩአርኤሎች ሰንጠረዥ መፈለግ አለብን፣ እና ሁልጊዜ መገናኛዎችን መፈለግ አለብን። በዩአርኤል መዝገቦች በቀጥታ መፈለግ አንችልም፣ ምክንያቱም እነዚህ መታወቂያዎችን የሚያመለክቱ ናቸው። urls.

ወደ መፍትሄ መንገድ ላይ

&& ሁለቱም ስብስቦች ትልቅ ስለሆኑ ቀርፋፋ። ከተተካው ቀዶ ጥገናው በአንጻራዊነት ፈጣን ይሆናል urls ላይ { "http://google.com/", "http://wingify.com/" }.

ሳልጠቀም በፖስትግሬስ ውስጥ የሴቲንግ መገናኛን ለመስራት መንገድ መፈለግ ጀመርኩ &&፣ ግን ብዙ ስኬት ሳያገኙ።

በመጨረሻም ችግሩን በተናጥል ብቻ ለመፍታት ወስነናል-ሁሉንም ነገር ስጠኝ urls ዩአርኤል ከስርዓተ-ጥለት ጋር የሚዛመድባቸው መስመሮች። ያለ ተጨማሪ ሁኔታዎች ይሆናል-

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 አገባብ እኔ አሁን subquery ተጠቅሜ ሰፋሁ recording_data.urls ሁኔታውን በቀጥታ መተግበር እንዲችሉ ድርድር WHERE.

እዚህ በጣም አስፈላጊው ነገር ነው && የተሰጠው ግቤት ተዛማጅ ዩአርኤል መያዙን ለማረጋገጥ ይጠቅማል። ትንሽ ካፈገፈጉ፣ ይህ ክዋኔ በድርድር አካላት (ወይም በጠረጴዛው ረድፎች) ውስጥ ሲንቀሳቀስ እና ቅድመ ሁኔታ (ግጥሚያ) ሲከሰት ሲቆም ማየት ይችላሉ። ምንም ነገር አያስታውስዎትም? አዎ፣ 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 መጠይቆች እንዴት እንደሚፈጸሙ፣ የመረጃ ቋቱ እንዴት የመጠይቅ ዕቅዶችን እንደሚያመነጭ፣ ኢንዴክሶች እንዴት እንደሚሠሩ፣ እና በቀላሉ የሚገናኙትን የውሂብ መጠን በሚገባ በመረዳት ጥያቄዎችን በማመቻቸት ረገድ በጣም ስኬታማ መሆን ይችላሉ። ይሁን እንጂ የተለያዩ አቀራረቦችን መሞከሩን መቀጠል እና ችግሩን ቀስ በቀስ ማጥፋት, ማነቆዎችን መፈለግ አስፈላጊ ነው.

እንደነዚህ ያሉትን ውጤቶች ስለማሳካት በጣም ጥሩው ክፍል የሚታይ ፣ የሚታየው የፍጥነት መሻሻል ነው - ከዚህ ቀደም ሊጫን እንኳን የማይችል ዘገባ አሁን በፍጥነት ይጫናል ።

ልዩ ምስጋና ጓዶቼ በአዲቲያ ሚሽራ ትእዛዝ, Aditya Gauru и Varun Malhotra ለአእምሮ ማጎልበት እና Dinkar Pandir በመጨረሻ ከመሰናበታችን በፊት በጥያቄያችን ላይ አንድ አስፈላጊ ስህተት ለማግኘት!

ምንጭ: hab.com

የአንድ SQL ምርመራ ታሪክ