🥇එක් SQL විමර්ශනයක කතාව

පසුගිය දෙසැම්බර් මාසයේදී මට VWO සහායක කණ්ඩායමෙන් රසවත් දෝෂ වාර්තාවක් ලැබුණි. විශාල ආයතනික සේවාලාභියෙකු සඳහා විශ්ලේෂණ වාර්තා වලින් එකක් සඳහා පැටවීමේ කාලය තහනම් බව පෙනේ. මෙය මගේ වගකීම් අංශය වන බැවින්, මම වහාම ගැටලුව විසඳීමට අවධානය යොමු කළෙමි.

මුදලටය

මම කියන්නේ මොකක්ද කියලා පැහැදිලි වෙන්න, මම VWO ගැන ටිකක් කියන්නම්. මෙය ඔබට ඔබේ වෙබ් අඩවිවල විවිධ ඉලක්කගත ව්‍යාපාර දියත් කළ හැකි වේදිකාවකි: A/B අත්හදා බැලීම්, අමුත්තන් සහ පරිවර්තනයන් නිරීක්ෂණය කිරීම, විකුණුම් පුනීලය විශ්ලේෂණය කිරීම, තාප සිතියම් ප්‍රදර්ශනය කිරීම සහ සංචාර පටිගත කිරීම් වාදනය කිරීම.

නමුත් වේදිකාවේ වැදගත්ම දෙය වාර්තා කිරීමයි. ඉහත සියලු කාර්යයන් එකිනෙකට සම්බන්ධ වේ. ආයතනික ගනුදෙනුකරුවන් සඳහා, විශ්ලේෂණ ආකාරයෙන් ඉදිරිපත් කරන බලවත් වේදිකාවක් නොමැතිව තොරතුරු විශාල ප්‍රමාණයක් නිෂ්ඵල වනු ඇත.

වේදිකාව භාවිතයෙන්, ඔබට විශාල දත්ත කට්ටලයක් මත අහඹු විමසුමක් කළ හැකිය. මෙන්න සරල උදාහරණයක්:

Chrome භාවිතා කළ හෝ (යුරෝපයේ පිහිටා ඇති සහ iPhone භාවිතා කළ) <date d1> සිට <date d2> දක්වා "abc.com" පිටුවේ ඇති සියලුම ක්ලික් කිරීම් පෙන්වන්න

Boolean ක්රියාකරුවන් වෙත අවධානය යොමු කරන්න. සාම්පල ලබා ගැනීම සඳහා අත්තනෝමතික ලෙස සංකීර්ණ විමසීම් කිරීමට විමසුම් අතුරුමුහුණතෙහි සේවාදායකයින්ට ඒවා ලබා ගත හැකිය.

මන්දගාමී ඉල්ලීම

අදාළ සේවාදායකයා බුද්ධිමත්ව ඉක්මනින් ක්‍රියා කළ යුතු දෙයක් කිරීමට උත්සාහ කරමින් සිටියේය:

"/jobs" අඩංගු URL එකක් සහිත ඕනෑම පිටුවකට පැමිණි පරිශීලකයින් සඳහා සියලුම සැසි වාර්තා පෙන්වන්න

මෙම වෙබ් අඩවියට ටොන් ගණනක් තදබදයක් තිබූ අතර අපි ඒ සඳහාම අනන්‍ය URL මිලියනයකට වඩා ගබඩා කරමින් සිටියෙමු. ඔවුන්ගේ ව්‍යාපාර ආකෘතියට අදාළ තරමක් සරල URL අච්චුවක් සොයා ගැනීමට ඔවුන්ට අවශ්‍ය විය.

මූලික විමර්ශනය

අපි බලමු Database එකේ මොනවද වෙන්නේ කියලා. පහත දැක්වෙන්නේ මුල් මන්දගාමී 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-අමි සහ උප විමසුම තනි තනිව වේගවත් නමුත් එකට දරුණු ලෙස මන්දගාමීද?

උප ප්‍රශ්නය 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-s වේගවත් වන අතර subqueries වේගවත් වේ, ඉතිරිව තිබුණේ එකම දෙයයි && ක්රියාකරු.

මෙය ප්රධාන මෙහෙයුමක් පමණි. රටාවක් සෙවීම සඳහා අපට සෑම විටම URL හි සම්පූර්ණ පාදක වගුව සෙවිය යුතු අතර, අපට සැමවිටම මංසන්ධි සොයා ගැනීමට අවශ්‍ය වේ. අපට සෘජුවම URL වාර්තා මගින් සෙවිය නොහැක, මන්ද මේවා හුදෙක් IDs පමණක් වන බැවිනි urls.

විසඳුමකට යන ගමනේදී

&& කට්ටල දෙකම විශාල බැවින් මන්දගාමී වේ. මම ප්රතිස්ථාපනය කළහොත් මෙහෙයුම සාපේක්ෂව ඉක්මන් වනු ඇත urls මත { "http://google.com/", "http://wingify.com/" }.

මම පාවිච්චි නොකර Postgres වල set intersection කරන්න ක්‍රමයක් හොයන්න පටන් ගත්තා &&, නමුත් වැඩි සාර්ථකත්වයක් නොමැතිව.

අවසානයේදී, අපි හුදකලාව ගැටලුව විසඳීමට තීරණය කළෙමු: මට සියල්ල දෙන්න 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 සහ එය සමග subquery ඔතා.

සියල්ල එකට එකතු කිරීමෙන්, අපට අවසාන ප්‍රශස්ත විමසුම ලැබේ:

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 විමර්ශනයක කතාව