Главная > MySQL > MySQL Storage engines

MySQL Storage engines

27 марта 2009
К комментариям Написать комментарий

Ликбез по движкам MySQL
На сегодняшний день MySQL поддерживает несколько движков. Наиболее популярные это MyISAM, InnoDB, но рассмотрим и другие. Получить список поддерживаемых движков вашего сервера можно командой SHOW ENGINES

mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| MyISAM     | YES     | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      |
| HEAP       | YES     | Alias for MEMORY                                               |
| MERGE      | YES     | Collection of identical MyISAM tables                          |
| MRG_MYISAM | YES     | Alias for MERGE                                                |
| ISAM       | NO      | Obsolete storage engine, now replaced by MyISAM                |
| MRG_ISAM   | NO      | Obsolete storage engine, now replaced by MERGE                 |
| InnoDB     | DEFAULT | Supports transactions, row-level locking, and foreign keys     |
| INNOBASE   | YES     | Alias for INNODB                                               |
| BDB        | YES     | Supports transactions and page-level locking                   |
| BERKELEYDB | YES     | Alias for BDB                                                  |
| NDBCLUSTER | NO      | Clustered, fault-tolerant, memory-based tables                 |
| NDB        | NO      | Alias for NDBCLUSTER                                           |
| EXAMPLE    | NO      | Example storage engine                                         |
| ARCHIVE    | YES     | Archive storage engine                                         |
| CSV        | NO      | CSV storage engine                                             |
| FEDERATED  | YES     | Federated MySQL storage engine                                 |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) |
+------------+---------+----------------------------------------------------------------+

MyISAM Storage engine
InnoDB Storage engine
Archive Storage engine
Memory Storage engine
Merge Storage engine
BDB Storage engine
NDB Storage engine
FEDERATED Storage engine
BLACKHOLE Storage engine
Falcon Storage engine
Maria Storage engine


MyISAM
Подробней о MyISAM
Движок по умолчанию. Не поддерживает транзакций, средняя надежность хранения данных. Превосходная производительность чтения данных (через SELECT). Блокирует всю таблицу при записи в неё данных, отчего маленькая производительность при частых записях. Распространённый движок.
В версии MySQL 6.* MyISAM планируется заменить на движок Maria.
Maria – это расширенная версия MyISAM, которая поддерживает весь основной функционал MyISAM и в дополнение к этому предлагает: поддержку восстановления данных после сбоев (data auto-recovery, crash safe), полное логирование (включая операции CREATE, DROP, RENAME и TRUNCATE) и новый формат строк PAGE. Более подробно тут.
Примечания:

  • Таблицы могут быть повреждены. Ежедневно архивируйте данные или установите еще один MySQL сервер для выполнения репликаций.
  • Включите авто-восстановление (auto-repair) в настройках Вашего сервера (my.cnf):
    myisam-recover=backup,force
    или рассмотрите возможность выполнения ежедневной автоматической проверки таблиц баз данных.
  • Конкурирующие записи полностью блокируют таблицы. Переключите все, что возможно, на оффлайн обработку записей сериями, что-бы не загружать движок сервера баз данных. (Оффлайн обработка – золотое правило, применимое для всех типов таблиц)
  • Таблица MyISAM может быть сжата стандартой утилитой myisampack. Сжатые таблицы MyISAM поддерживают только чтение.Объём не сжатой таблицы: 
    +--------------+---------+---------------+------------+
| table_name   | engine  | total_size_mb | table_rows |
+--------------+---------+---------------+------------+
| test_myisam  | MyISAM  |          6.46 |     112050 |
+--------------+---------+---------------+------------+

    Объём сжатой таблицы:

    C:\Program Files\MySQL\MySQL Server 5.0\data\gim>..\..\bin\myisampack test_myisam.MYI
Compressing test_myisam.MYD: (112050 records)
- Calculating statistics
- Compressing file
67.76%
+--------------+---------+---------------+------------+
| table_name   | engine  | total_size_mb | table_rows |
+--------------+---------+---------------+------------+
| test_myisam  | MyISAM  |          2.08 |     112050 |
+--------------+---------+---------------+------------+


