{"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\/et\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","title":{"rendered":"\u00dche SQL-uuringu lugu","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Eelmise aasta detsembris sain VWO tugimeeskonnalt huvitava veateate. \u00dche anal\u00fc\u00fcsiaruande laadimisaeg suure ettev\u00f5tte kliendi jaoks n\u00e4is olevat \u00fclem\u00e4\u00e4ra pikk. Kuna see kuulub minu vastutusalasse, keskendusin viivitamatult probleemi lahendamisele.<\/p>\n

<\/p>\n

Eelalugu<\/h2>\n

<\/p>\n

Et oleks selge, millest jutt, r\u00e4\u00e4gin pisut VWO-st. See on platvorm, millega saab oma saitidel k\u00e4ivitada erinevaid sihitud kampaaniaid: teostada A\/B katseid, j\u00e4lgida k\u00fclastajaid ja konversioone, teha m\u00fc\u00fcgivihje anal\u00fc\u00fcse, kuvada soojuskaarte ja esitada k\u00fclastuse salvestusi.<\/p>\n

<\/p>\n

Kuid k\u00f5ige olulisem platvormi aspekt on aruannete koostamine. K\u00f5ik eelnevalt mainitud funktsioonid on omavahel seotud. Suur hulk teavet oleks ettev\u00f5tte klientidele lihtsalt kasutuks, kui see ei oleks esitatud anal\u00fc\u00fcsimiseks m\u00f5eldud platvormi kaudu.<\/p>\n

<\/p>\n

Platvormi abil saab teha vabalt valitud p\u00e4ringu suure hulga andmete pealt. Siin on lihtne n\u00e4ide:<\/p>\n

<\/p>\n

Kuva k\u00f5ik kl\u00f5psud lehel \"abc.com\" \nPERIOODILT <kuup\u00e4ev d1> KUNI <kuup\u00e4ev d2> \ninimeste jaoks, kes \nkasutasid Chrome'i V\u00d5I \n(olid Euroopas JA kasutasid iPhone'i)<\/pre>\n
<\/p>\n
Pange t\u00e4hele loogika operaatorite olemust. Need on saadaval klientide p\u00e4ringu liideses, et luua piisavalt keerulisi p\u00e4ringuid andmestike saamiseks.<\/p>\n
<\/p>\n
Aeglane p\u00e4ring<\/h2>\n
<\/p>\n
Kliendil, millest me r\u00e4\u00e4gime, oli soov teha midagi, mis intuitiivselt peaks t\u00f6\u00f6tama kiiresti:<\/p>\n
<\/p>\n
Kuva k\u00f5ik seansi salvestused \nk\u00fclastajate jaoks, kes on k\u00e4inud mis tahes lehel \nURL-iga, mis sisaldab \"\/jobs\".<\/pre>\n
<\/p>\n
Saidil oli tohutult liiklust, ja me s\u00e4ilitasime \u00fcle miljoni unikaalse URL-aadressi vaid selle jaoks. Nad soovisid leida \u00fcsnagi lihtsa URL-malli, mis seondub nende \u00e4rimudeliga.<\/p>\n
\n<\/a><\/noindex><\/p>\n
Eeluurimine<\/h2>\n
<\/p>\n
Vaatame, mis toimub andmebaasis. Allpool on algne aeglane SQL-p\u00e4ring:<\/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
Siin on ajad:<\/p>\n
<\/p>\n
Eeldatav aeg: 1.480 ms\nT\u00e4itmise aeg: 1431924.650 ms<\/pre>\n
<\/p>\n
P\u00e4ring l\u00e4bis 150 tuhat rida. P\u00e4ringu planeerija n\u00e4itas m\u00f5ningaid huvitavaid detaile, kuid mingeid ilmseid kitsaskohti ei olnud.<\/p>\n
<\/p>\n
Vaatame p\u00e4ringut p\u00f5hjalikumalt. Nagu n\u00e4ha, tegeleb see JOIN<\/code> kolme tabeliga:<\/p>\n
<\/p>\n
    \n
  1. sessions<\/strong>: seansi teabe kuvamiseks: brauser, kasutajaagent, riik jne.<\/li>\n
  2. recording_data<\/strong>: salvestatud URL-id, lehek\u00fcljed, visiitide kestus.<\/li>\n
  3. urls<\/strong>: et v\u00e4ltida \u00e4\u00e4rmiselt suurte URL-ide dubleerimist, s\u00e4ilitame need eraldi tabelis.<\/li>\n<\/ol>\n
    <\/p>\n
    Pange t\u00e4hele, et k\u00f5ik meie tabelid on juba jagatud account_id<\/code>. Seega elimineeritakse olukord, kus \u00fche \u00fclem\u00e4\u00e4ra suure konto t\u00f5ttu tekivad probleemid \u00fclej\u00e4\u00e4nutele.<\/p>\n
    <\/p>\n
    T\u00f5endite otsing<\/h2>\n
    <\/p>\n
    S\u00fcvenedes, m\u00e4rkame, et konkreetse p\u00e4ringu korral on midagi valesti. Tasub t\u00e4helepanu p\u00f6\u00f6rata sellele reale:<\/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
    Esimene m\u00f5te oli, et \u00e4kki, kuna ILIKE<\/code> k\u00f5ikide nende pikkade URL-ide korral (meil on \u00fcle 1,4 miljoni unikaalse\u00a0<\/strong>URL-aadressi, mis on selle konto jaoks kogutud) v\u00f5ib j\u00f5udlus langetada.<\/p>\n
    <\/p>\n
    Aga ei \u2014 asi pole selles!<\/p>\n
    <\/p>\n
    SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com\/jobs%';\n  id\n--------\n ...\n(198661 real)\n\nAeg: 5231.765 ms<\/code><\/pre>\n
    <\/p>\n
    Otsingup\u00e4ring mallide j\u00e4rgi v\u00f5tab ainult 5 sekundit. Mallide p\u00f5hjal otsimine \u00fcle miljoni unikaalse URL-i osas pole kindlasti probleem.<\/p>\n
    <\/p>\n
    J\u00e4rgmine kahtlane kahtlane \u2014 mitu JOIN<\/code>. V\u00f5ib-olla nende \u00fclem\u00e4\u00e4rane kasutamine viis aeglustumise juurde? Tavaliselt JOIN<\/code>Need on k\u00f5ige ilmsemad kandidaadid j\u00f5udlusprobleemideks, kuid ma ei usu, et meie juhtum on t\u00fc\u00fcpiline.<\/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 < to_timestamp(1545177599)\n    AND recording_data.duration >=5\n    AND recording_data.num_of_pages > 0 ;\n count\n-------\n  8086\n(1 rida)\n\nAeg: 147.851 ms<\/code><\/pre>\n
    <\/p>\n
    Ja see ei olnud ka meie juhtum. JOIN<\/code>Need osutusid \u00fcllatavalt kiireteks.<\/p>\n
    <\/p>\n
    Kitsendame kahtlusi<\/h2>\n
    <\/p>\n
    Olin valmis hakkama p\u00e4ringut muutma, et saavutada v\u00f5imalikult suur j\u00f5udluse paranemine. Meie meeskond v\u00e4lja t\u00f6\u00f6tas kaks peamist ideed:<\/p>\n
    <\/p>\n