Оптимизация запросов MySQL — архитектура таблиц

Когда мы говорим про оптимизацию запросов, в первую очередь, стоит оптимизировать сами типы столбцов таблицы. Лучше это делать при создании таблиц или при добавлении новых столбцов в таблицу.

• Меньшие по размеру типы данных обычно быстрее, так как занимают меньше места на диске, в памяти и кэше процессора

• Следует упрощать сложные типы данных, если такое возможно. 

Например, IP адреса лучше хранить в целочисленных данных, так как сравнение строковых данных работает намного медленнее. 

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

• Целые числа: TINYINT (8 бит), SMALLINT (16 бит), MEDIUMINT (24 бит), INT (32 бит), BIGINT(64 бит) и диапазоны значений от -2^(N — 1) до 2^(N — 1). В зависимости от планируемого диапазона значений — выбираем более простой тип

Часто, отрицательные значений не требуются (например, авто-инкрементирующийся ID) и можно расширить диапазон в два раза благодаря атрибуту UNSIGNED.

• Вещественные числа занимают значительно больше места.

Расчеты с данными с плавающей точкой высокозатратные и могут приводить к ошибкам на бекенде, когда «1» на самом деле берется как «0.9999987». В том числе поэтому, целесообразнее выбрать Целочисленный тип вместо Вещественного. Если нам известно, что у данных максимум 2 знака после запятой, «109.40» храним и обрабатываем как «10940»

• Наиболее часто используемый строковый тип — VARCHAR. Используется при хранении данных переменной длины.

• В отличии от VARCHAR, CHAR имеет фиксированную длину и лучше оптимизирован для сравнения строк с примерно одинаковой длинной (например для хранения MD5 сверток данных пользователя). 

CHAR так же эффективен для хранения часто изменяемых данных, так как не подвержен фрагментации при записи

• Для хранения больших данных используются: TEXT — символьные данные, BLOB — двоичные данные

• Если количество вариаций данных ограничено, то можно использовать ENUM. 

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

• Лучше избегать значений NULL, если они не нужны. 

Стоит объявлять столбец как NOT NULL. MySQL тяжелее оптимизировать запросы к столбцам, допускающим NULL