Предыдущая часть

Особенности работы с Microsoft SQL Server в Delphi 5
(Часть 3)

У Анатолий Тенцер
Статья была опубликована в "КомпьютерПресс", №6-2001

• Блокировки
• Обработка транзакций
• Соответствие стандарту ANSI SQL 92
• Модель безопасности
  • Роли
  • Интегрированная безопасность
• Оптимизатор запросов Microsoft SQL Server

Блокировки

Microsoft SQL Server поддерживает блокировку на уровне записи для всех операций модификации данных. Если оптимизатор решит, что количество блокируемых записей в таблице слишком велико, то он может произвести эскалацию блокировок на группу страниц или на всю таблицу. Это происходит, например, при одновременном обновлении значительного количества записей. В подобном случае гораздо удобнее заблокировать таблицу (или группу страниц в ней), внести изменения, а затем разблокировать ее, вместо того, чтобы накладывать блокировку на каждую запись. Сервер не предоставляет средств для управления эскалацией блокировок и осуществляет ее автоматически.

Другой важной проблемой является модель обеспечения уровней изоляции транзакций REPEATABLE READ и SERIAZABLE. При выполнении транзакции с этим уровнем изоляции сервер блокирует диапазоны значений полей, по которым осуществляется выборка данных для предотвращения вставки «фантомных» значений. Например, если в транзакции с уровнем изоляции SERIAZABLE будет выполнен запрос

SELECT * FROM MyTable WHERE Name BETWEEN ‘A’ AND ‘C’

то сервер наложит блокировку по записи (Shared Lock) на диапазон значений, попавших в результат запроса, предотвращая тем самым вставку «фантомных» записей другими транзакциями. Блокировка будет удерживаться до конца транзакции. На измененные транзакцией записи накладывается блокировка по чтению (Exclusive Lock), предотвращающая чтение их другими транзакциями. Поэтому транзакции с высокими уровнями изоляции необходимо тщательно планировать и делать их максимально короткими.

Обработка транзакций

В Microsoft SQL Server поддерживаются все определенные стандартом ANSI SQL 92 уровни изоляции транзакций:

Для установки текущего уровня изоляции используется оператор

SET TRANSACTION ISOLATION LEVEL
     {
          READ COMMITTED
          | READ UNCOMMITTED
          | REPEATABLE READ
          | SERIALIZABLE
     }

Момент начала транзакции регулируется установкой

SET IMPLICIT_TRANSACTION ON|OFF

По умолчанию она установлена в ON, и каждый оператор выполняется в отдельной транзакции. По его завершении неявно выполняется COMMIT. Если необходимо выполнить транзакцию, состоящую из нескольких операторов, ее надо явно начать командой BEGIN TRANSACTION. Заканчивается транзакция оператором COMMIT или ROLLBACK.

Например:

INSERT MyTable VALUES (1)
     -- Выполнился внутри отдельной транзакции
     BEGIN TRANSACTION
     -– Начали явную транзакцию
     INSERT MyTable VALUES (2)
     INSERT MyTable VALUES (3)
     COMMIT
     –- завершили явную транзакцию
     При выдаче команды

     SET IMPLICIT_TRANSACTION OFF

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

Транзакция продолжается до тех пор, пока не будет выдана команда COMMIT или ROLLBACK.

Возможно создание вложенных транзакций. При этом функция @@TRANCOUNT показывает глубину вложенности транзакции. Например:

BEGIN TRANSACTION
     SELECT @@TRANCOUNT -- Выдаст 1
     BEGIN TRANSACTION
     SELECT @@TRANCOUNT -- Выдаст 2
     COMMIT
     SELECT @@TRANCOUNT -- Выдаст 1
COMMIT
SELECT @@TRANCOUNT -- Выдаст 0

Вложенный BEGIN TRANSACTION не начинает новую транзакцию. Он лишь увеличивает @@TRANCOUNT на единицу. Аналогично вложенный оператор COMMIT не завершает транзакцию, а лишь уменьшает @@TRANCOUNT на единицу. Реальное завершение транзакции происходит, когда @@TRANCOUNT становится равным нулю. Такой механизм позволяет писать хранимые процедуры, содержащие транзакцию, например:

CREATE PROCEDURE Foo
AS BEGIN
     BEGIN TRANSACTION
     INSERT MyTable VALUES (1)
     INSERT MyTable VALUES (1)
     COMMIT
END

При запуске вне контекста транзакции процедура выполнит свою транзакцию. Если она запущена внутри транзакции, внутренние BEGIN TRANSACTION и COMMIT просто увеличат и уменьшат счетчик транзакций.

