Статьи

Разбиение в SQL Server

Декабрь 24, 2017

Во время подготовки к сертификации 70-451 я немного поработал над разделами. Я нашел это довольно интересным упражнением, поэтому хотел поделиться им.

Существует несколько причин, по которым вы можете выполнять разбиение на разделы, хотя в первую очередь они связаны с производительностью и упрощением обслуживания. Однако, хотя вы можете увеличить производительность, это не решение всех ваших проблем .

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

Шаг 1 — Создать базу данных

Первый шаг — создание базы данных для игры.

CREATE DATABASE [sandpit] ON  PRIMARY
( NAME = N'sandpit', FILENAME = N'C:\temp\sandpit.mdf' , SIZE = 200mb ,
   MAXSIZE = UNLIMITED, FILEGROWTH = 256000KB )
 LOG ON
( NAME = N'sandpit_log', FILENAME = N'C:\temp\sandpit_log.ldf' ,
   SIZE = 50Mb , MAXSIZE = 2048GB , FILEGROWTH = 256000KB )
GO
use sandpit;
go

Шаг 2 — Создать функцию разделения

Функция Разделение используется, чтобы определить, где данные появляются в разделах. Вы указываете его как левый или правый, поэтому значение раздела либо слева, либо справа. Пример ниже прав, поэтому 15/10/1992 (19921015) появляется во втором разделе.

Раздел 1 — бесконечность до 17530101

Раздел 2 с 17510101 по 19990101

Раздел 3 с 20000101 по 20101231 и т. Д.

create partition function myPF (datetime)
as range right for values
('17530101','20000101','20100101','20110101','20120101','20130101','99990101')

Шаг 3 — Создать схему разделов

Partition схема используется для определения того, какие группы файлов данных переходит в. Здесь должно быть столько записей, сколько и в функции разделения. Вы не можете указать меньше, и если вы укажете больше, они будут использоваться в следующих разделах (поэтому, если вы используете функцию Split, позже!). Также здесь мы указываем функцию Partition, которую мы определили ранее, чтобы связать их вместе.

CREATE PARTITION SCHEME [myPS] as Partition [myPF]
to ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY])

Шаг 4 — Создайте таблицу, используя схему разделов

Далее нам нужно создать таблицу. Вместо того, чтобы указывать ‘на <filegroup>’ как обычно, мы указываем схему разбиения и поле, используемое для разделения данных. В этом случае я использую поле даты для разделения.

CREATE TABLE [dbo].[myPTable](
    [pKey] [bigint] IDENTITY(1,1) NOT NULL,
    [pDateTime] [datetime] NOT NULL,
    [uidGuid] [uniqueidentifier] NULL,
    [tDesc] [varchar](100) NULL,
 CONSTRAINT [PK_myPTable] PRIMARY KEY NONCLUSTERED
(
    [pKey] ASC,    [pDateTime] asc
))
on     myPS(pDateTime);

Это создаст нашу таблицу, но нам нужны данные в ней, поэтому, если вы запустите этот скрипт некоторое время, он заполнит таблицу некоторыми случайными данными.

while 1=1
begin
    insert into myPTable
        (pDateTime, uidGuid,tDesc)
    Values (
        dateadd(day, -1 * abs(convert(varbinary, newid()) % ((200*365))),
        dateadd(year,2,getdate())), NEWID(),GETDATE())
end

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

select $partition.myPF(pDateTime) as PartitionNumber, COUNT(1) as RowsInPartition,
    MIN(pDateTime) as MinDateInPartition,MAX(pDateTime) as MaxDateInPartition from myPTable
group by $partition.myPF(pDateTime)

Это дает мне:

образ

Шаг 5 — Разделение раздела

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

alter partition scheme myPS next used [Primary]
alter partition function myPF() split range ('19500101')

Запуск сценария распространения выше, теперь дает мне:

образ

Во 2-м разделе все еще довольно много, поэтому давайте снова разделимся:

alter partition scheme myPS next used [Primary]
alter partition function myPF() split range ('19000101')

Теперь мы получаем:

образ

Дополнительный кредит 1 — сжатие данных по разделам

Разделение — это функция выпуска Enterprise (и Developer!), Как и сжатие данных. Учитывая это, мы можем использовать сжатие данных на разделах, а также иметь разные уровни сжатия на каждом разделе. Таким образом, используя приведенный ниже скрипт, мы можем получить сжатие страниц в разделе 2 и сжатие строк в 3-5. ( Создание сжатых таблиц и индексов — MSDN )

USE [sandpit]
ALTER TABLE [myPTable]
REBUILD PARTITION = ALL
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1 to 2),
DATA_COMPRESSION = ROW ON PARTITIONS(3 TO 6)
) ;
GO

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

Дополнительный кредит 2 — Сжатие данных по индексу

Интересно, что вы также можете изменить разделение на индекс. Это может быть выполнено следующим образом:

create clustered index IX_myPTablePKey
on myPTable(pKey,pDateTime)
with (data_compression = Page on Partitions(1 to 2),
data_compression = row on Partitions(3 to 7))

Дополнительная информация о создании сжатых таблиц и индексов приведена здесь .

31
Share: