时间戳

首页 > 解决方案 > 为什么使用“in”和“on”语句进行查询无限运行

sql - 为什么使用“in”和“on”语句进行查询无限运行

问题描述

我有三个表，table3 基本上是 table1 和 table2 的中间表。当我执行包含“in”并连接 table1 和 table3 的查询语句时，它一直在运行，我无法得到结果。如果我使用id=134而不是id in (134,267,390,4234 ... )，结果就会出现。我不明白为什么“in”有效果，有人知道吗？

查询语句：

select count(*) from table1, table3 on id=table3.table1_id where table3.table2_id = 123 and id in (134,267,390,4234) and item = 30;

表结构：

table1:
   id integer primary key,
   item integer
   
table2:
   id integer,
   item integer

table3:
    table1_id integer,
    table2_id integer

-- the DB without index was 0.8 TB after the three indices is now 2.5 TB
indices on: table1.item, table3.table1_id, table3.table2_id

环境：Linux，sqlite 3.7.17

标签： sqldatabasesqlite

解决方案

from table1, table3是大多数数据库上的交叉连接，对于数据的大小，交叉连接是巨大的，但在 SQLite3 中它是内部连接。来自SQLite SELECT 文档

旁注： CROSS JOIN 的特殊处理。“INNER JOIN”、“JOIN”和“,”连接运算符之间没有区别。它们在 SQLite 中是完全可以互换的。

在这种特定情况下，这不是您的问题，但我们不要诱惑命运；总是明确地写出你的连接。

select count(*)
from table1
join table3 on id=table3.table1_id
where table3.table2_id = 123
  and id in (134,267,390,4234);

由于您只是在计数，因此您不需要 table1 中的任何数据，而是 ID。table3 有table1_id，所以不需要加入table1。我们可以完全使用 table3 连接表来做到这一点。

select count(*)
from table3
where table2_id = 123
  and table1_id in (134,267,390,4234);

SQLite 每个表只能使用一个索引。要在如此大的数据集上执行此操作，您需要两列的复合索引table3(table1_id, table2_id)： . 大概你不想要重复，所以这应该采用唯一索引的形式。这将涵盖仅针对 table1_id 的查询以及针对 table1_id 和 table2_id 的查询；您应该删除 table1_id 索引以节省空间和时间。

create unique index table3_unique on table3(table1_id, table2_id);

复合索引不适用于仅使用 table2_id 的查询，保留现有的 table2_id 索引。

您的查询现在应该运行 lickity-split。

有关更多信息，请阅读SQLite 查询优化器。

1 TB包含大量数据。虽然SQLite 在技术上可以处理这个问题，但它可能不是最佳选择。它非常适合小型和简单的数据库，但它缺少很多功能。您应该研究更强大的数据库，例如PostgreSQL。它不是灵丹妙药，所有相同的原则都适用，但它更适合这种规模的数据。

推荐阅读