mysql - 每個連接的計數-優化




join optimization (2)

出於優化目的,一個好的經驗法則是少加入而不是多加入。 實際上,您應該嘗試加入盡可能少的行。 使用任何其他聯接,您將乘以成本而不是增加成本。 因為mysql基本上只會生成一個大的乘法矩陣。 不過,其中很多都已通過索引和其他東西進行了優化。

但要回答您的問題:假設表具有唯一鍵,而idalb是專輯的唯一鍵,則實際上僅需進行一個大的連接即可進行計數。 然後,直到那時,您才能執行類似於代碼的操作:

select alb.titreAlb as "Titre",
       count(distinct payalb.idAlb, payalb.PrimaryKeyFields) "Pays",
       count(distinct peralb.idAlb, peralb.PrimaryKeyFields) "Personnages",
       count(distinct juralb.idAlb, juralb.PrimaryKeyFields) "Jurons"
from album alb
left join pays_album payalb using ( idAlb )
left join pers_album peralb using ( idAlb )
left join juron_album juralb using ( idAlb )
where alb.titreAlb = "LES CIGARES DU PHARAON"
group by alb.titreAlb

其中PrimaryKeyFields代表聯接表的主鍵字段(您必須對其進行查找)。

Distinct 將消除其他聯接對計數的影響。 但是不幸的是,通常, distinct 不會消除聯接對成本的影響。

雖然,如果您的索引覆蓋了表的所有(idAlb + PrimaryKeyFields)字段,那麼它的速度甚至可能與原始解決方案一樣快(因為它可以優化不重複項而不進行排序),並且將接近您的結果正在考慮(只需遍歷每個表/索引一次)。 但是在正常或最壞的情況下,它的性能要比合理的解決方案(例如SlimGhost的解決方案)更差-因為它會懷疑是否會找到最佳策略。 但是,嘗試一下並檢查其解釋(並發布發現),也許mysql會做一些瘋狂的事情。

結果:我使用了三種方法:

  1. 三個子查詢,每個子查詢1個(我的)
  2. 三個子查詢,無聯接,使用where過濾(SlimsGhost)
  3. 三重聯接(Solarflare)

我使用“解釋”和“分析”進行了一些統計,這些統計解釋了每個查詢必須執行的工作,並且以下結果不足為奇:

相對結果:

  1. 100%
  2. 79%
  3. 1715%

原始郵件

想法是聯接4個表,每次使用相同的PK,然後計算每個聯接分別給出的行數。

顯而易見的答案是將每個聯接與子查詢分開進行。

但是可以通過一個查詢來做到嗎? 會更有效嗎?

select "LES CIGARES DU PHARAON" as "Titre",
          (select count( payalb.idPays)
          from album alb
                     left join pays_album payalb using ( idAlb )
          where alb.titreAlb = "LES CIGARES DU PHARAON") as "Pays",
          (select count( peralb.idPers)
          from album alb
                     left join pers_album peralb using ( idAlb )
          where alb.titreAlb = "LES CIGARES DU PHARAON") as "Personnages",
          (select count( juralb.idJur)
          from album alb
                     left join juron_album juralb using ( idAlb )
          where alb.titreAlb = "LES CIGARES DU PHARAON") as "Jurons"
; 
+------------------------+------+-------------+--------+
| Titre                  | Pays | Personnages | Jurons |
+------------------------+------+-------------+--------+
| LES CIGARES DU PHARAON |    3 |          13 |     50 |
+------------------------+------+-------------+--------+

相冊行數:22

表pays_album行:45

表personnage_album行:100

表juron_album行:1704

這是我嘗試的:

select alb.titreAlb as "Titre",
         sum(case when alb.idAlb=payalb.idAlb then 1 else 0 end) "Pays",
         sum(case when alb.idAlb=peralb.idAlb then 1 else 0 end) "Personnages",
         sum(case when alb.idAlb=juralb.idAlb then 1 else 0 end) "Jurons"
from album alb
          left join pays_album payalb using ( idAlb )
          left join pers_album peralb using ( idAlb )
          left join juron_album juralb using ( idAlb )
where alb.titreAlb = "LES CIGARES DU PHARAON"
group by alb.titreAlb
;
+------------------------+------+-------------+--------+
| Titre                  | Pays | Personnages | Jurons |
+------------------------+------+-------------+--------+
| LES CIGARES DU PHARAON | 1950 |        1950 |   1950 |
+------------------------+------+-------------+--------+

但它計算完整聯接表的總行數,...(1950 = 3 * 13 * 50)

模式: https://github.com/LittleNooby/gbd2015-2016/blob/master/tintin_schema.png : https://github.com/LittleNooby/gbd2015-2016/blob/master/tintin_schema.png

表格內容: https://github.com/LittleNooby/gbd2015-2016/blob/master/tintin_description : https://github.com/LittleNooby/gbd2015-2016/blob/master/tintin_description

如果您想玩玩:

db_init: https://github.com/LittleNooby/gbd2015-2016/blob/master/tintin_ok.mysql : https://github.com/LittleNooby/gbd2015-2016/blob/master/tintin_ok.mysql


至於“數據庫的最少工作”,我認為以下內容對您的架構而言既正確又邏輯性較低。 但是請理解,除非您查看解釋計劃(預期的和實際的),否則您將無法確定。

不過,我還是建議您嘗試一下-它只能訪問“ alb”表一次,而您的原始查詢將需要訪問它四次(一次獲取“基本”唱片集記錄,然後再獲取三個子查詢的三個記錄)。

select alb.titreAlb as "Titre",
     (select count(*) from pays_album t2 where t2.idAlb = alb.idAlb) "Pays",
     (select count(*) from pers_album t2 where t2.idAlb = alb.idAlb) "Personnages",
     (select count(*) from juron_album t2 where t2.idAlb = alb.idAlb) "Jurons"
from album alb
where alb.titreAlb = "LES CIGARES DU PHARAON"






optimization