Раздел 34 · MySQL с нуля

Движки хранения: InnoDB и MyISAM

junior~20 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

В прошлом уроке мы создали базу и пользователя. Прежде чем создавать таблицы, разберём особенность MySQL, которой нет в PostgreSQL и SQLite. Таблица в MySQL может храниться на диске разными способами, и от способа зависит, переживут ли твои данные ошибку посреди продажи билетов.

Карандаш и ручка

Представь двух кассиров. Первый сначала записывает продажу карандашом на черновике. Если зритель передумал или касса зависла, черновик стирают, и в журнале не остаётся следов. Только когда всё прошло, кассир переносит запись в журнал. Второй пишет сразу ручкой в журнал. Так быстрее, но передумать уже нельзя: зачёркнутая строка всё равно остаётся в журнале.

В MySQL эти два кассира называются InnoDB и MyISAM. Сервер умеет хранить таблицы несколькими способами, и каждый способ это отдельный движок хранения. Верхняя часть сервера одна: она разбирает SQL и проверяет права. А вот читает и пишет строки уже движок, и его выбирают для каждой таблицы отдельно.

Какие движки есть

Спросим сервер:

SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

Читаем колонку Support: YES значит «есть», NO значит «в этой сборке нет», а DEFAULT стоит у движка, на котором создаётся таблица, если ты не указал другой. Это InnoDB. В колонке Comment у него написано главное: транзакции, блокировки строк и внешние ключи. В колонке Transactions одно YES на весь список.

Остальные движки нишевые. MEMORY держит таблицу в памяти и теряет её при перезапуске, CSV хранит строки в текстовом файле, BLACKHOLE принимает данные и выбрасывает их. Из всего списка тебе встретится разве что MyISAM, в старых базах. Движок по умолчанию хранится в переменной сервера:

SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

Опыт с черновиком

Создадим две одинаковые таблицы журнала, на разных движках. Движок задают словом ENGINE в конце CREATE TABLE:

CREATE TABLE log_innodb (id INT PRIMARY KEY, msg VARCHAR(50)) ENGINE=InnoDB;
CREATE TABLE log_myisam (id INT PRIMARY KEY, msg VARCHAR(50)) ENGINE=MyISAM;

Теперь откроем транзакцию, запишем по строке в обе таблицы и передумаем:

START TRANSACTION;
INSERT INTO log_innodb VALUES (1, 'продан билет A1');
INSERT INTO log_myisam VALUES (1, 'продан билет A1');
ROLLBACK;

Оба INSERT ответили Query OK, 1 row affected. А вот что сказал ROLLBACK:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Снова предупреждение. Прочитаем его:

SHOW WARNINGS;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

«Некоторые изменённые таблицы без поддержки транзакций не удалось откатить». Проверим обе:

SELECT * FROM log_innodb;
Empty set (0.00 sec)
SELECT * FROM log_myisam;
+----+----------------------------+
| id | msg                        |
+----+----------------------------+
|  1 | продан билет A1            |
+----+----------------------------+
1 row in set (0.00 sec)

InnoDB стёр черновик: таблица пуста. MyISAM записал ручкой сразу: строка осталась, хотя мы отменили транзакцию. А теперь представь продажу из двух шагов, «занять место» и «списать деньги». Если второй шаг упал, на MyISAM первый уже не отменить, и место останется занятым без оплаты. Зачем вообще нужны транзакции и что значит «целиком или никак», разобрано в теории про ACID, а с транзакциями InnoDB мы поработаем в уроке 19.

Опыт со ссылкой

Второе различие касается внешних ключей. Создадим таблицу возвратов, где каждая строка ссылается на билет. Сначала на MyISAM:

