Jak optimalizovat tabulky MySQL

Úvod

Optimalizace tabulek MySQL pomáhá měnit pořadí informací na vyhrazeném úložném serveru, aby se zvýšila rychlost vstupu a výstupu dat. Nicméně vědět, kdy použít jednotlivé optimalizační funkce a jak je aplikovat na vaši situaci, je klíčem k životaschopné údržbě tabulky.

Tento článek poskytuje praktické tipy a funkce pro optimalizaci tabulek MySQL.

Předpoklady

• Nainstalováno a nakonfigurováno MySQL verze 8.0
• Přístup k příkazovému řádku nebo příkazovému řádku
• Fragmentované databázové tabulky

Proč byste měli optimalizovat tabulky MySQL?

Hlavním důvodem neoptimalizované tabulky MySQL jsou často prováděné aktualizační a mazací dotazy. To zase způsobuje fragmentaci a má to několik důsledků:

1. Databázová tabulka zabírá více místa než potřebuje.

2. Dotazování dat zabere více času než by měl.

Techniky optimalizace tabulek MySQL řeší uspořádání dat uvnitř databáze. Výsledkem je čisté úložiště bez nadbytečného, ​​nevyužitého prostoru, což pomáhá urychlit dotazy.

Kdy byste měli optimalizovat tabulky MySQL?

Tabulky, kde se informace v databázi neustále aktualizují, jako jsou transakční databáze, jsou nejpravděpodobnějšími kandidáty na optimalizaci.

V závislosti na velikosti databáze však u větších tabulek trvá dokončení optimalizačního dotazu dlouho. Proto zamykání tabulky na dlouhý počet hodin není pro transakční systémy výhodné.

Místo okamžité optimalizace tabulky zvažte vyzkoušení některých z těchto triků pro tabulky motoru INNODB:

• Zrušte index, optimalizujte jej a přidejte zpět index . Pokud tabulka nějakou dobu funguje bez indexů, vypuštění, optimalizace a přidání indexů bude v některých případech fungovat rychleji.
• Analyzujte, která hodnota těží ze zhutnění nejvíce . Někdy není primární index užitečný nebo problematický. Optimalizace na nesprávnou hodnotu způsobuje fragmentaci se sekundárními indexy.

Najděte tabulky pro optimalizaci

Existuje několik způsobů, jak zobrazit tabulky a analyzovat je za účelem optimalizace. Začněte připojením k databázi MySQL:

use <database name>

V závislosti na vašem případu použití filtrujte potřebné informace podle jednoho z níže uvedených tipů.

Tip 1:Zobrazit nevyužité místo v tabulce

Zkontrolujte stav požadované tabulky pomocí:

show table status like "<table name>" \G

Výstup zobrazuje některé obecné informace o tabulce. Následující dvě čísla jsou důležitá:

• Délka_dat představuje množství místa, které databáze celkem zabírá.
• Data_free zobrazuje přidělené nevyužité bajty v databázové tabulce. Tyto informace pomáhají určit, které tabulky potřebují optimalizaci a kolik místa se poté uvolní.

Tip 2:Zobrazit nevyužité místo pro všechny tabulky

informační schéma ukládá metadata o schématu databáze. Chcete-li zkontrolovat alokovaná nepoužitá data pro všechny tabulky ve vybraném schématu, spusťte:

select table_name, data_length, data_free
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;

Dotaz zobrazí název tabulky, celkový prostor a nevyužité přidělené místo. Ve výchozím nastavení se paměť tiskne v bajtech.

Tip 3:Zobrazení dat v megabajtech

Vytiskněte data v megabajtech pomocí:

select table_name, round(data_length/1024/1024), round(data_free/1024/1024)
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;

Ukázkové tabulky nejsou příliš fragmentované. Nicméně pomocí optimize table pomáhá uvolnit místo v test_table .

Optimalizovat tabulky

Existuje několik způsobů, jak optimalizovat tabulky defragmentací. Obecné kroky, které provádíte, jsou však podobné.

Nejprve proces vytvoří dočasnou kopii tabulky, kde dochází k optimalizaci. Poté, když operace skončí, funkce nahradí původní tabulku optimalizovanou a přejmenuje ji podle originálu.

Tip 1:Optimalizujte tabulku pomocí MySQL

MySQL poskytuje funkci pro optimalizaci tabulky. Chcete-li optimalizovat tabulku, použijte:

OPTIMIZE TABLE <table name>;

Výstup zobrazuje informativní stavovou zprávu o akcích a výsledcích optimalizace v tabulkovém formátu.

Tip 2:Optimalizujte více stolů najednou

Chcete-li optimalizovat více tabulek najednou, použijte:

OPTIMIZE TABLE <table 1>, <table 2>, <table 3>;

Výsledek ukazuje stav z optimalizace pro každou optimalizovanou tabulku.

Tip 3:Optimalizujte tabulky pomocí terminálu

Proveďte optimalizaci tabulky prostřednictvím terminálu Linux pomocí:

sudo mysqlcheck -o <schema> <table> -u <username> -p <password>

Chcete-li například provést kontrolu schématu s názvem test a tabulka s názvem test_table pomocí rootu přihlašovací údaje uživatele, spusťte:

sudo mysqlcheck -o test test_table -u root

Chcete-li provést optimalizaci na více tabulkách, oddělte každý název tabulky mezerou:

sudo mysqlcheck -o <schema> <table 1> <table 2> -u <username> -p <password>

Po optimalizaci

Výsledky optimalizace mění hodnoty data_length a data_free optimalizované tabulky. Obě hodnoty jsou sníženy, což znamená:

1. Optimalizace uvolnila nevyužitou alokovanou paměť.

2. Celková paměť databáze je nižší kvůli uvolněnému prostoru.