許多小查詢在 SQLite 中很有效率

1. 執行摘要

對於 MySQL、PostgreSQL 或 SQL Server 等用戶端/伺服器資料庫引擎而言，每個網頁 200 個 SQL 陳述過多。
但對於 SQLite，每個網頁 200 個或更多 SQL 陳述不成問題。
SQLite 也可以像用戶端/伺服器資料庫一樣有效率地執行大型且複雜的查詢。但 SQLite 也可以有效率地執行許多較小的查詢。應用程式開發人員可以使用最適合手邊任務的技術。

2. 感知到的問題

SQLite 的適當用途頁面指出，SQLite 網站上的動態頁面通常各執行約 200 個 SQL 陳述。這引起了讀者的批評。範例

「200 個 SQL 陳述對於單一頁面來說是一個高得可笑的數字」
「對於大多數網站來說，200 個查詢實在太多了」
「[這是] 糟糕的設計」

對於傳統的用戶端/伺服器資料庫引擎（例如 MySQL、PostgreSQL 或 SQL Server），這樣的批評是有道理的。在用戶端/伺服器資料庫中，每個 SQL 陳述都需要從應用程式傳送訊息到資料庫伺服器，再傳送回應用程式。連續執行超過 200 個訊息往返可能會嚴重拖累效能。這有時稱為「N+1 查詢問題」或「N+1 選取問題」，而且是一種反模式。

3. N+1 查詢不是 SQLite 的問題

然而，SQLite 並非客戶端/伺服器。SQLite 資料庫在與應用程式相同的處理程序位址空間中執行。查詢不涉及訊息往返，只涉及函式呼叫。單一 SQL 查詢的延遲在 SQLite 中少得多。因此，使用大量查詢並非 SQLite 的問題。

4. 每個網頁需要超過 200 個 SQL 陳述式

SQLite 網站上的動態網頁大多是由 Fossil 版本控制系統產生的。典型的動態網頁會是時間軸，例如 https://www.sqlite.org/src/timeline。以下是時間軸使用的所有 SQL 的記錄。

記錄中的第一組查詢從 Fossil 資料庫的「config」和「global_config」表格中擷取顯示選項。然後有一個單一的複雜查詢，用來擷取時間軸上要顯示的所有元素清單。這個「時間軸」查詢示範了 SQLite 可以輕鬆處理涉及多個表格、子查詢和複雜 WHERE 子句約束的複雜關聯式資料庫查詢，而且它可以有效利用索引，以最小的磁碟 I/O 解決查詢。

在單一大型「時間軸」查詢之後，還有每個時間軸元素的額外查詢。Fossil 使用「N+1 查詢」模式，而不是嘗試在盡可能少的查詢中擷取所有資訊。但這沒關係，因為沒有不必要的 IPC 負擔。在每個時間軸頁面的底部，Fossil 會顯示產生該頁面大約花了多長時間。對於 50 個條目的時間軸，延遲通常小於 25 毫秒。剖析顯示，這些毫秒中只有少數花在資料庫引擎內部。

在 Fossil 中使用 N+1 查詢模式不會損害應用程式。但 N+1 查詢模式確實有其好處。首先，建立時間軸查詢的程式碼區段可以與準備每個時間軸條目以供顯示的區段完全分開。這提供了責任分離，有助於保持程式碼簡潔且易於維護。其次，顯示所需資訊以及萃取該資訊所需的查詢會根據要顯示的物件類型而有所不同。簽入需要一組查詢。問題追蹤需要另一組查詢。Wiki 頁面需要不同的查詢。依此類推。透過在處理各種實體的程式碼部分中依需求實作這些查詢，可以進一步分離責任並簡化整體程式碼庫。

因此，SQLite 能夠執行一個或兩個大型且複雜的查詢，或者它可以執行許多較小且較簡單的查詢。兩者都很有效率。應用程式可以使用任一種或兩種技術，具體取決於最適合手邊情況的技術。

以下是用於產生特定時間軸的所有 SQL 記錄（擷取於 2016-09-16）

