Оптимизация производительности баз данных для Web

Чтобы получить от базы данных с открытым кодом максимум производительности, нужны глубокие знания.

Вряд ли найдется web-приложение, которое не опиралось бы на ту или иную базу данных. Если вы не располагаете достаточным бюджетом или просто являетесь приверженцем программных продуктов с открытым кодом, то, по всей вероятности, будете разрабатывать свои приложения на базе гипертекстового процессора php (php hypertext processor) и какой-нибудь базы данных с открытым кодом. В таком случае вам следует ознакомиться с методами, позволяющими выжимать из баз данных все, на что они способны. В этой статье мы рассматриваем некоторые методики повышения производительности, которые подойдут практически для любой базы данных с открытым кодом.

oптимизация на уровне базы данных

Самым быстрым способом повышения производительности программного кода базы данных является замена встроенных в него операторов sql на хранимые процедуры.

Использование хранимых процедур

Хранимые процедуры -- это подпрограммы, содержащиеся в базе данных. Такие процедуры предварительно компилируются процессором базы данных и существенно повышают производительность последней, исключая многократные обращения вызывающего приложения (как правило, это страница php) к ядру базы данных.

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

Кроме того, по сравнению с выполнением sql-кода в таких средах, как php, asp (active server page) и jsp (java server pages), а также в средах некоторых других языков разработки хранимые процедуры позволяют заметно снизить интенсивность сетевого трафика. И наконец, если у вас возникнет необходимость в масштабировании приложения, гораздо проще расширить его код на несколько прикладных серверов, когда большая часть логики доступа к базе данных хранится и выполняется в рамках самой базы данных.

Хранение мультимедийных файлов в файловой системе

Мультимедийные файлы, будь то статические изображения, звуковые файлы или фильмы, часто рассматривают как двоичные объекты. Для них даже имеется специальный термин: большие двоичные объекты -- blob (binary large object). Поля blob можно хранить либо в базе данных, либо в файловой системе. В последнем случае пути к объектам blob хранятся в базе данных. Хранение объектов blob в файловой системе потребует от вас чуть больше работы, зато позволит добиться гораздо более высокой производительности, чем при хранении их в базе данных.

С увеличением числа сохраняемых двоичных объектов производительность процессора базы данных быстро уменьшается. Кроме того, удаление таких объектов может привести к образованию в файлах базы данных большого числа "мертвых зон". Когда вся информация от и до проходит через процессор базы данных, ему труднее поддерживать многозадачный режим работы. Хранение объектов blob в файловой системе, напротив, облегчает создание ссылок на загружаемые из web-страниц объекты. После загрузки информации web-сервер обслуживает обращение к файлу, а процессор базы данных занимается в это время другими задачами. Дополнительным преимуществом также является и то, что администратор может легко каталогизировать и администрировать мультимедийные файлы, записанные на диск, а также делать их резервные копии.

Использование индексации

Индексирование -- один из наиболее верных способов наращивания производительности базы данных. Кроме того, он входит в число основных механизмов базы данных, которому обычно уделяется незаслуженно мало внимания. Как правило, строки базы данных хранятся в том порядке, в каком создаются. Для извлечения из записи базы данных некоторой произвольной величины требуется последовательное сканирование соответствующих строк базы данных. Индекс создает отдельное множество строк, упорядоченных в соответствии с выбранным индексом и содержащих указатели на исходные строки. Индексированная база данных просматривается значительно быстрее, чем неиндексированные таблицы. Однако индексирование "съедает" дополнительное дисковое пространство. Кроме того, на модификацию индексированной таблицы требуется больше времени, поскольку все применяемые индексы тоже приходится корректировать.

Использование целочисленных ключевых полей

Возможно, у вас есть соблазн при создании таблиц обойтись без целочисленного ключевого поля. Например, в таблице записей, содержащих информацию о персонале, вы могли бы использовать символьные поля last_name и first_name, а также поля для адреса и контактной информации, при объединении записей, их просмотре и других операциях с ними использовать имена полей. Мы не советуем вам придерживаться такой практики. Лучше используйте числовое ключевое поле -- к примеру, person_id. Если ваши данные не содержат такого поля, вы должны создать автоинкрементное поле, которое не будет содержать никаких реальных данных и будет лишь выполнять роль ключевого поля.

Числовые поля имеют множество преимуществ. Вероятность ошибочного использования числа гораздо меньше вероятности ошибочного использования имени. При изменении имени человека (например, ввиду вступления в брак) вам не потребуется изменять в своем коде все ссылки на него. Кроме того, объединение записей, имеющих числовое ключевое поле, выполняется гораздо эффективнее, чем объединение записей с текстовым ключевым полем. Следует взять за практику создавать числовое поле первичного ключа при формировании каждой новой таблицы

Оптимизация прикладного кода

Чтобы добиться максимального повышения производительности базы данных, можно использовать несколько разных стратегий оптимизации программного кода приложения. Ниже мы приводим ряд рекомендаций по оптимизации кода, которые применимы для любого языка разработки web-приложений.

Использование сеансов связи

Сеансы связи поддерживаются несколькими средами разработки web-приложений. Обычно сеансы связи, крайне популярные среди поставщиков прикладного сервиса и программистов php, реализуются посредством пересылки маркеров cookies. Поскольку web не является средой, использующей информацию о состоянии, она не предоставляет программистам никакой информации о том, что мог делать пользователь перед тем, как "зашел" на данную страницу. С помощью сеансов связи программист может отслеживать процесс навигации пользователя. Как побочный эффект многие программисты стремятся сохранить всю информацию о нем в переменных сеанса. Хотя сохранение ссылок на базу данных, таких, как соединения или наборы записей, в переменной сеанса является делом заманчивым, это довольно плохая привычка. Запоминание соединений в ходе сеансов связи препятствует их объединению в пул. Кроме того, такая практика приводит к разбазариванию памяти и вычислительной мощности ЦПУ.

Если сеансы связи не завершаются корректно, соединения продолжают существовать в течение всего тайм-аута. Продолжительность тайм-аута может варьироваться, но в большинстве случаев она больше 20 мин. В течение всего этого времени оперативная память и вычислительная мощность ЦПУ растрачиваются вхолостую. Хотя может показаться, что открытие и последующее закрытие соединения на web-странице приводит к пустой трате ресурсов, на деле это способствует эффективному их сохранению. Достаточно лишь придерживаться "железного" правила: создавать соединение как можно позже и закрывать его как можно раньше. Это же правило применимо и к наборам записей.

Использование оптимальных запросов

Способ использования операторов sql может существенно повлиять на производительность вашего web-приложения. В частности, извлечение из базы данных длинного списка записей для отображения на одной непрерывной web-странице является нерациональным. Вам следует взять за один раз только часть записей (скажем, 10 или 50), а затем, чтобы отобразить следующую группу записей, использовать кнопку "Следующие 50" (next 50) или ссылку на web-странице.

При написании такого кода, старайтесь в полной мере использовать все преимущества языка sql. Например, ключевое слово limit (предельное число) ограничивает число возвращаемых записей. Ключевое слово offset (смещение) пропускает определенное число записей, возвращая следующие за ними записи. Чтобы вернуть третью группу пользовательских записей в количестве 50 штук, вам следует использовать запрос следующего вида:

select customer_id, customer_name from customer order by customer_id limit 50 offset 100.

Если таблицы содержат большое число столбцов или вы используете в запросах операции соединения, не следует употреблять оператор select * лишь для того, чтобы оградить себя от необходимости печатать наименования полей. Печатание нужных вам имен полей позволяет сэкономить несколько циклов ЦПУ при каждом запуске кода.

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

Использование оператора выбора

Для формирования запроса, требующего принятия решения на основе результата его выполнения, вы можете использовать один из двух способов. Наиболее очевидный, но и более медленный способ -- выполнить запрос и проверить его результат с помощью своего прикладного кода. Более квалифицированный и более выгодный способ -- использовать продвинутые функции языка sql. Оператор case языка sql, как и аналогичные операторы большинства других языков разработки, может делать выбор на основе значения входного параметра. В таком случае результат оператора select можно контролировать, используя оператор case, как это сделано в следующем примере:

select product_name,
case
when price < 5 then 'cheap'
when price > 5 and price < 20 then 'ok'
else 'too expensive for my taste'
end as product_price from products order by product_name;

В результате будем иметь набор записей, состоящий из двух столбцов:
первый столбец содержит наименование продукта, второй -- избранную нами интерпретацию цены.


***
mysql

