Java 开发

多种状态字段的查询性能优化案例

言七墨 · 3月27日 · 2020年 · · 221次已读

前置问题

当分页查询消息的时候,会查询符合条件的消息total数量,这个操作在查询消息数量比较多的时候,很容易出现timeout(从库查询一万多条消息,count 一下,需要90多秒),经查证,是现有receiver_visible_roles字段导致的:

receiver_visible_roles存储:目前这个字段是存了5种角色的可见性,1代表可见,0代表不可见,从高位到低位依次代表:E、D、C、B、A。假如A、B可见,其余角色不可见,则存储00011

receiver_visible_roles查询:假如查A、B可见,其余角色没有限制,则需要 in (00011, 00111, 01011, 01111, 10011, 10111, 11011, 11111),虽然该字段建有索引,并用到了索引,但还是很慢。另,假如再新增角色,查询性能会更慢…

解决方法一(建立组合索引)

由于只在receiver_visible_roles字段上建立了一个单独索引,目前这张表的这个字段存的基本都是A、B可见,其余角色均不可见,即00011。可见这个字段的区分度太低,故会造成多余的回表查询。

举个例子:

假如有一个查询语句要查询性别为男生的数据,因为这样的数据很多,我们要扫描很多次索引,然后再去取这个性别为男的数据。那么分为两部分,先扫描索引,然后再回表取这个符合要求的数据。如果我们不建立索引,那么就要扫描整张表。

  • 不建立索引需要的时间 = 扫描整张表
  • 建立索引需要的时间 = 扫描大量索引 + 回表扫描大量数据 

考虑一种极限情况,假如性别全为男,那么我们建立索引去查找的时间就是“扫描整个索引表 + 扫描整张表的总时间”,那么肯定特别耗时。

故通过以上分析,可以得出结论:给区分度不高的字段建立单独索引,性能反而还不如不建索引。

解决方式:

可以将主要查询字段idreceiver_visible_roles建立一个组合索引,此时索引就可以发挥真正的作用了。

但是,这种解决方式还有一个缺点,考虑后面业务的扩展,当新增角色时,进行in查询操作,组装的可能性将成指数级增长。此时,性能还是会受到影响,查询复杂度也会变得更高。

解决方法二(位运算解决)

1、修改receiver_visible_roles字段类型

alter table xxx.message modify receiver_visible_roles int(11) not null comment '接收者可见性';

2、字段代表含义即存储

角色表示可见性含义
1A可见
2B可见
4C可见
8D可见
16E可见
2^5待定角色可见

3、先看下从库的查询性能测试(随便找了个字段进行极端查询场景下的性能测试,在主库上查询应该更快)

本次通过消息表中的 status 字段测试,测试结果如下:

总total耗时查询语句筛选到total
20061.2秒select count(*) from xxx.message where root_id = 8391533 and status & 2 = 2;857
50111.5秒select count(*) from xxx.message where root_id = 4036467 and status & 4 = 4;2573
100373.6秒select count(*) from xxx.message where root_id = 5478255 and status & 4 = 4;3433
167715.8秒select count(*) from xxx.message where root_id = 1253643 and status & 2 = 2;7896
204566秒select count(*) from xxx.message where root_id = 2811939 and status & 2 = 2;8291
5820720秒select count(*) from xxx.message where root_id = 1550392 and status & 4 = 4;32619
13707741秒select count(*) from xxx.message where root_id = 1368106 and status & 4 = 4;69724

虽然用不到索引,但是这种方式的查询性能还是很好(估计是位运算比较偏底层吧=.=)

4、查询方式

需求查询方式
A可见,其它角色无所谓where receiver_visible_roles & 1 = 1
A、B均可见,其它角色无所谓where receiver_visible_roles & 3 = 3
B不可见where receiver_visible_roles & 2 = 0
假如都不可见where receiver_visible_roles & 31 = 0

5、对应到JOOQ

.bitAnd()

6、进一步优化

由于分页查询的耗时主要是因为count查询,故做进一步优化:

  • 重写工具 Jar 包中的Pagination类,保留total字段,删除count查询逻辑,增加hasNext字段;
  • hasNext逻辑实现:hasNext = content.size() == size,从而避免count查询;
  • 业务方通过hasNext字段判断是否遍历到最后一页;
  • 缺点:如果最后一页数据的size刚好等于页的size时,会多一次无用的分页查询;
  • 为了减少发布过程中对线上业务的影响,可以给total设置一个合适的默认值。
0 条回应