63 Какие критерии оптимизации бд можно выбрать? Бд оптимизация

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

www.internet-technologies.ru

Оптимизация работы с базой данных — Документация Django 1.6

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

Первым делом - профайлинг

Для практикующих программистов - это само собой разумеется. Первым делом определите какие запросы выполняются и как быстро. Вы также можете использовать сторонние приложения, например, django-debug-toolbar, или инструменты, которые мониторят непосредственно базу данных.

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

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

Используйте стандартные техники оптимизации БД

...включая:

Индексы. Используйте их в первую очередь, после того, как определите через профайлинг какие индексы необходимо добавить. Используйте django.db.models.Field.db_index или attr:Meta.index_together <django.db.models.Options.index_together>, чтобы добавить их через Django.
Используйте правильные типы полей.

Предположим вы уже выполнили эти очевидные вещи. Остальная часть этого раздела расскажет как оптимально использовать Django. Этот документ не описывает другие методы оптимизации, которые применимы для всех “тяжелых” операций, например кэширование.

Понимание QuerySets

Понимание QuerySets - важная часть для написания эффективного простого кода. В частности:

Понимание выполнения QuerySet

Для избежания проблем с производительностью, важно понимать:

Понимание кэширования атрибутов

Как и кэширование всего QuerySet, существует кэширование значения атрибутов в объектах ORM. В общем, не вызываемые атрибуты(not callable) будут закэшированы. Например, возьмем модель Weblog из примеров:

>>> entry = Entry.objects.get(id=1) >>> entry.blog # Blog object is retrieved at this point >>> entry.blog # cached version, no DB access

Но обращение к вызываемым атрибутам каждый раз к запросу к БД:

>>> entry = Entry.objects.get(id=1) >>> entry.authors.all() # query performed >>> entry.authors.all() # query performed again

Будьте внимательны читая код шаблонов - шаблонизатор не позволяет использовать скобки и автоматом вызывает функции и методы.

Будьте внимательны с собственными свойствами - вы должны самостоятельно реализовать кэширование.

Используйте шаблонный тэг with

Для использования кэширования в QuerySet можно использовать шаблонный тэг with.

Используйте iterator()

Если у вас очень много объектов, кэширование в QuerySet может использовать большой объем памяти. В этом случае может помочь iterator().

Выполняйте задачи базы данных в базе данных, а не в Python

Например:

Если этого не достаточно для создания необходимого SQL:

Получения объекта используя уникальное или проиндексированное поле

Есть две причины использовать поле с unique или db_index в методе get(). Первая - запрос будет быстрее т.к. будет использовать индекс в базе данных. Вторая - запрос будет на много медленнее, если несколько объектов будут удовлетворять запросу, уникальное поле исключает такую ситуацию.

По этому для нашего примера блога:

>>> entry = Entry.objects.get(id=10)

будет быстрее чем:

>>> entry = Entry.object.get(headline="News Item Title")

т.к. id проидексировано и уникально.

Следующий запрос будет скорее всего медленным:

>>> entry = Entry.objects.get(headline__startswith="News")

Во первых headline не проиндексировано и база данных будет медленнее вычислять результат.

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

Загружайте все данные сразу, если уверены, что будете использовать их.

Обращение несколько раз к базе данных для получения различных частей одного “массива” данных обычно менее эффективно, чем получение всех данных одним запросом. Это особенно важно для запросов выполняемых в цикле, что может привести к большому количеству запросов. По этому:

Не получайте данные, которые вам не нужны

Используйте QuerySet.values() и values_list()

Если вам нужен dict или list значений, а не объекты моделей ORM, используйте values(). Это можно использовать для подмены объектов моделей в шаблоне - если атрибуты словаря совпадают с используемыми атрибутами моделей, все будет хорошо работать.

Используйте QuerySet.defer() и only()

Используйте defer() и only(), если есть колонки в базе данных, которые вы не будете использовать. Запомните, что если вы все же будете их использовать, ORM сделает дополнительный запрос для их получения, что уменьшит производительность.

Заметим, что при создании моделей с “deferred” полями Django выполняет некоторую дополнительную работу. Не перестарайтесь, так как база данных читайте большинство не текстовых, не-VARCHAR данных с диска при запросе, даже если будет использоваться только несколько колонок, используйте профайлинг. Методы defer() и only() полезны, если вы можете избежать загрузки большинства текстовых полей или полей, которые долго конвертируются в объекты Python. Как всегда - используйте сначала профайлинг.

Используйте QuerySet.count()

...вместо``len(queryset)``, если вам необходимо только количество объектов.

Используйте QuerySet.exists()

...если необходимо проверить есть ли результат, вместо if queryset.

Но:

Но не переусердствуйте с count() и exists()

Если вам необходимы остальные данные из QuerySet, просто вычислите его.

Например, возьмем модель Email с полем body и связь многое-го-многому с моделью User, следующий шаблон будет оптимальным:

