我有一个包含几列的表A:
A B C
1 2 7
1 2 8
2 2 1
1 3 1
我必须在Java中构建一个函数,该函数将3个向量作为输入,vA,vB,vC,并返回在向量中具有任何值的行。数组用作过滤器。
A = {1,2,3,4}, b = {2}, C={7}
将返回一行,但A = {1}, b = {2}, C={0}
不会,因为c列中没有行的值为0向量是如此之大,以至于不可能生成很多像
WHERE (c = ? OR c = ? ...c = ? )
的OR。从SQL注入必须安全
性能至关重要
其他线程(https://stackoverflow.com/a/1532454/1991779)则建议一个临时表,然后联接这些表。问题是我有多个向量来匹配多个列而不是一个。我想我可以创建一个表,插入所有值,然后使用
WHERE A IN (SELECT A FROM TEMP ) AND B IN (SELECT B FROM TEMP ) AND C
IN (SELECT C FROM TEMP )
但是,我认为使用
JOIN
代替IN
会更快。任何使用Join
而不是IN
的建议。有什么建议么?
最佳答案
这使用了cross join
又称笛卡尔积(所有排列)的概念。因此,您的数组会生成一个派生表(在内存中),其行计数为x*y*z
,其中x,y,z是数组的大小。如果提供了大小为3,4和5的数组,则派生表的行数将为3 * 4 * 5 = 60。
您提供的产生一行的数组匹配只有4 * 1 * 1 = 4
下面的thing7
是您要搜索的主表。 covering index
即使其中包含大量数据,也应使此文件运行。覆盖索引是一种通过索引的b树扫描提供所提供的信息的索引,并且不需要读取数据页。为什么?因为所需的数据在索引中。在您的情况下,非常薄。
表A B C用作您的数组。
唯一要说的是,每个派生表都需要一个名称。因此,我们在查询中为其命名为xDerived
。将派生表视为返回并在内存中使用的东西。它不是物理表。
架构图
create table thing7
( id int auto_increment primary key,
A int not null,
B int not null,
C int not null,
index(A,B,C) -- covering index (uber-thin, uber-fast)
);
insert thing7(A,B,C) values
(1,2,7),
(1,2,8),
(2,2,1),
(1,3,1);
create table A
( id int auto_increment primary key,
value int
);
create table B
( id int auto_increment primary key,
value int
);
create table C
( id int auto_increment primary key,
value int
);
测试1
truncate table A;
truncate table B;
truncate table C;
insert A (value) values (1),(2),(3),(4);
insert B (value) values (2);
insert C (value) values (7);
select t7.*
from thing7 t7
join
( select A.value as Avalue, B.value as Bvalue, C.value as Cvalue
from A
cross join B
cross join C
order by a.value,b.value,c.value
) xDerived
on xDerived.Avalue=t7.A and xDerived.Bvalue=t7.B and xDerived.Cvalue=t7.C;
+----+---+---+---+
| id | A | B | C |
+----+---+---+---+
| 1 | 1 | 2 | 7 |
+----+---+---+---+
..
测试2
truncate table A;
truncate table B;
truncate table C;
insert A (value) values (1);
insert B (value) values (2);
insert C (value) values (0);
select t7.*
from thing7 t7
join
( select A.value as Avalue, B.value as Bvalue, C.value as Cvalue
from A
cross join B
cross join C
order by a.value,b.value,c.value
) xDerived
on xDerived.Avalue=t7.A and xDerived.Bvalue=t7.B and xDerived.Cvalue=t7.C;
-- no rows returned
将其转换为基于会话的搜索将非常容易。这里有一个概念,其中要搜索的数组(表A B C)具有会话列。然后,它将促进多用户并发使用。但这是过度设计的答案,但询问您是否需要更多信息。