⭐⭐⭐ Spring Boot 项目实战	⭐⭐⭐ Spring Cloud 项目实战
《Dubbo 实现原理与源码解析 —— 精品合集》	《Netty 实现原理与源码解析 —— 精品合集》
《Spring 实现原理与源码解析 —— 精品合集》	《MyBatis 实现原理与源码解析 —— 精品合集》
《Spring MVC 实现原理与源码解析 —— 精品合集》	《数据库实体设计合集》
《Spring Boot 实现原理与源码解析 —— 精品合集》	《Java 面试题 + Java 学习指南》

摘要: 原创出处苏三说技术「苏三」欢迎转载，保留摘要，谢谢！

🙂🙂🙂关注微信公众号：【芋道源码】有福利：

RocketMQ / MyCAT / Sharding-JDBC 所有源码分析文章列表

RocketMQ / MyCAT / Sharding-JDBC 中文注释源码 GitHub 地址

您对于源码的疑问每条留言都将得到认真回复。甚至不知道如何读源码也可以请教噢。

新的源码解析文章实时收到通知。每周更新一篇左右。

认真的源码交流微信群。

前言

最近有个小伙伴问了这样一个问题：百万商品分页查询接口，如何保证接口的性能？

这就需要对该分页查询接口做优化了。

这篇文章从9个方面跟大家一起聊聊分页查询接口优化的一些小技巧，希望对你会有所帮助。

1 增加默认条件

对于分页查询接口，如果没有特殊要求，我们可以在输入参数中，给一些默认值。

这样可以缩小数据范围，避免每次都count所有数据的情况。

对于商品查询，这种业务场景，我们可以默认查询当天上架状态的商品列表。

例如：

select * from product 
where edit_date>='2023-02-20' and edit_date<'2023-02-21' and status=1

如果每天有变更的商品数量不多，通过这两个默认条件，就能过滤掉绝大部分数据，让分页查询接口的性能提升不少。

温馨提醒一下：记得给时间和状态字段增加一个联合索引。

2 减少每页大小

分页查询接口通常情况下，需要接收两个参数：pageNo（即：页码）和pageSize（即：每页大小）。

如果分页查询接口的调用端，没有传pageNo默认值是1，如果没有传pageSize也可以给一个默认值10或者20。

不太建议pageSize传入过大的值，会直接影响接口性能。

在前端有个下拉控件，可以选择每页的大小，选择范围是：10、20、50、100。

前端默认选择的每页大小为10。

不过在实际业务场景中，要根据产品需求而且，这里只是一个参考值。

3 减少join表的数量

有时候，我们的分页查询接口的查询结果，需要join多张表才能查出数据。

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

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

select 
  p.id,
  p.product_name,
  u.unit_name,
  b.brand_name,
  c.category_name
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='测试商品' 
limit 0,20;

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

其实product表中有unit_id、brand_id和category_id三个字段。

我们可以先查出这三个字段，获取分页的数据缩小范围，之后再通过主键id集合去查询额外的数据。

我们可以把sql改成这样：

select 
  p.id,
  p.product_id,
  u.unit_id,
  b.brand_id,
  c.category_id
from product
where name='测试商品'
limit 0,20;

这个例子中，分页查询之后，我们获取到的商品列表其实只要20条数据。

再根据20条数据中的id集合，获取其他的名称，例如：

select id,name 
from unit
where id in (1,2,3);

然后在程序中填充其他名称。

伪代码如下：

List<Product> productList = productMapper.search(searchEntity);
List<Long> unitIdList = productList.stream().map(Product::getUnitId).distinct().collect(Collectors.toList());
List<Unit> unitList = UnitMapper.queryUnitByIdList(unitIdList);
for(Product product: productList) {
   Optional<Unit> optional = unitList.stream().filter(x->x.getId().equals(product.getId())).findAny();
   if(optional.isPersent()) {
      product.setUnitName(optional.get().getName());
   } 
}

这样就能有效的减少join表的数量，可以一定的程度上优化查询接口的性能。

4 优化索引

分页查询接口性能出现了问题，最直接最快速的优化办法是：优化索引。

因为优化索引不需要修改代码，只需回归测试一下就行，改动成本是最小的。

我们需要使用explain关键字，查询一下生产环境分页查询接口的执行计划。

看看有没有创建索引，创建的索引是否合理，或者索引失效了没。

索引不是创建越多越好，也不是创建越少越好，我们需要根据实际情况，到生产环境测试一下sql的耗时情况，然后决定如何创建或优化索引。

建议优先创建联合索引。

5 用straight_join

有时候我们的业务场景很复杂，有很多查询sql，需要创建多个索引。

在分页查询接口中根据不同的输入参数，最终的查询sql语句，MySQL根据一定的抽样算法，却选择了不同的索引。

不知道你有没有遇到过，某个查询接口，原本性能是没问题的，但一旦输入某些参数，接口响应时间就非常长。

这时候如果你此时用explain关键字，查看该查询sql执行计划，会发现现在走的索引，跟之前不一样，并且驱动表也不一样。

之前一直都是用表a驱动表b，走的索引c。

此时用的表b驱动表a，走的索引d。

为了解决Mysql选错索引的问题，最常见的手段是使用force_index关键字，在代码中指定走的索引名称。

但如果在代码中硬编码了，后面一旦索引名称修改了，或者索引被删除了，程序可能会直接报错。

这时该怎么办呢？

答：我们可以使用straight_join代替inner join。

straight_join会告诉Mysql用左边的表驱动右边的表，能改表优化器对于联表查询的执行顺序。

之前的查询sql如下：

select p.id from product p
inner join warehouse w on p.id=w.product_id;
...

我们用它将之前的查询sql进行优化：

select p.id from product p
straight_join warehouse w on p.id=w.product_id;
...

6 数据归档

随着时间的推移，我们的系统用户越来越多，产生的数据也越来越多。

单表已经到达了几千万。

这时候分页查询接口性能急剧下降，我们不能不做分表处理了。

做简单的分表策略是将历史数据归档，比如：在主表中只保留最近三个月的数据，三个月前的数据，保证到历史表中。

我们的分页查询接口，默认从主表中查询数据，可以将数据范围缩小很多。

如果有特殊的需求，再从历史表中查询数据，最近三个月的数据，是用户关注度最高的数据。

7 使用count(*)

在分页查询接口中，需要在sql中使用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(*)是最快的。

我们在使用count统计总记录数时，一定要记得使用count(*)。

8 从ClickHouse查询

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

答：可以将数据保存到ClickHouse。

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

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

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

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

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

9 数据库读写分离

有时候，分页查询接口性能差，是因为用户并发量上来了。

在系统的初期，还没有多少用户量，读数据请求和写数据请求，都是访问的同一个数据库，该方式实现起来简单、成本低。

刚开始分页查询接口性能没啥问题。

但随着用户量的增长，用户的读数据请求和写数据请求都明显增多。

我们都知道数据库连接有限，一般是配置的空闲连接数是100-1000之间。如果多余1000的请求，就只能等待，就可能会出现接口超时的情况。

因此，我们有必要做数据库的读写分离。写数据请求访问主库，读数据请求访问从库，从库的数据通过binlog从主库同步过来。

根据不同的用户量，可以做一主一从，一主两从，或一主多从。

数据库读写分离之后，能够提升查询接口的性能。

芋道源码 —— 纯源码解析博客

分页查询接口，从2s优化到了0.01s！

前言