MySQL大数据分页查询性能优化


Mysql优化

为大家介绍MySQL大数据分页查询性能优化的方法,希望大家学习后能有所提升。

使用limit start, count分页语句

select * from product limit start, count

当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下:

select * from product limit 10, 20   0.016秒
select * from product limit 100, 20   0.016秒
select * from product limit 1000, 20   0.047秒
select * from product limit 10000, 20   0.094秒

我们已经看出随着起始记录的增加,时间也随着增大, 这说明分页语句limit跟起始页码是有很大关系的,那么我们把起始记录改为40w看下(也就是记录的一般左右)

select * from product limit 400000, 20   3.229秒

再看我们取最后一页记录的时间

select * from product limit 866613, 20   37.44秒

难怪搜索引擎抓取我们页面的时候经常会报超时,像这种分页最大的页码页显然这种时间是无法忍受的。

从中我们也能总结出两件事情:

  1. limit语句的查询时间与起始记录的位置成正比

  2. mysql的limit语句是很方便,但是对记录很多的表并不适合直接使用。

对limit分页问题的性能优化方法

利用表的覆盖索引来加速分页查询

我们都知道,利用了索引查询的语句中如果只包含了那个索引列(覆盖索引),那么这种情况会查询很快。

因为利用索引查找有优化算法,且数据就在查询索引上面,不用再去找相关的数据地址了,这样节省了很多时间。另外Mysql中也有相关的索引缓存,在并发高的时候利用缓存就效果更好了。

在我们的例子中,我们知道id字段是主键,自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何:

这次我们之间查询最后一页的数据(利用覆盖索引,只包含id列查询的数据少在配合索引的确很快,如果使用*进行全表查询还是很慢的),如下:

select id from product limit 866613, 20 0.2秒

相对于查询了所有列的37.44秒,提升了大概100多倍的速度

那么如果我们也要查询所有列,有两种方法,一种是id>=的形式,另一种就是利用join,看下实际情况:

这段语句就是先通过单个字段(最好有索引的)进行分页查询(因为只有一个字段所以查询速度比全表查询要快很多)后,在通过上面查询的数据进行精准全表查询

