Vytvoření tabulky Sqlite3. modul sqlite - Práce s databází. Úprava a mazání záznamů

Jak optimalizovat dotazy MySQL?

U běžného, ​​nijak zvlášť navštěvovaného webu není velký rozdíl, zda jsou dotazy MySQL do databáze optimalizovány nebo ne. Ale pro produkční servery pod těžký náklad rozdíl mezi správným a nesprávným SQL je obrovský a za běhu mohou výrazně ovlivnit chování a spolehlivost služeb. V tomto článku se podívám na to, jak psát rychlé dotazy a na faktory, které je zpomalují.

Proč MySQL?

Dnes se hodně mluví o Dig Data a dalších nových technologiích. NoSQL a cloudová řešení jsou skvělá, ale spousta populárního softwaru (jako WordPress, phpBB, Drupal) stále běží na MySQL. Migrace do nejnovější řešení může vést nejen ke změně konfigurace na serverech. Kromě, Efektivita MySQL stále na úrovni, zejména verze Percona.

Nedělejte běžnou chybu, že při řešení problému pomalých dotazů házíte stále více a více hardwaru vysoké zatížení servery - je lepší jít ke kořenům problémů. Určitým typem optimalizace je také zvýšení výkonu procesorů a pevných disků a přidání RAM, nicméně o tom si v tomto článku nebudeme povídat. Také optimalizací webu a vyřešením problému s hardwarem poroste zátěž jen exponenciálně. Jedná se tedy pouze o krátkodobé řešení.

Dobrá znalost SQL je nejdůležitějším nástrojem pro vývojáře webu, který vám umožní efektivně optimalizovat a používat relační databáze data. V tomto článku se zaměříme na populární otevřít databázi data, často používaná ve spojení s PHP, a to je MySQL.

Pro koho je tento článek určen?

Pro webové vývojáře, architekty a vývojáře databází a správci systému, obeznámený s MySQL. Pokud jste MySQL dosud nepoužívali, tento článek vám možná nebude k ničemu, ale přesto se pokusím být co nejvíce informativní a užitečný, a to i pro ty, kteří s MySQL začínají.

Nejprve zálohujte

Doporučuji provést následující kroky na základě báze MySQL, se kterou pracujete, ale nezapomeňte to udělat záložní kopie. Pokud nemáte databázi, se kterou byste mohli pracovat, uvedu tam, kde je to vhodné, příklady pro vytvoření vlastní databáze.

Vytváření záloh MySQL je snadné pomocí nástroje mysqldump:

$ mysqldump myTab > myTab-backup.sql Můžete se dozvědět více o mysqldump.

Co zpomaluje dotaz?

Zde obecný seznam faktory ovlivňující rychlost provádění požadavků a zatížení serveru:

• indexy tabulek;
• KDE stav (a použití vnitřní funkce MySQL, například, jako IF nebo DATE);
• seřadit podle ORDER BY;
• časté opakování stejných požadavků;
• typ mechanismu ukládání dat (InnoDB, MyISAM, Memory, Blackhole);
• nepoužíváte verzi Percona;
• konfigurace serveru (my.cnf / my.ini);
• velké datové výstupy (více než 1000 řádků);
• nestabilní spojení;
• distribuovaná nebo klastrová konfigurace;
• Špatný design stolu.

Všem těmto problémům se budeme věnovat dále. Nainstalujte si také Percona, pokud tuto vestavěnou náhradu za standardní MySQL ještě nepoužíváte – výrazně zvýší výkon vaší databáze.

Co jsou indexy?

Indexy se v MySQL používají k vyhledávání řádků se zadanými hodnotami sloupců, například s klauzulí WHERE. Bez indexů musí MySQL počínaje prvním řádkem číst celou tabulku a hledat relevantní hodnoty. Jak další stůl, tím více nákladů.

Pokud tabulka obsahuje indexy ve sloupcích, které budou použity v dotazu, MySQL rychle najde umístění potřebné informace bez zobrazení celé tabulky. To je mnohem rychlejší než prohledávání každého řádku postupně.

Nestabilní připojení?

Když se vaše aplikace připojí k databázi a je nakonfigurováno stabilní připojení, bude použita pokaždé, aniž byste museli pokaždé otevírat nové připojení. Tento optimální řešení pro pracovní prostředí.

Snížení častého opakování stejných požadavků

Nejrychlejší a efektivní způsob věc, kterou jsem pro to našel, je vytvoření úložiště dotazů a výsledků jejich provádění pomocí Memcached nebo Redis. S Memcache můžete výsledek svého dotazu snadno uložit do mezipaměti, například takto:

