Ю. А. Григорьев, Г. И. Ревунков - Банки данных

12. Логическое проектирование систем распределенной обработки данных

Сначала исходный запрос S\ преобразуется в формулу реляционной алгебры. Используя законы этой алгебры, СУБД выполняет оптимизацию этой формулы, а затем преобразует оптимизированную формулу в совокуп­ ность подзапросов {Si, S3 и S^).

Подзапросы S2 и Ss могут быть выполнены параллельно в следующих случаях.

1. Сервер СУБД функционирует на SMP-компьютере и задействовано средство параллельной обработки; подзапросы выполняются на разных процессорах.

2. Таблицы «Клиент» и «Счет» хранятся на разных компьютерах и в СУБД задействовано средство распределенной обработки; подзапросы вы­ полняются на разных узлах системы. Затем таблица /?2 (она имеет меньший объем данных) передается на узел, где получена таблица R], и там выполня­ ется соединение этих таблиц.

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

•подзапросы Si и ^з выполняются параллельно,

•соединяемые таблицы R\ и Ri имеют меньшую размерность (число строк и столбцов), чем исходные таблицы клиент и счет.

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

запрос 5*5 будет соединять подтаблицы R^nR^c кодом клиента не более 500, а подзапрос 5*6 — подтаблицы RXHRIC КОДОМ клиента больше 500 и меньше 1000 (см. подзапрос S2).

Таким образом, подзапросы Ss и Se параллельно обрабатывают (на­ пример, на SMP-компьютере) как бы по половине (хотя на самом деле это может быть и не так) таблиц R\ и /?2-

Рекомендации по кодированию операторов SQL. Язык SQL отно­ сится к классу непроцедурных языков, т.е. программист указывает, что он хочет получить, но не определяет, как это сделать. Последовательность опе­ раций выполнения SQL-оператора определяет ядро СУБД. Поэтому с виду невинный SQL-оператор программы может вызвать перегрузку сервера СУБД.

Большинство рекомендаций по кодированию SQL-запросов связано с применением индексов. Поэтому прежде всего рассмотрим, как СУБД ис­ пользует индексы для поиска данных.

Пусть в БД хранится некоторая таблица Счет (рис. 12.19). Числа в этой таблице вымышленные.

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

230

Рис. 12.19. Пример первых девяти строк таблицы «Счет»

231

72. Логическое проектирование систем распределенной обработки данных

Рис. 12.20. Структура составного индекса (код клиента, тип счета)

CREATE INDEX имя индекса ON счет (код клиента, тип счета)

СУБД ведет индекс автоматически в специальном индексном файле. Индекс представляет собой совокупность иерархически связанных блоков данных, имеющих специальную структуру (рис. 12.20). Минимальное число уровней иерархии в индексе равно 3. Каждому оператору CREATE INDEX соответствует свой индекс. Рассмотрим организацию индекса.

Блоки всех уровней, кроме последнего, имеют следующую структуру: в первых двух столбцах хранятся значения атрибутов «Код клиента» и «Тип счета», а третий столбец содержит ссылку на блок индекса более высокого уровня. В третьем столбце блока последнего уровня индекса хранятся номера записей таблицы Счет. Например, номер записи 9 означает, что в 9-й записи таблицы Счет хранятся код клиента и тип счета, равные 1 и 1. Строки каж­ дого блока индекса лексикографически упорядочены (упорядочены коды) по значениям атрибутов, т.е. сначала строки упорядочены по атрибуту «Код клиента», а затем — по атрибуту «Тип счета».

Рассмотрим, как выполняется поиск записей с помощью индекса. Пусть на сервер поступил запрос:

СУБД формирует код 14 и начинает поиск с корня индекса. В корне ин­ декса выбирается первая строка, так как она единственная в блоке. Далее по

232

J2.2. Разработка прилоэюений

указателю считывается блок индекса 2-го уровня. В этом блоке выполняется поиск первой строки, значения атрибутов которой превышают код 14, и затем выполняется возврат на строку выше. В данном случае будет выбрана 1-я строка, так как во второй строке блока хранится код 21 > 14. Далее по указа­ телю считывается первый блок 3-го уровня и здесь выбираются строки, для которых выполняется неравенство 10 =^ (Код клиента. Тип счета) ^ 14. Этому требованию удовлетворяют все три строки считанного блока 3-го уровня. Таким образом, условию поиска удовлетворяют записи с номерами 9, 2, 3. Затем эти записи считываются из таблицы Счет и их поля Остаток помещают­ ся в результирующую таблицу (см. рис. 12.19).

