首页 > 数据库 >MySQL超大分页处理,以及优化思路说明。

MySQL超大分页处理,以及优化思路说明。

时间:2024-03-26 16:00:01浏览次数:37  
标签:10 分页 示例 超大 查询 cursor MySQL id

MySQL超大分页处理:

MySQL中分页处理的方式:

在 MySQL 中进行分页查询通常会使用 LIMITOFFSET 关键字来实现,下面是一种常见的方法:

sqlCopy CodeSELECT * FROM your_table LIMIT offset, limit;
  • offset 表示起始行数,从0开始计数,即第一行为0。
  • limit 表示每页返回的行数。

举个例子,如果我们想要查询学生信息表中的第 11 到 20 行数据,可以这样写查询语句:

sqlCopy CodeSELECT * FROM student_info LIMIT 10, 10;

这条 SQL 查询语句会返回从第 11 行到第 20 行的数据。你可以根据需要调整 offsetlimit 的值来获取不同页的数据。

另外,如果你想要指定查询的排序方式,可以在查询语句中添加 ORDER BY 子句。比如,按照学生的成绩降序查询前 10 名的学生信息:

sqlCopy CodeSELECT * FROM student_info ORDER BY score DESC LIMIT 10;

**缺点:**在数据量比较大的时候,使用limit分页查询,越往后,分页效率越低。

**原因:**LIMIT 1000000, 10。假设取第一百万条到一百万零十条的数据。那么MySQL需要排序前一百万零十条数据,然后舍弃前一百万条。所以性能很低。

优化思路

可以通过覆盖查询和子查询来提高效率。

select * 
from User_table a,
	(select id from User_table order by id limit 1000000,10)b 
where a.id = b.id

子查询使用覆盖索引去查询一百万条到一百万零十条的ID。再根据ID做条件去查数据。子查询中这个ID是主键,具体可以根据实际主键去查。

其他优化思路:

1、基于游标的分页优化

原理:通过游标(Cursor)逐批获取数据,避免一次性获取所有数据。

游标分页示例:

DELIMITER $$

CREATE PROCEDURE sp_cursor_paging(IN start_row INT, IN page_size INT)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE id INT;
    DECLARE name VARCHAR(255);
    DECLARE cursor_data CURSOR FOR SELECT id, name FROM your_table ORDER BY id LIMIT start_row, page_size;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cursor_data;

    read_loop: LOOP
        FETCH cursor_data INTO id, name;
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;

        -- 处理每一行数据,可以根据需要进行操作
        SELECT id, name;

    END LOOP;

    CLOSE cursor_data;

END$$

DELIMITER ;

在这个示例中,sp_cursor_paging 存储过程接受两个参数 start_rowpage_size,分别表示起始行和每页行数。存储过程通过游标 cursor_data 查询数据,并逐行获取数据进行处理。你可以根据实际需求修改 SELECT 语句和处理逻辑。

调用存储过程时,可以像这样传入起始行和每页行数:

sqlCopy CodeCALL sp_cursor_paging(0, 10); -- 获取第一页数据
CALL sp_cursor_paging(10, 10); -- 获取第二页数据

2、基于主键范围的分页优化

原理:利用主键范围进行分页,减少数据库的随机访问次数。

示例:使用 WHERE 子句限制主键范围,如 WHERE id > last_id LIMIT batch_size

3、缓存分页数据优化

原理:将查询结果缓存到缓存系统中,减少每次重新查询数据库。

示例:可以使用 Redis 等缓存系统缓存分页数据,从缓存中获取数据而不是每次都访问数据库。

4、使用索引优化

原理:确保查询字段有合适的索引,加快数据检索速度。

示例:为分页查询涉及的字段添加合适的索引,例如 CREATE INDEX idx_name ON your_table(name);

5、预先计算总行数优化

原理:提前计算总行数并缓存,避免每次 COUNT(*) 查询。

示例:在应用启动时或定时任务中计算总行数,并将结果存储到缓存中,需要时直接获取总行数而不是每次都进行 COUNT(*) 查询。

6、分区表优化

原理:使用分区表按某个字段分区,提高查询效率。

示例:创建分区表并按照时间范围等字段进行分区,将数据水平分布到不同分区中,减少查询范围。

