Введение
В процессе предоставления услуг хостинга мы обращаем внимание на наиболее часто
встречающиеся ошибки, которые совершают пользователи при разработке своих виртуальных
серверов. Одним из "тяжелых" мест для типичного веб-мастера является
работа с 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 - режиме, когда интерпретатор соответствующего
языка разово запускается веб-сервером для выполнения пришедшего запроса.
|