База даних MySQL підтримує кілька різних видів таблиць, всі вони мають свої особливі можливості і обмеження.
Почну свій опис з «рідного» для баз даних MySQL типу таблиць MyISAM. Якщо в операторі create table не поставити тип таблиці то буде призначений саме цей тип.
Кожна MyISAM таблиця зберігається на диску в трьох файлах (в піддиректорії каталогу mysql / data), імена цих файлів збігаються з назвою таблиці, а розширення може приймати одне з наступних значень:
.frm - структура таблиці, цей файл містить інформацію про імена і типах стовпців і індексів;
.MYD - в цьому файлі містяться дані таблиці;
.MYI - в цьому файлі містяться індекси таблиці.
Створення MyISAM таблиці з явним зазначенням типу:
Використання ключового слова ENGINE є кращим, проте можливо тільки з версії MySQL 4.0.18.
В MySQL існує тип таблиць MERGE. він дозволяє об'єднувати декілька таблиць типу MyISAM в одну. Цей тип в основному застосовується для зняття обмеження на обсяг для таблиць MyISAM. в тому випадку якщо операційна або файлову систему не дозволяють створювати файли більш певного розміру (наприклад 4gb для FAT32 і т.п.). Таблиці MyISAM об'єднуються в одну таблицю MERGE. повинні мати ідентичну структуру, тобто однакові стовпці, індекси і порядок їх проходження.
Для створеної таблиці типу MERGE з'являться файли структури таблиці з розширенням .rfm і файли з розширенням .MRG. Файли .MRG містять список індексних файлів, робота з якими повинна вестися як з єдиним файлом.
До об'єднаної таблиці можна застосовувати команди SELECT, DELETE і UPDATE. Якщо спробувати видалити таблицю MERGE за допомогою команди DROP TABLE, то буде знищена саме MERGE таблиця, вихідні таблиці MyISAM НЕ будуть порушені.
Приклад створення таблиці MERGE:
Результат вибірки з таблиці summ_table виглядає так:
В об'єднаній таблиці summ_table поле id вже не є унікальним!
(Таблиці MERGE не можуть містити унікальних ключів.)
З прикладу видно, що за допомогою параметра UNION задаються таблиці для об'єднання, а за допомогою параметра INSERT_METHOD можна задати параметр додавання записів в MyISAM таблиці (як значення в цьому випадку передається одна з трьох констант).
FIRST - При вставці нового запису в таблицю MERGE. запис розміщується в першій таблиці зі списку в параметрі UNION.
LAST - Запис розміщується в останній таблиці списку.
NO - Дане значення по суті ідентично відсутності параметра, і означає що вставка в MERGE таблицю неможлива, а використання оператора INSERT призведе до помилки.
Наступний тип таблиць MEMORY (HEAP) зберігається в оперативній пам'яті, через що всі запити до таких таблиць виконуються дуже швидко. Недолік у таких таблиць один - повна втрата даних у разі збою роботи сервера. У зв'язку з цим в таких таблицях зберігають в основному тимчасові дані, які можна легко відновити заново.
HEAP є синонімом MEMORY. Однак краще використовувати MEMORY - який є більш пізнім.
При створенні таблиці типу MEMORY. створюється один файл з розширенням frm, в якому визначається структура таблиці. При зупинці або перезавантаження сервера, дані про структуру таблиці залишаються, але вся інформація міститься в цій таблиці втрачається, оскільки зберігається тільки в оперативній пам'яті. При кожному перезавантаженні сервера, пересоздавать таблицю не потрібно, її структура залишається.
Таблиці типу MEMORY мають ряд обмежень:
1. Індекси використовуються тільки в операціях порівняння з операторами "=" і "<=>", З іншими операторами, такими як"> "або"<", индексирование столбцов не имеет смысла.
2. Як і з MERGE таблицями можливе використання тільки неунікальний індексів.
3. Не допустимі стовпці типів TEXT і BLOB.
4. До версії MySQL 4.1 в таблицях даного типу не поддержіваляс AUTO_INCREMENT.
Приклад створення таблиць типу MEMORY:
Тип EXAMPLE є заглушкою, створити таблицю такого типу можна а ось отримати або передати дані не можна. При створенні таблиць такого типу, як і з таблицями MEMORY створюється тільки один файл frm, в ньому визначається структура таблиці.
Тип EXAMPLE був введений для зручності і даемонстрірует, яким чином слід створювати свої таблиці. На мій особистий погляд - самої сумнівної корисності тип. )
Приклад створення таблиці і спроби модифікації даних:
Таблиці BDB обслуговуються транзакційних оброблювачем BerkeleyDB, який розроблений компанією Sleepycat. При створенні таблиць цього типу формуються два файли. Один з розширенням frm і в ньому зберігається структура таблиці, інший з розширенням db в ньому зберігаються дані і індекси.
Приклад створення таблиці BDB:
Тип таблиць InnoDB розроблений компанією Innobase Oy. Таблиці такого типу надають високу продуктивність і стійке зберігання даних в таблицях об'ємом до 1 Тбайт і навантаженням на сервер до 800 вставок / оновлень в секунду.
Особливості типу InnoDB:
1. Всі таблиці зберігаються в єдиному табличному просторі, тому імена таблиць повинні бути унікальні.
2. Зберігання даних в єдиному табличному просторі дозволяє зняти обмеження на обсяг таблиць. Файл з таблицями може бути розбитий на кілька частин і розподілений по декількох дисках або навіть хостів.
3. Таблиці підтримують автоматичне відновлення після збою.
4. Підтримуються транзакції.
5. Цей тип таблиць в MySQL єдиний, який підтримує каскадне видалення і зовнішні ключі.
6. Виконується блокування на рівні окремих записів.
7. Є розширена підтримка кодувань.
Приклад створення таблиці InnoDB:
«Екзотичні типи таблиць» - до цих типів я відношу ті, якими сам ніколи не користувався і вони мною, мало вивчені - але тим не менше вони існують і варто про них згадати.
NDB Cluster
Тип таблиць NDB Cluster призначений для організації кластерів MySQL, в разі коли таблиці розподілені між кількома комп'ютерами, об'єднаними в мережу.
ARCHIVE
Тип ARCHIVE призначений для зберігання великого обсягу даних в стислому форматі. При створенні таких таблиць, створюється файл збігається з ім'ям таблиці і з розширенням frm, в якому зберігається структура таблиці. Крім цього, створюються два файли з розширеннями ARZ і ARM, в яких зберігаються дані і мета-дані відповідно. Крім того, при оптимізації таблиці може з'явитися файл з розширенням ARN.
ARCHIVE таблиці підтримують тільки два SQL оператора: INSERT і SELECT. Оператор SELECT виконується методом повного сканування таблиці.
FEDERATED
Тип FEDERATED дозволяє зберігати дані на віддалених таблицях, які знаходяться на інших машинах в мережі. При створенні таблиці створюється тільки файл структури з розширенням frm, оскільки дані зберігаються на віддаленій машині.
Для створення таблиці FEDERATED необхідно спочатку створити таблицю на віддаленій машині, потім на робочій машині із зазначенням на віддалену.
Приклад створення таблиці FEDERATED:
Ось мабуть і все.