「SQL小贴士」MyBatis百万级Limit分页优化,性能最低提升6倍

我是小探花 2020-04-02 13:14:18 ⋅ 109 阅读

前言

在实际开发中分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。DBA的同事给出的解决方法就是加“索引/组合索引”,例如在name,create_time,status等字段上加组合索引,这样根据这些条件查询的时候能够有效的利用索引,性能迅速的就提高了。

以上做法对 select * from table limit 0,10 这个没有问题,但是当 limit 1000000,10 的时候数据读取就很慢了。我只想要10条数据但是每次查询都扫描了100多万行,在高频访问下堵的死死的。那么有什么办法解决这个问题呢?

方案1—最大ID

前提:数据库主键ID为自增

正常分页SQL语句为:

select id,name,content,create_time from users order by id asc limit 1000000,10

优化后SQL语句为:

select id,name,content,create_time from users where id > 1000000 order by id asc limit 10

看到这里大家应该明白了吧?进行查询的时候可以将上一页的最大值ID当成参数作为查询条件的,那么当次查询只需要扫描最大ID开始的20行数据即可。

方案2—入库时间

那么有的同学会问如果设计的表主键不是自增又该怎么办?这里还有一种方法根据数据的入库时间,具体方法和上面雷同。

select id,name,content,create_time from users where id > '2020-01-01 12:29:23' order by id asc limit 10

这样查询的时间基本固定,并且也不会随着数据量的增长而发生变化。

测评

如下图所示,小编准备了300w+的数据,这张表共有28个字段。



正常分页所需时长:

从200w开始获取10条数据共花费12s多。

根据入库时间进行分页:

这里的查询时间是上面SQL查询出来结果的create_time,可以看到花费了2s多。性能提升了约6倍!

备注:create_time 字段没有增加索引。

总结

当一个数据库表过于庞大,LIMIT offset, length中的offset值过大,则SQL查询语句会非常缓慢,你需增加order by,并且order by字段需要建立索引。
如果使用子查询去优化LIMIT的话,则子查询必须是连续的,某种意义来讲,子查询不应该有where条件,where会过滤数据,使数据失去连续性。
如果你查询的记录比较大,并且数据传输量比较大,比如包含了text类型的field,则可以通过建立子查询。

SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);

如果limit语句的offset较大,你可以通过传递pk键值来减小offset = 0,这个主键最好是int类型并且auto_increment

SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;

这条语句,大意如下:

SELECT * FROM users WHERE uid >= (SELECT uid FROM users ORDER BY uid limit 895682, 1) limit 0, 10; 如果limit的offset值过大,用户也会翻页疲劳,你可以设置一个offset最大的,超过了可以另行处理,一般连续翻页过大,用户体验很差,则应该提供更优的用户体验给用户。



全部评论: 0

    我有话说:

    为什么说作为程序员分库表的必要性一定要掌握?

      互联网大厂程序员必须掌握海量数据和高并发问题处理技能,期望进入大厂的程序员一定要仔细看这篇! MySQL 分库表是做什么的? 相信很多程序员对 MySQL 都比较熟悉了,目前国内

    大数据量下 MyBatis PageHelper 查询性能问题的解决办法

    前因 项目一直使用的是PageHelper实现功能,项目前期数据量较少一直没有什么问题。随着业务扩增,数据库扩增PageHelper出现了明显的性能问题。 几十甚至上的单表数据查询性能缓慢

    打造千流量秒杀系统

    讲师:易乐天 前小米国际电商技术负责人 10 年软件开发经验。多年 Linux 系统编程、高性能和高并发编程经验。参与过亿用户、千日活、并发系统开发,曾经将 IM 云 WebSocket

    Netty单机连接及高性能优化

    关于netty的学习和介绍,可以去github看官方文档,这里良心推荐《netty实战》和《netty权威指南》两本书,前者对于新手更友好,原理和应用都有讲到,多读读会发现很多高性能的优化点。

    Java Web实战篇-轻松提高数据库查询效率

    通过优化数据库设计、java后台和数据库优化达到提高数据查询的效率。

    什么情况下才需要分库表?

    我请教一下,我模拟测试,搞了几张大表都是3千左右的数据,带主键查询秒响应,jmeter插入能够达到每秒6、700条左右,我业务量每秒就是300条左右插入,这个场景下是否不需要考虑分库表的问题?

    iOS TableView性能优化

    TableView的性能优化非常考验开发的基本功,之前做项目实战的时候经常被这个问题困扰

    前端性能分析工具-Keepfast

    能够分析网站的资源构建性能和页面性能,生成性能报告并提供优化建议,让性能监控更方便。

    重磅官宣:Nacos2.0发布,性能提升10

    我们终于将其全部实现,实测性能提升 10 ,相信...

    挖一挖那些让公司网站瘫痪的SQL“终结者”

    一条慢查询会造成什么后果?之前我一直觉得不就是返回数据会慢一些么,用户体验变差? 其实远远不止,我经历过几次线上事故,有一次就是由一条 SQL 慢查询导致的。 那次是一条 SQL 查询耗时达到 2

    移动H5前端五大性能优化方案(实战篇)

    移动H5前端五大性能优化方案(实战篇)

    Apache Spark 3.0 发布,包含3400 多个补丁,TPC-DS 性能提升2

    Spark 是用于大数据处理,数据科学,机器学习和数据分析等领域的统一引擎。

    为什么单线程的Redis能够达到的QPS?

    达到数十级别的 QPS(暂时忽略阿里对 R...

    微信程序电商实战-首(上)

    上一篇:微信程序电商实战-入门篇 嗨,大家好!经过近两周的精心准备终于开始微信程序电商实战之路喽。那么最终会做成什么样呢?好了,不啰嗦了 我们先看首长什么样吧!   首效果图

    微信程序抖音实战-首(下)

    抖音程序首动态数据获取

    微信程序电商实战-首(下)

    上一篇:微信程序电商实战-首(上)好了,上一期我们把首搜索、导航栏和广告轮播给做完了,那么接下来会继续