🥇ஒரு 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 பதிவுகள் மூலம் நேரடியாகத் தேட முடியாது, ஏனெனில் இவை வெறும் ஐடிகள் மட்டுமே 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 வரிசை, நீங்கள் நிபந்தனையை நேரடியாகப் பயன்படுத்த முடியும் 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 விசாரணையின் கதை