计数查询需要很多时间

本文介绍了计数查询需要很多时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的数据库 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)