具有多个子查询优化的mysql查询

Question

考虑到 analisi_campo_free 是一个大约有 100 万行的表，有没有更简单的方法来为 mysql 编写此查询？

    select distinct ID_ANALISI from analisi_campo_free 
    where 1=1
    and ID_ANALISI IN ( select ID_ANALISI from analisi_campo_free acf inner join campo_free cf on acf.id_campo_free = cf.id_campo_free where posizione = 1 and valore = '06/11/2015')
    and ID_ANALISI IN ( select ID_ANALISI from analisi_campo_free acf inner join campo_free cf on acf.id_campo_free = cf.id_campo_free where posizione = 3 AND valore='vvvvvv');

谢谢

---

查询

select distinct ID_ANALISI from analisi_campo_free 
where 1=1
and ID_ANALISI IN ( select ID_ANALISI from analisi_campo_free acf inner join campo_free cf on acf.id_campo_free = cf.id_campo_free where posizione = 1 and valore like 'irene%') 
and ID_ANALISI IN ( select ID_ANALISI from analisi_campo_free acf inner join campo_free cf on acf.id_campo_free = cf.id_campo_free where posizione = 3 AND valore like 'antonio%');

这个解释需要大约 2.3 秒

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  cf      index   PRIMARY ID_LABORATORIO  4       6   16.67   Using where; Using index; Using temporary; Start temporary
1   SIMPLE  cf      index   PRIMARY ID_LABORATORIO  4       6   16.67   Using where; Using index; Using join buffer (Block Nested Loop)
1   SIMPLE  acf     ALL ID_ANALISI,FK_ANALISI_CAMPO_FREE_CAMPO_FREE             1054163 11.11   Using where; Using join buffer (Block Nested Loop)
1   SIMPLE  acf     eq_ref  ID_ANALISI,FK_ANALISI_CAMPO_FREE_CAMPO_FREE ID_ANALISI  5   elettroforesi.acf.ID_ANALISI,elettroforesi.cf.ID_CAMPO_FREE 1   11.11   Using where
1   SIMPLE  analisi_campo_free      ref ID_ANALISI  ID_ANALISI  4   elettroforesi.acf.ID_ANALISI    1   100 Using index; End temporary

查询

SELECT acf.ID_ANALISI, COUNT(DISTINCT cf.posizione, acf.valore) AS matches 
FROM analisi_campo_free AS acf 
INNER JOIN campo_free AS cf ON acf.id_campo_free = cf.id_campo_free 
WHERE (posizione = 1 AND valore like 'irene%')
   OR (posizione = 3 AND valore like 'antonio%')
GROUP BY acf.ID_ANALISI
HAVING matches = 2; 

这个解释需要大约 1.3 秒

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  cf      index   PRIMARY ID_LABORATORIO  4       6   30.56   Using where; Using index; Using temporary; Using filesort
1   SIMPLE  acf     ALL ID_ANALISI,FK_ANALISI_CAMPO_FREE_CAMPO_FREE             1054163 20.99   Range checked for each record (index map: 0x6)

索引是:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
analisi_campo_free  0   PRIMARY 1   ID_ANALISI_CAMPO_FREE   A   1054159             BTREE       
analisi_campo_free  0   ID_ANALISI  1   ID_ANALISI  A   1049622             BTREE       
analisi_campo_free  0   ID_ANALISI  2   ID_CAMPO_FREE   A   1049622             BTREE       
analisi_campo_free  1   FK_ANALISI_CAMPO_FREE_CAMPO_FREE    1   ID_CAMPO_FREE   A   1               BTREE       


Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
campo_free  0   PRIMARY 1   ID_CAMPO_FREE   A   6               BTREE       
campo_free  0   DESCRIZIONE 1   DESCRIZIONE A   6               BTREE       
campo_free  0   ID_LABORATORIO  1   ID_LABORATORIO  A   1               BTREE       
campo_free  0   ID_LABORATORIO  2   POSIZIONE   A   6               BTREE       
campo_free  0   ID_LABORATORIO  3   UTILIZZATO  A   6           YES BTREE       

我没有考虑过对情侣的询问，因为现在我知道我需要一个赞。最后也许我会使用我的第一个查询，因为在 querydsl 中翻译起来更简单。感谢您的帮助

Answer 1

WHERE 子句中的子查询在 MySQL 中通常表现不佳，尤其是 CORRELATED 子查询。这会找到关联的 campo_free 记录并计算符合条件的不同 posizone、valore 对。 HAVING 确保结果中只有同时具有两者的 ID_ANALISI。

SELECT acf.ID_ANALISI, COUNT(DISTINCT cf.posizone, cf.valore) AS matches 
FROM analisi_campo_free AS acf 
INNER JOIN campo_free AS cf ON acf.id_campo_free = cf.id_campo_free 
WHERE (posizione = 1 AND valore = '06/11/2015')
   OR (posizione = 3 AND valore='vvvvvv');
GROUP BY acf.ID_ANALISI
HAVING matches = 2
;

在 COUNT 中没有 DISTINCT；如果 posizone, valore 对对于每个 id_campo_free 值不是唯一的，则具有两个 (1,'6/11/2015') 或 (3,'vvvvvv') 匹配项的 ID_ANALISI 也可以包含在最终结果中。