昭通做网站公司,北京手机网站制作多少钱,wordpress10款音乐插件,浙江招标信息网前言
最近我在公司优化过几个慢查询接口的性能#xff0c;总结了一些心得体会拿出来跟大家一起分享一下#xff0c;希望对你会有所帮助。
我们使用的数据库是Mysql8#xff0c;使用的存储引擎是Innodb。这次优化除了优化索引之外#xff0c;更多的是在优化count(*)。
通…前言
最近我在公司优化过几个慢查询接口的性能总结了一些心得体会拿出来跟大家一起分享一下希望对你会有所帮助。
我们使用的数据库是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中使用最多的存储引擎是innodb和myisam。
在myisam中会把总行数保存到磁盘上使用count(*)时只需要返回那个数据即可无需额外的计算所以执行效率很高。
而innodb则不同由于它支持事务有MVCC即多版本并发控制的存在在同一个时间点的不同事务中同一条查询sql返回的记录行数可能是不确定的。
在innodb使用count(*)时需要从存储引擎中一行行的读出数据然后累加起来所以执行效率很低。
如果表中数据量小还好一旦表中数据量很大innodb存储引擎使用count(*)统计数据时性能就会很差。
2 如何优化count(*)性能
从上面得知既然count(*)存在性能问题那么我们该如何优化呢
我们可以从以下几个方面着手。
2.1 增加redis缓存
对于简单的count(*)比如统计浏览总次数或者浏览总人数我们可以直接将接口使用redis缓存起来没必要实时统计。
当用户打开指定页面时在缓存中每次都设置成count count1即可。
用户第一次访问页面时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 {Overridepublic 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 status1;统计无效订单的sql如下
select count(*) from order where status0;但如果在一个接口中同步执行这两条sql效率会非常低。
这时候可以改成成一条sql
select count(*),status from order
group by status;使用group by关键字分组统计相同status的数量只会产生两条记录一条记录是有效订单数量另外一条记录是无效订单数量。
但有个问题status字段只有1和0两个值重复度很高区分度非常低不能走索引会全表扫描效率也不高。
还有其他的解决方案不
答使用多线程处理。
我们可以使用CompleteFuture使用两个线程异步调用统计有效订单的sql和统计无效订单的sql最后汇总数据这样能够提升查询接口的性能。
最近我建了新的技术交流群打算将它打造成高质量的活跃群欢迎小伙伴们加入。
我以往的技术群里技术氛围非常不错大佬很多。 加微信su_san_java备注加群即可加入该群。
2.4 减少join的表
大部分的情况下使用count(*)是为了实时统计总数量的。
但如果表本身的数据量不多但join的表太多也可能会影响count(*)的效率。
比如在查询商品信息时需要根据商品名称、单位、品牌、分类等信息查询数据。
这时候写一条sql可以查出想要的数据比如下面这样的
select count(*)
from product p
inner join unit u on p.unit_id u.id
inner join brand b on p.brand_id b.id
inner join category c on p.category_id c.id
where p.name测试商品 and u.id123 and b.id124 and c.id125;使用product表去join了unit、brand和category这三张表。
其实这些查询条件在product表中都能查询出数据没必要join额外的表。
我们可以把sql改成这样
select count(*)
from product
where name测试商品 and unit_id123 and brand_id124 and category_id125;在count(*)时只查product单表即可去掉多余的表join让查询效率可以提升不少。
2.5 改成ClickHouse
有些时候join的表实在太多没法去掉多余的join该怎么办呢
比如上面的例子中查询商品信息时需要根据商品名称、单位名称、品牌名称、分类名称等信息查询数据。
这时候根据product单表是没法查询出数据的必须要去joinunit、brand和category这三张表这时候该如何优化呢
答可以将数据保存到ClickHouse。
ClickHouse是基于列存储的数据库不支持事务查询性能非常高号称查询十几亿的数据能够秒级返回。
为了避免对业务代码的嵌入性可以使用Canal监听Mysql的binlog日志。当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 * 搞混了。 最后说一句(求关注别白嫖我)
如果这篇文章对您有所帮助或者有所启发的话帮忙扫描下发二维码关注一下您的支持是我坚持写作最大的动力。
求一键三连点赞、转发、在看。
关注公众号【苏三说技术】在公众号中回复面试、代码神器、开发手册、时间管理有超赞的粉丝福利另外回复加群可以跟很多BAT大厂的前辈交流和学习。
