代码之家 › 专栏 › 技术社区 › blucz

SQL:在基于外部数据筛选行时,如何提高性能?

sqlite sql

blucz · 技术社区 · 14 年前

假设我有一个表示固定深度层次结构的模式,如下所示:


CREATE TABLE level0 (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    text TEXT NOT NULL
)
CREATE TABLE level1 (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    text TEXT NOT NULL,
    level0_id INTEGER NOT NULL
)
CREATE TABLE level2 (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    text TEXT NOT NULL,
    level1_id INTEGER NOT NULL,
    is_important INTEGER 
)

CREATE INDEX level2_level1_id ON level2 (level1_id)
CREATE INDEX level1_level0_id ON level1 (level0_id)

(为了给人一种规模感,假设1000行位于第0级,2000行位于第1级,20000行位于第2级,这是手机SD卡上的一个sqlite数据库。0级查询最多返回1000行,1级查询返回1-30行,2级查询返回1-20行)

我正在一次显示一个级别的层次结构。因此,显示这三个级别的查询如下所示:


SELECT id,text FROM level0
SELECT id,text FROM level1 WHERE level0_id = 12345
SELECT id,text FROM level2 WHERE level1_id = 23456

简单、快速和完全索引。现在,我还希望显示相同的层次结构,但我希望根据“重要”对其进行筛选。我只想显示级别0和级别1行,这些行最终导致级别2行,其中“重要”为1。

所以我写了一些新的查询,和以前的查询大不相同。


level 0:

SELECT DISTINCT l0.id,l0.text
FROM level2 AS l2
INNER JOIN level1 AS l1 ON l1.id = l2.level1_id
INNER JOIN level0 as l0 on l0.id = l1.level0_id
WHERE l2.is_important = 1

level 1:

SELECT DISTINCT l1.id,l1.text
FROM level2 AS l2
INNER JOIN level1 AS l1 ON l1.id = l2.level1_id
WHERE l2.is_important = 1

level 2:

SELECT id,text FROM level2 WHERE level1_id = 23456 AND is_important = 1

0级和1级查询明显比上面未过滤的查询慢很多很多。我明白他们为什么慢,但我很难提高他们的表现。

我觉得奇怪的是,通过遍历最大的表来提取较小的表来开始查询,但这似乎是用SQL能够理解的术语来表达我想要的内容的最简洁、最自然的方式。

所以我的问题是:您如何改进上面过滤的0级和1级查询的性能?

4 回复 | 直到 14 年前

MPelletier 14 年前

内部连接的快速技巧: SMALL_TABLE INNER JOIN BIG_TABLE 比倒车速度快。

在这种情况下,请尝试最后添加二级表。

Bob Jarvis - Ð¡Ð»Ð°Ð²Ð° Ð£ÐºÑÐ°ÑÐ½Ñ 14 年前

我建议查看两个查询(筛选和未筛选)的计划,以了解未筛选的查询为何如此缓慢。纯粹是猜测,但是如果只有索引在每个表的ID列上,那么数据库可能会决定对LEVEL2表进行连续遍历,以查找那些“重要”为1的行。

要尝试并影响此操作,请尝试在级别2(级别1_id,非常重要)上添加索引。这会将各种查询的WHERE子句中使用的所有列放入索引中。这似乎对其他查询也有帮助。

分享和享受。

KMW 14 年前

你试过换衣服吗

在二级(一级)上创建二级索引

到

创建二级索引二级索引一级索引(一级索引,重要吗?

blucz 14 年前

我最终得到了一个更快的查询,它使用了不同的技术,避免了最昂贵的连接。这比我在这个线程中应用了所有建议之后得到的查询快了大约3倍。对连接进行重新排序使我走上了最终消除连接的道路(并且自己也提供了最佳性能增益),所以我接受了这个答案。

我现在要回答的问题是:


level 1:

SELECT l1.id,l1.text
FROM level1 AS l1
WHERE EXISTS 
(SELECT * FROM level2 AS l2 WHERE l2.level1_id = l1.id AND l2.is_important)

LEVEL0查询是两种方法的混合体——我在LEVEL0和LEVEL1上联接,但使用嵌套查询过滤LEVEL2。