Өткөн жылдын декабрь айында мен VWO колдоо тобунан кызыктуу ката отчетун алдым. Ири корпоративдик кардар үчүн аналитикалык отчеттордун бири үчүн жүктөө убактысы өтө кыйындай көрүндү. Бул менин жоопкерчилигим болгондуктан, мен дароо маселени чечүүгө басым жасадым.
prehistory
Эмне жөнүндө айтып жатканымды түшүнүү үчүн, мен сизге VWO жөнүндө бир аз айтып берейин. Бул платформа, анын жардамы менен сиз веб-сайттарыңызда ар кандай максаттуу кампанияларды жүргүзө аласыз: A/B эксперименттерин жүргүзүү, конверттерди жана конверсияларды көзөмөлдөө, сатуу линиясын талдоо, жылуулук карталарын көрсөтүү жана зыярат жазууларын ойнотуу.
Бирок платформанын эң негизгиси - отчет берүү. Жогорудагы функциялардын баары бири-бири менен байланышкан. Ал эми корпоративдик кардарлар үчүн чоң көлөмдөгү маалымат аны аналитика түрүндө көрсөткөн күчтүү платформасыз жөн эле пайдасыз болмок.
Платформаны колдонуу менен сиз чоң маалымат топтомуна кокустук сурам жасай аласыз. Бул жерде жөнөкөй мисал:
Chrome ЖЕ (Европада жайгашкан ЖАНА iPhone колдонгон) адамдар үчүн "abc.com" баракчасында <date d1> ДЕНЕН <date d2> чейин бардык чыкылдатууларды көрсөтүү
Буль операторлоруна көңүл буруңуз. Алар үлгүлөрдү алуу үчүн өзүм билемдик менен татаал сурамдарды жасоо үчүн суроо интерфейсинде кардарларга жеткиликтүү.
Жай сурам
Каралып жаткан кардар интуитивдик тез иштеши керек болгон нерсени жасоого аракет кылган:
"/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 мс Аткаруу убактысы: 1431924.650 мс
Сурам 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'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-ами жана субсуроонун өзү өзүнчө тез, бирок чогуу абдан жайбы?
- Чакан суроону 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 сурамдары кандай аткарыларын, маалымат базасы суроо пландарын кантип түзөрүн, индекстердин кантип иштээрин жана жөн гана сиз иштеп жаткан маалыматтардын көлөмүн жакшы түшүнүү менен, сиз сурамдарды оптималдаштырууда абдан ийгиликтүү боло аласыз. Бирок, ар кандай ыкмаларды сынап көрүүнү улантуу жана көйгөйлөрдү акырындык менен чечип, тоскоолдуктарды табуу бирдей маанилүү.
Ушул сыяктуу натыйжаларга жетишүүнүн эң жакшы жагы - ылдамдыктын байкалаарлык жакшырышында - мурда жүктөлбөгөн отчет азыр дээрлик заматта жүктөлөт.
Өзгөчө рахмат менин жолдошторум Адитья Мишранын буйругу боюнча, Адитя Гауру и мээ чабуулу үчүн жана Динкар Пандир акыркы өтүнүчүбүздө биз аны менен коштошконго чейин маанилүү катаны тапканыбыз үчүн!
Source: www.habr.com