sql的count函数优化

sql的count(1)函数会执行遍历表统计符合条件的数目，下面有两个sql

第一条：

select count(1)
from membership_call_detail_statistics a
where a.repository_id = 'f2a4ed6b3e074e33bd99998c1def26f8'
and a.statistics_date 
between '2023-04-01 00:00:00' and '2023-07-03 00:00:00'

查询平均耗时500毫秒左右

第二条

select count(1)
from membership_call_detail_statistics a
where a.repository_id = 'f2a4ed6b3e074e33bd99998c1def26f8'
and a.id 
between (select min(id) from membership_call_detail_statistics where statistics_date = '2023-04-01 00:00:00')
and     (select max(id) from membership_call_detail_statistics where statistics_date = '2023-07-03 00:00:00')

查询平均耗时50毫秒左右

第二条是第一条的效率10倍，原因是第一条的repository_id ='f2a4ed6b3e074e33bd99998c1def26f8'，走的是date索引，需要回表查询；而第二条走的是id主键索引，不需要回表查询。所以对于表操作尽量走主键索引，不会回表。

chatgpt体验：http://me.chat129.com

chatgpt学习：http://www.chat129.com​​​​​​​