一则分页查询的简单优化

产品经理大清早跑过来说后台还款查询很慢。
查看慢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

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

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值