Избегаем часто встречающихся ошибок при работе с MySQL

Работа с базой данных — зачастую самое слабое место в производительности многих web-приложений. И часто можно заметить, как на одни и те же грабли наступают разработчики при проектировании базы данных. Данная подборка советов поможет вам избежать многих ошибок и узнать для себя много полезного.

Общее

  • Под каждую отдельную базу данных желательно создавать отдельного пользователя.
  • Кодировка базы может быть любой, если она UTF-8.
  • В большинстве случаев лучше использовать движок InnoDB.
  • В PHP лучше забыть про сильно устаревшее расширение MySQL и по возможности использовать PDO или MySQLi.
  • Без особой нужды не стоит открывать MySQL наружу. Лучше сделать проброс портов:
  • Используйте подсказки от PROCEDURE ANALYSE(), который анализирует структуру вашей таблицы и дает советы по оптимизации. Следует заметить, что это возможно только при наличии реальных данных в таблице, так как анализ происходит на их основе.
  • Лучше перейти на MariaDB и забыть про MySQL.

Поиск и запросы

  • Если при обращении к таблице вам нужна одна-единственная строка, то используйте LIMIT 1. Использование LIMIT 1 будет оптимальнее, так как база данных остановит выборку записей сразу же после нахождения строки вместо того, чтобы выбирать всю таблицу или индекс.
  • Помимо обычного индекса строк, вы можете проиндексировать любые столбцы в таблице. Это даст прирост производительности при поиске.
  • Не используйте ORDER BY RAND(). Если вам действительно нужен случайный порядок строк в запросе, то есть много различных способов сделать это иначе. Вам нужно будет написать дополнительный код, однако вы избавитесь от слабого места в производительности. ORDER BY RAND() экспоненциально замедляется при росте базы данных.
  • Избегайте использования SELECT *. Чем больше данных считывается из таблицы, тем медленнее запрос, что, в свою очередь, увеличивает время работы с хранилищем данных. Также, если сервер БД установлен отдельно от web-сервера, то будет большая задержка при передаче данных по сети. Лучше делать следующим образом:

Работа с данными

  • Если система 32-битная, то нет смысла ставить свойство UNSIGNED для типа INTEGER, так как такие большие числа в PHP не поддерживаются. Но если вы используете 64-битную систему, то в этом случае PHP поддерживает большие числа, вплоть до BIGINT со знаком.
  • Связанные таблицы «Foreign keys» должны иметь сходство по структуре ключей.
  • Для хранения булевых значений лучше всего использовать TINYINT(1).
  • Денежные единицы лучше всего хранить в целых числах и работать только с целыми числами. Например, числом копеек, чтобы 100 означало один рубль.

Работа со строками

  • Лучшая кодировка для большинства баз данных — UTF-8.
  • Тип TEXT вмещает в себя только 64 Кб. Для того, чтобы вместить более объемные тексты, нужно использовать LONGTEXT.
  • В версиях MySQL до 5.0.3 VARCHAR была ограничена 255 символами, однако в более старших версиях ограничением является 65535 символов.

Работа с датами

  • При сравнении DATETIME и TIMESTAMP не забывайте делать преобразование типов:
  • Так как TIMESTAMP хранится в виде UNIX_TIMESTAMP, есть возможность выставить автоматическое обновление колонки.
  • Типы данных DATE, TIME, DATETIME выводятся в виде строк, поэтому поиск и сравнение различных дат происходит через преобразование.

Работа с перечислениями

  • Для перечислений правильно использовать тип ENUM. Это очень быстрый и компактный тип поля, в котором значения хранятся как в TINYINT, но отображаются как в строковом поле.
  • Как и для любой строки, для перечислений можно ставить значение по умолчанию.
  • Поле с перечислением хранится как число, поэтому скорость работы довольно высокая.

Работа с бэкапами

Лучше использовать бэкап с дополнительными опциями -Q, -c, -e:

  • -Q оборачивает имена обратными кавычками.
  • -c делает полную вставку, включая имена колонок.
  • -e делает расширенную вставку.

В результате итоговый файл получается меньше, и создается он чуть быстрее.

Отладка

  • Если запросы работают медленно, то можно попробовать включить лог для медленных запросов в /etc/mysql/my.cnf, а потом оптимизировать запросы через EXPLAIN.
  • Чтобы наблюдать как за запросами, так и в целом за работой сервера, удобно использовать программу Mytop.

Источники: «MySQL шпаргалки» и «20 хороших советов по MySQL»