索引使 D1 能够通过对索引列上的常见(高频)查询减少数据库需要扫描的数据量(行数),从而提升查询性能。
索引在以下情况下很有用:
- 当您希望提升在谓词中经常使用的列上的读取性能时——例如
WHERE email_address = ?或WHERE user_id = 'a793b483-df87-43a8-a057-e5286d3537c5'——电子邮件地址、用户名、用户 ID 和日期是典型 Web 应用或服务中适合建立索引的列。 - 用于在列或列组上强制执行唯一性约束——例如,通过
CREATE UNIQUE INDEX对电子邮件地址或用户 ID 建立唯一索引。 - 当您需要同时查询多个列时——
(customer_id, transaction_date)。 - 用于连接表的列——例如
JOIN中的ON orders.customer_id = customers.id。合适的索引可以避免重复扫描或创建临时索引。使用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。
通过查询 sqlite_schema 系统表来列出数据库上的索引及其 SQL 定义:
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(跳到匹配行)。
| 模式 | 为什么会读取或写入过多行 | 修复 |
|---|---|---|
在未建立索引的列上使用 WHERE column = ? |
每次调用 D1 都会扫描整张表。 | 在过滤列上创建索引。 |
WHERE a = ? AND b = ? 且两列都没有索引 |
D1 会扫描整张表。仅在其中一列上建立索引可以避免全表扫描,但 D1 仍会读取匹配该列的每一行,然后再按另一列过滤。 | 在 (a, b) 上创建多列索引,使 D1 可以同时按两列缩小范围。 |
JOIN other ON other.x = main.y 且 other.x 未建立索引 |
取决于查询计划,D1 可能重复扫描被连接的表,或创建临时索引。 | 为连接 ON 条件中使用的列(此处为 other.x)建立索引,然后用 EXPLAIN QUERY PLAN 验证计划。 |
相关子查询,例如 WHERE id = (SELECT ... WHERE inner.key = outer.key ...) |
取决于查询计划,D1 可能为每个候选行评估内部查询。内部查询中的扫描会倍增读取的行数。 | 为子查询过滤和连接所用的列建立索引,然后用 EXPLAIN QUERY PLAN 验证计划。 |
ORDER BY RANDOM() LIMIT 1 |
D1 必须读取并排序整个结果集才能挑选随机行——索引无法提供帮助。 | 避免在大型表上使用 ORDER BY RANDOM()。选择适合主键类型、分布和所需随机性的采样策略。 |
WHERE column LIKE '%term%'(前导通配符),包括在 COUNT(*) 内 |
前导 % 会阻止常规 B-tree 索引优化 LIKE,因此查询通常需要全表扫描。 |
尽可能去掉前导通配符。类似 LIKE 'term%' 的前缀搜索在某些情况下可以使用索引。对于任意子字符串搜索,可考虑 带 trigram tokenizer 的 FTS5 ↗,它可以优化至少包含三个连续非通配符 Unicode 字符的模式。FTS5 索引会增加存储和写入成本,因此请针对你的工作负载进行基准测试。两种方法都请用 EXPLAIN QUERY PLAN 验证。 |
在每个请求上重新运行 CREATE INDEX(或其他 schema 更改) |
构建索引会为其索引的每一行写入一行,且写入的计费费率高于读取。按请求这样做会重复该成本。 | 使用 D1 migrations 一次性运行 schema 更改,而不是放在应用程序的热路径上。 |
对于多列索引(指定多个列的索引),仅当查询指定 所有 列,或指定列的子集且索引中左侧的所有列也包含在查询中时,查询才会使用该索引。
给定索引 CREATE INDEX idx_customer_id_transaction_date ON transactions(customer_id, transaction_date),下表显示何时会使用该索引:
| 查询 | 是否使用索引? |
|---|---|
SELECT * FROM transactions WHERE customer_id = '1234' AND transaction_date = '2023-03-25' |
是:指定了索引中的两列。 |
SELECT * FROM transactions WHERE transaction_date = '2023-03-28' |
否:仅指定了 transaction_date,未包含索引中最左侧的其他列。 |
SELECT * FROM transactions WHERE customer_id = '56789' |
是:指定了 customer_id,即索引中最左侧的列。 |
说明:
- 如果您创建包含三列的索引——
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 migrations)一次性应用这些 schema 更改。
- 索引会增加数据库所需的总体存储空间:索引实际上也是一张表。