InnoDB
Подробней о InnoDB
Транзакционный тип движка, применяемый при интенсивных операциях записи, благодаря возможности блокировки уровня строк таблицы. Великолепная восстанавливаемость и высокая надежность хранения данных. Поддерживает внешние ключи. Движок InnoDB был приобретен Oracle в 2005 году (под GPL).
Примечания:

  • Поддержка ACID транзакций. Встроенная отказоустойчивость данных, равная надежности 99.999%. Блокировка уровня строк (сравните с полной блокировкой всей таблицы в MyISAM) означает обеспечение быстрой записи конкурирующих операций.
  • Выполнение «SELECT Count(*) FROM table» без индексов выполняется в InnoDB очень медленно и требует сканирования всей таблицы. (Для MyISAM эта операция ничего не стоит, потому что он хранит внешние записи счетчиков для каждой таблицы).
    Если Вам часто необходима операция «SELECT COUNT(*)» на таблицах InnoDB, создайте MySQL триггер на вставку/удаление, который будет увеличивать/уменьшать счетчик, когда данные добавляются или удаляются из таблицы.
  • Резервирование (бакапирование)
    Простое архивирование всех файлов таблиц для InnoDB невозможно.
    MySQLDump резервирует InnoDB очень медленно. (Если Вы настаиваете на таком резервировании, включите флаг: –opt –compress)
    Быстрое жизнеспособное резервирование, которое так-же может быть использовано как новая «ведомая» (slave) машина, это InnoDB Hot Backup.
  • Восстановление
    В InnoDB встроена поддержка восстановления, которая работает в 99% случаев автоматически. Никогда не трогайте .frm или .ibd файлы в надежде «помочь» восстановлению базы данных. Если встроенное восстановление не сработало, переключайтесь на «ведомый» сервер и восстанавливайте основной из архивов.
  • InnoDB меньше, чем MyISAM, прощает выполнение запросов построенных не на индексах. InnoDB отправит Вас в «школу», что-бы быть уверенным, что каждый запрос или обновление будет запущено на индексах. Выполните непроиндексированный запрос и Вы поплатитесь за это временем исполнения.
  • Никогда не изменяйте my.cnf InnoDB лог файл, когда запущен сервер баз данных. Вы разрушите последовательный лог-номер (log sequence number) оставшись без возможность восстановления.
  • Поддерживает внешние ключи.
  • Считается, что InnoDB медленнее чем MyISAM, кто так считает просто не умеет его(InnoDB) готовить. Дело в том, что движок InnoDB сильно повязан на оперативной памяти – чем больше оперативной памяти ему дашь, тем быстрее он будет работать, что не наблюдается у MyISAM. Стоит обратить внимание на параметр innodb_buffer_pool_size, по умолчанию он установлен 8 мегабайт, это ничтожно мало, как минимум рекомендуется 1 гигабайт. Подробней о настройках InnoDB можно найти на блоге Зайцева. Народ добивался скорости быстрее чем у MyISAM, выделив, в сумме на InnoDB 16 гигабайт


ARCHIVE
Подробней о ARCHIVE
Используется для хранения больших объемов данных.
Примечание:

  • Сильно сжимает информацию.
  • Не поддерживает операторы DELETE и UPDATE (только INSERT и SELECT).
  • Не поддерживает индексы (в ближайшем будущем обещают добавить поддержку индексов).
  • Обычно медленнее чем другие движки.

Тест драйв:
Для теста бралась таблица с чуть более 100 000 записями, Windows XP, laptop Pentium M 2.00 GHz processor, 1GB RAM, running MySQL 5.0.10 beta.
Тест на создание (тест CREATE):

mysql> CREATE TABLE test_myisam ENGINE=myisam AS SELECT * FROM client_transaction_hist;
Query OK, 112050 rows affected (1.06 sec)
Records: 112050  Duplicates: 0  Warnings: 0
 
mysql> CREATE TABLE test_innodb ENGINE=innodb AS SELECT * FROM client_transaction_hist;
Query OK, 112050 rows affected (3.72 sec)
Records: 112050  Duplicates: 0  Warnings: 0
 
mysql> CREATE TABLE test_archive ENGINE=archive AS SELECT * FROM client_transaction_hist;
Query OK, 112050 rows affected (1.92 sec)
Records: 112050  Duplicates: 0  Warnings: 0

Подсчитаем объёмы таблиц

mysql> SELECT  table_name table_name,
    ->         engine,
    ->         ROUND(data_length/1024/1024,2) total_size_mb,
    ->         table_rows
    -> FROM   information_schema.tables
    -> WHERE  table_schema = 'gim' and
    ->        table_name like 'test%'
    -> ORDER BY 3;
+--------------+---------+---------------+------------+
| table_name   | engine  | total_size_mb | table_rows |
+--------------+---------+---------------+------------+
| test_archive | ARCHIVE |          1.64 |     112050 |
| test_myisam  | MyISAM  |          6.46 |     112050 |
| test_innodb  | InnoDB  |          9.52 |     112050 |
+--------------+---------+---------------+------------+

Соответственно даже после сжатия MyISAM таблицы Archive будет показывать лучший вариант.

+--------------+---------+---------------+------------+
| table_name   | engine  | total_size_mb | table_rows |
+--------------+---------+---------------+------------+
| test_archive | ARCHIVE |          1.64 |     112050 |
| test_myisam  | MyISAM  |          2.08 |     112050 |
| test_innodb  | InnoDB  |          9.52 |     112050 |
+--------------+---------+---------------+------------+

Не всегда ARCHIVE медленный, чем больше строк тем лучший он может показать результат. На примере теста вставки (тест INSERT) 1 000 000 строк

mysql> CREATE TABLE insert_test (c1 int, c2 varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)
 
mysql> DROP PROCEDURE test_insert;
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter //
mysql> create procedure test_insert()
    -> begin
    -> declare v_ctr tinyint;
    -> set v_ctr = 0;
    -> while v_ctr < 1000000
    -> do
    ->     insert into insert_test values (1,'testing insert');
    ->     set v_ctr = v_ctr + 1;
    -> end while;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;
mysql> CALL test_insert();
Query OK, 1 row affected (33.06 sec)
 
mysql> TRUNCATE TABLE insert_test;
Query OK, 0 rows affected (0.01 sec)
 
mysql> ALTER TABLE insert_test engine=archive;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> CALL test_insert();
Query OK, 1 row affected (21.42 sec)

Archive показал результат, луче быстрого MyISAM’a.
Другой подход заключается что бы загрузить стандартные таблицы MyISAM, а затем выполнить сжатие (тест ALTER TABLE)

mysql> ALTER TABLE myisam_insert ENGINE=archive;
Query OK, 3000000 rows affected, 0 warning (8.84 sec)
Records: 3000000  Duplicates: 0  Warnings: 0

Тест на сканирование (тест SELECT COUNT)

mysql> SELECT COUNT(*) FROM test_myisam WHERE client_id = 50;
+----------+
| count(*) |
+----------+
|       24 |
+----------+
1 row in set (0.25 sec)
 
mysql> SELECT COUNT(*) FROM test_innodb WHERE client_id = 50;
+----------+
| count(*) |
+----------+
|       24 |
+----------+
1 row in set (0.59 sec)
 
mysql> SELECT COUNT(*) FROM test_archive WHERE client_id = 50;
+----------+
| count(*) |
+----------+
|       24 |
+----------+
1 row in set (0.41 sec)

Тут Archive оказался медленее MyISAM, но быстрее InnoDB.
Так же не стоит забывать, что Archive поддерживается MySQL Query Cache.
Теперь проверим на 3 000 000 записей:

mysql> SELECT COUNT(*) FROM myisam_insert WHERE c1 = 1;
+----------+
| count(*) |
+----------+
|  3000000 |
+----------+
1 row in set (1.05 sec)
 
mysql> SELECT COUNT(*) FROM archive_insert WHERE c1 = 1;
+----------+
| count(*) |
+----------+
|  3000000 |
+----------+
1 row in set (2.20 sec)
 
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT COUNT(*) FROM archive_insert WHERE c1 = 1;
+----------+
| count(*) |
+----------+
|  3000000 |
+----------+
1 row in set (0.00 sec)
 
mysql> SHOW STATUS LIKE 'qcache_hits';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 1        |
+-------------------------+----------+


MEMORY aka HEAP
Подробней о MEMORY
Все в памяти. Очень быстрый поиск данных, однако все они хранятся только в памяти и будут потеряны при остановке сервера. Великолепно подходит для временных таблиц.
Примечания:

  • Этот тип таблиц предлагает супер быстрый возврат данных, который работает только для небольших временных таблиц.
    Если загружаете слишком большие объемы данных в Memory таблицы, MySQL начинает свопировать информацию на диск и тем самым Вы теряете все преимущества Memory движка.
  • Таблицы не располагают многими возможностями обычных таблиц. Они не могут иметь столбцы типа BLOB или TEXT. Нельзя использовать флаг AUTO_INCREMENT.
  • Можно создавать индексы, но нельзя индексировать столбцы, допускающие значения NULL. Индексы используются только в операциях = и <=> .
  • Записи таблиц имеют фиксированную длину. Для столбцов типа VARCHAR сразу выделяется максимальное число байтов. Поскольку память — ограниченный ресурс, можно задать предельное количество записей в резидентной таблице.
    Для этого предназначена опция max_rows инструкции CREATE TABLE. Серверная переменная max_heap_table_size задает максимальный объем памяти, занимаемой всеми резидентными таблицами.
    С версии MySQL 5.1 будет включена поддержка VARCHAR.