Оператор ROLLBACK ведет себя по-иному. Он всегда, независимо от текущего уровня вложенности, устанавливает значение переменной @@TRANCOUNT равным нулю и отменяет все изменения, от начала самой внешней транзакции. Если в хранимой процедуре возможен откат ее действий исходя из какого-то условия, можно использовать точки сохранения (savepoint)

CREATE PROCEDURE Foo
     AS BEGIN
          BEGIN TRANSACTION
          -- Этот оператор не может быть отменен вне контекста
          -- основной транзакции
          INSERT MyTable VALUES (1)
               SAVE TRANSACTION InsideFoo
               -- Операторы, начиная отсюда, могут быть отменены
               -- без отката основной транзакции
               INSERT MyTable VALUES (2)
               INSERT MyTable VALUES (3)
               IF (SELECT COUNT(*) FROM MyTable) > 3
                    ROLLBACK TRANSACTION InsideFoo
                    -- Отменяем изменения, внесенные после
                    -- последнего savepoint
          COMMIT
END

Отдельного обсуждения заслуживает команда ROLLBACK, вызванная в триггере.

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

CREATE TABLE MyTable (Id INTEGER)

     GO

     CREATE TRIGGER MyTrig ON MyTable FOR INSERT
     AS BEGIN
     IF (SELECT MAX(Id) FROM Inserted) >= 2 BEGIN
          ROLLBACK
          RAISERROR(‘Id >= 2’, 17, 1)
     END
END

GO

INSERT MyTable VALUES (1)
INSERT MyTable VALUES (2) – Вызовет ROLLBACK в триггере
-- Операторы, начиная отсюда, не выполнятся
INSERT MyTable VALUES (3)
INSERT MyTable VALUES (4)

Соответствие стандарту ANSI SQL 92

В Microsoft SQL Server имеются настройки, позволяющие изменять степень соответствия сервера стандарту ANSI SQL 92.

SET ANSI_NULLS {ON|OFF} — регулирует результат сравнения значений, содержащих NULL. Если ANSI_NULLS = OFF, то запрос

SELECT * FROM MyTable WHERE MyField = NULL

вернет все строки, в которых MyField установлено в NULL. Если ANSI_NULLS = OFF, то в соответствии со стандартом ANSI SQL92 сравнение с NULL возвращает UNKNOWN. Другие установки, на которые следует обратить внимание:

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

Параметр SET ANSI_DEFAULTS устанавливает режим максимальной совместимости с ANSI SQL 92. При установке SET ANSI_DEFAULTS ON устанавливаются в ON следующие параметры:

По умолчанию ANSI_DEFAULTS = ON для клиентов ODBC и OLE DB (ADO) и OFF для клиента DB-Library (BDE). Поскольку предпочтительным (и поддерживаемым в будущем) методом доступа является OLE DB, то при разработке клиентской части, использующей BDE, рекомендуется явно устанавливать SET ANSI_DEFAULTS ON. С различиями в значении этого параметра связана и проблема, возникающая при разработке запросов посредством Query Analyzer. Если в нем и в клиентском приложении имеются разные настройки совместимости с ANSI, одни и те же запросы могут выдавать разные результаты. Поэтому рекомендуется проверять настройки Query Analyzer на предмет их соответствия тем, которые предполагаются в клиентском приложении.

Модель безопасности

Роли

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

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

Интегрированная безопасность

Сильной стороной Microsoft SQL Server является его тесная интеграция с системой безопасности Windows NT. Права на доступ к серверу и базам данных можно давать пользователям и группам Windows NT. Механизм делегирования полномочий позволяет пользователям, подключившимся к одному из серверов, иметь доступ к другим серверам в сети со своими правами, отличающимися от прав сервера, к которому они подключились. Также возможна прозрачная для пользователя проверка его полномочий при доступе к серверу через Microsoft Internet Information Server или Microsoft Transaction Server.

Оптимизатор запросов Microsoft SQL Server

В версии 7.0 существенно переработан оптимизатор запросов. Сервер может использовать несколько индексов на каждую таблицу в запросе; один запрос может исполняться параллельно на нескольких процессорах. В SQL Server 7.0 реализованы три метода выполнения операции слияния таблиц (JOIN):

1. LOOP JOIN — для каждой записи в одной из таблиц производится цикл по связанным записям второй таблицы. Этот метод наиболее эффективен для малых результирующих наборов данных.
2. MERGE JOIN — требует, чтобы оба набора данных были отсортированы по сливаемому полю (набору полей). В этом случае сервер осуществляет слияние за один проход по каждому из наборов данных. Поскольку они уже упорядочены, нет необходимости просматривать все записи, достаточно выбирать их начиная с текущей, пока значение поля не изменится. Это самый быстрый метод слияния больших наборов данных.
3. HASH JOIN используется, когда невозможно использовать MERGE JOIN, а наборы данных велики. По одному из наборов строится хэш-таблица, а затем для каждой записи из второго набора вычисляется та же хэш-функция и производится ее поиск в таблице. В случае больших неотсортированных наборов данных этот алгоритм существенно эффективнее, чем LOOP JOIN.

