帐户中大部分读取和写入行数通常来自少数数据库和查询。这些行数会计入费用,而这些查询也会拖慢数据库。查询读取的行数远多于返回的行数时,通常会很慢。频繁运行的读取查询也会增加数据库负载。Worker 在同一个请求中连续发送多个小查询也会拖慢请求,因为每个查询都需要单独往返 D1。
若要查找这些数据库和查询,请使用 wrangler d1 insights 或 Cloudflare CLI 的 cf sql query 命令。wrangler d1 insights 会从 GraphQL Analytics API 返回排名靠前的查询。所有选项请参阅查询 insights。cf sql query 会将 SQL 发送到 SQL API。你可以修改示例中的 SQL 以获取不同结果。
如果使用 API 令牌,该令牌必须具有 Account Analytics Read 权限。两个命令都返回 JSON,因此 AI 代理可以运行命令并读取结果。
SQL 示例使用 events.d1Queries 数据集。该数据集为每条 D1 查询存储一行,列包括 databaseId、query、queryDurationMs、rowsRead、rowsReturned、rowsWritten 和 error。查询成功时,error 的值为空字符串。
查找过去 7 天内帐户中读取行数最多的数据库:
npx wrangler d1 insights --sort-by=reads --time-period=7d --limit=20 --jsonwrangler d1 insights 返回的是查询,而不是数据库。每个结果都包含执行该查询的数据库的 databaseId。将具有相同 databaseId 的结果的 totalRowsRead 相加,即可找出成本最高的数据库。
npx cf sql query --scope-account <account_id> "
SELECT
databaseId,
COUNT(*) AS numberOfTimesRun,
SUM(rowsRead) AS totalRowsRead,
SUM(rowsWritten) AS totalRowsWritten,
AVG(queryDurationMs) AS avgDurationMs
FROM events.d1Queries
WHERE timestamp >= NOW() - INTERVAL '7' DAY
AND error = ''
GROUP BY databaseId
ORDER BY totalRowsRead DESC
LIMIT 10
"若要查找写入行数最多的数据库,请将 ORDER BY 改为 totalRowsWritten。若要查找最慢的数据库,请将 ORDER BY 改为 avgDurationMs。
若要获取数据库名称,请运行 npx wrangler d1 info <database_id>。若要了解读取和写入行数对账单的影响,请参阅定价。
-
查找过去 7 天内帐户中读取行数最多的查询:
npx wrangler d1 insights --sort-by=reads --time-period=7d --json以下示例输出展示了五条结果中的前两条:
[ { "query": "SELECT \"Order\".Id, \"Order\".CustomerId, \"Order\".OrderDate,\n SUM(OrderDetail.UnitPrice * OrderDetail.Quantity) AS TotalAmount\n FROM \"Order\"\n JOIN OrderDetail ON OrderDetail.OrderId = \"Order\".Id\n GROUP BY \"Order\".Id\n ORDER BY \"Order\".Id DESC\n LIMIT ?", "avgRowsRead": 1244521, "totalRowsRead": 75268630080, "avgRowsWritten": 0, "totalRowsWritten": 0, "avgDurationMs": 283.471346125992, "totalDurationMs": 17144347.013699997, "numberOfTimesRun": 60480, "queryEfficiency": 1.6070439952399358e-05, "databaseId": "7c9e2b14-3f6a-4d8e-9b21-5a0c8f3e6d47" }, { "query": "SELECT \"Order\".Id, \"Order\".CustomerId, \"Order\".OrderDate,\n SUM(OrderDetail.UnitPrice * OrderDetail.Quantity) AS TotalAmount\n FROM \"Order\"\n JOIN OrderDetail ON OrderDetail.OrderId = \"Order\".Id\n GROUP BY \"Order\".Id\n ORDER BY \"Order\".Id DESC\n LIMIT ?", "avgRowsRead": 1244521, "totalRowsRead": 74719796319, "avgRowsWritten": 0, "totalRowsWritten": 0, "avgDurationMs": 257.3288101250854, "totalDurationMs": 15449764.431100003, "numberOfTimesRun": 60039, "queryEfficiency": 1.6070439952399358e-05, "databaseId": "2e4f6a81-9c3d-4b7e-a512-8d0f3c6b9e15" } ]npx cf sql query --scope-account <account_id> " SELECT databaseId, query, COUNT(*) AS numberOfTimesRun, SUM(rowsRead) AS totalRowsRead, AVG(rowsRead) AS avgRowsRead, if(SUM(rowsRead) = 0, 0.0, SUM(rowsReturned) / SUM(rowsRead)) AS queryEfficiency FROM events.d1Queries WHERE timestamp >= NOW() - INTERVAL '7' DAY AND error = '' GROUP BY databaseId, query ORDER BY totalRowsRead DESC LIMIT 5 "以下示例输出展示了五条结果中的前两条:
{ "data": [ { "avgRowsRead": 1244521, "databaseId": "7c9e2b14-3f6a-4d8e-9b21-5a0c8f3e6d47", "numberOfTimesRun": 60558, "query": "SELECT \"Order\".Id, \"Order\".CustomerId, \"Order\".OrderDate,\n SUM(OrderDetail.UnitPrice * OrderDetail.Quantity) AS TotalAmount\n FROM \"Order\"\n JOIN OrderDetail ON OrderDetail.OrderId = \"Order\".Id\n GROUP BY \"Order\".Id\n ORDER BY \"Order\".Id DESC\n LIMIT ?", "queryEfficiency": 1.6070439952399358e-05, "totalRowsRead": 75365702718 }, { "avgRowsRead": 1244521, "databaseId": "2e4f6a81-9c3d-4b7e-a512-8d0f3c6b9e15", "numberOfTimesRun": 60111, "query": "SELECT \"Order\".Id, \"Order\".CustomerId, \"Order\".OrderDate,\n SUM(OrderDetail.UnitPrice * OrderDetail.Quantity) AS TotalAmount\n FROM \"Order\"\n JOIN OrderDetail ON OrderDetail.OrderId = \"Order\".Id\n GROUP BY \"Order\".Id\n ORDER BY \"Order\".Id DESC\n LIMIT ?", "queryEfficiency": 1.6070439952399358e-05, "totalRowsRead": 74809401831 } ], "rows": 5, "statistics": { "elapsed_ms": 156, "rows_read": 11153623, "bytes_read": 3732357193 } }每个结果都包含执行该查询的数据库的
databaseId。在示例输出中,同一个JOIN查询会在多个数据库上运行。 -
获取你要修复的数据库的热门查询。使用第 1 步中的
databaseId,或查找成本最高的数据库中的数据库 ID:npx wrangler d1 insights <database_id> --sort-by=reads --time-period=7d --jsonnpx cf sql query --scope-account <account_id> " SELECT query, COUNT(*) AS numberOfTimesRun, SUM(rowsRead) AS totalRowsRead, AVG(rowsRead) AS avgRowsRead, AVG(queryDurationMs) AS avgDurationMs, quantileWeighted(0.95, queryDurationMs, sampleInterval) AS p95DurationMs, if(SUM(rowsRead) = 0, 0.0, SUM(rowsReturned) / SUM(rowsRead)) AS queryEfficiency FROM events.d1Queries WHERE timestamp >= NOW() - INTERVAL '7' DAY AND databaseId = '<database_id>' AND error = '' GROUP BY query ORDER BY totalRowsRead DESC LIMIT 5 " -
查找
avgRowsRead较高且queryEfficiency远低于1的查询。这些查询读取了许多行,却只返回少量行。通常是WHERE、JOIN或ORDER BY子句中的某列缺少索引。在第 1 步的示例输出中,
JOIN查询每次运行会读取 1,244,521 行,其queryEfficiency约为0.000016。JOIN子句中的OrderDetail.OrderId列没有索引。 -
找到能够修复查询的索引。有关针对各种查询模式的正确修复方法,请参阅索引与账单。对于示例查询,请在
OrderDetail表的OrderId列上添加索引。 -
为该索引创建迁移。数据库必须位于你的 Wrangler 配置文件中:
npx wrangler d1 migrations create <database_name> add_orderdetail_orderid_index✅ Successfully created Migration '0001_add_orderdetail_orderid_index.sql'!文件名开头的数字取决于你已有的迁移。
-
将
CREATE INDEX语句添加到新的迁移文件:migrations/0001_add_orderdetail_orderid_index.sqlsql -- Migration number: 0001 2026-10-29T15:42:06.092Z CREATE INDEX IF NOT EXISTS idx_orderdetail_orderid ON OrderDetail (OrderId); -
将迁移应用到远程数据库:
npx wrangler d1 migrations apply <database_name> --remote┌────────────────────────────────────────┬────────┐ │ name │ status │ ├────────────────────────────────────────┼────────┤ │ 0001_add_orderdetail_orderid_index.sql │ ✅ │ └────────────────────────────────────────┴────────┘ -
确认 D1 使用了新索引。在查询前添加
EXPLAIN QUERY PLAN并运行:npx wrangler d1 execute <database_name> --remote --command="EXPLAIN QUERY PLAN <query>"你添加索引的表对应的行应显示
SEARCH ... USING INDEX,而不是SCAN。对于示例查询,SCAN OrderDetail会变为SEARCH OrderDetail USING INDEX idx_orderdetail_orderid (OrderId=?)。更多信息请参阅测试索引。 -
确认读取行数有所减少。再次运行第 2 步中的命令,并使用更短的时间段,例如
--time-period=1h或INTERVAL '1' HOUR。结果包含该时间段内的所有查询。添加索引之前运行的查询会增加较长时间段的汇总值。
频繁运行的读取查询通常每次都返回相同数据。将结果保存在缓存中后,Worker 就不必每次都向 D1 发送查询。这会降低数据库负载和读取行数。
-
查找过去一天内帐户中运行次数最多的查询:
npx wrangler d1 insights --sort-by=count --time-period=1d --json以下示例输出展示了五条结果中的前两条:
[ { "query": "SELECT * FROM Customer WHERE Id = ?", "avgRowsRead": 1, "totalRowsRead": 41876, "avgRowsWritten": 0, "totalRowsWritten": 0, "avgDurationMs": 0.312, "totalDurationMs": 13065.312, "numberOfTimesRun": 41876, "queryEfficiency": 1, "databaseId": "b5d18e3a-6f29-4c04-8e7b-1a9c2d5f7e60" }, { "query": "SELECT * FROM Category", "avgRowsRead": 8, "totalRowsRead": 191232, "avgRowsWritten": 0, "totalRowsWritten": 0, "avgDurationMs": 0.274, "totalDurationMs": 6549.696, "numberOfTimesRun": 23904, "queryEfficiency": 1, "databaseId": "2e4f6a81-9c3d-4b7e-a512-8d0f3c6b9e15" } ]npx cf sql query --scope-account <account_id> " SELECT databaseId, query, COUNT(*) AS numberOfTimesRun, SUM(rowsRead) AS totalRowsRead, AVG(queryDurationMs) AS avgDurationMs FROM events.d1Queries WHERE timestamp >= NOW() - INTERVAL '1' DAY AND rowsWritten = 0 AND error = '' GROUP BY databaseId, query ORDER BY numberOfTimesRun DESC LIMIT 5 "以下示例输出展示了五条结果中的前两条:
{ "data": [ { "avgDurationMs": 0.3118, "databaseId": "b5d18e3a-6f29-4c04-8e7b-1a9c2d5f7e60", "numberOfTimesRun": 41903, "query": "SELECT * FROM Customer WHERE Id = ?", "totalRowsRead": 41903 }, { "avgDurationMs": 0.2741, "databaseId": "2e4f6a81-9c3d-4b7e-a512-8d0f3c6b9e15", "numberOfTimesRun": 23911, "query": "SELECT * FROM Category", "totalRowsRead": 191288 } ], "rows": 5, "statistics": { "elapsed_ms": 98, "rows_read": 1593847, "bytes_read": 512730114 } }SQL 查询只返回不写入行的查询。Wrangler 输出包含所有查询。若要在 Wrangler 输出中查找读取查询,请寻找
totalRowsWritten为0的结果。 -
查找
numberOfTimesRun较高且每次都返回相同数据的查询。如果数据不常变化,或应用可以展示短时间内过期的数据,请缓存结果。在示例输出中,
SELECT * FROM Category每天运行约 24,000 次,每次都返回相同的 8 行。类别几乎不会变化,因此此查询适合缓存。它的queryEfficiency已经是1,所以添加索引不会减少读取行数。 -
将结果保存在 Workers KV 或 Cache API中。根据数据变化频率设置过期时间。有关如何在 Worker 中使用缓存,请参阅使用 Workers 自定义缓存行为。
如果查询必须始终返回最新数据,请勿缓存。
Worker 每次对语句调用 run()、all()、first() 或 raw(),都会向 D1 发送单独请求并等待响应。如果 Worker 在同一个请求中连续发送许多查询(例如对列表中的每项都执行一次 INSERT),往返开销就会不断累积。若要在一次往返中向 D1 发送多条语句,请使用 batch()。
-
查找过去一天内帐户中写入行数最多的写入查询:
npx wrangler d1 insights --sort-by=writes --time-period=1d --json以下示例输出展示了五条结果中的前两条:
[ { "query": "INSERT INTO OrderDetail (Id, OrderId, ProductId, UnitPrice, Quantity, Discount) VALUES (?, ?, ?, ?, ?, ?)", "avgRowsRead": 0, "totalRowsRead": 0, "avgRowsWritten": 3, "totalRowsWritten": 64818, "avgDurationMs": 0.41, "totalDurationMs": 8858.46, "numberOfTimesRun": 21606, "queryEfficiency": 0, "databaseId": "7c9e2b14-3f6a-4d8e-9b21-5a0c8f3e6d47" }, { "query": "INSERT INTO \"Order\" (Id, CustomerId, EmployeeId, OrderDate, ShipVia, Freight) VALUES (?, ?, ?, ?, ?, ?)", "avgRowsRead": 0, "totalRowsRead": 0, "avgRowsWritten": 1, "totalRowsWritten": 6987, "avgDurationMs": 0.38, "totalDurationMs": 2655.06, "numberOfTimesRun": 6987, "queryEfficiency": 0, "databaseId": "7c9e2b14-3f6a-4d8e-9b21-5a0c8f3e6d47" } ]npx cf sql query --scope-account <account_id> " SELECT databaseId, query, COUNT(*) AS numberOfTimesRun, SUM(rowsWritten) AS totalRowsWritten, AVG(rowsWritten) AS avgRowsWritten FROM events.d1Queries WHERE timestamp >= NOW() - INTERVAL '1' DAY AND rowsWritten > 0 AND error = '' GROUP BY databaseId, query ORDER BY totalRowsWritten DESC LIMIT 5 " -
查找
numberOfTimesRun较高且avgRowsWritten较低的写入查询。这些查询每次运行时只会插入或更新表中的一行。avgRowsWritten包含写入表索引的行数,因此向带有两个索引的表插入一行时,查询的avgRowsWritten为3。在示例输出中,
OrderDetail的INSERT查询每天运行 21,606 次,Order的INSERT查询每天运行 6,987 次。每个订单大约有三个订单详情,而 Worker 会通过单独查询插入每条订单详情。OrderDetail查询的avgRowsWritten为3,因为该表有两个索引:主键索引,以及查找需要索引的查询中创建的idx_orderdetail_orderid索引。 -
在 Worker 代码中,找到循环运行这些查询的位置,或在同一请求中连续运行多个查询的位置。将这些语句放入一次
batch()调用:// Before: one round trip to D1 for each order detail for (const detail of details) { await env.DB.prepare( "INSERT INTO OrderDetail (Id, OrderId, ProductId, UnitPrice, Quantity, Discount) VALUES (?, ?, ?, ?, ?, ?)", ) .bind(detail.id, orderId, detail.productId, detail.unitPrice, detail.quantity, detail.discount) .run(); } // After: one round trip to D1 for all the order details const insertDetail = env.DB.prepare( "INSERT INTO OrderDetail (Id, OrderId, ProductId, UnitPrice, Quantity, Discount) VALUES (?, ?, ?, ?, ?, ?)", ); await env.DB.batch( details.map((detail) => insertDetail.bind(detail.id, orderId, detail.productId, detail.unitPrice, detail.quantity, detail.discount), ), );批次中的语句会在一个 SQL 事务 ↗中运行。如果某条语句失败,D1 会回滚批次中的所有语句。你也可以批处理同一请求中彼此结果无依赖关系的读取查询。
batch() 会减少往返次数,不会减少读取或写入的行数。批次中的每条语句在分析数据中仍计为单独查询。