Мне несколько надоело каждый раз воспроизводить список проблем MySQL по памяти и я решил написать этот пост.

В MySQL не очень много фичей, но за то крайне много граблей. Ниже перечисленно только малое количество из них.

Подключаемые хранилища

В MySQL разделено хранилище и логика. Эта архитектурная особенность является основной причиной всех его бед.

Вызвано это тем, что в данном случае явно присутсвует конфликт интересов:

• Подключаемые хранилищая требуют четкого и простого API для взаимодействия с ядром MySQL. Это требует, чтобы ядро знало о хранилище как можно меньше;
• Для эффективного выполнения запросов нужно знать о хранении данных как можно больше.

Не транзакционный DDL

Метаданные дублиются как в ядре MySQL, так и в его хранилише. Возникает проблема с транзакционным обновленим метаданных.

Из-за этого DDL-запросы в MySQL не транзакционны.

То есть, любой DDL-запрос автоматически подтвержает старую транзакцию и начинает новую. Этот факт может сильно усложнять написание скриптов миграции.

Дорогой ALTER TABLE

По той же причине, в MySQL почти любой ALTER TABLE влечет за собой полное пересоздание таблицы (по крайней мере в InnoDB). В том числе это касается таких операций как удалние CONSTRAINT-ов, удаление стоблцов и создание NULL-столбцов.

В большинстве СУБД подобные операции требуют только изменения метаданных и выполняются мгновенно.

AUTO_INCREMENT в InnoDB

В InnoDB значение последнего выданного AUTO_INCREMENT идентификатора не сохраняется на диске: при старте значение автоинкремента выставляется в MAX(ai_col) + 1.

В случае, если последние добавленные данные были удалены преред перезапуском сервера, произойдёт повторное использование ранее выданных идентификаторов. В случае реализации распределенной системы это крайне неприятно.

См. http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

Репликация

Репликация так же сущетсвует на уровне ядра MySQL.

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

В результате:

• при использовании репликации нужно писать как минимум, в 1.5 раза больше данных (лог для реплиакции + лог для хранилища + сами данные) чем без репликации (лог для хранилища + сами данные);
• мы получаем двухфазный коммит на ровном месте (между реплиакцией и хранилищем);
• ядро не может записать в лог физическое изменение данных, только логическое (либо изменение записи, либо запрос) и слейв вынужден предпринимать дополнительные усилия для воспроизведения изменений.

При этом в MySQL есть следущие форматы бинарного лога:

• STATEMENT (default: <= 5.7.6) - записываются сами запросы (например: DELETE FROM foo WHERE bar = 42 LIMIT 1);
• ROW (default: >= 5.7.7) - записывается изменения строк. DDL-запросы в лог пишутся как есть;
• MIXED - как повезет.

Самое забавное, что некоторые запросы при данной схеме не могут отработать корректно. Например:

ALTER TABLE foo ADD COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

Разумеется, никаких предупреждений при выполнении опастых запросов не происходит.

О том, что данные между мастером у репликой разошлись, узнаёшь, когда реплика встаёт колом: нельзя применить изменения из-за того, что данные давно разошлись.

Ко всем чудесам добавляется еще тот момент, что в отличие от мастера, слейв вынужден выполнять все запросы последовательно в один поток. Из-за этого слейв должен иметь более производительное оборудование, чем мастер…

У Царёва есть замечательный доклад на эту тему: https://habrahabr.ru/company/oleg-bunin/blog/313594/

Backup

Из-за множества хранилищ, в MySQL все крайне не просто с резервным копированием.

Есть следующие варианты:

• Слепок данных с файловой системы (остановили, заархивировали, запустили);
• Сделать дамп в SQL при помощи утилиты mysqldump;
• Архивирование бинарных логов от репликации (такой же хрупкий механизм, как и сама репликация).

Безумие mysqldump

Отдельно стоит упомянуть про mysqldump.

По-умолчанию при запуске:

mysqldump --databases sakila > sakila.sql

