Управление иерархическими данными в реляционных базах данных Текст научной статьи по специальности «Компьютерные и информационные науки»

УПРАВЛЕНИЕ, ВЫЧИСЛИТЕЛЬНАЯ ТЕХНИКА И ИНФОРМАТИКА

УДК 004.652

УПРАВЛЕНИЕ ИЕРАРХИЧЕСКИМИ ДАННЫМИ В РЕЛЯЦИОННЫХ БАЗАХ ДАННЫХ

© 2009 г. А.В. Маликов

Северо-Кавказский государственный North Caucasus State

технический университет, г. Ставрополь Technical University, Stavropol

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

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

Management methods for data, represented as directed graphs are suggested and analyzed. Problems of hierarchical data modification are analyzed. Performance evaluation for suggested hierarchical data modification methods is made.

Keywords: relational data bases; hierarchical structures; hierarchical data modification; performance benchmarks.

Моделирование иерархических данных в реляционных базах данных

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

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

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

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

Одним из первых методов моделирования иерархий является «модель списка смежных вершин» (adjacency list model), при использовании которой отношения, содержащие иерархические данные, дополняются атрибутами, являющимися внешними ключами к первичному ключу некоторого отношения. Данная модель очень распространена, но обладает серьезным недостатком - крайне низкая производительность выборки данных и сложность написания запросов на языке SQL, что делает не эффективным ее использование для хранения и обработки больших иерархических структур.

Большое число эффективных методов обработки иерархических структур является развитием оригинальной идеи, предложенной Джо Селко [1]. В основе его модели, названной «множественная модель дерева», лежит сопоставление каждой вершине дерева v eV, где V - множество вершин дерева, пары целочисленных значений (leftv, rightv), такой что, если u eV , u ^ v , то leftv < leftu , rightu < rightv . Для вычисления значений (lefti,righti), i = 1.. |V| используется специальный алгоритм обхода и нумерации вершин графа. Использование множественной модели дерева упрощает написание запросов на выборку данных и значительно повышает их производительность, но порождает несколько новых серьезных проблем, связанных с обеспечением эффективного выполнения операций обновления иерархических структур. Подробно данные проблемы рассматривались в [2].

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

рыи описатель, характеризующим полный путь от корня дерева к соответствующей вершине. Способ вычисления, хранения такого пути, а также тип данных для хранения его значений четко не определены и могут меняться в различных реализациях реляционных систем. Например, в [3] вводится новый тип данных на основе строкового типа данных для хранения значений материализованного пути и расширяется набор функций для выполнения основных операций модификации дерева и извлечения данных. В работе [4] для реализации идеи материализованного пути каждой вершине дерева ставится в соответствие матрица размера 2x2, содержащая целочисленные значения. Операциям модификации дерева ставятся в соответствие операции на матрицах.

Основными проблемами, присущими системам, основанным на обработке материализованных путей, являются:

1. Сложность (или полное отсутствие возможности) обработки орграфов произвольной структуры.

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

Представление орграфов в реляционной базе данных

В работе [2] предложены методы организации хранения в реляционных базах данных орграфов, которые допускают представление в виде ориентированной сети. В работе [5] представлены результаты экспериментов по хронометрированию времени выполнения запросов к таким базам данных. Проводилось сравнение между реляционной базой данных с классической структурой и базой данных со структурой, содержащей иерархические данные. При этом различным было только структурное описание данных, а информационное наполнение было взаимнооднозначным. Было показано, что время выполнения сложных запросов к базе данных со структурой, содержащей иерархические данные, более чем в 10 раз меньше, чем к базе данных с классической структурой.

Рассмотрим вопросы, связанные с модификацией данных иерархических структур; способ моделирования иерархических структур основан на работах [2, 5, 6]. Для проверки работоспособности и производительности методов были использованы:

- в качестве СУБД - MS SQL Server 2005x64 в однопользовательском режиме работы;

- в качестве аппаратной платформы - Pentium 4 4*3.66, 16 Gb of RAM, SCSI 5*70 Gb.

Пусть существует некоторая предметная область с определенной структурой. В общем виде взаимосвязи между сущностями и объектами предметной области можно описать орграфом. Орграфу сущностей ставится в соответствие ориентированная сеть с Cist источниками и Cst стоками. С целью подмены ориентированной сети сущностей деревом вводится бинарное

отношение ° - порядок обхода сущностей. Бинарное отношение ° строится на основе отношения непосредственной достижимости ^ на графе сущностей предметной области:

1. Пусть для сущностей Ai, i = 1... k , где k - количество сущностей ориентированной сети, на которые ссылается сущность B , справедливо B ^ Ai, тогда A1 о A2,A2 о A3,...,Ak оB . Следствием данного правила является факт существования наименьшего элемента A' бинарного отношения о; сущность A является одним из стоков ориентированной сети.

2. Пусть для двух произвольных сущностей An, Am и сущности B0 уровня 0 ориентированной сети

справедливо B0 ^ An , B0 ^ Am , An * Am , An ^ A ,

но не существует An ^ Am, где отношение достижимости ^ - есть рефлексивно-транзитивное замыкание отношения ^ , тогда An о Am, Am о B0.

Согласно данным правилам, всякой ориентированной сети сущностей предметной области можно сопоставить Cst! деревьев с числом листьев Cist и высотой не менее числа уровней ориентированной сети. Выбор в пользу той или иной версии дерева принимает администратор реляционной системы в соответствии с собственными знаниями о характере и интенсивности запросов к системе.

На основе сформированного дерева сущностей предметной области строится лес объектов предметной области, каждому из которых ставится в соответствие пара специальных ключей (leftKey, rightKey)

строкового типа данных. С учетом ограничений СУБД MS SQL Server 2005, накладываемых на использование индексов, тип данных был определен как varchar(900). Ключ leftKey рассчитывается для каждой вершины леса объектов и хранится в базе данных. По полю leftKey в таблице, содержащей лес объектов, строится кластерный индекс, поэтому строго желательно снизить размер значений leftKey. После построения кластерного индекса все объекты леса будут отсортированы на основе бинарного отношения лексикографической сортировки <. Данное бинарное отношение будет плотным, что позволит добавлять в лес новые объекты без пересчета ключей leftKey остальных объектов. Ключ rightKey вычисляется и

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

Пусть существует алфавит Alf мощностью MAf,

все элементы которого строго сравнимы между собой в бинарном отношении лексикографической сортировки, при этом символ a е Alf является наименьшим элементом, т.е. первым символом алфавита, а символ z е Alf является наибольшим элементом, т.е. последним символом алфавита. Каждый объект леса имеет уникальный идентификатор IDv из символов

алфавита Alf за исключением наибольшего элемента z : Alf / z .

Правила формирования ключа leftKeyv вершины v:

1. Независимым вершинам v, в том числе v е A, назначается leftKeyv = right ((replicate(a, l) + IDv), l),

где "+" - оператор конкатенации строк, replicate(x1, x2) - двухместная функция формирования символьной строки длины x2 из символов x1, right (x1, x2) - двухместная функция, возвращающая x2 правых символов строки x1.

2. Пусть согласно порядку обхода сущностей для v е Av, u е Au справедливо, что Av расположена

непосредственно перед Au, и существует вершина y е Ay (возможно y=u), такая что y ^+ u и y ^+ v, тогда leftKeyu = leftKeyv + right ((replicate(a, l) + IDv) ,l), где leftKeyv - известный leftKey вершины v. Если, согласно о, в иерархической структуре отсутствуют соответствующие кортежи для хранения вычисленного leftKeyu, то такие кортежи добавляются.

