Создание физической модели базы данных

Повышение производительности запросов: Секционирование

Секционирование

Во многих базах данных в таблицах хранится огромное количество данных. Чем больше размер таблицы, тем больше времени потребуется как для некоторых операций по выборке строк таблицы, так и для выполнения некоторых функций администратора базы данных. Например, для резервного копирования и восстановления. Большие по размеру индексированные таблицы имеют также большие индексы, которые требуют много времени СУБД для работы с ними.

Секционирование (partitioning) - это способ физического распределения таблиц и индексов среди двух или более табличных пространств в зависимости от значений ключевых колонок таблиц с целью повышения производительности операций ввода/вывода. Фрагмент таблицы, расположенный в отдельном табличном пространстве, будем называть секцией таблицы. Секционирование также повышает эффективность резервного копирования и восстановления за счет выполнения этих задач с участием меньшего объема данных. Обсуждению создания табличных пространств далее будет посвящен отдельный раздел следующей лекции. Для понимания данного материала нам достаточно знать, что это предопределенный поименованный фрагмент памяти на одном или нескольких дисках, к которому можно обращаться в предложениях SQL по имени.

В осуществлении секционирования одним из важных понятий является колонка таблицы, относительно значений которой СУБД будет делать физическое разнесение таблицы по различным табличным пространствам на жестких дисках. Эта колонка называется ключом секционирования (partition key).

В СУБД Oracle поддерживается несколько видов секционирования: секционирование по диапазону, хэш-секционирование, составное секционирование, а также различные виды секционирования индексов.

Секционирование по диапазону

Секционирование по диапазону (range partitioning) означает распределение строк таблицы на различные предопределенные табличные пространства в зависимости от значения ключа секционирования. Доступ к такой таблице, как и к любой другой, осуществляется по ее имени, причем доступ к секциям, расположенным в каждом табличном пространстве, можно получить отдельно. Например, таблицу, содержащую финансовые квартальные отчеты организации, можно секционировать по дате таким образом, что отчеты по каждому кварталу будут храниться в отдельном табличном пространстве. При такой организации секций данные только по одному кварталу будут выбираться из одного табличного пространства, что повысит эффективность работы базы данных в целом.

Секционирование по диапазону базируется на упорядочении строк таблицы в секциях (табличных пространствах) на основе значения колонок ключа секционирования. Концептуально таблица, секционированная по диапазону, устроена как на рис. 11.2 в примере ниже. Для создания секционированных таблиц используется команда SQL CREATE TABLE с предложением PARTITION. В СУБД Oracle ключ секционирования не может иметь тип LONG.

Рис. 11.2. Пример секционирования подиапазону

Пример. Рассмотрим систему обработки заказов. Предположим, что в ней есть таблица Sales, в которой сохраняются данных о количестве, времени и цене продаж для каждого клиента. Проектировщик базы данных может использовать секционирование по диапазону, а именно - по кварталу, для представления этой таблицы в базе данных. Предположим, что мы имеем четыре определенные ранее табличных пространства c именами ts_01, ts_02, ts_03, ts_04, распределенные по четырем дискам, как показано на рисунке ниже.

Фрагмент скрипта ниже определяет таблицу Sales с физическим размещением секций, как на рисунке выше:

CREATE TABLE Sales
(
	s_customer_id		number(6),
	s_amt					number(9,2),
	s_date					date)
PARTITION BY RANGE (s_date)
	(PARTITION st_q01 VALUES LESS THAN ('01-apr-2002')
		TABLESPACE ts_01,
	PARTITION st_q02 VALUES LESS THAN ('01-jul-2002')
		TABLESPACE ts_02,
	PARTITION st_q03 VALUES LESS THAN ('01-oct-2002')
		TABLESPACE ts_03,
	PARTITION st_q04 VALUES LESS THAN (MAXVALUE)
		TABLESPACE ts_04
);

