40. Физическое проектировании бд. Способы повышения производительности работы с бд. Определение структуры индексов. Денормализация. Оптимизация запросов.

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

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

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

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

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

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

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

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

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

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