Дополнительные техники физического моделирования в Teradata

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

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

Join Index

Это особый вид индекса, который представляет собой сохраненный и обновляемый результат SQL-запроса. Такое название индекс имеет потому, что запрос может включать соединение таблиц. Но соединения таблиц может и не быть — в этом случае индекс называют Single Table Join Index. Такую технику стоит рассматривать при наличии частых запросов соединения нескольких таблиц и/или выполнения агрегаций. Получив от пользователя SQL-запрос, для выполнения которого можно использовать только данные Join Index’a, оптимизатор, скорее всего, предпочтет этот способ. Существуют также способы дать оптимизатору понять, как выполнить соединение Join Index’a и базовой таблицы, если в индексе, например, не хватает полей, которые запросил пользователь. Это делается включением в Join Index поля «ROWID» — тогда СУБД сможет в случае необходимости выполнять эффективное соединение индекса с базовой таблицей.

Индексы можно ограничивать не только по ширине (количеству колонок базовой таблицы), но и по глубине. При создании Join Index’a можно указать условие «WHERE», и тогда в индекс войдут только необходимые данные. Такой способ подходит для оптимизации запросов, у которых есть вполне конкретные условия фильтрации. Тогда их можно включить в индекс и тем самым сократить его объем и минимизировать затрачиваемые ресурсы. Но надо помнить, что этот индекс будет рассматриваться оптимизатором только при обработке тех запросов, которые имеют такие же (или более строгие) условия фильтрации, как и при создании индекса.

Кроме того, поскольку Join Index — это почти то же самое, что и таблица, на нем можно строить индексы и собирать статистики.

Hash NUSI

По умолчанию NUSI-индекс распределяется по AMP’ам таким же образом, как распределена базовая таблица, на которой он построен. Это дает равномерное распределение индекса по AMP’ам и удобство использования. Внутри же каждого AMP’а NUSI сортируется по hash-значению от всех входящих в него колонок. Но есть возможность создать индекс таким образом, чтобы он сортировался по hash-значению конкретного поля, входящего в индекс. Такой индекс позволит в значительной степени оптимизировать производительность запросов с условиями на равенство. Значение, сравниваемое с колонкой индекса, при этом хешируется, и этот hash быстро ищется в индексе (который по нему и отсортирован).

Value Ordered NUSI

Но помимо сортировки по hash можно задать сортировку индекса по значению конкретного поля. Такого рода индексы позволяют хорошо оптимизировать запросы, имеющие в качестве условия фильтрации диапазоны или условия неравенства. При создании подобного индекса он сортируется по заданному полю и в таком виде сохраняется на диск. Думаю, преимущества поиска диапазонов по сортированному списку не нуждаются в комментариях.

Умышленный «перекос» данных

MPP-система с архитектурой Shared Nothing показывает наилучшую производительность при максимально равномерном распределении данных. Этому вопросу уделено много внимания в этой и других статьях. Но бывают случаи, когда имеет смысл создать «перекос» данных умышленно, и это положительно скажется на производительности.

Например, у вас есть система со 144 AMP’ами. И есть небольшой справочник, в котором, скажем, 200 записей, распределенных более-менее равномерно. Когда вы делаете выборку данных из справочника, то все 144 AMP’а обращаются к своим дискам, чтобы извлечь по одной-две записи каждый. Имеет ли смысл напрягать такое количество AMP’ов для такой плевой операции? В таких ситуациях можно создать искусственный «перекос» данных — для этого нужно добавить в справочник столбец-болванку, сделав его Primary Index, и заполнить одним и тем же значением. В этом случае при обращении к таблице будет работать только один AMP, который быстро извлечет все записи и перераспределит между всеми другими АМР’ами. Данный подход нельзя рекомендовать к использованию во всех подобных случаях, просто имейте в виду, что так можно делать. А даст это выгоду в вашем конкретном случае или нет — уже отдельный вопрос.

Ссылочная целостность

Teradata, как и другие реляционные СУБД, позволяет обеспечивать поддержку ссылочной целостности. Но у Teradata есть и ряд особенностей, о которых мы хотим упомянуть. Первая особенность заключается в том, что колонки родительской таблицы, на которые делается ссылка не обязательно должны быть объявлены как Primary Key. Teradata лишь требует, чтобы эти колонки образовывали уникальный индекс, первичный или вторичный. Хотя и определение PK остается доступным вариантом потому, что любое ограничение уникальности Teradata физически воплощает в виде уникального индекса.

Остальные особенности относятся к отдельным типам ссылочной целосности.

Standard RI

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

ALTER TABLE Employee ADD CONSTRAINT fk1 FOREIGN KEY (Dept) REFERENCES Department (Dept);

Особенность реализации данного типа ссылочной целостности в Teradata заключается в том, что при ее добавлении для существующих таблиц, даже в случае нарушения условий, ограничение будет создано и активировано (вы не сможете выполнить операции, нарушающие ссылочную целостность). При этом Teradata автоматически создаст таблицу ошибок с именем дочерней таблицы, дополненным суффиксом «_0», в которую поместит записи дочерней таблицы, которые на момент включения ссылочной целостности не соответствовали ее условиям. В дальнейшем пользователь ответственен за решение проблем с этими связями и удаление таблицы ошибок. Такая реализация позволяет, не дожидаясь разрешения проблем связывания таблиц включить и активировать ссылочную целостность, легко обнаруживать наличие проблем (есть таблица ошибок – есть проблемы), их масштаб и принимать меры по устранению (все проблемные строки в таблице ошибок).

Batch RI

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

ALTER TABLE Employee ADD CONSTRAINT fk1 FOREIGN KEY (Dept_Number) 	
REFERENCES WITH CHECK OPTION Department (Dept_Number);

Soft RI

Этот тип ссылочной целостности специфичен для Teradata. Любой из описанных выше типов, помогая оптимизатору, в то же время повышает нагрузку на систему, честно выполняя проверки. Soft RI же можно назвать «доверительной ссылочной целостностью», ибо его добавление для таблиц не приводит к включению механизма контроля связанности данных. Создание этого ограничения лишь добавит в словарь данных информацию о том как связаны между собой таблицы A и B. Для чего это нужно, скажете вы. При хорошем качестве данных вы дадите больше информации оптимизатору и исключите при этом дорогостоящие проверки ссылочной целостности. Добавляется следующей командой:

ALTER TABLE Employee ADD CONSTRAINT fk1 FOREIGN KEY (Dept_Number) 	
REFERENCES WITH NO CHECK OPTION Department (Dept_Number);

Что RI дает оптимизатору?

Наличие определенного для таблиц ограничения ссылочной целостности (любого из указанных выше типов), позволяет оптимизатору проводить Join Elimination, то есть не выполнять соединение таблиц при выполнении запроса, не содержащего в SELECT или WHERE части колонки родительской таблицы, например:
У нас есть View

REPLACE VIEW	EmpDept 
AS SELECT	Employee_Number, Last_Name, First_Name, E.Dept_Number, Dept_Name
FROM		Employee E 
INNER JOIN 	Department D
ON 		E.Dept_Number = D.Dept_Number;

, где Department – родительская, а Employee – дочерняя таблицы, между которыми определена ссылочная целостность.

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

SELECT 	Employee_Number, Last_Name, Dept_Number  
FROM 	EmpDept;

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

Сжатие данных

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

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

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

Сжатие пустых значений

Договоримся сразу — в этом разделе под пустым значением подразумевается только NULL. Философские споры о том, что NULL — это «ничто», а не пустое значение, приветствуются, но в комментариях.

Если у вас имеется таблица, в которой велик процент NULL’ов в колонках фиксированного размера, то при условии большого количества записей в таблице имеет смысл задуматься о сжатии NULL’ов. Делается это очень просто — в DDL-коде создания таблицы (или ALTER’e) после атрибутов колонки указывается слово «COMPRESS». Таким образом, система проведет анализ таблицы и для всех колонок, где присутствует значение NULL, проставит специальный бит, означающий: «здесь был NULL, но мы его сжали, и его не стало».

Сжатие конкретных значений

Так часто бывает, что помимо NULL’ов в таблицах есть часто встречающиеся значения. Например, это может быть фамилия, лидирующая со статистической точки зрения — скажем, Иванов. Любое поле (особенно строковое), для которого можно выделить статистически часто встречающиеся значения, — это хороший кандидат на сжатие. Могут быть и исключения — например, вы решили хранить статус клиента текстом (не спрашивайте почему). Таблица клиентов для ХД — это, как правило, десятки миллионов записей. Имея просто ограниченный набор статусов: «Активный», «Неактивный», «Заблокированный», можно применить компрессию и сократить расходы для десятков миллионов записей (в нашем примере — с VARCHAR (16) до двух бит).

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

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

Компрессия на уровне блоков (BLC)

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

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

Колоночное сжатие

Как мы описывали в одной из предыдущих статей (Поколоночное и гибридное хранение записей в СУБД Teradata ), СУБД Teradata поддерживает как колоночное хранение записей, так и строчное. При проектировании физической модели данных надо брать лучшее от каждого из вариантов. При использовании колоночного хранения таблицы возможно применение автоматической компрессии. Ее алгоритм основывается на том, что если блок данных содержит значения одной колонки, то вероятность их сжатия довольно велика (особенно если это неуникальные значения). Значения одной колонки при этом сжимаются на уровне контейнеров (см. статью выше). Если система определяет, что для данного значения компрессия не даст выигрыша, то она его просто не сжимает, если же видит, что сжать имеет смысл, — проверяет, какой из встроенных алгоритмов сжатия даст наибольший выигрыш и использует именно его. Например, если вы решили включить автоматическую колоночную компрессию по полю «баланс», то вряд ли получите большой выигрыш — у каждого клиента/абонента, как правило, собственный баланс, близкий к уникальному.

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

Что выбрать?

Приведенная ниже таблица поможет вам определиться с выбором вида сжатия.

Результат работ по физическому дизайну

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

• В первую очередь это таблицы (включая атрибуты хранения, такие, например, как параметры сжатия) и первичные индексы (включая параметры секционирования), на поддержку которых не требуется дополнительного расхода системных ресурсов. В идеале первичные индексы должны покрывать основную потребность в доступе к строкам таблиц, для которых они созданы, и способствовать максимальному использованию сценариев локального соединения таблиц на AMP’ах. Создание первичных индексов входит в обязательную программу физического дизайна для платформы Teradata.
• Вторичные индексы, призванные обеспечить альтернативные пути доступа к требуемым строкам таблиц. В отличие от первичных индексов, системе приходится расходовать дополнительные ресурсы на их поддержку, поэтому, принимая решение об их создании, надо взвешивать пользу от их использования и расход ресурсов на их поддержку. Неиспользуемые вторичные индексы должны быть удалены.
• В-третьих, это прочие объекты, которые требуется создать для достижения нужной производительности. К таким объектам относятся, например, различные виды join- и hash-индексов.

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

Подводя итоги

Внимательный читатель уже понял, что процесс физического дизайна модели данных — это процесс подготовки (до реализации модели) и процесс дотачивания (совершенствования после реализации). Нельзя на 100 % угадать с нагрузкой и данными, но на 50 % — можно. В следующий раз можно будет и на 60 %, а со временем, вероятно, получится и на 80 %. Таким образом, дотачивание будет занимать все меньше времени. Навык проектирования физических моделей повышается с опытом, поэтому, чем больше экспериментов и проектов вы реализуете, тем лучше будете представлять, как работать с моделями в дальнейшем.

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

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

Автор: Teradata_Russia

Источник