connect("localhost",11211); $cacheResult = $cache->get("key-name"); if($cacheResult)( //nepotřebuji dotaz $result = $cacheResult; ) else ( //spusťte dotaz $mysqli = mysqli("p:localhost","username","password","table" ); //přidej p: pro dlouhodobé uložení $sql = "VYBRAT * Z příspěvků LEVÉ PŘIPOJIT se userInfo using (UID) WHERE posts.post_type = "post" || posts.post_type = "článek" ORDER BY LIMIT 50" ; $mysqli->query($sql) $memc->set("key-name", $result->fetch_array(), MEMCACHE_COMPRESSED,86400 //Heslo $cacheResult do šablony $template->assign("); příspěvky", $cacheResult); ?> Nyní bude těžký dotaz pomocí LEFT JOIN proveden pouze jednou za 86 400 sekund (tedy jednou denně), což výrazně sníží zatížení serveru MySQL a ponechá zdroje pro další připojení.

Poznámka: Chcete-li vytvořit trvalé připojení, přidejte p: na začátek argumentu hostitele MySQLi.

Distribuovaná nebo klastrovaná konfigurace

Když je dat stále více a rychlost vašich služeb klesá, může vás zachvátit panika. Rychlé řešení se může stát distribucí zdrojů (sharding). Nedoporučuji to však dělat, pokud nemáte velké zkušenosti, protože distribuce ze své podstaty dělá datové struktury složitými.

Slabý design stolu

Vytváření databázových schémat není těžká práce, pokud budete dodržovat zlatá pravidla práce s omezeními a vědět, co bude fungovat. Například ukládání obrázků do buněk BLOB je velmi matoucí – uložení cesty k souboru do buňky VARCHAR je mnohem lepší řešení.

Zajištění správného návrhu pro požadované použití je nejdůležitější při vytváření vaší aplikace. Ukládejte různá data do různých tabulek (například kategorií a článků) a ujistěte se, že vztahy mnoho ku jedné a jedna k mnoha lze snadno přiřadit k ID. Použití FOREIGN KEY v MySQL je ideální pro ukládání kaskádových dat v tabulkách.

Při vytváření tabulky pamatujte na následující:

• Vytvářejte efektivní tabulky, které vyřeší vaše problémy, spíše než zaplňování tabulek zbytečnými daty a vztahy.
• Neočekávejte, že MySQL spustí vaši obchodní logiku nebo programování – data by měla být připravena pro váš skriptovací jazyk pro vložení řádku. Pokud například potřebujete seřadit seznam v náhodném pořadí, udělejte to v pole PHP bez použití ORDER BY z arzenálu MySQL.
• Použijte UNIKÁTNÍ typy indexů pro unikátní sady data a použijte ON DUPLICATE KEY UPDATE k udržení aktuálního data, například abyste věděli, kdy byl v minule změněno.
• Použijte typ údaje INT k ukládání celých čísel. Pokud neurčíte velikost datového typu, MySQL to udělá za vás.

Základy optimalizace

Abychom optimalizovali efektivně, musíme na vaši aplikaci použít tři přístupy:

1. Analýza (protokolování pomalých dotazů, studium systému, analýza dotazů a návrh databáze)
2. Požadavky na provedení (kolik uživatelů)
3. Technologická omezení (rychlost žehličky, zneužít MySQL)

Analýza může být provedena několika způsoby. Nejprve se podíváme na nejviditelnější způsoby, jak nahlédnout pod pokličku vašeho MySQL, kde běží dotazy. Úplně prvním optimalizačním nástrojem ve vašem arzenálu je EXPLAIN. Pokud přidáte tento příkaz před svůj dotaz SELECT, bude výsledek dotazu vypadat takto:

Sloupce, vidíte, šetřit důležité informace o žádosti. Sloupce, kterým byste měli věnovat největší pozornost, jsou possible_keys a Extra.

Sloupec possible_keys zobrazí indexy, ke kterým měla MySQL přístup za účelem provedení dotazu. Někdy je potřeba přiřadit indexy, aby váš dotaz běžel rychleji. Sloupec Extra bude indikovat, zda bylo použito další WHERE nebo ORDER BY. Nejdůležitější věcí, které je třeba si všimnout, je, zda je ve výstupu položka Použít řazení souborů.

Co dělá použití Filesort je uvedeno v nápovědě MySQL:

MySQL musí provést další průchod, aby zjistil, jak vrátit řádky v seřazené podobě. K tomuto řazení dochází iterací přes všechny řádky podle typu spojení a uložením klíče řazení a ukazatele řádku pro všechny odpovídající řádky podmíněný výraz KDE. Klíče jsou seřazeny a řádky jsou vráceny ve správném pořadí.

Další povolení zpomalí vaši aplikaci a je třeba se mu za každou cenu vyhnout. Dalším kritickým výsledkem Extra, kterému bychom se měli vyhnout, je použití dočasného. Říká, že MySQL muselo vytvořit dočasnou tabulku pro provedení dotazu. Je zřejmé, že jde o hrozné použití MySQL. V tomto případě by měl být výsledek dotazu uložen v Redis nebo Memcache a neměl by být znovu spuštěn uživateli.

Abychom se vyhnuli problému s použitím Filesort, musíme se ujistit, že MySQL používá INDEX. V současné době existuje několik klíčů specifikovaných v possible_keys, ze kterých si můžete vybrat, ale MySQL může pro konečný dotaz vybrat pouze jeden index. Indexy se také mohou skládat z několika sloupců a můžete také zadávat rady pro optimalizátor MySQL směřující na indexy, které jste vytvořili.

Hinting indexu

Optimalizátor MySQL použije statistiky založené na tabulkových dotazech k výběru nejlepšího indexu pro spuštění dotazu. Funguje docela jednoduše, na základě vestavěné statistické logiky, takže když má několik možností, ne vždy to funguje správná volba bez pomoci napovídání. Abyste zajistili, že byl použit správný (nebo nesprávný) klíč, použijte ve svém dotazu klíčová slova FORCE INDEX, USE INDEX a IGNORE INDEX. Více o indexovém hintingu si můžete přečíst v nápovědě MySQL.

Chcete-li zobrazit klávesy tabulky, použijte příkaz SHOW INDEX. Pro použití optimalizátorem můžete zadat několik rad.

Kromě EXPLAIN existuje klíčové slovo POPSAT. Pomocí DESCRIBE můžete zobrazit informace o tabulce následovně:

Přidání indexu

Chcete-li přidat indexy do MySQL, musíte použít syntaxi CREATE INDEX. Existuje několik typů indexů. FULLTEXT se používá pro fulltextové vyhledávání a UNIQUE slouží k ukládání jedinečných dat.

Chcete-li do tabulky přidat index, použijte následující syntaxi:

Mysql> CREATE INDEX idx_bookname ON `books` (bookname(10)); Tím se v tabulce knih vytvoří rejstřík, který bude používat prvních 10 písmen sloupce, v němž jsou uloženy názvy knih, a je typu varchar. V tomto případě jakékoli hledání s dotazem WHERE na název knihy se shodou do 10 znaků poskytne stejný výsledek jako skenování celé tabulky od začátku do konce.

Složené indexy

Indexy mají velký vliv na rychlosti vyřízení dotazu. Pouze hlavní schůzky jedinečný klíč nestačí - kompozitní klávesy jsou skutečná oblast aplikace v Nastavení MySQL, což někdy vyžaduje nějaké A/B testování pomocí EXPLAIN.

Pokud například potřebujeme odkazovat na dva sloupce v podmínce klauzule WHERE, ideálním řešením by byl složený klíč.

Mysql> CREATE INDEX idx_composite ON users(uživatelské jméno, aktivní); Jakmile máme vytvořený klíč založený na sloupci uživatelské jméno, ve kterém je uloženo jméno uživatele a aktivní sloupce typu ENUM, které určuje, zda je jeho účet aktivní. Nyní je vše optimalizováno pro dotaz, který použije WHERE k nalezení platného uživatelského jména s aktivním účtem (aktivní = 1).

Jak rychlé je vaše MySQL?

Pojďme si zapnout profilování, abychom se blíže podívali na dotazy MySQL. To lze provést tím, že to uděláte nastavit příkaz profilování=1, po kterém pro zobrazení výsledku musíte spustit show profiles.

Pokud používáte PDO, spusťte následující kód:

$db->query("set profiling=1"); $db->query("vyberte nadpis, tělo, značky z příspěvků"); $rs = $db->query("zobrazit profily"); $db->query("set profiling=0"); // deaktivace profilování po provedení dotazu $records = $rs->fetchAll(PDO::FETCH_ASSOC); // získání výsledků profilování $errmsg = $rs->errorInfo(); //Zde zachyťte nějaké chyby Totéž lze provést pomocí mysqli:

$db = new mysqli($host,$username,$password,$dbname);

$db->query("set profiling=1"); $db->query("vyberte nadpis, tělo, značky z příspěvků"); if ($result = $db->query("ZOBRAZIT profily", MYSQLI_USE_RESULT)) ( while ($row = $result->fetch_row()) ( var_dump($row); ) $result->close(); ) if ($result = $db->query("zobrazit profil pro dotaz 1", MYSQLI_USE_RESULT)) ( while ($row = $result->fetch_row()) ( var_dump($row); ) $result->close( ) $db->query("set profiling=0"); To vám vrátí profilovaná data obsahující čas provedení dotazu ve druhém prvku asociativního pole.

Array(3) ( => string(1) "1" => string(10) "0.00024300" => string(17) "vyberte nadpis, tělo, značky z příspěvků" ) Dokončení tohoto dotazu trvalo 0,00024300 sekund. Je to docela rychlé, takže se nebojme. Ale když se čísla zvětší, musíme se podívat hlouběji. Přejděte do své aplikace a procvičte si pracovní příklad. Zkontrolujte konstantu DEBUG v konfiguraci databáze a poté začněte prozkoumávat systém povolením výstupu profilování pomocí funkcí var_dump nebo print_r. Tímto způsobem se můžete ve své aplikaci pohybovat ze stránky na stránku a získat pohodlné profilování systému.

Chcete-li provést úplný audit vašich požadavků, povolte protokolování. Někteří vývojáři webových stránek se obávají, že protokolování výrazně ovlivňuje provádění a dále zpomaluje požadavky. Praxe však ukazuje, že rozdíl je zanedbatelný.

Chcete-li povolit protokolování v MySQL 5.1.6, použijte globální proměnnou log_slow_queries, můžete také označit soubor pro protokolování pomocí proměnné slow_query_log_file. To lze provést spuštěním následujícího dotazu:

Nastavit globální log_slow_queries = 1; set global slow_query_log_file = /dev/slow_query.log; Můžete to také zadat v konfiguračních souborech /etc/my.cnf nebo my.ini vašeho serveru.

Po provedení změn nezapomeňte restartovat MySQL server potřebným příkazem, například service mysql restart, pokud používáte Linux.

Ve verzích MySQL po 5.6.1 je proměnná log_slow_queries zastaralá a místo ní se používá slow_query_log. Pro pohodlnější ladění můžete také povolit výstup tabulky nastavením proměnné log_output na TABLE, nicméně tato funkce je dostupná až od MySQL 5.6.1.

Log_output = TABLE; log_queries_not_using_indexes = 1; long_query_time = 1; Proměnná long_query_time určuje počet sekund, po kterých je dotaz považován za pomalý. Hodnota je 10 a minimum je 0. Můžete také zadat milisekundy pomocí zlomku; teď jsem naznačil vteřinu. A nyní je každý požadavek, který bude proveden déle než 1 sekundu, zaznamenán do protokolů v tabulce.

Protokolování bude provedeno do tabulek mysql.slow_log a mysql.general_log vašeho MySQL databáze data. Chcete-li protokolování vypnout, změňte log_output na NONE.

Přihlášení na produkčním serveru

Na produkčním serveru, který obsluhuje klienty, je lepší používat protokolování pouze na krátkou dobu a sledovat zátěž, aby nedocházelo ke zbytečné zátěži. Pokud je vaše služba přetížená a je potřeba okamžitá pozornost, zkuste problém izolovat spuštěním SHOW PROCESSLIST nebo přístupem k tabulce information_schema.PROCESSLIST spuštěním SELECT * FROM information_schema.PROCESSLIST;.

Protokolování všech požadavků na produkčním serveru vám může poskytnout mnoho informací a stát se dobrý lék pro výzkumné účely při kontrole projektu, ale protokoly za dlouhá období vám mnoho nedají užitečné informace ve srovnání s protokoly za období až 48 hodin (zkuste sledovat špičkové zatížení, abyste měli šanci lépe prozkoumat provádění dotazu).

Poznámka: Pokud máte web, který občas zažívá vlny provozu a občas malý až žádný provoz, například sportovní web mimo sezónu, použijte tyto informace k vytváření a studiu protokolování.

Protokolování více požadavků

Nejen, že je důležité si být vědom dotazů, jejichž provedení trvá déle než sekundu, ale také si musíte být vědomi dotazů, které jsou spouštěny stovkykrát. I když jsou dotazy prováděny rychle, v zaneprázdněném systému mohou spotřebovat všechny zdroje.

To je důvod, proč musíte být vždy ve střehu po provedení změn v živém projektu - to je nejkritičtější čas pro provoz jakékoli databáze.

Horká a studená cache

Počet požadavků a zatížení serveru mají silný dopad na provádění a mohou také ovlivnit dobu provádění požadavků. Při vývoji byste měli stanovit pravidlo, že dokončení každého požadavku na bezplatném serveru by nemělo trvat déle než zlomek milisekundy (0,0xx nebo rychleji).

Použití Memcache má silný vliv na zatížení serverů a uvolní zdroje, které provádějí požadavky. Ujistěte se, že používáte Memcached efektivně a otestovali jste svou aplikaci pomocí horké mezipaměti (načtená data) a studené mezipaměti.

Abyste se vyhnuli běhu na produkčním serveru s prázdnou mezipamětí, je dobré mít skript, který před spuštěním serveru shromáždí veškerou nezbytnou mezipaměť, aby velký příliv klientů nezkrátil dobu spouštění systému.

Oprava pomalých dotazů

Nyní, když je protokolování nastaveno, můžete na svém webu najít nějaké pomalé dotazy. Pojďme je opravit! Jako příklad uvedu několik běžných problémů a můžete vidět logiku jejich řešení.

Pokud jste to ještě nenašli pomalý dotaz, zkontrolujte nastavení long_query_time, pokud používáte tuto metodu protokolování. V opačném případě po kontrole všech vašich profilovacích dotazů (nastavení profilování=1) vytvořte seznam dotazů, které zaberou více času než zlomek milisekundy (0,000x sekund) a začněte od něj.

Běžné problémy

Zde je šest nejčastějších problémů, které jsem našel při optimalizaci dotazů MySQL:

ORDER BY a řazení souborů

Zabránění řazení souborů někdy není možné kvůli klauzuli ORDER BY. Pro optimalizaci uložte výsledek do Memcache nebo proveďte řazení v logice aplikace.

Pomocí ORDER BY s WHERE a LEFT JOIN

ORDER BY velmi zpomaluje dotazy. Pokud je to možné, snažte se nepoužívat ORDER BY. Pokud potřebujete řazení, použijte řazení podle indexů.

Pomocí ORDER BY na dočasných sloupcích

Prostě to nedělej. Pokud potřebujete spojit výsledky, udělejte to ve své aplikační logice; V dočasné tabulce dotazů MySQL nepoužívejte filtrování ani řazení. To vyžaduje mnoho zdrojů.

Ignorování FULLTEXTOVÉHO indexu

Používání LIKE je nejvíce nejlepší způsob dělat fulltextové vyhledávání pomalý.

Nerozumná volba velké množství linky

Pokud ve svém dotazu zapomenete na LIMIT, může to značně prodloužit čas potřebný k načtení z databáze v závislosti na velikosti tabulek.

Nadměrný pomocí JOIN místo vytváření složených tabulek nebo pohledů

Když v jednom dotazu použijete více než tři nebo čtyři operátory LEFT JOIN, zeptejte se sami sebe: je zde vše správně? Pokračujte, pokud máte chuť dobrý důvod, například - dotaz se nepoužívá často pro výstup v panelu administrátora nebo může být výsledek výstupu uložen do mezipaměti. Pokud potřebujete spustit dotaz s velký počet operace spojení tabulek, pak je lepší přemýšlet o vytváření složených tabulek z potřebných sloupců nebo pomocí pohledů.

Tak

Probrali jsme základy optimalizace a nástroje potřebné k provedení práce. Prozkoumali jsme systém pomocí profilování a příkazu EXPLAIN, abychom viděli, co se děje s databází a jak bychom mohli zlepšit návrh.

Podívali jsme se také na některé příklady a klasická úskalí, do kterých se můžete při používání MySQL dostat. Pomocí indexového hintingu můžeme zajistit, že MySQL vybere potřebné indexy, zvláště když je na stejné tabulce provedeno více výběrů. Chcete-li pokračovat ve studiu tématu, doporučuji vám podívat se na projekt Percona.

V každodenní práce Při psaní dotazů se musím potýkat s poměrně podobnými chybami.

V tomto článku bych rád uvedl příklady toho, jak NEPÍSAT dotazy.

• Vyberte všechna pole
  SELECT * FROM tabulky

  Při psaní dotazů nepoužívejte výběr všech polí - "*". Uveďte pouze pole, která skutečně potřebujete. Tím se sníží množství načítaných a odesílaných dat. Nezapomeňte také na krycí indexy. I když skutečně potřebujete všechna pole v tabulce, je lepší je uvést. Za prvé, zlepšuje čitelnost kódu. Při použití hvězdičky není možné poznat, která pole jsou v tabulce, aniž byste se na ni podívali. Za druhé, časem se může změnit počet sloupců ve vaší tabulce a pokud je dnes pět INT sloupců, tak za měsíc mohou přibýt pole TEXT a BLOB, což zpomalí výběr.

• Žádosti v cyklu.
  Musíte jasně pochopit, že SQL je jazyk pro práci s množinami. Někdy programátoři, kteří jsou zvyklí myslet v pojmech procedurální jazyky, je těžké přeorganizovat myšlení do řeči množin. Toho lze dosáhnout zcela jednoduše přijetím jednoduchého pravidla – „nikdy neprovádějte dotazy ve smyčce“. Příklady, jak to lze provést:

  1. Vzorky
  $news_ids = get_list("SELECT news_id FROM today_news ");
  while($news_id = get_next($news_ids))
  $news = get_row("VYBRAT název, tělo zprávy FROM news WHERE news_id = ". $news_id);

  Pravidlo je velmi jednoduché – čím méně požadavků, tím lépe (i když jako každé pravidlo existují výjimky). Nezapomeňte na konstrukci IN(). Výše uvedený kód lze napsat v jednom dotazu:
  SELECT nadpis, tělo FROM dnešní_zprávy VNITŘNÍ PŘIPOJIT SE novinky USING(id_novinek)

  2. Vložky
  $log = parse_log();
  while($record = next($log))
  query("INSERT INTO logs SET value = ". $log["value"]);!}

  Mnohem efektivnější je zřetězit a provést jeden dotaz:
  INSERT INTO logs (hodnota) VALUES (...), (...)

  3. Aktualizace
  Někdy je potřeba aktualizovat více řádků v jedné tabulce. Pokud je aktualizovaná hodnota stejná, pak je vše jednoduché:
  AKTUALIZACE zpráv SET title="test" WHERE id IN (1, 2, 3).!}

  Pokud je změněná hodnota pro každý záznam jiná, lze to provést pomocí následujícího dotazu:
  AKTUALIZACE novinek SET
  titulek = PŘÍPAD
  KDYŽ news_id = 1 POTOM "aa"
  KDYŽ news_id = 2 POTOM "bb" KONEC
  WHERE news_id IN (1, 2)

  Naše testy ukazují, že takový požadavek je 2-3x rychlejší než několik samostatných požadavků.

