[Комуникация] Резюме на разделени таблици в SQL Server

Официална документация

Стъпки на високо ниво за създаване на таблица с дял

1. Създай файлова група (подобно на oracle tablespace), разбира се, не можеш да я създадеш, можеш да сложиш всички дялове в една файлова група

2. Установете функция за разделяне и разпределете данните според обхвата

3. Установете схема за дял (partition scheme), асоциирайте функцията на дяловете и също асоциирайте файловата група, функцията разделя данните на няколко диапазона, трябва да асоциирате няколко файлови групи, разбира се, можете също да поставите тези диапазони от дялове в една и съща файлова група.

4. Създайте таблица и свържете схемата за зониране

Срещнат бъг

Когато директно кликнете с десен бутон върху таблицата, за да експортирате структурата на таблицата, не можете да експортирате информацията за дяловете и можете да експортирате информацията за дяловете на таблицата само чрез десен клик върху скрипта за генериране на задачи в базата данни

Някои изводи от таблицата на разпределение:

1. Разделените полета не е задължително да бъдат индексирани

2. Разделените полета могат да бъдат създадени като клъстерирани или некластерирани индекси

3. Партиционираните полета, независимо дали са клъстерирани или неклъстерирани индекси, се преизграждат в клъстерирани индекси без свързани схеми за разделяне, а разделената таблица става непартиционирана таблица

4. Преобразувайте обикновена таблица в разделена таблица, просто създайте клъстериран индекс в таблицата и използвайте схемата за разделяне върху клъстерирания индекс. Например, когато поле 1 на непартиционирана таблица създава клъстериран индекс и е свързано със схема за разделяне, таблицата се преобразува в разпределена таблица, а разделеното поле е поле 1.

5. Преобразувайте разделената таблица в нормална таблица, ако има поле с индекс, преизградете разделеното поле като клъстериран индекс и не асоциирайте се със схемата на партициониране; ако полето на дял няма индекс, полето може да създаде нов клъстериран индекс и да не се асоциира със схемата на партициите

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

7. За да се създаде таблица с дялове, ограничението за уникалност трябва да включва колона с дялове

8. При създаване на схема за дялове е необходимо да се гарантира, че броят на групите файлове съвпада с сегмента от диапазона на дяловете на функцията и няма значение дали името на файловата група е дублирано.

9. Функциите за разделяне и схемите за разделяне са в база данни, а не са насочени към цялата инстанция

10. Таблицата с дялове е твърде голяма, за да заема много място на диска, а размерът ѝ все още не се променя след изтриване на някои полета

Стъпки за създаване на таблица с дялове

1.1. Пример за създаване на файлова група

alter database test1 add filegroup part1;

alter database test1 add filegroup part1000;

alter database test1 add filegroup part2000;

alter database test1 add filegroup part3000;

alter database test1 add filegroup part4000;

1.2. Установете пример за файл и асоциирайте файлова група