В силу исходных характеристик и истории разработки базы данных mysql ей присущи некоторые специфические проблемы производительности. Например, наивысшей производительности mysql можно добиться на машинах intel, работающих под управлением ОС linux. Этому есть немало причин, но главная из них заключается в способе распределения оперативной памяти системы. Таким образом, если вы выбрали mysql, то не используйте microsoft windows nt, и наоборот.

Бытует мнение, что mysql -- исключительно быстродействующая база данных. Многие даже утверждают, что по скорости она значительно превосходит любую другую базу данных из имеющихся в продаже. Компания tcx, активно продвигающая mysql на рынок, организовала web-узел (http://www.tcx.com), на котором сопоставляет ее с другими базами данных и приводит результаты сравнения ее производительности для разных программных платформ. В соответствии с этими результатами производительность процессора базы данных mysql превосходит производительность всех других процессоров баз данных в среднем на 40%. И все же давайте смотреть на вещи с точки зрения здорового научного скептицизма.

Дело в том, что такая высокая производительность mysql дается нам отнюдь не даром, а за счет того, что она не поддерживает транзакции. Транзакции заметно снижают производительность процессора базы данных. Кроме того, они требуют поддержки журнальных файлов, позволяющих выполнять поэтапную отмену изменений, проделанных при транзакциях, или полностью аннулировать последние. Системный администратор должен терпеливо "нянчиться" с файлом регистрации и следить за тем, чтобы он не достиг слишком большого размера. К тому же, вместе с резервным копированием базы данных необходимо выполнять и резервное копирование журнальных файлов.

Ограничения

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

Некоторые программисты тратят больше времени на то, чтобы ввести в код ограничения, а потом отлаживать их и бороться с проблемами, обусловленными их нарушением, чем на разработку и оптимизацию логики своих программ. Кроме того, если вы не используете ограничения, у вас больше шансов на то, что разрабатываемое вами приложение можно будет переносить на другие платформы.

Типы таблиц, используемые в mysql

В mysql используется четыре типа таблиц: статические, динамические, heap ("куча") и сжатые. Согласно руководству по эксплуатации mysql, статические таблицы самые быстрые из трех типов таблиц, размещаемых на дисках. Зато они не могут содержать столбцы переменной длины. Если в таблице имеется хотя бы один такой столбец, mysql вынуждена создавать динамическую таблицу вместо статической. Динамические таблицы содержат гораздо больше данных, особенно касающихся размера таблицы, но значительно медленнее статических таблиц.

Два остальных табличных типа являются специальными. Таблицы типа heap существуют только в оперативной памяти и потому их можно считать исключительно быстродействующими. Но таблицы этого типа бывают лишь небольшого или среднего размера. Сжатые таблицы предназначены только для считывания и тоже очень быстродействующие. В руководстве по эксплуатации mysql (http://www.mysql.com/doc) вы найдете дополнительную информацию, касающуюся всех табличных типов.

Мертвое пространство mysql

Когда данные столбцов переменной длины изменяются, причем новые данные имеют более короткую длину, в информационных файлах mysql образуется "мертвое" пространство. Программного решения, которое позволило бы справиться с этой проблемой, не существует. Другая проблема производительности сопряжена с обычным использованием индексов из-за постепенной деградации последних. В ПО mysql имеется инструментальное средство myisamchk, позволяющее высвободить мертвое пространство и переоптимизировать индексы. Эту программу следует периодически запускать по отношению к базе данных, чтобы держать последнюю под контролем.

postgresql

В отличие от mysql база данных postgresql поддерживает хранимые процедуры, ограничения и транзакции. Разработчики этой базы данных не пошли по пути сокращения ее функциональных возможностей ради повышения производительности. Зато богатый набор функций postgresql имеет свои преимущества. В postgresql есть две оригинальные встроенные функции, которые можно использовать для повышения ее производительности, -- команды vacuum и explain.

Когда postgresql модифицирует строку, она сохраняет исходную строку, а в конце своего внутреннего файла данных создает новую. Старая строка помечается как устаревшая и используется другими транзакциями, которые все еще используют предыдущее состояние базы данных, существовавшее до того, как была предъявлена текущая транзакция. Тот же процесс имеет место и при удалении строк. Команда vacuum удаляет устаревшие строки из базы данных и уплотняет ее. Чтобы "содержать базу данных в чистоте", эту команду следует запускать периодически.

Важным методом отладки кода является оптимизация способа выполнения запроса. Если, пытаясь идентифицировать узкие места составленного вами запроса, вы будете просто визуально просматривать его, то очень скоро поймете, что это ни к чему вас не приведет. Чтобы исключить такой примитивный подход, большинство баз данных оснащены функцией анализа, которая не выполняет запрос, а лишь анализирует его для вас. В postgresql такая функция называется explain. Вот пример ее использования:

explain select phone_number from people where id=930;
notice: query plan: seq scan on people (cost=0.00..30.50 rows=20 width=15)

База данных сообщает, что требуется последовательное сканирование.

Численные значения стоимости (cost) приводятся лишь для сравнения. Величина rows представляет собой предполагаемое число строк, которое должно быть возвращено запросом. Последняя величина -- width -- это ширина строки в битах. Если вы запустите команду vacuum по отношению к этой базе данных, то, вероятнее всего, заметите улучшение производительности, предсказываемое функцией explain. Более того, создав индекс для столбца phone_number, вы ускорите выполнение запроса, заменив последовательное сканирование сканированием по индексу.

Статьи по теме

Оптимизация и тюнинг производительности MariaDB MySQL сервера внутри Docker

Содержание: Установка и знакомство с MySQLTuner Установка MySQL Tuning Primer Script Первичная оптимизация MySQL сервера ...

2023-11-08

Работа с базами данных MySQL в ISPManager 5 + Docker

В ISPManager 5 добавлена контейнерная виртуализация Docker для использования альтернативных версий MySQL. Цитата из документации ISPManager 5: ...

2023-10-27

«Облачные» хранилища данных: в ожидании доверия

Информационные технологии активно мигрируют в сторону «облаков». Эксперты прогнозируют, что одним из главных приоритетов для CIO в ближ...

2011-11-05

ГОСТ 34.602-89. на Техническое задание по разработке web проекта

ТЕХНИЧЕСКОЕ ЗАДАНИЕ НА СОЗДАНИЕ АВТОМАТИЗИРОВАННОЙ СИСТЕМЫ Information technology. Set of standards for automated systems. Technical directions ...

2010-12-28

Orange color: теория и практика

Знаете ли Вы, чем оранжевый цвет отличается от других ? Если нет, то знайте, что разница в скорости восприятия глазом отдельных цветов ра...

2010-12-27

FAQ по MS SQL 7.0

11. Как запускать-останавливать SQL Server через командную строку? Мне это нужно для использования в скриптах.Остановка:NET STOP MSSQLSERVERили если вы...

2010-09-17

Виртуальная реализация SQL Server 2005

Ранее в материале «Разбираем “айсберг” sql server 2005» читателям предлагалось воспользоваться опубликованной в internet серией информационных...

2010-09-17

SQL Server в вопросах и ответах

В: У меня на разных компьютерах установлено два sql server 7.0. Для обмена данными между ними я использовал data transformation services (dts), чтобы импортиро�...

2010-09-17

Один из вариантов соглашения об именах объектов MS SQL Server

Помните, что имена объектов sql сервера не могут превышать 30 символов. Не используйте зарезервированные слова, специальные символы, и не н...

2010-09-17

Создание и удаление БД в MS SQL Server

В sql server'е базы данных хранятся в виде обычных файлов на диске. Как минимум на одну БД приходится таких файлов 2: *.mdf и *.ldf. В первом хранятся...

2010-09-17

Создание и удаление таблиц в MS SQL Server

Все данные в БД sql server хранятся в таблицах.Таблицы состоят из колонок, объединяющих значения одного типа, и строк - записей в таблице. В од�...

2010-09-17

Единая аутентификация Windows NT/2000 и Oracle

При использовании Windows NT/2000 как платформы для работы Oracle, и на стороне сервера и на стороне клиентов, имеется возможность использовать ед...

2010-09-17

Oracle / PHP FAQ

Что такое PHP и что необходимо иметь для работы с Oracle?PHP - рекурсивный акроним "PHP Hypertext Preprocessor". Это интерпретируемый язык программирования ...

2010-09-17

Oracle и PHP - это очень просто

ВведениеСУБД Oracle, когда против желания фирмы-изготовителя, а когда согласно – в зависимости от направления политических ветров в конкр�...

2010-09-17

Сжатие данных в целях экономии места и ускорения работы Oracle

Используйте сжатие данных таблицы для экономии места на диске и повышения производительности запросов. В большинстве систем поддержки ...

2010-09-17