mysql - 每个连接的计数 - 优化

Question

结果:我使用了三种方法:

1. 三个子查询，每个查询 1 个连接(我的)
2. 三个子查询，无连接，使用 where 进行过滤 (SlimsGhost)
3. 三重连接 (Solarflare)

我用“解释”和“分析”做了一些统计，解释了每个查询必须做的工作，以下结果并不令人惊讶:stats

相对结果:

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_description

如果你想玩它:

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

Answer 1

出于优化目的，一个好的经验法则是加入更少，而不是更多。事实上，您应该尝试将尽可能少的行与尽可能少的行连接起来。对于任何额外的连接，您都会增加成本，而不是增加成本。因为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) 字段，那么这甚至可能与原始解决方案一样快(因为它可以优化 distinct 来避免执行排序)并且会接近您的想法(只需遍历每个表/索引一次)。但在正常或最坏的情况下，它的表现应该比合理的解决方案(如 SlimGhost 的解决方案)更差 - 因为它能否找到最佳策略是值得怀疑的。但是尝试一下并检查解释(并发布结果)，也许 mysql 会做一些疯狂的事情。