Във всяка система за управление на релационни бази данни е наложително периодично да се извършват операции по поддръжка.
Операциите за поддръжка в MySQL (в официалната документация се наричат изявления – statements) се разделят на четири основни категории: проверка, анализ, оптимизация и поправяне на таблици.
Всяка от тези операции прави нещо малко по-различно и ние ще разгледаме главните особености на всяка от тези четири операции поотделно.
Ще забележите, че три от четирите работят с двете най-използвани хранилища за данни в MySQL – MyISAM и InnoDB (основно хранилище по подразбиране).
Единствената операция, която не работи с InnoDB, е изявлението за поправяне – table repair
, което в абсолютно никакъв случай не е недостатък в InnoDB, както сами ще се убедите в статията.
Поддръжката и оптимизацията на таблиците в базата данни може да се извършва през phpMAdmin или през SSH.
Анализиране на таблица – ANALYZE TABLE
ANALYZE TABLE извършва анализ на разпределението на ключове и съхранява разпределението за посочената таблица или таблици.
ANALYZE TABLE работи с таблици InnoDB, NDB и MyISAM, не работи с views.
ANALYZE TABLE се поддържа за partitioned таблици и можете да използвате ALTER TABLE ... ANALYZE PARTITION
за анализиране на един или повече дялове. По време на анализа, таблицата е заключена с read lock
за четене за InnoDB и MyISAM.
По подразбиране сървърът записва резултатите от ANALYZE TABLE в бинарен лог, за да се възпроизвеждат само вторичните (slave) репликации. За да спрете записването, посочете ключовата дума NOWRITETO_BINLOG или псевдонимът LOCAL.
ANALYZE TABLE връща резултата от операцията по начин, показан в следващите таблици:
MySQL използва запазеното разпределение на ключове (stored key distribution), за да определи реда за присъединяване (join) на таблицата към нещо различно от константа. В допълнение, ключовите разпределения могат да се използват, когато се решава кои индекси да се използват за конкретна таблица в рамките на дадена заявка.
Ако системната променлива *innodbstatspersistent* е активирана, ANALYZE TABLE трябва да се изпълнява след зареждане на значителни данни в InnoDB таблица или за създаване на нов индекс.
За да проверите кардиналното разпределение на запаметените ключове, използвайте инструкцията show index
или таблицата information_schema.statistics
:
Проверка на таблица – CHECK TABLE
CHECK TABLE проверява таблица или таблици за грешки. За MyISAM таблиците се актуализират ключовите статистически данни. CHECK TABLE също може да проверява views за проблеми, като например таблици, които са посочени в дефиниция за view, която вече не съществува.
CHECK TABLE работи с таблици InnoDB, MyISAM, ARCHIVE и CSV.
CHECK TABLE се поддържа за partitioned таблици и можете да използвате ALTER TABLE ... CHECK PARTITION
за проверка на един или повече дялове.
CHECK TABLE пренебрегва виртуалните генерирани колони, които не са индексирани.
CHECK TABLE връща резултата от операцията по начин, показан в следващите таблици:
Изявлението може да генерира много редове информация за всяка проверена таблица. Последният ред Msg_type
има стойност status и Msg_text
нормално трябва да е OK.
За MyISAM таблица ако не получите ОК
или Table is already up to date
трябва да опитате да поправите (repair) таблицата.
Съобщението Table is already up to date
означава, че няма нужда да се проверява таблицата.
Следните бележки се отнасят за InnoDB таблици:
- Ако CHECK TABLE открие повредена страница, спира сървъра, за да предотврати разпространението на грешки;
- Ако CHECK TABLE открие повредено поле
DB_TRX_ID
илиDB_ROLL_PTR
в клъстериран индекс, CHECK TABLE може да стане причина InnoDB да достъпи невалиден undo log запис и води до спиране на сървъра, свързано с MVCC (multiversion concurrency control); - Ако CHECK TABLE открие грешки в InnoDB таблици или индекси, съобщава за грешка и обикновено маркира индекса, понякога маркира и таблицата като повредена, предотвратявайки по-нататъшното използване на индекса или таблицата;
- Ако CHECK TABLE открие неправилен брой записи във вторичен индекс, съобщава за грешка, но не води до спиране на сървъра или до спиране на достъпа до него;
- CHECK TABLE проверява структурата на индексираната страница, след което проверява позицията на всеки ключ. Той не валидира ключовия показалец към клъстерния запис, нито проследява BLOB показалеца;
- Когато стартирате CHECK TABLE на големи InnoDB таблици, други операции могат да бъдат блокирани по време на изпълнението на CHECK TABLE;
- Функцията CHECK TABLE за InnoDB SPATIAL индекси включва проверка на валидността на R-tree и проверка за да се гарантира, че броят редове в R-tree съответства на индекса в клъстера;
- CHECK TABLE поддържа вторични индекси на виртуално генерирани колони, които се поддържат от InnoDB.
Оптимизиране на таблица – OPTIMIZE TABLE
OPTIMIZE TABLE анализира таблицата, съхранява разпределението на ключовете, възстановява неизползваното пространство и дефрагментира файла с данните.
OPTIMIZE TABLE се използва най-често в следните случаи (в зависимост от вида на таблицата):
- След извършване на значителни операции по вмъкване, актуализиране или изтриване в InnoDB таблица. Таблицата и индексите се реорганизират и дисковото пространство може да бъде възстановено за използване от операционната система;
- След извършване на съществени операции по вмъкване, актуализиране или изтриване на колони, които са част от FULLTEXT индекс в InnoDB таблица;
- След изтриване на голяма част от MyISAM или ARCHIVE таблица или извършване на голям брой промени в MyISAM или ARCHIVE таблица. Може да използвате OPTIMIZE TABLE за възстановяване на неизползваното дисково пространство и за дефрагментиране на файла с данни. След големи промени в таблицата, тази операция може да подобри значително производителността (бързината).
Подобно на операция за дефрагментиране, командата за оптимизиране на таблици възстановява неизползваното пространство за MyISAM. С InnoDB той основно изпълнява ALTER TABLE команда, която казва на MySQL да възстанови таблицата и нейните индекси.
Ако получите отговор Table does not support optimize, doing recreate + analyze instead
, това е коректния отговор за InnoDB таблица.
По подразбиране сървърът записва резултатите от OPTIMIZE TABLE в бинарeн лог, за да се възпроизвеждат на вторичните (slave) репликации. За да спрете записването, посочете ключовата дума NOWRITETO_BINLOG или псевдонимът LOCAL.
InnoDB детайли
За таблици с InnoDB, OPTIMIZE TABLE е преобразувана в ALTER TABLE ... FORCE
, която възстановява таблицата, за да актуализира статистическите данни за индексите и да освободи неизползваното пространство в клъстер индекса.
Това показва изхода на OPTIMIZE TABLE, когато го стартирате в таблица InnoDB, както е показано тук:
OPTIMIZE TABLE преизгражда (recreate) таблицата, като използва метода на копиране на таблицата при следните условия:
- Когато е активирана системната променлива
old_alter_table
; - Когато е активирана
mysqld - skip-new
опцията.
OPTIMIZE TABLE използвайки онлайн DDL (Data definition language) не се поддържа за InnoDB таблици, които съдържат FULLTEXT индекси. Вместо това се използва методът за копиране на таблицата.
InnoDB съхранява данните, като използва метод за разпределение на страници и не страда от фрагментация по същия начин, както при MyISAM, например. Когато преценявате дали да стартирате или не оптимизиране, помислете за натоварването от транзакциите, които сървърът ще обработва:
- Очаква се известно ниво на фрагментация. InnoDB само запълва страниците с цели
93%
, за да остави място за обновяване, без да се налага да разделяте страниците; - Изтриването на операции може да остави празнини, които оставят страниците по-малко запълнени от желаното, което би могло да направи полезно оптимизирането на таблицата;
- Актуализациите на редовете обикновено пренаписват данните в една и съща страница, в зависимост от типа данни и формата на реда, когато има достатъчно място;
- Работните натоварвания с голяма конкуренция могат да оставят празнини в индексите с течение на времето, тъй като InnoDB запазва множество версии на едни и същи данни, които се дължат на механизма MVCC (
multiversion concurrency control
).
MyISAM Детайли
За MyISAM таблици OPTIMIZE TABLE работи по следния начин:
- Ако таблицата е изтрила или разделила редове, поправете (repair) таблицата;
- Ако индексните страници не са сортирани, ги сортирайте (sort);
- Ако статистическите данни на таблицата не са актуални (и поправянето не може да бъде извършено чрез сортиране на индекса), актуализирайте ги (update).
Поправяне на таблица – REPAIR TABLE
REPAIR TABLE поправя евентуално повредена таблица, само за определени хранилища за данни (MyISAM ):
Въпреки че обикновено не трябва да се налага да изпълнявате REPAIR TABLE, ако се наложи, тази операция е много вероятно да възстанови всички данни от MyISAM таблица.
Ако MyISAM таблица често се поврежда, опитайте се да намерите причината за това, за да премахнете необходимостта от използване на REPAIR TABLE.
REPAIR TABLE работи с MyISAM, ARCHIVE и CSV таблици, не работи с InnoDB и views.
За MyISAM таблиците има същия ефект като myisamchk --recover tbl_name
по подразбиране.
REPAIR TABLE се поддържа за разделени (partitioned) таблици. Но опцията USE_FRM
не може да се използва с това изявление за разделена таблица.
Можете да използвате ALTER TABLE ... REPAIR PARTITION
за поправяне на един или повече дялове.
REPAIR TABLE опции
*NOWRITETO_BINLOG or LOCAL*
По подразбиране сървърът записва резултатите от REPAIR TABLE в бинарeн лог, така че да се възпроизвеждат на вторичните (slave) репликации. За да спрете записването, посочете ключовата дума NOWRITETO_BINLOG или псевдонимът LOCAL.
QUICK
Ако използвате опцията QUICK, REPAIR TABLE се опитва да поправи само индексния файл, а не файла с данни. Този вид ремонт е като този, направен от myisamchk --recover --quick
.
EXTENDED
Ако използвате опцията EXTENDED, MySQL създава индекса ред по ред, вместо да създава един индекс едновременно чрез сортиране. Този вид поправка е като този, направен от myisamchk --safe-recover
.
USE_FRM
Опцията USE_FRM е достъпна за използване, ако индексният файл .MYI
липсва или ако хедърът е повреден. Тази опция казва на MySQL да не се доверява на информацията в хедъра на файла .MYI
и да я създаде отново, като използва информацията от файла .frm
. Този вид repair не може да се направи с myisamchk
.
Изчисляване на контролна сума – CHECKSUM TABLE
CHECKSUM TABLE отчита контролна сума за съдържанието на таблицата. Можете да използвате това изявление (операция) за да се уверите, че съдържанието е същото преди и след архивиране, връщане на транзакция (rollback) или друга операция, предназначена да възстанови данните до работещо състояние.
За несъществуваща таблица CHECKSUM TABLE връща NULL и генерира предупреждение. По време на контролната операция, таблицата е заключена за четене (read lock) за InnoDB и MyISAM.
В InnoDB се използва механизъм за установяване на корупция, когато една страница в табличното пространство се чете от диска в буферния пакет InnoDB.
Тази функция се контролира от опцията за конфигуриране innodb_checksums
, която в MySQL 5.6.3
, е заменена от innodb_checksum_algorithm
.
Командата innochecksum
помага да се диагностицират корупционните проблеми, като се тестват стойностите на контролната сума за конкретен файл за таблица, докато MySQL сървърът е изключен.
Съображения за ефективност и производителност
По подразбиране цялата таблица се чете ред по ред и се изчислява контролната сума. За големи таблици това може да отнеме много време, поради което тази операция трябва да се изпълнява периодично.
Това изчисление ред по ред се получава с клаузата EXTENDED с InnoDB и всички останали хранилища на данни, различни от MyISAM, и с MyISAM таблици, които не са създадени с клаузата CHECKSUM = 1
:
За MyISAM таблици, създадени с клаузата CHECKSUM = 1
, CHECKSUM TABLE
или CHECKSUM TABLE ... QUICK
връща контролната сума live
, която може да се върне много бързо. Ако таблицата не отговаря на всички тези условия, методът QUICK връща NULL.
Методът QUICK не се поддържа с таблици InnoDB.
Стойността на контролната сума зависи от формата на реда в таблицата. Ако форматът на реда се промени, контролната сума също се променя. Например, форматът за съхранение на временни типове, като TIME, DATETIME и TIMESTAMP, е променен в MySQL 5.6, така че ако версия 5.5 таблицата се ъпгрейдне към MySQL 5.6, стойността на контролната сума ще се промени.