MERGE aka MRG_ISAM
Подробней о MERGE
Коллекция MyISAM таблиц логически объединенных вместе для единого представления. В таблице типа Merge группируется несколько таблиц MyISAM одинаковой структуры. Программа MySQL создает файл с расширением .MRG, в котором содержится список таблиц. При доступе к объединенной таблице программа обращается к каждой таблице из списка. Если в списке всего одна таблица, то создается только ее псевдоним. Если же таблиц две или более, их записи трактуются так, будто они находятся в одной таблице. С функциональной точки зрения объединенная таблица обладает всеми свойствами обычной таблицы.
Примечания:

  • Объединенная таблица создается очень быстро, так как требуется всего лишь сформировать список имен исходных таблиц. В случае уничтожения такой таблицы удаляется лишь MRG-файл, но не исходные таблицы.
  • В них нельзя вставлять записи, поскольку программа MySQL не имеет возможности определить, в какую из исходных таблиц они должны быть помещены.
  • При работе с такими таблицами задействуется большее число файловых дескрипторов, так как программе приходится открывать каждую исходную таблицу в отдельности. Следовательно, извлечение данных из объединенных таблиц осуществляется медленнее, чем из таблиц других типов. Даже если дескрипторы индексных файлов совместно используются несколькими потоками, все равно приходится читать индексный файл каждой исходной таблицы.

Примеры:

CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
CREATE TABLE total (a INT NOT NULL, message CHAR(20), KEY(a))
TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

Не создавайте UNIQUE или PRIMARY KEY в таблице total если не уверенны что ключ будет уникальным.

mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+


BDB aka BERKELEYDB
Подробней о BDB
Смесь MyISAM и InnoDB. С функциональной точки зрения таблицы BDB ведут себя аналогично таблицам MyISAM. Нет никаких ограничений на число столбцов или индексов. Во всём основном BDB как InnoDB: поддерживает тразакции, команда SELECT COUNT(*) FROM table_name выполняется медленно и тп. Однако BDB уступает в самовосстановлении данных InnoDB. Не смотря на все его большие возможности, BDB не получило распространение.
! В ближайшем будущем (точнее с MySQL 5.1) поддержка этого движка прекратится. (источник)
Примечания:

  • Для таблиц BDB обязательно наличие первичного ключа. Если он не задан, MySQL самостоятельно создаст внутренний первичный ключ, охватывающий первые пять байтов каждой записи.
  • В BDB таблицы блокируются на уровне страниц. Страница — это совокупность последовательно расположенных записей таблицы. Страничные блокировки необходимы, когда MySQL сканирует таблицу, а также при удалении, вставке и обновлении записей.
  • Блокировки таблиц BDB могут приводить к возникновению тупиков, т.е. взаимоблокировок. В подобной ситуации одна или несколько транзакций отменяется. Это следует учитывать при написании приложений. Инструкция, которая приводит к автоматической отмене транзакции, возвращает сообщение об ошибке. Транзакции отменяются также в случае нехватки места в файловой системе.


NDB aka NDBCluster
Подробней о NDB
Кластерный движок – данные автоматически разделяются и реплицируются по различным машинам, именуемым – дата узлы. Применяется для приложений, которые требуют высокой производительности с наивысшей степенью доступности. NDB хорошо работает на системах требующих высокой отдачи на операциях чтения. Для «тяжелых» приложений требующих активной записи в конкурирующей среде рассмотрите вариант с InnoDB.


FEDERATED
Подробней о FEDERATED
Позволяет «монтировать» удалённую таблицу на локальном сервере. По сути, скопировать содержимое.
Примечания:

  • Для создания FEDERATED таблицы необходимо существование удалённой таблицы.
  • Нельзя использовать ALTER TABLE.
  • Не поддерживает тразакции.
  • FEDERATED таблице не известно о каких-либо структурных изменениях, которые могут произойти в удалённой таблице. Можете схлопотать ошибки во время выполнения.

