Использование CONNECT - Индексирование

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

CONNECT предоставляет четыре типа индексирования:

1. Стандартное индексирование
2. Индексирование блоками
3. Удаленное индексирование
4. Динамическое индексирование

Стандартное индексирование

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

Вы можете определить их в операторе CREATE TABLE, либо с помощью оператора CREATE INDEX, либо оператора ALTER TABLE. Во всех случаях файлы индексов автоматически создаются. Их можно удалить с помощью оператора DROP INDEX или оператора ALTER TABLE, и это удалит файлы индексов.

Индексы автоматически перестраиваются при создании таблицы, изменении ее с помощью команд INSERT, UPDATE или DELETE, или при изменении параметра SEPINDEX. Если у вас много изменений в таблице в один момент, вы можете использовать блокировку таблицы, чтобы предотвратить перестройку индексов после каждой команды. Индексы будут перестроены при разблокировке таблицы. Например:

lock table t1 write;
insert into t1 values(...);
insert into t1 values(...);
...
unlock tables;

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

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

В отличие от других движков хранилища, CONNECT создает индексы как файлы, которые по умолчанию названы по имени файла данных, а не имени таблицы, и расположены в каталоге файла данных. В зависимости от параметра таблицы SEPINDEX, индексы сохраняются в одном уникальном файле или в отдельных файлах (если SEPINDEX истинно). Например, если индексы находятся в отдельных файлах, первичный индекс таблицы dept.dat типа DOS — это файл с именем dept_PRIMARY.dnx. Это позволяет определять несколько таблиц в одном файле данных с различными опциями, такими как сопоставленные или не сопоставленные, а также совместно использовать файлы индексов.

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

Примечание 1: Индексируемые столбцы должны быть объявлены NOT NULL; CONNECT не поддерживает индексы, содержащие нулевые значения.

Примечание 2: MRR используется стандартным индексированием, если оно включено.

Примечание 3: Индексирование префиксов не поддерживается. Если указано, движок CONNECT игнорирует префикс и создает полный индекс.

Обработка ошибок индексирования

Способ обработки индексирования в CONNECT очень специфичен. Все изменения в таблице выполняются независимо от индексирования. Только после изменения таблицы или при отправке команды OPTIMIZE TABLE индексы создаются. Если возникает ошибка, соответствующий индекс не создается. Однако, CONNECT, будучи не транзакционным движком, не может откатить изменения, внесенные в таблицу. Основные причины ошибок индексирования:

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

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

В обоих случаях после исправления ошибки перестройте индексы с помощью команды OPTIMIZE TABLE.

Сопоставление файлов индексов

Для ускорения процесса индексирования CONNECT создает структуру индекса в памяти из файла индекса. Это можно сделать, прочитав файл индекса или используя его как будто он находится в памяти с помощью «сопоставления файла». В включенных версиях сопоставление файла используется в соответствии с булевым системным параметром connect_indx_map. Установите его в 0 (чтение файла) или 1 (сопоставление файла).

Индексирование блоками

Для ускорения операций ввода-вывода CONNECT по возможности использует режим чтения/записи блоками по n строк, где n — значение, заданное в опции BLOCK _ SIZE при создании таблицы, или значение по умолчанию в зависимости от типа таблицы. Это автоматически выполняется для фиксированных файлов (FIX, BIN, DBF или VEC), но должно быть указано для переменных файлов (DOS, CSV или FMT).

Для таблиц с блоками дальнейшая оптимизация может быть достигнута, если значения данных для некоторых столбцов «сгруппированы», то есть они не равномерно распределены в таблице, а сгруппированы в некоторых последовательных строках. Индексирование блоками позволяет пропускать блоки, в которых ни одна строка не удовлетворяет условному предикату, даже не читая блок. Это особенно верно для отсортированных столбцов.

Вы указываете это при создании таблицы, используя опцию столбца DISTRIB =d. Значение перечисления d может быть scattered, clustered или sorted. Как правило, только один столбец может быть отсортирован. Индексирование блоками используется только для сгруппированных и отсортированных столбцов.

Разница между стандартным индексированием и индексированием блоками

• Индексирование блоками внутренне обрабатывается CONNECT при последовательном чтении данных таблицы. Это означает, что при использовании стандартного индексирования для таблицы индексирование блоками не используется.
• В запросе может быть использован только один стандартный индекс. Однако индексирование блоками может объединять ограничения, поступающие от условия where, подразумевающего несколько сгруппированных/отсортированных столбцов.
• Файлы индекса блоков быстрее создаются и значительно меньше, чем файлы стандартных индексов.

Примечания для этого выпуска:

• При всех операциях создания или изменения таблицы CONNECT автоматически рассчитывает или пересчитывает и сохраняет значения min/maxi или битовые карты для каждого блока, позволяя пропускать блоки, не содержащие приемлемых значений. В случае, если оптимизированный файл больше не соответствует таблице, потому что он был случайно уничтожен или потому что были изменены некоторые определения столбцов, вы можете использовать команду OPTIMIZE TABLE для перестроения оптимизированного файла.
• Специальная обработка отсортированных столбцов в настоящее время ограничена сортировкой по возрастанию. Столбцы, отсортированные по убыванию, должны быть помечены как сгруппированные. Неправильная сортировка не проверяется в операциях Update или Insert, но отмечается при оптимизации таблицы.
• Индексирование блоками может выполняться двумя способами. Сохраняя значения min/max, существующие для каждого блока, или сохраняя битовую карту, позволяющую узнать, какие значения отдельных столбцов встречаются в каждом блоке. Этот второй способ часто обеспечивает лучшую оптимизацию, за исключением отсортированных столбцов, для которых оба способа эквивалентны. Подход с битовой картой может быть применен только к столбцам, имеющим не слишком много различных значений. Это оценивается по значению параметра MAX _ DIST, связанному со столбцом при создании таблицы. Индексирование блоками с битовой картой будет использоваться, если это число не больше, чем значение MAXBMP для базы данных.
• CONNECT не может выполнять индексирование блоками для столбцов символьных данных, не чувствительных к регистру. Чтобы принудительно выполнить индексирование блоками для символьного столбца, укажите его кодировку как нечувствительную к регистру, например, как двоичную. Однако это также будет применяться ко всем другим условиям, этот столбец теперь чувствителен к регистру.

Удаленное индексирование

Удаленное индексирование специфично для типа таблицы MYSQL. Оно эквивалентно тому, что делает движок хранилища FEDERATED. Таблица MYSQL не поддерживает индексы как таковые. Поскольку доступ к таблице обрабатывается удаленно, это удаленная таблица, которая поддерживает индексы. То, что делает таблица MYSQL, — это просто добавление условия WHERE к команде SELECT, отправляемой удаленному серверу, что позволяет удаленному серверу использовать индексирование, если это применимо. Обратите внимание, однако, что поскольку CONNECT добавляет по возможности все или часть условия where исходного запроса, это часто происходит даже если удаленный индексированный столбец не объявлен локально индексированным. Единственный, но очень важный случай, когда столбец следует объявить локально индексированным, — это когда он используется для объединения таблиц. В противном случае необходимое условие WHERE не будет добавлено к отправленному запросу SELECT.

См. Индексирование таблиц MYSQL для получения дополнительной информации.

Динамическое индексирование

Индекс, созданный как «динамический», является стандартным индексом, который в некоторых случаях может быть перестроен для конкретного запроса. Это происходит, в частности, для некоторых запросов, где две таблицы объединяются по индексированному ключу столбца. Если таблица «from» большая, а таблица «to» уменьшается в размерах из-за условия WHERE, может быть целесообразно перестроить индекс на этой уменьшенной таблице.

Из-за времени, затрачиваемого на перестройку индекса, это будет целесообразно только если время, выигранное за счёт уменьшения размера индекса, больше этого времени перестройки. Вот почему это не следует делать, если таблица «from» небольшая, потому что не будет достаточного количества строк для объединения, чтобы компенсировать дополнительное время. В противном случае преимущества использования динамического индекса:

• Время индексирования немного быстрее, если индекс меньше.
• Процесс объединения вернёт только строки, удовлетворяющие условию WHERE.
• Поскольку таблица читается последовательно при перестройке индекса, MRR не требуется.
• Создание индекса может быть быстрее, если таблица уменьшена с помощью индексирования блоками.
• При создании индекса CONNECT также сохраняет в памяти значения других используемых столбцов.

Этот последний пункт особенно важен. Он означает, что после реконструирования индекса объединение выполняется по временной таблице в памяти.

К сожалению, движки хранения, вызываемые MariaDB независимо для каждой таблицы, не имеют глобальной информации, чтобы определить, когда целесообразно использовать динамическую индексацию. Вот почему следует использовать ее только в тех случаях, когда вы видите, что некоторые важные запросы объединения занимают очень много времени и только для столбцов, используемых для объединения таблиц. Как объявить индекс динамическим, — с помощью опции DYNAM индекса типа Boolean. Например, запрос:

select d.diag, count(*) cnt from diag d, patients p where d.pnb =
p.pnb and ageyears < 17 and county = 30 and drg <> 11 and d.diag
between 4296 and 9434 group by d.diag order by cnt desc;

Такой запрос, объединяющий таблицу diag с таблицей patients, может занимать очень много времени, если таблицы большие. Чтобы объявить первичный ключ в столбце pnb таблицы patients динамическим:

alter table patients drop primary key;
alter table patients add primary key (pnb) comment 'DYNAMIC' dynam=1;

Примечание 1: Комментарий здесь не является обязательным, но полезен для того, чтобы увидеть, что индекс является динамическим, если вы используете команду SHOW INDEX.

Примечание 2: В настоящее время нет способа просто изменить опцию DYNAM без удаления и добавления индекса. К сожалению, это занимает время.

Виртуальная индексация

Она применяется только к виртуальным таблицам типа VIR и должна быть сделана по столбцу, определяющему SPECIAL=ROWID или SPECIAL=ROWNUM.