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