CREATE TABLE refunds (id INT PRIMARY KEY, ticket_id INT, FOREIGN KEY (ticket_id) REFERENCES tickets (id)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

Таблица создалась без единого предупреждения. Запишем возврат билета 999, которого в кинотеатре нет:

INSERT INTO refunds VALUES (1, 999);
Query OK, 1 row affected (0.00 sec)

Прошло. Посмотрим, во что превратилась таблица:

SHOW CREATE TABLE refunds\G
*************************** 1. row ***************************
       Table: refunds
Create Table: CREATE TABLE `refunds` (
  `id` int NOT NULL,
  `ticket_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ticket_id` (`ticket_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Внешнего ключа нет. MyISAM молча выбросил его и оставил только обычный индекс KEY ticket_id. Ни ошибки, ни предупреждения: ты уверен, что ссылки проверяются, а их не проверяет никто.

Та же таблица на InnoDB, который стоит по умолчанию:

CREATE TABLE refunds2 (id INT PRIMARY KEY, ticket_id INT, FOREIGN KEY (ticket_id) REFERENCES tickets (id));
INSERT INTO refunds2 VALUES (1, 999);

Таблица создалась, а на вставке сервер ответил ошибкой:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`kino`.`refunds2`, CONSTRAINT `refunds2_ibfk_1` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`))

InnoDB не пустил ссылку на несуществующий билет. Подробно про внешние ключи поговорим в уроке 10.

Как это выглядит на диске

Движок решает и то, какие файлы появятся в каталоге данных сервера. Каждая база это папка, а внутри неё:

  • у таблицы InnoDB один файл .ibd, в нём и строки, и индексы;
  • у таблицы MyISAM два файла: .MYD со строками и .MYI с индексами.

Сами эти файлы руками не трогают и не копируют на ходу: сервер держит часть данных в памяти, и копия работающей таблицы окажется битой. Для копий есть свои инструменты, о них поговорим в уроке про резервные копии.

Где ещё встретится MyISAM? В старом проекте, который начинали до 2010 года, в дампе с дешёвого хостинга, в статье десятилетней давности, где ENGINE=MyISAM вписан в каждый пример. Поэтому, получив чужую базу, первым делом проверь движок каждой таблицы через SHOW CREATE TABLE. Строка ENGINE=MyISAM в конце значит, что ни транзакций, ни внешних ключей у этой таблицы нет.

Почему по умолчанию InnoDB

До версии 5.5 (2010 год) по умолчанию стоял MyISAM, поэтому он и встречается в старых проектах. Потом его место занял InnoDB, и вот почему:

  • Транзакции. Изменения выполняются целиком или не выполняются совсем, как мы видели на ROLLBACK.
  • Внешние ключи. База сама следит, чтобы ссылки вели на существующие строки.
  • Восстановление после сбоя. InnoDB сначала записывает изменения в журнал повторов. Если сервер выключили посреди записи, при запуске он сверится с журналом и приведёт таблицы в порядок. MyISAM-таблица после такого сбоя может оказаться повреждённой, и её чинят вручную.
  • Блокировка строк. Пока одна касса меняет билет A1, другая спокойно продаёт A2. MyISAM на время записи закрывает всю таблицу целиком, и остальные ждут.

У MyISAM почти не осталось преимуществ: даже служебные таблицы самого MySQL в версии 8 переехали на InnoDB. Правило простое: все таблицы на InnoDB, а слово ENGINE можно вообще не писать.

Перевести старую таблицу

Если тебе досталась MyISAM-таблица, её переводят одной командой:

ALTER TABLE log_myisam ENGINE=InnoDB;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

1 row affected здесь честный: сервер создал новую таблицу на InnoDB и скопировал в неё все строки. На большой таблице это займёт время, поэтому такие переводы делают в тихие часы.

Если ошибиться в имени движка, сервер откажет:

CREATE TABLE t_memory (id INT) ENGINE=NoSuchEngine;
ERROR 1286 (42000): Unknown storage engine 'NoSuchEngine'

Что запомнить

  • Движок решает, как таблица лежит на диске и что умеет: транзакции, внешние ключи, восстановление после сбоя.
  • InnoDB откатывает изменения по ROLLBACK и проверяет внешние ключи, поэтому он стоит по умолчанию.
  • MyISAM пишет сразу и навсегда: после отката строки остаются, а сервер печатает предупреждение 1196.
  • Внешний ключ в таблице MyISAM MySQL молча пропускает, хотя индекс под колонку создаёт.
  • Старую таблицу переводят командой ALTER TABLE ... ENGINE=InnoDB, она переписывает все строки.

Попробуй сам

Замени ROLLBACK на COMMIT и посмотри, есть ли теперь разница между таблицами. Выполни SHOW CREATE TABLE tickets\G и найди, на каком движке живут билеты. Переведи log_myisam на InnoDB и повтори опыт с ROLLBACK.

Проверь себя

Задание

Дальше

С движком разобрались: InnoDB, и точка. Теперь можно думать, что класть в колонки. В следующем уроке начнём с чисел: целые типы, DECIMAL для денег и автоматическая нумерация строк.

домашка

Домашка