Оптимальное использование MySQL. Некоторые советы.

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


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

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

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

В ситуациях, когда реально требуется получить только определенную порцию данных из базы, можно использовать ключ LIMIT для функции SELECT. Это полезно, когда, например, нужно показать результаты поиска чего-либо в базе данных. Допустим, в базе есть список товаров, которые предлагает Ваш интернет-магазин. Выдавать весь список товаров в нужной категории несколько негуманно по отношению к пользователю - каналы связи с интернет не у всех быстрые и выдача лишних ста килобайт информации зачастую заставляет пользователей провести не одну минуту в ожидании результатов загрузки страницы. В таких ситуациях информацию выдают порциями, например, по 20 позиций. Неправильно делать выборку из базы всей информации и фильтрацию вывода скриптом. Гораздо оптимальнее будет сделать запрос вида:

SELECT good, price FROM books LIMIT 10,20

В результате, MySQL "отдаст" Вам 20 записей из базы начиная с 10-й позиции. Выдав результат пользователю, сделайте ссылки "Следующие 20 товаров", в качестве параметра передав скрипту следующую позицию, с которой будет делаться вывод списка товаров, и используйте это число при генерации запроса к 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 table_name WHERE col1=val1 AND col2=val2

и существует смешанный индекс для полей col1 и col2, то данные будут возвращены напрямую. Если же созданы отдельные индексы для col1 и для col2, то оптимизатор попробует найти наиболее ограниченный индекс путем определения того, какой из индексов может найти меньше строк, и будет использовать этот индекс для получения данных.

Если у таблицы есть смешанный индекс, то будет использоваться любое левостороннее совпадение с существующим индексом. Например, если есть смешанный индекс 3-х полей (col1, col2, col3), то индексный поиск можно осуществлять по полям (col1), (col1, col2) и (col1, col2, col3).

Как Вы наверняка знаете, для работы с MySQL-сервером необходимо предварительно установить с ним соединение, предъявив логин и пароль. Процесс установки соединения может продолжаться гораздо большее время, нежели непосредственная обработка запроса к базе после установки соединения. Следуя логике, надо избегать лишних соединений к базе, не отсоединяясь от нее там, где это можно сделать, если в дальнейшем планируется продолжить работу с SQL-сервером. Например, если Ваш скрипт установил соединение к базе, сделал выборку данных для анализа, не нужно закрывать соединение к базе, если в процессе работы этого же скрипта Вы планируете результаты анализа поместить в базу.

Читать комменты и комментировать

Добавить комментарий / отзыв



Защитный код
Обновить

Оптимальное использование MySQL. Некоторые советы. | | 2012-02-05 15:41:07 | | Статьи Web-мастеру | | Одним из тяжелых мест для типичного веб-мастера является работа с MySQL-сервером. Обычно изучение принципов функционирования SQL и методов работы с базами данных ведется по литературе, из которой | РэдЛайн, создание сайта, заказать сайт, разработка сайтов, реклама в Интернете, продвижение, маркетинговые исследования, дизайн студия, веб дизайн, раскрутка сайта, создать сайт компании, сделать сайт, создание сайтов, изготовление сайта, обслуживание сайтов, изготовление сайтов, заказать интернет сайт, создать сайт, изготовить сайт, разработка сайта, web студия, создание веб сайта, поддержка сайта, сайт на заказ, сопровождение сайта, дизайн сайта, сайт под ключ, заказ сайта, реклама сайта, хостинг, регистрация доменов, хабаровск, краснодар, москва, комсомольск |
 
Поделиться с друзьями: