Данная статья является четвёртой частью перевода руководства по повышению производительности Firebird за авторством А.Ковязина и Э.Грегорио от 23.05.2024 (и потому продолжается сквозная нумерация пунктов), а так же текстовой расшифровкой соответствующего видео.

4.1. Проверка не активных или неактивированных индексов

Индексы в базе данных Firebird могут находиться в трёх различных состояниях: активное, неактивное или неактивированное.

Активный индекс – это стандартный индекс, который используется системой для оптимизации выполнения запросов к базе данных. Он активно участвует в процессе поиска и сортировки данных, что позволяет значительно ускорить выполнение операций.
Не активный индекс – это индекс, который был отключён вручную с помощью команды ALTER INDEX ... INACTIVE. В этом случае он больше не используется при обработке запросов, хотя технически продолжает существовать в структуре базы данных.
Неактивированный индекс - это индекс, который должен был быть включен в процессе восстановления с помощью утилиты gbak, однако этот процесс завершился преждевременно, как правило, по причине возникновения какой-либо ошибки. В идеале, не активированных индексов в базе данных быть не должно. Индекс, находящийся в не активированном состоянии, не используется оптимизатором запросов, следовательно, он не участвует в оптимизации выборок данных.

4.2 Проверка состояния индексов

Для того чтобы убедиться, что все индексы базы данных находятся в активном и рабочем состоянии, необходимо провести тщательную проверку их текущего статуса и воспользоваться следующим SQL-запросом:

SELECT count(*) FROM RDB$INDICES WHERE RDB$INDEX_INACTIVE<>0;

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

Однако, если результат запроса не равен нулю, это свидетельствует о наличии как минимум одного не активированного индекса. Это может указывать на возможные проблемы с индексами или неполадки в системе.

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

SELECT RDB$INDEX_NAME,RDB$INDEX_INACTIVE FROM RDB$INDICES WHERE RDB$INDEX_INACTIVE<>0;

Этот запрос вернет имена всех не активированных индексов вместе со значением поля RDB$INDEX_INACTIVE. Значение 1 говорит о том, что индекс не активирован, тогда как значение 3 говорит о его частичной активности (или неполной активации).

Активация не активированных индексов

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

ALTER INDEX <indexnamexxx> ACTIVE;

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

4.3 Пересчёт селективности (статистики) индексов

Оптимизатор запросов в системах управления базами данных Firebird и InterBase принимает свои решения, опираясь на два основных параметра: размер таблицы (cardinality) и селективность индексов (selectivity). Селективность индекса показывает, насколько эффективно этот индекс помогает находить необходимые данные среди большого количества записей. Высокая селективность обеспечивает более точные и быстрые запросы, которые используют соответствующий индекс.

Селективность индекса рассчитывается как величина, обратная числу уникальных значений в индексируемом столбце (или нескольких столбцах). Это значит, что она вычисляется путём деления единицы на количество этих уникальных значений. Например, если у вас есть индекс по первичному ключу для таблицы, которая содержит 10 тысяч записей, то селективность данного индекса будет равна 1 / 10000 = 0,0001. Если же в той же таблице есть столбец с только десятью различными значениями, то селективность индекса по этому столбцу составит 1 / 10 = 0,1. Стоит заметить, что для оптимизатора базы данных низкая селективность индекса является предпочтительной.

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

Пересчёт селективности индексов выполняется в следующих ситуациях:

при создании нового индекса;
при выполнении команды ALTER INDEX ACTIVE;
при использовании команды SET STATISTICS INDEX indexname.

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

Ещё одна ситуация: вы создаёте таблицу, задаёте для неё индексы, наполняете её данными, делаете резервную копию и восстанавливаете базу данных (при восстановлении индексы воссоздаются заново, и их селективность обновляется). Далее вы вносите изменения в данные одной или нескольких таблиц — например, добавляете, удаляете или изменяете примерно 30–40% строк. После таких манипуляций текущие показатели селективности теряют свою актуальность.

Чтобы просмотреть актуальные значения селективности индексов, можно использовать специальный SQL-запрос. Важно отметить, что селективность неактивных индексов будет отображаться как «-1».

Пересчёт статистики индексов в базе данных Firebird представляет собой один из методов повышения эффективности выполнения запросов. Статистика индексов предоставляет системе Firebird данные об их селективности, что помогает оптимизатору запросов выбрать наилучший план исполнения того или иного запроса. Ниже приведён подробный алгоритм действий для пересчёта статистики индексов в базе данных Firebird:

Подключение к базе данных В первую очередь необходимо подключиться к вашей базе данных, используя любой удобный инструмент администрирования, например isql, FlameRobin или другой аналогичный инструмент.
Подключение к базе данных В первую очередь необходимо подключиться к вашей базе данных, используя любой удобный инструмент администрирования, например isql, FlameRobin или другой аналогичный инструмент.
- Подключение к базе данных В первую очередь необходимо подключиться к вашей базе данных, используя любой удобный инструмент администрирования, например isql, FlameRobin или другой аналогичный инструмент.
- Обновление статистики индекса После подключения к базе данных выполните SQL-команду для обновления статистики конкретного индекса
  
  SET STATISTICS INDEX <index_name>;
- Фиксация изменений Чтобы зафиксировать внесенные изменения, выполните следующую команду:
  COMMIT;
- Проверка обновленной статистики Для просмотра результатов обновления статистики можно использовать следующую команду:
  select i.rdb$relation_name, i.rdb$index_name, i.rdb$statistics from rdb$indices i order by 1, 2;
Выполнение этих шагов позволит вам поддерживать актуальность статистики индексов и тем самым улучшить общую производительность вашей базы данных Firebird.
Вот и все. Вы пересчитали статистику индексов в базе данных Firebird. Вы можете делать это периодически, особенно после больших изменений в базе, чтобы сохранить эффективность работы индексов.
4.4. Проверка состояния индексов и пересчет статистики с использованием HQbird
Чтобы проверить состояние индексов с помощью HQbird, выполните следующие действия:
- Зайдите в веб-интерфейс по адресу http://localhost:8082 и выберите задание «Пересчет статистики индексов» для зарегистрированной базы данных. Это задание выполнит обе задачи.
- Откройте диалог и включите задание.
- Задание будет выполнять как пересчет статистики, так и проверку состояния индекса. Расписание по умолчанию - еженедельно по субботам в 02:00 утра. Если вам нужно срочно пересчитать статистику индексов (например, если вы только что установили HQbird), вы можете нажать кнопку «Запустить сейчас» в виджете задания.
- Для баз данных размером более 20 ГБ мы рекомендуем пересчитывать индексы ежемесячно, а не еженедельно.
На этом этапе мы завершаем текущую часть нашего руководства.
Ссылка на видеоверсию на Youtube: https://youtu.be/RSl6oDQAdvk
Ссылка на Rutube: https://rutube.ru/video/91f59113d5666994284eddedcc9ca569/
Ссылка на Платформу: https://plvideo.ru/watch?v=uTWZIc2K7siC
Официальный канал на Substack: https://firebirdfoundation.substack.com/ - подписывайтесь, что бы получать информацию из первых рук.
Сообщество Firebird в ВК: https://vk.com/firebird_friday
Чат Firebird в Телеграме: https://t.me/firebird_friday

Четвёртый (и предпоследний) шаг к повышению производительности Firebird