• Provádění operací na indexovaných polích
  SELECT user_id FROM users WHERE blogs_count * 2 = $value

  Tento dotaz nebude používat index, i když je indexován sloupec blogs_count. Aby byl index použit, nesmí být v indexovaném poli v dotazu provedeny žádné transformace. U takových požadavků přesuňte konverzní funkce do jiné části:
  SELECT user_id FROM users WHERE blogs_count = $value / 2;

  Podobný příklad:
  SELECT user_id FROM uživatelů WHERE TO_DAYS (CURRENT_DATE) – TO_DAYS (registrováno)<= 10;

  Nepoužije index na registrované pole, zatímco
  SELECT user_id FROM users WHERE registrovaný >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
  vůle.

• Načítání řádků pouze za účelem počítání jejich počtu
  $vysledek = mysql_query("SELECT * FROM tabulka", $odkaz);
  $num_rows = mysql_num_rows($vysledek);
  Pokud potřebujete vybrat počet řádků, které splňují určitou podmínku, použijte tabulkový dotaz SELECT COUNT(*) FROM místo výběru všech řádků pouze pro sčítání počtu řádků.
• Načítání dalších řádků
  $vysledek = mysql_query("SELECT * FROM tabulka1", $odkaz);
  while($row = mysql_fetch_assoc($výsledek) && $i< 20) {
  …
  }
  Pokud potřebujete načíst pouze n řádků, použijte LIMIT namísto zahození nadbytečných řádků v aplikaci.
• Pomocí ORDER BY RAND()
  SELECT * FROM tabulka ORDER BY RAND() LIMIT 1;

  Pokud má tabulka více než 4-5 tisíc řádků, pak ORDER BY RAND() bude fungovat velmi pomalu. Mnohem efektivnější by bylo spustit dva dotazy:

  Pokud má tabulka auto_increment" primární klíč a žádné mezery:
  $rnd = rand(1, dotaz("VYBRAT MAX(id) Z tabulky"));
  $row = query("SELECT * FROM table WHERE id = ".$rnd);

  Nebo:
  $cnt = dotaz("SELECT COUNT(*) FROM tabulky");
  $row = query("SELECT * FROM table LIMIT ".$cnt.", 1");
  což však může být také pomalé, pokud je v tabulce velmi velký počet řádků.

• Použití velkého počtu JOINů
  VYBRAT
  v.video_id
  a.name,
  g.žánr
  Z
  videa AS v
  PŘIPOJIT SE VLEVO
  link_actors_videos AS la ON la.video_id = v.video_id
  PŘIPOJIT SE VLEVO
  herci JAKO ON a.actor_id = la.actor_id
  PŘIPOJIT SE VLEVO
  link_genre_video AS lg ON lg.video_id = v.video_id
  PŘIPOJIT SE VLEVO
  žánry AS g ON g.genre_id = lg.genre_id

  Je třeba pamatovat na to, že při spojování tabulek one-to-many se počet řádků ve výběru s každým dalším JOINem zvýší. V takových případech je rychlejší rozdělit takový dotaz na několik jednoduchých.

