Mysql

Движки таблиц MySql

MyIsam

Одна из основных (наряду с InnoDB) систем хранения данных в СУБД MySQL. Она основывается на принципах ISAM и обладает в сравнении с ним рядом полезных дополнений. Поддерживается с версий MySQL 3.x, до версий MySQL 5.5 являлась системой хранения по умолчанию. MyISAM имеет возможности по сжатию данных, по созданию полнотекстовых индексов. Однако не является устойчивой к сбоям и не выполняет требования ACID.[1]

InnoDB

Одна из выбираемых подсистем низкого уровня в СУБД MySQL, входит во все стандартные сборки для различных операционных систем. Основным отличием InnoDB от других подсистем низкого уровня MySQL является наличие механизма транзакций и внешних ключей.

В отличие от таблиц MyISAM, где для каждой таблицы создается один файл данных, данные InnoDB в настройках по умолчанию хранятся в больших совместно используемых файлах (изменить это можно с помощью настроек опции innodb_file_per_table), что позволяет использовать постраничный кэш страниц базы данных. Формат данных InnoDB обеспечивает надежное хранение данных за счет транзакционности и блокировки данных на уровне строки.

Memory

Таблица, хранимая в памяти. Поддерживает хеш индекс. Может подойти для справочных» таблиц или таблиц «соответствия», например для таблицы, в которой почтовым кодам соответствуют названия регионов; Для кэширования результатов периодического агрегирования данных; для промежуточных результатов при анализе данных.

CSV

хранит таблицы в CSV формате позволяет редактировать таблицы внешними приложениями плохо документирован, есть открытые баги

FEDERATED

Представляет собой «прозрачное» подключение к другому серверу (не репликация). Есть множество ограничений, планируется возможность подключения к отличным от MySQL серверам.

BLACKHOLE

Данные идут «вникуда», двоичные логи пишутся. Оптимизация репликации (мастер-сервер не пишет данные на диск)

Разное

Char & Varchar

При выборе типов строк действует правило минимума. Оцените максимальную длину строки и поставьте ограничение. Тип CHAR — тип фиксированной длины. Это значит, что для любой строки будет выделено всегда одно и то же количество байт. VARCHAR — тип переменной длины. В такой колонке строка будет занимать ровно свою длину (в количестве символов). Однако Mysql прибавит еще 1 или 2 байта на хранение длины самой строки. Также стоит учесть, что обновление такой строки может быть дорогой операцией (чревато фрагментацией данных, а значит — замедлением чтения). Используйте такое правило:

Если значения в текстовой колонке похожи по длине, выбирайте CHAR, иначе — VARCHAR.

Innodb VS MyIsam

• MyISAM поддерживает сжатие таблиц в отличие от InnoDB.

• MyISAM имеет встроенные полнотекстный поиск в отличие от InnoDB.

• InnoDB поддерживает транзакции в отличие от MyISAM.

• InnoDB поддерживает блокировки уровня строки (MyISAM - только уровня таблицы).

• InnoDB поддерживает ограничения внешних ключей (MyISAM - нет).

• InnoDB более надежна при больших объемах данных.

• InnoDB в теории немного быстрее.

DATATIME vs TIMESTAMP

TIMESTAMP хранит значение равное количеству секунд, прошедших с полуночи 1 января 1970 года по усреднённому времени Гринвича. При получении из базы отображается с учётом часового пояса. Предназначен для хранения даты и/или времени происхождения неких событий. Важное отличие от DATATIME в диапазонах значений: очевидно, что TIMESTAMP не годится для хранения исторических событий (даже таких, как дни рождений), но отлично подходит для хранения текущих (логирование, даты размещения статей, добавления товаров, оформления заказов) и предстоящих, в обозримом будущем, событий (выходы новых версий, календари и планировщики и т.д).

Основное удобство использования типа TIMESTAMP состоит в том, что для столбцов этого типа в таблицах можно задавать значение по умолчанию в виде подстановки текущего времени, а так же установки текущего времени при обновлении записи. Если вам требуется эти возможности, то с вероятностью 99% TIMESTAMP — именно то, что вам нужно.

