Query-Performance

哪個查詢有更好的性能?

  • April 13, 2020

我有3個表如下:

class
 id:bigint(PK)
 name:varchar (unique)


principal:
 id: bigint(PK)
 pid:bigint
 role:boolean
 uniqueConstraint(pid, role)

entry:
 cid: (FK, reference class)
 pid: (FK, refernce principal)
 object_id: bigint
 code: tinyint
 PK: (cid, pid, obj)

entry查詢必須使用參數集檢查是否存在記錄。

假設參數集如下:

  • 班級名稱:班級#3
  • 作為使用者的主體 ID:3
  • 作為角色的主體 ID:2
  • 對象 ID:45

我寫了 2 個查詢,一個使用join一個使用sub-query,但我不知道哪個在大規模生產環境中表現更好:

查詢編號 1:

select id 
from entry 
where pid in ( select id 
              from principal 
              where (pid=2 AND role) 
                 OR (pid=3 AND !role)) 
 AND cid = (select id 
            from class 
            where name='Class#3') 
 AND object_id=45

和查詢號 2:

select e.id 
from class c 
inner join entry e on e.cid=c.id 
                 and c.name='Class#3' 
inner join principal p on p.id=e.pid 
                     and p.id in ( select id 
                                   from principal 
                                   where (pid=2 AND role) 
                                      OR (pid=3 AND !role)) 
where e.object_id=45

當然還有一個額外的條件來檢查我沒有在查詢中包含的程式碼

假設 ’entry’ 中有 100 行class,10000principal和 250000 以上,並且必須為每個請求執行查詢(如解釋),並且有 3000 個使用者不斷同時在系統上工作

  1. 這些查詢中的哪一個會執行得更好,為什麼?原因對於進一步的工作非常重要
  2. 有沒有比這兩種方法更好的方法來編寫查詢,或者甚至更好的方法來構造模式?

問候


PS:我已經閱讀過這個關於比較子查詢和連接的問題,但我的問題並不完全是一個簡單的比較


principal更新:修復了表中的角色列名

通常,表達查詢的最簡單方法會執行得最好。

以下查詢應該接近最優:

select e.id
from entry e
join class c on c.id = e.cid 
join principal p on p.id = e.pid
where e.object_id = 45
and c.name = 'Class#3'
and (p.id = 2 and p.role or p.id = 3 and !p.role);

但很大程度上取決於您的數據和索引。比較執行計劃以確定。

引用自:https://dba.stackexchange.com/questions/264875