SQL查询优化-performance问题

Question

我有以下要优化的SQL查询：

select table1.tiers as col1, table1.id_item as col2 
from items table1 
where (table1.tiers is not null) 
  and table1.tiers<>''
  and table1.id_item = (select max(table2.id_item)
                        from items table2
                        where table1.tiers=table2.tiers) 
  and table1.n_version_item=(select max(table2.n_version_item) 
                             from items table2 
                             where table2.id_item=table1.id_item)

我试过这个：

select table1.tiers as col1, table1.id_item as col2 
from items table1 
where (table1.tiers is not null) 
  and table1.tiers<> '' 
  and CONCAT(table1.id_item,table1.n_version_item) =  (select CONCAT(max(table2.id_item),max(table2.n_version_item)) 
                                                       from items table2
                                                       where table2.id_item=table1.id_item 
                                                         and table1.tiers=table2.tiers)

但我不会得到同样的结果。原始的第一个查询比修改后的查询返回的行数少。请注意，表项有一个主键(id，version)，并且对于每一对，都会有一个层受到影响。

Answer 1

当使用函数时，它将阻止索引被使用，因此CONCAT(table1.id_item,table1.n_version_item)将不会读取索引，除非它的函数基于索引。但是，正如a_horse_with_no_name在评论中提到的，您可以使用以下代码：

select itm.tiers as col1, itm.id_item as col2 
from items itm
where itm.tiers is not null 
  and itm.tiers<>''
  and (itm.id_item , itm.n_version_item)= (select 
 max(item_sub.id_item),max(item_sub.n_version_item)
                        from items item_sub
                        where itm.tiers=item_sub.tiers) 

然后，您必须检查查询的查询计划使用了什么索引(您可以在id_item和n_version_item上以列tiers和其他索引作为索引的开头)

Answer 2

我认为你想要：

select i.tiers as col1, i.id_item as col2 
from items i 
where i.tiers is not null and  -- redundant, but I'm leaving it in
      i.tiers <> '' 
      (id_item, n_version_item) = (select i2.id_item, max(i2.n_version_item)
                                   from items i2
                                   where i2.tiers = i.tiers
                                   order by i2.id_item desc, i2.n_version_item desc
                                   limit 1
                                  );

对于这个版本，您需要一个关于items(tiers, id_item, n_version_item)的索引。

Answer 3

如果在“函数”(CONCAT、DATE等)中隐藏列，则不能使用索引来提高性能。这就排除了你的第二个版本。

与此相关的是"Row Constructors“的使用(参见a_horse_with_no_name的注释)。从历史上看，它们的优化效果很差；避免使用它们。我指的是WHERE (a,b) IN ( (1,2), ...)或其他变体。

现在，让我们剖析

  and table1.id_item = (select max(table2.id_item)
                    from items table2
                    where table1.tiers=table2.tiers) 

table2需要按此顺序使用INDEX(tiers, id_item)。这样，子查询就非常快了。另一个子查询需要将这些提要INDEX(id_item, n_version_item)到其余的子查询中：

  and table1.id_item = <<value>>

现在让我们来看一下整个

where (table1.tiers is not null) 
  and  table1.tiers<>''
  and  table1.id_item = <<value>>
  and  table1.n_version_item = <<value>>

=很容易优化，其他的就不容易了。所以让我们构建

INDEX(id_item, n_version_item,  -- in either order
      tiers)    -- last

通过使用我指定的顺序，您可以避免还需要上面提到的INDEX(id_item, n_version_item)。

(如果你提供了SHOW CREATE TABLE会有所帮助；我需要知道PK是什么，以及其他一些事情。)

作为奖励，这些索引将是“覆盖索引”。

作为最后一个注释(一个次要的注释)：

where (table1.tiers is not null) 
  and  table1.tiers<>''

对于您所表示的任何内容，最好只使用一种编码(NULL与空字符串)。