mysql - 相关查询的 EC(内部)计算

Question

我目前正在研究查询优化，并且正在尝试计算其中包含依赖查询的查询的成本。

查询是:

select 
    U.userName, email, yearOfBirth, countryName
from 
    tblUser U 
inner join 
    tblCountry C on U.countryNo = C.countryNo
left join 
    tblList L on U.userName = L.userName
where 
    noOfPoints = 1000 
    and yearOfBirth = 1975 
    and listNo is null 
    and not exists (select count(*)
                    from tblseek S
                    where S.userName = U.userName
                    having count(serialNo) > 3)

现在一些数据:

首先是表及其连接:

tbl国家:Nr=19，Fr=67，Br=1

tbl用户:Nr=1,881,923，Fr=11，Br=171,084

列表:Nr=4,667,166，Fr=62，Br=75,277

tblSeek:Nr=8,530,865，Fr=13，Br=656,221

用户寻宝次数分布为:

0 次查找:10% 的用户

1 次查找:11% 的用户

2 次查找:19% 的用户

3 及以上:60% 的用户

就每个用户的列表数量而言，用户之间的分布是:

0 个列表:30% 的用户

1-3 列出:34% 的用户

4-6 列表:36% 的用户

众所周知，整个系统的最大积分为5200，并且在用户之间均匀分配。

众所周知，最年轻的用户为 16 岁，最大的为 57 岁，并且年龄在所有用户中均匀分布。

• block 大小为:2048 位

• 分配给查询的内存量为:100 block

• 每个表均按其主键排序，如果有多个字段，则按其在表中出现的顺序排序。

现在，我要计算什么:

我试图在外部查询中执行联接之前确定运行依赖查询的成本，因此将 tblUser 加载到内存并执行内部查询，然后完成其他联接。

我的问题:好吧，我发现为了做到这一点，我需要以 171,084 个 block 的代价将 tblUser 加载到内存中，并以 656,221 个 block 的代价加载 tblSeek，我无法确定的是如何以及在哪里执行“Where”子句的条件，因为您可能会注意到该子句中的大多数条件都与 tblUser 的属性有关(除了列表条件)。

我设法估算出(我不确定这样做是否正确，如果我错了请纠正我)在统一转移知识下有多少记录会回答“点= 1000”(其361)记录)并对“yearOfBirth = 1975”(45,900 条记录)也执行了相同的操作。我通过将 tblUser 的 Nr 分别除以均匀分流已知的数字区域来做到这一点。

我无法想出一种方法将它们组合成一个数字，表示所有条件混合在一起(包括相关查询)将返回多少条记录。

如果您能指导我一点，我将非常感激。

谢谢

汤姆

Answer 1

使用EXPLAIN FORMAT=JSON SELECT ...进一步了解查询的执行方式。

使用它来获取工作的一些“行数”:

FLUSH STATUS;
SELECT ...
SHOW SESSIONS STATUS LIKE 'Handler%';

索引。 (我无法告诉你已经拥有什么，因为你没有提供 SHOW CREATE TABLE 。)

U: INDEX(yearOfBirth, noOfPoints)

EXISTS(...)通常表述为简单的 EXISTS ( SELECT * FROM ... ) ;不需要COUNT(*) 。我不明白EXISTS的目的。也许这会检查 tblseek 有多少行:

AND ( SELECT COUNT(*) FROM tblseek WHERE userName = U.userName ) <= 3

也许你想要INDEX(userName, serialNo) ， 以该顺序。这是一个“覆盖”索引，因此子查询可以完全在索引中执行。 EXPLAIN将通过“使用索引”来指示。

该子查询总共将接触 tblseek(或其索引)中的每一行一次。实际上，每次扫描都会超调一次。所以触摸的行数很可能是N+M，其中N是tblseek中的行数M 是 userName 的不同值的数量在tblseek 。如果没有该索引，行数将会高很多。