mysql - 统计查询花费的时间

Question

我的数据库 mysql 中有 61K 行，我尝试进行计数，但查询需要 4 秒，我认为这太多了。

select count( distinct appeloffre0_.ID_APPEL_OFFRE) 
from ao.appel_offre appeloffre0_ 
inner join ao.lot lots1_ on appeloffre0_.ID_APPEL_OFFRE=lots1_.ID_APPEL_OFFRE 
inner join ao.lieu_execution lieuexecut2_ on appeloffre0_.ID_APPEL_OFFRE=lieuexecut2_.appel_offre 
inner join ao.acheteur acheteur3_ on appeloffre0_.ID_ACHETEUR=acheteur3_.ID_ACHETEUR 
inner join ao.ao_activite aoactivite4_ on appeloffre0_.ID_APPEL_OFFRE=aoactivite4_.ID_APPEL_OFFRE

我的查询结果:

+----------------------------------------------+
| count( distinct appeloffre0_.ID_APPEL_OFFRE) |
+----------------------------------------------+
|                                        61100 |
+----------------------------------------------+
1 row in set (4.35 sec)

为什么在explain cmd表appeloffre0_中使用键appel_offre_ibfk_2，它是FK列ID_ACHETEUR上的索引？

+----+-------------+--------------+--------+----------------------------+--------------------+---------+--------------------------------+-------+--------------------------+
| id | select_type | table        | type   | possible_keys              | key                | key_len | ref                            | rows  | Extra                    |
+----+-------------+--------------+--------+----------------------------+--------------------+---------+--------------------------------+-------+--------------------------+
|  1 | SIMPLE      | appeloffre0_ | index  | PRIMARY,appel_offre_ibfk_2 | appel_offre_ibfk_2 | 4       | NULL                           | 60031 | Using index              |
|  1 | SIMPLE      | acheteur3_   | eq_ref | PRIMARY                    | PRIMARY            | 4       | ao.appeloffre0_.ID_ACHETEUR    |     1 | Using index              |
|  1 | SIMPLE      | lieuexecut2_ | ref    | fk_ao_lieuex               | fk_ao_lieuex       | 4       | ao.appeloffre0_.ID_APPEL_OFFRE |     1 | Using index              |
|  1 | SIMPLE      | aoactivite4_ | ref    | ao_activites_ao_fk         | ao_activites_ao_fk | 4       | ao.lieuexecut2_.appel_offre    |     3 | Using where; Using index |
|  1 | SIMPLE      | lots1_       | ref    | FK_LOT_AO                  | FK_LOT_AO          | 4       | ao.lieuexecut2_.appel_offre    |     5 | Using where; Using index |
+----+-------------+--------------+--------+----------------------------+--------------------+---------+--------------------------------+-------+--------------------------+
5 rows in set (0.00 sec)

显示来自appel_offre的索引

+-------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name           | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| appel_offre |          0 | PRIMARY            |            1 | ID_APPEL_OFFRE      | A         |       60953 |     NULL | NULL   |      | BTREE      |         |               |
| appel_offre |          1 | appel_offre_ibfk_1 |            1 | ID_APPEL_OFFRE_MERE | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| appel_offre |          1 | appel_offre_ibfk_2 |            1 | ID_ACHETEUR         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| appel_offre |          1 | appel_offre_ibfk_3 |            1 | USER_SAISIE         | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| appel_offre |          1 | appel_offre_ibfk_4 |            1 | USER_VALIDATION     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| appel_offre |          1 | ao_fk_3            |            1 | TYPE_MARCHE         | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| appel_offre |          1 | ao_fk_5            |            1 | USER_CONTROLE       | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.03 sec)

1. 如何使用索引来运行快速计数查询？

2. 当我们有多个连接时如何使用索引？

3. 当我们有多个连接和多个搜索时如何使用索引查询？

Answer 1

不要在计数中使用 distinct，而是尝试用 exists 条件替换 1 到多个内部联接。

例如，如果 lot.ID_APPEL_OFFRE 不唯一，则删除内部联接

inner join ao.lot lots1_ on appeloffre0_.ID_APPEL_OFFRE=lots1_.ID_APPEL_OFFRE 

并在 where 子句中添加一个存在条件

where exists (select 1 from ao.lot lots_1 where appeloffre0_.ID_APPEL_OFFRE=lots1_.ID_APPEL_OFFRE)