ProHoster > Блог > Захиргаа > Нэг SQL судалгааны түүх

Нэг SQL судалгааны түүх

Өнгөрсөн XNUMX-р сард би VWO-ийн тусламжийн багаас алдааны талаархи сонирхолтой тайланг хүлээн авсан. Томоохон корпорацын үйлчлүүлэгчдэд зориулсан аналитик тайлангуудын нэгийг ачаалах хугацаа хэт их санагдсан. Энэ бол миний хариуцах чиглэл учраас би тэр даруй асуудлыг шийдвэрлэхэд анхаарлаа хандуулсан.

Эрьт урьдын түүх

Юу ярьж байгаагаа тодорхой болгохын тулд би танд VWO-ийн талаар бага зэрэг хэлье. Энэ бол та өөрийн вэбсайтууд дээр янз бүрийн зорилтот кампанит ажил явуулах боломжтой платформ юм: A/B туршилт хийх, зочдод болон хөрвүүлэлтийг хянах, борлуулалтын юүлүүрт дүн шинжилгээ хийх, дулааны зураг харуулах, зочилсон бичлэгийг тоглуулах.

Гэхдээ платформын хамгийн чухал зүйл бол тайлагнах явдал юм. Дээрх бүх функцууд хоорондоо холбоотой байдаг. Мөн корпорацийн үйлчлүүлэгчдийн хувьд асар их хэмжээний мэдээлэл нь үүнийг аналитик хэлбэрээр үзүүлдэг хүчирхэг платформгүйгээр зүгээр л ашиггүй болно.

Платформыг ашигласнаар та том өгөгдлийн багц дээр санамсаргүй асуулга хийх боломжтой. Энд энгийн жишээ байна:

Chrome ашигладаг ЭСВЭЛ (Европт байрладаг, iPhone ашигладаг) хүмүүсийн хувьд "abc.com" хуудсан дээрх <огноо d1>-с <огноо d2> хүртэлх бүх товшилтыг харуул.

Boolean операторуудад анхаарлаа хандуулаарай. Эдгээр нь дээж авахын тулд дур мэдэн нарийн төвөгтэй асуулга хийх хүсэлтийн интерфейс дэх үйлчлүүлэгчдэд боломжтой.

Удаан хүсэлт

Тухайн үйлчлүүлэгч нь зөн совингоор хурдан ажиллах ёстой зүйлийг хийхийг оролдсон:

"/ажлын байр" агуулсан 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. urls: Хэт том 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маш хурдан болсон.

Сэжигтнүүдийн хүрээг нарийсгаж байна

Гүйцэтгэлийн сайжруулалтад хүрэхийн тулд би асуулгыг өөрчилж эхлэхэд бэлэн байсан. Миний баг болон би хоёр үндсэн санааг боловсруулсан:

  • Дэд асуулгын 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-уудтай огтлолцсон хэсгийг олдог гэсэн үг юм. Энд байгаа "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-s хурдан, дэд асуулга хурдан, ганц зүйл л үлдсэн && оператор.

Энэ бол зүгээр л гол үйл ажиллагаа юм. Загвар хайхын тулд бид үргэлж URL-уудын үндсэн хүснэгтийг бүхэлд нь хайх хэрэгтэй бөгөөд бид үргэлж уулзваруудыг олох хэрэгтэй. Бид шууд URL бүртгэлээр хайлт хийх боломжгүй, учир нь эдгээр нь зөвхөн ID-д хамаарах болно 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

сэтгэгдэл нэмэх