Типы таблиц MySQL

В  Сети миллионы сайтов осуществляют запросы и сохраняют данные в БД с помощью MySQL. MySQL дает нам простор для выбора типа таблиц при хранении данных. Так что же выбрать? Попробуем рассмотреть варианты хранения данных в MySQL.

MySQL поддерживает два различных типа таблиц: транзакционные (InnoDB и BDB) и без поддержки транзакций (HEAP(он же MEMORY), MERGE и MyISAM, CSV). Основное их отличие в надежности.

 

1е (транзакционные таблиц (Transaction-safe tables, TST)) позволяют восстановить данные в случае при помощи резервных копий и журналов транзакций, плюс они лучше обеспечивают параллельность при одновременном обновлении и чтении таблицы. К ним применимы команды COMMIT и ROLLBACK.

2е (нетранзакционные таблиц (non-transaction-safe tables, NTST)) работают быстрее, для них требуется меньше дискового пространства. Но ценой меньшей надежности.

Преобразовывать таблицы из одного типа в другой можно при помощи оператора ALTER TABLE. По умолчанию MySQL создаст таблицу типа MyISAM(начиная с 5.5-InnoDB), его и рассмотрим подробнее.

Таблицы MyISAM

Использовались по-умолчанию в MySQL вплоть до выхода в свет версии 5.5. Индекс хранится в файле с расширением `.MYI' (MYIndex), а данные - в файле с расширением `.MYD' (MYData). Таблицы MyISAM можно проверять/восстанавливать при помощи утилиты myisamchk. Таблицы MyISAM можно сжимать при помощи команды myisampack, после чего они будут занимать намного меньше места.
В MyISAM поддерживается три различных типа таблиц. Два из них выбираются автоматически, в зависимости от типа используемых столбцов. Третий - сжатые таблицы - может быть создан только при помощи инструмента myisampack.
1.Cтатические таблицы (с фиксированной длиной)
Это формат, принятый по умолчанию. Он используется, когда таблица не содержит столбцов VARCHAR, BLOB или TEXT.
Данный формат - самый простой и безопасный, а также наиболее быстрый при работе с дисками. Скорость достигается за счет простоты поиска информации на диске: в таблицах статического формата с индексом для этого достаточно всего лишь умножить номер строки на ее длину.
Кроме того, при сканировании таблицы очень просто считывать постоянное количество записей при каждом чтении с диска.
Если произойдет сбой во время записи в файл MyISAM фиксированного размера, myisamchk в любом случае сможет легко определить, где начинается и заканчивается любая строка. Поэтому обычно удается восстановить все записи, кроме тех, которые были частично перезаписаны.
2.Динамические таблицы
Формат используется для таблиц, которые содержат столбцы VARCHAR, BLOB или TEXT, а также если таблица была создана с параметром ROW_FORMAT=dynamic.
Это несколько более сложный формат, так как у каждой строки есть заголовок, в котором указана ее длина. Одна запись может заканчиваться более чем в одном месте, если она была увеличена во время обновления.
Чтобы произвести дефрагментацию таблицы, можно воспользоваться командами OPTIMIZE table или myisamchk. Если у вас есть статические данные, которые часто считываются/изменяются в некоторых столбцах VARCHAR или BLOB одной и той же таблицы, во избежание фрагментации эти динамические столбцы лучше переместить в другие таблицы.
3.Сжатые таблицы
Таблицы этого тип предназначены только для чтения. Они генерируются при помощи дополнительного инструмента myisampack.
Несмотря на то, что формат таблиц MyISAM очень надежен (все изменения в таблице записываются до возвращения значения оператора SQL), таблица, тем не менее, может быть повреждена. Такое происходит в следующих случаях:
*Процесс mysqld уничтожен во время осуществления записи;
*Неожиданное отключение компьютера (например, если выключилось электропитание);
*Ошибка аппаратного обеспечения;
*Использование внешней программы (например myisamchk) на открытой таблице.
*Ошибка программного обеспечения в коде MySQL или MyISAM.

MERGE

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

MEMORY

