Свежие комментарии

    Архивы рубрики ‘Базы данных’

    10 самых распространенных ошибок, которые допускают PHP разработчики при работе с MySQL

    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 инъекции зависит от такого кода:

    // ТАК НЕ ДЕЛАТЬ
    $username = $_POST["name"];   
    $password = $_POST["password"];   
    $sql = "SELECT userid FROM usertable WHERE username='$username' AND password='$password';";   
    // выполнить запрос..
    ?>

    Сценарий можно взломать, если ввести “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 может быть реальной альтернативой для небольших или встроенных приложений.

    Оптимизируем сервер MySQL: изменяем значения переменных

    Итак, вы купили выделенный или виртуальный сервер (VPS). Разместили на нем один или множество своих сайтов. И потекли посетители. И кажется, все хорошо. Посещаемость растет, заработок растет. Однако что это – с увеличением посещаемости сайты начинают открываться все медленнее и медленнее. Все чаще и чаще посетители жалуются, что сайт не открывается. И нужно что-то делать.

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

    Материал данной статьи будет актуален для владельцев виртуального или выделенного сервера. Так как только они смогут изменить настройки установленного на хостинге MySQL-сервера. На более дешевых вариантах хостинга никто не разрешит вам изменять настройки святая святых веб-сервера.

    Открываем панель управления сайтом

    Ну что ж, время – деньги. А особенно когда веб-сервер работает кое-как, и ваши посетители просто в ярости. Поэтому сразу перейдем к практике.

    В настройке MySQL-сервера нам поможет панель администрирования phpMyAdmin. Данная панель, наверное, является наиболее полным справочником о том, как следует оптимизировать сервер MySQL. Хотя, если эта панель не установлена на вашем хостинге, все приведенные ниже шаги можно выполнить непосредственно при помощи SQL-команд.

    В качестве примера будет использован VPS сервер, арендованный на хостинге http://hostpro.ua. Итак, зайдите в панель управления вашим сервером. Для этого введите в адресной строке браузера IP-адрес вашего VPS-сервера и порт панели управления. Например, http://194.33.180.160:2086/.

    После того, как панель управления запросит пароль для логина root, и вы его успешно введете, вы окажетесь в святая-святых вашего веб-сервера. В нашем случае (см. на рисунке) используется панель управления cPanel. В разделе SQL Services данной панели управления выберите ссылку phpMyAdmin.

    Панель phpMyAdmin: ищем проблемные участки

    Итак, панель phpMyAdmin мы открыли (убедитесь, что вы открыли ее от имени администратора сервера, а не от имени одного из установленных на сервере сайтов, иначе вы не сможете изменять глобальные переменные). Теперь переходим в раздел Состояние, и начинаем его пристально изучать.

    В данном разделе отображаются статистические сведения о работе вашего MySQL-сервера. Первым делом посмотрите на значения следующих счетчиков:

    — MySQL сервер работает … – количество времени с момента последнего перезапуска MySQL-сервера. Как правило, хорошо работающий MySQL-сервер никогда не перезапускается. Поэтому, если значение данного счетчика не вызывает у вас уважения, значит на сервере действительно есть проблемы, из-за которых сервер MySQL часто приходится автоматически перезапускать;

    — таблица Соединения строчка Максимально одновременных – показывает максимальное количество одновременных соединений с базой данных MySQL, которое было зарегистрировано с момента последнего перезапуска веб-сервера; значение данного счетчика должно быть далеко до максимально возможного количества одновременных соединений (далее мы научимся изменять эту настройку), ведь если максимальное количество одновременных соединений будет превышено, новые посетители не смогут зайти на сайт (для них будет отображено сообщение too many connections);

    — таблица Соединения строчка Неудачных попыток – показывает процент всех соединений с базой данных, которые установить не удалось; чем меньше это значение, тем лучше;

    — таблица Соединения строчка Прерваны – показывает процент всех соединений с базой данных, которые не удалось довести до конца; чем меньше это значение, тем лучше.

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

    Теперь переходим к значениям переменных (в разделе Состояние, ниже). Нас интересуют значения, которые выделены красным цветом. Данным цветом панель phpMyAdmin сигнализирует о проблемах в работе вашего MySQL-сервера.

    Следующий шаг после того, как проблемная переменная найдена – чтение описания, которое приводится для данной переменной. Например, переменная Created_tmp_disk_tables. На нашем сервере значение этой переменной – 268 k, что очень много. Читаем описание: «Количество временных таблиц, автоматически созданных сервером на диске, во время выполнения SQL-выражений. Если значение Created_tmp_disk_tables велико, следует увеличить значение переменной tmp_table_size, чтобы временные таблицы располагались в памяти, а не на жестком диске.». Что же из этого следует?

    Панель phpMyAdmin: изменяем значения глобальных переменных

    Настройка сервера MySQL чаще всего заключается в изменении глобальных переменных. Делается это при помощи SQL-запросов. Однако перед тем как изменить значение переменной, следует посмотреть, чему значение этой переменной равно сейчас.

    В панели phpMyAdmin для этого достаточно перейти в раздел Переменные, после чего найти нужную переменную. При этом обратите внимание, что название переменной в панели phpMyAdmin пишется без знаков подчеркивания (см. рисунок). Вместо знаков подчеркивания используются пробелы.

    После того, как значение переменной найдено, перейдите в раздел SQL панели phpMyAdmin, и измените значение данной переменной при помощи SQL-запроса вида SET GLOBAL переменная = значение.

    Например, у нас в разделе Состояние красным цветом было выделено значение переменной Created_tmp_disk_tables. И в описании к этой переменной было указано, что нужно увеличить значение переменной tmp_table_size. Значит:

    — в разделе Переменные найдите переменную tmp table size, и запомните ее значение; у нас значение данной переменной было равно 33,554,432, то есть, для временных таблиц выделялось 32 Мбайта;

    — в разделе SQL вводим команду SET GLOBAL tmp_table_size = 48554432. То есть, увеличиваем буфер примерно до 48 Мбайт (размер всех буферов указывается в байтах).

    Наиболее частые проблемные участки

    Общий алгоритм оптимизации MySQL-сервера мы рассмотрели. Теперь давайте вкратце рассмотрим, какие переменные следует изменять при наличии тех или иных «красных значений».

    Handler_read_rnd, Handler_read_rnd_next, Select_full_join. Красные значения этих переменных говорят о том, что таблицы баз данных MySQL не проиндексированы, либо что ваши сайты, при запросах к таблицам, не используют возможности индексов. Решение этой проблемы будет рассмотрено в следующих статьях.

    Slow_launch_threads. Количество потоков, которые создавались очень медленно. Решение этой проблемы будет рассмотрено в следующих статьях.

    Table_locks_waited. Указывает на проблемы с SQL-запросами, которые совершают ваши сайты. Чтобы решить эти проблемы, следует оптимизировать SQL-запросы.

    Slow_queries. Количество запросов, которые выполнялись очень медленно. Для решения данной проблемы следует включить ведение лога медленных запросов, после чего оптимизировать все SQL-запросы, которые будут занесены в лог-файл. Эти действия в данной статье рассмотрены не будут.

    Created_tmp_disk_tables. Увеличьте значение переменной tmp_table_size (размер буфера в байтах). Фактически, значение данного буфера определяет максимальный размер временной таблицы, которая будет храниться в оперативной памяти. То есть, если временная таблица имеет больший размер, чем tmp_table_size, она будет располагаться на жестком диске.

    Sort_merge_passes. Увеличьте значение переменной sort_buffer_size (буфер для выполнения сортировки результатов запроса).

    Opened_tables. Увеличьте значение переменной table_cache. В данном буфере хранятся дескрипторы всех открытых сервером MySQL таблиц.

    Threads_created. Увеличьте значение переменной thread_cache_size.

    Key_reads. Увеличьте значение переменной key_buffer_size. Данный буфер является общим для всех работающих сайтов. Рекомендуется устанавливать размер данного буфера, равный 30-35% от общего количества оперативной памяти на сервере.

    Максимально одновременных. И последний совет. В разделе Переменные панели phpMyAdmin посмотрите значение переменной max connections. Если текущее количество одновременных соединений с сервером MySQL (таблица Соединения строчка Максимально одновременных) приближается к максимально возможному, следует увеличить значение этой переменной. Либо подумать над покупкой отдельного хостинга для части сайтов, работающих на данном хостинге.

    Редактирование файла my.cnf

    Изменение переменных при помощи SQL-запроса SET GLOBAL имеет один существенный недостаток – после перезапуска MySQL-сервера все сделанные вами изменения будут потеряны. Поэтому вместо ввода SQL-команд правильнее отредактировать содержимое файла my.cnf, который находится в каталоге /etc.

    Достаточно найти в этом текстовом файле раздел [mysqld], и либо ввести в нем нужные переменные и их значения (в формате переменная = значение), либо отредактировать значения уже существующих переменных.

    О редактировании данного файла читайте в будущих статьях на эту серию.

    Что делать дальше

    После того, как вы изменили значения нужных переменных, снова зайдите в раздел Состояние, и щелкните по ссылке Сбросить (перезапустить сервер MySQL мы не можем, ведь после перезапуска все сделанные нами изменения будут потеряны). Так вы обнулите все счетчики сервера. И нам останется лишь подождать день-два, чтобы посмотреть:

    — снизился ли процент неудачных и прерванных соединений;

    — исчезли ли «красные» значения переменных.

    Если «красные» значения переменных все еще есть, следует снова откорректировать значения соответствующих глобальных переменных. Ведь оптимизация работы веб-сервера – это процесс бесконечный.