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

Кластеризация таблиц в СУБД семейства Oracle

В СУБД семейства Oracle поддерживаются различные типы кластеризации базовых таблиц. Рассмотрим использование кластеризации таблиц на примерах.

Пример 20.21.

Рассмотрим таблицы "Отдел" (DEPARTAMENT) и "Служащий" (EMPLOYEE), структура которых показана на рис. 20.8.

Рис. 20.8. Таблицы "Отдел" (DEPARTAMENT) и "Служащий" (EMPLOYEE)

Таблица "Служащий" (EMPLOYEE) описана в примере 20.1 настоящей лекции, а описание таблицы "Отдел" (DEPARTAMENT) приведено в табл. 20.2 ниже.

Обе таблицы некластеризованы и хранятся каждая на своих физических страницах. Предположим, что анализ запросов показывает, что для 80% запросов эти таблицы используются совместно, при этом соединение выполняется по колонке "Номер подразделения" (DEPNO). Можно построить кластер для этих двух таблиц.

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

После кластеризации по колонке "Номер отдела" (DEPNO) строки таблиц будут сохраняться совместно, разделяя одни и те же физические страницы базы данных.

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

Кластеризация может существенно ускорить работу с соединениями. Однако, планируя использование кластеров, следует учитывать следующие факторы.

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

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

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

Из этого следует, что существуют две основные причины использования кластеров: а) необходимость обеспечить прямой доступ к строке за одну операцию чтения, и б) необходимость сократить число операций ввода-вывода при доступе к часто используемым совместно данным путем размещения их в близко расположенных физических страницах БД.

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

Пример 20.22.

Создадим кластер для таблиц "Отдел" (DEPARTAMENT) и "Служащий" (EMPLOYEE). Для создания кластеров используется команда SQL CREATE CLUSTER, которая в нашем случае будет иметь вид:

CREATE CLUSTER emp_dept_c (DEPNO integer) 
SIZE 512,
-- TABLESPACE …
-- STORAGE …
INDEX;
CREATE TABLE DEPARTAMENT 
   ( 
   DEPNO 	integer NOT NULL, 
   DNAME 	char(20),
   LOC   		char(30),
   MANAGER  	char(20),
   PHONE  	char(15),
   CLUSTER emp_dept_c (DEPNO)
);

 CREATE TABLE EMPLOYEE
(
   EMPNO  	integer NOT NULL,
   ENAME  	char(40),
   LNAME  	char(20),
   DEPNO  	int NOT NULL,,
   JOB  		char(20),
   AGE  		date,
   HIREDATE  	date NOT NULL WITH DEFAULT,
   SAL  		dec(8,2),
   COMM  	dec(8,2),
   FINE  		dec(8,2),
   Biog 		text,
   Foto 		image,
   PRIMARY KEY (EMPNO),
   CLUSTER emp_dept_c (DEPNO) 
);

CREATE INDEX emp_dept_c_id ON CLUSTER emp_dept_c;

Параметр SIZE определяет размер кластера — фактически, максимальное число кластерных ключей, которые могут сохраняться на одной физической странице данных. Ключевое слово INDEX означает, что создаваемый кластер является индексным.

Предложение CLUSTER emp_dept_c (DEPNO) указывает СУБД, что таблица должна быть добавлена в кластер. Обратите внимание, что в таблице "Отдел" (DEPARTAMENT) снято ограничение первичного ключа на колонку "Номер подразделения" (DEPNO). Это связано с тем, что Oracle автоматически создает индекс на первичный ключ, а этот индекс в данном случае не нужен. Последнее предложение создает кластерный индекс для кластерного ключа кластера.

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

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

Пример 20.23.

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

SELECT * FROM EMPLOYEE WHERE EMPNO= 997;

До кластеризации по колонке "Номер служащего" (EPMNO) доступ будет выполняться через индекс, и, согласно рис. 20.9, потребуется 4 операции ввода-вывода, чтобы получить результирующую строку.

Рис. 20.9. Доступ через индекс

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

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

Пример 20.24.

Создадим хеш-кластер для таблицы "Служащий" (EMPLOYEE) примера 20.21.

CREATE CLUSTER PERSONNEL (EMPNO integer) 
SIZE 512 
HASHKEYS 500 
-- STORAGE (INITIAL 100K  NEXT 50K  PCTINCREASE 10)
;

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

Параметр SIZE определяет максимальное число хеш-ключей, размещаемое на физической странице базы данных. Он равен оценке общего пространства в байтах, требуемого для сохранения среднего числа строк, которое связано с каждым значением хеш-ключа. Если доступного пространства на странице 1600 байт, а значение параметра –512 байт, то три значения хеш-ключа будут распределяться на физической странице.

С помощью предложения HASH IS можно переопределить хеш-функцию, которую СУБД Oracle использует по умолчанию.

Пример 20.25.

Если у нас есть хеш-кластер для таблицы EMPLOYEE и кластерный ключ определен как "Код домашнего адреса сотрудника" ( home_area_code number ), то вероятно, что будет случаться много коллизий в хеш-кластере, если город, где живут сотрудники, невелик. Для того чтобы избежать такой коллизии, можно переопределить встроенную хеш-функцию СУБД Oracle в команде CREATE CLUSTER, добавив предложение HASH IS:

CREATE CLUSTER personnel 
 (home_area_code  number,
home_prefix     number ) 
HASHKEYS 20
HASH IS MOD(home_area_code + home_suffix_tel, 101);

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

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

1. До 1000 записей – СУБД не имеет больших преимуществ перед последовательным файлом.
2. От 1000 до 10000 записей – это преимущество незначительно.
3. От 10000 до 100000 записей – между настольными и промышленными СУБД не ощущается разницы в производительности.
4. От 100000 до 1000000 записей – промышленные СУБД обеспечивают приемлемую производительность без специальных способов ее повышения.
5. От 1000000 записей – надо начинать думать о повышении производительности.

Резюме

В настоящей лекции мы рассмотрели три основных метода повышения производительности запросов, которые используют встроенные в СУБД механизмы и объекты: индексы, секции и кластеры.

Основная идея индексирования таблиц состоит в создании специальных объектов СУБД – индексов для повышения производительности выборки строк таблиц в запросах. Как правило, для первичных ключей таблиц СУБД создает индексы автоматически.

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

Основная идея секционирования таблиц — с помощью встроенных команд СУБД разбить таблицы большого объема на ряд физических фрагментов в соответствии с некоторым критерием секционирования, чтобы сократить объем ввода-вывода при обработке фрагментов. Секционирование очень часто применяется при работе с таблицами большого объема. Если в проектируемой базе данных предполагается наличие объектов большого объема (более 1 Гб), обязательно следует рассмотреть возможность использования техники секционирования.

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

В целом, большую таблицу стоит секционировать, если выполняются следующие два условия:

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

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

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

Однако нужно помнить, что проектирование кластеров – это "штучная" работа. Выигрыш в производительности может быть не слишком высок по сравнению с другими проектными решениями. Чтобы построить высокопроизводительный кластер для таблиц, полезно знать статистику использования аналогичного кластера при эксплуатации аналогичного ХД, а это не всегда бывает известно.