Следующий тип таблиц MEMORY (HEAP) хранится в оперативной памяти, из-за чего все запросы к таким таблицам выполняются очень быстро. Недостаток у таких таблиц один — полная потеря данных в случае сбоя работы сервера. В связи с этим в таких таблицах хранят в основном временные данные, которые можно легко восстановить заново.
При создании таблицы типа MEMORY, создаётся один файл с расширением frm, в котором определяется структура таблицы. При остановке или перезагрузке сервера, данные о структуре таблицы остаются, но вся информация содержащаяся в этой таблице теряется, поскольку хранится только в оперативной памяти. При каждой перезагрузке сервера, пересоздавать таблицу не нужно, её структура остаётся.
Таблицы типа MEMORY имеют ряд ограничений:
1. Индексы используются только в операциях сравнения с операторами "=" и "<=>", с другими операторами, такими как ">" или "<", индексирование столбцов не имеет смысла.
2. Как и с MERGE таблицами возможно использование только неуникальных индексов.
3. Не допустимы столбцы типов TEXT и BLOB.
4. До версии MySQL 4.1 в таблицах данного типа не поддерживаляс AUTO_INCREMENT.
Пример создания таблиц типа MEMORY:
create table tbl1 (field1 INT) ENGINE=MEMORY;
create table tbl1 (field1 INT) ENGINE=HEAP;

InnoDB

С MySQL 5.5 является типом по-умолчанию. Тип таблиц InnoDB разработан компанией Innobase. Таблицы такого типа предоставляют высокую производительность и устойчивое хранение данных в таблицах объёмом до 1 Тбайт и нагрузкой на сервер до 800 вставок/обновлений в секунду.
Особенности типа InnoDB:
1. Все таблицы хранятся в едином табличном пространстве, поэтому имена таблиц должны быть уникальны.
2. Хранение данных в едином табличном пространстве позволяет снять ограничение на объём таблиц. Файл с таблицами может быть разбит на несколько частей и распределён по нескольким дискам или даже хостам.
3. Таблицы поддерживают автоматическое восстановление после сбоя.
4. Поддерживаются транзакции.
5. Этот тип таблиц в MySQL единственный, который поддерживает каскадное удаление и внешние ключи.
6. Выполняется блокировка на уровне отдельных записей.
7. Имеется расширенная поддержка кодировок.
Пример создания таблицы InnoDB:
create table tab1 (field1 INT, field2 CHAR(10), INDEX (field1)) ENGINE=InnoDB;

BDB (BerkeleyDB)

Таблицы BDB обслуживаются транзакционным обработчиком BerkeleyDB, который разработан компанией Sleepycat. При создании таблиц этого типа формируются два файла. Один с расширением frm и в нём хранится структура таблицы, другой с расширением db в нём хранятся данные и индексы.
Особенности таблиц BDB:
1. Для таблиц ведётся журнал, что позволяет повысить устойчивость базы и увеличить вероятность успешного восстановления в случае сбоя.
2. Таблицы типа BDB хранятся в виде бинарных деревьев. Этот метод хранения замедляет сканирование таблицы (допустим для выборки всех строк таблицы) и увеличивает занимаемое таблицей место на диске. Однако поиск отдельных значений в таблице становится быстрее. Все остальные таблицы хранят в виде бинарных деревьев свои индексы.
3. Все таблицы BDB должны иметь первичный ключ, при отсутствии создаётся скрытый первичный ключ с атрибутом AUTO_INCREMENT.
4. Для данного типа таблиц поддерживаются транзакции на уровне страниц.
5. Подсчёт количества строк в таблице при помощи функции COUNT() происходит медленнее, из-за того что подсчёт строк для таблиц BDB (в отличие от MyISAM) на стороне сервера не поддерживается и полный пересчёт происходит при каждом обращении.
6. Ключи не являются упакованными, как в таблицах MyISAM и занимают больше места.
7. В случае, когда таблица BDB занимает всё свободное место на диске, происходит откат транзакции и вывод сообщения об ошибке. В отличие от BDB, таблицы MyISAM просто будут ждать появления свободного места, что приведёт к зависанию сервера.
8. Файлы таблиц BDB нельзя переносить между системами простым копированием, поскольку при их создании путь к файлу таблицы сохраняется. Для переноса базы необходимо использовать утилиту mysqldump.
Пример создания таблицы BDB:
create table tab1 (field1 INT) engine=BDB;

CSV

Формат CSV, представляет собой обычный текстовый файл, записи в котором хранятся в строках, а поля разделены точкой с запятой. При создании таблицы формируются два файла. Один с расширением frm, содержащим структуру таблицы. Второй с расширением CSV — содержащим данные в CSV — формате. (Также, данные в CSV формате можно читать другими программами, например при помощи Excel).

 

Ну вот, в кратце, и все. Что выбирать - каждый решает сам с учетом структуры данных, планируемой нагрузки и необходимой отказоустойчивости.  В последнее время после известных событий, затронувших MySQL, бурно развиваются и альтернативные типы, но о них в другой раз.

До встречи на страницах моего блога.