3. Частным случаем п. 2 является вычисление ключа для вершин, связанных отношением непосредственной достижимости. Пусть вершина u ссылается на v : u ^ v, а между соответствующими u и v сущностями отсутствуют другие сущности в порядке обхода, тогда u назначается ключ leftKeyu = leftKeyv + right [(replicate(a, l) + IDv ), l).

4. Вершине v назначается rightKeyv = leftKeyv + z . Ключ rightKeyv однозначно может быть рассчитан на основе leftKeyv и поэтому не хранится в таблицах

базы данных.

На рис. 1 показан пример реализации данных методов. Рассматривается простая иерархия из 3 сущностей с порядком обхода: «Город» о «Вуз» о «Кафедра». Алфавит Alf = {0,1,2,3,4,5,6} записан в порядке лексикографической сортировки <; "0" - наименьший элемент; "6" - не используемый при кодировании вершин наибольший элемент. Если l = 2, то табличное представление иерархии представлено в таблице. Данные отсортированы по атрибуту leftKey.

ID = "1"

Представление иерархии в виде таблицы

ID Значение leftKey

1 Ставрополь 01

2 СевКавГТУ 0102

4 ИСТ 010204

5 АСОУ 010205

3 СГУ 0103

Кафедра

Рис. 1. Представление иерархии в виде дерева

Теорема о ключе дерева

Если вершина v является предком u : u ^v, то предикат v.leftKey < u.leftKey and u.rightKey < v.rightKey (или только через leftKey: v.leftKey < u.leftKey and u.leftKey + z < v.leftKey + z ) будет истинным. И наоборот, если вершина v является потомком u : v ^ u , то предикат u.leftKey < v.leftKey and v.rightKey < u.rightKey (или только через leftKey: u.leftKey < v.leftKey and v.leftKey + z < u.leftKey + z ) будет истинным. Оптимизатор запросов отключит использование индекса для условия v.leftKey + z < < u.leftKey + z, так как в нем слева и справа использованы вычисляемые значения. Это приведет к значительному увеличению времени выполнения запроса в связи с полным просмотром таблицы.

Базовая теорема о ключе дерева. Если для двух вершин v и u дерева справедливо u ^ v , то leftKeyu e (leftKeyv, leftKeyv + z ),

leftKeyu + z e (leftKeyv, leftKeyv + z), т.е. (leftKeyu,leftKeyu + z) с (leftKeyv,leftKeyv + z) . И

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

(leftKeyu, leftKeyu + z )^(leftKeyv, leftKeyv + z 0 ,

то возможна только одна из трех ситуаций: (leftKeyu, leftKeyu + z ) с (leftKeyv, leftKeyv + z ) или (leftKeyv, leftKeyv + z) с (leftKeyu, leftKeyu + z ) или (leftKeyv, leftKeyv + z ) = (leftKeyu, leftKeyu + z ).

Доказательство. В основе доказательства теоремы лежат свойства лексикографической сортировки. Пусть leftKeyv < leftKeyu и в их составе присутствует минимальное подмножество l1 левых символов таких, что left (leftKeyv, l1) < left (leftKeyu, l1), где left (x1, x2 ) -двухместная функция, возвращающая x2 левых символов строки x1. Но тогда, согласно правилу формирования ключа rightKeyv, справедливо left(leftKeyv, l1) = left(rightKeyv, l1) и, следовательно, rightKeyv < leftKeyu, что означает отсутствие частичных пересечений диапазонов ключей вершин дерева в данном предположении. Пусть leftKeyv < leftKeyu и в их составе отсутствует минимальное подмножество

l1 левых символов таких, что left(leftKeyv, l1) < <left(leftKeyu,li) . Значит leftKeyv = leftQeftKeyu, где l2 - длина leftKeyv. Тогда справедливо leftKeyu + z < leftKeyv + z , так как в leftKeyu в позиции l2 +1 согласно правилу формирования ключа гарантированно находится символ меньше наибольшего элемента z алфавита Alf , что означает

(leftKeyu, leftKeyu + z ) n {leftKeyv, leftKeyv + z ) * 0 , {leftKeyu, leftKeyu + z ) с (leftKeyv, leftKeyv + z ) . Аналогично доказывается ситуация {leftKeyu, leftKeyu + z )n{leftKeyv, leftKeyv + z ) * 0 , (leftKeyv, leftKeyv + z) с {leftKeyu, leftKeyu + z )

и равенство диапазонов. Теорема доказана.

Данная теорема имеет одно важное следствие. Следствие базовой теоремы о ключе дерева. Если вершина v является предком u : u ^ v , то предикат v.leftKey < uJeflKey and u.leftKey < v.leftKey + z будет истинным. И наоборот, если вершина v является потомком u : v ^ u , то u.leftKey < v.leftKey and v.leftKey < u.leftKey + z) будет истинным. Для запроса с таким предикатом оптимизатор построит план выполнения с использованием кластерного индекса, что значительно сократит время выполнения запроса.

