Порядок расположения столбцов

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

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

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

В общем случае, часто изменяемые столбцы лучше размещать рядом.

Создание ссылочных ограничений

С точки зрения реляционной теории связи между отношениями устанавливаются косвенно: по совпадению значений первичного и внешнего ключа.

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

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

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

Факторы, влияющие на производительность БД

Индексы

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

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

В простейшем случае индекс можно представить следующей таблицей:

 

Таблица - Структура простейшего индекса

Значение индексного выражения	№ записи
Алексеев
Петрова
…	…

 

СУБД, как правило, поддерживает несколько типов индексов.

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

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

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

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

В реляционных СУБД система сама определяет, надо использовать индекс при выполнении запроса или нет. В том случае, если СУБД не находит индекс, то скорее всего произойдёт последовательный просмотр записей. В случае поиска единичных записей (например, вывести сведения о конкретном человеке) – это очень дорогостоящая операция. Поэтому на этапе создания физической модели базы данных необходимо проанализировать запросы и определить, сколько и каких индексов необходимо создать.

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

Рассмотрим некоторые рекомендации по определению состава индексов.

Индексы создаются для первичных ключей.

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

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

Если таблица большая и запрос затрагивает менее 25% строк, то желательно создать индекс по условию поиска.

Если таблица большая, но запрос затрагивает больше 25% строк, то требуется более серьёзный анализ с учётом особенностей конкретной СУБД. Можно попытаться выяснить, будет ли СУБД использовать при выполнении запроса индекс, системными средствами, например, с помощью команды Show plan или аналогичной ей.

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

Если, например, в одних запросах для поиска используется столбец ШифрТовара, в других ШифрТовара и ЦветТовара, то составной индекс ШифрТовара, ЦветТовара удовлетворит оба типа запросов.

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

Использование индексов позволяет избежать такой дорогостоящей операции, как физическая сортировка строк таблицы.

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

Для хранения индексов требуется дополнительное место на диске и дополнительные файлы, что может создать проблемы, так как ОС способна поддерживать определённое количество файлов.

Использование индексов требует от администратора правильного использования буферов и дисков (например, индексы и таблицы – в разных буферах при считывании и физическое хранение на разных дисках).

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

 

 

Денормализация

Нормализация предполагает хранение каждого факта в одном месте.

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

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

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

Денормализации лучше избегать, если

- система может достичь приемлемой производительности без денормализации;

- денормализация не улучшает производительность;

- система становится менее надёжной в работе.

 

При выполнении денормализации рекомендуется учитывать следующие моменты:

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

если места на диске нет, то создаётся только денормализованная база, при этом её обновление всегда выполняется программно или с помощью хранимых процедур;

если приложение создаётся для работы с денормализованными таблицами, то желательно учесть в нём будущую возможность работы с нормализованной базой.

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