主页 / dba / 问题 / 326493
Accepted
Elikill58
Asked: 2023-04-27 14:56:30 +0800 CST

为什么制作临时表比多个 WHERE 条件更快？

  • 772

我有一张这样的桌子：

CREATE TABLE Sessions (
  SessionId int(11) NOT NULL AUTO_INCREMENT,
  ExternalId int(11) NOT NULL,
  Active bit(1) NOT NULL DEFAULT b'0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

ALTER TABLE Sessions
  ADD PRIMARY KEY (SessionId),
  ADD KEY ExternalId (ExternalId),
  ADD KEY Active (Active);

该表包含超过 4000 万行，只有 2k 行设置为活动行，并且每个 ExternalId 仅 3 行。

我想获得所有活动会话以获得精确的ExternalId. 所以，我基本上做了：

SELECT * FROM Sessions WHERE Active = 1 AND ExternalId = myId;

但是，这需要很多时间（超过 3 秒）。经过一番调查，我发现这个更快：

SELECT * FROM (SELECT * FROM Sessions WHERE Active = 1) x WHERE x.ExternalId = myId;

这样，大约需要 0.005 秒。

我想知道为什么制作临时表比使用 2 个参数进行基本 where 检查更快？

performance

2 个回答

  1. Best Answer

    在第一个查询中，mariadb 需要决定是在 Active 上还是在 ExternaId 上使用索引。由于 Active 是一个布尔字段，它可能假设一半的行（2000 万）将满足条件，并且它将使用 ExternalId 上的索引，认为它可以更有效。

    在第二个中，您强制它首先使用活动索引选择活动会话。如果活动会话只是表的一小部分，则会产生几行，然后必须扫描这些行以找到 ExternalId 与您选择的会话相对应的行。

    在两个查询上使用 EXPLAIN 应该会为您提供有关 maridab 使用的索引的信息。

    • 3

  2. 使用复合索引会更快更简单：

    INDEX(Active, ExternalId)

    这两列的顺序无关紧要。添加时，将电流丢弃INDEX(Active)为不必要的。

    关于术语的挑剔： FROM ( SELECT ... )是“派生表”。临时表是CREATE TEMPORARY TABLE ...或隐式创建的临时表。优化器可能会也可能不会使用隐式临时表来实现派生表。

    在您的原始查询中，什么是“myid”？是数字吗？存储例程的参数？字符串？如果它是一个简单的文字数字，我希望优化器使用INDEX(ExternalId)，找到 3 行，然后决定哪一个是Active。这应该已经足够快了。所以...我闻起来像是统计数据搞砸了。运行ANALYZE TABLE Sessions;。（这种问题很少见。）

    • 0

相关问题