ProHoster > Blog > Идораи > Ҳикояи як таҳқиқоти SQL

Ҳикояи як таҳқиқоти SQL

Моҳи декабри соли гузашта ман аз дастаи дастгирии VWO гузориши ҷолибе гирифтам. Вақти боркунӣ барои яке аз гузоришҳои таҳлилӣ барои муштарии бузурги корпоративӣ манъкунанда ба назар мерасид. Ва азбаски ин соҳаи масъулияти ман аст, ман фавран ба ҳалли мушкилот диққат додам.

prehistory

Барои равшан кардани он, ки ман дар бораи чӣ гап мезанам, ман ба шумо дар бораи VWO каме нақл мекунам. Ин платформаест, ки бо он шумо метавонед маъракаҳои гуногуни мақсаднокро дар вебсайтҳои худ оғоз кунед: таҷрибаҳои A/B гузаронед, меҳмонон ва табдилҳоро пайгирӣ кунед, канали фурӯшро таҳлил кунед, харитаҳои гармиро намоиш диҳед ва сабтҳои боздидро бозӣ кунед.

Аммо чизи муҳимтарин дар платформа гузориш додан аст. Ҳамаи вазифаҳои дар боло зикршуда бо ҳам алоқаманданд. Ва барои муштариёни корпоративӣ, миқдори зиёди иттилоот бе платформаи пурқуввате, ки онро дар шакли таҳлил пешниҳод мекунад, бефоида хоҳад буд.

Бо истифода аз платформа, шумо метавонед дар маҷмӯи додаҳои калон дархости тасодуфӣ кунед. Инак як мисоли оддӣ:

Ҳама кликҳоро дар саҳифаи "abc.com" АЗ <date d1> ТО <date d2> нишон диҳед барои одамоне, ки Chrome ё (дар Аврупо ҷойгиранд ва iPhone истифода кардаанд)

Ба операторҳои булӣ диққат диҳед. Онҳо ба мизоҷон дар интерфейси дархост дастрасанд, то дархостҳои худсарона мураккабро барои гирифтани намунаҳо анҷом диҳанд.

Дархости суст

Муштарии мавриди назар кӯшиш мекард, ки кореро анҷом диҳад, ки ба таври интуитивӣ бояд зуд кор кунад:

Намоиши ҳамаи сабтҳои сессия барои корбароне, ки ба ягон саҳифа бо URL дорои "/jobs" ташриф овардаанд

Ин сайт як тонна трафик дошт ва мо танҳо барои он беш аз як миллион 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 се ҷадвал:

  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хеле зуд баромад.

Маҳдуд кардани доираи гумонбарон

Ман омода будам, ки дархостро тағир диҳам, то ҳама беҳбудиҳои имконпазирро ба даст орам. Ман ва дастаи ман ду идеяи асосиро таҳия кардем:

  • EXISTS-ро барои URL зерпурсиш истифода баред: Мо мехостем бори дигар тафтиш кунем, ки оё дар зерпурсиш барои 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-ҳо бо паёмҳои умумӣ пайдо мекунем. Ин каме печида аст, зеро "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-ҳо зуд ва зерпурсишҳо зуд ҳастанд, танҳо чизе боқӣ монд && оператор.

Ин танҳо як амалиёти асосӣ аст. Мо ҳамеша бояд тамоми ҷадвали асосии 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, чӣ гуна пойгоҳи додаҳо нақшаҳои дархостро тавлид мекунад, чӣ гуна кор кардани индексҳо ва танҳо андозаи маълумоте, ки шумо бо он сарукор доред, шумо метавонед дар оптимизатсияи дархостҳо муваффақ бошед. Бо вуҷуди ин, идома додани кӯшиши равишҳои гуногун ва оҳиста-оҳиста бартараф кардани мушкилот, пайдо кардани монеаҳо муҳим аст.

Қисмати беҳтарини ба даст овардани чунин натиҷаҳо ин беҳбуди назаррас ва намоёни суръат аст - дар он гузорише, ки қаблан ҳатто бор намешуд, ҳоло қариб фавран бор мешавад.

Ташаккури махсус ба рафикони ман бо фармони Адитя Мишра, Адитя Гауру и Варун Малхотра барои хучуми майна ва Динкар Пандир барои дарёфти хатои муҳим дар дархости ниҳоии мо пеш аз он ки дар ниҳоят бо он хайрухуш кунем!

Манбаъ: will.com

Илова Эзоҳ