mysql - 如何计算对一个数据库表进行简单选择查询的成本？

Question

员工(姓名、职务、dname、地址)都是相同长度的字符串字段。

ename 属性是一个候选键。该关系包含 10,000 页。有 10 个缓冲区页。

查询是:选择 E.标题、E.名称来自员工 EWHERE E.title=‘管理员’

假设只有 10% 的 Employee 元组满足选择条件。

假设 ename 上的聚集 B+ 树索引(唯一索引)可用。最佳计划的成本是多少？

我如何计算这笔费用？如果标题上有聚集的 B+ 树索引，我将如何计算？

另一个查询:选择E.ename来自员工 EWHERE E.title=‘管理员’AND E.dname=‘财务’

假设只有 10% 的 Employee 元组满足条件 E.title ='Administrator'，只有 10% 满足 E.dname ='Finance'，只有 5% 满足这两个条件。

假设聚集 B+ 树索引(唯一索引)可用。最佳计划的成本是多少？

专家!请帮忙。任何意见/建议将不胜感激。我想了解整个过程。我做了很多研究，我想我知道如何计算每个操作的成本，让我困惑的是他们说关系包含 10,000 页，而不说每个页面中有多少元组？根据我所了解到的，我相信我们必须以元组的形式得到关系的总大小，我对吗？为什么？

感谢所有花时间阅读问题的人:-)

Answer 1

如果没有合适的索引，查询将进行表扫描。由于读取行是执行时间的主要部分(在许多情况下)；您提到的变化并不重要。

如果您确实有相关索引，并且该索引具有足够的选择性(10% 可能是“选择性足够”)，则查询将分为两个步骤:

1. 扫描索引的部分，这是一个单独的 BTree。
2. 对于每一行，从该 BTree 获取主键(假设您使用的是 InnoDB)。使用该 PK，在主 BTree 中查找包含 PK 和数据的行。

如果所有必需的 block 都缓存在 buffer_pool 中(再次假设 InnoDB)，则成本的变化相当小。

如果不是所有 block 都在缓存中(因为 mysqld 刚刚启动，或者因为索引/数据太大而无法保持缓存)，那么您就需要“计算磁盘命中数”。这是因为“成本”的主要部分是 I/O。现在计算成本相当复杂，因为需要知道已经缓存了多少百分比、查询是否会“破坏”缓存、这 10% 是均匀分散、还是聚集在一起，还是介于两者之间。

由于(对于 InnoDB)，PK 与数据“聚集”，因此通过 PK 进行的查找与通过辅助键进行的查找的行为不同。

10K 行是“小”。 10 个缓冲页——什么意思？ “所有都是长度相同的字符串字段”——使用 CHAR 代替 VARCHAR 是不现实的，也不是一个好主意。不管怎样，字符串长度对这个讨论影响很小。

WHERE E.title='Administrator' AND E.dname='Finance' -- 请求 INDEX(title, dname) 位于其中 订单。

“经验法则”:一个 block (InnoDB) 可以容纳 100 行(数据或索引)。 (当然，这可能会有很大差异。但有时对于“计算磁盘命中数”很方便。)

在我的cookbook ，我发现更容易专注于设计“最佳”索引，而无需计算“成本”。

有关查询的进一步说明

“假设只有 10% 的 Employee 元组满足条件 E.title ='Administrator'，只有 10% 满足 E.dname ='Finance'，只有 5% 满足这两个条件。”对于 MySQL，以下是更多详细信息:

情况 1:INDEX(title) -- 与第一个查询类似 -- 索引范围扫描 10%，然后探测数据。
情况 2:INDEX(dname) -- 同上。
情况 3:两个索引 - 使用“索引合并相交”进行两个索引“范围扫描”的可能性很小，将两个集合合并在一起，然后访问行数据。
情况 4(最好):INDEX(title, dname)(或相反顺序):返回索引范围扫描，但仅限于 5% 的项目。

MySQL 的首选引擎是 InnoDB。我所讨论的假设是，而不是 MyISAM。对于 InnoDB，“数据”存储在 B+Tree 中，每个二级索引也是如此。在思考如何执行查询时，请记住这种相似性。另请注意，二级索引的“叶节点”包含 PK，从而提供查找其余记录的机制。