優化：僅插入沒有附加條件的現有行

我有兩個數據庫：

“目標”數據庫看起來像

CREATE TABLE parent (
 erow_id  integer PRIMARY KEY,
 -- these two columns is a composite key (uid)
 uid_p1   integer,
 uid_p2   integer,
);

CREATE TABLE child (
 erow_id  integer PRIMARY KEY,
 parent   integer, -- pointed to parent.erow_id
 value    text,
 vtype    integer
);

-- only this one index is presented
CREATE INDEX idx_child_parent ON child (parent);

“更新檔”（它是附加的數據庫）看起來像

CREATE TABLE source (
 value   text,
 -- composite uid pointed to target.parent
 uid_p1  int,  
 uid_p2  int   
);

兩個數據庫都是“固定的”，很難修改它們，甚至添加索引。

僅當沒有 type = 100 的行（例如）時，我才需要將新行（值）插入到childfrom中。patch``parent

此刻我使用了這樣一個醜陋的查詢：

INSERT INTO target.child (value, parent, vtype)
 SELECT 
   p1.value, target.parent.erow_id, 100
 FROM
   patch.source p1
   INNER JOIN target.parent 
     ON (target.parent.uid_p1 = p1.uid_p1 
     AND target.parent.uid_p2 = p1.uid_p2)
 WHERE NOT EXISTS (
   SELECT  
     1
   FROM
     patch.source p2, 
     target.child,
     target.parent
   WHERE 
     (p1.rowid = p2.rowid) AND
     (target.child.vtype = 100) AND
     (target.child.parent = target.parent.erow_id) AND
     (target.parent.uid_p1 = p2.uid_p1) AND
     (target.parent.uid_p2 = p2.uid_p2)
 );

EXPLAIN QUERY PLAN:

• SCAN TABLE patch.source AS p1
• EXECUTE CORRELATED SCALAR SUBQUERY
• SEARCH TABLE patch.source AS p2 USING INTEGER PRIMARY KEY (rowid=?)
• SCAN TABLE target.child // 看起來像一個瓶頸
• SEARCH TABLE target.parent USING INTEGER PRIMARY KEY (rowid=?)
• SEARCH TABLE target.parent USING AUTOMATIC COVERING INDEX (uid_p1=? AND uid_p2=?)

是否可以在不使用新索引的情況下優化這個非常慢的查詢？這是一個大問題——我現在至少不能修改targetdb。

謝謝你。

除了添加索引（你說這是不允許的）之外，查詢是不必要的複雜。不需要相關子查詢中的 3 個表引用中的 2 個，因為它們連接到主查詢和主鍵中的相同表。您可以將其簡化為：

INSERT INTO target.child (value, parent, vtype)
 SELECT 
   p1.value, p.erow_id, 100
 FROM
   patch.source AS p1
   INNER JOIN target.parent AS p
     ON  p.uid_p1 = p1.uid_p1 
     AND p.uid_p2 = p1.uid_p2
 WHERE NOT EXISTS (
   SELECT  
     1
   FROM
     target.child AS c
   WHERE 
     c.vtype = 100 AND
     c.parent = p.erow_id
 );

上的索引child (vtype, parent)將有助於我考慮性能。如果該索引是，則可以使用子句UNIQUE進一步簡化查詢：OR IGNORE

INSERT OR IGNORE
INTO target.child (value, parent, vtype)
 SELECT 
   p1.value, p.erow_id, 100
 FROM
   patch.source AS p1
   INNER JOIN target.parent AS p
     ON  p.uid_p1 = p1.uid_p1 
     AND p.uid_p2 = p1.uid_p2 ;

有什麼OR IGNORE作用：

IGNORE

當發生適用的約束違例時，IGNORE解析算法會跳過包含約束違例的一行，並繼續處理 SQL 語句的後續行，就好像沒有出錯一樣。包含違反約束的行之前和之後的其他行被正常插入或更新。IGNORE使用衝突解決算法時不返回錯誤。

引用自：https://dba.stackexchange.com/questions/162551