sql - 为什么 != 运算符比 = 运算符快？

Question

我有一个 SQLite 数据库，我执行以下查询:

SELECT
    category,
    name,
    COUNT(side.id) as nb
FROM main
LEFT JOIN side
    ON main.id = side.id_main
WHERE category != 3
GROUP BY category, name;

此查询大约需要 0.3 秒并返回 8000 行。
这是关联的查询计划:

id  parent  notused detail
7   0   0   SCAN TABLE main USING COVERING INDEX idx_main
19  0   0   SEARCH TABLE side USING AUTOMATIC COVERING INDEX (id_main=?)

现在，我只想选择一个类别。所以，我重写了我的 WHERE像这样的条款:

SELECT
    category,
    name,
    COUNT(side.id) as nb
FROM main
LEFT JOIN side
    ON main.id = side.id_main
WHERE category = 1
GROUP BY category, name;

这个查询现在需要大约 43 秒并返回 5000 行!长了100倍!
这是关联的查询计划:

id  parent  notused detail
7   0   0   SEARCH TABLE main USING COVERING INDEX idx_main (category=?)
11  0   0   SCAN TABLE side

为什么等于运算符这么慢？我的查询有什么问题吗？

繁殖步骤:

创建以下表

CREATE TABLE main (
id INTEGER PRIMARY KEY AUTOINCREMENT,
category INTEGER,
name VARCHAR(64)
);

CREATE TABLE side (
id INTEGER PRIMARY KEY AUTOINCREMENT,
id_main INTEGER
);

CREATE INDEX idx_side ON side (id, id_main);

CREATE INDEX idx_main ON main (category, name);

使用以下 Perl 脚本填充表

use strict;
use warnings FATAL => 'all';

use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=file.db","","");

my $v = 'A';

for my $i (1..9000) {
    my $category = 1;
    $category = 2 if $i > 5000;
    $category = 3 if $i > 8000;
    $dbh->do("INSERT INTO main(category, name) VALUES($category, '$v')");
    $v++;
}

for my $i (1..100000) {
    my $r = int(rand() * 9000 + 1);
    $dbh->do("INSERT INTO side(id_main) VALUES($r)");
}

Answer 1

感谢您提出有趣的问题并解释计划。
这些计划向我们展示了为什么“!=”比“=”查询花费的时间更少。
"!="查询扫描 idx_main 索引以获取数据，然后使用另一个自动创建的索引在“侧”表中查找相应的行。所以，我认为它就像数据库只是完全扫描了一个索引，然后在另一个索引中搜索了合适的数据。
“=”查询首先使用索引搜索 category=1 的行，然后完全扫描整个“side”表，这需要很长时间，因为它需要检查该表中的每个元组。