• Pomocí LIMIT
  SELECT… FROM table LIMIT $start, $per_page

  Mnoho lidí si myslí, že takový dotaz vrátí $ za_stránku záznamů (obvykle 10-20), a proto bude fungovat rychle. Na prvních stránkách to bude fungovat rychle. Ale pokud je počet záznamů velký a potřebujete provést dotaz SELECT... FROM table LIMIT 1000000, 1000020, pak k provedení takového dotazu MySQL nejprve vybere 1000020 záznamů, zahodí první milion a vrátí 20. nemusí být vůbec rychlé. Neexistují žádné triviální způsoby, jak problém vyřešit. Mnozí jednoduše omezují množství dostupné stránky rozumné číslo. Takové dotazy můžete také urychlit pomocí krycích indexů nebo řešení třetích stran (například sphinx).

• Nepoužívá se ON DUPLICATE KEY UPDATE
  $row = query("SELECT * FROM table WHERE id=1");

  If($row)
  dotaz("UPDATE table SET sloupec = sloupec + 1 WHERE id=1")
  jiný
  dotaz("INSERT INTO table SET sloupec = 1, id=1");

  Podobnou konstrukci lze nahradit jedním dotazem za předpokladu, že pro pole id existuje primární nebo jedinečný klíč:
  INSERT INTO table SET sloupec = 1, id=1 ON DUPLICATE KEY UPDATE sloupec = sloupec + 1

