周梦康 发表于 2021-08-23 96 次浏览 标签 : MysqlJava

昨天看到的数据有很多慢 sql ,一看发现是一个不到 100 万行表的翻页问题。因为原来开发的同学使用了 PageHelper 的插件,开发的时候确实方便了那么一点点,但是随着时间的推移,数据量的增多,问题就慢慢凸显出来了。

原始的 sql 是这样的

SELECT b.id,
       b.gmt_create,
       b.gmt_modified,
       b.is_delete,
       b.title,
       a.x
       a.status,
       a.app_code
FROM a
  LEFT JOIN b ON a.`a_id`= b.id
 WHERE a.app_code= 'x'
   AND a.status= 0
  ORDER BY a.`a_id` DESC

使用PageHelper就不需要显性的增加offsetlimit参数了,并且会默认做count(*)的查询,也就是说会默认执行如下 sql 获取总数

SELECT count(*) 
FROM a
  LEFT JOIN b ON a.`a_id`= b.id
 WHERE a.app_code= 'x'
   AND a.status= 0

实际我们只需要执行如下 sql 即可

SELECT count(*) 
FROM a
 WHERE a.app_code= 'x'
   AND a.status= 0

好家伙,速度直接提升了8~10倍。然后就是大分页的问题,以一个实际查询为例

SELECT b.id,
       b.gmt_create,
       b.gmt_modified,
       b.is_delete,
       b.title,
       a.x
       a.status,
       a.app_code
FROM a
  LEFT JOIN b ON a.`a_id`= b.id
 WHERE a.app_code= 'x'
   AND a.status= 0
  ORDER BY a.`a_id` DESC
  LIMIT 900000, 10

最后一行是 PageHelper 拦截器自动加上的,这是典型的大分页问题。应该先用子查询通过覆盖索引查出符合条件的 ids 然后再 join 查出其他相关信息即可。

但是 PageHelper 没办法自定义插入到 SQL 中间,下面是手动调整之后的

SELECT b.id,
       b.gmt_create,
       b.gmt_modified,
       b.is_delete,
       b.title,
       a.x
       a.status,
       a.app_code
  FROM(
SELECT `article_id`
  FROM a
 WHERE `app_code`= 'x'
   AND `status`= 0
 ORDER BY x_id DESC
 LIMIT 900000, 10) a2
  LEFT JOIN a on a2.`x_id`= a.`x_id`
   AND a.`app_code`= 'x'
   AND `status`= 0
  LEFT JOIN b on a.`x_id`= b.`id`
 ORDER BY a2.`x_id` DESC

好家伙,速度提升 10 倍多。这两个 SQL 一起优化完,整个接口性能直接提升 20 倍。所以线上分页还是别用 PageHelper 了吧。

评论列表