Бір SQL зерттеуінің тарихы

Өткен желтоқсанда мен VWO қолдау тобынан қызықты қате туралы есеп алдым. Ірі корпоративтік клиентке арналған аналитикалық есептердің бірінің жүктелу уақыты өте қиын болып көрінді. Бұл менің жауапкершілік саласы болғандықтан, мен бірден мәселені шешуге назар аудардым.

тарихын

Не туралы айтып тұрғанымды түсіну үшін мен сізге VWO туралы аздап айтып беремін. Бұл сіздің веб-сайттарыңызда әртүрлі мақсатты науқандарды бастауға болатын платформа: A/B эксперименттерін жүргізу, келушілер мен конверсияларды қадағалау, сату шұңқырын талдау, жылу карталарын көрсету және келу жазбаларын ойнату.

Бірақ платформадағы ең маңызды нәрсе - есеп беру. Жоғарыда аталған функциялардың барлығы өзара байланысты. Ал корпоративтік клиенттер үшін ақпараттың үлкен көлемі оны аналитикалық формада ұсынатын қуатты платформасыз пайдасыз болар еді.

Платформаны пайдалана отырып, сіз үлкен деректер жинағына кездейсоқ сұрау жасай аласыз. Міне, қарапайым мысал:

Chrome пайдаланған немесе (Еуропада орналасқан және iPhone пайдаланған) адамдар үшін "abc.com" бетіндегі барлық шертулерді көрсетіңіз.

Логикалық операторларға назар аударыңыз. Олар үлгілерді алу үшін ерікті түрде күрделі сұраулар жасау үшін сұрау интерфейсіндегі клиенттерге қолжетімді.

Баяу сұрау

Қарастырылып отырған клиент интуитивті түрде тез жұмыс істеуі керек нәрсені жасауға тырысты:

"/жұмыстары" бар 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 мс Орындалу уақыты: 1431924.650 мс

Сұрау 150 мың жолды тексерді. Сұрауды жоспарлаушы бірнеше қызықты мәліметтерді көрсетті, бірақ айқын кедергілер жоқ.

Өтінішті әрі қарай зерттейік. Көріп отырғаныңыздай, ол жасайды JOIN үш кесте:

1. сессиялар: сеанс ақпаратын көрсету үшін: браузер, пайдаланушы агенті, ел және т.б.
2. жазу_деректері: жазылған URL мекенжайлары, беттер, кіру ұзақтығы
3. 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's - өнімділік мәселелеріне ең айқын үміткерлер, бірақ мен біздің жағдайымыз типтік екеніне сенбедім.

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 және ішкі сұраудың өзі жеке жылдам, бірақ бірге өте баяу?

• Ішкі сұрауды 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 жылдам және ішкі сұраулар жылдам, жалғыз нәрсе қалды && оператор.

Бұл тек негізгі операция. Үлгіні іздеу үшін біз әрқашан 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(*) әртүрлі деректер жиындарының үлкен саны үшін баяу және жылдам сұрауларда. Содан кейін, деректердің шағын жиыны үшін мен барлық нәтижелердің дұрыс екенін қолмен тексердім.

Барлық сынақтар тұрақты оң нәтиже берді. Біз бәрін түзеттік!

Алынған сабақтар

Бұл оқиғадан көп сабақ алуға болады:

1. Сұрау жоспарлары бүкіл оқиғаны көрсетпейді, бірақ олар анықтама бере алады
2. Басты күдіктілер әрқашан нағыз кінәлі бола бермейді
3. Баяу сұрауларды кедергілерді оқшаулау үшін бөлуге болады
4. Барлық оңтайландырулар табиғатта редукциялық емес
5. Пайдаланыңыз EXIST, мүмкін болса, өнімділіктің күрт өсуіне әкелуі мүмкін

қорытынды

Сұрау уақытынан ~24 минуттан 2 секундқа дейін жеттік - өнімділіктің айтарлықтай өсуі! Бұл мақала үлкен болғанымен, біз жасаған барлық эксперименттер бір күнде болды және оңтайландыру мен тестілеуге 1,5 және 2 сағат уақыт кетеді деп есептелді.

SQL - бұл керемет тіл, егер сіз одан қорықпасаңыз, бірақ оны үйренуге және қолдануға тырысыңыз. SQL сұрауларының қалай орындалатынын, дерекқордың сұрау жоспарларын қалай жасайтынын, индекстердің қалай жұмыс істейтінін және сіз жұмыс істеп жатқан деректердің жай ғана өлшемін жақсы түсіну арқылы сұрауларды оңтайландыруда өте табысты бола аласыз. Дегенмен, әртүрлі тәсілдерді қолданып көруді жалғастыру және қиындықтарды тауып, мәселені баяу бұзу маңызды.

Бұл нәтижелерге қол жеткізудің ең жақсы бөлігі жылдамдықты айтарлықтай жақсарту болып табылады - бұрын тіпті жүктелмейтін есеп қазір бірден дерлік жүктеледі.

-ға ерекше рахмет менің жолдастарым Адитья Мишраның бұйрығымен, Адитья Гауру и Варун Мальхотра миға шабуыл үшін және Динкар Пандир соңғы сұрауымызбен қоштасқанға дейін маңызды қатені тапқаныңыз үшін!

Ақпарат көзі: www.habr.com