Работа с индексами¶

Особенности индексов key-value в TCS¶

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

Этот вид индексов работает только для запросов на равенство значений. Запросы на сравнение значений (больше/меньше) пока не поддерживаются.

Создание индекса key-value¶

См. CREATE INDEX.

Удаление индекса key-value¶

См. DROP INDEX.

Перестроение индекса key-value¶

Для всех индексов key-value проводится автоматическая переиндексация по мере изменения данных в таблице. Принудительно перестраивать индексы вручную не требуется.

Первичные индексы¶

Первичный индекс задается при создании таблицы. Он может быть:

• задан пользователем, например:

  CREATE TABLE t(
      a int,
      b int,
      c int,
      PRIMARY KEY(a)
  )
  

• либо создан автоматически, например:

  CREATE TABLE t(
      a int,
      b int,
      c int
  )
  

  Здесь в таблицу t будет автоматически добавлено поле rowid (оно станет первым полем в таблице), а по нему будет создан первичный индекс.

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

Первичный индекс должен быть уникальным.

Он может строиться как по одному столбцу, так и по нескольким (см. составные индексы).

Вторичные индексы¶

Вторичные индексы могут быть только неуникальными (задать UNIQUE для них в текущей версии TCS нельзя).

Они могут строиться как по одному столбцу, так и по нескольким (см. составные индексы).

Составные индексы¶

Составной индекс может содержать более одного столбца.

Для создания составных KV-индексов поддерживаются запросы следующего вида:

• первичный составной индекс:

  CREATE TABLE t(
      a int,
      b int,
      c int,
      PRIMARY KEY(a, c)
  )
  

• вторичный составной индекс:

  CREATE INDEX SYNC test_index ON departments(name, manager, size);
  

Чтение с использованием индекса осуществляется при наличии условия (в т.ч. сортировки) по первому или по нескольким полям индекса. Например:

SELECT * FROM departments WHERE name = 'HR' AND manager != 'Ivanov' ORDER BY size;

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

Порядок сортировки по индексу¶

По умолчанию, все индексы в TCS создаются с возрастающим порядком сортировки. Например, инструкция CREATE INDEX t(a,b,c) имеет тот же эффект, что и CREATE INDEX t(a ASC, b ASC, c ASC).

При необходимости порядок сортировки для индекса, заданного пользователем (как первичного, так и вторичного), можно задать с помощью ключевых слов desc (убывающий порядок) и asc (возрастающий порядок). Для составных индексов порядок сортировки можно задавать индивидуально для каждой компоненты. Например:

-- первичный составной индекс
CREATE TABLE t(a int, b int, primary key(b DESC, a ASC))

-- вторичный составной индекс
CREATE INDEX my_ind ON t(b DESC, a ASC)

Для первичного индекса, создаваемого автоматически (rowid), также можно явным образом задать порядок сортировки. Например:

CREATE TABLE t(a int, primary key(rowid DESC))

Здесь выборка по первичному ключу вернет данные в порядке, обратном порядку их вставки.

Порядок чтения по индексу¶

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

CREATE INDEX i ON t(a, rowid DESC)

В результате при чтении по индексу i данные будут предоставляться в порядке убывания rowid.

Также можно явно задавать порядок чтения по вторичному индексу, в состав которого входит автоматически создаваемый первичный индекс (rowid). Например:

CREATE INDEX sec ON t(a, rowid desc)

Здесь записи в выборке по составному индексу a, rowid будут отсортированы вначале по a (в убывающем порядке), а затем внутри одинаковых групп – по rowid (в том порядке, который был указан при создании первичного индекса по rowid).

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

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

Оптимизации при работе с индексами¶

Индексы (и первичные, и вторичные) оптимизируют запросы с указанием равенства и диапазона значений.

Для эффективной работы фильтрующее выражение в запросе должно сначала задавать условия равенства по первым компонентам индекса (если он составной), а затем – условия вхождения в диапазон для последующей компоненты: col1=x1, col2=x2, ..., coln in [ln, rn]

Например, если индекс состоит из одной компоненты (a), то с его использованием можно сделать запросы со следующими фильтрами:

a = 1
a > 1
a > 2 AND a < 3
a < 3

Если индекс содержит две компоненты (a, b), то в запросе можно задать следующие фильтры:

a = 1 AND b > 2
a = 1 AND b = 1
a = 1 AND b > 3
a > 1
a > 1 AND a < 10

Индексы используются планировщиком:

• при сканировании, например:

  explain select * from t where a = 1 and c = 2
  ----
  logical_plan TableScan: t projection=[a, b, c], full_filters=[t.a = Int32(1), t.c = Int32(2)]
  physical_plan
  01)IndexScanExec: table=tcs.public.t, index=pk_tcs_public_t, direct_projection=[a, b, c]
  02)--Range: (a = 1) AND (c = 2)
  

• при поиске по диапазону, например:

  explain select * from t where a > $1 and a < $10
  ----
  logical_plan TableScan: t projection=[a, b, c], full_filters=[t.a > $1, t.a < $10]
  physical_plan
  01)IndexScanExec: table=tcs.public.t, index=pk_tcs_public_t, direct_projection=[a, b, c]
  02)--Range: $1 < a < $10
  

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

Таким образом, при сканировании исполняются проекции двух видов:

• прямая проекция – проводится только с использованием первичного индекса.

• косвенная проекция – проводится дополнительно для каждой записи с использованием вторичного индекса.

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

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

CREATE TABLE t(a int, b int, c int)

-- создаем вторичный индекс
CREATE INDEX SYNC ta ON t(a)

EXPLAIN SELECT * FROM t WHERE a = 1
----
logical_plan TableScan: t projection=[a, b, c], full_filters=[t.a = Int32(1)]
physical_plan
01)IndexScanExec: table=tcs.public.t, index=ta, direct_projection=[a, rowid], indirect_projection=[b, c]
02)--Range: a = 1

Здесь планировщик производит выборку по индексу ta. Сначала он смог исполнить проекцию колонки a и rowid с помощью первичного индекса (прямая проекция). А для колонок b и c пришлось построчно исполнить косвенные проекции.

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