我有一个包含几列的表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)具有会话列。然后,它将促进多用户并发使用。但这是过度设计的答案,但询问您是否需要更多信息。

09-10 06:50
查看更多