Оптимальное использование MySQL
Автор: Петр Диденко, Вадим Шестерин (host.ru)
Введение
В процессе предоставления услуг хостинга мы обращаем
внимание на наиболее часто встречающиеся ошибки, которые совершают
пользователи при разработке своих виртуальных серверов. Одним из "тяжелых"
мест для типичного веб-мастера является работа с MySQL-сервером. Обычно
изучение принципов функционирования SQL и методов работы с базами данных
ведется по литературе, из которой выбираются только актуальные на момент
чтения вещи - как соединиться с базой, как сделать запрос, как обновить
информацию или добавить новую запись в базу данных и так далее.
Такой подход, конечно, дает желаемый результат -
интерфейсы веб-сайта пользователя в итоге оказываются интегрированными с
базой данных. Однако не всегда пользователи задумываются о том, насколько
оптимально работает их база, как можно оптимизировать происходящие при
работе с MySQL процессы и каково будет функционирование виртуального
сервера при увеличившейся нагрузке, "наплывах" пользователей в результате,
например, "раскрутки" сайта.
Эта статья поможет Вам оптимизировать работу с СУБД
MySQL. Изложенный материал не претендует на детальное описание оптимизации
MySQL вообще, а лишь обращает внимание на наиболее часто совершаемые
пользователями ошибки и рассказывает о том, как их избежать. Более
подробно узнать о тонкостях настройки MySQL можно на специализированных
страницах, ссылки на которые приведены в конце этой статьи.
Какие данные нужно хранить в MySQL
Не старайтесь поместить в базы данных всю информацию,
которая у Вас есть. Например, не нужно хранить там картинки, хоть MySQL
это и позволяет. Помещая в базу данных двоичные образы графических файлов,
Вы только замедлите работу своего сервера. Прочитать файл с картинкой с
диска гораздо проще и, с точки зрения потребляемых ресурсов, экономичнее,
нежели соединиться из скрипта к SQL, сделать запрос, получить образ,
обработать его и, выдав нужные http-заголовки, показать посетителю
веб-сервера. Во втором случае операция выдачи картинки потребует в
несколько раз больше ресурсов процессора, памяти и диска. Также стоит
помнить о том, что существуют механизмы кэширования веб-документов,
которые позволяют пользователю экономить на трафике, а при динамической
генерации контента Вы фактически лишаете своих посетителей этой удобной
возможности.
Вместо картинок лучше хранить в MySQL информацию, на
основе которой можно генерировать ссылки на статические картинки в
динамически создаваемых скриптами документах.
Оптимизация запросов
В ситуациях, когда реально требуется получить только
определенную порцию данных из MySQL, можно использовать ключ LIMIT для
функции SELECT. Это полезно, когда, например, нужно показать результаты
поиска чего-либо в базе данных. Допустим, в базе есть список товаров,
которые предлагает Ваш интернет-магазин. Выдавать весь список товаров в
нужной категории несколько негуманно по отношению к пользователю - каналы
связи с интернет не у всех быстрые и выдача лишних ста килобайт информации
зачастую заставляет пользователей провести не одну минуту в ожидании
результатов загрузки страницы. В таких ситуациях информацию выдают
порциями по, допустим, 10 позиций. Неправильно делать выборку из базы всей
информации и фильтрацию вывода скриптом. Гораздо оптимальнее будет сделать
запрос вида
select good, price from
books limit 20,10
В результате, MySQL "отдаст" Вам 10 записей из базы
начиная с 20-й позиции. Выдав результат пользователю, сделайте ссылки
"Следующие 10 товаров", в качестве параметра передав скрипту следующую
позицию, с которой будет делаться вывод списка товаров, и используйте это
число при генерации запроса к MySQL.
Также следует помнить, что при составлении запросов к
базе данных (SQL queries) следует запрашивать только ту информацию,
которая Вам реально нужна. Например, если в базе 10 полей, а в данный
момент реально требуется получить только два из них, вместо запроса
select * from
table_name
используйте конструкцию вида
select field1, field2 from
table_name
Таким образом, Вы не будете нагружать MySQL ненужной
работой, занимать лишнюю память и совершать дополнительные дисковые
операции.
Также следует использовать ключ WHERE там, где нужно
получать информацию, попадающую под определенный шаблон. Например, если
нужно получить из базы поля с названиями книг, автором которых является
Иванов, следует использовать конструкцию вида
select title from books
where author='Иванов'
Также есть ключ LIKE, который позволяет искать поля,
значения которых "похожи" на заданный шаблон :
select title from books
where author like 'Иванов%'
В данном случае MySQL выдаст названия книг, значения поля
author у которых начинаются с 'Иванов'.
Ресурсоемкие операции
Вместе с тем следует помнить, что существуют операции,
выполнение которых само по себе требует больших ресурсов, чем для обычных
запросов. Например, использование операции DISTINCT к функции SELECT
вызывает потребление гораздо большего количества процессорного времени,
чем обычный SELECT. DISTINCT пытается искать уникальные значения, зачастую
производя множество сравнений, подстановок и расчетов. Причем, чем больше
становится объем данных, к которому применяется DISTINCT (ведь Ваша база
со временем растет), тем медленее будет выполняться такой запрос и рост
ресурсов, требуемых для выполнения такой функции, будет происходить не
прямо пропорцонально объему хранимых и обрабатываемых данных, а гораздо
быстрее.
Индексы
Индексы используют для более быстрого поиска по значению
одного из полей. Если индекс не создается, то MySQL осуществляет
последовательный просмотр всех полей с самой первой записи до самой
последней, осуществляя сопоставление выбранного значения с исходным. Чем
больше таблица и чем больше в ней полей, тем дольше осуществляется
выборка. Если же у данной таблицы существует индекс для рассматриваемого
столбца, то MySQL сможет сделать быстрое позиционирование к физическому
расположению данных без необходимости осуществлять полный просмотр
таблицы. Например, если таблица состоит из 1000 строк, то скорость поиска
будет как минимум в 100 раз быстрее. Эта скорость будет еще выше, если
есть необходимость обратиться сразу ко всем 1000 столбцам, т.к. в этом
случае не происходит затрат времени на позиционирование жесткого диска.
В каких ситуациях создание индекса целесообразно:
-
Быстрый поиск строк при использовании конструкции
WHERE
-
Поиск строк из других таблиц при выполнении
объединения
-
Поиск значения MIN() или MAX() для
проиндексированного поля
-
Сортировка или группировка таблицы в случае, если
используется проиндексированное поле
-
В некоторых случаях полностью теряется необходимость
обращаться к файлу данных. Если все используемые поля для некоторой
таблицы цифровые и формируют левосторонний индекс для некоторого ключа,
то значения могут быть возвращены полностью из индексного дерева с
намного большей скоростью
-
Если выполняются запросы вида
SELECT * FROM tbl_name
WHERE col1=val1 AND col2=val2;
и существует смешанный индекс для полей col1 и col2, то
данные будут возвращены напрямую. Если же созданы отдельные индексы для
col1 и для col2, то оптимизатор попробует найти наиболее ограниченный
индекс путем определения того, какой из индексов может найти меньше строк,
и будет использовать этот индекс для получения данных.
Если у таблицы есть смешанный индекс, то будет
использоваться любое левостороннее совпадение с существующим индексом.
Например, если есть смешанный индекс 3-х полей (col1, col2, col3), то
индексный поиск можно осуществлять по полям (col1), (col1, col2) и (col1,
col2, col3).
Поддержка соединения
Как Вы наверняка знаете, для работы с MySQL-сервером
необходимо предварительно установить с ним соединение, предъявив логин и
пароль. Процесс установки соединения может продолжаться гораздо большее
время, нежели непосредственная обработка запроса к базе после установки
соединения. Следуя логике, надо избегать лишних соединений к базе, не
отсоединяясь от нее там, где это можно сделать, если в дальнейшем
планируется продолжить работу с SQL-сервером. Например, если Ваш скрипт
установил соединение к базе, сделал выборку данных для анализа, не нужно
закрывать соединение к базе, если в процессе работы этого же скрипта Вы
планируете результаты анализа поместить в базу.
Также можно поддерживать так называемое persistent
(постоянное) соединение к базе, но это возможно в полном объеме при
использовании более сложных сред программирования, чем php или perl в
обычном CGI-режиме, когда интерпретатор соответствующего языка разово
запускается веб-сервером для выполнения пришедшего
запроса.