主页 / dba / 问题 / 60203
Accepted
LOSTinDB
Asked: 2014-03-06 07:46:57 +0800 CST

有人可以解释为什么在mysql中加入两个视图这么慢吗？

  • 772

这是我昨天问的一个问题 - https://stackoverflow.com/questions/22180727/left-joining-two-views-is-slow。

我得到了一个对我有帮助的好答案，但我不明白为什么 LEFT JOIN 比查找慢得多。LEFT JOIN 是 16 秒——我很确定我的表至少优化了 90%——在进行查找时只需 0.14 秒。当我 LEFT JOIN 表时，它并没有这么慢那么为什么要查看？

mysql join

3 个回答

  1. Best Answer

    根据关于视图的 MySQL 文档

    MySQL Server 5.6 中提供了视图（包括可更新视图）。视图是存储的查询，在调用时会产生结果集。视图充当虚拟表。

    关于视图，首先必须意识到的是它产生了一个结果集。从视图调用的查询中产生的结果集是一个虚拟表，因为它是按需创建的。之后没有可以调用的 DDL 来立即索引结果集。出于所有意图和目的，结果集是一个没有任何索引的表。实际上，您正在执行的 LEFT JOIN 基本上是带有一些过滤的笛卡尔积。

    为了让您更详细地了解两个视图的 JOIN，我将参考我去年发表的一篇文章，解释 MySQL 用于评估 JOIN 和 WHERE 的内部机制（在 JOIN 条件和 WHERE 条件之间存在执行差异吗？）。我将向您展示在Understanding MySQL Internals (Page 172)中发布的机制：

    • 确定哪些键可用于从表中检索记录，并为每个表选择最佳键。
    • 对于每个表，决定表扫描是否比读取键更好。如果匹配key值的记录很多，key的优势就会降低，表扫描会变快。
    • 当查询中存在多个表时，确定应连接表的顺序。
    • 重写 WHERE 子句以消除死代码，减少不必要的计算并尽可能更改约束以打开使用键的方式。
    • 从联接中消除未使用的表。
    • 确定键是否可用于ORDER BY和GROUP BY。
    • 尝试简化子查询，并确定它们的结果可以缓存到什么程度。
    • 合并视图（将视图引用扩展为宏）

    好的，似乎应该使用索引。不过，仔细看看。如果你用 wordView代替Table，看看机制的执行会发生什么：

    机制修改

    • 确定可用于从中检索记录的键views，并为每个键选择最佳键view。
    • 对于每个view，确定view扫描是否比读取键更好。如果匹配key值的记录很多，key的优势就会降低，view扫描速度会变快。
    • 当查询中存在views多个时，确定应连接的顺序。views
    • 重写 WHERE 子句以消除死代码，减少不必要的计算并尽可能更改约束以打开使用键的方式。
    • 从联接中消除未使用views的。
    • 确定键是否可用于ORDER BY和GROUP BY。
    • 尝试简化子查询，并确定它们的结果可以缓存到什么程度。
    • 合并视图（将视图引用扩展为宏）

    每个表（视图）都没有索引。因此，使用虚拟表、临时表或没有索引的表在执行 JOIN 时确实变得模糊不清。使用的键仅用于 JOIN 操作，而不是用于更快地查找内容。

    将您的查询想象为拿起两本电话簿，即 2014 年黄页和 2013 年黄页。每本黄页书都包含住宅电话号码白页。

    • 2012 年底，使用数据库表生成 2013 年黄页。
    • 2013 年期间
      • 人们更改了电话号码
      • 人们收到了新的电话号码
      • 人们放弃了电话号码，转而使用手机
    • 2013 年底，使用数据库表生成 2014 年黄页。

    显然，这两个电话簿之间存在差异。对数据库表进行 JOIN 以找出 2013 年和 2014 年之间的差异应该没有问题。

    想象一下手动合并两个电话簿以找出差异。听起来很疯狂，不是吗？尽管如此，当您加入两个视图时，这正是您要求 mysqld 执行的操作。请记住，您没有加入真正的表，也没有可供捎带的索引。

    现在，让我们回顾一下实际的查询。

    SELECT DISTINCT
viewA.TRID, 
viewA.hits,
viewA.department,
viewA.admin,
viewA.publisher,
viewA.employee,
viewA.logincount,
viewA.registrationdate,
viewA.firstlogin,
viewA.lastlogin,
viewA.`month`,
viewA.`year`,
viewA.businesscategory,
viewA.mail,
viewA.givenname,
viewA.sn,
viewA.departmentnumber,
viewA.sa_title,
viewA.title,
viewA.supemail,
viewA.regionname
FROM
viewA
LEFT JOIN viewB ON viewA.TRID = viewB.TRID
WHERE viewB.TRID IS NULL

    您正在使用一个虚拟表（没有索引的表）viewA，将它连接到另一个虚拟表 viewB。间歇性生成的临时表将与 viewA 一样大。然后，您在大型临时表上运行内部排序以使其与众不同。

    结语

    考虑到评估 JOIN 的内部机制，以及视图结果集的瞬态和无索引特性，您的原始查询（两个视图的 LEFT JOIN）应该获得数量级的运行时间。同时，考虑到我刚刚描述的相同 JOIN 算法，您从 StackOverflow 得到的答案应该表现良好。

    我希望我刚刚发布的血腥细节能回答你关于为什么的问题。

    • 11

  2. EXPLAIN EXTENDED [select query]然后SHOW WARNINGS将显示视图的重写形式。从这里，更容易分析性能特征。

    视线检查查询通常不容易优化。

    • 1

  3. 答案与执行这些操作的方法有关。

    由于视图本质上是无索引的，因此使用视图中的字段的 JOIN 操作将比使用表的 JOIN 操作花费更长的时间，因为扫描不能使用索引。

    在这种情况下，查找还限制了在处理过程中必须返回的记录数量——它只从一个视图中提取另一个视图中不存在的记录。JOIN 提取所有记录，然后检查两者中是否存在记录。

    • -2

相关问题