Создание физической модели базы данных: проектирование производительности

О некоторых параметрах проектирования индексов

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

Кардинальностью колонки (cardinality) таблицы называется число дискретных различных значений колонки, которые встречаются в строках таблицы. Например, если в таблице "Служащий" (EMPLOYEE) мы заводим колонку для указания пола – "Пол" (SEX), то кардинальность этой колонки есть 2, так в природе у людей существует только два пола — мужской и женский. Для колонки первичного ключа кардинальность будет равна числу строк в таблице.

Причина, по которой кардинальность колонки важна для проектирования индексов, состоит в том, что кардинальность индексируемой колонки определяет число уникальных входов, которые должны сохраняться в индексе, т.е. число записей в индексе. Так, для индексируемой колонки "Пол" (SEX) будут существовать два уникальных входа, которые будут повторяться много раз в индексе. При предположении равновероятного распределения пола сотрудников на 100000 строк в таблице "Служащий" (EMPLOYEE) каждый вход индекса будет повторяться 50000 раз. СУБД вряд ли будут принимать решение об использовании такого индекса при построении плана запроса.

Определить кардинальность потенциальной колонки индексирования в существующей таблице БД достаточно просто.

SELECT COUNT (DISTINCT колонка) FROM таблица;

При проектировании новой БД для ХД следует оценить кардинальность всех потенциальных индексируемых колонок во всех таблицах физической модели данных ХД, исходя из имеющейся документации.

Способ, с помощью которого СУБД оценивает действие кардинальности, состоит в использовании фактора селективности выборки (selectivity factor). Фактор селективности выборки индекса определяется как величина, обратная кардинальности индексной колонки:

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

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

Хорошими кандидатами для индексирования обычно являются:

• колонки первичного ключа. По определению, колонки первичного ключа должны иметь уникальный индекс ;
• колонки внешнего ключа. Они дают хороший индекс по двум причинам. Во-первых, они часто применяются для выполнения соединений с родительскими таблицами. Во-вторых, они могут быть использованы СУБД при поддержке ссылочной целостности в операциях удаления строк родительской и дочерних таблиц;
• любые колонки, которые содержат уникальные значения;
• колонки, запросы или соединения, которые используют от 5 до 10% строк таблицы;
• колонки, которые часто входят как аргументы в функции агрегирования;
• колонки, которые часто используются для проверки правильности ввода данных в программах ввода-редактирования.

Факторы, влияющие на низкую эффективность индексов:

• таблицы маленького размера. Не следует создавать индексы для таблиц размером менее пяти физических страниц. Для таких страниц стоимость поддержки индекса больше, чем стоимость сканирования всей таблицы. Конечно, уникальный индекс требуется для первичного ключа и поддержки ссылочной целостности;
• интенсивные обновления таблиц в пакетном режиме. Такие таблицы обычно имеют проблемы с переполнением индекса при интенсивной модификации таблицы. Если индекс необходим для такой таблицы, то целесообразнее его удалять перед обновлением и создавать после него;
• асимметрия значений ключей (Skewness of keys). Если распределение значений ключа имеет значительную асимметрию, то кардинальность индекса может оказаться достаточно высокой и СУБД из-за низкого фактора селективности будет часто использовать этот индекс. Но результат применения индекса будет неудовлетворительным из-за того, что значительная часть строк таблицы имеет одно и то же значение ключа, что приведет к нивелированию стоимости использования индекса по сравнению со сканирование всей таблицы.

Плохими кандидатами для индексирования обычно являются:

• колонки с низкой кардинальностью. Они дают высокий фактор селективности, и СУБД обычно избегает их использования при обработке запросов. Стоимость поддержки индекса для колонки с низкой кардинальностью сопоставима со стоимостью сканирования всей таблицы, поскольку при доступе через индекс многие страницы базой таблицы посещаются много раз;
• колонка имеет много неопределенных значений (NULL-значения). В этом случае неопределенные значения могут дать значительную асимметрию распределения значений колонки, несмотря на то, что кардинальность колонки будет подходящей для использования индекса ;
• колонки с часто изменяемыми значениями. Индекс для таких колонок часто обновляется, что приводит к его переполнению, поскольку в большинстве алгоритмов обработки B-Tree-индексов физическая страница индекса становится доступной для распределения данных только после того, как из нее будет удалена последняя запись. В частности, это обстоятельство приводит к созданию дополнительных страниц индекса и уровней индекса ;
• значительная длина индексных колонок. Составной индекс или индекс для одной колонки с длиной более чем 50 байт будет приводить к росту числа уровней индекса, несмотря на то, что строк в таблице может быть немного. Большое число уровней снижает производительность операций выборки строк через индекс, т.к. каждый уровень требует по крайней мере одной операции ввода-вывода.

