本博客日IP超过2000,PV 3000 左右,急需赞助商。
极客时间所有课程通过我的二维码购买后返现24元微信红包,请加博主新的微信号:xttblog2,之前的微信号好友位已满,备注:返现
受密码保护的文章请关注“业余草”公众号,回复关键字“0”获得密码
所有面试题(java、前端、数据库、springboot等)一网打尽,请关注文末小程序
腾讯云】1核2G5M轻量应用服务器50元首年,高性价比,助您轻松上云
昨天,群里有一个网友问我关于 MySQL 大数据量分页的问题。有人回答说用缓存 Redis,这个就比较麻烦了。而且别人问的是 MySQL 分页,而不是架构如何设计!
那么我今天就来分享一下,5 种常见的 MySQL Limit 分页方法和优化技巧。
第一种方法,直接使用数据库提供的 SQL 语句 limit 来分页。
SELECT * FROM 表名称 LIMIT M,N
适用于数据量较少的情况。缺点是,全表扫描,速度会很慢且有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3)。Limit 限制的是从结果集的 M 位置处取出 N 条输出,其余抛弃。
方法二,建立主键或唯一索引,利用索引(假设每页 10 条)。
SELECT * FROM 表名称 WHERE id_pk > (pageNum * 10) LIMIT M
适用于数据量较多的情况。这样方法,会使用索引扫描,速度会很快。如果你的数据查询出来并不是按照 pk_id 排序的,那么就有可能漏掉数据的情况。所以使用的时候要注意。不行的话,你就用方法三。
方法三,基于索引再排序。
SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M
这种方法适用于数据量较多的情况。最好 ORDER BY 后的列对象是主键或唯一索引,使得 ORDER BY 操作能利用索引。
这种方法的特点是索引扫描,速度会很快。
方法四,基于索引使用 prepare(第一个问号表示 pageNum,第二个 ?表示每页元组数)。
PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk > (?* ?) ORDER BY id_pk ASC LIMIT M
这个 SQL 很厉害,适合海量数据的场景。这个查询会使用索引扫描,速度会很快。prepare 语句又比一般的查询语句快一点。
方案五,利用 MySQL 支持 ORDER 操作可以利用索引快速定位部分元组,避免全表扫描。
例如:读第 101000 到 101019 行元组( pk 是主键/唯一键)。
SELECT * FROM 表名称 WHERE pk >= 101000 ORDER BY pk ASC LIMIT 0,20
时间关系,我都不贴测试数据了。有问题,明天群里讨论。
学习是时刻进行的,不能遇到问题了,才去学。那就晚了,还是下面这个课程,很便宜。想深入理解 MySQL,建议你还是看看吧,返现还在进行,通过我的二维码购买,返现 5 元红包。
知识无价,这还不够你一顿饭钱,也不够你一包烟钱。
最后,欢迎关注我的个人微信公众号:业余草(yyucao)!可加作者微信号:xttblog2。备注:“1”,添加博主微信拉你进微信群。备注错误不会同意好友申请。再次感谢您的关注!后续有精彩内容会第一时间发给您!原创文章投稿请发送至532009913@qq.com邮箱。商务合作也可添加作者微信进行联系!
本文原文出处:业余草: » MySQL 海量数据的 5 种分页方法和优化技巧