Использование индексов для больших таблиц очевидно. Предполо­ жим, что в таблице Счет хранится 1 млн записей. Если бы таблица Счет не была индексирована по атрибутам Код клиента и Тип счета, то для провер­ ки условия серверу СУБД необходимо было бы считать 1 млн записей. В данном же случае СУБД просмотрела только три блока индекса и считала три записи.

Следует отметить, что использование индексов приводит к увеличе­ нию времени обновления таблицы. Предположим, выполняется оператор включения новой записи в таблицу Счет:

INSERT INTO счет (номер счета, код клиента, тип счета, остаток) VALUES (15, 1,3,55);

Пусть номер первой пустой записи в таблице Счет равен 10. Сделаем упрощающее предположение, что максимальное число строк в каждом бло­ ке индекса равно 3 (см. рис. 12.20). СУБД не только включает новую запись в таблицу, но и обновляет индекс.

Сначала ядро СУБД формирует код 13 (код клиента и тип счета в но­ вой записи). В индексе выполняется поиск блока 3-го уровня, где хранится строка с наименьшим кодом, удовлетворяющим условию (код клиента, тип счета) > 13. Это третья строка первого блока 3-го уровня (14 > 13). Перед этой строкой и должна быть включена новая строка (1, 3, 10). Но этот блок заполнен. В этом случае СУБД выполняет следующие действия: за­ прашивается память под еще один блок 3-го уровня, верхняя половина строк первого блока (3-го уровня) остается в нем, а нижняя строка вместе с новой переписывается в новый блок. Так как появился новый блок 3-го уровня, то в блок 2-го уровня необходимо включить новую строку с указа­ телем на этот новый блок. Но блок 2-го уровня заполнен. Поэтому созда­ ется новый блок 2-го уровня и в блок 1-го уровня помещается новая стро­ ка с указателем на этот новый блок. В результате формируется индекс, показанный на рис. 12.21.

Так как при включении новой строки в заполненный блок выполняет­ ся его «расщепление» (т.е. из одного блока получаются два), то индекс на­ зывается Б-деревом {В означает Bindery).

233

12. Логическое проектирование систем распределенной обработки данных

Рис. 12.21. Обновленный индекс

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

запрос

приведет к перебору всех записей таблицы Счет.

2. По возможности не используйте операцию NOT (не) для индекси­ рованных атрибутов. Например, запрос

будет выполняться долго, так как в этом случае СУБД будет работать по следующему алгоритму (здесь предполагается, что атрибут Номер счета индексирован):

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

234

12.2.Разработка пргтоэюений

•считать из индекса номера записей, удовлетворяющих условию «но­ мер счета = 3» (список U2);

•найти разность списков (U3 = U1 - U2), т.е. исключить из U1 номе­ ра, содержащиеся в списке U2;

•читать записи с номерами из списка U3.

При выполнении запроса

СУБД организует поиск записей по сложному индексу (код клиента, тип счета) и уже после их чтения отфильтрует записи с условием «номер счета = 3».

3. Если создан сложный индекс (см. выше), то не используйте второй атрибут отдельно от первого в условии поиска, например:

Отсюда можно было ожидать, что для выполнения запроса СУБД использу­ ет индекс. Однако, так как запрос не ссылается на первую часть сложного индекса (код клиента), СУБД не будет использовать этот индекс, что приве­ дет к перебору всех записей таблицы Счет. Использование в условии поиска атрибутов Код клиента и Тип счета или одного атрибута Код клиента при­ ведет к поиску записей с помощью индекса.

Приведем еще несколько рекомендаций по созданию индекса.

1. Индексируемая таблица не должна быть слишком маленькой. Если СУБД может записать все строки таблицы всего в несколько блоков дан­ ных, то создавать индекс нет смысла. Объем ввода/вывода при обращении к строкам таблицы от этого не уменьшится.

2.Если для работы с таблицей очень часто используются операторы INSERT, UPDATE, DELETE, то большое число индексируемых атрибутов может привести к существенному увеличению времени выполнения этих операторов из-за значительных временных затрат на модификацию индексов.

3.Если часто выполняется поиск по какой-либо группе атрибутов, то рекомендуется построить сложный индекс для этих атрибутов.

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

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

235

11END LOOP;

12CLOSE курсор;

13COMMIT;

14END;

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

1) на рабочей станции первой операционистки — проводка (100, 1001,300);

2) на рабочей станции второй операционистки — проводка (1001, 100, 150);

Конечно, эту задачу можно решить и по-другому, но здесь демонстри­ руется использование курсора, связывающего непроцедурный язык SQL с процедурным языком (в данном случае — с PL/SQL).

Прежде чем рассказать, каким образом СУБД блокирует записи, по­ ясним некоторые операторы рассмотренной процедуры:

1 — CREATE PROCEDURE: создание хранимой процедуры с пара­ метрами «HOMcpi» (номер счета «откуда»), «номерг» (номер счета «куда»), «сумма» (сумма проводки).

2 — CURSOR: декларативный оператор, который определяет курсор с именем «курсор». Курсор имеет входные параметры («номсчета!» и «номсчетаг»), а также описывает оператор SELECT для обновления (FOR UPDATE OF).

3 — декларативный оператор, который строит в оперативной памяти переменную «запись». Структура записи совпадает со структурой списка атрибутов, описанных в операторе SELECT (номер, остаток).

4 — BEGIN: начало описания тела процедуры.

5 — OPEN: открыть курсор с параметрами, получаемыми процедурой при вызове. При открытии курсора оператор SELECT после подстановки параметров выполняется ядром СУБД. Результаты поиска возвращаются обратно процедуре в виде множества записей курсора.

6 — LOOP: начать цикл по записям курсора.

7 — FETCH: скопировать поля (номер и остаток) текущей записи кур­ сора в переменную «запись».

8 — EXIT: выйти из цикла, если все записи курсора обработаны.

237

12, Логическое проектирование систем распределенной обработки данных

9 — IF: обновить поле «остаток» переменной «запись».

10 — UPDATE: обновить текущую запись курсора.

11 — END LOOP: завершить тело цикла LOOP.

12 — CLOSE: закрыть курсор.

13 — COMMIT: завершить транзакцию и снять все блокировки.

14 — END: завершить описание тела процедуры.

На рис. 12.22 показано, как с помощью блокировок СУБД корректно выполняет обновления записей таблицы Счет при одновременном обраще­ нии рабочих станций к процедуре «проводка».

Первый процесс открывает курсор, и ядро СУБД выполняет описан­ ный в этом курсоре оператор SELECT. Все найденные записи автоматиче­ ски блокируются, так как при описании курсора было указано FOR UPDATE OF. При открытии курсора другим процессом выполняется поиск записей, но так как они блокированы, выполнение оператора SELECT от­ кладывается и второй процесс переходит в состояние ожидания. После вы­ полнения первым процессом оператора COMMIT обновленные записи ста­ новятся доступными другим клиентам (т.е. перемещаются из сегмента отка­ та в таблицу Счет), записи 100 и 1001 разблокируются и выполняется опе­ ратор SELECT, связанный со вторым процессом. При этом поиск ведется уже по обновленной таблице Счет.

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

В PL/SQL для работы с транзакциями используют два оператора:

• COMMIT — это многофункциональная команда; она использует­ ся, чтобы

а) завершить предыдущую транзакцию и начать новую, б) отменить все блокировки, установленные предыдущей транзакцией,

• ROLLBACK — позволяет программным путем отменить изменения, выполненные текущей транзакцией.

Рассмотрим схему ведения транзакций (рис. 12.23).

Если программа выполняет изменение записи (программа 1), то это изменение фиксируется на диске в сегменте отката. Здесь сохраняются за­ писи до и после изменений. СУБД ведет в сегменте отката одну транзакцию для каждого активного процесса. Если другой процесс выполняет при этом поиск той же записи, то она читается не из сегмента отката, а из базы дан­ ных, т.е. до завершения транзакции этот процесс «не видит» никаких изме­ нений, выполненных первым процессом.

По команде COMMIT СУБД выполняет следующие действия.

238

Записи 100 и 1001 разблокируются

Рис. 12.22. Схема обработки двух запросов процедурой «проводка»

239