Типы таблиц MySQL

Использовались по-умолчанию в MySQL вплоть до выхода в свет версии 5.5.

Каждая MyISAM таблица хранится на диске в трёх файлах (в поддиректории каталога mysql/data), имена этих файлов совпадают с названием таблицы, а расширение может принимать одно из следующих значений:
.frm — структура таблицы, этот файл содержит информацию об именах и типах столбцов и индексов;
.MYD — в этом файле содержатся данные таблицы;
.MYI — в этом файле содержатся индексы таблицы.

• Таблицы MyISAM обладают рядом особенностей:
• Данные хранятся в кроссплатформенном формате, что позволяет переносить базы с сервера непосредственным копированием файлов, минуя промежуточные формы.
• Максимальное количество индексов — 64 (с версии 4.1.2). Каждый индекс может быть максимум из 16 столбцов.
• С версии MySQL 4.1, для каждого текстового столбца может быть задана своя кодировка.
• Допускается индексирование текстовых столбцов, в том числе и переменной длины.
• Поддерживается полнотекстовый поиск.
• Таблицы имеют специальный флаг, указывающий на правильность закрытия таблицы. Если сервер был остановлен аварийно, то при загрузке незакрытые флаги будут сигнализировать о возможных поврежденных таблицах и сервер попытается автоматически их проверить и восстановить.
• Таблицы MyISAM можно проверять/восстанавливать при помощи утилиты myisamchk.
• Таблицы MyISAM можно сжимать при помощи команды myisampack.

В MyISAM поддерживается три различных типа таблиц. Два из них выбираются автоматически, в зависимости от типа используемых столбцов. Третий - сжатые таблицы - может быть создан только при помощи инструмента myisampack.

Это формат, принятый по умолчанию. Он используется, когда таблица не содержит столбцов VARCHAR, BLOB или TEXT. Данный формат - самый простой и безопасный, а также наиболее быстрый при работе с дисками. Скорость достигается за счет простоты поиска информации на диске: в таблицах статического формата с индексом для этого достаточно всего лишь умножить номер строки на ее длину. Кроме того, при сканировании таблицы очень просто считывать постоянное количество записей при каждом чтении с диска. Если произойдет сбой во время записи в файл MyISAM фиксированного размера, myisamchk в любом случае сможет легко определить, где начинается и заканчивается любая строка. Поэтому обычно удается восстановить все записи, кроме тех, которые были частично перезаписаны.

Формат используется для таблиц, которые содержат столбцы VARCHAR, BLOB или TEXT, а также если таблица была создана с параметром ROW_FORMAT=dynamic. Это несколько более сложный формат, так как у каждой строки есть заголовок, в котором указана ее длина. Одна запись может заканчиваться более чем в одном месте, если она была увеличена во время обновления. Чтобы произвести дефрагментацию таблицы, можно воспользоваться командами OPTIMIZE table или myisamchk. Если у вас есть статические данные, которые часто считываются/изменяются в некоторых столбцах VARCHAR или BLOB одной и той же таблицы, во избежание фрагментации эти динамические столбцы лучше переместить в другие таблицы.

Таблицы этого тип предназначены только для чтения. Они генерируются при помощи дополнительного инструмента myisampack. Несмотря на то, что формат таблиц MyISAM очень надежен (все изменения в таблице записываются до возвращения значения оператора SQL), таблица, тем не менее, может быть повреждена. Такое происходит в следующих случаях:

• Процесс mysqld уничтожен во время осуществления записи;
• Неожиданное отключение компьютера (например, если выключилось электропитание);
• Ошибка аппаратного обеспечения;
• Использование внешней программы (например myisamchk) на открытой таблице.
• Ошибка программного обеспечения в коде MySQL или MyISAM.

С MySQL 5.5 является типом по-умолчанию. Тип таблиц InnoDB разработан компанией Innobase. Таблицы такого типа предоставляют высокую производительность и устойчивое хранение данных в таблицах объёмом до 1 Тбайт и нагрузкой на сервер до 800 вставок/обновлений в секунду. Особенности типа InnoDB:

