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

当分页查询消息的时候，会查询符合条件的消息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。可见这个字段的区分度太低，故会造成多余的回表查询。

举个例子：

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

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

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

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

解决方式：

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

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

1、修改receiver_visible_roles字段类型

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

2、字段代表含义即存储

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

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

虽然用不到索引，但是这种方式的查询性能还是很好（估七墨博客计是位运算比较偏底层吧=.=）

4、查询方式

5、对应到JOOQ

.bitAnd()

6、进一步优化

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

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