Операции за поддръжка на MySQL таблици

Във всяка система за управление на релационни бази данни е наложително периодично да се извършват операции по поддръжка.

Операциите за поддръжка в MySQL (в официалната документация се наричат изявления – statements) се разделят на четири основни категории: проверка, анализ, оптимизация и поправяне на таблици.

Всяка от тези операции прави нещо малко по-различно и ние ще разгледаме главните особености на всяка от тези четири операции поотделно.

Ще забележите, че три от четирите работят с двете най-използвани хранилища за данни в MySQLMyISAM и InnoDB (основно хранилище по подразбиране).

Единствената операция, която не работи с InnoDB, е изявлението за поправяне – table repair, което в абсолютно никакъв случай не е недостатък в InnoDB, както сами ще се убедите в статията.

Бележка: В официалната документация на MySQL се използват главни букви и само поради тази причина ние спазваме този тип синтаксис.

Поддръжката и оптимизацията на таблиците в базата данни може да се извършва през 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 връща резултата от операцията по начин, показан в следващите таблици:

ANALYZE TABLE връща резултата от операцията по начин
ANALYZE TABLE връща резултата от операцията по начин

MySQL използва запазеното разпределение на ключове (stored key distribution), за да определи реда за присъединяване (join) на таблицата към нещо различно от константа. В допълнение, ключовите разпределения могат да се използват, когато се решава кои индекси да се използват за конкретна таблица в рамките на дадена заявка.

Ако системната променлива *innodbstatspersistent* е активирана, ANALYZE TABLE трябва да се изпълнява след зареждане на значителни данни в InnoDB таблица или за създаване на нов индекс.

За да проверите кардиналното разпределение на запаметените ключове, използвайте инструкцията show index или таблицата information_schema.statistics:

използвайте инструкцията show index

Проверка на таблица – 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 връща резултата от операцията по начин, показан в следващите таблици:

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

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 Детайли

За MyISAM таблици OPTIMIZE TABLE работи по следния начин:

  • Ако таблицата е изтрила или разделила редове, поправете (repair) таблицата;
  • Ако индексните страници не са сортирани, ги сортирайте (sort);
  • Ако статистическите данни на таблицата не са актуални (и поправянето не може да бъде извършено чрез сортиране на индекса), актуализирайте ги (update).

Поправяне на таблица – REPAIR TABLE

REPAIR TABLE поправя евентуално повредена таблица, само за определени хранилища за данни (MyISAM ):

REPAIR TABLE

Въпреки че обикновено не трябва да се налага да изпълнявате REPAIR TABLE, ако се наложи, тази операция е много вероятно да възстанови всички данни от MyISAM таблица.

Ако MyISAM таблица често се поврежда, опитайте се да намерите причината за това, за да премахнете необходимостта от използване на REPAIR TABLE.

REPAIR TABLE работи с MyISAM, ARCHIVE и CSV таблици, не работи с InnoDB и views.

REPAIR TABLE

За 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:

Това изчисление ред по ред се получава с клаузата EXTENDED с InnoDB

За MyISAM таблици, създадени с клаузата CHECKSUM = 1, CHECKSUM TABLE или CHECKSUM TABLE ... QUICK връща контролната сума live, която може да се върне много бързо. Ако таблицата не отговаря на всички тези условия, методът QUICK връща NULL.

методът QUICK връща NULL

Методът QUICK не се поддържа с таблици InnoDB.

Стойността на контролната сума зависи от формата на реда в таблицата. Ако форматът на реда се промени, контролната сума също се променя. Например, форматът за съхранение на временни типове, като TIME, DATETIME и TIMESTAMP, е променен в MySQL 5.6, така че ако версия 5.5 таблицата се ъпгрейдне към MySQL 5.6, стойността на контролната сума ще се промени.

Важно: Ако контролните суми за две таблици са различни, тогава е почти сигурно, че таблиците са по някакъв начин различни. Тъй като обаче функцията за хеширане, използвана от CHECKSUM TABLE, не гарантира пълна автентичност, има изключително малка, но все пак вероятност две таблици, които не са еднакви, да генерират една и съща контролна сума.

Обновена: 29.09.2021

Беше ли Ви полезна тази статия?

Вижте още