ALTER DATABASE test1 ДОБАВИ FILE(NAME = test1part1,FILENAME = 'G:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) КЪМ FILEGROUP PART1;

ALTER DATABASE test1 ADD FILE(NAME = test1part1000,FILENAME = 'G:\test1part1000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) КЪМ FILEGROUP PART1000;

ALTER DATABASE test1 ADD FILE(NAME = test1part2000,FILENAME = 'G:\test1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) КЪМ FILEGROUP PART2000;

ALTER DATABASE test1 ADD FILE(NAME = test1part3000,FILENAME = 'G:\test1part3000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) КЪМ FILEGROUP PART3000;

ALTER DATABASE test1 ADD FILE(NAME = test1part4000,FILENAME = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) КЪМ FILEGROUP PART4000;

2. Пример за установяване на функция на разпределение, функцията на партициониране се нарича partfun1

CREATE PARTITION FUNCTION partfun1 (int)

КАТО ДИАПАЗОН ЛЯВ ЗА СТОЙНОСТИ ('1000', '2000', '3000', '4000')

--СТОЙНОСТИ ('1000', '2000', '3000', '4000') показва, че таблицата ще бъде разделена на 5 зони, които се разделят според размера на стойностите в полетата на таблицата, като петте зони са най-малки - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-най-големи

3. Пример за създаване на схема, асоцииране на функцията за разделяне partfun1 и асоцииране на файловата група

СЪЗДАВАНЕ НА СХЕМА НА ПАРТИЦИИ partschema1

AS PARTITION partfun1

TO (част 1, част 1000, част 2000, част 3000, част 4000);

--Изграден върху няколко файлови групи от част 1, част 1000, част 2000, част 3000, част 4000

СЪЗДАВАНЕ НА СХЕМА ЗА ПАРТИЦИИ, partschema2

AS PARTITION partfun1

КЪМ (ЧАСТ1,[ПЪРВИЧНА],[ПЪРВИЧНА],[ПЪРВИЧНА],[ПЪРВИЧНА]);

--Изграден върху групата на файловете part1, [PRIMARY], няма проблем да се замени част 1 с [PRIMARY], така че да е подобно на изграждането върху групата файлове [PRIMARY]

СЪЗДАВАНЕ НА СХЕМА ЗА РАЗПРЕДЕЛЕНИЕ partschema3

AS PARTITION partfun1

ALL TO (част 1);

--всичко е изградено върху файловата група part1

СЪЗДАВАНЕ НА СХЕМА НА ПАРТИЦИИТЕ partschema4

AS PARTITION partfun1

ВСИЧКИ КЪМ ([ПЪРВИЧНА]);

--всичко е изградено върху [PRIMARY] файловата група

4. Пример за създаване на таблица с дял

СЪЗДАЙ ТАБЛИЦА parttable1(

[ID] [int] НЕ NULL,

[IDText] [нварчар] (макс) NULL,

[Дата] [време на среща] NULL)

ON [partschema1](ID);

вмъкнете в стойностите на parttable1 (1,'1',getdate()-4);

вмъкнете в стойности на parttable1 (1001,'1001',getdate()-3);

Вмъкнете в стойностите на parttable1 (2001,'2001',getdate()-2);

вмъкнете в стойностите на parttable1 (3001,'3001',getdate()-1);

вмъкнете в стойностите на parttable1 (4001,'4001',getdate());

5. Проверете данните от таблицата с дялове

ИЗБЕРЕТЕ * ОТ parttable1;

--Връща всички редове от таблицата на партициите

ИЗБЕРЕТЕ различни $PARTITION. [partfun1] (4) ОТ PARTTABLE1;

--Връща коя разделяне на реда с ID поле стойност 4 принадлежи на

ИЗБЕРЕТЕ * ОТ parttable1, където $PARTITION. [partfun1] (ID)=2

--Връща всички редове във втория дял ID, ID е идентификаторът на полето на дял

Забележка: Не можете да използвате SELECT * ОТ parttable1, където $PARTITION. [partfun1] (ID)=2 има резултат, което означава, че е разделена таблица, а последният тест 7 от тази статия е неразделена таблица, но SELECT * FROM parttable1, където $PARTITION. [partfun1] (ID)=2 все още има резултат

Нови дялове

1. Задайте файлова група, която може да се използва за схемата на дяловете (добавете файлова група за схемата на дяловете).

2. Модифициране на функцията на дяловете (добавяне на диапазона от данни на функцията за партициониране)

ALTER PARTITION SCHEME partschema1 СЛЕДВАЩО ИЗПОЛЗВАНО [ОСНОВНО]

ALTER PARTITION FUNCTION partfun1() РАЗДЕЛЕН ДИАПАЗОН ('4500')

Изберете p.partition_number,p.редове от sys.indexes i вътре се присъедини към sys.partitions p на p.object_id = i.object_id и i.object_id = object_id('parttable2') по 1

--Първото изражение, ако схемата за разделяне използва ALL TO ([PRIMARY]), това изявление не е необходимо да се изпълнява

--Към второто изказване се добавя нов дял – диапазонът е 4000-4500

--Третото твърдение проверява дали новото разпределение съществува и дали има редове

Delete\Merge partition

ALTER PARTITION FUNCTION partfun1() MERGE RANGE ('2000')

Разделянето 1000-2000 беше премахнато и обединено в 1000-3000

--Не може да се изпълни ALTER TABLE NAME DROP PARTITIONNAME като oracle;

Изтрийте таблицата с дялове и съответната група файлове

Редът на изтриване е: изтриване на таблицата на дяловете, изтриване на схемата на дяловете, изтриване на функцията за дял и накрая изтриване на групата файлове, като съответният файл се изтрива след изтриването на групата файлове

Пример за преобразуване на разделена таблица в нормална таблица и нормална таблица в разделена таблица

МАСА ЗА СПУСКАНЕНЕ1;

СЪЗДАЙ ТАБЛИЦА parttable1(

[Id] [int] ИДЕНТИЧНОСТ(1,1) НЕ NULL,

[Име] [варчар] (16) НЕ НУЛЕВО,

[Id2] [инт] НЕ НУЛЕВ

) ON partschema1(Id2);

вмъкнете в parttable1 стойности ('1',1);

вмъкнете в parttable1 стойности ('1001',1001);

вмъкнете в parttable1 стойности ('2001',2001);

да се вмъкнат стойности в parttable1 ('3001',3001);

вмъкнете стойности в parttable1 ('4001',4001);

1. Уникалното ограничение, създадено върху таблицата с партиции, трябва да съдържа колона за дялове.

ALTER TABLE parttable1 ДОБАВИ ОГРАНИЧЕНИЕ PK_prattable1_id КЛЪСТЕРИРАН ПЪРВИЧЕН КЛЮЧ ([ID] ASC)

Колона за грешка 'Id2' е колона за разделяне на индекса 'PK_prattable1_id'. Колоните за разделяне за уникален индекс трябва да са подмножество на индексния ключ.

2. Създайте нов клъстериран индекс в колоната на дял id2 и parttable1 все още е разделена таблица

създаване на клъстериран индексен CI_prattable1_id2 върху parttable1(id2);

3. Колона на дял id2 създава неклъстериран индекс, а parttabletable1 все още е разделена таблица

индекс на падане CI_prattable1_id2 на parttable1;

създаване на неклъстериран индексен NCI_prattable1_id2 върху parttable1(id2);

4. Колоната с идентификатор на неразделена колона създава клъстериран индекс, parttable1 или разделена таблица, което показва, че неразделената колона може да бъде клъстерна индексна колона

създаване на клъстериран индексен CI_prattable1_id върху parttable1(id);

5. Колона на дял ID2 се преобразува в неклъстериран индекс и не използва схема за разделяне, parttable1 все още е таблица на дялове

създаване на неклъстериран индексен NCI_prattable1_id2 върху parttable1(id2) С (DROP_EXISTING = ON) ВЪРХУ [ПЪРВИЧНА];

6. Колоната id2 на дяловете се реконструира в клъстериран индекс без условието ON, а parttabletable1 все още е таблица на партициите

индекс на падане CI_prattable1_id на parttable1;

индекс на отпадане NCI_prattable1_id2 на Parttable1;

създаване на клъстериран индексен CI_prattable1_id2 върху parttable1(id2);

създаване на клъстериран индексен CI_prattable1_id2 върху parttable1(id2) WITH (DROP_EXISTING = ON);

7. Партиционираната колона id2 се възстановява в клъстериран индекс с условие ON, но без използване на схема за разделяне, и parttable1 става неразделена таблица

създаване на клъстериран индексен CI_prattable1_id2 върху parttable1(id2) С (DROP_EXISTING = ON) ВЪРХУ [ПЪРВИЧНА];

8. Колона на дял id2 се преобразува в клъстериран индекс и чрез схемата за разделяне, parttable1 става разпределена таблица

създаване на клъстериран индекс CI_prattable1_id2 върху parttable1(id2) WITH (DROP_EXISTING = ON) върху partschema1(Id2);

9. След като изтрия клъстерирания индекс в 8 по-горе, parttable1 все още е разделена таблица

индекс на падане CI_prattable1_id2 на parttable1;

10. Колона на дял ID2 се създава като клъстериран индекс и не използва схемата за разделяне, а parttabletable1 става неразделена таблица

създаване на клъстериран индексен CI_prattable1_id2 върху parttable1(id2) ВЪРХУ [ПЪРВИЧНА];

11. След изтриване на клъстерирания индекс на горните 10, parttable1 все още е неразделена таблица

индекс на падане CI_prattable1_id2 на parttable1;

12. Колоната на дяловете id2 е новосъздадена като неклъстериран индекс, въпреки че се използва схемата за разделяне, тя все още е неразделена таблица

създаване на неклъстериран индексен NCI_prattable1_id2 върху parttable1(id2) върху partschema1(Id2);

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

ALTER ТАБЛИЦА Table_name DROP ОГРАНИЧЕНИЕ PK_NAME С (>

СЪЗДАЙТЕ КЛЪСТЕРИРАН ИНДЕКСЕН PK_NAME ВЪРХУ Table_name(колона) С (НА [ПЪРВИЧНА];

или

ПРОМЯНА НА ТАБЛИЦА Table_name ДОБАВЯНЕ НА ОГРАНИЧЕНИЕ PK_NAME ОСНОВЕН КЛЮЧ КЛЪСТЕРИРАН (колона) С (ВЪРХУ [ОСНОВЕН];

Ако нормалната таблица се преобразува в разделена таблица, ако първоначалният първичен ключ трябва да се запази, ограничението за първичен ключ се премахва, след което първичният ключ се създава без да се обявява като агрегиран индекс, след което се създава нов агрегиран индекс и схемата за разделяне се използва в агрегирания индекс

ALTER ТАБЛИЦА Table_name DROP ОГРАНИЧЕНИЕ PK_NAME С (>

ALTER ТАБЛИЦА Table_name ДОБАВИ ОГРАНИЧЕНИЕ PK_NAME ПЪРВИЧЕН КЛЮЧ NONCLUSTERED(column) С (ВЪРХУ [PRIMARY];

--Създайте първичен ключ, но не като клъстериран индекс

СЪЗДАВАНЕ НА КЛЪСТЕРИРАН ИНДЕКСЕН index_name ВЪРХУ Table_name(колона) ВЪРХУ схемата на партициите (поле за разпределение)

--Създаване на нов клъстериран индекс, в който се използва схема за разделяне

Направете заявка за общия брой редове и размера на разделена таблица, например таблицата е CRM. EmailLog

Изпълнителен sp_spaceused 'CRM. EmailLog';

Потърсете информация за разделена таблица колко реда има всеки дял, например таблицата е CRM. EmailLog

Изберете Convert(Varchar(50), ps.name

) като partition_scheme,

p.partition_number,

Конверсия (Варчар(10), ds2.name

) като файлова група,

convert(varchar(19), isnull(v.value, ''), 120) като range_boundary,

str(p.rows, 9) като редове

от sys.indexes i

Join sys.partition_schemes ps on i.data_space_id = ps.data_space_id

Присъедини се към sys.destination_data_spaces dds

on ps.data_space_id = dds.partition_scheme_id

Присъединете се към sys.data_spaces DS2 на dds.data_space_id = ds2.data_space_id

Join sys.partitions p on dds.destination_id = p.partition_number

и p.object_id = i.object_id и p.index_id = i.index_id

Присъединете се sys.partition_functions PF на ps.function_id = pf.function_id

ЛЯВ JOIN SYS. Partition_Range_values v на pf.function_id = v.function_id

и v.boundary_id = p.partition_number - pf.boundary_value_on_right

КЪДЕТО i.object_id = object_id('crm. EmailLog')

и i.index_id в (0, 1)

Ред по p.partition_number

Функции за дял на заявки

Изберете * от sys.partition_functions

Вижте архитектурата на дяловете

изберете * от sys.partition_schemes