При фильтрации по индексу сервер не осуществляет сразу выборку данных из таблицы. Вместо этого строится набор «закладок» (Bookmark), а затем производится выборка данных в одной операции (Bookmark Lookup). Это позволяет резко снизить количество обращений к диску.

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

CREATE TABLE T1 (
     Id INTEGER PRIMARY KEY,
     ...
     )

     CREATE TABLE T2 (
     Id INTEGER PRIMARY KEY,
     ...
     )

     CREATE TABLE T3 (
     Id INTEGER PRIMARY KEY,
     T1Id INTEGER REFERENCES T1(Id),
     T2Id INTEGER REFERENCES T2(Id),
     ...
     )
запрос

SELECT *
     FROM T1
          INNER JOIN T3 ON T1.Id = T3.T1Id
          INNER JOIN T2 ON T2.Id = T3.T2Id
WHERE ...

может быть существенно ускорен созданием индексов:

CREATE INDEX T3_1 ON T3(T1Id, T2Id)

После слияния T3 с T1 он позволяет получить упорядоченный по T2Id набор данных, который может быть слит с T2 путем эффективного алгоритма MERGE JOIN. Впрочем, лучший эффект, возможно, даст индекс:

CREATE INDEX T3_2 ON T3(T2Id, T1Id)

Это зависит от количества записей в T1, T2 и распределения их сочетаний в T3. В OLAP-системе (или в слабо загруженном OLTP-приложении) лучше построить оба этих индекса, в то время как при интенсивном обновлении таблицы T3, возможно, от одного из них придется отказаться. Сервер может сам выдать рекомендации по построению индексов — для этого в него включен Index Tuning Wizard, доступный через Query Analyzer. Он анализирует запрос (или поток команд, собранный при помощи SQL Trace) и выдает рекомендации по структуре индексов в конкретной базе данных.

В процессе работы с Microsoft SQL Server в оптимизаторе запросов автором были обнаружены два «тонких» места , которые рекомендуется учитывать.

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

DECLARE @I INTEGER

     SET @I = 10

     SELECT *
          FROM History H
                    INNER JOIN Objects O ON O.Id = H.ObjectId
          WHERE H.StatusId = @I

Сервер сгенерировал следующий план исполнения:

Внимание: в качестве параметра выступает переменная, при этом сервер не может точно оценить, в какой диапазон статистики она попадет. В этом случае он делает предположение, что количество записей, полученных из History, будет равно средней селективности по используемому полю, помноженной на количество записей в таблице (в данном случае – 10 151). Исходя из этого выбирается алгоритм слияния HASH JOIN, требующий значительных накладных расходов на построение хэш-таблицы. В случае если реальное количество записей ощутимо меньше (реально этот запрос выбирает 100-200 записей, имеющих соответствующий StatusId за последний день), алгоритм LOOP JOIN дает во много раз большую производительность. Итак, если вы точно знаете, что фильтрация по конкретному полю даст ограниченный набор данных (не более нескольких сотен записей), а сервер об этом «не догадывается», — укажите ему алгоритм слияния явно.

SELECT *
     FROM History H
          INNER LOOP JOIN Objects O ON O.Id = H.ObjectId
     WHERE H.StatusId = @I

Делать это надо, только если вы уверены, что этот запрос будет выполняться со значениями параметра, имеющими высокую селективность. На больших наборах данных выполнение LOOP JOIN будет гораздо медленнее.

2. Цена операции Bookmark Lookup (извлечение данных из таблицы по известным значениям индекса) явно завышена. Поэтому иногда, даже при наличии подходящего индекса, вместо INDEX SCAN (поиск по индексу) с последующим Bookmark Lookup (выборка из таблицы) сервер принимает решение о полном сканировании таблицы (TABLE SCAN или CLUSTERED INDEX SCAN). Пример такого запроса приведен на рисунке. Обратите внимание на предполагаемую стоимость запроса (Estimated subtree cost) для случая, когда для таблицы явно задан поиск по индексу: она чрезвычайно завышена. Видно, что 100% расчетной стоимости выполнения дает операция Bookmark Lookup. Реально же этот запрос быстрее выполняется при индексном доступе, чем при сканировании таблицы. В этом случае рекомендуется попробовать явно указать индекс для доступа к таблице.

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

Дополнительную информацию Вы можете получить в компании Interface Ltd.

Обсудить на форуме Borland
Отправить ссылку на страницу по e-mail