{"id":35259,"date":"2019-10-31T22:03:17","date_gmt":"2019-10-31T19:03:17","guid":{"rendered":"https:\/\/prohoster.info\/blog\/istoriya-odnogo-sql-rassledovaniya\/"},"modified":"2019-10-31T22:03:17","modified_gmt":"2019-10-31T19:03:17","slug":"istoriya-odnogo-sql-rassledovaniya","status":"publish","type":"post","link":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","title":{"rendered":"P\u0159\u00edb\u011bh jednoho SQL vy\u0161et\u0159ov\u00e1n\u00ed","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

V prosince lo\u0148sk\u00e9ho roku jsem obdr\u017eel zaj\u00edmavou zpr\u00e1vu o chyb\u011b od t\u00fdmu podpory VWO. Doba na\u010d\u00edt\u00e1n\u00ed jednoho z analytick\u00fdch report\u016f pro velk\u00e9ho korpor\u00e1tn\u00edho klienta se zd\u00e1la b\u00fdt nep\u0159im\u011b\u0159en\u011b dlouh\u00e1. A proto\u017ee to spad\u00e1 do m\u00e9 odpov\u011bdnosti, okam\u017eit\u011b jsem se soust\u0159edil na \u0159e\u0161en\u00ed probl\u00e9mu.<\/p>\n

<\/p>\n

Pozad\u00ed<\/h2>\n

<\/p>\n

Abych objasnil, o co se jedn\u00e1, pov\u00edm jenom trochu o VWO. Je to platforma, pomoc\u00ed kter\u00e9 m\u016f\u017eete spou\u0161t\u011bt r\u016fzn\u00e9 c\u00edlen\u00e9 kampan\u011b na sv\u00fdch webech: prov\u00e1d\u011bt A\/B experimenty, sledovat n\u00e1v\u0161t\u011bvn\u00edky a konverze, analyzovat prodejn\u00ed trycht\u00fd\u0159e, zobrazovat tepeln\u00e9 mapy a p\u0159ehr\u00e1vat z\u00e1znamy n\u00e1v\u0161t\u011bv.<\/p>\n

<\/p>\n

Ale nejd\u016fle\u017eit\u011bj\u0161\u00ed na platform\u011b je tvorba report\u016f. V\u0161echny v\u00fd\u0161e uveden\u00e9 funkce jsou vz\u00e1jemn\u011b propojen\u00e9. A pro korpor\u00e1tn\u00ed klienty by obrovsk\u00e9 mno\u017estv\u00ed informac\u00ed bylo prost\u011b neu\u017eite\u010dn\u00e9 bez siln\u00e9 platformy, kter\u00e1 by je reprezentovala pro analytiku.<\/p>\n

<\/p>\n

Pomoc\u00ed platformy m\u016f\u017eete prov\u00e9st libovoln\u00fd dotaz na velk\u00e9m souboru dat. Zde je jednoduch\u00fd p\u0159\u00edklad:<\/p>\n

<\/p>\n

Zobrazit v\u0161echny kliknut\u00ed na str\u00e1nce \"abc.com\"\nOD <data d1> DO <data d2>\npro u\u017eivatele, kte\u0159\u00ed\npou\u017e\u00edvali Chrome NEBO\n(b byli v Evrop\u011b A pou\u017e\u00edvali iPhone)<\/pre>\n
<\/p>\n
V\u0161imn\u011bte si logick\u00fdch oper\u00e1tor\u016f. Jsou k dispozici pro klienty v rozhran\u00ed dotazu, aby mohli vytv\u00e1\u0159et libovoln\u011b slo\u017eit\u00e9 dotazy pro z\u00edsk\u00e1n\u00ed vzork\u016f.<\/p>\n
<\/p>\n
Pomal\u00fd dotaz<\/h2>\n
<\/p>\n
Klient, o kter\u00e9m je \u0159e\u010d, se pokusil ud\u011blat n\u011bco, co by intuitivn\u011b m\u011blo fungovat rychle:<\/p>\n
<\/p>\n
Zobraz v\u0161echny z\u00e1znamy sezen\u00ed\npro u\u017eivatele, kte\u0159\u00ed nav\u0161t\u00edvili jakoukoli str\u00e1nku\ns URL, kde je \"\\\/jobs\"<\/pre>\n
<\/p>\n
Na tomto webu byl obrovsk\u00fd objem provozu a uchov\u00e1vali jsme v\u00edce ne\u017e milion unik\u00e1tn\u00edch URL adres pouze pro n\u011bj. A cht\u011bli naj\u00edt celkem jednoduchou \u0161ablonu URL t\u00fdkaj\u00edc\u00ed se jejich obchodn\u00edho modelu.<\/p>\n
\n<\/a><\/noindex><\/p>\n
P\u0159edb\u011b\u017en\u00e9 vy\u0161et\u0159ov\u00e1n\u00ed<\/h2>\n
<\/p>\n
Pod\u00edvejme se, co se d\u011bje v datab\u00e1zi. N\u00ed\u017ee je p\u016fvodn\u00ed pomal\u00fd SQL dotaz:<\/p>\n
<\/p>\n
SELECT \n    count(*) \nFROM \n    acc_{account_id}.urls as recordings_urls, \n    acc_{account_id}.recording_data as recording_data, \n    acc_{account_id}.sessions as sessions \nWHERE \n    recording_data.usp_id = sessions.usp_id \n    AND sessions.referrer_id = recordings_urls.id \n    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com\\\/jobs%')::text[]   ) \n    AND r_time > to_timestamp(1542585600) \n    AND r_time < to_timestamp(1545177599) \n    AND recording_data.duration >=5 \n    AND recording_data.num_of_pages > 0 ;<\/code><\/pre>\n
<\/p>\n
A tady jsou \u010dasy:<\/p>\n
<\/p>\n
O\u010dek\u00e1van\u00fd \u010das: 1.480 ms\nDoba trv\u00e1n\u00ed: 1431924.650 ms<\/pre>\n
<\/p>\n
Dotaz proch\u00e1zel 150 tis\u00edc \u0159\u00e1dk\u016f. Pl\u00e1nova\u010d dotaz\u016f uk\u00e1zal n\u011bkolik zaj\u00edmav\u00fdch detail\u016f, ale \u017e\u00e1dn\u00e1 o\u010dividn\u00e1 \u00fazk\u00e1 m\u00edsta.<\/p>\n
<\/p>\n
Poj\u010fme se pod\u00edvat na dotaz d\u00e1l. Jak je vid\u011bt, d\u011bl\u00e1 JOIN<\/code> t\u0159\u00ed tabulek:<\/p>\n
<\/p>\n
    \n
  1. sessions<\/strong>: pro zobrazen\u00ed relace: prohl\u00ed\u017ee\u010d, u\u017eivatelsk\u00e1 agentura, zem\u011b atd.<\/li>\n
  2. recording_data<\/strong>: zaznamenan\u00e9 URL, str\u00e1nky, d\u00e9lka n\u00e1v\u0161t\u011bv<\/li>\n
  3. urls<\/strong>: abychom se vyhnuli duplikaci extr\u00e9mn\u011b dlouh\u00fdch URL, ukl\u00e1d\u00e1me je do samostatn\u00e9 tabulky.<\/li>\n<\/ol>\n
    <\/p>\n
    Tak\u00e9 si v\u0161imn\u011bte, \u017ee v\u0161echny na\u0161e tabulky jsou ji\u017e rozd\u011bleny podle account_id<\/code>. T\u00edm je vylou\u010deno, \u017ee by kv\u016fli jednomu obzvl\u00e1\u0161t\u011b velk\u00e9mu \u00fa\u010dtu nastaly probl\u00e9my ostatn\u00edm.<\/p>\n
    <\/p>\n
    Hled\u00e1n\u00ed stop<\/h2>\n
    <\/p>\n
    P\u0159i bli\u017e\u0161\u00edm pohledu vid\u00edme, \u017ee n\u011bco v konkr\u00e9tn\u00edm dotazu nen\u00ed v po\u0159\u00e1dku. Stoj\u00ed za to pod\u00edvat se na tento \u0159\u00e1dek:<\/p>\n
    <\/p>\n
    urls && array(\n\tselect id from acc_{account_id}.urls \n\twhere url ILIKE '%enterprise_customer.com\/jobs%'\n)::text[]<\/code><\/pre>\n
    <\/p>\n
    Prvn\u00ed my\u0161lenka byla, \u017ee mo\u017en\u00e1 kv\u016fli ILIKE<\/code> na v\u0161ech t\u011bch dlouh\u00fdch URL (m\u00e1me v\u00edce ne\u017e 1,4 milionu unik\u00e1tn\u00edch\u00a0<\/strong>URL adres shrom\u00e1\u017ed\u011bn\u00fdch pro tento \u00fa\u010det) m\u016f\u017ee v\u00fdkonnost klesat.<\/p>\n
    <\/p>\n
    Ale ne \u2014 to nen\u00ed ono!<\/p>\n
    <\/p>\n
    SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com\/jobs%';\n  id\n--------\n ...\n(198661 rows)\n\n\u010cas: 5231.765 ms<\/code><\/pre>\n
    <\/p>\n
    Samotn\u00fd dotaz na vyhled\u00e1v\u00e1n\u00ed podle vzoru trv\u00e1 pouze 5 sekund. Vyhled\u00e1v\u00e1n\u00ed podle vzoru na milionu unik\u00e1tn\u00edch URL rozhodn\u011b nen\u00ed probl\u00e9m.<\/p>\n
    <\/p>\n
    Dal\u0161\u00ed podez\u0159el\u00fd na seznamu \u2014 n\u011bkolik JOIN<\/code>. Mo\u017en\u00e1 jejich nadm\u011brn\u00e9 pou\u017e\u00edv\u00e1n\u00ed vedlo k zpomalen\u00ed? Obvykle JOIN<\/code>\u2018y jsou nejv\u00edce z\u0159ejm\u00fdmi kandid\u00e1ty na probl\u00e9my s v\u00fdkonem, ale nev\u011b\u0159il jsem, \u017ee n\u00e1\u0161 p\u0159\u00edpad je typick\u00fd.<\/p>\n
    <\/p>\n
    analytics_db=# SELECT\n    count(*)\nFROM\n    acc_{account_id}.urls as recordings_urls,\n    acc_{account_id}.recording_data_0 as recording_data,\n    acc_{account_id}.sessions_0 as sessions\nWHERE\n    recording_data.usp_id = sessions.usp_id\n    AND sessions.referrer_id = recordings_urls.id\n    AND r_time > to_timestamp(1542585600)\n    AND r_time =5\n    AND recording_data.num_of_pages > 0 ;\n count\n-------\n  8086\n(1 row)\n\n\u010cas: 147.851 ms<\/code><\/pre>\n
    <\/p>\n
    A to tak\u00e9 nebyl n\u00e1\u0161 p\u0159\u00edpad. JOIN<\/code>\u2018y se uk\u00e1zaly jako velmi rychl\u00e9.<\/p>\n
    <\/p>\n
    Z\u00fa\u017een\u00ed okruhu podez\u0159el\u00fdch<\/h2>\n
    <\/p>\n
    Byl jsem p\u0159ipraven za\u010d\u00edt m\u011bnit dotaz, abych dos\u00e1hl jak\u00fdchkoli mo\u017en\u00fdch zlep\u0161en\u00ed v\u00fdkonu. My s t\u00fdmem jsme vyvinuli 2 hlavn\u00ed n\u00e1pady:<\/p>\n
    <\/p>\n