Создание горизонтально секционированных представлений

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

Клод Сидман, SQL Magazine Online/RE, #3/2000

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

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

К счастью, в SQL Server 7.0 появилась новая возможность - создавать горизонтально секционированные представления, что является эффективно работающей альтернативой описанному выше решению, которое чревато осложнениями. Горизонтально секционированные представления позволяют разбить большую таблицу на несколько подтаблиц, и в то же время для взаимодействия со всеми таблицами необходимо одно единственное представление. Пользователю или разработчику потребуется применить только одно представление для извлечения записей из любой подтаблицы. Помимо удобного интерфейса такие секционированные представления обеспечивают существенный рост производительности.

Несмотря на свою очевидную полезность и элегантное исполнение, эта новая возможность удивительно скудно документирована (имеется лишь краткое описание в Books Online (BOL) и одно упоминание в документации к инструментарию SQL Server). Чтобы восполнить этот пробел, рассмотрим более подробно, что собой представляют горизонтально секционированные представления, как их применять и как автоматически наполнять таблицы данными.

Создание горизонтально секционированных представлений

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

CREATE VIEW V_tRADES
AS
SELECT * FROM TRADE_011999
UNION ALL
SELECT * FROM TRADE_021999
UNION ALL
SELECT * FROM TRADE_031999
UNION ALL
SELECT * FROM TRADE_041999
UNION ALL
SELECT * FROM TRADE_051999
UNION ALL
SELECT * FROM TRADE_061999
UNION ALL
SELECT * FROM TRADE_071999
UNION ALL
SELECT * FROM TRADE_081999
UNION ALL
SELECT * FROM TRADE_091999
UNION ALL
SELECT * FROM TRADE_101999
UNION ALL
SELECT * FROM TRADE_111999
UNION ALL
SELECT * FROM TRADE_121999

Чтобы план исполнения запроса работал наиболее эффективно, ядру SQL Server необходимы гарантии того, что в каждой таблице содержится только то, что предполагалось. Это можно гарантировать, если использовать проверки выполнения ограничений. Каждую таблицу следует создавать с указанием ограничений, как это сделано в листинге 2. Этот листинг включает операторы языка определения данных DDL (Data Definition Language), необходимые для создания таблицы и индексов.

CREATE TABLE TRADE_011999 (
TRADEID uniqueidentifier NOT NULL,
INSTRUMENTID int NOT NULL,
TRADETYPE char(1) NOT NULL,
AMOUNT money NOT NULL,
TRADEDATE datetime NOT NULL
)

ALTER TABLE [TRADE_011999] WITH CHECK ADD
CONSTRAINT [JAN1999] DEFAULT (newid()) FOR TRADEID,
PRIMARY KEY NONCLUSTERED
(
[TRADEID]
) ,
CONSTRAINT [CK_011999] CHECK (MONTH([TRADEDATE]) = 1 and
YEAR([TRADEDATE]) = 1999)
GO

CREATE INDEX [IX_tRADEDATE] ON [TRADE_011999]([TRADEDATE])
GO

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

SELECT * FROM V_tRADES
WHERE
TRADEDATE BETWEEN '1/10/1999' AND '1/15/1999'
AND TRADETYPE = 'B'

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

Наполнение таблиц

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

CREATE PROCEDURE USP_INSERT_tRADE
@InstrumentId int,
@TradeType char(1),
@Amount money,
@TradeDate datetime
AS
SET NOCOUNT ON
DECLARE @insertString nvarchar(3000)

SELECT @insertString =
N`insert into TRADE_` +
right( `00`+convert(nvarchar(2),month(@TradeDate)) ,2) +
convert(nvarchar(4),year(@TradeDate)) +
N`(
Instrumentid,
TradeType,
Amount,
TradeDate
) values (
@xInstrumentId,
@xTradeType ,
@xAmount ,
@xTradeDate
)'
EXEC sp_executesql
@insertString
,
N`@xInstrumentid int,
@xTradeType char(1),
@xAmount money,
@xTradedate datetime'
,
@xInstrumentid = @InstrumentID,
@xTradeType = @TradeType ,
@xAmount = @Amount,
@xTradeDate

На экране 1 приведен пример плана обработки запроса данных о продажах за февраль 1999. План исполнения запроса показывает, что SQL Server будет сканировать только февральскую таблицу, а у других таблиц он проверит лишь ограничения, накладываемые на дату продажи.

Секционирование структуры больших таблиц дает преимущества, выходящие за рамки только улучшения производительности. Секционированную таблицу легче архивировать с течением времени - соответствующая секция просто перемещается на носитель, который затем удаляется в архив. К примеру, если секционировать таблицу размером 7Гб на 12 таблиц по 600 Мб каждая, то можно поместить данные за каждый месяц на отдельный CD-ROM. Без секционирования таблицы администратора базы данных ожидает утомительная работа по копированию части базы данных в другую таблицу и последующему удалению скопированных записей. В организациях с круглосуточным графиком работы (24х7) проведение подобной операции может сделать базу данных недоступной на неприемлемо длительный период времени. Секционирование позволяет улучшить доступ к данным без дополнительных накладных расходов.

Об авторе:

Клод Сидман (cseidman@mediaone.net) работает независимым администратором баз данных, разработчиком и преподавателем, специализирующимся на SQL Server, OLAP, PERL и ASP. Обладает сертификатами MCDBA, MCSE, MCP+I и MCT.