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

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

Общее

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

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

• Если при обращении к таблице вам нужна одна-единственная строка, то используйте LIMIT 1. Использование LIMIT 1 будет оптимальнее, так как база данных остановит выборку записей сразу же после нахождения строки вместо того, чтобы выбирать всю таблицу или индекс.$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama' LIMIT 1");if (mysql_num_rows($r) > 0) { ... }
• Помимо обычного индекса строк, вы можете проиндексировать любые столбцы в таблице. Это даст прирост производительности при поиске.
• Не используйте ORDER BY RAND(). Если вам действительно нужен случайный порядок строк в запросе, то есть много различных способов сделать это иначе. Вам нужно будет написать дополнительный код, однако вы избавитесь от слабого места в производительности. ORDER BY RAND() экспоненциально замедляется при росте базы данных.
• Избегайте использования SELECT *. Чем больше данных считывается из таблицы, тем медленнее запрос, что, в свою очередь, увеличивает время работы с хранилищем данных. Также, если сервер БД установлен отдельно от web-сервера, то будет большая задержка при передаче данных по сети. Лучше делать следующим образом:$r = mysql_query("SELECT username FROM user WHERE user_id = 1");$d = mysql_fetch_assoc($r);echo «Welcome {$d['username']}»;

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

• Если система 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 не забывайте делать преобразование типов:SELECT * FROM table WHERE `datetime` = DATE(`timestamp`)
• Так как TIMESTAMP хранится в виде UNIX_TIMESTAMP, есть возможность выставить автоматическое обновление колонки.
• Типы данных DATE, TIME, DATETIME выводятся в виде строк, поэтому поиск и сравнение различных дат происходит через преобразование.

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

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

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

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

			mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql
		

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

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

Отладка

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