MySQL-count(*) 和 not in 的查询优化

小小工匠

发布于 2021-08-17 11:15:56

1.2K0

文章被收录于专栏：小工匠聊架构小工匠聊架构

官方文档

https://dev.mysql.com/doc/

如果英文不好的话，可以参考 searchdoc 翻译的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html

优化的原因

MySQL-Btree索引和Hash索引初探中什么情况下会使用到B树索引。

not int 和 <> 操作无法使用索引

not in 的优化

如果not in 的指标范围非常大的话，这个效率很差。

举个例子

select customer_id ,first_name ,last_name ,email 
from customer 
where customer_id 
not in (select customer_id from payment);

每个customer_id都要到payment中查询一遍，数据量大时很慢。

优化后 -----------> left join

select customer_id ,first_name ,last_name ,email 
from customer  a 
left join payment b 
on a.customer_id = b.customer_id 
where b.customer_id  is null

这样的话，可以避免对payment表的多次查询。

使用汇总表优化count(*)查询

select count(*) from product_comment where product_id = 999;

如果这个表有上亿条，或者并发访问很高的情况，这个SQL的执行效果也不是很理想

优化思路：就是使用汇总表

汇总表就是提前统计出来数据，记录到表中以备后续的查询使用。

Step1: 建立汇总表

字段看自己的需求，基本的有下面两列

create table product_comment_cnt(product_id int , cnt int);

然后每天定时的汇总，更新改表，对于当天新增的未统计到的数据，可以单独查询，然后累加

新的SQL如下

select sum(cnt) from (
  # 汇总表中查询到的由定时任务更新的数据 
   select cnt from product_comment_cnt where product_id = 999
 
    union all 
  
   #  新增的数据 
    select count(*) from product_comment where product_id = 999 
     and timestr > date(now())
) a

提供思路，实际情况自行调整。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020/02/03 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql