В процессе предоставления услуг хостинга мы обращаем внимание на
наиболее часто встречаюшиеся ошибки, которые совершают пользователи при
разработке своих виртуальных серверов. Одним из "тяжелых" мест для
типичного веб-мастера является работа с 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).
Подробнее об индексировании:
Синтаксис create index
Описание механизма индексирования
Поддержка соединения
Как Вы наверняка знаете, для работы с MySQL-сервером необходимо
предварительно установить с ним соединение, предъявив логин и пароль.
Процесс установки соединения может продолжаться гораздо большее время,
нежели непосредственная обработка запроса к базе после установки
соединения. Следуя логике, надо избегать лишних соединений к базе, не
отсоединяясь от нее там, где это можно сделать, если в дальнейшем
планируется продолжить работу с SQL-сервером. Например, если Ваш скрипт
установил соединение к базе, сделал выборку данных для анализа, не нужно
закрывать соединение к базе, если в процессе работы этого же скрипта Вы
планируете результаты анализа поместить в базу.
Также можно поддерживать так называемое persistent (постоянное)
соединение к базе, но это возможно в полном объеме при использовании более
сложных сред программирования, чем php или perl в обычном CGI-режиме,
когда интерпретатор соответствующего языка разово запускается веб-сервером
для выполнения пришедшего запроса.
Чтобы обсудить это в форуме, нажмите здесь. |
