Ключевые слова:mysql, tune, optimization, sql, database, (найти похожие документы)
From: Петр Диденко <kip@zenon.net>, Вадим Шестерин <rowdy@zenon.net>
Newsgroups: http://www.zenon.net/
Date: Mon, 18 Apr 2004 14:31:37 +0000 (UTC)
Subject: Оптимальное использование MySQL
Оригинал: http://hosting.zenon.net/art/0002.html
Введение
В процессе предоставления услуг хостинга мы обращаем внимание на
наиболее часто встречающиеся ошибки, которые совершают пользователи
при разработке своих виртуальных серверов. Одним из "тяжелых" мест для
типичного веб-мастера является работа с 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 столбцам, т.к. в этом случае не происходит затрат времени
на позиционирование жесткого диска.
В каких ситуациях создание индекса целесообразно:
1. Быстрый поиск строк при использовании конструкции WHERE
2. Поиск строк из других таблиц при выполнении объединения
Поиск значения 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 (http://www.mysql.com/doc/C/R/CREATE_INDEX.html)
Описание механизма индексирования (http://www.mysql.com/doc/M/y/MySQL_indexes.html)
Поддержка соединения
Как Вы наверняка знаете, для работы с MySQL-сервером необходимо
предварительно установить с ним соединение, предъявив логин и пароль.
Процесс установки соединения может продолжаться гораздо большее время,
нежели непосредственная обработка запроса к базе после установки
соединения. Следуя логике, надо избегать лишних соединений к базе, не
отсоединяясь от нее там, где это можно сделать, если в дальнейшем
планируется продолжить работу с SQL-сервером. Например, если Ваш
скрипт установил соединение к базе, сделал выборку данных для анализа,
не нужно закрывать соединение к базе, если в процессе работы этого же
скрипта Вы планируете результаты анализа поместить в базу.
Также можно поддерживать так называемое persistent (постоянное)
соединение к базе, но это возможно в полном объеме при использовании
более сложных сред программирования, чем php или perl в обычном
CGI-режиме, когда интерпретатор соответствующего языка разово
запускается веб-сервером для выполнения пришедшего запроса.
Другие советы
Более детально узнать о том, как оптимизировать работу MySQL, Вы
можете на страницах, ссылки на который приведены ниже.
Getting Maximum Performance from MySQL
http://www.mysql.com/doc/P/e/Performance.html
What one can and should optimize
http://www.mysql.com/information/presentations/presentation-oscon2000-20000719/index.html
Петр Диденко <kip@zenon.net>, Вадим Шестерин <rowdy@zenon.net >.
Компания Зенон Н.С.П. (http://www.zenon.net/)