Следует соблюдать следующие общие правила при создании индексов.

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

К сожалению, часто проектировщики принимают крайне неудачные решения об индексировании. Это приводит к тому, что в ХД появляется слишком много индексов. В результате тратится много времени на поддержку этих индексов, дисковое пространство расходуется неэффективно, СУБД "путается" в выборе подходящего индекса или не использует их вовсе. Поэтому необходимо помнить о двух основных принципах построения индекса:

1. гарантировать уникальность значений колонки, которая будет индексироваться;
2. увеличить производительность обработки запросов в ХД. Это, кстати, единственная разумная причина для создания неуникальных индексов.

На рис. 20.4 приведены рекомендации по выбору колонок для создания индексов.

увеличить изображение
Рис. 20.4. Характеристика колонок для создания индексов

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

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

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

Секционирование (partitioning) — это способ физического распределения таблиц и индексов среди двух или более табличных пространств (СУБД Oracle) или в одной или более файловых группах (СУБД MS SQL Server) в зависимости от значений ключевых колонок таблиц с целью повышения производительности операций ввода-вывода. Таким образом, секционирование – это разбиение таблицы на группы, с сохранением для всех групп общего определения структуры. Табличное пространство (СУБД Oracle) или файловая группа (СУБД MS SQL Server) – это физическое месторасположение таблиц БД в файловой структуре операционной системы.

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

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

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

Мы отдельно рассмотрим секционирование таблиц для семейств СУБД Oracle и MS SQL Server.

Секционирование таблиц в СУБД семейства Oracle

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

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

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

Секционирование по диапазону базируется на упорядочении строк таблицы в секциях на основе значения колонок ключа секционирования. Концептуально таблица, секционированная по диапазону, устроена, как на рис. 20.5.

увеличить изображение
Рис. 20.5. Секционирование таблицы "Продажи" (Sales)

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

Пример 20.8.

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

Фрагмент скрипта определяет таблицу "Продажи" (Sales) с физическим размещением секций, как на рис. 20.5:

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 алгоритмы хеширования используют этот размер для вычисления позиции строки на физической странице базы данных. Определение размера таблицы может привести к большому числу коллизий, т.е. к попаданию строк с различными значениями ключа на одну и ту же страницу, что приводит к поддержке цепочек переполнения и дополнительному вводу-выводу.

Пример 20.9.

Рассмотрим ту же, что и в примере 20.8, таблицу "Продажи" (Sales) и ту же схему ( рис. 20.5) табличных пространств. Однако используем в качестве ключа секционирования "Идентификатор покупателя" ( s_customer_id ). Отметим, что распределение значений этой колонки может быть очень неравномерно. Фрагмент кода 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 partittioning) является комбинацией секционирования по диапазону и хеш-секционирования. Это означает, что таблица сначала распределяется среди табличных пространств на основе диапазона значений ключа секционирования, далее каждая из полученных секций диапазонов делится на подчиненные секции, или подсекции, и затем строки равномерно распределяются среди подчиненных секций по значению хеш-ключа.

Пример 20.10.

Рассмотрим ту же, что и в предыдущем примере, таблицу "Продажи" (Sales) и ту же схему табличных пространств. В качестве ключа секционирования по диапазону используем колонку "Дата продажи" (s_date). В качестве ключа хеш-секционирования — "Идентификатор покупателя" (s_customer_id). Однако теперь каждая секция по диапазону будет разделена на предопределенное число подсекций. Фрагмент кода 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) распределяет строки заданного диапазона среди этих четырех подчиненных секций.

Секционирование индексов в СУБД семейства Oracle

В СУБД Oracle предусмотрено секционирование индексов (index partitioning), которое означает преднамеренное распределение индексов таблиц по назначенным табличным пространствам в соответствии с ключом секционирования. Секционирование индексов может быть глобальным и локальным.