{% if display_inbox %} {% with emails=user.emails.all %} {% if emails %} You have {{ emails|length }} email(s) {% for email in emails %} {{ email.body }} {% endfor %} {% else %} No messages today. {% endif %} {% endwith %} {% endif %}

Он оптимальный потому что:

Так как QuerySets ленивый, запрос не будет выполнен при ‘display_inbox’ равном False.
Тег with означает что мы сохраняем user.emails.all в переменной для последующего использования.
Строка {% if emails %} вызывает QuerySet.__bool__(), который выполняет``user.emails.all()`` что приводит к запросу к базе данных, и как минимум первая строка ответа будет преобразована ORM объект. Если результат будет пуст, вернется False, иначе True.
Использование {{ emails|length }} вызывает QuerySet.__len__(), заполняя оставшийся кэш без выполнения запроса.
for выполняет цикл по уже заполненному кэшу.

В общем этот код делает один или ноль запросов к базе данных. Единственная необходимая оптимизация это использование тега with. Использование QuerySet.exists() или QuerySet.count() вызвало бы дополнительные запросы.

Используйте QuerySet.update() и delete()

Вместо загрузки данных в объекты, изменения значений и отдельного их сохранения, используйте SQL UPDATE запросы через QuerySet.update(). Аналогично используйте массовое удаление при возможности.

Однако учтите, что эти методы не вызывают save() или delete() объектов. Это означает, что логика добавленная вами в эти методы, не будет выполнена, учитывая обработчики сигналов от объектов.

Используйте значения ключей непосредственно

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

вместо:

Не сортируйте данные, если вам это не требуется

Сортировка требует ресурсы. Каждое поле, по которому производится сортировка, требует от базы данных дополнительных ресурсов. Если модель имеет сортировку по-умолчанию (Meta.ordering) и она вам не нужна, уберите её из запроса с помощью order_by() (без параметров).

Добавление индекса в вашу базу данных может улучшить производительность операции сортировки.

Используйте общее добавление

При создании объектов, если возможно, используйте метод bulk_create() чтобы сократить количество SQL запросов. Например:

Entry.objects.bulk_create([ Entry(headline="Python 3.0 Released"), Entry(headline="Python 3.1 Planned") ])

...предпочтительнее чем:

Entry.objects.create(headline="Python 3.0 Released") Entry.objects.create(headline="Python 3.1 Planned")

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

Это относится и к ManyToManyFields, это:

my_band.members.add(me, my_friend)

...предпочтительнее чем:

my_band.members.add(me) my_band.members.add(my_friend)

...где Bands и Artists связаны через многое-ко-многому.

djbook.ru

63 Какие критерии оптимизации бд можно выбрать?

Критериями оптимизации работы БД являются:

простота обслуживания – 1 администратор на 1 Тбайт данных

Основными направлениями повышения эффективности работы БД являются: оптимизация производительности БД, оптимизация кода, оптимизация работы СУБД, оптимизация структур данных, автоматизация мониторинга работы БД.

Оптимизация производительности БД

Производительность СУБД оценивается:

временем выполнения запросов;
скоростью поиска информации в неиндексированных полях;
временем выполнения операций импортирования БД из других форматов;
скоростью создания индексов и выполнения таких массовых операций, как обновление, вставка, удаление данных;
максимальным числом параллельных обращений к данным в многопользовательском режиме;
временем генерации отчетов.
Оптимизация кода запросов
Ресурсоемкие операции это запросы, содержащие операторы DISTINCT, UNION, MINUS, INTERSECT, ORDER BY или GROUP BY, которые заставляют СУБД выполнять операцию сортировки. Оператор DISTINCT требует выполнить одну операцию сортировки, другие операторы заставляют ядро выполнить как минимум две операции сортировки. Всегда следует искать другие пути выполнения подобных запросов. Большинство запросов, содержащих UNION, MINUS и INTERSECT, могут быть выполнены иными способами. Не делайте ненужных объединений (joins).
Первым шагом в оптимизации запроса должно быть исключение полного сканирования таблицы. Для первоначальной оптимизации запросов рекомендуется использовать команду EXPLAIN PLAN. Использование индексов в запросах оправдано, если запрос извлекает меньше 15% строк из таблицы. Во всех остальных случаях полный просмотр таблицы (Full Table Scan FTS) будет работать быстрее.
Одна из наиболее медленных команд в SQL это команда UPDATE. Это является следствием того, что большинство согласованных изменений в таблицах требуют полного просмотра таблиц. В результате этого эти операции являются ресурсоемкими и очень медленными, когда таблицы слишком большие.

Оптимизация работы СУБД

Для оптимизации работы СУБД существует несколько способов, это:

блокировка доступа к данным при наличии конфликтующих одновременных обращений;
использование серверов приложений;
эффективное использование оперативной памяти и памяти на дисках;
правильный выбор размера буфера ввода/вывода;
кэширование данных;
повышение эффективности работы сети;
работа с объектными файлами.

Оптимизация структур данных

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

Главными задачами системы мониторинга БД является [4]:

обнаружение неполадок и отказов;
гибкие возможности оповещения;
автоматическое исправление неполадок;
настройка на новые (нестандартные) показатели.

studfiles.net

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

Практически все современные блоги и сайты работают на движках которые используют в качестве БД MySQL, ну или не меньше 95% всех движков. Есть конечно и другие виды баз данных, но они используются в очень малом количестве движков. Про использовании в качестве базы данных простых текстовых файлов я вообще молчу. Подобные решения я встречал лиш в очень простых движках разработки многолетней давности. Подобные базы очень нестабильны и ненадёжны. Потерять все данные из текстовых файлов можно при любой перегрузке файловой системы.

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

Существуют движки в которых заранее предусмотренн процесс оптимизации БД. Один из старых движков PHP Nuke и один из новых Даннео позволяют производить оптимизацию БД прямо из админки. Причём если в Nuke это производиться по желанию вебмастера когда он об этом вспомнит, то в Даннео встроенна система оповещения о необходимости произвести оптимизацию БД. По умолчанию это нужно делать раз в 10 суток. Хотя в принципе это зависит от посещаемости сайта, от количества контента и наличия некоторых дополнительных функций. Если хотя бы иногда не производить оптимизацию БД, то во первых страницы сайта начнут открываться немного медленнее, во вторых время обращения к БД будет немного больше. И в конечном итоге нагрузка на сервер будет увеличиваться.

Есть пример из собственного опыта - попросили разобраться с одним сайтом который заметно медленно открывался и его владельцу хостер стал присылать письма счастья. Не буду называть движок, скажу что один из очень популярных последние пару лет. Я не сразу разобрался в чём дело, тестировал сам сервер на нагрузоустойчивость, включил скрипт статистики Awstats для вычисления посещаемости по логам сервера... Лиш когда я залез в БД через PHP My Admin и увидел что там твориться, я понял в чём дело. 3 года никто не оптимизировал БД! Сама база была к тому времени по обьёму более 80 мегабайт а общий обьём неоптимизированного содержимого был в районе 5-6 мегабайт. После оптимизации БД сайт стал летать а хостер забыл о перегрузке сервера.

Ну а как сделать оптимизацию таблиц в БД если сама система управления контентом (движок) Вашего сайта или блога не имеет такой функции? Очень просто, нужно вспомнить Ваш логин и пароль для доступа в панель управления аккаунтом и через него зайти через PHP My Admin в базу данных. На многих хостингах доступ к БД через PHP My Admin разрешенн и по прямой ссылке которую можно получить в панели управления. Нужно только ввести логи и пароль доступа к самой БД. В интерфейсе нужно либо вручную отметить все таблицы которые нуждаются в оптимизации, либо воспользоваться функцией - отметить таблицы нуждающиеся в оптимизации. Потом выбрать в выпадающем списке команд (это в самом низу таблицы) - оптимизировать отмеченные таблицы. Если Вы никогда этого не делали а Ваш сайт или блог достаточно посещаем и ему больше чем 6-7 месяцев, то Вы наверняка заметите увеличение скорости работы.

Интересные посты

blogroot.ru

Оптимизация БД MSSQL ~ Knowledge Base

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

Сборник рецептов собран в небольшом количестве тут

SQL скрипт, позволяющий оценить необходимость создание индекса

Cкрипт определения запроса, вызвавшего ожидание, но с большей детализацией

Определяем запрос, вызвавший ожидание на SQL сервере

Активные, готовые к выполнению и ожидающие запросы, а также те, что явно блокируют другие сеансы

По собранным статистикам можно получить самые тяжелые запросы

Но для того чтобы их применять, необходимо понимать, что же они делают. Невозможно попросту взять и налепить индексов в базе. Нужно выполнить оценку целесообразности создания того или иного индекса. В принятии решения помогает не только встроенный механизм MSSQL, Ваш личный опыт, но и программист! Да, администратор, без программиста можно наломать дров. Я понимаю, что это вечное соперничество между программистом и администратором, но до тех пор, пока программист не сможет аргументировать, что сервер неверно настроен и тормоза возникают не из-за кода, все в наших руках! Я не прошу вас мирится с программистом, а призываю найти общий язык и выявить проблему совместно. Бывают моменты, когда один из специалистов немного не понимает, что от него нужно или не хочет. Этот вариант будем считать исключением и рассмотрим идеальную ситуацию, когда все хотят решать проблему.

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

Допустим вы получили следующую рекомендацию

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

Выглядит рекомендация как

/* Отсутствуют сведения об индексе из ExecutionPlan1.sqlplan Обработчик запросов считает, что реализация следующего индекса может сократить стоимость запроса на 68.0514%. */ USE [DB] GO CREATE NONCLUSTERED INDEX [IX_Document145_VT14742_Fld14747RRef] ON [dbo].[_Document145_VT14742] ([_Fld14747RRef]) INCLUDE ([_Document145_IDRRef]) GO

Статья о блокировкахЧасть перваяЧасть вторая