Эта утилита сделает дамп базы. Единственный нюанс: она заблокирует работу с базой на время создания резервной копии.

Неопытные администраторы после прочтения документации выполняют команду:

mysqldump --skip-lock-tables --databases sakila > sakila.sql

Эта команда создаёт дамп базы в SQL-формате.

Проблема в том, что данные разных таблиц буду сняты в разное время без использования механизма транзакций.

В результате, созданный таким образом дамп можно выкинуть в /dev/null. Узнают об этом обычно в самый неудачный момент: после аварии в момент восстановления базы по ошибкам нарушения FOREIGN KEY.

Корректный вызов, если вы используете только InnoDB, выглядит чуть-чуть по-другому:

mysqldump --single-transaction --databases sakila > sakila.sql

По моему опыту на эти грабли наступают ужасающе часто.

Самостийность

AUTO_INCREMENT

Нет возможность получить значение AUTO_INCREMENT до вставки записи.

TIMESTAMP vs DATETIME

Эти два типа хранят комбинацию дата+время, но между собой имеют очень мало общего.

TIMESTAMP:

• Внутри хранит кол-во секунд с момента EPOC;
• На клиенте учитывает временную зону;
• Имеет крайне забавное значение, которое не понятно как обрабатывать: 0000-00-00 00:00:00;
• Диапазон значений: от '1970-01-01 00:00:01' UTC до '2038-01-19 03:14:07' UTC;
• Внезапно, по-умолчанию поле этого типа изменяет своё значение при каждом обновлении записи на текущее время.

DATETIME:

• Внутри хранит дату и время;
• Временная зона не влияет на значение поля;
• Диапазон значений: от 0000-01-01 00:00:00 до 9999-12-31 23:59:59.

UPDATE

В MySQL операция UPDATE не соответвует стандарту SQL92.

Речь идет про абзац:

13.9 <update statement: positioned>

6) The <value expression>s are effectively evaluated before updat-
   ing the object row. If a <value expression> contains a reference
   to a column of T, then the reference is to the value of that
   column in the object row before any value of the object row is
   updated.

И иллюстрируется запросом:

UPDATE foo SET a = b, b = a WHERE id = 42;

Беда в том, этот запрос в MySQL эквивалентен:

UPDATE foo SET a = b, b = b WHERE id = 42;

DELETE

В MySQL 5.5 запрос вида:

DELETE FROM foo WHERE id IN (SELECT id FROM baz);

Выполнялся так, будто подзапрос SELECT id FROM baz является корелирующим. То есть данный подзапрос выполняется для каждой строки таблицы foo.

Я таким образом из одной таблицы удалял 170 записей минут 40.

Для решения этой проблемы у MySQL есть свой специфичный синтаксис:

DELETE foo
FROM foo
JOIN baz ON (foo.id = baz.id);

Кроме MySQL этот синтаксис никто не понимает :(

DEADLOCK

В MySQL можно организовать DEADLOCK при помощи одного подключения, например:

CREATE big_table (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(1024),
    ver INT NOT NULL DEFAULT 0
);
INSERT INTO big_table (name) VALUES (...); -- выполняем много-много раз.
BEGIN;
UPDATE big_table SET ver = ver + 1;
ROLLBACK;

Отключаемся. Подключаемся.

UPDATE big_table SET ver = ver + 1 WHERE id = 123;

Будет DEADLOCK, если ROLLBACK не закончится в течение 300 сек.

Ловушки конфигурирования

lower_case_table_names

В MySQL есть изумительный параметр lower_case_table_names, который приводит имена всех таблиц в нижний регистр и по-умолчанию включён под Windows.

В результате, если взять базу, у которой имена таблиц не в нижнем регистре, и развернуть её под Windows, то перенести эту базу обратно под Linux уже не получится.

Ссылки

Еще немного о проблемах MySQL:

• https://grimoire.ca/mysql/choose-something-else
• https://habrahabr.ru/company/oleg-bunin/blog/313594/