-- sqlite3_open: /home/drh/sqlite/sqlite/.fslckout
PRAGMA foreign_keys=OFF;
SELECT sql FROM localdb.sqlite_schema WHERE name=='vfile';
-- sqlite3_open: /home/drh/.fossil
PRAGMA foreign_keys=OFF;
SELECT value FROM vvar WHERE name='repository';
ATTACH DATABASE '/home/drh/www/repos/sqlite.fossil' AS 'repository' KEY '';
SELECT value FROM config WHERE name='allow-symlinks';
SELECT value FROM global_config WHERE name='allow-symlinks';
SELECT value FROM config WHERE name='aux-schema';
SELECT 1 FROM config WHERE name='baseurl:http://';
SELECT value FROM config WHERE name='ip-prefix-terms';
SELECT value FROM global_config WHERE name='ip-prefix-terms';
SELECT value FROM config WHERE name='localauth';
SELECT value FROM vvar WHERE name='default-user';
SELECT uid FROM user WHERE cap LIKE '%s%';
SELECT login FROM user WHERE uid=1;
SELECT cap FROM user WHERE login = 'nobody';
SELECT cap FROM user WHERE login = 'anonymous';
SELECT value FROM config WHERE name='public-pages';
SELECT value FROM global_config WHERE name='public-pages';
SELECT value FROM config WHERE name='header';
SELECT value FROM config WHERE name='project-name';
SELECT value FROM config WHERE name='th1-setup';
SELECT value FROM global_config WHERE name='th1-setup';
SELECT value FROM config WHERE name='redirect-to-https';
SELECT value FROM global_config WHERE name='redirect-to-https';
SELECT value FROM config WHERE name='index-page';
SELECT mtime FROM config WHERE name='css';
SELECT mtime FROM config WHERE name='logo-image';
SELECT mtime FROM config WHERE name='background-image';
CREATE TEMP TABLE IF NOT EXISTS timeline(
  rid INTEGER PRIMARY KEY,
  uuid TEXT,
  timestamp TEXT,
  comment TEXT,
  user TEXT,
  isleaf BOOLEAN,
  bgcolor TEXT,
  etype TEXT,
  taglist TEXT,
  tagid INTEGER,
  short TEXT,
  sortby REAL
)
;
INSERT OR IGNORE INTO timeline SELECT
  blob.rid AS blobRid,
  uuid AS uuid,
  datetime(event.mtime,toLocal()) AS timestamp,
  coalesce(ecomment, comment) AS comment,
  coalesce(euser, user) AS user,
  blob.rid IN leaf AS leaf,
  bgcolor AS bgColor,
  event.type AS eventType,
  (SELECT group_concat(substr(tagname,5), ', ') FROM tag, tagxref
    WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid
      AND tagxref.rid=blob.rid AND tagxref.tagtype>0) AS tags,
  tagid AS tagid,
  brief AS brief,
  event.mtime AS mtime
 FROM event CROSS JOIN blob
WHERE blob.rid=event.objid
 AND NOT EXISTS(SELECT 1 FROM tagxref WHERE tagid=5 AND tagtype>0 AND rid=blob.rid)
 ORDER BY event.mtime DESC LIMIT 50;
