计数查询需要很多时间 [英] Count query take much time
问题描述
我的数据库 mysql 中有 61K 行，我尝试进行计数，但查询需要 4 秒，我认为它太多了.
I have i have 61K rows in my database mysql and I try to make a count but the query take 4 seconds and i think it's too much.
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 上的索引?
why in explain cmd table appeloffre0_ use key appel_offre_ibfk_2 which is a index on FK column 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 的索引
show index from 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)
如何使用索引来运行快速计数查询?
how can i use index to run fast count query ?
当我们有多个连接时如何使用索引?
how to use index when we have multiple join ?
当我们有多个连接和多个搜索时如何使用索引查询?
how to use index when we have multiple join and multiple search query ?
推荐答案
不要在计数中使用 distinct 尝试用 exists 条件替换 1 到多个内部连接.
Instead of using distinct in your count try replacing your 1 to many inner joins with exists conditions.
例如，如果 lot.ID_APPEL_OFFRE 不是唯一的，则移除内连接
For example, if lot.ID_APPEL_OFFRE is not unique, then remove the inner join
inner join ao.lot lots1_ on appeloffre0_.ID_APPEL_OFFRE=lots1_.ID_APPEL_OFFRE
并在您的 where 子句中添加一个存在条件
and add an exists condition in your where clause
where exists (select 1 from ao.lot lots_1 where appeloffre0_.ID_APPEL_OFFRE=lots1_.ID_APPEL_OFFRE)
这篇关于计数查询需要很多时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
