Создание физической модели базы данных

Введение

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

Повышение производительности запросов: Индексы

Индексирование

Индексирование (indexing) - это способ обеспечения быстрого доступа к значениям колонки или комбинации колонок. Физически новые строки добавляются в конец таблицы, результатом чего становится неупорядоченное размещение значений в колонках. Без использования каких-либо методов упорядочения данных единственным способом просмотра значения колонки со стороны СУБД является последовательный просмотр каждой строки от начала таблицы к ее концу, так называемое сканирование таблицы. Производительность такого сканирования пропорционально размеру таблицы, размеру физической страницы базы данных и длине строки. Одним из способов внесения отношения порядка в значения колонок без нарушения физического расположения строк таблицы является создание объекта реляционной СУБД - индекса (index). Индекс - это объект в реляционной базе данных, который предназначен для организации быстрого доступа к строкам таблицы по значениям одной или более колонок этих строк.

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

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

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

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

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

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

В СУБД Oracle и SQLBase каждая строка таблицы обладает уникальным идентификатором ROWID - идентификатором строки, который представляет собой псевдоколонку с информацией о точном расположении строки в базе данных и содержит еще некоторую идентифицирующую информацию (идентификатор объекта базы данных, файла данных, блока и строки). Идентификатор строки хранится в индексе вместе со значениями ключевых полей.

Зачем создавать индекс для колонки или группы колонок? Это важный вопрос, и мы на него можем ответить следующим образом:

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

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

Чтобы решать эти задачи, проектировщик базы данных должен знать, как работает индекс, какие типы индексов поддерживает СУБД, а также понимать смысл методов индексирования.

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