mysql - 使用 where 子句的慢速计数查询

Question

我正在尝试执行计数以获得分页结果的总数，但查询速度太慢 2.12s

+-------+
| size  |
+-------+
| 50000 |
+-------+
1 row in set (2.12 sec)

我的计数查询

select  count(appeloffre0_.ID_APPEL_OFFRE) as size  
from  ao.appel_offre appeloffre0_  
inner join ao.acheteur acheteur1_ 
on appeloffre0_.ID_ACHETEUR=acheteur1_.ID_ACHETEUR 
where 
(exists (select 1 from ao.lot lot2_ where lot2_.ID_APPEL_OFFRE=appeloffre0_.ID_APPEL_OFFRE and lot2_.ESTIMATION_COUT>=1)) 
and (exists (select 1 from ao.lieu_execution lieuexecut3_ where lieuexecut3_.appel_offre=appeloffre0_.ID_APPEL_OFFRE and lieuexecut3_.region=1)) 
and (exists (select 1 from ao.ao_activite aoactivite4_ where aoactivite4_.ID_APPEL_OFFRE=appeloffre0_.ID_APPEL_OFFRE and (aoactivite4_.ID_ACTIVITE=1))) 
and appeloffre0_.DATE_OUVERTURE_PLIS>'2015-01-01' 
and (appeloffre0_.CATEGORIE='fournitures' or appeloffre0_.CATEGORIE='travaux' or appeloffre0_.CATEGORIE='services') 
and acheteur1_.ID_ENTITE_MERE=2

解释命令:

+----+--------------------+--------------+------+---------------------------------------------+--------------------+---------+--------------------------------+-------+--------------------------+
| id | select_type        | table        | type | possible_keys                               | key                | key_len | ref                            | rows  | Extra                    |
+----+--------------------+--------------+------+---------------------------------------------+--------------------+---------+--------------------------------+-------+--------------------------+
|  1 | PRIMARY            | acheteur1_   | ref  | PRIMARY,acheteur_ibfk_1                     | acheteur_ibfk_1    | 5       | const                          |     3 | Using where; Using index |
|  1 | PRIMARY            | appeloffre0_ | ref  | appel_offre_ibfk_2                          | appel_offre_ibfk_2 | 4       | ao.acheteur1_.ID_ACHETEUR      | 31061 | Using where              |
|  4 | DEPENDENT SUBQUERY | aoactivite4_ | ref  | ao_activites_activite_fk,ao_activites_ao_fk | ao_activites_ao_fk | 4       | ao.appeloffre0_.ID_APPEL_OFFRE |     3 | Using where              |
|  3 | DEPENDENT SUBQUERY | lieuexecut3_ | ref  | fk_ao_lieuex,fk_region_lieuex               | fk_ao_lieuex       | 4       | ao.appeloffre0_.ID_APPEL_OFFRE |     1 | Using where              |
|  2 | DEPENDENT SUBQUERY | lot2_        | ref  | FK_LOT_AO                                   | FK_LOT_AO          | 4       | ao.appeloffre0_.ID_APPEL_OFFRE |     5 | Using where              |
+----+--------------------+--------------+------+---------------------------------------------+--------------------+---------+--------------------------------+-------+--------------------------+

索引 acheteur_ibfk_1 是一个 FK 引用表 ENTITE_MERE 因为我在 where 子句中有 acheteur1_.ID_ENTITE_MERE=2。

Answer 1

您的 joins 可以有多个条件通过使用 ON condition1 AND condition2 等

SELECT COUNT(appeloffre0_.ID_APPEL_OFFRE) as size  
FROM  ao.appel_offre appeloffre0_  
JOIN ao.acheteur acheteur1_ ON appeloffre0_.ID_ACHETEUR=acheteur1_.ID_ACHETEUR 
JOIN ao.lot lot2_ ON appeloffre0_.ID_APPEL_OFFRE=lot2_.ID_APPEL_OFFRE AND lot2_.ESTIMATION_COUT>=1 
JOIN ao.lieu_execution lieuexecut3_ ON appeloffre0_.ID_APPEL_OFFRE=lieuexecut3_.ID_APPEL_OFFRE AND lieuexecut3_.ID_ACTIVITE=1
JOIN ao.ao_activite aoactivite4_ ON appeloffre0_.ID_APPEL_OFFRE=aoactivite4_.ID_APPEL_OFFRE AND aoactivite4_.ID_ACTIVITE=1
WHERE appeloffre0_.DATE_OUVERTURE_PLIS>'2015-01-01' 
AND (appeloffre0_.CATEGORIE='fournitures' OR appeloffre0_.CATEGORIE='travaux' OR appeloffre0_.CATEGORIE='services') 
AND acheteur1_.ID_ENTITE_MERE=2;