Читать интересную книгу Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ - Хелен Борри

Тема оптимизации: использование внутренних возможностей

Firebird наследует недокументированную возможность, которая может ускорить выполнение запроса при некоторых условиях. Это RDB$DB_KEY (обычно называется просто db key), внутренний ключ, поддерживаемый сервером базы данных для внутреннего использования при оптимизации запросов и управлении версиями записей. Внутри контекста транзакции, где он используется, он представляет позицию строки в таблице8[118].

Относительно RDB$DB_KEY

Первый урок заключается в том, что RDB$DB_KEY является прямым указателем, связанным с базой данных, а не с физическим адресом на диске. Второй - значения RDB$DB_KEY не следуют в предсказуемой последовательности. Не используйте вычисления, включающие их относительные позиции! Третий урок в том, что они изменчивы - они изменяются после резервного копирования и последующего восстановления, а иногда и после подтверждения транзакции. Главным является понимание мимолетности db key и отсутствие предположений о его существовании в то время, когда ссылающаяся на него операция завершается или отменяется.

Размер RDB$DB_KEY

Для таблиц RDB$DB_KEY использует 8 байт. Для просмотров он использует коэффициент умножения этих 8 байт, сколько таблиц используется в просмотре. Например, если просмотр соединяет три таблицы, его RDB$DB_KEY использует 24 байт. Это важно, когда вы работаете с хранимыми процедурами и собираетесь сохранять RDB$DB_KEY В переменных. Вы должны использовать тип данных CHAR(n) корректной длины.

По умолчанию db key возвращается в виде шестнадцатеричного числа - две шестнадцатеричные цифры представляют каждый байт: 16 шестнадцатеричных цифр возвращаются для 8 байт. Сделайте для одной из ваших таблиц в isql следующее:

SQL> SELECT RDB$DB_KEY FROM MYTABLE;

RDB$DB KEY

000000B600000002

000000B600000004

000000B600000006

000000B600000008

000000B60000000A

Преимущества

Поскольку RDB$DB KEY напрямую указывает на место хранения записи, он будет быстрее для поиска, чем первичный ключ. Если по каким-то причинам в таблице нет первичного ключа или активного уникального индекса, или уникальный индекс допускает пустые значения, то возможно существование полных дубликатов строк. В этих условиях RDB$DB_KEY является единственным способом точной идентификации каждой строки.

Некоторые виды операций выполняются быстрее в хранимой процедуре при использовании RDB$DB_KEY- обычно в случаях изменения и удаления при сложных условиях. Для добавлений (даже при огромных пакетах) RDB$DB_KEY недоступен, потому что не существует способа определить заранее, какими будут значения.

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

Оптимизация запросов

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

UPDATE TABLEA А

SET A.TOTAL = (SELECT SUM (B.VALUEFIELD)

FROM TABLEB В

WHERE B.FK = A.PK)

WHERE <условия...>

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

CREATE PROCEDURE ...

. .

AS

BEGIN

FOR SELECT B.FK, SUM(B.VALUEFIELD) FROM TABLEB В

GROUP BY B.FK

INTO :B_FK, : TOTAL DO

UPDATE TABLEA A SET A.TOTAL = :TOTAL

WHERE A.PK = :B_FK

AND ...

END

Хотя это и быстрее, тем не менее остается проблема, что записи в А выбираются по первичному ключу каждый раз, когда выполняется проход по циклу FOR ... DO.

Некоторые люди получают лучший результат при этом необычном синтаксисе:

. . .

DECLARE VARIABLE DBK CHAR (8);

/* 8 символов для db_key таблицы А */

. . .

FOR SELECT B.FK,

SUM(B.VALUEFIELD) ,

A. RDB$DB_KEY

FROM TABLEB В

JOIN TABLEA A ON A.PK = B.FK

WHERE <условия>

GROUP BY B.FK, A.RDB$DB_KEY

INTO :B_FK, :TOTAL, :DBK DO

UPDATE TABLEA SET A.TOTAL = :TOTAL

WHERE A.RDB$DB_KEY = :DBK;

! ! !

ПРИМЕЧАНИЕ. В Firebird нет необходимости в ключевом столбце для создания соединения, однако он нужен в списке SELECT, чтобы предложение GROUP BY было допустимым.

. ! .

Перечислим преимущества такого подхода.

* Фильтрация общих записей для А и В будет эффективной, когда оптимизатор может создать хороший фильтр для явного соединения.

* Если соединение может применить свое собственное предложение поиска, то существует выгода от выполнения фильтрации до того, как изменение будет проверять свое собственное условие.

* Строки таблицы правой стороны (А), локализованные с помощью прямых указателей db key, выделяются во время соединения быстрее, чем при просмотре первичного ключа или его индекса.