7、定期优化表结构优化

原理:定期对表结构进行优化,包括索引优化、数据清理等。

示例:定期检查表结构,删除不必要的索引、清理过期数据等,保持查询性能。

标签:10,分页,示例,超大,查询,cursor,MySQL,id
From: https://blog.csdn.net/m0_56615376/article/details/137048998

相关文章

  • MySQL官方使用手册(自译)
    Chatper1OverviewOfMySQLDBMSwhatisMySQLMySQL,themostpopularOpenSourceSQLdatabasemanagementsystem,isdeveloped,distributed,andsupportedbyOracleCorporation.TheMySQLwebsite(http://www.mysql.com/)providesthelatestinformationa......
  • 【IT老齐055】Mysql Ngram全文检索技术
    【IT老齐055】MysqlNgram全文检索技术场景select*fromarticlewheretitlelikeJava%可能用到索引,看索引选择性select*fromarticlewheretitledlike%Java一定不会用到索引select*fromarticlewheretitlelike%Java%一定不会用到索引解决......
  • 【WEEK5】学习目标及总结【SpringMVC+MySQL】【中文版】
    学习目标:彻底完成SpringMVC的学习两周完成MySQL的学习——第一周学习内容:参考视频教程【狂神说Java】SpringMVC最新教程IDEA版通俗易懂拦截器文件的上传和下载学习时间及产出:第五周MON~Fri2024.3.25【WEEK5】【DAY1】拦截器【中文版】【WEEK5】【DAY1】Inter......
  • MySQL 中 WITH ROLLUP 用法
    WITHROLLUP是MySQL8中用于生成汇总行的一种扩展语法。它通常与GROUPBY子句一起使用,用于在查询结果中添加总计或分组小计。当你在查询中使用WITHROLLUP时,数据库会根据GROUPBY子句中指定的列生成汇总行,以显示每个分组的合计值。这使得你可以在单个查询中同时获取详细......
  • MySQL单表操作学习DDL_DML_DQL语句
    1创建数据库----DDL语句CREATETABLE`student`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'学号',`createDate`datetimeDEFAULTNULLCOMMENT'创建时间',`userName`varchar(20)DEFAULTNULLCOMMENT'用户名',`pwd`varchar(36)DEFA......
  • 一次快速使用docker安装mysql8.0记录(配置和数据挂载到指定目录)
    mkdir/home/mysql8vim/home/my.cnf[mysql]#设置mysql客户端默认字符集default-character-set=UTF8MB4[mysqld]port=3307max_connections=200max_connect_errors=10#修改加密方式,因为mysql8.x版本默认的密码加密的方式,Navicat识别不了,需修改为mysql_native_password......
  • mybatis plus 添加分页插件
    一、后端配置支持分页控件1、在pom.xml添加上依赖<!--Mybatis-Plus--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.3.2......
  • DBCP一个配置,浪费了MySQL 50%的性能!
    1.引言研究背景数据库性能的重要性数据库性能优化对于保证应用的响应速度和处理大量数据的能力至关重要。它可以显著减少查询时间,提高事务处理效率,降低硬件成本,并确保系统稳定性与可扩展性。优化后的数据库能够更好地服务于用户需求,增强客户满意度,对企业的长期发展和竞争力具......
  • Linux(2)系统基本操作-Mysql数据库原生安装_Mysql常用命令_安装和使用过程常用问题
    二、Linux系统基本操作1、查询centos版本[root@host-10-150-223-171~]#uname-aLinuxhost-10-150-223-1713.10.0-957.el7.x86_64#1SMPThuNov823:39:32UTC2018x86_64x86_64x86_64GNU/Linux[root@host-10-150-223-171~]#cat/etc/redhat-releaseCentO......
  • java计算机毕业设计(附源码)新知书店(ssm+mysql+maven+LW文档)
    本系统(程序+源码)带文档lw万字以上  文末可领取本课题的JAVA源码参考系统程序文件列表系统的选题背景和意义选题背景:新知书店,作为一家专注于传播知识和文化的零售场所,承载着促进社会文化发展和满足人们精神需求的重要使命。在数字化时代背景下,实体书店面临着前所未有的挑......