我有一张这样的桌子:
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 检查更快?
在第一个查询中,mariadb 需要决定是在 Active 上还是在 ExternaId 上使用索引。由于 Active 是一个布尔字段,它可能假设一半的行(2000 万)将满足条件,并且它将使用 ExternalId 上的索引,认为它可以更有效。
在第二个中,您强制它首先使用活动索引选择活动会话。如果活动会话只是表的一小部分,则会产生几行,然后必须扫描这些行以找到 ExternalId 与您选择的会话相对应的行。
在两个查询上使用 EXPLAIN 应该会为您提供有关 maridab 使用的索引的信息。
使用复合索引会更快更简单:
这两列的顺序无关紧要。添加时,将电流丢弃
INDEX(Active)
为不必要的。关于术语的挑剔:
FROM ( SELECT ... )
是“派生表”。临时表是CREATE TEMPORARY TABLE ...
或隐式创建的临时表。优化器可能会也可能不会使用隐式临时表来实现派生表。在您的原始查询中,什么是“myid”?是数字吗?存储例程的参数?字符串?如果它是一个简单的文字数字,我希望优化器使用
INDEX(ExternalId)
,找到 3 行,然后决定哪一个是Active
。这应该已经足够快了。所以...我闻起来像是统计数据搞砸了。运行ANALYZE TABLE Sessions;
。(这种问题很少见。)