SELECT * FROM product WHERE ID > =(select id from` product limit 866613, 1) limit 20

查询时间为0.2秒,简直是一个质的飞跃。

另一种写法,和上面的理念基本一样

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

查询时间也很短!

其实两者用的都是一个原理嘛,所以效果也差不多

Mysql的分页查询十分简单,但是当数据量大的时候一般的分页就吃不消了。

传统分页查询:

SELECT c1,c2,cn… FROM table LIMIT n,m

MySQL的limit工作原理就是先读取前面n条记录,然后抛弃前n条,读后面m条想要的,所以n越大,偏移量越大,性能就越差。

推荐分页查询方法

1、尽量给出查询的大致范围,这样就会在指定的范围开始查询

SELECT c1,c2,cn… FROM table WHERE id>=20000 LIMIT 10;

2、子查询法

SELECT c1,c2,cn… FROM table WHERE id>=(SELECT id FROM table LIMIT 20000,1)LIMIT 10;

3、高性能MySQL一书中提到的只读索引方法

优化前SQL:

SELECT c1,c2,cn… FROM member ORDER BY last_active LIMIT 50,5

优化后SQL:

SELECT c1, c2, cn .. .
FROM member
INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5)
USING (member_id)

分别在于,优化前的SQL需要更多I/O浪费,因为先读索引,再读数据,然后抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了,然后通过member_id读取需要的列。

4、第一步用用程序读取出ID,然后再用IN方法读取所需记录

程序读ID:

SELECT id FROM table LIMIT 20000, 10;
SELECT c1, c2, cn .. . FROM table WHERE id IN (id1, id2, idn.. .)


文章来源地址https://www.toymoban.com/diary/sql/553.html

到此这篇关于MySQL大数据分页查询性能优化的文章就介绍到这了,更多相关内容可以在右上角搜索或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

原文地址:https://www.toymoban.com/diary/sql/553.html

如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请联系站长进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用
PHP过滤XSS攻击插件源码实例
上一篇 2023年11月24日 00:24
记录几个国内前端CDN静态资源公共库
下一篇 2023年11月25日 10:00

相关文章

  • 千万级数据深分页查询SQL性能优化实践

    如何在Mysql中实现上亿数据的遍历查询?先来介绍一下系统主角:关注系统,主要是维护京东用户和业务对象之前的关注关系;并对外提供各种关系查询,比如查询用户的关注商品或店铺列表,查询用户是否关注了某个商品或店铺等。但是最近接到了一个新需求,要求提供查

    2024年02月11日
    浏览(55)
  • 【性能优化】MySQL百万数据深度分页优化思路分析

            一般在项目开发中会有很多的统计数据需要进行上报分析,一般在分析过后会在后台展示出来给运营和产品进行 分页查看 , 最常见的一种就是根据日期进行筛选 。这种统计数据随着时间的推移数据量会慢慢的变大,达到百万、千万条数据只是时间问题。 一、数

    2024年02月15日
    浏览(48)
  • 【QT性能优化】QT性能优化之QT6框架高性能模型视图代理框架千万级数据表分页查询优化

    QT性能优化之QT6框架高性能模型视图代理框架千万级数据表分页查询优化 简介 本文介绍了QT模型视图代理框架中的QT表格控件和QT数据库模块中的QT数据库查询模型结合使用的一个应用实践案例:QT高性能表格控件分页展示千万行数据。本文介绍了这个应用实践案例的运行效果

    2024年02月14日
    浏览(51)
  • MySQL大数据量分页查询方法及其优化

    ---方法1: 直接使用数据库提供的SQL语句 ---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N ---适应场景: 适用于数据量较少的情况(元组百/千级) ---原因/缺点: 全表扫描,速度会很慢 且 有的数据库 结果集返回不稳定 (如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从

    2024年02月15日
    浏览(35)
  • MySQL分页查询详解:优化大数据集的LIMIT和OFFSET

    最近在工作中,我们遇到了一个需求,甲方要求直接从数据库导出一个业务模块中所有使用中的工单信息。为了实现这一目标,我编写了一条SQL查询语句,并请求DBA协助导出数据。尽管工单数量并不多,只有3000多条,但每个工单都包含了大量的信息。DBA进行了多次导出操作,

    2024年02月10日
    浏览(51)
  • 【性能优化】MySql数据库查询优化方案

    了解系统运行效率提升的整体解决思路和方向 学会MySQl中进行数据库查询优化的步骤 学会看慢查询、执行计划、进行性能分析、调优 ​关于这个问题,我们通常首先考虑的是硬件升级,毕竟服务器的内存、CPU、磁盘IO速度 、网络速度等都是制约我们系统快慢的首要因素。硬

    2024年02月03日
    浏览(57)
  • MySQL性能优化盲区(高并发情况下,事务内的数据先更新还是先查询?)

    近期看到了一个前阿里资深开发的学术分析视频: 高并发情况下,一个事务内有更新操作还有查询操作,那是先更新好,还是先无锁查询好? 仅70秒的视频,深感学问太深,但是海哥讲的有待补充,于是写下了这篇文章,作为补充。 鸣谢:前阿里资深开发极海Channel的技术分

    2024年03月15日
    浏览(48)
  • 《高性能MYSQL》-- 查询性能优化

    查询性能优化 深刻地理解MySQL如何真正地执行查询,并明白高效和低效的原因何在 查询的生命周期(不完整):从客户端到服务器,然后服务器上进行语法解析,生成执行计划,执行,并给客户端返回结果。 一条查询,如果查询得很慢,原因大概率是访问的数据太多 对于低

    2024年03月11日
    浏览(71)
  • 《高性能MySQL》——查询性能优化(笔记)

    将查询看作一个任务,那么它由一系列子任务组成,实际我们所做的就是: 消除一些子任务 减少子任务的执行次数 让子任务运行更快 查询的生命周期大概可分为 = { 客户端 服务器 : 进行解析 , 生成执行计划 执行:包括到存储引擎的调用,以及用后的数据处理 { 排序 分组

    2024年02月13日
    浏览(52)
  • oracle 大数据常见优化 &分页查询

    参考某微信社区博主,此文为温故知新;原参考博主账号与链接已丢失 1.limit 语句样式:select * from table limit m,n 适用场景:适用于数据量较少的情况(元组、百/千) 缺点:全表扫描,速度劣势,有的数据库结果集返回不稳定。limit限制是从结果集的M位置处取出N条输出,其余

    2024年02月05日
    浏览(41)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包