Графическая интерпретация базовой теоремы о ключе дерева и ее следствия представлена на рис. 2.

leftKey

v3.leftKey

v3.leftKey+z

Рис. 2. Графическая интерпретация базовой теоремы о ключе дерева

Реализация операций модификации данных в иерархических структурах

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

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

Рассмотрим простой пример, имеющий конкретную практическую направленность. Одной из основных сущностей, обрабатываемых в автоматизированных системах управления учебным процессом (в примере рассматривается «Интегрированная автоматизированная система управления "ВУЗ"», свидетельство об официальной регистрации базы данных № 2005620267 от 18.10.2005), является учебный план. С некоторыми упрощениями подмножество реляционных таблиц, соответствующих учебному плану, представлено на рис. 3.

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

Над учебным планом допустимо выполнение стандартных операций: добавление, редактирование, удаление. Обычно человек-оператор готовит временный массив данных, который во время выполнения процедуры записи переносится в постоянную базу данных. При этом производится контроль и запись множества связанных кортежей во множество таблиц. Например, при добавлении нового учебного плана эффективной будет последовательная запись данных в таблицы (от родительских таблиц к дочерним) командой: INSERT INTO таблица_в_постоянной_базе_данных (переченьполей) SELECT перечень полей FROM временная таблица

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

Дисциплина учебного плана

PK Код дисциплины

Название Всего часов

Курс

PK Код курса

Код учебного плана Курс

Семестр

PK Код семестра

FK1 Код курса Семестр

Виды учебной работы Учебные занятия

PK Код вида PK Код занятия

FK2 FK1 Код дисциплины Код семестра Название вида Количество Код вида Тема занятия

Рис. 3. Фрагмент базы данных «Учебные планы» предметной области «учебный процесс»

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

Рассмотрим рис. 3. Возможны две реализации бинарного отношения «порядок обхода сущностей о »:

1. «Курс» о «Семестр» о «Дисциплина учебного плана» о «Виды учебной работы» о «Учебные занятия».

2. «Дисциплина учебного плана» о «Курс» о «Семестр» о «Виды учебной работы» о «Учебные занятия».

При обработке атомарных значений, принадлежащих конкретному объекту предметной области, необходимо обновить ключи 1еАКеу соответствующего поддерева. Следовательно, необходимо уменьшить число операций обновления ключей, в том числе использовать методы, повышающие производительность операций обновления. При использовании бинарного отношения о в задачах модификации дерева возникает частная задача определения «родственников» вершины: ее родителей и потомков одновременно.

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

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

{v.leftKey < u.leftKey and u.leftKey < v.leftKey + z) or {u.leftKey < v.leftKey and v.leftKey < u.leftKey + z).

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

(v.leftKey < u.leftKey and u.leftKey < v.leftKey + z)

or {u.leftKey < v.leftKey and v.leftKey < u.leftKey + z) =

