写点什么

count(*) 查询性能很差?用这 5 招轻松优化

作者:小小怪下士
  • 2022-11-16
    湖南
  • 本文字数:3299 字

    阅读完需:约 11 分钟

前言

最近我在公司优化过几个慢查询接口的性能,总结了一些心得体会拿出来跟大家一起分享一下,希望对你会有所帮助。


我们使用的数据库是Mysql8,使用的存储引擎是Innodb。这次优化除了优化索引之外,更多的是在优化count(*)


通常情况下,分页接口一般会查询两次数据库,第一次是获取具体数据,第二次是获取总的记录行数,然后把结果整合之后,再返回。


查询具体数据的 sql,比如是这样的:`


select id,name from user limit 1,20;
复制代码


它没有性能问题。


但另外一条使用 count(*)查询总记录行数的 sql,例如:


select count(*) from user;
复制代码


却存在性能差的问题。


为什么会出现这种情况呢?

1 count(*)为什么性能差?

在 Mysql 中,count(*)的作用是统计表中记录的总行数。


count(*)的性能跟存储引擎有直接关系,并非所有的存储引擎,count(*)的性能都很差。


在 Mysql 中使用最多的存储引擎是:innodbmyisam


在 myisam 中会把总行数保存到磁盘上,使用 count(*)时,只需要返回那个数据即可,无需额外的计算,所以执行效率很高。


而 innodb 则不同,由于它支持事务,有MVCC(即多版本并发控制)的存在,在同一个时间点的不同事务中,同一条查询 sql,返回的记录行数可能是不确定的。


在 innodb 使用 count(*)时,需要从存储引擎中一行行的读出数据,然后累加起来,所以执行效率很低。


如果表中数据量小还好,一旦表中数据量很大,innodb 存储引擎使用 count(*)统计数据时,性能就会很差。

2 如何优化 count(*)性能?

从上面得知,既然count(*)存在性能问题,那么我们该如何优化呢?


我们可以从以下几个方面着手。

2.1 增加 redis 缓存

对于简单的 count(*),比如:统计浏览总次数或者浏览总人数,我们可以直接将接口使用 redis 缓存起来,没必要实时统计。


当用户打开指定页面时,在缓存中每次都设置成 count = count+1 即可。


用户第一次访问页面时,redis 中的 count 值设置成 1。用户以后每访问一次页面,都让 count 加 1,最后重新设置到 redis 中。



这样在需要展示数量的地方,从 redis 中查出 count 值返回即可。


该场景无需从数据埋点表中使用 count(*)实时统计数据,性能将会得到极大的提升。


不过在高并发的情况下,可能会存在缓存和数据库的数据不一致的问题。


但对于统计浏览总次数或者浏览总人数这种业务场景,对数据的准确性要求并不高,容忍数据不一致的情况存在。

2.2 加二级缓存

对于有些业务场景,新增数据很少,大部分是统计数量操作,而且查询条件很多。这时候使用传统的 count(*)实时统计数据,性能肯定不会好。


假如在页面中可以通过 id、name、状态、时间、来源等,一个或多个条件,统计品牌数量。


这种情况下用户的组合条件比较多,增加联合索引也没用,用户可以选择其中一个或者多个查询条件,有时候联合索引也会失效,只能尽量满足用户使用频率最高的条件增加索引。


也就是有些组合条件可以走索引,有些组合条件没法走索引,这些没法走索引的场景,该如何优化呢?


答:使用二级缓存


二级缓存其实就是内存缓存。


我们可以使用caffine或者guava实现二级缓存的功能。


目前SpringBoot已经集成了 caffine,使用起来非常方便。


只需在需要增加二级缓存的查询方法中,使用@Cacheable注解即可。


 @Cacheable(value = "brand", , keyGenerator = "cacheKeyGenerator")   public BrandModel getBrand(Condition condition) {       return getBrandByCondition(condition);   }
复制代码


然后自定义 cacheKeyGenerator,用于指定缓存的 key。


public class CacheKeyGenerator implements KeyGenerator {    @Override    public Object generate(Object target, Method method, Object... params) {        return target.getClass().getSimpleName() + UNDERLINE                + method.getName() + ","                + StringUtils.arrayToDelimitedString(params, ",");    }}
复制代码


这个 key 是由各个条件组合而成。


这样通过某个条件组合查询出品牌的数据之后,会把结果缓存到内存中,设置过期时间为 5 分钟。


后面用户在 5 分钟内,使用相同的条件,重新查询数据时,可以直接从二级缓存中查出数据,直接返回了。


这样能够极大的提示 count(*)的查询效率。


但是如果使用二级缓存,可能存在不同的服务器上,数据不一样的情况。我们需要根据实际业务场景来选择,没法适用于所有业务场景。

2.3 多线程执行

不知道你有没有做过这样的需求:统计有效订单有多少,无效订单有多少。


这种情况一般需要写两条 sql,统计有效订单的 sql 如下:


select count(*) from order where status=1;
复制代码


统计无效订单的 sql 如下:


select count(*) from order where status=0;
复制代码


但如果在一个接口中,同步执行这两条 sql 效率会非常低。


这时候,可以改成成一条 sql:


select count(*),status from ordergroup by status;
复制代码


使用group by关键字分组统计相同 status 的数量,只会产生两条记录,一条记录是有效订单数量,另外一条记录是无效订单数量。


但有个问题:status 字段只有 1 和 0 两个值,重复度很高,区分度非常低,不能走索引,会全表扫描,效率也不高。


还有其他的解决方案不?


答:使用多线程处理。


我们可以使用CompleteFuture使用两个线程异步调用统计有效订单的 sql 和统计无效订单的 sql,最后汇总数据,这样能够提升查询接口的性能。

2.4 减少 join 的表

大部分的情况下,使用 count(*)是为了实时统计总数量的。


但如果表本身的数据量不多,但 join 的表太多,也可能会影响 count(*)的效率。


比如在查询商品信息时,需要根据商品名称、单位、品牌、分类等信息查询数据。


这时候写一条 sql 可以查出想要的数据,比如下面这样的:


select count(*)from product pinner join unit u on p.unit_id = u.idinner join brand b on p.brand_id = b.idinner join category c on p.category_id = c.idwhere p.name='测试商品' and u.id=123 and b.id=124 and c.id=125;
复制代码


使用 product 表去join了 unit、brand 和 category 这三张表。


其实这些查询条件,在 product 表中都能查询出数据,没必要 join 额外的表。


我们可以把 sql 改成这样:


select count(*)from productwhere name='测试商品' and unit_id=123 and brand_id=124 and category_id=125;
复制代码


在 count(*)时只查 product 单表即可,去掉多余的表 join,让查询效率可以提升不少。

2.5 改成 ClickHouse

有些时候,join 的表实在太多,没法去掉多余的 join,该怎么办呢?


比如上面的例子中,查询商品信息时,需要根据商品名称、单位名称、品牌名称、分类名称等信息查询数据。


这时候根据 product 单表是没法查询出数据的,必须要去join:unit、brand 和 category 这三张表,这时候该如何优化呢?


答:可以将数据保存到ClickHouse


ClickHouse 是基于列存储的数据库,不支持事务,查询性能非常高,号称查询十几亿的数据,能够秒级返回。


为了避免对业务代码的嵌入性,可以使用Canal监听Mysqlbinlog日志。当 product 表有数据新增时,需要同时查询出单位、品牌和分类的数据,生成一个新的结果集,保存到 ClickHouse 当中。


查询数据时,从 ClickHouse 当中查询,这样使用 count(*)的查询效率能够提升 N 倍。


需要特别提醒一下:使用 ClickHouse 时,新增数据不要太频繁,尽量批量插入数据。


其实如果查询条件非常多,使用 ClickHouse 也不是特别合适,这时候可以改成ElasticSearch,不过它跟 Mysql 一样,存在深分页问题。

3 count 的各种用法性能对比

既然说到 count(*),就不能不说一下 count 家族的其他成员,比如:count(1)、count(id)、count(普通索引列)、count(未加索引列)。


那么它们有什么区别呢?


  • count(*) :它会获取所有行的数据,不做任何处理,行数加 1。

  • count(1):它会获取所有行的数据,每行固定值 1,也是行数加 1。

  • count(id):id 代表主键,它需要从所有行的数据中解析出 id 字段,其中 id 肯定都不为 NULL,行数加 1。

  • count(普通索引列):它需要从所有行的数据中解析出普通索引列,然后判断是否为 NULL,如果不是 NULL,则行数+1。

  • count(未加索引列):它会全表扫描获取所有数据,解析中未加索引列,然后判断是否为 NULL,如果不是 NULL,则行数+1。


由此,最后 count 的性能从高到低是:


count(*) ≈ count(1) > count(id) > count(普通索引列) > count(未加索引列)


所以,其实count(*)是最快的。


意不意外,惊不惊喜?


千万别跟select * 搞混了。

用户头像

还未添加个人签名 2022-09-04 加入

热衷于分享java技术,一起交流学习,探讨技术。 需要Java相关资料的可以+v:xiaoyanya_1

评论

发布
暂无评论
count(*)查询性能很差?用这5招轻松优化_Java_小小怪下士_InfoQ写作社区