-- SELECT value FROM config WHERE name='timeline-utc';
SELECT count(*) FROM timeline WHERE etype!='div';
SELECT min(timestamp) FROM timeline;
SELECT julianday('2016-09-15 14:54:51',fromLocal());
SELECT EXISTS (SELECT 1 FROM event CROSS JOIN blob WHERE blob.rid=event.objid AND mtime<=2457647.121412037);
SELECT max(timestamp) FROM timeline;
SELECT julianday('2016-09-24 17:42:43',fromLocal());
SELECT EXISTS (SELECT 1 FROM event CROSS JOIN blob WHERE blob.rid=event.objid AND mtime>=2457656.238009259);
SELECT value FROM config WHERE name='search-ci';
SELECT value FROM vvar WHERE name='checkout';
SELECT value FROM config WHERE name='timeline-max-comment';
SELECT value FROM global_config WHERE name='timeline-max-comment';
SELECT value FROM config WHERE name='timeline-date-format';
SELECT value FROM config WHERE name='timeline-truncate-at-blank';
SELECT value FROM global_config WHERE name='timeline-truncate-at-blank';
SELECT * FROM timeline ORDER BY sortby DESC;
SELECT value FROM config WHERE name='hash-digits';
SELECT value FROM global_config WHERE name='hash-digits';
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68028;
SELECT pid FROM plink WHERE cid=68028 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM tagxref WHERE rid=68028 AND tagid=9 AND tagtype>0;
SELECT value FROM config WHERE name='timeline-block-markup';
SELECT value FROM config WHERE name='timeline-plaintext';
SELECT value FROM config WHERE name='wiki-use-html';
SELECT value FROM global_config WHERE name='wiki-use-html';
SELECT 1 FROM private WHERE rid=68028;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68026;
SELECT pid FROM plink WHERE cid=68026 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68026;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68024;
SELECT pid FROM plink WHERE cid=68024 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68024;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68018;
SELECT pid FROM plink WHERE cid=68018 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68018;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68012;
SELECT pid FROM plink WHERE cid=68012 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68012;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68011;
SELECT value FROM config WHERE name='details';
SELECT pid FROM plink WHERE cid=68011 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM tagxref WHERE rid=68011 AND tagid=9 AND tagtype>0;
SELECT 1 FROM private WHERE rid=68011;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68008;
SELECT pid FROM plink WHERE cid=68008 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68008;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68006;
SELECT pid FROM plink WHERE cid=68006 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68006;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=68000;
SELECT pid FROM plink WHERE cid=68000 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=68000;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67997;
SELECT pid FROM plink WHERE cid=67997 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67997;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67992;
SELECT pid FROM plink WHERE cid=67992 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67992;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67990;
SELECT pid FROM plink WHERE cid=67990 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67990;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67989;
SELECT pid FROM plink WHERE cid=67989 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67989;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67984;
SELECT pid FROM plink WHERE cid=67984 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67984;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67983;
SELECT pid FROM plink WHERE cid=67983 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67983;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67979;
SELECT pid FROM plink WHERE cid=67979 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67979;
SELECT value FROM config WHERE name='ticket-closed-expr';
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='1ec41379c9c1e400' AND tkt_uuid<'1ec41379c9c1e401';
SELECT 1 FROM private WHERE rid=67980;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67977;
SELECT pid FROM plink WHERE cid=67977 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='1ec41379c9c1e400' AND tkt_uuid<'1ec41379c9c1e401';
SELECT 1 FROM private WHERE rid=67977;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='1ec41379c9c1e400' AND tkt_uuid<'1ec41379c9c1e401';
SELECT 1 FROM private WHERE rid=67974;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67971;
SELECT pid FROM plink WHERE cid=67971 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67971;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67972;
SELECT pid FROM plink WHERE cid=67972 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67972;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67969;
SELECT pid FROM plink WHERE cid=67969 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67969;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67966;
SELECT pid FROM plink WHERE cid=67966 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67966;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67962;
SELECT pid FROM plink WHERE cid=67962 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67962;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67960;
SELECT pid FROM plink WHERE cid=67960 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67960;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67957;
SELECT pid FROM plink WHERE cid=67957 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67957;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67955;
SELECT pid FROM plink WHERE cid=67955 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67955;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67953;
SELECT pid FROM plink WHERE cid=67953 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='5990a1bdb4a073' AND tkt_uuid<'5990a1bdb4a074';
SELECT 1 FROM blob WHERE uuid>='5990a1bdb4a073' AND uuid<'5990a1bdb4a074';
SELECT 1 FROM private WHERE rid=67953;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67941;
SELECT pid FROM plink WHERE cid=67941 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67941;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67940;
SELECT pid FROM plink WHERE cid=67940 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67940;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67938;
SELECT pid FROM plink WHERE cid=67938 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67938;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67935;
SELECT pid FROM plink WHERE cid=67935 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67935;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67934;
SELECT pid FROM plink WHERE cid=67934 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67934;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67932;
SELECT pid FROM plink WHERE cid=67932 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67932;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67930;
SELECT pid FROM plink WHERE cid=67930 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67930;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67928;
SELECT pid FROM plink WHERE cid=67928 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM tagxref WHERE rid=67928 AND tagid=9 AND tagtype>0;
SELECT 1 FROM private WHERE rid=67928;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='0eab1ac7591f511d' AND tkt_uuid<'0eab1ac7591f511e';
SELECT 1 FROM private WHERE rid=67919;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='01874d252ac44861' AND tkt_uuid<'01874d252ac44862';
SELECT 1 FROM blob WHERE uuid>='01874d252ac44861' AND uuid<'01874d252ac44862';
SELECT 1 FROM private WHERE rid=67918;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67916;
SELECT pid FROM plink WHERE cid=67916 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='0eab1ac759' AND tkt_uuid<'0eab1ac75:';
SELECT 1 FROM private WHERE rid=67916;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='a49bc0a8244feb08' AND tkt_uuid<'a49bc0a8244feb09';
SELECT 1 FROM blob WHERE uuid>='a49bc0a8244feb08' AND uuid<'a49bc0a8244feb09';
SELECT 1 FROM private WHERE rid=67914;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67913;
SELECT pid FROM plink WHERE cid=67913 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='0eab1ac7591f' AND tkt_uuid<'0eab1ac7591g';
SELECT 1 FROM private WHERE rid=67913;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67911;
SELECT pid FROM plink WHERE cid=67911 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67911;
SELECT status='Closed' OR status='Fixed' FROM ticket  WHERE tkt_uuid>='0eab1ac7591f511d' AND tkt_uuid<'0eab1ac7591f511e';
SELECT 1 FROM private WHERE rid=67909;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67907;
SELECT pid FROM plink WHERE cid=67907 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67907;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67899;
SELECT pid FROM plink WHERE cid=67899 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67899;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67897;
SELECT pid FROM plink WHERE cid=67897 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67897;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67895;
SELECT pid FROM plink WHERE cid=67895 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67895;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67893;
SELECT pid FROM plink WHERE cid=67893 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67893;
SELECT value FROM tagxref WHERE tagid=8 AND tagtype>0 AND rid=67891;
SELECT pid FROM plink WHERE cid=67891 AND pid NOT IN phantom ORDER BY isprim DESC;
SELECT 1 FROM private WHERE rid=67891;
SELECT count(*) FROM plink
 WHERE pid=67928 AND isprim
   AND coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.pid), 'trunk')
      =coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.cid), 'trunk')
;
SELECT count(*) FROM plink
 WHERE pid=68011 AND isprim
   AND coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.pid), 'trunk')
      =coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.cid), 'trunk')
;
SELECT count(*) FROM plink
 WHERE pid=68028 AND isprim
   AND coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.pid), 'trunk')
      =coalesce((SELECT value FROM tagxref
                  WHERE tagid=8 AND rid=plink.cid), 'trunk')
;
SELECT value FROM config WHERE name='show-version-diffs';
SELECT value FROM config WHERE name='adunit-omit-if-admin';
SELECT value FROM global_config WHERE name='adunit-omit-if-admin';
SELECT value FROM config WHERE name='adunit-omit-if-user';
SELECT value FROM global_config WHERE name='adunit-omit-if-user';
SELECT value FROM config WHERE name='adunit';
SELECT value FROM global_config WHERE name='adunit';
SELECT value FROM config WHERE name='auto-hyperlink-delay';
SELECT value FROM global_config WHERE name='auto-hyperlink-delay';
SELECT value FROM config WHERE name='footer';
PRAGMA database_list;
PRAGMA database_list;
PRAGMA localdb.freelist_count;
PRAGMA localdb.page_count;

此頁面最後修改於 2022-01-08 05:02:57 UTC