Индексы. Интеграция с другими ЯП

1. Оптимизация

1.1. Жизненный цикл запроса, план запроса.

1.1.1. Жизненный цикл запроса.

Мы написали запрос, что происходит дальше?

  1. Создается подключение к СУБД. В СУБД отправляется запрос в виде обычного текста.

  2. Парсер проверяет корректность синтаксиса запроса и создает дерево запроса.

  3. Система переписывания запросов преобразует запрос – получаем обновленное дерево запроса; используется система правил

  4. Планировщик / оптимизатор создает план запроса.

  5. Обработчик рекурсивно обходит план запроса и получает результирующий набор строк.

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

  • Тип команды (SELECT, UPDATE, DELETE, INSERT);
  • Список используемых отношений;
  • Целевое отношение, в которое будет записан результат;
  • Список полей (* преобразуется в полный список всех полей);
  • Список ограничений (которые указаны в WHERE);
  • и т.д.

1.1.2. План запроса. Как читать, на что обращать внимание. Операторы EXPLAIN и ANALYZE.

Планировщик (planner) – компонент PostgreSQL, пытающийся выработать наиболее эффективный способ выполнения запроса SQL.

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

Оператор EXPLAIN:

Индекс — это объект базы данных, который реализует связь между строками таблицы по определённым ключам, позволяя значительно ускорить поиск и выборку данных. Индексы хранятся отдельно от таблиц, но могут быть воссозданы на основе информации, содержащейся в самих таблицах, поэтому их относят к вспомогательным структурам. Кроме того, индексы применяются для обеспечения некоторых ограничений целостности, например уникальности данных в определённых столбцах. Дальше в пункте 1.2 рассмотрим индексы более подробно.

Функции:

  • Выводит план выполнения, генерируемый планировщиком PostgreSQL для заданного оператора.
  • Показывает, как будут сканироваться таблицы, затрагиваемые оператором — просто последовательно, по индексу и т.д.
  • Показывает, какой алгоритм соединения будет выбран для объединения считанных из таблиц строк.
  • Показывает ожидаемую стоимость (в условных единицах) выполнения запроса.
  • ОТСУТСТВУЕТ в стандарте SQL.

Синтаксис:

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
BUFFERS [ boolean ]
TIMING [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }

    INSERT INTO my_table ...;
    EXPLAIN SELECT * FROM my_table;
- - - -
QUERY PLAN
Seq Scan on my_table (cost=0.00..18334.00 rows=1000000 width=37)

Что это значит?

  • Данные читаются методом Sed Scan (см. следующий вопрос)
  • Данные читаются из таблицы my_table
  • cost — затраты (в некоторых условных единицах) на получение первой строки..всех строк
  • rows — приблизительное количество возвращаемых строк при выполнении операции Seq Scan (никакие строки не вычитываются, значение приблизительное)
  • width — средний размер одной строки в байтах

При повторном вызове покажет старую статистику, необходимо вызвать команду ANALYZE для ее обновления.

Оператор ANALYZE:

Функции:

  • Собирает статистическую информацию о содержимом таблиц в базе данных и сохраняет результаты в системном каталоге pg_statistic;
  • Без параметров анализирует все таблицы в текущей базе данных.
  • Если в параметрах передано имя таблицы, обрабатывает только заданную таблицу.
  • Если в параметрах передан список имен столбцов, то сбор статистики запустится только по этим столбцам.
  • ОТСУТСТВУЕТ в стандарте SQL.

Синтаксис:

ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]

ANALYZE my_table;
EXPLAIN SELECT * FROM my_table;

EXPLAIN (ANALYZE) SELECT * FROM my_table;
- - - -
QUERY PLAN
Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37)
(actual time=0.402..97.000 rows=1000010 loops=1)

Planning time: 0.042 ms
Execution time: 138.229 ms

Теперь запрос будет исполняться реально.

  • actual time — реальное время в миллисекундах, затраченное для получения первой строки и всех строк соответственно.
  • rows — реальное количество строк, полученных при Seq Scan.
  • loops — сколько раз пришлось выполнить операцию Seq Scan.
  • Planning time — время, потраченное планировщиком на построение плана запроса.
  • Execution time — общее время выполнения запроса.

1.2. Индексы. Определение, условия использования, способы сканирования.

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

Аналогии с другими языками программирования и структурами данных:

  • Хеш-таблицы (Hash Map, Dictionary) — индексы по типу хеш-индексов похожи на работу с хеш-таблицами, когда поиск элемента занимает постоянное время (O(1)) при равномерном распределении.
  • B-деревья, бинарные деревья поиска — индексы вида B-Tree напоминают деревья поиска в языках программирования, где эффективен поиск по диапазонам и сортированным наборам данных.

В PostgreSQL 9.6 встроены шесть разных видов индексов.

