MySQL:可以让这个查询更快吗?

使用当前模式,很难改进此查询。

你已经有了索引 feature 这是当前模式设计所能做的最好的工作。

问题是 比…更相似 不是订单关系。如果 a 更类似于 b 比它要 c ,并不意味着 C 不太像 一 比它要 乙 . 因此,您不能构建描述这种关系的单个索引,并且需要分别为每个项创建索引,这将使您的索引 N^2 条目长,其中 N 是项目数。

如果你总是只需要上衣 500 项,您可以将索引限制为该数字(在这种情况下,它将保持 500 * N 条目)。

MySQL 不支持索引视图或物化视图,因此必须自己执行:

1. 创建这样的表:

   CREATE TABLE similarity
           (
           id1 INT NOT NULL,
           id2 INT NOT NULL,
           similarity DOUBLE NOT NULL,
           PRIMARY KEY (id1, id2),
           KEY (id1, similarity)
           )
   

2. 每当向表中插入新功能时,请反映 similarity :

   INSERT
   INTO    similarity
   SELECT  @newid, id,
           LEAST(@newcount, count) AS ns
   FROM    test
   WHERE   feature = @newfeature
           AND id <> @newid
   ON DUPLICATE KEY UPDATE
   SET     similarity = similarity + ns;
   
   
   INSERT
   INTO    similarity
   SELECT  @newid, id,
           LEAST(@newcount, count) AS ns
   FROM    test
   WHERE   feature = @newfeature
           AND id <> @newid
   ON DUPLICATE KEY UPDATE
   SET     similarity = similarity + ns;
   

3. 及时消除多余的相似性:

   DELETE  s
   FROM    (
           SELECT  id1,
                   (
                   SELECT  similarity
                   FROM    similarity si
                   WHERE   si.id1 = s.id1
                   ORDER BY
                           si.id1 DESC, si.similarity DESC
                   LIMIT 499, 1
                   ) AS cs
           FROM    (
                   SELECT  DISTINCT id1
                   FROM    similarity
                   ) s
           ) q
   JOIN    similarity s
   ON      s.id1 = q.id1
           AND s.similarity < q.cs
   

4. 查询数据:

   SELECT  id2
   FROM    similarity
   WHERE   id1 = @myid
   ORDER BY
           similarity DESC
   LIMIT 500
   

将浮点数作为主键(pk)的一部分是一个杀手。因此,它不应成为任何约束的一部分-唯一键(UK)、外键(FK)等。

要多次提高SQL查询的性能,请尝试如下更改架构:

CREATE TABLE test ( 
item_id      INTEGER,
feature_id INTEGER,
count   INTEGER );

CREATE TABLE features (
id   INTEGER, feature_value double not null );

CREATE TABLE items (
id   INTEGER, item_description varchar2(100) not null );

ALTER TABLE test ADD CONSTRAINT fk_test_item_id foreign key (item_id) references items(id);

ALTER TABLE test ADD CONSTRAINT fk_test_feature_id foreign key(feature_id) references features(id);

随着您的测试表像上面一样规范化,我将项目和特性分离到它自己的单独表中,这不仅仅是一个承载每个映射计数的映射表。

如果您现在启动了先前启动的SQL查询,但只做了如下所述的少量修改,那么您应该会看到SQL查询性能有了显著的/巨大的改进。

select t1.id, t2.id, sum( least( t1.count, t2.count )) as priority 
from test as t1 inner join test as t2 on t2.feature_id = t1.feature_id 
where t1.id = {some user supplied id value}
group by t1.id, t2.id 
order by priority desc
limit 500;

干杯!

一种优化是将项目本身从自联接中排除:

inner join test as t2 
     on t2.feature = t1.feature and t2.id <> t1.id
                                    ^^^^^^^^^^^^^^

要进一步加速,请在上创建覆盖索引 (feature, id, count) .

我会从这个开始…喜欢听到你正在看的表演。我认为你不需要最少的(T1和T2计数)。如果您首先根据id=某个值来限定Where,那么显然您将获得所有这些“特性”。然后通过一个只与匹配的“特性”相关的自连接,你就可以得到一个计数。由于您将其按ID1和ID2细分,因此每个“功能”都将被计数一次。在这个查询的最后,由于我没有明确地排除t2.id等于某个用户值,所以它的计数应该与T1中的功能计数完全相同,并且在它下面的任何内容都将是您的其他最接近的匹配项。

我会确保我有一个关于ID和特性的索引。

select STRAIGHT_JOIN
      t1.id,
      t2.id, 
      count(*) as MatchedInBoth
   from 
      test as t1,
      test as t2
   where 
          t1.id = {some user value}
      and t1.feature = t2.feature
   group by
      t1.id,
      t2.id
   order by 
      MatchedInBoth desc 
   limit 
      500; 

结果可能是

t1            t2           MatchedInBoth
{user value}  {user value} 275
{user value}  Other ID 1   270
{user value}  Other ID 2   241
{user value}  Other ID 3   218
{user value}  Other ID 4   197
{user value}  Other ID 5   163, etc

你能把它敲到只有一张桌子吗?使用子查询,您可能能够避免联接,如果子查询更快、索引并只执行一次,这将是一个胜利。像这样的东西(未经测试)。

选择
T2.ID,
和(t2.count)作为优先级
来自试验t2
其中t2.id=一些用户提供的id值和
t2.count>(从测试T1中选择min(count),其中id=一些用户提供的值)和
t2.feature in(从测试T1中选择feature,其中id=一些用户提供的值)
按T1.ID分组
按优先级排序描述
限值500;

如果这不起作用,MySQL很难意识到内部选择是常量表,并将为每一行重新执行它们。再次将它们包装在select中会强制进行常量表查找。这是一个黑客:

<代码> 选择BR/>
T1.ID,
和(t2.count)作为优先级
来自试验t2
其中t2.id=一些用户提供的id值和
t2.count>。(
选择*从(
从测试T1中选择min(count),其中id=some user supplied
value)as const)和
t2.功能输入(选择*从(
从测试T1中选择功能,其中id=some user supplied value
)as const)
按T1.ID分组
按优先级排序描述
限值500;
< /代码>