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

Поделиться в соц. сетях

mailru Оптимальное использование MySQL
facebook Оптимальное использование MySQL
odnoklassniki Оптимальное использование MySQL
livejournal Оптимальное использование MySQL
googlebuzz Оптимальное использование MySQL

Также рекомендуем:

  1. Построение дерева иерархии с помощью PHP / MySQL Рассмотрим пример построения дерева иерархии (в развернутом виде) на основе информации из базы данных с помощью PHP и MySQL. Ключ к решению данной задачи – использование рекурсивной функции. Иерархия разделов будет храниться в таблице базы данных MySQL. . Ниже на скриншоте показана данная таблица (catalogue): id – первичный ключ таблицы pid – id родительского раздела [...]...
  2. Переполнение буфера и раскрытие данных в MySQL Программа: MySQL 4.1.18, и более ранние версии.MySQL 5.0.20 . Опасность: Средняя Наличие эксплоита: Да Описание: Уязвимость позволяет удаленному авторизованному пользователю получить доступ к важным данным и скомпрометировать уязвимую систему. 1. Уязвимость существует из-за недостаточной обработки данных в команде COM_TABLE_DUMP. Удаленный пользователь может с помощью специально сформированного пакета получить доступ к важным данным на системе (к [...]...
  3. MySQL – это просто! Сначала ответим на вопрос – что такое MySQL? Это База Данных (БД), в которой структурировано хранятся данные. Следует второй вопрос – зачем отказываться от простых и удобных файлов к сложной БД? Потому что файлы, генерируемые каким либо скриптом на основе файловой БД (гостевые книги, CMS, форумы), постепенно начинают увеличиваться в размере, а так же их [...]...
  4. Вышел MySQL 5.1.30, первый стабильный релиз серии 5.1 После публикации 29 тестовых версий анонсирован первый стабильный релиз MySQL 5.1, пригодный для промышленной эксплуатации и обеспечивающий увеличение производительности для «тяжелых» SQL запросов, по сравнению с MySQL 5.0, примерно на 15-20%. Главные новшества появившиеся в MySQL 5.1: . Сегментирование – возможность разбить одну большую таблицу на несколько частей, размещенных в разных файловых системах, основываясь на [...]...
  5. Сравнение хранилищ данных для MySQL с поддержкой транзакций В 48-страничной презентации «Landscape of Transactional Storage Engines» представлены результаты исследования производительности доступных для MySQL хранилищ данных, имеющих поддержку транзакций. Раскрыты особенности работы и устройство Innodb, Falcon, PBXT и SolidDB....
  6. Работа с SQLite Введение SQLite – это реляционная база данных, запросы к которой можно осуществлять при помощи языка запросов SQL. База данных не поддерживает все особенности SQL и уступает в функциональности другим развитым СУБД, но вполне подходит для хранения и извлечения информации.. Отличие SQLite от MySQL и аналогичных СУБД Классические СУБД, такие как MySQL (а так же MS [...]...
  7. Металлическое кольцо . Создайте новое изображение (ширина и высота должны быть кратные двум, дальше вы увидите почему). (У некоторых с этим эффектом возникают проблемы – необходимо, чтобы созданное изображение было в формате RGB). Заполните фон черным цветом. Теперь возьмите направляющие сверху и слева и установите их в центре изображения. У вас должно получиться перекрестие как на рисунке. [...]...
  8. Обзор платных хостингов Для начала разберёмся, что такое хостинг. Хостинг – это услуги размещение Вашего сайта в интернете. В 2002 году появилось очень много новых компаний предоставляющие хостинг. Так вот, в этой статье я опишу подробно новые и старые хостинги.. 100GIG (http://www.100gig.com/) Русский хостинг, на котором нужно платить только за трафик, 1000 MB. Трафика стоит 1$. На каждый [...]...
  9. В PHP 5.3 появился ActiveRecord аналогичный Ruby on Rails Поиски решения активной записи в php были изнурительны до того момента, как один из источников в google не предложил вариант ActiveRecord аналогичный Ruby on Rails. Читатель заметит, что вышеупомянутые результаты слишком устаревшие и большая часть из них мало общего с действенностью.. В конце концов, PHP получит более надежный способ активной записи аналогичный способу применяемому в [...]...
  10. Текст из воды Step 1Создайте документ и напишите любой текст. Растеризуйте слой.. Используя кисть, добавьте несколько капель и подтеков к тексту. Объедините оба слоя Layer > Flatten Image. Step 2Идем в палитру каналов и создаем маску канала путем перетаскивания любого слоя Red, Green или Blue на иконку New Channel внизу палитры каналов. Называем его water. Далее инвертируем канал [...]...
  11. Выгравированный текст Очень простой туториал наглядно показывающий, как с помощью «эффектов слоя» (layer effects) сделать выгравированный текст на произвольной текстуре.. Создайте новое изображение 200×200 @72 dpi. Возьмите любую текстуру на ваш выбор, или, если есть желание используйте приведённую выше.Продублируйте слой текстуры. У вас должно быть два слоя, выглядящие как один.Выберите инструмент outline text. Напишите любую букву, лучше [...]...
  12. Пулевые отверстия 1. Создадим новое изображение 512×512 px, 72 dpi. 2. Нажмем D, чтобы установить цвета палитры по умолчанию (черно-белый).Создадим новый слой.Filter > Render > CloudsFilter > Render > Difference Clouds 3. Filter > Blur > Gaussian Blur (радиус 4 px)Filter > Sketch > Chrome: Detail- 10, Smoothness- 0 4. Image > Adjustments > Levels 5. Edit [...]...
  13. Лист бумаги Часть первая. 1. Создаем новый документ, называем Paper. 2. Выбираем Rectangular Marquee Tool (М) 3.Выделим весь лист. 4. Выделенное заполняем белым. 5. Заходим Layer>Layer Style>Drop Shadow-Opacity 20%-Distance 2-Spread 0 -Size 1 6. Нажимаем на Stroke.-Size 1px -Outside -Opacity 5% (Внимание! Для каждого размера листа, свои опции!) 7. Выбираем Elliptical Marquee Tool (M) 8. Зажимаем Shift [...]...
  14. Для тех, кто хочет больше,чем HTML Для тех, кто хочет самостоятельно научиться локально делать сайты, программировать, используя языки: php, sgi, да еще с поддержкой базы данных (mysql). Все это Вы сможете, если установите локальный сервер на своим компъютере. . В этой статье я не даю подробных инструкций и комментариев, так как это получится целый талмуд. Я сам не супер-мупер пользователь, да [...]...
  15. Заснеженный пейзаж Открываем исходное изображение в рабочем пространстве (RGB).. Создаем снег. 1) Заходим в палитру каналы Channels, дублируем синий канал и инвертируем его (Ctrl+i) 2) В копии синего канала используем команду Select > Color Range, пипеткой выбираем светло-серый цвет на основном изображении, параметр Fuzziness устанавливаете исходя из изображения, в данном случае использован парамет Fuzziness = 110. 3) [...]...

Комментарии запрещены.