Свойства:

  • Все индексы — вторичные, они отделены от таблицы. Вся информация о них содержится в системном каталоге.
  • При добавлении/изменении данных, связанных с индексом, индекс каждый раз перестраивается (это замедляет выполнение запроса).
  • Внутри могут лежать разные математические структуры (B-дерево, черно-красное дерево…)
  • Индексы могут быть многоколоночными (поддержание условия на несколько полей).
  • Индексы связывают ключи и TID (tuple id - #page: #offset) — номер страницы и строки на ней.
  • Обновление полей таблицы, по которым не создавались индексы, не приводит к перестроению индексов (Heap-Only Tuples, HOT).

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

Условия использования:

  • Совпадают оператор и типы аргументов.
  • Индекс валиден.
  • Важен порядок полей внутри многоколоночного индекса, чтобы накладывать условия, ожидая, что оптимизатор выберет индекс.
  • План с его использованием — оптимален (минимальная стоимость).
  • Всю информацию Postgres берет из системного каталога.

Синтаксис:

CREATE [UNIQUE] INDEX [CONCURRENTLY] [name] ON table_name [USING METHOD]...
CREATE INDEX ON my_table(column_2);
ALTER INDEX [IF EXISTS] name RENAME TO new_name
DROP INDEX [CONCURRENTLY] [IF EXISTS] name [, ...] [CASCADE|RESTRICT]

ALTER INDEX [IF EXISTS] name RENAME TO new_name
ALTER INDEX [IF EXISTS] name SET TABLESPACE tablespace_name
ALTER INDEX [IF EXISTS] name SET (storage_parameter = value [, ... ])
ALTER INDEX [IF EXISTS] name RESET (storage_parameter [, ... ])
DROP INDEX [CONCURRENTLY] [IF EXISTS] name [, ...] [CASCADE|RESTRICT]

Задачи, где индексы нужны

  • Поиск по столбцу с уникальными значениями (например, ID)

Задача: У вас есть таблица с данными о пользователях, где ID является уникальным идентификатором.

Запрос:

SELECT * FROM users WHERE id = 123;

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

  • Поиск с условиями, ограничивающими выборку (селективность)

Задача: Вы хотите найти все заказы пользователя с определённым статусом или датой.

Запрос:

SELECT * FROM orders WHERE status = 'shipped';

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

  • Поиск по многим столбцам (составные индексы)

Задача: Вы хотите выполнить поиск с несколькими условиями.

Запрос:

SELECT * FROM products WHERE category = 'electronics' AND price < 1000;

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

Когда индексы не нужны и их минусы

  • Для небольших таблиц

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

Почему не использовать индекс:: В таких случаях последовательное сканирование таблицы будет быстрее, так как накладные расходы на обновление и поддержание индекса (при изменении данных) могут превышать выгоду от использования индекса. Для маленьких таблиц индексы обычно не нужны.

  • При изменении большого количества данных

Задача: Если в таблице часто обновляются или вставляются строки, особенно по полям, по которым создаются индексы, это может значительно замедлить операции.

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

Пример: Таблица с заказами, где ежедневно добавляется или обновляется большое количество строк. Если на поле status создаётся индекс, каждое обновление статуса будет требовать перерасчёта индекса.

  • Индексы на низкоселективные столбцы

Задача: Поиск по столбцу, где значения часто повторяются.

Запрос:

SELECT * FROM users WHERE country = 'Russia';

Почему не использовать индекс:

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

Пример: Если 90% пользователей находятся в одной стране, то индекс на поле country не даст прироста в производительности. В таких случаях проще выполнить последовательное сканирование таблицы.

  • Когда данные часто удаляются или перемещаются

Задача: Если в таблице часто удаляются или перемещаются строки.

Почему не использовать индекс:

Каждый раз при удалении или перемещении строк нужно обновлять индексы. Частое обновление индексов может существенно замедлить работу базы данных.

Пример:

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

Индексы полезны, когда:

  • Есть уникальные или селективные поля.
  • Требуется быстрое выполнение запросов с фильтрацией или сортировкой.
  • Таблица активно используется для чтения.

Индексы неэффективны или не нужны, когда:

  • Таблица маленькая, и не даёт большого выигрыша от индекса.
  • Часто происходят изменения данных в индексируемых столбцах.
  • Столбец имеет низкую селективность.
  • Часто происходят удаления или перемещения строк.

1.3. Способы считывания данных

1.3.1. Seq Scan

Идея: Последовательное, блок за блоком, чтение данных таблицы.

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

Недостатки: Обычно выполняется гораздо медленнее индексного сканирования, так как считывает все данные таблицы.

1.3.2. Index Scan

Идея: Используется индекс для условий WHERE (селективность условия), читает таблицу при отборе строк.

Преимущества: При селективности условия время N → lnN. (В результате выполнения запроса выбирается значительно меньше строк, чем их кол-во в странице)

Недостатки: Если будем собирать индекс по всем полям, он будет весить зачастую значительно больше, чем данные в таблице. При увеличении выборки возрастают шансы, что придется возвращаться к одной и той же табличной странице несколько раз. (В таком случае оптимизатор переключается на Bitmap Scan)

1.3.3. Bitmap Index Scan

Идея: Сначала Index Scan, затем контроль выборки по таблице. В большей части работа со строками (индекс по строке, битовая карта страниц, последующий отбор)

Преимущества: Эффективно для большого количества строк.

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

  • В случае почти упорядоченных данных построение битовой карты — лишний шаг, обычное индексное сканирование будет таким же.
  • Создается битовая карта, где предполагаем, что в соответствии с собранной статистикой наши строки удовлетворяют нашему условию; в ней есть странички;
  • Если условия наложены на несколько полей таблицы, и эти поля проиндексированы, сканирование битовой карты позволяет (если оптимизатор сочтет это выгодным) использовать несколько индексов одновременно. Для каждого индекса строятся битовые карты версий строк, которые затем побитово логически умножаются (если выражения соединены условием AND), либо логически складываются (если выражения соединены условием OR).

1.3.4. Index Only Scan

Идея: Практически не обращаемся к таблице, все необходимые значения в индексе.

Преимущества: Очень быстрая операция.

Недостатки: Может применяться только тогда, когда индекс включает все необходимые для выборки поля, и дополнительный доступ к таблице не требуется.

Если индекс уже содержит все необходимые для запроса данные, то индекс называется покрывающим.

2. Интеграция с другими ЯП

2.1. Взаимодействие из-под Python

Python DB API 2.0 - стандарт интерфейсов для пакетов, работающих с БД. “Набор правил”, которым подчиняются отдельные модули, реализующие работу с конкретными базами данных. Ноутбук с примерами.

2.2. Курсоры

Курсор – специальный объект, выполняющий запрос и получающий его результат.

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

В PL/pgSQL:

name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;

DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

Практические задачи (Индексы)

Задача 1: Использование индексов для ускорения поиска по уникальному полю

  • Создайте таблицу users(user_id SERIAL PRIMARY KEY, name TEXT, email TEXT, country TEXT, created_at TIMESTAMP).
  • Заполните таблицу users случайными данными (например, 1 миллион строк).
  • Выполните запрос для поиска пользователя по user_id: 
    EXPLAIN ANALYZE SELECT * FROM users WHERE user_id = 123456;
    Обратите внимание на используемый метод чтения данных и затраты.
  • Если индекс не используется, создайте индекс по полю user_id: 
    CREATE INDEX idx_user_id ON users(user_id);
  • Повторите запрос и убедитесь, что теперь используется индекс для поиска.

Задача 2: Селективность и использование индексов

  • Создайте таблицу orders(order_id SERIAL PRIMARY KEY, user_id INT, status TEXT, total_amount NUMERIC, order_date TIMESTAMP).
  • Заполните таблицу случайными данными.
  • Создайте индекс по столбцу status: 
    CREATE INDEX idx_status ON orders(status);
  • Выполните запрос: 
    EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';
  • Посмотрите, используется ли индекс.
  • Измените запрос, чтобы найти заказы с меньшей селективностью, например: 
    EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';
  • Посмотрите, какой метод сканирования был использован (например, Seq Scan или Index Scan).

Задача 3: Составные индексы

  • Создайте таблицу products(product_id SERIAL PRIMARY KEY, name TEXT, category TEXT, price NUMERIC, stock_quantity INT).
  • Заполните таблицу случайными данными.
  • Выполните запрос без индекса: 
    EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'electronics' AND price < 1000;
  • Создайте составной индекс на столбцы category и price: 
    CREATE INDEX idx_category_price ON products(category, price);
  • Повторите запрос с EXPLAIN ANALYZE и посмотрите, изменился ли план запроса.

Задача 4: Индексы и сортировка

  • Создайте таблицу employees(employee_id SERIAL PRIMARY KEY, name TEXT, department TEXT, salary NUMERIC, hire_date TIMESTAMP).
  • Заполните таблицу случайными данными.
  • Выполните запрос для сортировки по зарплате: 
    EXPLAIN ANALYZE SELECT * FROM employees ORDER BY salary DESC;
  • Создайте индекс на поле salary: 
    CREATE INDEX idx_salary ON employees(salary);
  • Повторите запрос с EXPLAIN ANALYZE и проверьте, используется ли индекс.

Задача 5: Индексы и частичные индексы

  • Создайте таблицу orders(order_id SERIAL PRIMARY KEY, status TEXT, total_amount NUMERIC, order_date TIMESTAMP).
  • Заполните таблицу случайными данными.
  • Создайте частичный индекс на поле status для значения shipped: 
    CREATE INDEX idx_shipped_orders ON orders(status) WHERE status = 'shipped';
  • Выполните запрос для поиска заказов с таким статусом: 
    EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';
  • Проверьте, используется ли частичный индекс.