Секционированные таблицы и индексы SQL Server 2005 - Секционирование диапазона - сведения о продажах

Секционирование диапазона - сведения о продажах

Характер использования сведений о продажах зачастую изменчив. Как правило, данные текущего месяца - это оперативные данные; данные предшествующих месяцев - это в большой степени данные, предназначенные для анализа. Чаще всего анализ производится ежемесячно, ежеквартально, либо ежегодно. Поскольку разным аналитикам могут потребоваться значительные объемы различных аналитических данных одновременно, то секционирование лучше всего позволит изолировать их деятельность. В рассматриваемом далее сценарии данные стекаются из 283 узлов и поставляются в виде двух файлов стандартного формата ASCII. Все файлы отправляются на центральный файл-сервер не позднее 3.00 am первого дня каждого месяца. Размеры файлов колеблются, но в среднем составляют примерно 86.000 заказов в месяц. Каждый заказ в среднем составляет 2.63 позиции, поэтому файлы OrderDetails составляют в среднем по 226180 строк. Каждый месяц добавляется примерно 25 миллионов новых заказов и 64 миллиона строк номенклатуры заказов. Сервер анализа истории поддерживает данные за 2 последних года. Данные за два года - это чуть меньше 600 миллионов заказов и более 1.5 миллиардов строк в таблице OrderDetails. Поскольку данные часто анализируются путем сравнения показателей месяцев одного и того же квартала, либо одних и тех же месяцев за предыдущие годы, то выбрано диапазонное секционирование. В качестве размера диапазона выбран месяц.

На основе схемы 11 ("Шаги по созданию секционированной таблицы") мы решили секционировать таблицу, используя диапазонное секционирование по столбцу OrderDate. Наши аналитики в основном объединяют и анализируют данные последних 6 месяцев, либо последних 3 месяцев текущего и прошлого годов (например, январь-март 2003 плюс январь-март 2004). Чтобы максимально усилить расслоение дисков, а заодно изолировать большинство группировок данных, на одном физическом диске будет располагаться по несколько файловых групп, но они будут смещены на шесть месяцев за тем, чтобы уменьшить количество конфликтов при разделении ресурсов. Текущий месяц - октябрь 2004, и все 283 обособленных офисов управляют своими текущими продажами локально. Сервер хранит данные с октября 2002 по сентябрь 2004 включительно. Для того чтобы воспользоваться преимуществом новой 16-процессорной системы и SAN (Storage Area Network - высокоскоростная сеть, связывающая хранилища данных), данные каждого месяца будут находиться в своем собственном файле файловой группы, и располагаться на наборе чередующихся зеркал (RAID 1+0). Рисунок 12 иллюстрирует размещение данных на логических дисках.

Каждый из 12 логических дисков использует конфигурацию RAID 1+0, поэтому общее количество дисков, необходимое для таблиц Orders и OrderDetails, равно 48. Не смотря на это, SAN поддерживает до 78 дисков, так что остальные 30 дисков используются для transaction log, TempDB, системных баз данных и прочих небольших таблиц, таких как Customers (9 миллионов записей) и Products (386 750 записей), и т.д. Таблицы Orders и OrderDetails будут использовать одни и те же граничные условия и одно и то же размещение на диске; фактически, они будут использовать одну и ту же схему секционирования. В результате (взгляните на два логических диска E:\ и F:\ на Рисунке 13) данные таблиц Orders и OrderDetails за одни и те же месяцы будут располагаться на одних и тех же дисках:

Хотя это и выглядит запутанным, все это весьма просто реализовать. Самое сложное в создании нашей секционированной таблицы - это доставка данных из большого количества источников - 283 хранилища должны иметь стандартный механизм доставки. Тем не менее, на центральном сервере есть только одна таблица Orders и одна таблица OrderDetails. Чтобы превратить обе таблицы в секционированные, мы должны сначала создать функцию и схему секционирования. Схема секционирования определяет физическое расположение секций на дисках, таким образом, файловые группы также должны существовать. Поскольку для наших таблиц необходимы файловые группы, то следующим шагом является их создание. Синтаксис операторов создания каждой файловой группы идентичен приведенному ниже, тем не менее, данным образом должны быть созданы все двадцать четыре файловые группы.Вы можете поменять названия/расположения дисков на один-единственный диск, для того чтобы протестировать и изучить синтаксис. Убедитесь, что Вы исправили размеры файла на MB вместо GB, и выбрали меньший начальный размер файлов, исходя из доступного вам дискового пространства. Двадцать четыре файла и файловые группы будут созданы в базе данных SalesDB. Все будут иметь схожий синтаксис, за исключением местоположения, имени файла и имени файловой группы:

ALTER DATABASE SalesDB 
ADD FILE      
      (NAME = N'SalesDBFG1File1',
        FILENAME = N'E:\SalesDB\SalesDBFG1File1.ndf',
        SIZE = 20GB,
        MAXSIZE = 35GB,
  FILEGROWTH = 5GB) 
TO FILEGROUP [FG1]
GO

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

Функция секции будет определена по столбцу OrderDate с типом данных datetime. Для того чтобы обе таблицы можно было секционировать по столбцу OrderDate, этот столбец должен присутствовать в обеих таблицах. В действительности, значения ключей секционирования обоих таблиц (если обе таблицы будут секционированы по одному и тому же ключу) будут дублировать друг друга; однако это необходимо для получения преимуществ выравнивания, к тому же в большинстве случаев размер ключевых столбцов будет относительно небольшим (размер поля datetime всего 8 байт). Как уже описывалось в Главе "CREATE PARTITION FUNCTION для диапазонных секций", наша функция будет диапазонной функцией секционирования, у которой первое граничное условие будет в первой (LEFT) секции.

CREATE PARTITION FUNCTION TwoYearDateRangePFN(datetime)
AS 
RANGE LEFT FOR VALUES ('20021031 23:59:59.997',      -- Oct 2002
    '20021130 23:59:59.997',      -- Nov 2002
    '20021231 23:59:59.997',      -- Dec 2002
    '20030131 23:59:59.997',      -- Jan 2003
    '20030228 23:59:59.997',      -- Feb 2003
    '20030331 23:59:59.997',      -- Mar 2003
    '20030430 23:59:59.997',      -- Apr 2003
    '20030531 23:59:59.997',      -- May 2003
    '20030630 23:59:59.997',      -- Jun 2003
    '20030731 23:59:59.997',      -- Jul 2003
    '20030831 23:59:59.997',      -- Aug 2003
    '20030930 23:59:59.997',      -- Sep 2003
    '20031031 23:59:59.997',      -- Oct 2003
    '20031130 23:59:59.997',      -- Nov 2003
    '20031231 23:59:59.997',      -- Dec 2003
    '20040131 23:59:59.997',      -- Jan 2004
    '20040229 23:59:59.997',      -- Feb 2004
    '20040331 23:59:59.997',      -- Mar 2004
    '20040430 23:59:59.997',      -- Apr 2004
    '20040531 23:59:59.997',      -- May 2004
    '20040630 23:59:59.997',      -- Jun 2004
    '20040731 23:59:59.997',      -- Jul 2004
    '20040831 23:59:59.997',      -- Aug 2004
    '20040930 23:59:59.997')      -- Sep 2004
GO

Поскольку и крайне левый, и крайне правый граничные случаи охвачены, эта функция секционирования фактически создает 25 секции. Таблица будет поддерживать 25-ую секцию, которая останется пустой. Для этой пустой секции не требуется никакой специальной файловой группы, поскольку никакие данные не должны когда-либо в нее попасть. Для того чтобы гарантировать, что никакие данные в нее не попадут, constraint ограничит диапазон данных этой таблицы. Для того чтобы направить данные на соответствующие диски используется схема секционирования, отображающая секции на файловые группы. Схема секционирования будет использовать явное определение файловых групп для каждой из 24 файловых групп, содержащих данные, и PRIMARY - для 25-ой пустой секции.

CREATE PARTITION SCHEME [TwoYearDateRangePScheme]
AS 
PARTITION TwoYearDateRangePFN TO 
( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], 
       [FG7], [FG8], [FG9], [FG10],[FG11],[FG12],
       [FG13],[FG14],[FG15],[FG16],[FG17],[FG18],
       [FG19],[FG20],[FG21],[FG22],[FG23],[FG24], 
       [PRIMARY] )
GO

Таблица может быть создана с тем же синтаксисом, который поддерживали предыдущие релизы SQL Server - используя предложенную по умолчанию, либо определенную пользователем файловую группу (для создания НЕ секционированной таблицы) - либо используя схему (для создания секционированной таблицы). Что касается того, какой из вариантов предпочтительнее (даже если эта таблица в будущем станет секционированной), то все зависит от того, как таблица будет заполняться и сколькими секциями вы собираетесь манипулировать. Наполнение кучи (heap) и последующее создание в ней кластерного индекса, вероятно, обеспечит лучшую производительность, чем загрузка в таблицу, содержащую кластерный индекс. Кроме того, в мультипроцессорных системах вы можете загружать данные в таблицу параллельно, и затем тоже параллельно строить индексы. В качестве примера создадим таблицу Orders и загрузим в нее данные, используя операторы INSERT … SELECT. Чтобы создать таблицу Orders в качестве секционированной, определите схему секционирования в выражении ON оператора CREATE TABLE.

