Влияние индексов на производительность операций INSERT, UPDATE и DELETE в Firebird SQL

В целом, индексы необходимы для любой серьезной базы данных, поскольку они критически важны для ускорения запросов с предложением WHERE (SELECT, UPDATE, MERGE, DELETE и т.д.). Однако каждый индекс имеет свою цену, и эта цена очевидна при сравнении скорости операций INSERT/UPDATE/DELETE по индексированным и неиндексированным полям.

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

Тестовая база данных

Создадим простую базу данных Firebird:

CREATE DATABASE “E:\TESTFIREBIRDINDEX.FDB” USER “SYSDBA” PASSWORD “masterkey”;
CREATE TABLE TABLEIND1 (
    I1            INTEGER NOT NULL PRIMARY KEY,
    NAME          VARCHAR(250),
    MANORWOMAN  SMALLINT
);
CREATE GENERATOR G1;
SET TERM ^ ;

create or alter procedure INS1MLN
returns (
    INSERTED_CNT integer)
as
BEGIN
inserted_cnt = 0;
WHILE (inserted_cnt <1000000) DO
BEGIN
 Insert into tableind1(i1, name, manorwoman) values(gen_id(g1,1), 'TEST name', (:inserted_cnt - (:inserted_cnt/2)*2));
 inserted_cnt=inserted_cnt+1;
END
suspend;
END^
SET TERM ; ^
GRANT INSERT ON TABLEIND1 TO PROCEDURE INS1MLN;
GRANT EXECUTE ON PROCEDURE INS1MLN TO SYSDBA;
COMMIT;

Чтобы продемонстрировать проблему с плохими индексами, выполним следующие операции:

• Сначала вставим 1 миллион записей в таблицу
• Затем обновим эти записи
• После этого удалим все записи
• И, наконец, выполним SELECT count(*) из таблицы

Приведенные ниже команды выполняют описанные операции:

set stat on;  /*включить отображение статистики в isql*/
select * from ins1mln;
update tableind1 SET MANORWOMAN  = 3;
delete from tableind1;
select count(*) from tableind1;

Запустим их и сохраним результаты для дальнейшего анализа.

После этого создадим другую таблицу с той же структурой - и добавим туда индекс для столбца MANORWOMAN

CREATE INDEX TABLEIND1_IDX1 ON TABLEIND1 (MANORWOMAN);

Как видно выше в скрипте, мы вставляем в этот столбец только целочисленные значения 0 или 1. Такой индекс бесполезен для запросов SELECT, и в теории все разработчики баз данных должны избегать таких плохих индексов (за исключением особого случая с несбалансированным распределением значений в таблице), но на практике существует множество индексов с 2 уникальными значениями или даже с 1 из них.

Затем повторим скрипт с этим индексом и сравним результаты - см. следующую таблицу:

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

Давайте получим статистику для этой тестовой базы данных (с плохим индексом для MANORWOMAN) и попробуем найти некоторые детали. Для сбора статистики выполним следующую команду:

gstat -r e:\testfirebirdindex.fdb > e:\teststat.txt

Раздел статистики таблицы TABLEIND1 и индексов выглядит интригующе, но какую полезную информацию он нам дает?

TABLEIND1 (128)
    Primary pointer page: 166, Index root page: 167
    Average record length: 0.00, total records: 1000000
    Average version length: 27.00, total versions: 1000000, max versions: 1
    Data pages: 16130, data page slots: 16130, average fill: 93%
    Fill distribution:
       0 - 19% = 1
      20 - 39% = 0
      40 - 59% = 0
      60 - 79% = 0
      80 - 99% = 16129

    Index RDB$PRIMARY1 (0)
      Depth: 3, leaf buckets: 1463, nodes: 1000000
      Average data length: 1.00, total dup: 0, max dup: 0
      Fill distribution:
           0 - 19% = 0
          20 - 39% = 0
          40 - 59% = 0
          60 - 79% = 1
          80 - 99% = 1462

    Index TABLEIND1_IDX1 (1)
      Depth: 3, leaf buckets: 2873, nodes: 2000000
      Average data length: 0.00, total dup: 1999997, max dup: 999999
      Fill distribution:
           0 - 19% = 0
          20 - 39% = 1
          40 - 59% = 1056
          60 - 79% = 0
          80 - 99% = 1816

Чтобы понять значение показанных чисел и процентных значений, мы можем использовать HQbird Database Analyst, который предлагает визуальную интерпретацию статистики базы данных:

Нажав Reports/View recommendations, мы можем найти соответствующее объяснение для этого индекса:

Количество плохих индексов: 1. Под «плохими» мы подразумеваем индексы с множеством дублирующихся ключей (90% всех ключей) и большими группами одинаковых ключей (30% всех ключей). Большие группы одинаковых ключей замедляют сборку мусора - MaxEquals здесь представляет собой процент максимальных групп ключей с одинаковыми значениями. Поиск по такому индексу неэффективен. Вы можете удалить такие индексы (если они не находятся на ограничениях внешнего ключа).

Индекс (Отношение) Дубликаты MaxEquals TABLEIND1.IDX1 (TABLEIND1) : 100%, 50%

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

Если HQbird Database Analyst выделяет индексы как «Бесполезные» (т.е. они имеют только 1 значение), такие индексы рекомендуется немедленно удалить, если это возможно.

Если индекс выделен как «Плохой» (несколько значений), его также следует рассмотреть для удаления, но с осторожностью: возможно, плохой индекс используется в каком-то конкретном SQL-запросе, который требует определенной комбинации индексов (включая плохой) для быстрого выполнения.

Конечно, эти SQL-запросы должны быть переписаны для использования более оптимального плана выполнения без плохого индекса. Чтобы найти такие запросы, следует провести аудит SQL-планов: самый простой способ - запустить сеанс трассировки с помощью HQbird PerfMon и включить запись SQL-планов, а затем выполнить поиск SQL-планов для конкретных плохих индексов.