Добавления

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

Однако не все входные значения(VALUE2) оператора INSERT получаются так просто.

Это может быть очень сложным набором значений, полученным из выражений, со-

единений или агрегатных операций. В хранимой процедуре операция INSERT может разветвляться в предложении ELSE предиката IF (EXISTS (...)), например:

IF EXISTS(SELECT...) THEN

. . .

ELSE

BEGIN

INSERT INTO TABLEA

SELECT

C.PKEY,

SUM (B. AVALUE) ,

AVG(B.BVALUE),

COUNT(DISTINCT C.XYZ)

FROM TABLEB B JOIN TABLEC С

ON B.X = C.Y

WHERE C.Z = 'value'

AND С.PKEY NOT IN(SELECT PKEY FROM TABLEA)

GROUP BY С.PKEY;

END

. . .

Реализация этого в хранимой процедуре:

FOR SELECT

С.PKEY,

SUM(B.AVALUE),

AVG(B.BVALUE),

COUNT(DISTINCT C.XYZ)

FROM TABLEB B JOIN TABLEC С

ON B.X = C.Y

WHERE C.Z = 'value'

AND С.PKEY NOT IN(SELECT PKEY FROM TABLEA)

GROUP BY С.PKEY

INTO :C_KEY, :TOTAL, :B_AVG, :C_COUNT DO

BEGIN

SELECT A.RDB$DBKEY FROM TABLEA A

WHERE A.PKEY = :C_KEY

INTO :DBK;

IF (DBK IS NULL) THEN /* строка не существует */

INSERT INTO TABLEA(PKEY, TOTAL, AVERAGE_B, COUNT_C)

VALUES(:C_KEY, :TOTAL, :B_AVG, :C_COUNT);

ELSE

UPDATE TABLEA SET

TOTAL = TOTAL + :TOTAL,

AVERAGE_B = AVERAGE_B + :B_AVG,

COUNT_C = COUNT_C + :C_COUNT

WHERE A. RDB$DB_KEY = : DBK;

END

Длительность действия

По умолчанию областью действия db key является текущая транзакция. Вы можете считать, что он остается правильным во время действия текущей транзакции. Подтверждение или откат транзакции приведет к тому, что значения RDB$DB_KEY станут непредсказуемыми. Если вы используете commitRetaining, контекст транзакции сохраняется, блокируя сборку мусора и, следовательно, предотвращая "переназначение" старого db_key. При этих условиях значения RDB$DB_KEY для любых используемых строк в вашей транзакции сохраняются действительными, пока не произойдет "жесткое" подтверждение или откат.

После жесткого подтверждения или отката другая транзакция может удалить строку, которая была изолирована внутри контекста вашей транзакции и, следовательно, рассматривалась как "существующая" в вашем приложении. Любое значение RDB$DB_KEY теперь может указывать на несуществующую строку. Если у вас достаточно большой интервал между моментом, когда начинается ваша транзакция и когда завершается ваша работа, вы должны проверять, не была ли за это время строка изменена или заблокирована другой транзакцией.

Некоторые интерфейсы приложений, например IB Objects, являются суперинтеллектуальными в плане добавлений и могут подготовить "сегмент" для вновь добавленных строк в клиентских буферах для быстрого обновления списка после подтверждения. Такие возможности важны для производительности при работе в сети. Однако "интеллектуальность", подобная этой, основывается на точных реальных ключах. Поскольку db_key является просто заменителем ключа для набора, наследуемого от предыдущих подтвержденных данных, он не имеет смысла для новой строки - он не доступен при изменениях в клиентском буфере.

Изменение величины длительности действия

Значение длительности действия по умолчанию для RDB$DB_KEY можно изменить во время соединения с базой данных, используя параметр API isc_dpb_dbkey_scope. Некоторые разработки - например, компоненты IB Objects в инструментах окружения Borland Object Pascal - предоставляют его в классе соединения. Однако не рекомендуется расширять область действия db key в высоко интерактивной среде, поскольку это остановит сборку мусора, приводя к нежелательному росту размера файла базы данных и замедлению работы системы вплоть до ее зависания или краха. Не используйте соединения, имеющие область действия для db key, отличающуюся от значения по умолчанию.

RDB$DB_KEY в многотабличных наборах

Все таблицы поддерживают свои собственные 8-байтовые столбцы RDB$DB_KEY. Просмотры и соединения во время выполнения генерируют db key путем конкатенации RDB$DB_KEY из строк исходных таблиц. Если вы используете RDB$DB_KEY в многотабличных наборах, будьте особенно внимательны при задании каждого из них.