一则分页查询的简单优化

产品经理大清早跑过来说后台还款查询很慢。
查看慢sql发现慢在count(*)上。
数据库为Mysql，可以简化为如下查询:

---

一张用户表，一张还款记录表。查询某个用户的还款记录

SELECT
    count(*)
FROM
    repay_detail repay
INNER JOIN account a ON (repay.login_id = a.login_id)
WHERE
    a. NAME = '张三'
AND repay.repay_time = '2017-11-19'

由于要分页，使用了一款非常流行的Mybatis分页插件 PageHelper
每次点击分页按钮都要统计一下总数，用户表跟还款记录表很大，又涉及表之间的级联，尽管都加了索引，count(*)依然很慢。
该插件分页实现如下：

通过创建一个拦截器，在sql里面实现统计总数跟分页
com.github.pagehelper.PageHelper.startPage(int pageNum, int pageSize)
先通过count(*)计算总数，总数返回给前端用，再通过 limit 分页。
一次请求，插件最终生成了两条sql:
1)select count(*)
2)select * from

由于这只是一个内部系统，查询的都是静态数据，也就管理员一个人用用，不讲究实时性，不涉及分布式、高并发，那么相同的查询条件就没必要每次都统计总条数，只需要第一次请求的时候知道条目总数就够了。
首先把记录总数缓存起来。（实现有点复杂，不建议这样用，可以跳过这一步，直接从sql入手。）

      // 首先计算每次请求的hashCode（都是字符串），将相同查询条件的总数缓存
      // 第一次请求才计算总数: select count(*) from table
      PageHelper.startPage(1, 10);//添加拦截器，统计总条数
      // 然后把请求对应的总数缓存到ThreadLocal里面
      ThreadLocal<Integer> totalCountRecord; //根据查询条件来缓存总数
      totalCountRecord.set(total); // 记录条目总数
      // 当发现还是相同的请求，就直接从ThreadLocal缓存里面拿总数
      // false就是关掉 select count(*) from table统计，不再统计
      PageHelper.startPage(1, 10,false);

由于分页功能的存在，每次只返回10条记录。
经过测试，在查询结果很少的情况下拆成单独的sql反而更快。
去掉inner join，如下语句

1) 先统计
SELECT
    count(*)
FROM
    repay_detail repay
INNER JOIN account a ON (repay.login_id = a.login_id)
WHERE
    a. NAME = '张三'
AND repay.repay_time = '2017-11-19'
2) 再分页
SELECT
    a. NAME,
    repay.amount
FROM
    repay_detail repay
INNER JOIN account a ON (repay.login_id = a.login_id)
WHERE
    a. NAME = '张三'
AND repay.repay_time = '2017-11-19'
LIMIT 10

变为

1) 统计分拆
先在java里面根据用户名取得用户id，由于生产环境里面name一般是手机号，可以作为唯一键，只查询一条记录很快。
select login_id from account where NAME = '张三'
2) 转换为对一个表的统计
再把用户id传给分页插件，变成了对一个表的统计，速度很快
SELECT
    count(*)
FROM
    repay_detail repay
WHERE repay.login_id = 123 and repay.repay_time = '2017-11-19'
3) 结果分拆
接下来就是分页取数据，每次给前端返回10条。
考虑一种复杂情况，根据还款时间，一下子查询到了10个用户。
即便这样，由于只有10条数据，在java里面根据从还款表里面得到的用户id到account表里取用户名，速度飞快。
也可以直接写在子查询里面，实现更简单。
这种方法只适用于一次性返回数据量少的情况。
SELECT
  (select a.anme from account a where a.login_id = repay.login_id) as name,
    repay.amount 
FROM
    repay_detail repay
WHERE repay.repay_time = '2017-11-19' limit 10

最终实现对一个表的统计与查询，速度快了很多。