= {v.leftKey < u.leftKey or u.leftKey < v.leftKey) and

{v.leftKey < u.leftKey or v.leftKey < u.leftKey + z) and {u.leftKey < v.leftKey + z or u.leftKey < v.leftKey)and

{u.leftKey < v.leftKey + z or v.leftKey < u.leftKey + z) =

= TRUE and v.leftKey < u.leftKey + z and

u.leftKey < v.leftKey + z and TRUE =

= v.leftKey < u.leftKey + z and u.leftKey < v.leftKey + z .

Графическая интерпретация полученного решения представлена на рис. 4.

v.leftKey+z

ШШЩ ^ u.leftKey

u.leftKey+z ' ШШ le

v.leftKey

Рис. 4. Графическая интерпретация задачи определения для вершины v ее «родственников» {u}

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

INSERT INTO таблица_с_иерархическими_данными (атрибутсозначениями)

SELECT атомарныезначения FROM временнаятаблица

На втором этапе для добавленных атомарных значений вычисляются новые leftKey :

UPDATE таблицасиерархическимиданными SET leftKey = makeKey(ID) WHERE ID in (выборка_ добавленных_Ю),

где makeKey(ID) - функция вычисления ключа leftKey для значения ID на основе связей атомарных значений в иерархической структуре.

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

Поступила в редакцию

производительности запросов модификации информации баз данных было проведено по 10 добавлений, обновлений и удалений новых учебных планов в базу данных с плоскими отношениями (см. рис. 3), и в таблицу, содержащую иерархические данные (см. таблицу). При этом исходное информационное наполнение двух баз данных и добавляемые данные были полностью идентичными. Среднее число добавляемых кортежей: курс - 5; семестр - 10; дисциплина учебного плана - 50; виды учебной работы - 300; учебные занятия - 2000.

Среднее время добавления учебного плана в базу данных с плоскими отношениями составило 0,217 с а в таблицу, содержащую иерархические данные, -0,143 с, т.е. время добавления данных сократилось примерно на треть. Аналогичные результаты получены для операций обновления и удаления информации.

Литература

1. Celko J. Trees in SQL. 2000. URL: http://www.intelligententerprise. com/ 001020/celko. shtml

2. Маликов А., Гулевский Ю. Математическая модель хране-

ния и эффективной обработки орграфов, представленных в машинном виде // Изв. вузов. Сев.-Кавк. регион. Техн. науки. 2007. № 6.

3. Roy J. Using the Node Type to Solve Problems with Hierar-

chies in DB2® Universal Database. IBM Worldwide Sales Support. 2003. URL: http://www-106.ibm.com/ developerworks /db2/library/techarticle/0302roy/0302roy.html

4. Tropashko V. Nested Intervals Tree Encoding with Continued Fractions. Oracle Corp. 2004. URL: http://arxiv.org/abs/cs. DB/0402051

5. Malikov A., Gulevskiy Y., Parkhomenko D. Mathematical

model for storing and effective processing of directed graphs in semistructured data management systems // ARTIFICIAL INTELLIGENCE, KNOWLEDGE ENGINEERING and DATA BASES (AIKED '08): Proceedings of the 7th WSEAS International Conference. UK, Cambridge, University of Cambridge, 2008.

6. Маликов А. Проектирование реляционных баз данных на

основе операций выборки и соединения. Исследование их свойств. Ставрополь, 2002.

17 июля 2008 г.

Маликов Андрей Валерьевич - д-р техн. наук, профессор, кафедра «Информационные системы и технологии», Северо-Кавказский государственный технический университет, г. Ставрополь. Тел. (8652) 95-66-58. E-mail: malikov@ncstu. ru

Malikov Andrey Valerievich - Doctor of Technical Sciences, professor, department «Information system and technology», North Caucasus State Technical University, Stavropol. Ph. (8652) 95-66-58. E-mail: [email protected]