CREATE TABLE SalesDB.[dbo].[Orders]  
(
      [PurchaseOrderID] [int]    NOT NULL,
      [EmployeeID] [int]         NULL,
      [VendorID] [int]           NULL,
      [TaxAmt] [money]           NULL,
      [Freight] [money]          NULL,
      [SubTotal] [money]         NULL,
      [Status] [tinyint]         NOT NULL,
      [RevisionNumber] [tinyint] NULL,
      [ModifiedDate] [datetime]  NULL,
      [ShipMethodID]  [tinyint]  NULL,
      [ShipDate] [datetime]      NOT NULL, 
      [OrderDate] [datetime]     NULL
            CONSTRAINT OrdersRangeYear
                 CHECK ([OrderDate] >= '20021001' 
 AND [OrderDate]   < '20041001'), 
      [TotalDue] [money]         NULL
) ON TwoYearDateRangePScheme(OrderDate)
GO

Поскольку таблица OrderDetails собирается использовать ту же схему, она должна включать в себя столбец OrderDate.

CREATE TABLE [dbo].[OrderDetails](
      [OrderID] [int]           NOT NULL,
      [LineNumber] [smallint]   NOT NULL,
      [ProductID] [int]         NULL,
      [UnitPrice] [money]       NULL,
      [OrderQty] [smallint]     NULL,
      [ReceivedQty] [float]     NULL,
      [RejectedQty] [float]     NULL,
      [OrderDate] [datetime]    NOT NULL
            CONSTRAINT OrderDetailsRangeYearCK
                 CHECK ([OrderDate] >= '20021001' 
 AND [OrderDate]   < '20041001'), 
      [DueDate] [datetime]      NULL,
      [ModifiedDate] [datetime] NOT NULL 
            CONSTRAINT [OrderDetailsModifiedDateDFLT] 
               DEFAULT (getdate()),
      [LineTotal]   AS (([UnitPrice]*[OrderQty])),
      [StockedQty]  AS (([ReceivedQty]-[RejectedQty]))
) ON TwoYearDateRangePScheme(OrderDate)
GO

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

INSERT dbo.[Orders]
      SELECT o.[PurchaseOrderID] 
                  , o.[EmployeeID]
                  , o.[VendorID]
                  , o.[TaxAmt]
                  , o.[Freight] 
                  , o.[SubTotal] 
                  , o.[Status] 
                  , o.[RevisionNumber] 
                  , o.[ModifiedDate] 
                  , o.[ShipMethodID] 
                  , o.[ShipDate] 
                  , o.[OrderDate] 
                  , o.[TotalDue] 
      FROM AdventureWorks.Purchasing.PurchaseOrderHeader AS o
            WHERE ([OrderDate] >= '20021001' 
              AND  [OrderDate]  < '20041001')
GO
 
INSERT dbo.[OrderDetails]
      SELECT      od.PurchaseOrderID
                  , od.LineNumber
                  , od.ProductID
                  , od.UnitPrice
                  , od.OrderQty
                  , od.ReceivedQty
                  , od.RejectedQty
                  , o.OrderDate
                  , od.DueDate
                  , od.ModifiedDate
      FROM AdventureWorks.Purchasing.PurchaseOrderDetail AS od
            JOIN AdventureWorks.Purchasing.PurchaseOrderHeader AS o
     ON o.PurchaseOrderID = od.PurchaseOrderID
            WHERE (o.[OrderDate] >= '20021001' 
              AND  o.[OrderDate]  < '20041001')
GO

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

SELECT $partition.TwoYearDateRangePFN(o.OrderDate) 
                  AS [Partition Number]
      , min(o.OrderDate) AS [Min Order Date]
      , max(o.OrderDate) AS [Max Order Date]
      , count(*) AS [Rows In Partition]
FROM dbo.Orders AS o
GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate)
ORDER BY [Partition Number]
GO
 
SELECT $partition.TwoYearDateRangePFN(od.OrderDate) 
                  AS [Partition Number]
      , min(od.OrderDate) AS [Min Order Date]
      , max(od.OrderDate) AS [Max Order Date]
      , count(*) AS [Rows In Partition]
FROM dbo.OrderDetails AS od
GROUP BY $partition.TwoYearDateRangePFN(od.OrderDate)
ORDER BY [Partition Number]
GO

И наконец теперь, после того как вы загрузили данные, Вы можете создать кластерный индекс и внешний ключ (Foreign key) между таблицами OrderDetails и Orders. В данном случае кластерный индекс будет построен на первичном ключе (Primary Key) точно так же, как вы идентифицируете обе эти таблицы по их ключу секционирования (для OrderDetails к индексу Вы добавите столбец LineNumber для уникальности). По умолчанию при построении индексов на секционированной таблице происходит их выравнивание по отношению к секционированной таблице согласно той же самой схеме секционирования; явно задавать схему не обязательно.

ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
      PRIMARY KEY CLUSTERED (OrderDate, OrderID)
GO
 
ALTER TABLE dbo.OrderDetails
ADD CONSTRAINT OrderDetailsPK
      PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
GO

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

ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
      PRIMARY KEY CLUSTERED (OrderDate, OrderID)
      ON TwoYearDateRangePScheme(OrderDate)
GO
 
ALTER TABLE dbo.OrderDetails
ADD CONSTRAINT OrderDetailsPK
      PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
      ON TwoYearDateRangePScheme(OrderDate)
GO