Osvědčené postupy pro řešení potíží s flexibilním serverem Azure Database for MySQL
PLATÍ PRO:
Flexibilní server Azure Database for MySQL – Jednoúčelový server Azure Database for MySQL
Důležité
Jednoúčelový server Azure Database for MySQL je na cestě vyřazení. Důrazně doporučujeme upgradovat na flexibilní server Azure Database for MySQL. Další informace o migraci na flexibilní server Azure Database for MySQL najdete v tématu Co se děje s jednoúčelovým serverem Azure Database for MySQL?
Následující části vám pomůžou zajistit, aby flexibilní serverové databáze Azure Database for MySQL běžely hladce a používaly tyto informace jako hlavní principy pro zajištění optimálního návrhu schémat a zajištění nejlepšího výkonu pro vaše aplikace.
Kontrola počtu indexů
V zaneprázdněném databázovém prostředí můžete pozorovat vysoké využití vstupně-výstupních operací, což může být indikátorem špatných vzorů přístupu k datům. Nepoužité indexy můžou mít negativní vliv na výkon, protože spotřebovávají místo na disku a mezipaměť a zpomalují operace zápisu (INSERT, DELETE/ UPDATE). Nevyužité indexy zbytečně spotřebovávají více místa v úložišti a zvětšují velikost zálohy.
Před odebráním indexu nezapomeňte shromáždit dostatek informací, abyste ověřili, že se už nepoužívá. Toto ověření vám může pomoct vyhnout se neúmyslnému odebrání indexu, který je kritický pro dotaz, který běží jenom čtvrtletně nebo ročně. Nezapomeňte také zvážit, jestli se index používá k vynucení jedinečnosti nebo řazení.
Poznámka:
Nezapomeňte pravidelně kontrolovat indexy a provádět potřebné aktualizace na základě jakýchkoli úprav dat tabulky.
SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name;
(nebo)
use information_schema; select tables.table_name, statistics.index_name, statistics.cardinality, tables.table_rows from tables join statistics on (statistics.table_name = tables.table_name and statistics.table_schema = '<YOUR DATABASE NAME HERE>' and ((tables.table_rows / statistics.cardinality) > 1000));
Výpis nejužších indexů na serveru
Výstup z následujícího dotazu poskytuje informace o nejčastěji používaných indexech ve všech tabulkách a schématech na databázovém serveru. Tyto informace jsou užitečné při identifikaci poměru zápisů ke čtení proti každému indexu a počtu latencí čtení i jednotlivých operací zápisu, což může znamenat, že se pro podkladovou tabulku a závislé dotazy vyžaduje další ladění.
SELECT
object_schema AS table_schema,
object_name AS table_name,
index_name, count_star AS all_accesses,
count_read,
count_write,
Concat(Truncate(count_read / count_star * 100, 0), ':',
Truncate(count_write / count_star * 100, 0)) AS read_write_ratio,
count_fetch AS rows_selected ,
count_insert AS rows_inserted,
count_update AS rows_updated,
count_delete AS rows_deleted,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency ,
Concat(Round(sum_timer_fetch / 1000000000000, 2), ' s') AS select_latency,
Concat(Round(sum_timer_insert / 1000000000000, 2), ' s') AS insert_latency,
Concat(Round(sum_timer_update / 1000000000000, 2), ' s') AS update_latency,
Concat(Round(sum_timer_delete / 1000000000000, 2), ' s') AS delete_latency
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star > 0
ORDER BY sum_timer_wait DESC
Kontrola návrhu primárního klíče
Flexibilní server Azure Database for MySQL používá modul úložiště InnoDB pro všechny netemporární tabulky. S InnoDB se data ukládají v clusterovém indexu pomocí struktury B-Tree. Tabulka je fyzicky uspořádaná na základě hodnot primárního klíče, což znamená, že řádky jsou uloženy v pořadí primárního klíče.
Každá položka sekundárního klíče v tabulce InnoDB obsahuje ukazatel na hodnotu primárního klíče, ve které jsou data uložena. Jinými slovy, sekundární položka indexu obsahuje kopii hodnoty primárního klíče, na kterou položka ukazuje. Proto mají možnosti primárního klíče přímý vliv na množství režijních nákladů na úložiště v tabulkách.
Pokud je klíč odvozen od skutečných dat (např. uživatelské jméno, e-mail, SSN atd.), nazývá se přirozený klíč. Pokud je klíč umělý a neodvozuje se z dat (např. automaticky se zvýší celé číslo), označuje se jako syntetický klíč nebo náhradní klíč.
Obecně se doporučuje vyhnout použití přirozených primárních klíčů. Tyto klíče jsou často velmi široké a obsahují dlouhé hodnoty z jednoho nebo více sloupců. To zase může představovat závažnou režii úložiště, protože hodnota primárního klíče se kopíruje do každé položky sekundárního klíče. Kromě toho přirozené klíče obvykle nedodržují předem určené pořadí, což výrazně snižuje výkon a vyvolává fragmentaci stránky při vkládání nebo aktualizaci řádků. Abyste se těmto problémům vyhnuli, používejte místo přirozených klíčů monotonicky rostoucí náhradní klíče. Dobrým příkladem monotonicky rostoucího náhradního klíče je sloupec typu autoinkrementace (big)integer. Pokud potřebujete určitou kombinaci sloupců, musí být jedinečné, deklarujte tyto sloupce jako jedinečný sekundární klíč.
Během počátečních fází vytváření aplikace si možná nepřemyslíte, že si předem představte čas, kdy se tabulka začne přibližovat dvěma miliardami řádků. V důsledku toho můžete pro datový typ sloupce ID (primárního klíče) použít 4 bajtové celé číslo se 4 bajty. Nezapomeňte zkontrolovat všechny primární klíče tabulky a přepnout na použití 8bajtů celých sloupců (BIGINT), aby bylo možné využít potenciál pro velký objem nebo růst.
Poznámka:
Další informace o datových typech a jejich maximálních hodnotách najdete v referenční příručce k MySQL v tématu Datové typy.
Použití překrývajících se indexů
Předchozí část vysvětluje, jak jsou indexy v MySQL uspořádané jako stromy B a v clusterovém indexu, uzly typu list obsahují datové stránky podkladové tabulky. Sekundární indexy mají stejnou strukturu B-strom jako clusterované indexy a můžete je definovat v tabulce nebo zobrazení pomocí clusterovaného indexu nebo haldy. Každý řádek indexu v sekundárním indexu obsahuje neclusterovanou hodnotu klíče a lokátor řádku. Tento lokátor odkazuje na datový řádek v clusterovém indexu nebo haldě s hodnotou klíče. V důsledku toho musí jakékoli vyhledávání zahrnující sekundární index přecházet od kořenového uzlu přes uzly větví na správný uzel typu list, aby převzal hodnotu primárního klíče. Systém pak provede náhodné čtení vstupně-výstupních operací v indexu primárního klíče (znovu přejde z kořenového uzlu přes uzly větví na správný uzel typu list) a získá řádek dat.
Abyste se vyhnuli tomuto nadbytečnému náhodnému čtení vstupně-výstupních operací v indexu primárního klíče, abyste získali řádek dat, použijte index pokrytí, který zahrnuje všechna pole vyžadovaná dotazem. Obecně platí, že použití tohoto přístupu je výhodné pro vstupně-výstupní úlohy a úlohy uložené v mezipaměti. Proto jako osvědčený postup použijte pokrytí indexů, protože se vejdou do paměti a jsou menší a efektivnější pro čtení než skenování všech řádků.
Představte si například tabulku, kterou používáte k vyhledání všech zaměstnanců, kteří se připojili ke společnosti po 1. lednu 2000.
mysql> show create table employee\G
*************************** 1. row ***************************
Table: employee
Create Table: CREATE TABLE `employee` (
`empid` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(10) DEFAULT NULL,
`lname` varchar(10) DEFAULT NULL,
`joindate` datetime DEFAULT NULL,
`department` varchar(10) DEFAULT NULL,
PRIMARY KEY (`empid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)`
`mysql> select empid, fname, lname from employee where joindate > '2000-01-01';
Pokud na tomto dotazu spustíte plán EXPLAIN, zjistíte, že se aktuálně nepoužívají žádné indexy a k filtrování záznamů zaměstnanců se používá samotná klauzule where.
mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employee
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.01 sec)
Pokud jste ale přidali index, který se zabývá sloupcem v klauzuli where, společně s předpokládanými sloupci, uvidíte, že se index používá k mnohem rychlejšímu a efektivnějšímu vyhledání sloupců.
mysql> CREATE INDEX cvg_idx_ex ON employee (joindate, empid, fname, lname);
Pokud teď spustíte plán EXPLAIN na stejném dotazu, zobrazí se hodnota Using Index v poli Extra, což znamená, že InnoDB spustí dotaz pomocí indexu, který jsme vytvořili dříve, což potvrzuje, že se jedná o krytý index.
mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employee
partitions: NULL
type: range
possible_keys: cvg_idx_ex
key: cvg_idx_ex
key_len: 6
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)
Poznámka:
Je důležité zvolit správné pořadí sloupců v indexu pokrytí, aby se dotaz správně zobrazoval. Obecným pravidlem je zvolit sloupce pro filtrování první (klauzule WHERE), pak řazení/seskupení (ORDER BY a GROUP BY) a nakonec projekce dat (SELECT).
Z předchozího příkladu jsme viděli, že pokrytí indexu pro dotaz poskytuje efektivnější cesty načítání záznamů a optimalizuje výkon v vysoce souběžném databázovém prostředí.
Další kroky
Pokud chcete najít partnerské odpovědi na nejdůležitější otázky nebo publikovat nebo odpovědět na otázky, navštivte web Stack Overflow.