Примеры:
Таблица на удалённом сервере.

CREATE TABLE `City` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` char(35) NOT NULL default '',
  `CountryCode` char(3) NOT NULL default '',
  `District` char(20) NOT NULL default '',
  `Population` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM;

Таблица на локальном сервере

CREATE TABLE `City` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` char(35) NOT NULL default '',
  `CountryCode` char(3) NOT NULL default '',
  `District` char(20) NOT NULL default '',
  `Population` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) ENGINE = FEDERATED
connection='mysql://user:pass@remote.com:3306/world/City';
SELECT * FROM City WHERE ID = 1;
 
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | Kabul | AFG         | Kabol    |    1780000 |
+----+-------+-------------+----------+------------+


BLACKHOLE aka /dev/null engine
Подробней о BLACKHOLE
BLACKHOLE действует как черная дыра. Она принимает данные, но не сохраняет их. Поиски всегда возвращают пустой результат.
Примечания:

  • Поддерживает все виды индексов.
  • Не сохраняет данные, однако ведёт двоичный журнал (binary log) действий, если, конечно, он включен.
    Это может быть полезно в качестве репликации или фильтра.
    Другие возможные использования BLACKHOLE:
    - Проверка синтаксиса файла дампа.
    - Измерение непроизводительных(?) затрат, сравнивая показатели BLACKHOLE с учетом и без учета двоичного логирования.
    - Может использоваться для нахождения критических параметров эффективности комманд, не связанных с типом движка непосредственно.

Примеры:

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.03 sec)
 
mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM test;
Empty set (0.00 sec)


Falcon
Подробней о Falcon
Появится в MySQL 5.2. В настоящее время (5.2.0-alpha), движок находится в состоянии alpha. Функциональный и быстрый движок.

  • полная поддержка транзакций (full ACID)
  • поддержка ссылочной целостности таблиц
  • хранение всех типов данных MySQL
  • оптимизации для работы с большим количеством оперативной памяти
  • полная мультиверсионность, позволяющая уменьшить, а иногда полностью устранить необходимость в блокировках
  • сжатие данных
  • алгоритмы работы с диском

Впоследствии вполне сможет стать хорошей заменой для InnoDB.


Maria
Подробней о Maria
Новый MySQL движок для хранения данных. Maria представляет собой расширенную версию хранилища MyISAM, с добавлением средств сохранения целостности данных после краха.

Примечания:

  • В случае краха производится откат результатов выполнения текущей операции или возврат в состояние до команды LOCK TABLES. Реализация через ведение лога операций;
  • Возможность восстановления состояния из любой точки в логе операций, включая поддержку CREATE/DROP/RENAME/TRUNCATE. Может быть использовано для создания инкрементальных бэкапов, через периодическое копирование лог файла.
  • Поддержка всех форматов столбцов MyISAM, расширена новым форматом «rows-in-block», использующим страничный механизм хранения данных, при котором данные в столбцах могут кэшироваться;
  • В будущем будет реализовано два режима: транзакционный и без отражения в логе транзакций, для не критичных данных.
  • В Maria размер страницы данных равен 8Кб (в MyISAM 1Кб), что позволяет достичь более высокой производительности для индексов по полям фиксированного размера, но медленнее в случае индексирования ключей переменной длинны.
  • Недостатки которые планируется устранить: неэффективная работа со столбцами, данные в которых занимают менее 25 байт; Maria 1.0 поддерживает один поток записи или много на чтение (в MyISAM – один на запись _и_ много на чтение (concurrent insert)); нет поддержки INSERT DELAYED.

Резюмируя, приведём наиболее подходящие движки хранения для различных задач:

  • Поисковый движок – NDB кластер
  • Логирование веб статистики – Обычные файлы для логирования с оффлайновым обработчиком и записью всей статистики в InnoDB таблицы
  • Финансовые транзакции – InnoDB
  • Сессионные данные – MyISAM или NDB кластер
  • Локальные расчеты – HEAP
  • Словари – MyISAM

Полезные ссылки:

http://mysqlinfo.ru/anything_VidiTablicISposobIhHraneniya.htm

http://dev.mysql.com/tech-resources/articles/storage-engine.html

http://dev.mysql.com/doc

http://www.mysql.ru/docs/man/

Aco MySQL

  1. Пока что нет комментариев.
  1. Пока что нет уведомлений.