← Cloudflare D1 / d1 / best-practices

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

Индексы позволяют D1 повышать производительность частых (популярных) запросов по проиндексированным столбцам за счёт сокращения объёма данных (количества строк), которые базе данных нужно просканировать при выполнении запроса.

Индексы полезны в следующих случаях:

• Если нужно повысить производительность чтения по столбцам, которые регулярно используются в предикатах, например WHERE email_address = ? или WHERE user_id = 'a793b483-df87-43a8-a057-e5286d3537c5' - адреса электронной почты, имена пользователей, идентификаторы пользователей и даты хорошо подходят для индексирования в типичных веб-приложениях и сервисах.
• Для применения ограничений уникальности к столбцу или столбцам, например к адресу электронной почты или идентификатору пользователя, с помощью CREATE UNIQUE INDEX.
• В случаях, когда вы запрашиваете данные сразу по нескольким столбцам, например (customer_id, transaction_date).
• Для столбцов, используемых для соединения таблиц, например ON orders.customer_id = customers.id в JOIN. Подходящий индекс позволяет избежать повторных сканирований или создания временного индекса. Используйте EXPLAIN QUERY PLAN чтобы проверить план запроса.

Индексы обновляются автоматически при вставке, обновлении или удалении данных в таблице и столбцах, на которые они ссылаются. Вручную обновлять индекс после записи в связанную таблицу не требуется.

Чтобы создать индекс для таблицы D1, используйте CREATE INDEX команду SQL и укажите таблицу и столбец (или столбцы), по которым нужно создать индекс.

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

CREATE TABLE IF NOT EXISTS orders (
    order_id INTEGER PRIMARY KEY,
    customer_id STRING NOT NULL, -- for example, a unique ID aba0e360-1e04-41b3-91a0-1f2263e1e0fb
    order_date STRING NOT NULL,
    status INTEGER NOT NULL,
    last_updated_date STRING NOT NULL
)

Чтобы создать индекс для customer_id столбец выполните следующую инструкцию для своей базы данных:

CREATE INDEX IF NOT EXISTS idx_orders_customer_id ON orders(customer_id)

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

-- Uses the index: the indexed column is referenced by the query.
SELECT * FROM orders WHERE customer_id = ?

-- Does not use the index: customer_id is not in the query.
SELECT * FROM orders WHERE order_date = '2023-05-01'

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

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

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

Подробнее см. в PRAGMA optimize.

Чтобы получить список индексов базы данных и их SQL-определения, выполните запрос к sqlite_schema системная таблица:

SELECT name, type, sql FROM sqlite_schema WHERE type IN ('index');

Это вернёт вывод, похожий на приведённый ниже:

┌──────────────────────────────────┬───────┬────────────────────────────────────────┐
│ name                             │ type  │ sql                                    │
├──────────────────────────────────┼───────┼────────────────────────────────────────┤
│ idx_users_id                     │ index │ CREATE INDEX idx_users_id ON users(id) │
└──────────────────────────────────┴───────┴────────────────────────────────────────┘

Обратите внимание, что вы не можете изменить эту таблицу или существующий индекс. Чтобы изменить индекс, сначала удалите её и создать новый индекс с обновлённым определением.

Чтобы убедиться, что для запроса использовался индекс, добавьте перед ним EXPLAIN QUERY PLAN ↗. Это выведет план запроса для следующего оператора, включая то, какие индексы использовались (если использовались).

Например, если предположить, что users таблица имеет email_address TEXT столбец и создали индекс CREATE UNIQUE INDEX idx_email_address ON users(email_address), любой запрос с предикатом по email_address должен использовать ваш индекс.

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email_address = '[email protected]';
QUERY PLAN
`--SEARCH users USING INDEX idx_email_address (email_address=?)

Просмотрите USING INDEX <INDEX_NAME> вывод планировщика запросов, подтверждающий, что индекс был использован.

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

Индекс может уменьшить количество строк, считываемых запросом. Используйте meta объект для оценки использования. См. "Можно ли использовать индекс, чтобы уменьшить количество строк, прочитанных запросом?" и "Как оценить свой (итоговый) счёт?".

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

Неожиданно большие счета за D1 чаще всего вызваны небольшим числом часто выполняемых запросов, каждый из которых читает (или записывает) намного больше строк, чем возвращает. В следующей таблице перечислены типичные паттерны, затрагивающие слишком много строк, и способы их исправления. Используйте EXPLAIN QUERY PLAN чтобы проверить, выполняет ли запрос полное SCAN (считывает каждую строку) или SEARCH ... USING INDEX (переходит сразу к подходящим строкам).

Для составного индекса (индекса, охватывающего несколько столбцов) запросы будут использовать этот индекс только если в них указан либо all столбцов или подмножество столбцов при условии, что все столбцы "слева" от них также включены в запрос.

При индексе CREATE INDEX idx_customer_id_transaction_date ON transactions(customer_id, transaction_date), в следующей таблице показано, когда индекс используется, а когда нет:

Примечания:

• Если вместо этого вы создали индекс по трём столбцам: customer_id, transaction_date, а также shipping_status: запрос, который использует как customer_id и transaction_date будет использовать индекс, так как вы включили все столбцы "слева".
• С тем же индексом запрос, использующий только transaction_date и shipping_status бы не использовать индекс, так как вы не использовали customer_id (крайний левый столбец) в запросе.

Частичные индексы охватывают лишь часть строк таблицы. Такие индексы определяются с помощью WHERE предложение при создании индекса. Частичный индекс может быть полезен для исключения определённых строк, например тех, где значения NULL либо когда в разных запросах встречаются строки с определённым значением.

• Конкретным примером частичного индекса может служить индекс в таблице со столбцом order_status INTEGER столбец, где 6 может представлять "order complete" в коде вашего приложения.
• Это позволит выполнять запросы по заказам со статусом «не выполнен», «в процессе» или «отправлен», что, вероятно, будет одним из самых частых типов запросов, ведь пользователи часто проверяют статус своего заказа.
• Частичные индексы также не позволяют индексу разрастаться без ограничений со временем. Индексу не нужно хранить строку для каждого завершённого заказа, а завершённые заказы обычно запрашиваются значительно реже, чем заказы в процессе выполнения.

Частичный индекс, исключающий из индекса завершённые заказы, будет выглядеть примерно так:

CREATE INDEX idx_order_status_not_complete ON orders(order_status) WHERE order_status != 6

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

Используйте DROP INDEX чтобы удалить индекс. Удалённые индексы восстановить нельзя.

Учитывайте следующие моменты при создании индексов:

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