Не стоит бояться того, что с приближением к 2038 году ваш софт перестанет работать. Во-первых, до этого времени вашим софтом, скорее всего, просто перестанут пользоваться (особенно версиями, которые пишутся сейчас). Во-вторых, с приближением к этой дате разработчики MySQL обязательно что-нибудь придумают для сохранения работоспособности вашего софта. Все решится так же хорошо, как проблема Y2K.

Итак, тип TIMESTAMP используем для хранения дат и времени свершения событий нашего времени, а DATETIME и DATE — для хранения дат и времени свершения исторических событий, или событий глубокого будущего.

Диапазоны значений — это важное отличие между типами TIMESTAMP, DATETIME и DATE, но не главное. Главное то, что TIMESTAMP хранит значение в UTC. При сохранении значения оно переводится из текущего временной зоны в UTC, а при его чтении — во время текущей временной зоны из UTC. DATETIME и DATE хранят и выводят всегда одно и то же время, независимо от временных зон.

Временные зоны устанавливаются в СУБД MySQL глобально или для текущего подключения. Последнее можно использовать для обеспечения работы разных пользователей в разных временных зонах на уровне СУБД. Все значения времени физически будут храниться в UTC, а приниматься от клиента и отдаваться клиенту — в значениях его временной зоны. Но только при использовании типа данных TIMESTAMP. DATE и DATETIME всегда принимают, хранят и отдают одно и то же значение.

Функция NOW() и ее синонимы возвращают значение времени в текущей временной зоне пользователя.

Учитывая все эти обстоятельства, необходимо быть крайне внимательными при изменении временной зоны в пределах подключения к серверу и использовании типов DATE и DATETIME. Если надо хранить дату (например, дату рождения), то никаких проблем не будет. Дата рождения в любой зоне одинаковая. Т.е. если вы родились 1 января в 0:00 UTC/GMT+0, то это не значит, что в Америке будут праздновать ваш день рождения 31 декабря. Но если вы решите хранить время события в столбце DATETIME, то тут уже построить работу с пользовательскими временными зонами на уровне СУБД просто не выйдет.

DATETIME 8 байт TIMESTAMP4 байта

Является ли триггер частью транзакции?

For transactional tables, failure of a statement should cause rollback of all changes performed by the statement. Failure of a trigger causes the statement to fail, so trigger failure also causes rollback. For nontransactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect.

NULL

К NULL-значениям нужно привыкнуть. По идее, NULL обозначает отсутствующее или неизвестное значение и обрабатывается отличным от других значений образом. Проверить значение на равенство NULL с помощью обычных арифметических операторов сравнения (=, < или <>) нельзя. Это отлично иллюстрирует следующий запрос:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

Очевидно, что от таких сравнений значащих результатов ожидать нечего. Вместо этого нужно пользоваться операторами IS NULL и IS NOT NULL:

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

Отметим, что два значения NULL считаются равными при выполнении команды GROUP BY.

В MySQL 0 или NULL приравнивается к логическому false, а все остальное - к true. По умолчанию значение "истина" для булевого оператора равно 1.

При выполнении команды ORDER BY значения NULL всегда располагаются в самом начале списка результатов, даже при использовании параметра DESC.

Проблема LIMIT 100000, 10

Основная проблема тут в том, что при «LIMIT 100000, 10» MySQL сначала посмотрит 100000 записей, а только потом вернёт 10.

Дополнительно:

• https://ruhighload.com/%D0%92%D1%8B%D0%B1%D0%BE%D1%80+%D1%82%D0%B8%D0%BF%D0%BE%D0%B2+%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85+%D0%B2+mysql
• https://itif.ru/otlichiya-myisam-innodb/
• https://valera.ws/2009.09.17~time-formats-in-mysql/
• https://habr.com/company/mailru/blog/266811/