Локально секционированный индекс имеет такой же ключ секционирования, то же количество табличных пространств и те же правила секционирования, что и отвечающая ему базовая таблица. Глобально секционированный индекс содержит предложение PARTITION BY RANGE, в котором задаются параметры секционирования, отличные от параметров секционирования соответствующей базовой таблицы.

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

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

В локально секционированном индексе ключевые значения одной секции индекса соответствуют строкам таблицы из одной ее секции.

Пример 14/11.

Создадим локальный секционированный индекс для таблицы "Продажи" (Sales) из примера 20.8. Ключом секционирования этой таблицы является колонка "Дата продажи" (s_date). Фрагмент кода создания индекса приведен ниже:

CREATE INDEX sales_ndx ON Sales (s_date)
LOCAL
(PARTITION st_i_q01 TABLESPACE ts_01,
     PARTITION st_i_q02 TABLESPACE ts_02,
     PARTITION st_i_q03 TABLESPACE ts_03,
     PARTITION st_i_q04  TABLESPACE ts_04
);

Локально секционированный индекс называется равносекционированным (equi-partitioned), если он имеет то же число секций и те же правила секционирования, что и его базовая таблица. Обратите внимание, что в примере при создании индекса не использовалось предложение PARTITION BY RANGE. СУБД Oracle автоматически берет структуру секционирования для индекса из структуры секционирования базовой таблицы Sales. Также можно опустить и предложения типа PARTITION st_i_q02 TABLESPACE ts_02. Если опущено PARTITION, то СУБД Oracle автоматически создаст имена секций. Если опущено TABLESPACE, то СУБД Oracle автоматически разместит секции в тех же табличных пространствах, в которых находятся соответствующие секции базовой таблицы.

Глобально секционированный индекс имеет структуру секций, отличную от структуры секций базовой таблицы данного индекса. В качестве примера создадим глобально секционированный индекс для таблицы "Продажи" (Sales) из примера 20.8.

Пример 20.12.

В качестве ключа секционирования для индекса возьмем колонку "Идентификатор покупателя" (s_customer_id). В фрагменте кода ниже для секций индекса используются другие индексные пространства с именами ts_i_01, ts_i_02, ts_i_03. Число секций индекса не совпадает с числом секций базовой таблицы для этого индекса.

CREATE INDEX sales_ndx ON Sales (s_customer_id)
GLOBAL
PARTITION BY RANGE (s_customer_id)
(PARTITION st_i_q1 VALUES LESS THAN (10000)
TABLESPACE ts_i_01,
     PARTITION st_i_q2 VALUES LESS THAN (20000)
TABLESPACE ts_i_02,
     PARTITION st_i_q3 VALUES LESS THAN (MAXVALUE)
TABLESPACE ts_i_03,
);

Локально секционированный индекс может быть создан по колонке, отличной от ключа секционирования базовой таблицы индекса. В примере 20.13 создается такой непрефиксный индекс для таблицы "Продажи" (Sales) из примера 20.8.

Пример 20.13.

В качестве колонки секционирования для индекса взята колонка "Идентификатор покупателя" (s_customer_id), а для секций индекса выбраны другие табличные пространства ts_i_01, ts_i_02, ts_i_03, ts_i_04, отличающиеся от табличных пространств секций базовой таблицы индекса.

CREATE INDEX sales_ndx_1 ON Sales (s_customer_id)
LOCAL
(PARTITION st_i_q01 TABLESPACE ts_i_01,
     PARTITION st_i_q02 TABLESPACE ts_i_02,
     PARTITION st_i_q03 TABLESPACE ts_i_03,
     PARTITION st_i_q04  TABLESPACE ts_i_04
);

При принятии решения о секционировании индексов следует иметь в виду следующее.

• Локальное префиксное секционирование индекса является наиболее эффективным методом секционирования индекса. Поскольку строки одной секции базовой таблицы будут индексироваться в одной секции индекса, СУБД не придется сканировать все секции при выборке данных по запросу.
• Локальное непрефиксное секционирование индекса требует от СУБД выполнения большего объема работы, так как для поиска данных требуется сканировать все секции индекса. Этот тип следует принимать во внимание при параллельной обработке данных.
• Глобальное префиксное секционирование индекса является наиболее эффективным методом секционирования индекса при обработке данных, когда необходимо сканирование диапазона. Этот тип секционирования группирует строки в одной секции, и СУБД знает, в какой секции искать значения из заданного диапазона.