Префиксные индексы

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

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

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

подсказка

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

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

Начиная с версии 1.5.0, таблицы Primary Key поддерживают определение ключей сортировки с помощью ORDER BY. Начиная с версии 1.5.0, таблицы Duplicate Key, Aggregate и Unique Key поддерживают определение ключей сортировки с помощью ORDER BY.

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

  к сведению

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

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

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

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

Возьмем в качестве примера таблицу Duplicate 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 index составляет 36 байт, превышающая часть будет обрезана. Поэтому каждая запись в Prefix index этой таблицы представляет собой uid (4 байта) + name (берутся только первые 32 байта), а префиксными полями являются uid и name.

Примечание

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

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

  Возьмем следующую таблицу в качестве примера, где первые три столбца являются столбцами ключа сортировки. Префиксным полем этой таблицы является name (20 байт). Это происходит потому, что этот Prefix index начинается со столбца типа VARCHAR (name) и обрезается напрямую без включения дальнейших столбцов, даже если запись Prefix index не достигает 36 байт в длину. Поэтому этот Prefix index содержит только поле 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)

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

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

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

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

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

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

    подсказка

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

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

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

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

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

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

Можно ли изменить Prefix index?

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

Начиная с версии 1.5.0, ключи сортировки таблиц Primary Key могут быть изменены. А начиная с версии 1.5.0, ключи сортировки таблиц Duplicate Key, Aggregate и Unique Key могут быть изменены. Ключи сортировки в таблицах Duplicate Key и Primary Key могут быть комбинацией любых столбцов сортировки. Ключи сортировки в таблицах Aggregate и Unique Key должны включать все ключевые столбцы, но порядок этих столбцов не обязательно должен соответствовать ключевым столбцам.

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

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

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