Каталог статей

Оптимальное использование MySQL  >  помощь веб-мастеру




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

•    Получить ссылку

  В новом окне Просмотров:[495]Добавлено:22.11.2024 Подробнее