Číst

Správa indexů – to znamená, jak jsou vytvářeny a udržovány – může významně ovlivnit výkon SQL dotazů.

Velmi často lze použít následující optimalizace:

• odstranit nepoužívané indexy
• identifikovat nepoužívané a neúčinné indexy
• zlepšit indexy
• úplně se vyhněte SQL dotazům!
• zjednodušit sql dotazy
• a magické možnosti ukládání do mezipaměti

Kombinování DDL dotazů

Dotazy, které mění strukturu dat, obvykle blokují tabulku. Historicky spuštění dotazu ALTER vyžadovalo vytvoření nové kopie tabulky, což může být velmi časově a diskově náročné. Místo tří dotazů s malými úpravami je proto mnohem výhodnější provést jeden kombinovaný. To může ušetřit značné množství času na úlohách správy databáze.

Odstranění duplicitních indexů

Duplicitní indexy jsou škodlivé ze dvou důvodů: všechny požadavky na změnu dat budou pomalejší, protože dvojitá práce aby byla zachována úplnost indexu. Navíc to vytváří zbytečný stres souborový systém, protože velikost databáze se stává fyzicky velkou a vede ke zvýšení času potřebného k vytvoření záloh a obnovení.

Nějaký jednoduché podmínky může vést k duplicitním indexům. Například mysql nepotřebuje index na PRIMÁRNÍ pole.

Duplicitní index může také existovat, pokud je levá strana jednoho z indexů přesně stejná jako jiný index.

Nástroj pt-duplicate-key-checker z perkona-toolkit je jednoduchý a rychlý způsob zkontrolujte strukturu databáze, zda neobsahuje zbytečné indexy.

Odstranění nepoužívaných indexů

Kromě indexů, které se nikdy nepoužívají, protože jsou duplicitní, mohou existovat neduplikované indexy, které se prostě nikdy nepoužívají. Takové indexy mají stejný dopad jako duplicitní indexy. Ve standardním mysql neexistuje způsob, jak určit, které indexy se nepoužívají, ale některé verze mají podobnou funkci, například když pomocí Google Oprava MySQL.

Tato oprava zavedla funkci: SHOW INDEX_STATISTICS.

A v běžném mysql musíte nejprve shromáždit všechny použité dotazy SQL, spustit je a podívat se na plán provádění, přičemž shromáždíte informace o použitých indexech v každém případě a přenesete je do jediné tabulky. V každém případě je to užitečná zkušenost.

Optimalizace indexových polí.

Kromě vytváření nových indexů pro zlepšení výkonu můžete výkon zlepšit pomocí dalších optimalizací návrhu. Tyto optimalizace zahrnují použití vlastních dat a typů polí. Zisk v v tomto případě- to znamená menší zatížení disku a větší objem indexů, které se vejdou do RAM.

Typy dat

Některé typy lze bezbolestně vyměnit na stávajících stávajících základech.

BIGINT vs. INT

Když je PRIMÁRNÍ klíč definován jako BIGINT AUTO INCREMENT, není obecně žádný důvod jej používat. Datový typ INT UNSIGNED AUTO_INCREMENT může uložit maximální počet až 4294967295. Pokud ve skutečnosti máte více záznamů, než je tento počet, budete pravděpodobně potřebovat jinou architekturu.

Od takové změny z BIGINT na INT UNSIGNED začíná každý řádek tabulky zabírat 2x méně místa na disku, navíc se velikost obsazená PRIMARY klíčem zmenší z 8 bajtů na 4.

Toto je možná jedno z nejhmatatelnějších jednoduchých vylepšení, které lze provést zcela bezbolestně.

DATETIME vs. TIMESTAMP

Všechno je zde jednoduché: časové razítko - 4 bajty, datum a čas - 8 bajtů.

Mělo by se používat, kdykoli je to možné, protože:

• dodatečná kontrola integrity dat
• takové pole bude používat pouze 1 bajt k uložení 255 jedinečných hodnot
• Taková pole se čtou pohodlněji :)

Historicky vedlo použití polí výčtu k tomu, že základna byla závislá na změnách možných hodnot ve výčtu. Toto byl blokující požadavek DDL. Od verze MySQL 5.1 je přidávání nových variant do výčtu velmi rychlé a nesouvisí s velikostí tabulky.

NULL vs NOT NULL

Pokud si nejste jisti, že sloupec může obsahovat hodnotu null (NULL), je lepší jej definovat jako NOT NULL. Index na takovém sloupci bude mít menší velikost a bude snadněji zpracovatelný.

Automatické převody typů

Při výběru datového typu pro spojování polí se může stát, že datový typ pole není definován. Vestavěná konverze může být zcela zbytečná režie.

U celočíselných polí se ujistěte, že jsou stejné položky PODPÍNANÉ a NEPODEpsané, u proměnných typů polí práce navíc Při připojování může dojít ke konverzi kódování, takže je také nezapomeňte zkontrolovat. Častým problémem je automatická konverze mezi kódováním latin1 a utf8.

Typy sloupců