• Все таблицы хранятся в едином табличном пространстве, поэтому имена таблиц должны быть уникальны.
• Хранение данных в едином табличном пространстве позволяет снять ограничение на объём таблиц. Файл с таблицами может быть разбит на несколько частей и распределён по нескольким дискам или даже хостам.
• Таблицы поддерживают автоматическое восстановление после сбоя.
• Поддерживаются транзакции.
• Этот тип таблиц в MySQL единственный, который поддерживает каскадное удаление и внешние ключи.
• Выполняется блокировка на уровне отдельных записей.
• Имеется расширенная поддержка кодировок.

В MySQL существует тип таблиц MERGE (или таблица MRG_MyISAM), который представляет собой совокупность идентичных таблиц MyISAM, которые могут использоваться как одна таблица. К совокупности таблиц можно применять только команды SELECT, DELETE и UPDATE. Если же попытаться применить к таблице MERGE команду DROP, она подействует только на определение MERGE.

Следующий тип таблиц MEMORY (HEAP) хранится в оперативной памяти, из-за чего все запросы к таким таблицам выполняются очень быстро. Недостаток у таких таблиц один — полная потеря данных в случае сбоя работы сервера. В связи с этим в таких таблицах хранят в основном временные данные, которые можно легко восстановить заново. При создании таблицы типа MEMORY, создаётся один файл с расширением frm, в котором определяется структура таблицы. При остановке или перезагрузке сервера, данные о структуре таблицы остаются, но вся информация содержащаяся в этой таблице теряется, поскольку хранится только в оперативной памяти. При каждой перезагрузке сервера, пересоздавать таблицу не нужно, её структура остаётся. Таблицы типа MEMORY имеют ряд ограничений:

• Индексы используются только в операциях сравнения с операторами «=» и «⇔», с другими операторами, такими как «>» или «<», индексирование столбцов не имеет смысла.
• Как и с MERGE таблицами возможно использование только неуникальных индексов.
• Не допустимы столбцы типов TEXT и BLOB.
• До версии MySQL 4.1 в таблицах данного типа не поддерживаляс AUTO_INCREMENT.

Тип EXAMPLE является заглушкой, создать таблицу такого типа можно а вот получить или передать данные нельзя. При создании таблиц такого типа, как и с таблицами MEMORY создаётся только один файл frm, в нём определяется структура таблицы.

Таблицы BDB обслуживаются транзакционным обработчиком BerkeleyDB, который разработан компанией Sleepycat. При создании таблиц этого типа формируются два файла: .frm - хранится структура таблицы. .db - хранятся данные и индексы.

Особенности таблиц BDB:

• Для таблиц ведётся журнал, что позволяет повысить устойчивость базы и увеличить вероятность успешного восстановления в случае сбоя.
• Таблицы типа BDB хранятся в виде бинарных деревьев. Этот метод хранения замедляет сканирование таблицы (допустим для выборки всех строк таблицы) и увеличивает занимаемое таблицей место на диске. Однако поиск отдельных значений в таблице становится быстрее. Все остальные таблицы хранят в виде бинарных деревьев свои индексы.
• Все таблицы BDB должны иметь первичный ключ, при отсутствии создаётся скрытый первичный ключ с атрибутом AUTO_INCREMENT.
• Для данного типа таблиц поддерживаются транзакции на уровне страниц.
• Подсчёт количества строк в таблице при помощи функции COUNT() происходит медленнее, из-за того что подсчёт строк для таблиц BDB (в отличие от MyISAM) на стороне сервера не поддерживается и полный пересчёт происходит при каждом обращении.
• Ключи не являются упакованными, как в таблицах MyISAM и занимают больше места.
• В случае, когда таблица BDB занимает всё свободное место на диске, происходит откат транзакции и вывод сообщения об ошибке. В отличие от BDB, таблицы MyISAM просто будут ждать появления свободного места, что приведёт к зависанию сервера.
• Файлы таблиц BDB нельзя переносить между системами простым копированием, поскольку при их создании путь к файлу таблицы сохраняется. Для переноса базы необходимо использовать утилиту mysqldump.