Введение

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

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

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

Какие данные нужно хранить в MySQL

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

Вместо картинок лучше хранить в MySQL информацию, на основе
которой можно генерировать ссылки на статические картинки в
динамически создаваемых скриптами документах.

Оптимизация запросов

В ситуациях, когда реально требуется получить только определенную
порцию данных из MySQL, можно использовать ключ LIMIT для функции href="http://www.mysql.com/doc/S/E/SELECT.html">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 столбцам, т.к. в этом случае не
происходит затрат времени на позиционирование жесткого диска.

В каких ситуациях создание индекса целесообразно:

1. 
   
   Быстрый поиск строк при использовании
   конструкции WHERE
   
   
2. 
   
   Поиск строк из других таблиц при выполнении
   объединения
   
   
3. 
   
   Поиск значения MIN() или MAX() для
   проиндексированного поля
   
   
4. 
   
   Сортировка или группировка таблицы в случае,
   если используется проиндексированное поле
   
   
5. 
   
   В некоторых случаях полностью теряется
   необходимость обращаться к файлу данных. Если все используемые
   поля для некоторой таблицы цифровые и формируют левосторонний
   индекс для некоторого ключа, то значения могут быть возвращены
   полностью из индексного дерева с намного большей скоростью.
   
   
6. 
   
   Если выполняются запросы вида
   SELECT *
   FROM tbl_name WHERE col1=val1 AND col2=val2;
   и существует
   смешанный индекс для полей col1 и col2, то данные будут возвращены
   напрямую. Если же созданы отдельные индексы для col1 и для col2,
   то оптимизатор попробует найти наиболее ограниченный индекс путем
   определения того, какой из индексов может найти меньше строк, и
   будет использовать этот индекс для получения данных.
   Если у
   таблицы есть смешанный индекс, то будет использоваться любое
   левостороннее совпадение с существующим индексом. Например, если
   есть смешанный индекс 3-х полей (col1, col2, col3), то индексный
   поиск можно осуществлять по полям (col1), (col1, col2) и (col1,
   col2, col3).

Подробнее об индексировании: