MySQL/MariaDB: особенности, InnoDB vs MyISAM

Введение

MySQL (и его открытый форк MariaDB) — один из самых широко используемых движков СУБД, особенно в веб-разработке. В статье разберём ключевую особенность MySQL — выбор движка хранения (storage engine) для каждой отдельной таблицы, в частности различия между современным, рекомендуемым InnoDB и устаревающим, но исторически значимым MyISAM.

Концепция

InnoDB — современный, рекомендуемый по умолчанию движок хранения MySQL, поддерживающий транзакции (статья 408), внешние ключи (статья 397) с реальной проверкой ссылочной целостности, и построчные блокировки (статья 409). MyISAM — более старый движок, не поддерживающий транзакции и внешние ключи вовсе, использующий блокировки на уровне всей таблицы (а не отдельных строк), что для большинства современных применений делает его значительно менее подходящим выбором, хотя для очень специфичных, редких сценариев (таблицы, предназначенные исключительно для чтения, без какой-либо конкурентной записи) MyISAM теоретически мог давать небольшое преимущество в производительности за счёт более простой внутренней структуры.

Пример кода: MySQL/MariaDB

-- Явное указание движка хранения при создании таблицы
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY, -- AUTO_INCREMENT — MySQL-эквивалент SERIAL из PostgreSQL
    customer_id INT,
    total_amount DECIMAL(12, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB; -- современный, рекомендуемый выбор для большинства таблиц

-- Проверка текущего движка существующих таблиц
SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = 'my_database';

-- Изменение движка существующей таблицы (требует копирования всех данных — может занять время)
ALTER TABLE legacy_table ENGINE=InnoDB;

-- Специфичный для MySQL синтаксис — REPLACE INTO и INSERT ... ON DUPLICATE KEY UPDATE,
-- не имеющие прямого эквивалента в стандартном SQL/других СУБД (статья 427)
INSERT INTO customers (id, name, email) VALUES (1, 'Иван Петров', 'ivan@example.com')
ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email);
-- ^ "upsert" — вставка новой строки, или обновление существующей при конфликте по ключу

Пояснения к коду

ENGINE=InnoDB явно указывается при создании таблицы — хотя современные версии MySQL используют InnoDB по умолчанию, явное указание делает выбор движка очевидным при чтении схемы и защищает от неожиданностей, если настройки по умолчанию конкретной конфигурации сервера были изменены администратором. INSERT ... ON DUPLICATE KEY UPDATE — практичная, специфичная для MySQL конструкция «upsert» (insert-or-update), решающая распространённую задачу без необходимости в отдельных SELECT для проверки существования строки перед выбором между INSERT и UPDATE — другие СУБД решают ту же задачу через иной синтаксис (ON CONFLICT в PostgreSQL, MERGE в стандартном SQL/SQL Server), что подробнее разбирается в статье 427 про различия диалектов.

Подводные камни

Продолжение использования MyISAM для новых таблиц по привычке или из-за устаревших примеров в интернете, без понимания, что отсутствие поддержки транзакций и внешних ключей в MyISAM делает его неподходящим выбором для большинства современных приложений, где целостность данных и согласованность при параллельном доступе важны — для нового кода (если не существует очень специфичной, осознанной причины для MyISAM) InnoDB должен быть выбором по умолчанию без особых раздумий.
Смешивание таблиц с разными движками в рамках операций, ожидающих транзакционности всей операции целиком (например, JOIN или внешний ключ между InnoDB-таблицей и MyISAM-таблицей) — поскольку MyISAM не поддерживает транзакции, любая операция, логически зависящая от атомарности между таблицей InnoDB и таблицей MyISAM, не получит реальной транзакционной гарантии для части операции, относящейся к MyISAM-таблице, что может привести к неожиданному, частичному состоянию при сбое посередине такой смешанной операции.
Длительная блокировка таблицы при операциях с MyISAM (из-за блокировки на уровне всей таблицы, а не отдельных строк, как у InnoDB) в системе с конкурентным доступом — это может создать заметные задержки для других, ожидающих параллельных операций с той же таблицей, что особенно проблематично для активно используемых, часто изменяемых таблиц, где построчные блокировки InnoDB дают значительно лучшую параллельность.
Игнорирование различий синтаксиса между MySQL/MariaDB и другими СУБД (включая ON DUPLICATE KEY UPDATE из примера, не работающий в PostgreSQL/SQL Server без соответствующей замены на их собственный синтаксис) при написании кода, который теоретически может потребовать переноса на другую СУБД — специфичные для MySQL конструкции дают практические удобства, но создают зависимость от конкретного диалекта, аналогично подобным подводным камням для PostgreSQL-специфичных возможностей (статья 423), и эта зависимость должна быть осознанным выбором, а не случайным следствием использования первого попавшегося, удобного синтаксиса без учёта переносимости.