Prefix индексы

Укажите одну или несколько колонок для формирования sort key при создании таблицы. Строки данных в таблице будут отсортированы на основе sort key и затем сохранены на диске.

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

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

подсказка

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

Примечания по использованию

Начиная с версии 3.0, таблицы Primary Key поддерживают определение sort keys с использованием ORDER BY. Начиная с версии 3.3, таблицы Duplicate Key, таблицы Aggregate и таблицы Unique Key поддерживают определение sort keys с использованием ORDER BY.

• Данные в таблице Duplicate Key сортируются в соответствии с sort key ORDER BY. Sort key может быть комбинацией любых колонок.

  к сведению

  Когда указаны и ORDER BY, и DUPLICATE KEY, DUPLICATE KEY не вступает в силу.

• Данные в таблице Aggregate сначала агрегируются в соответствии с aggregate key AGGREGATE KEY, затем сортируются в соответствии с sort key ORDER BY. Колонки в ORDER BY и AGGREGATE KEY должны быть одинаковыми, но порядок колонок не обязательно должен быть одинаковым.

• Данные в таблице Unique Key сначала заменяются в соответствии с unique key UNIQUE KEY, затем сортируются в соответствии с sort key ORDER BY. Колонки в ORDER BY и UNIQUE KEY должны быть одинаковыми, но порядок колонок не обязательно должен быть одинаковым.

• Данные в таблице Primary Key сначала заменяются в соответствии с primary key PRIMARY KEY, затем сортируются в соответствии с sort key ORDER BY.

Возьмем в качестве примера таблицу Duplicate Key. Sort key определяется как uid и name с использованием ORDER BY.

CREATE TABLE user_access (
    uid int,
    name varchar(64),
    age int,
    phone varchar(16),
    last_access datetime,
    credits double
)
ORDER BY (uid, name);

подсказка

После создания таблицы вы можете использовать SHOW CREATE TABLE <table_name>;, чтобы просмотреть указанные колонки сортировки и порядок этих колонок в предложении ORDER BY из возвращенного результата.

Поскольку максимальная длина записи Prefix индекса составляет 36 байт, превышающая часть будет обрезана. Поэтому каждая запись в Prefix индексе этой таблицы - это uid (4 байта) + name (берутся только первые 32 байта), и prefix поля - это uid и name.

Примечание

• Количество prefix полей не может превышать 3, а максимальная длина записи Prefix индекса составляет 36 байт.

• В пределах prefix полей колонки типа CHAR, VARCHAR или STRING могут встречаться только один раз и должны находиться в конце.

  Возьмем в качестве примера следующую таблицу, где первые три колонки являются колонками sort key. Prefix поле этой таблицы - name (20 байт). Это связано с тем, что этот Prefix индекс начинается с колонки типа VARCHAR (name) и обрезается напрямую без включения дальнейших колонок, даже если запись Prefix индекса не достигает 36 байт в длину. Поэтому этот Prefix индекс содержит только поле name.

  MySQL [example_db]> describe user_access2;
  +-------------+-------------+------+-------+---------+-------+
  | Field       | Type        | Null | Key   | Default | Extra |
  +-------------+-------------+------+-------+---------+-------+
  | name        | varchar(20) | YES  | true  | NULL    |       |
  | uid         | int         | YES  | true  | NULL    |       |
  | last_access | datetime    | YES  | true  | NULL    |       |
  | age         | int         | YES  | false | NULL    |       |
  | phone       | varchar(16) | YES  | false | NULL    |       |
  | credits     | double      | YES  | false | NULL    |       |
  +-------------+-------------+------+-------+---------+-------+
  6 rows in set (0.00 sec)

• Если sort key указан в таблице с использованием ORDER BY, Prefix индекс формируется на основе sort key. Если sort key не указан с использованием ORDER BY, Prefix индекс формируется на основе Key колонок.

Как правильно спроектировать sort key для формирования Prefix индекса, который может ускорить запросы

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

• Количество колонок sort key обычно составляет 3 и не рекомендуется превышать 4. Sort key со слишком большим количеством колонок не может улучшить производительность запросов, но увеличивает накладные расходы на сортировку при загрузке данных.
• Рекомендуется приоритезировать колонки для формирования sort key в порядке, указанном ниже:
  1. Выберите колонки, которые часто используются в условиях фильтрации запросов, в качестве колонок sort key. Если количество колонок sort key больше одной, расположите их в порядке убывания их частоты в условиях фильтрации запросов. Таким образом, если условия фильтрации запросов включают prefix Prefix индекса, производительность запроса может быть значительно улучшена. И если условия фильтрации включают весь prefix Prefix индекса, запрос может полностью использовать Prefix индекс. Конечно, если условия фильтрации включают prefix, хотя и не весь prefix, Prefix индекс все еще может оптимизировать запрос. Однако эффект Prefix индекса будет ослаблен, если длина prefix, включенного в условия фильтрации, слишком мала. Опять же, возьмем таблицу Unique Key, чей sort key - (uid,name), в качестве примера. Если условия фильтрации запроса включают весь prefix, например select sum(credits) from user_access where uid = 123 and name = 'Jane Smith';, запрос может полностью использовать Prefix индекс для повышения производительности. Если условия запроса включают только часть prefix, например select sum(credits) from user_access where uid = 123;, запрос также может воспользоваться Prefix индексом для повышения производительности. Однако если условия запроса не включают prefix, например select sum(credits) from user_access where name = 'Jane Smith';, запрос не может использовать Prefix индекс для ускорения.

  • Если несколько колонок sort key имеют схожую частоту в качестве условий фильтрации запросов, вы можете измерить кардинальность этих колонок.

  • Если кардинальность колонки высока, она может отфильтровать больше данных во время запроса. Если кардинальность слишком низкая, например для колонок булева типа, ее эффект фильтрации не идеален.

    подсказка

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

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

Соображения по определению колонок sort key при создании таблицы

• Типы данных колонок сортировки:

  • Колонки сортировки в таблицах Primary Key поддерживают числовые типы (включая целые числа, булевы значения), строки и типы date/datetime.
  • Колонки сортировки в таблицах Duplicate Key, таблицах Aggregate и таблицах Unique Key поддерживают числовые типы (включая целые числа, булевы значения, десятичные числа), строки и типы date/datetime.

• В таблицах Aggregate и таблицах Unique Key колонки сортировки должны быть определены перед другими колонками.

Можно ли изменить Prefix индекс?

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

Начиная с версии 3.0, sort keys таблиц Primary Key могут быть изменены. И начиная с версии 3.3, sort keys таблиц Duplicate Key, таблиц Aggregate и таблиц Unique Key могут быть изменены. Sort keys в таблицах Duplicate Key и таблицах Primary Key могут быть комбинацией любых колонок сортировки. Sort keys в таблицах Aggregate и таблицах Unique Key должны включать все key колонки, но порядок этих колонок не обязательно должен соответствовать key колонкам.

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

Как проверить, ускоряет ли Prefix индекс запросы

После выполнения запроса вы можете проверить, вступает ли в силу Prefix индекс, и просмотреть его эффект фильтрации по детальным метрикам, таким как ShortKeyFilterRows, в узле сканирования в Query Profile.