Секционирование таблиц SQL Server на практических примерах

В этом руководстве вы узнаете о секционировании таблиц SQL Server и о том, как создавать секционированные таблицы.

Содержание

Введение в секционирование SQL Server
Создание секционированной таблицы
1) Создание групп файлов
2) Создать функцию разделения
3) Создание схемы разделов
4) Создание секционированной таблицы
Краткое содержание

Введение в секционирование SQL Server

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

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

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

Создание секционированной таблицы

Чтобы создать секционированную таблицу, выполните следующие действия:

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

Мы будем использовать образец базы данных BikeStores для демонстрации. Следующий запрос возвращает сумму заказа по датам и продуктам:

WITH order_data(order_date, product_name, amount)
AS(
SELECT
  order_date,
  product_name,
  SUM(i.quantity * i.list_price *(1 - discount))
FROM sales.orders o
INNER JOIN sales.order_items i
  ON i.order_id = o.order_id
INNER JOIN production.products p
  ON p.product_id = i.product_id
GROUP BY order_date,
         product_name
)
SELECT * FROM order_data;

Выход:

Ниже приведена сводка заказов:

WITH order_data(order_date, product_name, amount)
AS(SELECT
  order_date,
  product_name,
  SUM(i.quantity * i.list_price *(1 - discount))
FROM sales.orders o
INNER JOIN sales.order_items i
  ON i.order_id = o.order_id
INNER JOIN production.products p
  ON p.product_id = i.product_id
GROUP BY order_date,
         product_name)
SELECT
  YEAR(order_date) year,
  COUNT(*) row_count
FROM order_data
GROUP BY YEAR(order_date);

Выход:

Мы создадим секционированную таблицу с именем sales.order_reports, которая будет хранить данные о заказах, возвращаемые вышеуказанным запросом. Таблица sales.order_reports будет иметь три секции. И каждая секция будет хранить строки, даты заказов которых будут в 2016, 2017 и 2018 годах.

1) Создание групп файлов

При создании базы данных SQL Server создает как минимум два файла: файл данных и файл журнала:

Файл данных содержит данные и объекты, такие как таблицы, индексы и представления.
Файл журнала содержит информацию для восстановления транзакций в базе данных.

SQL Server позволяет хранить данные в нескольких файлах данных и использует файловую группу для группировки файлов данных. По умолчанию файл данных принадлежит к ПЕРВИЧНОЙ файловой группе.

Чтобы добавить больше файловых групп в базу данных, используйте оператор ALTER DATABASE … ADD FILEGROUP.

Сначала добавьте три группы файлов в базу данных BikeStores:

ALTER DATABASE bikestores
ADD FILEGROUP orders_2016;
ALTER DATABASE bikestores
ADD FILEGROUP orders_2017;
ALTER DATABASE bikestores
ADD FILEGROUP orders_2018;

Во-вторых, проверьте файловые группы текущей базы данных, используя следующий оператор:

SELECT
  name
FROM sys.filegroups
WHERE type = 'FG';

Выход:

В-третьих, назначьте физические файлы файловым группам:

ALTER DATABASE bikestores    
ADD FILE    (
    NAME = orders_2016,
    FILENAME = 'D:\data\orders_2016.ndf',
        SIZE = 10 MB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP orders_2016;
ALTER DATABASE bikestores    
ADD FILE    (
    NAME = orders_2017,
    FILENAME = 'D:\data\orders_2017.ndf',
        SIZE = 10 MB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP orders_2017;
ALTER DATABASE bikestores    
ADD FILE    (
    NAME = orders_2018,
    FILENAME = 'D:\data\orders_2018.ndf',
        SIZE = 10 MB, 
        MAXSIZE = UNLIMITED, 
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP orders_2018;

В этом примере мы назначаем три файла, расположенных в D:\data, файловым группам.

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

SELECT 
 name as filename,
 physical_name as file_path
FROM sys.database_files
where type_desc = 'ROWS';

Выход:

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

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

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

В нашем примере разделы будут основаны на значениях столбца order_date. И функция раздела создаст три раздела:

CREATE PARTITION FUNCTION order_by_year_function(date)
AS RANGE LEFT 
FOR VALUES('2016-12-31', '2017-12-31','2018-12-31');

В этом заявлении:

order_by_year_function — это имя функции разделения. Она принимает аргумент, тип данных которого — DATE.
AS RANGE LEFT FOR VALUES определяет три границы, в которых строки с датой до 31.12.2016 будут принадлежать разделу 1, строки с датой до 31.12.2017 и после 31.12.2016 будут принадлежать разделу 2, строки с датой между 31.12.2017 и 31.12.2018 будут принадлежать разделу 3.

3) Создание схемы разделов

Схема секционирования — это объект базы данных, который сопоставляет разделы, возвращаемые функцией секционирования, с файловыми группами.

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

CREATE PARTITION SCHEME order_by_year_scheme
AS PARTITION order_by_year_function
TO([orders_2016], [orders_2017], [orders_2018]);

4) Создание секционированной таблицы

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

CREATE TABLE sales.order_reports(
  order_date date,
  product_name varchar(255),
  amount decimal(10, 2) NOT NULL DEFAULT 0,
  PRIMARY KEY(order_date, product_name)
) 
ON order_by_year_scheme(order_date);

В этом заявлении:

Предложение ON order_by_year_scheme(order_date) определяет схему секционирования и столбец секционирования(order_date) для таблицы.
Столбец раздела должен быть включен в кластеризованный индекс, иначе возникнет ошибка.

Следующий оператор INSERT загружает данные в таблицу sales.order_reports:

INSERT INTO sales.order_reports(order_date, product_name, amount)
  SELECT
    order_date,
    product_name,
    SUM(i.quantity * i.list_price *(1 - discount))
  FROM sales.orders o
  INNER JOIN sales.order_items i
    ON i.order_id = o.order_id
  INNER JOIN production.products p
    ON p.product_id = i.product_id
  GROUP BY order_date,
           product_name;

Для проверки строк каждого раздела используйте следующий запрос:

SELECT 
 p.partition_number AS partition_number,
 f.name AS file_group, 
 p.rows AS row_count
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_NAME(OBJECT_ID) = 'order_reports'
order by partition_number;

Выход:

Краткое содержание

Используйте секционирование таблиц для хранения данных таблицы в нескольких физических разделах или секциях.