Технологии бд теоретические основы организации бд. Реляционная модель данны



страница15/20
Дата23.04.2016
Размер2.56 Mb.
1   ...   12   13   14   15   16   17   18   19   20

3.9.Физическое проектирование баз данных

3.9.1.Учет особенностей используемого сервера БД


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

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

В большинстве систем индекс создается автоматически для каждого определенного в таблице первичного, возможного и внешнего ключа. Здесь никуда не денешься: потребность в определении ключей следует из семантики предметной области, а для поддержания и использования ключей СУБД нуждается в соответствующих индексах. Но вот что касается дополнительных индексов, вводимых для целей более эффективного выполнения запросов, то с ними нужно быть очень аккуратным. Требуется тщательный предварительный анализ наиболее важного набора запросов (к сожалению далеко не всегда это возможно). Нужно также отдавать себе отчет в том, что создание нового индекса для большой заполненной таблицы - это серьезная дорогостоящая операция (как правило, СУБД выполняет сортировку строк таблицы в соответствии со значением ключевого атрибута).

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


3.9.2.Противоречия теории и практики нормализации


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

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

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

3.9.3.Денормализация для оптимизации


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

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


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


Оптимизация запросов к БД связана с построением адекватной запросам и оптимальной структуры индексов таблиц БД и оптимизации собственно текстов запросов.
Оптимальная структура индексов

От структуры индексов таблиц БД в огромной степени зависит эффективность выполнения запросов. При выполнении запросов сервер БД обычно сначала просматривает список индексов, определенных для участвующих в запросе таблиц. Затем выбирается лодна из двух схем выполнения запроса – использовать имеющиеся индексы или последовательно просмотреть таблицы. Оптимизатор сервера стремиться выполнить запрос с максимальным быстродействием и с минимальными накладными расходами.
«Полезность» индекса

Эффективность использования индекса при поиске информации в таблице БД сильно зависит от того, построен ли индекс по уникальным значениям и, если нет, насколько отличаются данные, по которым он построен.

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

Пусть необходимо выбрать из таблицы из таблицы RASHOD все записи о расходе товара за 10.10.2006, у которых количество расходуемого товара превышает 300 единиц.

SELECT *

FROM RASHOD

WHERE DAT_RASH = “10.10.2007” AND

KOLVO > 300

При выполнении запроса сервер БД определяет – есть ли индексы, построенные одновременно по столбцам DAT_RASH и KOLVO, столбцам KOLVO и DAT_RASH? Или индексы, в которые указанные столбцы входят в качестве ведущих.(например, индекс, построенный по столбцам DAT_RASH, KOLVO,TOVAR, но не индекс, построенный по столбцам DAT_RASH, TOVAR, KOLVO). При отсутствии таких индексов проверяется наличие индексов отдельно по столбцам DAT_RASH и KOLVO .

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

Показатель полезности индекса рассчитывается как число различающихся значений индексных полей внутри индекса, отнесенное к среднему количеству записей. Для участия в выполнении запроса выбираются индексы с максимальным уровнем полезности. Такие индексы обеспечивают более быстрый поиск. Максимальным индексом полезности обладают уникальные индексы, т.е. индексы, построенные по определениям первичных и уникальных ключей.

В некоторых случаях оптимизатор сервера БД не может корректно определить полезность индекса. В решения этой проблемы в диалектах языка SQL существуют способы принудительного использования того или иного индекса при выполнении запроса.

Целесообразность создания индексов

Индексы необходимо создавать в случае, когда по столбцу или группе столбцов:

  • Часто производится поиск в БД;

  • Часто строятся объединения таблиц;

  • Часто производится сортировка;

  • Часто производится сортировка;

Не рекомендуется строить индексы по столбцам или группам столбцов, которые:

  • Редко используются для поиска, объединения , сортировки результатов запроса

  • Часто меняют значение, что приводит к необходимости часто обновлять индекс и способно существенно замедлить скорость работы с БД;

  • Содержит небольшое число вариантов значения
Частичное использование составного индекса

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

Следует помнить, что при использовании в запросах не всех столбцов из индекса, можно использовать только непрерывную последовательность столбцов, что важно для указания порядка сортировки в предложении ORDER BY.

Порядок следования условий по столбцам в предложении WHERE оператора SELECT не важен (если условия объединяются по AND).

Многопоточность поиска по OR и IN

При частом использовании в условной части WHERE оператора SELECT нескольких столбцов, между собой операцией «или» (OR)Ж

SELECT *

WHERE A=100 OR B=200 OR C= 300

вместо индекса по столбцам A,B,C лучше создать несколько индексов, построенных по каждому из этих полей, поскольку в противном случае будет осуществлен последовательный просмотр всей таблицы.

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

Отдельный поток поиска порождает и каждый элемент в списке IN. Например,



WHERE A IN (100, 200, 300)

интерпретируется как



WHERE A=100 OR A=200 OR A=300 .

Однако при указании диапазона BETWEEN



WHERE A BETWEEN 100 AND 300

этого не происходит. Поэтому, где возможно, следует заменять IN на BETWEEN.




Уменьшение общего количества индексов.

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

3.9.5.Оптимизация клиентских приложений

Минимизация соединений с БД

Каждое соединение с БД потребляет системные ресурсы, и их чрезмерный расход может сказаться на эффективности доступа к БД. Поэтому рекомендуется снижать число соединений с удаленной БД к минимуму, а в идеале иметь одно соединение с каждой БД.
Использование SQL-ориентированных компонентов при построении клиентских приложений

Использование SQL-ориентированных компонентов (например, в Delphi компонент TQuery вместо TTable) позволяет уйти от «навигационного способа» обработки данных, который был характерен для локальных и файл-серверных СУБД, и, как следствие, решает задачу снижения трафика в вычислительной сети.
Перенос тяжести вычислительной работы на сервер

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

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

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

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

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

1   ...   12   13   14   15   16   17   18   19   20


База данных защищена авторским правом ©bezogr.ru 2016
обратиться к администрации

    Главная страница