Предложение PARTITION BY RANGE (s_date) указывает СУБД Oracle выполнить секционирование таблицы по ключу секционирования - s_date. Предложения вида (PARTITION st_q01 VALUES LESS THAN ('01-apr-2002') TABLESPACE ts_01 определяют имя секции st_q01 и ее размещение в соответствующем табличном пространстве ts_01.

Чтобы получить доступ к строкам таблицы, расположенным в определенной секции, узнать о продажах в третьем квартале, можно использовать команду SELECT, как показано ниже:

SELECT s_customer_id, s_amt FROM Sales PARTITION (st_q03);

Как мы можем увидеть, для этого нужно указать опцию PARTITION (имя секции) после имени таблицы в предложении FROM.

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

Хэш-секционирование

Хэш-секционирование (hash partitioning) означает равномерное распределение строк таблицы по назначенным табличным пространствам в зависимости от значения ключа секционирования, который в данном случае хэшируется. Этот вид секционирования удобно применять для строк, у которых распределение значений ключа секционирования неравномерно или плохо группируется. Если проектировщик базы данных принимает решение о создании хэш-секционированной таблицы, то он должен достаточно точно представлять размер этой таблицы, поскольку встроенные в СУБД Oracle алгоритмы хэширования используют этот размер для вычисления позиции строки на физической странице базы данных. Неверное определение размера таблицы может привести к большому числу коллизий, т.е. к попаданию строк с различными значениями ключа на одну и ту же страницу, что приводит к поддержке цепочек переполнения и дополнительному вводу/выводу.

Пример. Рассмотрим ту же таблицу Sales, что и в предыдущем примере, и ту же схему (рис. 11.2) табличных пространств. Однако используем в качестве ключа секционирования идентификацию клиента. Отметим, что распределение значений этой колонки может быть очень неравномерно. Фрагмент кода SQL для создания хэш-секционированной таблицы Sales можно написать так:

CREATE TABLE Sales
(
	s_customer_id		number(6),
	s_amt					number(9,2),
	s_date					date)
PARTITION BY HASH (s_customer_id)
  (PARTITION q01 TABLESPACE ts_01,
   PARTITION q02 TABLESPACE ts_02,
   PARTITION q03  TABLESPACE ts_03,
   PARTITION q04 TABLESPACE ts_04
);

Предложение PARTITION BY HASH (s_customer_id) указывает СУБД Oracle выполнить секционирование таблицы по ключу секционирования - s_customer_id. Предложения вида (PARTITION q01 TABLESPACE ts_01 определяют имя секции st_q01 и ее размещение в соответствующем табличном пространстве ts_01.

Составное секционирование

Составное секционирование (composite partitioning) является комбинацией секционирования по диапазону и хэш-секционирования. Это означает, что таблица сначала распределяется среди табличных пространств на основе диапазона значений ключа секционирования, далее каждая из полученных секций диапазонов делится на подчиненные секции или подсекции, и затем строки равномерно распределяются среди подчиненных секций по значению хэш-ключа.

Пример. Рассмотрим ту же, что и в предыдущем примере таблицу Sales и ту же схему (рис. 11.2) табличных пространств. В качестве ключа секционирования по диапазону используем дату продажи. В качестве ключа хэш-секционирования -идентификацию клиента. Однако теперь каждая секция по диапазону будет разделена на предопределенное число подсекций. Фрагмент кода SQL для создания таблицы Sales с составным секционированием можно написать так:

CREATE TABLE Sales
(
	s_customer_id		number(6),
	s_amt					number(9,2),
	s_date					date)
PARTITION BY RANGE (s_date)
SUB PARTITION BY HASH (s_customer_id)
SUB PARTITION 4
STORE IN (ts_01, ts_02, ts_03, ts_04)
  (PARTITION q01 VALUES LESS THAN ('01-apr-2002'),
   PARTITION q02 VALUES LESS THAN ('01-jul-2002'),
   PARTITION q03 VALUES LESS THAN ('01-oct-2002'),
   PARTITION q04 VALUES LESS THAN (MAXVALUE)
);

Секции q01, q02, q03, q04 будут содержать строки с диапазоном дат, которые определены в предложениях типа PARTITION q02 VALUES LESS THAN ('01-jul-2002') и будут распределены в табличных пространствах ts_01, ts_02, ts_03, ts_04. Предложение SUB PARTITION 4 предписывает СУБД Oracle разбиение каждой секции на четыре логические единицы, а предложение SUB PARTITION BY HASH (s_customer_id) распределяет строки заданного диапазона среди этих четырех подчиненных секций.