Některé typy dat jsou často uloženy ve špatných sloupcích. Změna typu při tom může vést k efektivnějšímu ukládání, zejména pokud jsou tyto sloupce zahrnuty do indexu. Podívejme se na několik typických příkladů.

IP adresa

Adresu IPv4 lze uložit do pole INT UNSIGNED, které zabírá pouze 4 bajty. Běžná situace nastává, když je IP adresa uložena v poli VARCHAR(15), které zabírá 12 bajtů. Tato jedna změna může zmenšit velikost o 2/3. Funkce INET_ATON() a INET_NTOA se používají k převodu mezi řetězcem s adresou IP a číselnou hodnotou.

U adres IPv6, které jsou stále populárnější, je důležité ukládat jejich 128bitovou číselnou hodnotu do polí BINARY(16) a nepoužívat VARCHAR pro formát čitelný člověkem.

Ukládání polí md5 jako CHAR(32) je běžnou praxí. Pokud použijete pole VARCHAR(32), přidáte ke každé hodnotě také režii délky řetězce navíc. Řetězec md5 je však hexadecimální hodnota – a lze jej uložit efektivněji pomocí funkcí UNHEX() a HEX(). V tomto případě mohou být data uložena v BINARY(16) polích. Tato jednoduchá akce zmenší velikost pole z 32 bajtů na 16 bajtů. Podobný princip lze aplikovat na libovolné hexadecimální hodnoty.

Natočeno podle knihy Ronalda Bradforda.

Používání databází člověku velmi usnadňuje život a práci s daty a umožňuje jim je získávat krátké termíny potřebné informace z databáze nebo do ní zapisovat. Práce s daty však vyžaduje správný přístup, programátor by měl vzít v úvahu některé aspekty interakce s databázemi. Zejména mluvíme o o MySQL. Dále se podívejme na souhrn tipů pro optimalizaci interakce s databázemi Data MySQL.

Usnadněte ukládání dotazů MySQL do mezipaměti

Vestavěný mechanismus ukládání dotazů do mezipaměti na serveru MySQL může výrazně zlepšit výkon. Většina databázových serverů MySQL obsahuje mechanismus ukládání do mezipaměti. Mnoho identických dotazů do databáze v krátkém časovém období může způsobit značné ztráty výkonu, mechanismus ukládání do mezipaměti je schopen takové dotazy ukládat do mezipaměti a vracet data z mezipaměti. Existují dotazy, které MySQL neumí ukládat do mezipaměti, a proto se doporučuje provádět tyto dotazy trochu jinak.

// toto MySQL dotaz nebude moci ukládat do mezipaměti $res = mysql_query("VYBRAT uživatelské jméno FROM uživatele WHERE datum_registrace >= CURDATE()"); // můžete to udělat jinak $dnes = datum("Y-m-d"); $res = mysql_query("VYBRAT uživatelské jméno FROM uživatele WHERE datum_registrace >= "$today"");

Faktem je, že v prvním požadavku byla použita funkce CURDATE() a zvláštnost její činnosti neumožňuje umístit výsledky dotazu do mezipaměti. Hodnotu data lze předem zapsat do řetězce dotazu, čímž se v dotazu eliminuje použití funkce CURDATE().
Analogicky existují další funkce, které se samy neukládají do mezipaměti MySQL server, mezi nimi RAND(), NOW() a také další funkce, jejichž výsledky jsou nedeterministické.

Podívejte se, jak váš dotaz běží pomocí syntaxe EXPLAIN

Pomocí syntaxe EXPLAIN můžete vidět, jak MySQL provádí váš dotaz. Jeho použití může pomoci identifikovat slabá místa ve výkonu dotazů a také ve struktuře tabulky. Jako výsledek dotazu EXPLAIN vrátí data, která ukážou, jaké indexy se používají, jak jsou data vybírána z tabulek, jak jsou řazena atd. K tomu stačí přidat klíčové slovo EXPLAIN na začátek SELECT dotazu, po kterém se zobrazí tabulka s daty.

Když potřebujete jeden záznam, nastavte LIMIT 1

Existuje poměrně málo případů, kdy potřebujete zkontrolovat přítomnost alespoň jednoho záznamu z tabulky, v tomto případě se doporučuje přidat do dotazu parametr LIMIT 1, protože Databázový stroj zastaví načítání dat po nalezení prvního záznamu namísto načítání všech dat. Šetříte zdroje.

// dotaz na město pomocí kódu Shymkent z databáze $res = mysql_query("SELECT * FROM location WHERE city = "Shymkent""); if (mysql_num_rows($res) > 0)( ) // přidání LIMIT 1 pro optimalizaci dotazu $res = mysql_query("SELECT * FROM location WHERE city = "Shymkent" LIMIT 1"); if (mysql_num_rows($res) > 0)( )

Indexujte pole, která hledáte

V konkrétním případě index znamená index polí, ve kterých vyhledáváte, což zvýší rychlost vyhledávání. Mimochodem, regulární index nemůže pracovat s podmínkami ve formě regulárních výrazů:

// index město LIKE 'shym%' zde bude fungovat // index zde nebude použit město LIKE '%shymkent%'

Chcete-li vytvořit index pro podmínky s regulární výrazy měli byste použít , nebo se zamyslet nad svým indexovým systémem.

Indexujte pole, podle kterých jsou tabulky spojeny

Pokud používáte více spojení tabulek, možná budete chtít zvážit, zda jsou pole zapojená do spojení indexována v obou tabulkách. Tato záležitost ovlivňuje, jak bude MySQL provádět vnitřní optimalizaci spojení polí tabulky. Sjednocovací pole musí být stejného typu a kódování. Tito. pokud je například jedno pole typu DECIMAL a druhé INT, pak MySQL nebude moci index používat.

Najít alternativu k ORDER BY RAND()

Použití náhodného řazení je skutečně velmi pohodlné a mnoho začínajících programátorů na to má stejný názor. Nicméně existuje úskalí a velmi významné, pomocí podobnou metodu načítáním vašich dotazů opouštíte problémové místo výkonu. Zde se doporučuje uchýlit se k dodatečnému kódu namísto použití ORDER BY RAND() jako alternativy, abyste se zbavili výkonnostního úzkého hrdla, které se bude při narůstajícím objemu dat připomínat.

Použijte výběr konkrétních polí místo SELECT *

Nebuďte líní označit v dotazu při výběru konkrétní povinná pole místo použití „*“ – při výběru všech polí platí, že čím více dat je z tabulky načteno, tím je váš dotaz pomalejší.

Přidejte pole ID pro všechny tabulky

Každá tabulka, pokud je provedena dobře, by měla mít pole id typ INT, což je primární klíč (PRIMARY_KEY), a AUTO_INCREMENT. Kromě toho musíte pro pole zadat parametr UNSIGNED, což znamená, že hodnota bude vždy kladná.
MySQL má interní operace, které mohou používat primární klíč, to přichází do hry pro složité konfigurace databáze, jako jsou clustery, paralelizace atd.
Kromě toho, pokud existuje několik tabulek a potřebujete provést spojený dotaz, budou ID tabulek velmi užitečné.

ENUM jako alternativa k VARCHAR

Představme si, že chcete do tabulky přidat pole, které by mělo obsahovat konkrétní sadu hodnot. Tradičně mnoho programátorů nastavuje pro pole typ VARCHAR. Existuje však jiný typ pole, který je mnohem rychlejší a kompaktnější. Hodnoty tohoto typu se ukládají stejným způsobem jako TINYINT, ale zobrazují se jako v řetězcovém typu.

Místo NULL použijte NOT NULL

Pole NULL zabírají více prostoru v záznamu, protože je potřeba tuto hodnotu NULL označit. Tabulky MyISAM, pole NULL jsou uložena tak, že každé pole zabírá 1 bit navíc, který se zaokrouhluje na nejbližší bajt. Pokud použití NULL v poli není důležité, pak se doporučuje použít NOT NULL.

Použijte připravené výpisy

$res = "AKTUALIZOVAT SET hostitelů ip = INET_ATON("($_SERVER["REMOTE_ADDR"])") WHERE id = $host_id";

Používejte statické tabulky

Statická tabulka je běžná tabulka v databázi, kromě toho, že každé pole v tabulce má pevnou velikost. Pokud má tabulka sloupce, které nemají pevnou délku, mohou to být například: VARCHAR, TEXT, BLOB, přestane být statická a bude zpracována MySQL trochu jinak. Statické tabulky, nebo je lze také nazývat tabulky pevné velikosti, pracují rychleji než nestatické. Záznamy z takových tabulek budou zobrazeny rychleji, pokud je vyžadován výběr požadovaný řádek MySQL rychle vypočítá svou pozici. Pokud pole nemá pevnou velikost, pak se v tomto případě vyhledávání provádí podle indexu. Použití má další výhody statické tabulky, faktem je, že tyto tabulky se snáze ukládají do mezipaměti a lze je také obnovit po pádu databáze.

Použijte vertikální separaci

Vertikální dělení – zahrnuje rozdělení tabulky do sloupců za účelem zvýšení výkonu tabulky. Například pokud máte v tabulce pole, která se používají velmi zřídka, nebo jsou pole s variabilní délka, pak je lze umístit do samostatného stolu, čímž se stůl vyloží, čímž se zvýší rychlost práce s ním.

Oddělte velké dotazy INSERT a DELETE

Provádění velkého množství dotazů tohoto druhu může vést k zamykání tabulky, což může vést k nefunkčnost aplikace obecně. Paralelní požadavky na webový server mohou generovat další přístup k tabulce. Pokud je tabulka zablokována předchozím požadavkem, další požadavky se řadí do fronty a ve výsledku se to projeví zpomalením webu nebo dokonce pádem serveru.
Potřebujete-li vytvořit velké množství požadavků, zkuste je řídit tím, že je budete posílat v malých dávkách, než abyste vše ukládali do databáze. Dokončení vašeho požadavku může trvat déle, ale bude to mít menší dopad na ostatní uživatele.
Příklad:

While (1)( mysql_query("DELETE FROM logs WHERE log_date<= "2015-07-20" LIMIT 1000"); if (mysql_affected_rows() == 0){ // записи удалены успешно break; } usleep(50000); // делаем небольшую паузу }

Zkuste použít malá pole

Jak víte, databázová data jsou uložena na pevném disku, což může být často jedním ze slabých míst webové aplikace. Faktem je, že malé záznamy jsou výhodnější, protože... jejich použití snižuje zátěž na pevném disku. Pokud jste si jisti, že konkrétní tabulka uchová několik řádků, pak by racionálním řešením bylo použít typy polí s minimálními možnými hodnotami. Pokud je například hlavní klíč typu INT a do tabulky budete ukládat jen malé množství dat, je lepší jej vytvořit typu MEDIUMINT, SMALLINT nebo dokonce TINYINT.

Vyberte si typ stolů, aby vyhovoval vašim úkolům

Dva dnes široce známé typy tabulek jsou MyISAM a InnoDB, každý z nich má své výhody a nevýhody. Například MyISAM je dobrý při čtení dat z tabulek ve velkém množství, ale při zápisu je pomalejší. Funguje také dobře na dotazy jako SELECT COUNT(*).
Mechanismus ukládání dat InnoDB je složitější než u MyISAM, nicméně podporuje zamykání řádků, což je pozitivní stránka při škálování. Proto nelze říci, že jeden je lepší než druhý, a není to správné, musíte si vybrat typ podle svých potřeb.