Чтобы сделать наши курсы по Greenplum еще более полезными, сегодня разберем особенности индексов и накладываемых ими ограничений на SQL-запросы к таблицам этой MPP-СУБД. Что такое уникальные индексы и как они поддерживаются в таблицах, оптимизированных для добавления, в Greenplum версии 7, выпущенной в середине декабря 2022 года.
Еще раз о пользе и опасности индексации таблиц в БД
Индексы ускоряют поиск данных в базе данных, позволяя SQL-запросам выполняться быстрее за счет обращения к меньшему количеству строк. Индекс представляет собой оптимизированную для поиск древовидную структуру данных из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки, чтобы искать строки согласно критерию поиска. Справедливости ради стоит отметить, что индексы можно реализовать разными структурами данных, но чаще всего применяются B*-деревья, B+-деревья, B-деревья и хэши. В частности, в MPP-СУБД Greenplum и PostgreSQL, для индексов используются B-деревья.
Кластерный индекс упорядочивает строки таблицы по значению его столбца (ключа). При отсутствии кластерного индекса, таблица называется кучей и ее индекс (некластерный) содержит только указатели на строки. В любой таблице может быть только 1 кластерный индекс, тогда как количество некластерных индексов не лимитируется и каждый из них определяет свой собственный порядок следования записей. В уникальных индексах каждое значение встречается только один раз, а в неуникальных значения могут повторяться.
Индексы сокращают время поиска и снижают его алгоритмическую сложность: время поиска данных с помощью индексов, при росте объема базы растет медленнее, чем при использовании полного перебора. Однако, индексы увеличивают размер базы данных и провоцируют рост накладных расходов при записи данных, а также требуют обслуживания. В частности, необходимо создавать и поддерживать актуальную статистику, регулярно обновляя ее при операциях вставки и обновления, что занимает ресурсы и время.
Считается, что для SQL-запросов с низкой селективностью, которые возвращают более половины строк таблицы, индекс не нужен. Поэтому индексация не очень распространена в Greenplum, где ориентированной на работу с большими объемами данных. Быстрота поиска в Greenplum достигается и в рамках последовательного сканирования, если каждый сегмент содержит равную часть данных, и все сегменты при чтении работают параллельно.
Однако, в случае SQL-запросов с высокой селективностью индексы в Greenplum могут повысить их производительность. Также индексы могут повысить производительность сжатых таблиц, оптимизированных для добавления (AO, Append Only), к которым обращаются SQL-запросы. Подробнее про индексацию таблиц в Greenplum мы писали здесь. Напомним, AO-таблицы не предназначены для частого обновления данных после начальной загрузки, а последующие вставки в них выполняются только в пакетных операциях. Обычно AO-таблицы очень большие, загружаются один раз, редко обновляются и часто запрашиваются для чтения данных, т.е. аналитических SQL-запросов. А таблицы кучи, наоборот подходят для конкурентных запросов, включая итеративные UPDATE и DELETE, а также одиночные INSERT. Про виды таблиц в Greenplum мы рассказывали в этой статье.
До выпуска 7-ой версии Greenplum, которая вышла в декабре 2022 года, уникальные индексы поддерживались только для таблиц кучи, но не для AO-таблиц. Начиная с версии 7, уникальные индексы можно задать и для таблиц, оптимизированных для добавления. Как это работает, рассмотрим далее.
Уникальные индексы в AO-таблица Greenplum 7
Уникальные индексы выступают в качестве ограничений, обеспечивая уникальность столбца или группы столбцов во время поступления данных или во время построения индекса. Такие индексы в Greenplum могут быть только B-деревьями и применяются только во время INSERT или подобных операций (COPY FROM, UPDATE и пр.) для каждого кортежа. Если уникальные индексы строятся на таблице, уже содержащей данные, механизм ShareLock применяется к ней на протяжении всей операции, предотвращая одновременное изменение. При этом выполняется полное последовательное сканирование таблицы, информирующее модуль B-дерева о кортежах, которые необходимо проиндексировать. Для создания записей индекса значения его ключей, полученные в результате сканирования, помещаются в файл сортировки. Обнаружение конфликта выполняется во время последующих сравнений сортировки: если есть какие-либо одинаковые ключи, обнаруживается конфликт и возникает ошибка.
Для каждого кортежа, подлежащего операции INSERT, UPDATE, COPY FROM и пр., он сначала вставляется в таблицу, а затем сразу после этого проверяется на уникальность при вставке для него элемента индекса. Если значение не уникально, возникнет ошибка, что приведет к прерыванию транзакции, и кортеж станет невидимым благодаря MVCC. Проверка видимости для кортежей в таблицах кучи очень проста, поскольку вся информация о видимости кортежа хранится в нем самом (xmin и xmax): достаточно выбрать кортеж по его идентификатору и сравнить xmin/xmax с моментальным снимком.
Однако, кортежи AO-таблиц не размещают свои метаданные видимости вместе с их фактическими данными, т.е. не хранят внутри себя поля xmin/xmax, что затрудняет проверку видимости. Для этих таблиц нужно посмотреть на их вспомогательные отношения (каталог блоков и карту видимости), которые сами являются таблицами кучи, чтобы определить видимость.
Поиск по индексу в таблицах AO/CO реализован с дополнительным уровнем косвенности. Идентификаторы из записи индекса разбиваются на номер файла сегмента и номер строки, которые используются для поиска по индексу отношения каталога блоков. Результатом поиска является строка каталога блока, которая может сообщить смещение блока, содержащего кортеж. После этого можно получить кортеж, просканировав блок. Одна строка каталога блоков содержит такого рода информацию для диапазона кортежей данных: xmin кортежа каталога блока эквивалентен xmin кортежа данных. Поэтому информация о транзакциях серии кортежей хранится в разреженном виде, что соответствует идее пакетной загрузки AO-таблиц. Можно определить видимость кортежа, просто взглянув на поля xmin и xmax строки каталога блоков, даже без сканирования кортежа данных с диска.
Таблицы, ориентированные на столбцы, имеют несколько записей каталога блоков (по одной для каждого столбца), охватывающих диапазон кортежей данных. Но для определения видимости кортежа нужно найти запись только для 1 существующего столбца, то есть не нужно искать каждую запись для каждого столбца. Поэтому механизм, реализованный в Greenplum 7, подходит как для строковых, так и для колоночных таблиц.
Когда кортеж УДАЛЯЕТСЯ из AO-таблицы, подобно таблицам кучи, они физически не удаляются из relfile. Физическое удаление происходит во время операции очистки (VACUUM). Аналогично физическое удаление записи индекса для кортежа также откладывается до VACUUM. Строка карты видимости записывает метаданные, сообщающие об удалении кортежа. Запись каталога блоков для этой строки НЕ удаляется. Как и строки каталога блоков, одна строка visimap охватывает несколько строк данных. Поэтому видимая строка visimap, покрывающая данную строку данных, означает, что строка данных удалена. Таким образом, при выполнении проверки уникального индекса, даже если есть видимая строка каталога блоков, это не обязательно приводит к конфликту. Строка могла быть удалена, поэтому нужно выполнить дополнительный поиск отношения visimap.
Благодаря запрету на одновременные операции UPDATE/DELETE для AO-таблиц, отсутствуют проблемы с уникальностью строк в visimap сразу после удаления кортежа данных. Отложенная очистка (Lazy VACUUM, без ключевого слова FULL) для AO-таблиц сильно отличается от таблиц кучи. В отличие от таблиц кучи, в AO-таблицах у кортежей изменены их идентификаторы, т.к. активные кортежи на самом деле перемещаются из одного seg-файла в другой, а не остаются в исходном файле сегмена, который в дальнейшем будет сжат. Поэтому отложенная очистка в AO-таблицах Greenplum может выполняться одновременно со вставками, что требует разрешения конфликтов. Это реализовано в 7-ой версии MPP-СУБД благодаря механизму проверки уникальности индекса. Другие операции, такие как DELETE, не нужно профилировать, так как нет взаимодействия с уникальным индексом для задействованных путей кода: следует учитывать только входные данные. Подробнее про команду очистки в GP читайте в нашей новой статье.
В заключение отметим, что построение индексов после загрузки данных с помощью CREATE INDEX более производительно, чем индексы, обновляемые для каждого кортежа во время загрузки. Уникальные индексы не являются исключением. На практике почти нет разницы во времени сборки таблиц кучи по сравнению с АО-таблицами или уникального индекса по сравнению с неуникальным индексом. Сообщая оптимизатору об уникальности столбца, уникальные индексы могут ускорить SQL-запросы, приводя к ряду оптимизаций, включая повышение селективности в запросах с JOIN. Очистка AO-таблиц имеет дополнительное преимущество, заключающееся в очистке вспомогательных отношений, чтобы сократить время проверки уникальности. Что еще полезного появилось в работе с AO-таблицами в свежем выпуске Greenplum, читайте в нашей новой статье. А здесь вы узнаете, как в новой версии Greenplum стала возможной кластеризация AO/CO-таблиц по кластерным индексам и чем это полезно.
Освойте администрирование и эксплуатацию Greenplum с Arenadata DB для эффективного хранения и аналитики больших данных на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:
Источники
