分库分表分页查询：为什么你的查询又慢又耗费内存？
September 3, 2023
你们公司是怎么解决分页查询的？平均性能如何？ #
我司业务是toB的，所以我们的分表主要有两种形式：按企业id分表、按时间分表。我们的做法是：让查询在单表中进行，避免跨表分页。
如果是按企业id分表的，我们不会支持跨企业查询，也没有这个需求。
在按时间分表时，限定每次查询的记录在一张表中。比如，我们的表是按月分表的，那么我们的查询条件中，会限定在某个月的某个时间段内，这样就可以保证查询的记录在一张表中。
需要全局查询统计信息的，我们会交给大数据来进行，这样可以避免对数据库的压力。大数据订阅binlog，将数据同步到大数据的数据库中然后进行统计。
深度分页时，性能很差。我们会限制分页查询的深度。
为什么分页查询那么慢？ #
- 网络性能，跨表分页查询时，可能查询的数据量远远超出了一次查询的数据量，会增加网络传输的压力。
- cpu、内存问题。排序是一个cpu密集型操作，如果需要进行二次排序，则会给cpu带来很大压力。同时，需要排序的数据量可能远远超出 分页的数量，会给内存带来压力。
全局查询有什么优缺点？对于一个查询 LIMIT X OFFSET Y 来说，如果我命中了三张表，会取来多少数据？ #
全局查询，会增加查询的复杂度，降低查询的性能。
如果是平均分布的，那会取来3X条数据。但是如果查询条件比较复杂，你不能保证在每一个查询条件下，数据都能平均分布到三张表中。
怎么提高分页查询的速度？ #
如果是按排序的范围分页（比如时间）如果是上下滚动分页，或者是可以接受只有上一页、下一页，没有跳转分页，那么就可以是用游标分页的方式，即每一次记住上一次查询的最后一条记录的索引/主键信息，查询下一页时，直接使用这个索引/主键信息来查询。
如果是按哈希分库分表，要进行跨表分页，最好是使用中间表的方式来进行分页查询，将where和order by条件都放到中间表中，然后再查询中间表，这样可以避免跨表查询。
什么是二次查询？它的步骤是什么样的？ #
分库分表场景下的分页查询中的二次查询，是指在第一次查询的基础上，再次查询，来获取分页的数据：
- 首次查询，拿到最小值。
- 二次查询，确认最小值的全局偏移量。
- 在二次查询的结果里根据最小值取到符合偏移量的数据。
怎么在二次查询里面计算全局的偏移量？ #
个人观点：这里搞得有点复杂，不如采用用中间表的方式。
二次查询有什么优缺点？ #
优点：
- 不需要引入中间表或其它中间件。 缺点:
- 性能较差。因为需要二次查询，所以会增加查询的复杂度，降低查询的性能。
代理形态的分库分表中间件有什么优缺点？怎么解决或者改进它的缺点？ #
性能较差。因为所有的数据库查询都要经过中间件，最终结果是中间件的性能决定了整个系统的性能。 目前没有特别好的解决方案。
使用中间表来进行分库分表，有什么优缺点？怎么设计中间表？ #
引入中间表的优点：
- 可以避免跨表查询。
- 可以避免查询过多的数据，减少网络传输的压力。
- 可以避免二次排序，减少cpu和内存的压力。
缺点：
- 需要额外的存储空间。
- 排序字段必须在中间表中。实际上，最好是where条件和order by条件都在中间表中。
- 如果中间表和业务表/原表的写入没有使用事务，可能会导致数据不一致。
在使用中间表的时候，你怎么保证数据一致性？你能保证强一致吗？如果不能，不一致的时间最差是多久？ #
- 使用事务来保证数据一致性。这样做的话，可以保证强一致性，但是会影响性能。
- 订阅binlog，将数据同步到中间表中。这样做的话，不会影响性能，但是可能会导致数据不一致。不一致的时间，取决于binlog的同步延迟。
你们公司有没有考虑使用别的中间件来解决分页查询？你选择哪一个？为什么？ #
我们也有使用es和clickhouse来提供查询，我们使用es主要用来做模糊搜索，使用clickhouse主要用来做统计查询。
但是，实际上上我司分库分表的数据表进行查询时，有以下几个特点：
- 查询列表数据，主要是按企业id和时间来查询，没有跨企业查询的需求。
- 查询统计数据时，会跨企业查询，但是一般会交给大数据来做。