{"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\/lv\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","title":{"rendered":"Vienas SQL izmekl\u0113\u0161anas st\u0101sts","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Pag\u0101ju\u0161aj\u0101 gada decembr\u012b sa\u0146\u0113mu interesantu zi\u0146ojumu par k\u013c\u016bdu no VWO atbalsta komandas. Liel\u0101 korporat\u012bv\u0101 klienta anal\u012btisk\u0101 zi\u0146ojuma iel\u0101des laiks \u0161\u0137ita neticami gar\u0161. T\u0101 k\u0101 \u0161\u012b ir mana atbild\u012bba, es t\u016bl\u012bt piev\u0113rsos probl\u0113mas risin\u0101\u0161anai.<\/p>\n<p><\/p>\n<h2>Iepriek\u0161\u0113j\u0101 pieredze<\/h2>\n<p><\/p>\n<p>Lai saprastu, par ko ir runa, p\u0101ris v\u0101rdos past\u0101st\u012b\u0161u par VWO. T\u0101 ir platforma, ar kuras pal\u012bdz\u012bbu var palai\u017e da\u017e\u0101das m\u0113r\u0137tiec\u012bgas kampa\u0146as sav\u0101s vietn\u0113s: veikt A\/B eksperimentus, izsekot apmekl\u0113t\u0101jus un konversijas, veikt p\u0101rdo\u0161anas piltuves anal\u012bzi, att\u0113lot siltuma kartes un atska\u0146ot apmekl\u0113jumu ierakstus.<\/p>\n<p><\/p>\n<p>Bet vissvar\u012bg\u0101kais platform\u0101 ir zi\u0146ojumu izveido\u0161ana. Visas iepriek\u0161min\u0113t\u0101s funkcijas ir savstarp\u0113ji saist\u012btas. Un korporat\u012bviem klientiem milz\u012bgs inform\u0101cijas apjoms b\u016btu vienk\u0101r\u0161i bezj\u0113dz\u012bgs bez sp\u0113c\u012bgas platformas, kas to p\u0101rst\u0101v anal\u012bzei.<\/p>\n<p><\/p>\n<p>Izmantojot platformu, var veikt br\u012bvas piepras\u012bjumus liel\u0101 datu kop\u0101. \u0160eit ir vienk\u0101r\u0161s piem\u0113rs:<\/p>\n<p><\/p>\n<pre>Par\u0101d\u012bt visus klik\u0161\u0137us uz lapas \"abc.com\"\nNO &lt;datums d1&gt; L\u012aDZ &lt;datums d2&gt;\ncilv\u0113kiem, kuri\nizmantoja Chrome VAI\n(bija Eirop\u0101 UN izmantoja iPhone)<\/pre>\n<p><\/p>\n<p>Piev\u0113rsiet uzman\u012bbu lo\u0123iskajiem operatoriem. Tie ir pieejami klientiem piepras\u012bjuma interfeis\u0101, lai veiktu jebkura veida sare\u017e\u0123\u012btus piepras\u012bjumus par paraugu sa\u0146em\u0161anu.<\/p>\n<p><\/p>\n<h2>L\u0113ns vaic\u0101jums<\/h2>\n<p><\/p>\n<p>Klients, par kuru run\u0101jam, cent\u0101s izdar\u012bt kaut ko, kas intuit\u012bvi b\u016btu j\u0101str\u0101d\u0101 \u0101tri:<\/p>\n<p><\/p>\n<pre>Par\u0101diet visus sesiju ierakstus\nl\u012bdz lietot\u0101jiem, kas apmekl\u0113ju\u0161i jebkuru lapu\nar URL, kur\u0101 ir \"\\\/jobs\"<\/pre>\n<p><\/p>\n<p>\u0160aj\u0101 vietn\u0113 bija milz\u012bgs datpl\u016bsmas apjoms, un m\u0113s glab\u0101j\u0101m vair\u0101k nek\u0101 miljonu unik\u0101lu URL adreses tikai tai. Un vi\u0146i v\u0113l\u0113j\u0101s atrast diezgan vienk\u0101r\u0161u URL paraugu, kas attiektos uz vi\u0146u biznesa modeli.<\/p>\n<p>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h2>S\u0101kotn\u0113jais izmekl\u0113jums<\/h2>\n<p><\/p>\n<p>Apskat\u012bsim, kas notiek datu b\u0101z\u0113. Zem\u0101k ir nor\u0101d\u012bts l\u0113nais SQL piepras\u012bjums:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">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 &amp;&amp;  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com\\\/jobs%')::text[]   ) \n    AND r_time &gt; to_timestamp(1542585600) \n    AND r_time &lt; to_timestamp(1545177599) \n    AND recording_data.duration &gt;=5 \n    AND recording_data.num_of_pages &gt; 0 ;<\/code><\/pre>\n<p><\/p>\n<p>L\u016bk, laiki:<\/p>\n<p><\/p>\n<pre>Pl\u0101notais laiks: 1.480 ms\nIzpildes laiks: 1431924.650 ms<\/pre>\n<p><\/p>\n<p>Piepras\u012bjums apiet 150 t\u016bksto\u0161us rindu. Vaic\u0101jumu pl\u0101not\u0101js par\u0101d\u012bja da\u017eas interesantas deta\u013cas, ta\u010du ac\u012bmredzamu v\u0101ju vietu nebija.<\/p>\n<p><\/p>\n<p>Paskat\u012bsimies uz \u0161o piepras\u012bjumu vair\u0101k. K\u0101 redzams, tas veic <code>JOIN<\/code> tr\u012bs tabulas:<\/p>\n<p><\/p>\n<ol>\n<li><strong>sessions<\/strong>: lai att\u0113lotu sesijas inform\u0101ciju: p\u0101rl\u016bks, lietot\u0101ja a\u0123ents, valsts un t\u0101 t\u0101l\u0101k.<\/li>\n<li><strong>recording_data<\/strong>: ierakst\u012bt\u0101s URL, lapas, apmekl\u0113jumu ilgums<\/li>\n<li><strong>urls<\/strong>: lai izvair\u012btos no \u0101rk\u0101rt\u012bgi garu URL dubl\u0113\u0161anas, m\u0113s tos uzglab\u0101jam atsevi\u0161\u0137\u0101 tabul\u0101.<\/li>\n<\/ol>\n<p><\/p>\n<p>T\u0101pat \u0146emiet v\u0113r\u0101, ka visas m\u016bsu tabulas jau ir sadal\u012btas p\u0113c <code>account_id<\/code>. T\u0101d\u0113j\u0101di tiek izsl\u0113gta situ\u0101cija, kad viena \u012bpa\u0161i liela konta d\u0113\u013c citas sastop gr\u016bt\u012bbas.<\/p>\n<p><\/p>\n<h2>Pier\u0101d\u012bjumu mekl\u0113jumos<\/h2>\n<p><\/p>\n<p>P\u0113c tuv\u0101kas apskates m\u0113s redzam, ka kaut kas konkr\u0113taj\u0101 piepras\u012bjum\u0101 nav k\u0101rt\u012bb\u0101. J\u0101piev\u0113r\u0161 uzman\u012bba \u0161ai rindai:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">urls &amp;&amp; array(\n\tselect id from acc_{account_id}.urls \n\twhere url ILIKE '%enterprise_customer.com\/jobs%'\n)::text[]<\/code><\/pre>\n<p><\/p>\n<p>Pirmais domas izn\u0101kums bija, ka iesp\u0113jams, d\u0113\u013c <code>ILIKE<\/code> visos \u0161ajos garajos URL (mums ir vair\u0101k nek\u0101 1,4 miljoni <strong>unik\u0101lo\u00a0<\/strong>URL adreses, kas apkopotas \u0161im kontam) veiktsp\u0113ja var\u0113tu samazin\u0101ties.<\/p>\n<p><\/p>\n<p>Bet n\u0113 \u2014 t\u0101 nav lieta!<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com\/jobs%';\n  id\n--------\n ...\n(198661 rindi\u0146as)\n\nLaiks: 5231.765 ms<\/code><\/pre>\n<p><\/p>\n<p>Pats mekl\u0113\u0161anas piepras\u012bjums p\u0113c mode\u013ca aiz\u0146em tikai 5 sekundes. Mekl\u0113\u0161ana p\u0113c mode\u013ca miljon\u0101 unik\u0101lo URL noteikti nav probl\u0113ma.<\/p>\n<p><\/p>\n<p>N\u0101kamais aizdom\u012bgs sarakst\u0101 \u2014 vair\u0101ki <code>JOIN<\/code>. Iesp\u0113jams, to p\u0101rm\u0113r\u012bga izmanto\u0161ana izrais\u012bja pal\u0113nin\u0101\u0161anos? Parasti <code>JOIN<\/code>\u2018y ir \u2014 viss ac\u012bmredzam\u0101kie kandid\u0101ti uz veiktsp\u0113jas probl\u0113m\u0101m, ta\u010du es netic\u0113ju, ka m\u016bsu gad\u012bjums ir tipisks.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">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 &gt; to_timestamp(1542585600)\n    AND r_time =5\n    AND recording_data.num_of_pages &gt; 0 ;\n count\n-------\n  8086\n(1 rinda)\n\nLaiks: 147.851 ms<\/code><\/pre>\n<p><\/p>\n<p>Un tas ar\u012b nebija m\u016bsu gad\u012bjums. <code>JOIN<\/code>\u2018y izr\u0101d\u012bj\u0101s diezgan \u0101tri.<\/p>\n<p><\/p>\n<h2>Sa\u0161aurin\u0101m aizdom\u012bgo loku<\/h2>\n<p><\/p>\n<p>Es biju gatavs s\u0101kt main\u012bt piepras\u012bjumu, lai pan\u0101ktu jebk\u0101dus iesp\u0113jamos veiktsp\u0113jas uzlabojumus. M\u0113s ar komandu izstr\u0101d\u0101j\u0101m 2 galven\u0101s idejas:<\/p>\n<p><\/p>\n<ul>\n<li><strong>Izmantot EXISTS URL apak\u0161piepras\u012bjumam<\/strong>: M\u0113s v\u0113l\u0113j\u0101mies v\u0113lreiz p\u0101rbaud\u012bt, vai nav probl\u0113mu ar apak\u0161piepras\u012bjumu URL. Viens no veidiem, k\u0101 to pan\u0101kt \u2014 vienk\u0101r\u0161i izmantot <code>EXISTS<\/code>. <code>EXISTS<\/code> <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS\">var<\/a><\/noindex> krietni uzlabot veiktsp\u0113ju, jo t\u0101 beidzas uzreiz, tikl\u012bdz atrod vien\u012bgo rindu p\u0113c nosac\u012bjuma.<\/li>\n<\/ul>\n<p><\/p>\n<pre><code class=\"plaintext\">SELECT\n\tcount(*) \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  (  1 = 1  )\n    AND sessions.referrer_id = recordings_urls.id\n    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com\/jobs%'))\n    AND r_time &gt; to_timestamp(1547585600)\n    AND r_time =5\n    AND recording_data.num_of_pages &gt; 0 ;\n count\n 32519\n(1 row)\nTime: 1636.637 ms<\/code><\/pre>\n<p><\/p>\n<p>Nu j\u0101. Apak\u0161uzdots, kad tas ir ietverts,\u00a0<code>EXISTS<\/code>, padara visu super \u0101tru. N\u0101kamais lo\u0123iskais jaut\u0101jums ir, k\u0101p\u0113c vaic\u0101jums ar <code>JOIN<\/code>-\u0101m un pats apak\u0161uzdots ir \u0101tri atsevi\u0161\u0137i, bet kop\u0101 tie ir \u0161ausm\u012bgi l\u0113ni?<\/p>\n<p><\/p>\n<ul>\n<li><strong>P\u0101rvietojam apak\u0161uzdotu uz CTE <\/strong>: ja vaic\u0101jums ir \u0101trs pats par sevi, m\u0113s varam vispirms apr\u0113\u0137in\u0101t \u0101tru rezult\u0101tu un p\u0113c tam pieg\u0101d\u0101t to galvenajam vaic\u0101jumam.<\/li>\n<\/ul>\n<p><\/p>\n<pre><code class=\"plaintext\">WITH matching_urls AS (\n    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com\/jobs%'\n)\n\nSELECT \n    count(*) FROM acc_{account_id}.urls as recordings_urls, \n    acc_{account_id}.recording_data as recording_data, \n    acc_{account_id}.sessions as sessions,\n    matching_urls\nWHERE \n    recording_data.usp_id = sessions.usp_id \n    AND  (  1 = 1  )  \n    AND sessions.referrer_id = recordings_urls.id\n    AND (urls &amp;&amp; array(SELECT id from matching_urls)::text[])\n    AND r_time &gt; to_timestamp(1542585600) \n    AND r_time =5 \n    AND recording_data.num_of_pages &gt; 0;<\/code><\/pre>\n<p><\/p>\n<p>Bet tas joproj\u0101m bija \u013coti l\u0113ni.<\/p>\n<p><\/p>\n<h2>Atrodam vain\u012bgo<\/h2>\n<p><\/p>\n<p>Visu \u0161o laiku ac\u012bs acu priek\u0161\u0101 mirgoja viena nianse, no kuras es past\u0101v\u012bgi nov\u0113rstos. Bet, t\u0101 k\u0101 nekas cits vairs nebija palicis, es nol\u0113mu paskat\u012bties uz to. Es run\u0101ju par <code>&amp;&amp;<\/code> operatoru. L\u012bdz \u0161im <code>EXISTS<\/code> vienk\u0101r\u0161i uzlabojusi veiktsp\u0113ju, <code>&amp;&amp;<\/code> bija vien\u012bgais paliku\u0161ais kop\u012bgais faktors vis\u0101s l\u0113n\u0101 vaic\u0101juma versij\u0101s.<\/p>\n<p><\/p>\n<p>Skatoties uz <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/9.1\/functions-array.html\">dokumenti<\/a><\/noindex>, m\u0113s redzam, ka <code>&amp;&amp;<\/code> tiek izmantots, kad nepiecie\u0161ams atrast kop\u012bgus elementus starp diviem mas\u012bviem.<\/p>\n<p><\/p>\n<p>Ori\u0123in\u0101laj\u0101 vaic\u0101jum\u0101 tas ir:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">AND  (  urls &amp;&amp;  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com\/jobs%')::text[]   )<\/code><\/pre>\n<p><\/p>\n<p>Kas noz\u012bm\u0113, ka m\u0113s veicam mekl\u0113\u0161anu p\u0113c parauga m\u016bsu URL, p\u0113c tam atrodam krustojumu ar visiem URL ar kop\u012bgajiem ierakstiem. Tas ir nedaudz mulsino\u0161i, jo 'urls' \u0161eit nenor\u0101da uz tabulu, kur\u0101 ir visi URL, bet uz kolonnu 'urls' tabul\u0101 <code>recording_data<\/code>.<\/p>\n<p><\/p>\n<p>Pieaugot aizdom\u0101m par <code>&amp;&amp;<\/code>, es m\u0113\u0123in\u0101ju atrast tiem apstiprin\u0101jumu vaic\u0101juma pl\u0101n\u0101, ko izveidoju. <code>EXPLAIN ANALYZE<\/code> (man jau bija saglab\u0101ts pl\u0101ns, bet man parasti ir \u0113rt\u0101k eksperiment\u0113t SQL, nek\u0101 m\u0113\u0123in\u0101t saprast va\u013c\u0113j\u0101s vaic\u0101jumu pl\u0101not\u0101jus).<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">Filtra: ((urls &amp;&amp; ($0)::text[]) UN (r_time &gt; '2018-12-17 12:17:23+00'::timestamp ar laika joslu) UN (r_time = '5'::double precision) UN (num_of_pages &gt; 0))\n                           Filtra d\u0113\u013c no\u0146emt\u0101s rindas: 52710<\/code><\/pre>\n<p><\/p>\n<p>Tur bija vair\u0101kas rindu filtrus tikai no <code>&amp;&amp;<\/code>. Kas noz\u012bm\u0113ja, ka \u0161\u012b darb\u012bba nebija tikai d\u0101rga, bet ar\u012b tika izpild\u012bta vair\u0101kas reizes.<\/p>\n<p><\/p>\n<p>Es to p\u0101rbaud\u012bju, izol\u0113jot nosac\u012bjumu<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">SELECT 1\nFROM \n    acc_{account_id}.urls k\u0101 recordings_urls,\n    acc_{account_id}.recording_data_30 k\u0101 recording_data_30,\n    acc_{account_id}.sessions_30 k\u0101 sessions_30 \nWHERE \n\turls &amp;&amp; array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com\/jobs%')::text[]<\/code><\/pre>\n<p><\/p>\n<p>\u0160is vaic\u0101jums tika izpild\u012bts l\u0113ni. T\u0101 k\u0101 <code>JOIN<\/code>-i ir \u0101tri un apak\u0161vaic\u0101jumi ir \u0101tri, palika tikai <code>&amp;&amp;<\/code> operators.<\/p>\n<p><\/p>\n<p>Tie\u0161i tas ir galvenais operators. Mums vienm\u0113r ir j\u0101mekl\u0113 vis\u0101 galvenaj\u0101 URL tabul\u0101, lai mekl\u0113tu p\u0113c parauga, un mums vienm\u0113r ir j\u0101p\u0101rbauda krustojumi. M\u0113s nevaram mekl\u0113t tie\u0161i p\u0113c URL ierakstiem, jo tie ir tikai identifikatori, kas atsaucas uz <code>urls<\/code>.<\/p>\n<p><\/p>\n<h2>Ce\u013c\u0101 uz risin\u0101jumu<\/h2>\n<p><\/p>\n<p><code>&amp;&amp;<\/code> l\u0113ns, jo abi kopas ir milz\u012bgas. Darb\u012bba b\u016bs sal\u012bdzino\u0161i \u0101tra, ja es aizst\u0101\u0161u <code>urls<\/code> uz <code>{ \"http:\/\/google.com\/\", \"http:\/\/wingify.com\/\" }<\/code>.<\/p>\n<p><\/p>\n<p>Es s\u0101ku mekl\u0113t veidu, k\u0101 Postgres veikt vair\u0101kumu krustojumus bez <code>&amp;&amp;<\/code>, bet bez \u012bpa\u0161iem pan\u0101kumiem.<\/p>\n<p><\/p>\n<p>Galu gal\u0101 m\u0113s nol\u0113m\u0101m vienk\u0101r\u0161i atrisin\u0101t probl\u0113mu izol\u0113ti: dod man visas <code>urls<\/code> rindas, kuru URL atbilst paraugam. Bez papildu nosac\u012bjumiem tas b\u016bs \u2014\u00a0<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">SELECT urls.url\nFROM \n\tacc_{account_id}.urls k\u0101 urls,\n\t(SELECT unnest(recording_data.urls) AS id) K\u0100 atv\u0113rti_urls\nWHERE\n\turls.id = atv\u0113rti_urls.id UN\n\turls.url ILIKE '%jobs%'<\/code><\/pre>\n<p><\/p>\n<p>T\u0101 viet\u0101\u00a0<code>JOIN<\/code> sintakse es vienk\u0101r\u0161i izmantoju apak\u0161vaic\u0101jumu un izpletu <code>recording_data.urls<\/code> mas\u012bvu, lai var\u0113tu tie\u0161i piem\u0113rot nosac\u012bjumu <code>WHERE<\/code>.<\/p>\n<p><\/p>\n<p>Visp\u0101r\u0113jais svar\u012bg\u0101kais \u0161eit ir tas, ka <code>&amp;&amp;<\/code> tiek izmantots, lai p\u0101rbaud\u012btu, vai konkr\u0113tais ieraksts satur atbilsto\u0161o URL. Nedaudz piev\u0113r\u0161ot uzman\u012bbu, var redz\u0113t \u0161aj\u0101 oper\u0101cij\u0101 p\u0101rvieto\u0161anos pa mas\u012bva elementiem (vai tabulas rind\u0101m) un apst\u0101\u0161anos, izpildot nosac\u012bjumu (atbilst\u012bbu). Neko neaizmirstat? J\u0101, <code>EXISTS<\/code>.<\/p>\n<p><\/p>\n<p>T\u0101 k\u0101 uz <code>recording_data.urls<\/code> var atsaukties no \u0101rpuses apak\u0161vaic\u0101juma konteksta, kad tas notiek, m\u0113s varam atgriezties pie m\u016bsu vec\u0101 drauga <code>EXISTS<\/code> un iesai\u0146ot to apak\u0161vaic\u012bjum\u0101.<\/p>\n<p><\/p>\n<p>Apvienojot visu kop\u0101, m\u0113s ieg\u016bstam gal\u012bgo optimiz\u0113to vaic\u0101jumu:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">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  (  1 = 1  )  \n    AND sessions.referrer_id = recordings_urls.id \n    AND r_time &gt; to_timestamp(1542585600) \n    AND r_time =5 \n    AND recording_data.num_of_pages &gt; 0\n    AND EXISTS(\n        SELECT urls.url\n        FROM \n            acc_{account_id}.urls as urls,\n            (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) \n            AS unrolled_urls\n        WHERE\n            urls.id = unrolled_urls.rec_url_id AND\n            urls.url  ILIKE  '%enterprise_customer.com\/jobs%'\n    );\n<\/code><\/pre>\n<p><\/p>\n<p>Un m\u0113s esam beigu laiks izpildes <code>Laiks: 1898.717 ms<\/code> Laiks svin\u0113t?!?<\/p>\n<p><\/p>\n<p>Ne tik \u0101tri! Vispirms j\u0101noskaidro pareiz\u012bba. Es biju \u013coti skeptisks attiec\u012bb\u0101 uz <code>EXISTS<\/code> optimiz\u0101ciju, jo t\u0101 maina lo\u0123iku uz agr\u0101ku pabeig\u0161anu. Mums j\u0101b\u016bt p\u0101rliecin\u0101tiem, ka m\u0113s nepievienoj\u0101m nezin\u0101mu k\u013c\u016bdu vaic\u0101jum\u0101.<\/p>\n<p><\/p>\n<p>Vienk\u0101r\u0161a p\u0101rbaude bija izpild\u012bt <code>count(*)<\/code> un l\u0113ni, un \u0101tri vaic\u0101jumi lielam da\u017e\u0101du datu kopu skaitam. P\u0113c tam, maza datu apak\u0161kopa, es manu\u0101li p\u0101rbaud\u012bju visus rezult\u0101tus.<\/p>\n<p><\/p>\n<p>Visas p\u0101rbaudes deva stabilus pozit\u012bvus rezult\u0101tus. M\u0113s visu salaboj\u0101m!<\/p>\n<p><\/p>\n<h2>Izvilkti m\u0101c\u012bbas<\/h2>\n<p><\/p>\n<p>No \u0161\u012bs v\u0113stures var g\u016bt daudz m\u0101c\u012bbu:<\/p>\n<p><\/p>\n<ol>\n<li>Vaic\u0101jumu pl\u0101ni nepasaka visu st\u0101stu, bet var dot pavedienus<\/li>\n<li>Galvenie aizdom\u0101s tur\u0113tie ne vienm\u0113r ir \u012bstie vain\u012bgie<\/li>\n<li>L\u0113nus vaic\u0101jumus var sadal\u012bt, lai izol\u0113tu \u0161aurus vietas<\/li>\n<li>Ne visas optimiz\u0101cijas p\u0113c b\u016bt\u012bbas ir redukt\u012bvas<\/li>\n<li>Izmanto\u0161ana <code>EXIST<\/code>, kur tas ir iesp\u0113jams, var rad\u012bt strauju veiktsp\u0113jas pieaugumu<\/li>\n<\/ol>\n<p><\/p>\n<h2>Izvade<\/h2>\n<p><\/p>\n<p>M\u0113s esam p\u0101rg\u0101ju\u0161i no vaic\u0101juma laika ~24 min\u016btes l\u012bdz 2 sekund\u0113m \u2014 \u013coti b\u016btisks veiktsp\u0113jas pieaugums! Lai ar\u012b \u0161is raksts ir bijis gar\u0161, visi eksperimenti, ko m\u0113s dar\u012bj\u0101m, notika vien\u0101 dien\u0101 un, p\u0113c apl\u0113s\u0113m, pras\u012bja no 1,5 l\u012bdz 2 stund\u0101m optimiz\u0101cij\u0101m un test\u0113\u0161anai.<\/p>\n<p><\/p>\n<p>SQL ir br\u012bni\u0161\u0137\u012bga valoda, ja nebaid\u0101ties no t\u0101s, bet m\u0113\u0123in\u0101t to saprast un izmantot. Ar labu izpratni par to, k\u0101 SQL vaic\u0101jumi tiek izpild\u012bti, k\u0101 DB \u0123ener\u0113 vaic\u0101jumu pl\u0101nus, k\u0101 darbojas indeksi un vienk\u0101r\u0161i datu lielumu, ar kuru tiekat gal\u0101, j\u016bs varat \u013coti veiksm\u012bgi att\u012bst\u012bt vaic\u0101jumu optimiz\u0101ciju. Nav mazsvar\u012bgi, tom\u0113r, turpin\u0101t izm\u0113\u0123in\u0101t da\u017e\u0101dus pieg\u0101jienus un l\u0113n\u0101m sadal\u012bt probl\u0113mu, atrodot \u0161aur\u0101s vietas.<\/p>\n<p><\/p>\n<p>Lab\u0101k\u0101 da\u013ca par \u0161o rezult\u0101tu sasnieg\u0161anu ir ac\u012bmredzamais redzams darba \u0101truma uzlabojums \u2014 kad zi\u0146ojums, kas iepriek\u0161 pat nen\u0101ca atv\u0113rt, tagad iel\u0101d\u0113jas gandr\u012bz uzreiz.<\/p>\n<p><\/p>\n<p><strong>\u012apa\u0161a pateic\u012bba\u00a0<\/strong>maniem kol\u0113\u0123iem\u00a0<em>komand\u0101 Aditya Mishra<\/em>,\u00a0<em>Aditya Gaur\u00a0<\/em>un\u00a0<em><noindex><a rel=\"nofollow\" href=\"https:\/\/twitter.com\/s0ftvar\">Varun Malhotra\u00a0<\/a><\/noindex><\/em>par smadze\u0146u v\u0113tru un\u00a0<em>Dinkar Pandir\u00a0<\/em>par to, ka atrada svar\u012bgu k\u013c\u016bdu m\u016bsu gala piepras\u012bjum\u0101, pirms m\u0113s ar to gal\u012bgi \u0161\u0137\u012br\u0101mies!<\/p>\n<p>Avots: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/455832\/\">habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u0412 \u0434\u0435\u043a\u0430\u0431\u0440\u0435 \u043f\u0440\u043e\u0448\u043b\u043e\u0433\u043e \u0433\u043e\u0434\u0430 \u044f \u043f\u043e\u043b\u0443\u0447\u0438\u043b \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043d\u044b\u0439 \u043e\u0442\u0447\u0435\u0442 \u043e\u0431 \u043e\u0448\u0438\u0431\u043a\u0435 \u043e\u0442 \u043a\u043e\u043c\u0430\u043d\u0434\u044b\u00a0\u043f\u043e\u0434\u0434\u0435\u0440\u0436\u043a\u0438 VWO. \u0412\u0440\u0435\u043c\u044f \u0437\u0430\u0433\u0440\u0443\u0437\u043a\u0438 \u043e\u0434\u043d\u043e\u0433\u043e \u0438\u0437 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u043e\u0442\u0447\u0435\u0442\u043e\u0432 \u0434\u043b\u044f \u043a\u0440\u0443\u043f\u043d\u043e\u0433\u043e \u043a\u043e\u0440\u043f\u043e\u0440\u0430\u0442\u0438\u0432\u043d\u043e\u0433\u043e \u043a\u043b\u0438\u0435\u043d\u0442\u0430 \u043a\u0430\u0437\u0430\u043b\u043e\u0441\u044c \u043d\u0435\u043f\u043e\u043c\u0435\u0440\u043d\u043e \u0431\u043e\u043b\u044c\u0448\u0438\u043c. \u0410 \u0442\u0430\u043a \u043a\u0430\u043a \u044d\u0442\u043e \u0441\u0444\u0435\u0440\u0430 \u043c\u043e\u0435\u0439 \u043e\u0442\u0432\u0435\u0442\u0441\u0442\u0432\u0435\u043d\u043d\u043e\u0441\u0442\u0438, \u044f \u0442\u0443\u0442 \u0436\u0435 \u0441\u043e\u0441\u0440\u0435\u0434\u043e\u0442\u043e\u0447\u0438\u043b\u0441\u044f \u043d\u0430 \u0440\u0435\u0448\u0435\u043d\u0438\u0438 \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u044b. \u041f\u0440\u0435\u0434\u044b\u0441\u0442\u043e\u0440\u0438\u044f \u0427\u0442\u043e\u0431\u044b \u0431\u044b\u043b\u043e \u043f\u043e\u043d\u044f\u0442\u043d\u043e \u043e \u0447\u0451\u043c \u0440\u0435\u0447\u044c, \u044f \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443 \u0441\u043e\u0432\u0441\u0435\u043c \u043d\u0435\u043c\u043d\u043e\u0433\u043e \u043e VWO. \u042d\u0442\u043e \u043f\u043b\u0430\u0442\u0444\u043e\u0440\u043c\u0430, [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-35259","post","type-post","status-publish","format-standard","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.5 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u0412 \u0434\u0435\u043a\u0430\u0431\u0440\u0435 \u043f\u0440\u043e\u0448\u043b\u043e\u0433\u043e \u0433\u043e\u0434\u0430 \u044f \u043f\u043e\u043b\u0443\u0447\u0438\u043b \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043d\u044b\u0439 \u043e\u0442\u0447\u0435\u0442 \u043e\u0431 \u043e\u0448\u0438\u0431\u043a\u0435 \u043e\u0442 \u043a\u043e\u043c\u0430\u043d\u0434\u044b \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u043a\u0438 VWO. \u0412\u0440\u0435\u043c\u044f \u0437\u0430\u0433\u0440\u0443\u0437\u043a\u0438 \u043e\u0434\u043d\u043e\u0433\u043e \u0438\u0437 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u043e\u0442\u0447\u0435\u0442\u043e\u0432 \u0434\u043b\u044f \u043a\u0440\u0443\u043f\u043d\u043e\u0433\u043e \u043a\u043e\u0440\u043f\u043e\u0440\u0430\u0442\u0438\u0432\u043d\u043e\u0433\u043e \u043a\u043b\u0438\u0435\u043d\u0442\u0430 \u043a\u0430\u0437\u0430\u043b\u043e\u0441\u044c \u043d\u0435\u043f\u043e\u043c\u0435\u0440\u043d\u043e \u0431\u043e\u043b\u044c\u0448\u0438\u043c.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/lv\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.5\" \/>\n\t\t<meta property=\"og:locale\" content=\"lv_LV\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u0418\u0441\u0442\u043e\u0440\u0438\u044f \u043e\u0434\u043d\u043e\u0433\u043e SQL \u0440\u0430\u0441\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u0438\u044f | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u0412 \u0434\u0435\u043a\u0430\u0431\u0440\u0435 \u043f\u0440\u043e\u0448\u043b\u043e\u0433\u043e \u0433\u043e\u0434\u0430 \u044f \u043f\u043e\u043b\u0443\u0447\u0438\u043b \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043d\u044b\u0439 \u043e\u0442\u0447\u0435\u0442 \u043e\u0431 \u043e\u0448\u0438\u0431\u043a\u0435 \u043e\u0442 \u043a\u043e\u043c\u0430\u043d\u0434\u044b \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u043a\u0438 VWO. \u0412\u0440\u0435\u043c\u044f \u0437\u0430\u0433\u0440\u0443\u0437\u043a\u0438 \u043e\u0434\u043d\u043e\u0433\u043e \u0438\u0437 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u043e\u0442\u0447\u0435\u0442\u043e\u0432 \u0434\u043b\u044f \u043a\u0440\u0443\u043f\u043d\u043e\u0433\u043e \u043a\u043e\u0440\u043f\u043e\u0440\u0430\u0442\u0438\u0432\u043d\u043e\u0433\u043e \u043a\u043b\u0438\u0435\u043d\u0442\u0430 \u043a\u0430\u0437\u0430\u043b\u043e\u0441\u044c \u043d\u0435\u043f\u043e\u043c\u0435\u0440\u043d\u043e \u0431\u043e\u043b\u044c\u0448\u0438\u043c.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/lv\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2019-10-31T19:03:17+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2019-10-31T19:03:17+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47Viena SQL izmekl\u0113\u0161anas st\u0101sts | ProHoster","description":"Pag\u0101ju\u0161\u0101 gada decembr\u012b sa\u0146\u0113mu interesantu k\u013c\u016bdas zi\u0146ojumu no VWO atbalsta komandas. Liel\u0101 korporat\u012bv\u0101 klienta anal\u012btisk\u0101 zi\u0146ojuma iel\u0101des laiks \u0161\u0137ita p\u0101rm\u0113r\u012bgi gar\u0161.","canonical_url":"https:\/\/prohoster.info\/lv\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"lv_LV","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0418\u0441\u0442\u043e\u0440\u0438\u044f \u043e\u0434\u043d\u043e\u0433\u043e SQL \u0440\u0430\u0441\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u043d\u0438\u044f | ProHoster","og:description":"\u0412 \u0434\u0435\u043a\u0430\u0431\u0440\u0435 \u043f\u0440\u043e\u0448\u043b\u043e\u0433\u043e \u0433\u043e\u0434\u0430 \u044f \u043f\u043e\u043b\u0443\u0447\u0438\u043b \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043d\u044b\u0439 \u043e\u0442\u0447\u0435\u0442 \u043e\u0431 \u043e\u0448\u0438\u0431\u043a\u0435 \u043e\u0442 \u043a\u043e\u043c\u0430\u043d\u0434\u044b \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u043a\u0438 VWO. \u0412\u0440\u0435\u043c\u044f \u0437\u0430\u0433\u0440\u0443\u0437\u043a\u0438 \u043e\u0434\u043d\u043e\u0433\u043e \u0438\u0437 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u043e\u0442\u0447\u0435\u0442\u043e\u0432 \u0434\u043b\u044f \u043a\u0440\u0443\u043f\u043d\u043e\u0433\u043e \u043a\u043e\u0440\u043f\u043e\u0440\u0430\u0442\u0438\u0432\u043d\u043e\u0433\u043e \u043a\u043b\u0438\u0435\u043d\u0442\u0430 \u043a\u0430\u0437\u0430\u043b\u043e\u0441\u044c \u043d\u0435\u043f\u043e\u043c\u0435\u0440\u043d\u043e \u0431\u043e\u043b\u044c\u0448\u0438\u043c.","og:url":"https:\/\/prohoster.info\/lv\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T19:03:17+00:00","article:modified_time":"2019-10-31T19:03:17+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"35259","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-21 22:33:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 02:07:28","updated":"2026-01-21 22:33:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/posts\/35259","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/comments?post=35259"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/posts\/35259\/revisions"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/media?parent=35259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/categories?post=35259"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/tags?post=35259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}