PHP является относительно простым языком, и большинство начинающих разработчиков могут написать функциональный код в течение нескольких часов. Тем не менее, создание прочной, надежной базы данных требует времени и опыта. Вот десять из самых грубых ошибок MySQL, которые я совершил (некоторые применимы к любому языку/базе данных)

1. Использование MyISAM вместо InnoDB

В MySQL есть несколько подсистем баз данных, но вы, скорее всего, сталкивались MyISAM и InnoDB. Подсистема MyISAM используется по умолчанию.

Если только вы не разрабатываете очень простую или экспериментальную базу данных, то почти наверняка это неправильный выбор. В MyISAM не поддерживаются ограничения внешнего ключа или транзакции, которые необходимы для обеспечения целостности данных. Кроме того, вся таблица блокируется всякий раз, когда вставляется или обновляется запись, это оказывает негативное влияние на производительность по мере возрастания нагрузки.
Решение простое: используйте InnoDB.

2. Использование функций PHP для MySQL

PHP предоставляет библиотеку функций для работы MySQL с самого первого дня (или почти с первого, не имеет значения). Многие приложения полагаются на функции mysql_connect, mysql_query, mysql_fetch_assoc и т.д., но в PHP руководстве указано:

Если вы используете MySQL 4.1.3 или более позднюю версию, настоятельно рекомендуется использовать расширение mysqli

MySQLi, или улучшенное расширение MySQL, имеет ряд преимуществ:
• (опционально) объектно-ориентированный интерфейс
• подготовленные выражения (которые помогают предотвратить SQL-инъекций и увеличения производительности)
• множественные операторы и поддержка транзакций.

В качестве альтернативы можете рассматривать PDO для поддержки нескольких баз данных.

3. Отсутствие проверки пользовательского ввода

Правило №1 должно быть: “Никогда не доверять пользовательскому вводу”. Проверяйте каждую строку с помощью серверного языка PHP, и не полагайтесь на JavaScript.
Простейшая атака SQL инъекции зависит от такого кода:

Сценарий можно взломать, если ввести “admin'; --” в поле ввода имени пользователя. SQL строка примет вид:

SELECT userid FROM usertable WHERE username='admin';

Хитрый злоумышленник может войти в систему как "admin" и не должен знать пароль, потому что это закомментировал SQL.

4. Не используется UTF-8
Те из нас, кто находится в США, Великобритании, Австралии, редко рассматривают другие языки, кроме английского. Мы с радостью завершаем шедевр только для того, чтобы его нельзя было использовать в другом месте.

UTF-8 решает многие вопросы интернационализации. Хотя кодировка не будет должным образом поддерживаться в PHP до версии 6.0, это не помешает вам установить настройки MySQL набора символов UTF-8.

5. Предпочтение PHP вместо SQL
Если вы новичок в MySQL, так и тянет решить проблему с помощью языка, который вы знаете. Это может привести к ненужному и замедлению кода.

Например, вместо того, чтобы использовать встроенную MySQL функцию AVG(), используется цикл PHP для расчета среднего значения путем суммирования всех значений в наборе записей.

Избегайте использования SQL-запросов внутри циклов PHP. Как правило, более эффективно выполнить запрос, а затем обработать результаты в цикле. В общем, используйте сильные стороны вашей базы данных при анализе данных.

4. Не оптимизированы запросы

99% проблем производительности PHP будет вызвано базой данных, и один неверный запрос SQL может создать хаос в вашем веб-приложении. В MySQL есть такие инструменты как EXPLAIN statement и Query Profiler, которые помогут найти проблемные SELECT запросы.

7. Использование неверных типов данных
MySQL предлагает целый ряд числовых, строковых и временных типов данных. Если вы храните дату, используйте поле DATE или DATETIME. Использование целого числа или строка может сделать SQL запросы более сложными, или даже невозможными.

Часто возникает желание придумывать собственные форматы данных, например, хранение сериализованных PHP объектов в строке. Управление базой данных может стать проще, но MySQL станет свалкой для хранения данных, и это может привести к проблемам в дальнейшем.

8. Использование * в запросах SELECT
Никогда не используйте * для возврата всех столбцов в таблице, это ленивый способ. Вы должны извлекать только нужные данные. Даже если требуется каждое поле, таблица неизбежно изменится.

9. Недо- или пере- индексирование

В качестве общего правила, индексы должны быть применены к любому столбцу в предложении WHERE запроса SELECT.

Предположим, у нас есть таблица с цифровым идентификатором (первичный ключ) и адресом электронной почты. При авторизации СУБД MySQL должна найти правильный идентификатор с помощью поиска электронной почты. При использовании индекса MySQL может использовать быстрый алгоритм поиска, чтобы найти электронную почту практически мгновенно. Без индекса, MySQL необходимо проверить каждую запись последовательно, пока адрес не будет найден.

Иногда хочется добавить индексы к каждому столбцу, однако, они заново создаются во время каждой операции INSERT или UPDATE. Это может ударить производительности. Добавляйте индексы только в случае необходимости.

10. Забывают создать резервную копию.

Такое бывает редко, но базы данных иногда ломаются. Жесткий диск может остановиться. Серверы могут взорваться. Веб-узлы, могут обанкротиться. Потеря данных MySQL может стать катастрофической, поэтому убедитесь, что вы настроили автоматизированное резервное копирование или репликацию.

11. Бонусная ошибка
MySQL может быть наиболее широко используемой базой данных среди разработчиков PHP, но это не единственный вариант. PostgreSQL и Firebird являются её ближайшими конкурентами, оба с открытым исходным кодом и не контролируется корпорацией. Даже SQLite может быть реальной альтернативой для небольших или встроенных приложений.