Mysql - огляд типів таблиць

Привіт, сьогодні ми поговоримо про типи таблиць в MySQL. Архітектура MySQL дозволяє підключати різні движки таблиць. На даний момент MySQL підтримує безліч різних типів таблиць, кожен з яких має свої переваги і недоліки. Я перерахую і коротко опишу основні типи таблиць, а потім проведу невеличкий тест продуктивності найбільш часто використовуваних типів - myisam і innodb.

Для того щоб подивитися які типи підтримує ваша інсталяція MySQL необхідно виконати наступний SQL запит:

В результаті ви отримуєте таблицю яка містить інформацію про те які типи таблиць встановлено у вашій системі і короткий опис їх можливостей.

Federated MySQL storage engine

MyISAM storage engine

/ Dev / null storage engine (anything you write to it disappears)

Collection of identical MyISAM tables

Hash based, stored in memory, useful for temporary tables

Archive storage engine

Supports transactions, row-level locking, and foreign keys

Нас в основному буде цікавити стовпець support, який містить інформацію про підтримку типу таблиці і може приймати значення: NO - не підтримується, YES - підтримується, DEFAULT -використовується за замовчуванням. Починаючи з версії 5.5.5 за замовчуванням обраний тип innodb, раніше стандартним типом був myisam.

У версії MySQL 5.5 підтримується 9 різних типів таблиць.

• InnoDB - движок з підтримкою транзакцій, відкатів і захистом від втрати даних. В даному типі таблиць використовуються блокування на рівні запису і не блокуючий читання, що дозволило поліпшити продуктивність при многопользовательском режимі роботи. InnoDB зберігає призначені для користувача дані в кластерних індексах, що дозволяє компенсувати в / в для простих запитів заснованих на первинних ключах.
• MyISAM - движок таблиць MySQL використовується в основному в Web-додатках, сховищах даних і інших програмних середовищах. Даний тип таблиць підтримується всіма інсталяціями MySQL.
• Memory - зберігає дані в оперативній пам'яті для дуже швидкого доступу. Також відомий як HEAP (купа).
• Merge - використовується для логічного об'єднання однакових MyISAM таблиць і звернення до них, як до єдиного об'єкту. Добре підійде для дуже великих сховищ даних.
• Archive - ідеальне рішення для зберігання великих обсягів інформації, до якої не потрібно частий доступ.
• Federated - надає можливість об'єднувати різні MySQL сервера для створення однієї логічної бази даних з декількох фізичних машин. Ідеально підійде для архітектур, які підтримують розподілене зберігання даних.
• CSV - зберігає призначені для користувача дані в текстових файлах розділяючи значення комами. Використовується якщо необхідний простий обмін з додатками, які вміють експортувати / імпортувати дані з CSV формату.
• Blackhole - приймає, але не повертає ніяких даних. Результатами будь-яких запитів з таких сховищ будуть порожні вибірки.
• Example - тестовий движок, не виконує ніяких функцій, буде корисний тільки розробникам, які збираються писати свій движок, як приклад.

Порівняльна таблиця основних типів таблиць

Максимальний обсяг збережених даних

Індекси повнотекстового пошуку

Підтримка кластерних БД

Тестування продуктивність InnoDB і MyIASM

Найбільший інтерес для web-розробника складають innodb і myisam. Зараз ми проведемо порівняльний тест продуктивності цих типів таблиць. Для цього спочатку створимо дві однакові за структурою таблиці, але з різним типом движка зберігання:

Напишемо невеликий скрипт який буде виконувати 3 тесту: запис даних (insert), вибірка по ключу, вибірка по які ключовому полю.

Для того щоб виконати тест, потрібно раскоментіть один відповідний блок коду. І власне, то що у мене вийшло в результаті тестування:

Вибірка по ключу

Вибірка по які ключовому полю

Як ми бачимо myisam працює значно швидше, особливо це помітно при вставці даних. Хоча innodb і дає ряд нових можливостей і переваг, така повільність не дозволяє йому конкурувати з myisam, особливо в web-додатках.