{"id":36704,"date":"2019-10-31T22:13:15","date_gmt":"2019-10-31T19:13:15","guid":{"rendered":"https:\/\/prohoster.info\/blog\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej\/"},"modified":"2019-10-31T22:13:15","modified_gmt":"2019-10-31T19:13:15","slug":"optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","status":"publish","type":"post","link":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","title":{"rendered":"Optimalizace dotaz\u016f datab\u00e1ze na p\u0159\u00edkladu B2B slu\u017eby pro stavebn\u00edky","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Jak m\u016f\u017eeme zdesetin\u00e1sobit po\u010det dotaz\u016f na datab\u00e1zi, ani\u017e bychom p\u0159e\u0161li na v\u00fdkonn\u011bj\u0161\u00ed server, a p\u0159itom udr\u017eet syst\u00e9m funk\u010dn\u00ed? Uk\u00e1\u017eu v\u00e1m, jak jsme bojovali s poklesem v\u00fdkonu na\u0161\u00ed datab\u00e1ze, jak jsme optimalizovali SQL dotazy, abychom obslou\u017eili co nejv\u00edce u\u017eivatel\u016f, ani\u017e bychom zvy\u0161ovali n\u00e1klady na v\u00fdpo\u010detn\u00ed zdroje.<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><br \/>\nVytv\u00e1\u0159\u00edm slu\u017ebu pro \u0159\u00edzen\u00ed obchodn\u00edch proces\u016f ve stavebn\u00edch spole\u010dnostech. Spolupracuje s n\u00e1mi p\u0159ibli\u017en\u011b 3 tis\u00edce firem. V\u00edce ne\u017e 10 tis\u00edc lid\u00ed ka\u017ed\u00fd den pracuje s na\u0161\u00edm syst\u00e9mem 4-10 hodin. \u0158e\u0161\u00ed r\u016fzn\u00e9 \u00fakoly pl\u00e1nov\u00e1n\u00ed, varov\u00e1n\u00ed, upozorn\u011bn\u00ed, validace\u2026 Pou\u017e\u00edv\u00e1me PostgreSQL 9.6. V datab\u00e1zi m\u00e1me p\u0159ibli\u017en\u011b 300 tabulek a denn\u011b do n\u00ed p\u0159ijde a\u017e 200 milion\u016f dotaz\u016f (10 tis\u00edc r\u016fzn\u00fdch). V pr\u016fm\u011bru m\u00e1me 3-4 tis\u00edce dotaz\u016f za sekundu, v nejru\u0161n\u011bj\u0161\u00edch chv\u00edl\u00edch v\u00edce ne\u017e 10 tis\u00edc dotaz\u016f za sekundu. V\u011bt\u0161ina dotaz\u016f je OLAP. P\u0159id\u00e1n\u00ed, zm\u011bny a maz\u00e1n\u00ed jsou v\u00fdrazn\u011b m\u00e9n\u011b \u010dast\u00e9, tedy OLTP z\u00e1t\u011b\u017e je relativn\u011b mal\u00e1. Tato \u010d\u00edsla uv\u00e1d\u00edm, aby jste mohli posoudit rozsah na\u0161eho projektu a pochopit, jak m\u016f\u017ee b\u00fdt na\u0161e zku\u0161enost pro v\u00e1s u\u017eite\u010dn\u00e1.<\/p>\n<h3>Obr\u00e1zek prvn\u00ed. Lyrick\u00fd<\/h3>\n<p>\nKdy\u017e jsme za\u010dali vyv\u00edjet, moc jsme nep\u0159em\u00fd\u0161leli o tom, jak\u00e1 z\u00e1t\u011b\u017e dopadne na datab\u00e1zi a co ud\u011bl\u00e1me, pokud server p\u0159estane fungovat. P\u0159i navrhov\u00e1n\u00ed datab\u00e1ze jsme se \u0159\u00eddili obecn\u00fdmi doporu\u010den\u00edmi a sna\u017eili jsme se st\u0159\u00edlet si do nohy, ale d\u00e1l ne\u017e k obecn\u00fdm rad\u00e1m jako \"nepou\u017e\u00edvejte vzor <noindex><a rel=\"nofollow\" href=\"https:\/\/en.wikipedia.org\/wiki\/Entity%E2%80%93attribute%E2%80%93value_model\">Entity Attribute Values<\/a><\/noindex> jsme se nedostali. Navrhovali jsme podle princip\u016f normalizace, abychom se vyvarovali nadbyte\u010dnosti dat, a nezab\u00fdvali jsme se urychlov\u00e1n\u00edm r\u016fzn\u00fdch dotaz\u016f. Jakmile p\u0159i\u0161li prvn\u00ed u\u017eivatel\u00e9, \u010delili jsme probl\u00e9m\u016fm s v\u00fdkonem. Jak to obvykle b\u00fdv\u00e1, nebyli jsme na to v\u016fbec p\u0159ipraveni. Prvn\u00ed probl\u00e9my byly jednoduch\u00e9. V\u011bt\u0161inou se to \u0159e\u0161ilo p\u0159id\u00e1n\u00edm nov\u00e9ho indexu. Ale nastal okam\u017eik, kdy jednoduch\u00e9 opravy p\u0159estaly fungovat. Uv\u011bdomili jsme si, \u017ee n\u00e1m chyb\u00ed zku\u0161enosti a \u017ee je pro n\u00e1s \u010d\u00edm d\u00e1l t\u00edm obt\u00ed\u017en\u011bj\u0161\u00ed pochopit, co zp\u016fsobuje probl\u00e9my, najali jsme specialisty, kte\u0159\u00ed n\u00e1m pomohli spr\u00e1vn\u011b nastavit server, p\u0159ipojit monitorov\u00e1n\u00ed a uk\u00e1zali, kam se d\u00edvat, abychom z\u00edskali <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/9.6\/pgstatstatements.html\">statistiky<\/a><\/noindex>.<\/p>\n<h3>Obr\u00e1zek druh\u00fd. Statistick\u00fd<\/h3>\n<p>\nM\u00e1me tedy asi 10 000 r\u016fzn\u00fdch dotaz\u016f, kter\u00e9 se denn\u011b prov\u00e1d\u011bj\u00ed na na\u0161\u00ed datab\u00e1zi. Z t\u011bchto 10 000 jsou tu monstra, kter\u00e1 se prov\u00e1d\u011bj\u00ed 2-3 milionykr\u00e1t s pr\u016fm\u011brnou dobou prov\u00e1d\u011bn\u00ed 0,1-0,3 ms, a pak jsou tu dotazy s pr\u016fm\u011brnou dobou prov\u00e1d\u011bn\u00ed 30 sekund, kter\u00e9 se vyvol\u00e1vaj\u00ed 100kr\u00e1t denn\u011b.<\/p>\n<p>Optimalizovat v\u0161ech 10 000 dotaz\u016f nebylo mo\u017en\u00e9, a tak jsme se rozhodli zjistit, jak\u00fdm sm\u011brem sm\u011brovat \u00fasil\u00ed, abychom spr\u00e1vn\u011b zlep\u0161ili v\u00fdkon datab\u00e1ze. Po n\u011bkolika iterac\u00edch jsme za\u010dali dotazy rozd\u011blovat do typ\u016f.<\/p>\n<h4>TOP dotazy<\/h4>\n<p>\nTo jsou nejzat\u011b\u017euj\u00edc\u00ed dotazy, kter\u00e9 zab\u00edraj\u00ed nejv\u00edce \u010dasu (celkov\u00fd \u010das). Jsou to dotazy, kter\u00e9 se bu\u010f velmi \u010dasto vyvol\u00e1vaj\u00ed, nebo dotazy, kter\u00e9 se prov\u00e1d\u011bj\u00ed velmi dlouho (dlouh\u00e9 a \u010dast\u00e9 dotazy byly optimalizov\u00e1ny ji\u017e v prvn\u00edch iterac\u00edch boje za rychlost). Celkov\u011b server str\u00e1v\u00ed na jejich vykon\u00e1n\u00ed nejv\u00edce \u010dasu. Je tak\u00e9 d\u016fle\u017eit\u00e9 odd\u011blit top dotazy podle celkov\u00e9ho \u010dasu prov\u00e1d\u011bn\u00ed a samostatn\u011b podle IO \u010dasu. Zp\u016fsoby optimalizace t\u011bchto dotaz\u016f se pon\u011bkud li\u0161\u00ed.<\/p>\n<p>B\u011b\u017enou prax\u00ed v\u0161ech spole\u010dnost\u00ed je pracovat s TOP dotazy. Je jich m\u00e1lo, optimalizace i jedin\u00e9ho dotazu m\u016f\u017ee uvolnit 5-10 % zdroj\u016f. Jak v\u0161ak projekt \u201edosp\u00edv\u00e1\u201c, optimalizace TOP dotaz\u016f se st\u00e1v\u00e1 st\u00e1le obt\u00ed\u017en\u011bj\u0161\u00edm \u00fakolem. V\u0161echny jednoduch\u00e9 metody u\u017e byly vyzkou\u0161eny a nejv\u00edce \u201ezat\u011b\u017euj\u00edc\u00ed\u201c dotaz zab\u00edr\u00e1 \u201epouze\u201c 3-5 % zdroj\u016f. Pokud TOP dotazy celkem zab\u00edraj\u00ed m\u00e9n\u011b ne\u017e 30-40 % \u010dasu, pravd\u011bpodobn\u011b jste ji\u017e vynalo\u017eili \u00fasil\u00ed, abychom pracovaly rychle a nastal \u010das p\u0159ej\u00edt k optimalizaci dotaz\u016f z dal\u0161\u00ed skupiny.<br \/>\nZ\u016fst\u00e1v\u00e1 odpov\u011bd\u011bt na ot\u00e1zku, kolik vrcholn\u00fdch dotaz\u016f za\u0159adit do t\u00e9to skupiny. Obvykle beru nejm\u00e9n\u011b 10, ale ne v\u00edce ne\u017e 20. Sna\u017e\u00edm se, aby se doba prvn\u00edho a posledn\u00edho v TOP skupin\u011b li\u0161ila maxim\u00e1ln\u011b 10kr\u00e1t. Tedy pokud doba prov\u00e1d\u011bn\u00ed dotaz\u016f n\u00e1hle klesne z 1. m\u00edsta na 10., vezmu TOP-10, pokud kles\u00e1n\u00ed je plynulej\u0161\u00ed, zv\u011bt\u0161uji velikost skupiny na 15 nebo 20.<br \/>\n<img decoding=\"async\" alt=\"Optimalizace dotaz\u016f datab\u00e1ze na p\u0159\u00edkladu B2B slu\u017eby pro stavebn\u00edky\" src=\"\/wp-content\/uploads\/2019\/08\/2a9d9e6053d1aebb71aa213757bd2393.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<\/p>\n<h4>St\u0159edn\u00ed dotazy (medium)<\/h4>\n<p>\nTo jsou v\u0161echny dotazy, kter\u00e9 p\u0159ich\u00e1zej\u00ed hned za TOP, s v\u00fdjimkou posledn\u00edch 5-10 %. Obvykle se pr\u00e1v\u011b v optimalizaci t\u011bchto dotaz\u016f skr\u00fdv\u00e1 mo\u017enost v\u00fdrazn\u011b zv\u00fd\u0161it v\u00fdkon serveru. Tyto dotazy mohou tvo\u0159it a\u017e 80 %. Ale i kdy\u017e jejich pod\u00edl p\u0159ekro\u010dil 50 %, znamen\u00e1 to, \u017ee je na n\u011b t\u0159eba se pod\u00edvat podrobn\u011bji.<\/p>\n<h4>Ocas (tail)<\/h4>\n<p>\nJak bylo \u0159e\u010deno, tyto dotazy p\u0159ich\u00e1zej\u00ed na konci a zaberou 5-10% \u010dasu. Na n\u011b lze zapomenout, pokud nepou\u017e\u00edv\u00e1te automatick\u00e9 n\u00e1stroje pro anal\u00fdzu dotaz\u016f, pak jejich optimalizace m\u016f\u017ee b\u00fdt tak\u00e9 levn\u00e1.<\/p>\n<p>Jak ohodnotit ka\u017edou skupinu?<\/p>\n<p>Pou\u017e\u00edv\u00e1m SQL dotaz, kter\u00fd pom\u00e1h\u00e1 prov\u00e9st takov\u00e9 hodnocen\u00ed pro PostgreSQL (jsem si jist\u00fd, \u017ee pro mnoho dal\u0161\u00edch DBMS lze napsat podobn\u00fd dotaz).<\/p>\n<p><b class=\"spoiler_title\">SQL dotaz pro hodnocen\u00ed velikosti skupin TOP-MEDIUM-TAIL<\/b><\/p>\n<pre><code class=\"sql\">SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) FROM ( SELECT CASE WHEN rn  20 AND rn  800 THEN tt_percent ELSE 0 END AS time_tail FROM ( SELECT total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query, ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn FROM pg_stat_statements ORDER BY total_time DESC ) AS t ) AS ts\n<\/code><\/pre>\n<p>V\u00fdsledkem dotazu jsou t\u0159i sloupce, z nich\u017e ka\u017ed\u00fd obsahuje procento \u010dasu, kter\u00fd je v\u011bnov\u00e1n zpracov\u00e1n\u00ed dotaz\u016f z t\u00e9to skupiny. Uvnit\u0159 dotazu jsou dv\u011b \u010d\u00edsla (v m\u00e9m p\u0159\u00edpad\u011b 20 a 800), kter\u00e1 odd\u011bluj\u00ed dotazy jedn\u00e9 skupiny od druh\u00e9.<\/p>\n<p>Takto se p\u0159ibli\u017en\u011b vztahuj\u00ed pod\u00edly dotaz\u016f na za\u010d\u00e1tku optimalizace a nyn\u00ed.<\/p>\n<p><img decoding=\"async\" alt=\"Optimalizace dotaz\u016f datab\u00e1ze na p\u0159\u00edkladu B2B slu\u017eby pro stavebn\u00edky\" src=\"\/wp-content\/uploads\/2019\/08\/9c70ad9aba8b835c94729c9dda656eab.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nZ diagramu je vid\u011bt, \u017ee pod\u00edl TOP dotaz\u016f v\u00fdrazn\u011b klesl, zato pod\u00edl \u201est\u0159edn\u00edch\u201c dotaz\u016f vzrostl.<br \/>\nZpo\u010d\u00e1tku do TOP dotaz\u016f spadaly o\u010dividn\u00e9 chyby. S\u010dasem zmizely d\u011btsk\u00e9 nemoci, pod\u00edl TOP dotaz\u016f se zmen\u0161oval a bylo pot\u0159eba vynalo\u017eit v\u00edce \u00fasil\u00ed, aby se t\u011b\u017ek\u00e9 dotazy zrychlily. <\/p>\n<p><b class=\"spoiler_title\">Abychom z\u00edskali text dotaz\u016f, pou\u017eijeme takov\u00fd dotaz<\/b><\/p>\n<pre><code class=\"sql\">SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query FROM pg_stat_statements ORDER BY total_time DESC ) AS T WHERE rn  20 AND rn  800  -- TAIL\n<\/code><\/pre>\n<p>Zde je seznam nej\u010dast\u011bji pou\u017e\u00edvan\u00fdch technik, kter\u00e9 n\u00e1m pom\u00e1haly zrychlovat TOP dotazy:<\/p>\n<ul>\n<li>Redesign syst\u00e9mu, nap\u0159\u00edklad p\u0159epracov\u00e1n\u00ed logiky ozn\u00e1men\u00ed na message broker m\u00edsto periodick\u00fdch dotaz\u016f do DB.<\/li>\n<li>P\u0159id\u00e1n\u00ed nebo zm\u011bna index\u016f.<\/li>\n<li>P\u0159eps\u00e1n\u00ed ORM dotaz\u016f na \u010dist\u00fd SQL.<\/li>\n<li>P\u0159eps\u00e1n\u00ed logiky lazy na\u010d\u00edt\u00e1n\u00ed dat.<\/li>\n<li>Ke\u0161ov\u00e1n\u00ed prost\u0159ednictv\u00edm denormalizace dat. Nap\u0159\u00edklad m\u00e1me vazbu tabulek Doprava -&gt; Faktura -&gt; Dotaz -&gt; \u017d\u00e1dost. To znamen\u00e1, \u017ee ka\u017ed\u00e1 doprava je spojena se \u017e\u00e1dost\u00ed prost\u0159ednictv\u00edm dal\u0161\u00edch tabulek. Abychom v ka\u017ed\u00e9m dotazu nespojovali v\u0161echny tabulky, jsme duplikovali odkaz na \u017e\u00e1dost v tabulce Doprava.<\/li>\n<li>Caching static tables with directories and rarely changing tables in the program's memory.<\/li>\n<\/ul>\n<p>\nSometimes changes led to a significant redesign, but they provided a 5-10% system load reduction and were justified. Over time, the output became smaller, and more serious redesigns were required.<\/p>\n<p>Then we turned our attention to the second group of queries - the mid-ranking group. It had many more queries, and it seemed that analyzing the entire group would take a lot of time. However, most queries turned out to be very simple to optimize, and many problems repeated dozens of times in various variations. Here are examples of some typical optimizations we applied to dozens of similar queries, with each group of optimized queries reducing the database load by 3-5%.<\/p>\n<ul>\n<li> Instead of checking for record existence with COUNT and full table scans, we started using EXISTS.\n <\/li>\n<li>We got rid of DISTINCT (there's no universal recipe, but sometimes it can be easily eliminated, speeding up the query by 10-100 times).\n<p>For example, instead of a query to select all drivers from a large delivery table (DELIVERY), <\/p>\n<pre><code class=\"sql\">SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME\nFROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID\n<\/code><\/pre>\n<p>\nwe changed the query to one against a relatively small PERSON table.<\/p>\n<pre><code class=\"sql\">SELECT P.ID, P.FIRST_NAME, P.LAST_NAME\nFROM PERSON\nWHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)\n<\/code><\/pre>\n<p>\nIt seemed we used a correlated subquery, but it speeds up by more than 10 times.\n <\/li>\n<li>In many cases, we completely abandoned COUNT and <br \/>\n<noindex><a rel=\"nofollow\" href=\"https:\/\/www.citusdata.com\/blog\/2016\/10\/12\/count-performance\/#dup_counts_estimated_filtered\">replaced it with the calculation of an approximate value.<\/a><\/noindex>\n <\/li>\n<li>m\u00edsto\n<pre><code class=\"sql\">UPPER(s) LIKE 'JOHN%' \n<\/code><\/pre>\n<p>\nVPS KVM <\/p>\n<pre><code class=\"sql\">s ILIKE 'John%'\n<\/code><\/pre>\n<p>\n <\/li>\n<\/ul>\n<p>\nEach specific query could be sped up by 3-1000 times. Despite the impressive results, at first we felt there was no point in optimizing a query that runs in 10 ms, is in the third hundred of the heaviest queries, and generally takes a fraction of a percent of the overall database load time. But by applying the same recipe to a group of similar queries, we reclaimed a few percent. To avoid spending time manually reviewing all hundreds of queries, we wrote a few simple scripts that used regular expressions to find similar queries. As a result, the automated search for groups of queries allowed us to further improve our performance with modest efforts.<\/p>\n<p>U\u017e t\u0159i roky pracujeme na stejn\u00e9m vybaven\u00ed. Pr\u016fm\u011brn\u00e1 denn\u00ed z\u00e1t\u011b\u017e je kolem 30 %, v \u0161pi\u010dk\u00e1ch dosahuje a\u017e 70 %. Po\u010det dotaz\u016f i u\u017eivatel\u016f vzrostl p\u0159ibli\u017en\u011b desetin\u00e1sobn\u011b. A to v\u0161e d\u00edky st\u00e1l\u00e9mu monitorov\u00e1n\u00ed p\u0159\u00edslu\u0161n\u00fdch skupin dotaz\u016f TOP-MEDIUM. Jakmile se n\u011bjak\u00fd nov\u00fd dotaz objev\u00ed ve skupin\u011b TOP, okam\u017eit\u011b ho analyzujeme a sna\u017e\u00edme se jej zrychlit. Skupinu MEDIUM kontrolujeme jednou t\u00fddn\u011b pomoc\u00ed skript\u016f pro anal\u00fdzu dotaz\u016f. Pokud se objev\u00ed nov\u00e9 dotazy, kter\u00e9 ji\u017e um\u00edme optimalizovat, rychle je m\u011bn\u00edme. N\u011bkdy nach\u00e1z\u00edme nov\u00e9 zp\u016fsoby optimalizace, kter\u00e9 lze aplikovat ihned na n\u011bkolik dotaz\u016f. <\/p>\n<p>Podle na\u0161ich p\u0159edpov\u011bd\u00ed sou\u010dasn\u00fd server zvl\u00e1dne zv\u00fd\u0161en\u00ed po\u010dtu u\u017eivatel\u016f je\u0161t\u011b 3-5kr\u00e1t. Mimochodem m\u00e1me je\u0161t\u011b jeden trumf v ruk\u00e1vu - dosud jsme nep\u0159evedli SELECT dotazy na zrcadlo, jak se doporu\u010duje. Ale to d\u011bl\u00e1me v\u011bdom\u011b, proto\u017ee nejd\u0159\u00edve chceme pln\u011b vyu\u017e\u00edt mo\u017enosti \u201einteligentn\u00ed\u201c optimalizace, ne\u017e zapneme \u201et\u011b\u017ekou artilerii\u201c.<br \/>\nKritick\u00fd pohled na odvedenou pr\u00e1ci m\u016f\u017ee nazna\u010dit, \u017ee je mo\u017en\u00e9 pou\u017e\u00edt vertik\u00e1ln\u00ed \u0161k\u00e1lov\u00e1n\u00ed. Koupit v\u00fdkonn\u011bj\u0161\u00ed server, m\u00edsto abychom ztr\u00e1celi \u010das specialist\u016f. Server nemus\u00ed b\u00fdt tak drah\u00fd, zvl\u00e1\u0161\u0165 kdy\u017e limity vertik\u00e1ln\u00edho \u0161k\u00e1lov\u00e1n\u00ed je\u0161t\u011b nebyly vy\u010derp\u00e1ny. Nicm\u00e9n\u011b, po\u010det dotaz\u016f vzrostl pouze desetin\u00e1sobn\u011b. B\u011bhem n\u011bkolika let se zv\u00fd\u0161ila funk\u010dnost syst\u00e9mu a nyn\u00ed je v\u00edce druh\u016f dotaz\u016f. Funk\u010dnost, kter\u00e1 byla, d\u00edky ke\u0161ov\u00e1n\u00ed, se nyn\u00ed prov\u00e1d\u00ed men\u0161\u00edm po\u010dtem dotaz\u016f, nav\u00edc efektivn\u011bj\u0161\u00edch. To znamen\u00e1, \u017ee m\u016f\u017eeme sm\u011ble vyn\u00e1sobit je\u0161t\u011b 5, abychom z\u00edskali skute\u010dn\u00fd koeficient zrychlen\u00ed. Tak\u017ee podle nejskromn\u011bj\u0161\u00edch v\u00fdpo\u010dt\u016f m\u016f\u017eeme \u0159\u00edci, \u017ee zrychlen\u00ed \u010dinilo 50 a v\u00edcekr\u00e1t. Vertik\u00e1ln\u011b zv\u00fd\u0161it kapacitu serveru 50kr\u00e1t by bylo dra\u017e\u0161\u00ed. Obzvl\u00e1\u0161\u0165 kdy\u017e jednou proveden\u00e1 optimalizace funguje st\u00e1le, zat\u00edmco \u00fa\u010det za pronajat\u00fd server chod\u00ed ka\u017ed\u00fd m\u011bs\u00edc.<br \/>\n<br \/>Zdroj: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/461071\/\">habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041a\u0430\u043a \u0432\u044b\u0440\u0430\u0441\u0442\u0438 \u0432 10 \u0440\u0430\u0437 \u043f\u043e\u0434 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0443 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u043a \u0411\u0414 \u043d\u0435 \u043f\u0435\u0440\u0435\u0435\u0437\u0436\u0430\u044f \u043d\u0430 \u0431\u043e\u043b\u0435\u0435 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0439 \u0441\u0435\u0440\u0432\u0435\u0440 \u0438 \u0441\u043e\u0445\u0440\u0430\u043d\u0438\u0442\u044c \u0440\u0430\u0431\u043e\u0442\u043e\u0441\u043f\u043e\u0441\u043e\u0431\u043d\u043e\u0441\u0442\u044c \u0441\u0438\u0441\u0442\u0435\u043c\u044b? \u042f \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443, \u043a\u0430\u043a \u043c\u044b \u0431\u043e\u0440\u043e\u043b\u0438\u0441\u044c \u0441 \u043f\u0430\u0434\u0435\u043d\u0438\u0435\u043c \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043d\u0430\u0448\u0435\u0439 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445, \u043a\u0430\u043a \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u043b\u0438 SQL \u0437\u0430\u043f\u0440\u043e\u0441\u044b, \u0447\u0442\u043e\u0431\u044b \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u0442\u044c \u043a\u0430\u043a \u043c\u043e\u0436\u043d\u043e \u0431\u043e\u043b\u044c\u0448\u0435 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0435\u0439 \u0438 \u043d\u0435 \u043f\u043e\u0432\u044b\u0448\u0430\u0442\u044c \u0440\u0430\u0441\u0445\u043e\u0434\u044b \u043d\u0430 \u0432\u044b\u0447\u0438\u0441\u043b\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0435 \u0440\u0435\u0441\u0443\u0440\u0441\u044b. \u042f \u0434\u0435\u043b\u0430\u044e \u0441\u0435\u0440\u0432\u0438\u0441 \u0434\u043b\u044f \u0443\u043f\u0440\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u0431\u0438\u0437\u043d\u0435\u0441 \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0430\u043c\u0438 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":27493,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-36704","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.3 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041a\u0430\u043a \u0432\u044b\u0440\u0430\u0441\u0442\u0438 \u0432 10 \u0440\u0430\u0437 \u043f\u043e\u0434 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0443 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u043a \u0411\u0414 \u043d\u0435 \u043f\u0435\u0440\u0435\u0435\u0437\u0436\u0430\u044f \u043d\u0430 \u0431\u043e\u043b\u0435\u0435 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0439 \u0441\u0435\u0440\u0432\u0435\u0440 \u0438 \u0441\u043e\u0445\u0440\u0430\u043d\u0438\u0442\u044c \u0440\u0430\u0431\u043e\u0442\u043e\u0441\u043f\u043e\u0441\u043e\u0431\u043d\u043e\u0441\u0442\u044c \u0441\u0438\u0441\u0442\u0435\u043c\u044b?\" \/>\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\/cs\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.3\" \/>\n\t\t<meta property=\"og:locale\" content=\"cs_CZ\" \/>\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\u041e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u044f \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u043d\u0430 \u043f\u0440\u0438\u043c\u0435\u0440\u0435 B2B \u0441\u0435\u0440\u0432\u0438\u0441\u0430 \u0434\u043b\u044f \u0441\u0442\u0440\u043e\u0438\u0442\u0435\u043b\u0435\u0439 | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041a\u0430\u043a \u0432\u044b\u0440\u0430\u0441\u0442\u0438 \u0432 10 \u0440\u0430\u0437 \u043f\u043e\u0434 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0443 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u043a \u0411\u0414 \u043d\u0435 \u043f\u0435\u0440\u0435\u0435\u0437\u0436\u0430\u044f \u043d\u0430 \u0431\u043e\u043b\u0435\u0435 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0439 \u0441\u0435\u0440\u0432\u0435\u0440 \u0438 \u0441\u043e\u0445\u0440\u0430\u043d\u0438\u0442\u044c \u0440\u0430\u0431\u043e\u0442\u043e\u0441\u043f\u043e\u0441\u043e\u0431\u043d\u043e\u0441\u0442\u044c \u0441\u0438\u0441\u0442\u0435\u043c\u044b?\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej\" \/>\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:13:15+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2019-10-31T19:13:15+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\udd47Optimalizace dotaz\u016f datab\u00e1ze na p\u0159\u00edkladu B2B slu\u017eby pro stavebn\u00edky | ProHoster","description":"Jak zv\u00fd\u0161it po\u010det dotaz\u016f k datab\u00e1zi 10kr\u00e1t, ani\u017e bychom p\u0159e\u0161li na v\u00fdkonn\u011bj\u0161\u00ed server a zachovali funk\u010dnost syst\u00e9mu?","canonical_url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"cs_CZ","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\u041e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u044f \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u043d\u0430 \u043f\u0440\u0438\u043c\u0435\u0440\u0435 B2B \u0441\u0435\u0440\u0432\u0438\u0441\u0430 \u0434\u043b\u044f \u0441\u0442\u0440\u043e\u0438\u0442\u0435\u043b\u0435\u0439 | ProHoster","og:description":"\u041a\u0430\u043a \u0432\u044b\u0440\u0430\u0441\u0442\u0438 \u0432 10 \u0440\u0430\u0437 \u043f\u043e\u0434 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0443 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u043a \u0411\u0414 \u043d\u0435 \u043f\u0435\u0440\u0435\u0435\u0437\u0436\u0430\u044f \u043d\u0430 \u0431\u043e\u043b\u0435\u0435 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0439 \u0441\u0435\u0440\u0432\u0435\u0440 \u0438 \u0441\u043e\u0445\u0440\u0430\u043d\u0438\u0442\u044c \u0440\u0430\u0431\u043e\u0442\u043e\u0441\u043f\u043e\u0441\u043e\u0431\u043d\u043e\u0441\u0442\u044c \u0441\u0438\u0441\u0442\u0435\u043c\u044b?","og:url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","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:13:15+00:00","article:modified_time":"2019-10-31T19:13:15+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"36704","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-22 04:30:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 01:40:22","updated":"2026-01-22 04:30:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/36704","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=36704"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/36704\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/27493"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=36704"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=36704"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=36704"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}