浅谈mysql使用limit分页优化方案的实现

Mysql limit分页语句用法

与Oracle和MS SqlServer相比，mysql的分页方法简单的让人想哭。

--语法：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

--举例：

select * from table limit 5; --返回前5行

select * from table limit 0,5; --同上，返回前5行

select * from table limit 5,10; --返回6-15行

如何优化limit

当一个查询语句偏移量offset很大的时候，如select * from table limit 10000,10 , 最好不要直接使用limit，而是先获取到offset的id后，再直接使用limit size来获取数据。效果会好很多。

如：

select * From customers Where customer_id >=(
select customer_id From customers Order By customer_id limit 10000,1
) limit 10;

一、测试实验

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秒

像这种分页最大的页码页显然这种时间是无法忍受的。

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

  • limit语句的查询时间与起始记录的位置成正比。
  • mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。

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

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

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

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。

另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。

在我们的例子中，我们知道id字段是主键，自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何：
这次我们之间查询最后一页的数据（利用覆盖索引，只包含id列），如下：

select id from product limit 866613, 20

查询时间为0.2秒，相对于查询了所有列的37.44秒，提升了大概100多倍的速度。

那么如果我们也要查询所有列，有两种方法，

id>=的形式：

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

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

利用join

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

查询时间也很短，赞！

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

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。

赞 (0)

相关推荐

  • mysql limit 分页的用法及注意要点

    mysql limit 分页的用法及注意事项: 在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能. SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数.LIMIT 接受一个或两个数字参数.参数必须 是一个整数常量.如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参

  • mysql limit分页优化详细介绍

    mysql limit分页优化 同样是取10条数据 select * from yanxue8_visit limit 10000,10 和 select * from yanxue8_visit limit 0,10 就不是一个数量级别的. 网上也很多关于limit的五条优化准则,都是翻译自MySQL手册,虽然正确但不实用.今天发现一篇文章写了些关于limit优化的,很不错. 文中不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据.根据他的数据,

  • mysql limit分页优化方法分享

    同样是取10条数据 select * from yanxue8_visit limit 10000,10 和 select * from yanxue8_visit limit 0,10 就不是一个数量级别的. 网上也很多关于limit的五条优化准则,都是翻译自MySQL手册,虽然正确但不实用.今天发现一篇文章写了些关于limit优化的,很不错. 文中不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据.根据他的数据,明显要好于直接使用limit.这

  • MYSQL分页limit速度太慢的优化方法

    在mysql中limit可以实现快速分页,但是如果数据到了几百万时我们的limit必须优化才能有效的合理的实现分页了,否则可能卡死你的服务器哦.    当一个表数据有几百万的数据的时候成了问题! 如 * from table limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就很慢,可以按照一下方法解决     第一页会很快    PERCONA PERFORMANCE CONFERENCE 2009上,来自雅虎的几位工程师带来了一篇"EfficientPag

  • MySQL limit使用方法以及超大分页问题解决

    前言 日常开发中,我们使用mysql来实现分页功能的时候,总是会用到mysql的limit语法.而怎么使用却很有讲究的,今天来总结一下. limit语法 limit语法支持两个参数,offset和limit,前者表示偏移量,后者表示取前limit条数据. 例如: ## 返回符合条件的前10条语句 select * from user limit 10 ## 返回符合条件的第11-20条数据 select * from user limit 10,20 从上面也可以看出来,limit n 等价于l

  • 详解MySQL的limit用法和分页查询语句的性能分析

    limit用法 在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能. SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数.LIMIT 接受一个或两个数字参数.参数必须是一个整数常量.如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目.初始记

  • 在MySQL中使用LIMIT进行分页的方法

    今天看一个水友说他的MySQL现在变的很慢.问什么情况时.说单表超过2个G的一个MyISAM.真垃圾的回答方式. 简单答复:换一个强劲的服务器.换服务器很管用的:) ---        最终让取到慢查询: SELECT * FROM pw_gbook WHERE uid='N' ORDER BY postdate DESC LIMIT N,N; SELECT * FROM pw_gbook WHERE uid='N' ORDER BY postdate DESC LIMIT N,N; 如: S

  • Mysql limit 优化，百万至千万级快速分页 复合索引的引用并应用于轻量级框架

    MySql 这个数据库绝对是适合dba级的高手去玩的,一般做一点1万篇新闻的小型系统怎么写都可以,用xx框架可以实现快速开发.可是数据量到了10万,百万至千万,他的性能还能那么高吗?一点小小的失误,可能造成整个系统的改写,甚至更本系统无法正常运行!好了,不那么多废话了.用事实说话,看例子: 数据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引.这是一个

  • Mysql Limit 分页查询优化详解

    select * from table LIMIT 5,10; #返回第6-15行数据 select * from table LIMIT 5; #返回前5行 select * from table LIMIT 0,5; #返回前5行 我们来写分页 物理分页 select * from table LIMIT (当前页-1)*每页显示条数,每页显示条数; MySQL之Limit简单优化.md 同样是取90000条后100条记录,传统方式还是改造方式? 传统方式是先取了前90001条记录,取其中最

  • 浅谈mysql使用limit分页优化方案的实现

    Mysql limit分页语句用法 与Oracle和MS SqlServer相比,mysql的分页方法简单的让人想哭. --语法: SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset --举例: select * from table limit 5; --返回前5行 select * from table limit 0,5; --同上,返回前5行 select * from table limit 5,10; --返回6

  • 浅谈Mysql大数据分页查询解决方案

    浅谈Mysql大数据分页查询解决方案

    目录 1.简介 2.分页插件使用 3.sql测试与分析 3.1 limit现象分析 3.2 解决之道 4 测试时走过的坑 4.1 百万数据内容都一样 4.2 写sql时,把"77"写成了77: 4.3 一个有趣的现象 总结 1.简介 之前,面阿里的时候,有个面试官问我有没有使用过分页查询,我说有,他说分页查询是有问题的,怎么解决:后来这个问题我没有回答出来:本着学习的态度,今天来解决一下这个问题: 2.分页插件使用 1.pom文件 <dependency> <grou

  • 浅谈MySQL之select优化方案

    目录 生活中的例子 慢查询 如何去优化 count limit 最大值最小值min&max 生活中的例子 我们是否看到过在公司中许多查询语句都是select * xxxx 心中的想法肯定是,别人写了select *,那我写吧,省去了不少麻烦事儿 慢查询 首先去思考,最基本的,是否我们使用的数据库插查询语句存在了访问的数据太多 其实大部分性能低的查询往往都可以通过减少访问的数据量来优化的 因为select * 会给服务器带来额外的I/O.内存和cpu的消耗 数据库中慢查询开销的三个指标 相应时间

  • 浅谈MySQL大表优化方案

    背景 阿里云RDS FOR MySQL(MySQL5.7版本)数据库业务表每月新增数据量超过千万,随着数据量持续增加,我们业务出现大表慢查询,在业务高峰期主业务表的慢查询需要几十秒严重影响业务 方案概述 一.数据库设计及索引优化 MySQL数据库本身高度灵活,造成性能不足,严重依赖开发人员的表设计能力以及索引优化能力,在这里给几点优化建议 时间类型转化为时间戳格式,用int类型储存,建索引增加查询效率 建议字段定义not null,null值很难查询优化且占用额外的索引空间 使用TINYINT类

  • 浅谈mysql 树形结构表设计与优化

    浅谈mysql 树形结构表设计与优化

    前言 在诸多的管理类,办公类等系统中,树形结构展示随处可见,以"部门"或"机构"来说,接触过的同学应该都知道,最终展示到页面的效果就是层级结构的那种,下图随机列举了一个部门的树型结构展示图 设计考虑因素 1.表结构设计 稍稍有点开发和表结构设计经验的同学,设计出这样一张表,应该很容易,只需要在depart表中,添加一个pid/字段即可满足要求,参考下表: CREATE TABLE `depart` ( `depart_id` varchar(32) NOT NULL

  • 浅谈MySQL与redis缓存的同步方案

    浅谈MySQL与redis缓存的同步方案

    本文介绍MySQL与Redis缓存的同步的两种方案 方案1:通过MySQL自动同步刷新Redis,MySQL触发器+UDF函数实现 方案2:解析MySQL的binlog实现,将数据库中的数据同步到Redis 一.方案1(UDF) 场景分析:当我们对MySQL数据库进行数据操作时,同时将相应的数据同步到Redis中,同步到Redis之后,查询的操作就从Redis中查找 过程大致如下: 在MySQL中对要操作的数据设置触发器Trigger,监听操作 客户端(NodeServer)向MySQL中写入数

  • 浅谈mysql执行过程以及顺序

    浅谈mysql执行过程以及顺序

    前言:mysql在我们的开发中基本每天都要面对的,作为开发中的数据的来源,mysql承担者存储数据和读写数据的职责.因为学习和了解mysql是至关重要的,那么当我们在客户端发起一个sql到出现详细的查询数据,这其中究竟经历了什么样的过程?mysql服务端是如何处理请求的,又是如何执行sql语句的?本篇博客将来探讨这个问题: 一:mysql执行过程 mysql整体的执行过程如下图所示: 1.1:连接器 连接器的主要职责就是: ①负责与客户端的通信,是半双工模式,这就意味着某一固定时刻只能由客户端向

  • 浅谈MySQL和MariaDB区别(mariadb和mysql的性能比较)

    MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可.开发这个分支的原因之一是:甲骨文公司收购了MySQL后,有将MySQL闭源的潜在风险,因此社区采用分支的方式来避开这个风险. MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品. MariaDB由MySQL的创始人麦克尔·维德纽斯主导开发,他早前曾以10亿美元的价格,将自己创建的公司MySQL卖给了SUN,此后,随着SUN被甲骨文收购,MySQL的所有权也落入

  • 浅谈Mysql主键索引与非主键索引区别

    目录 什么是索引 主键索引和普通索引的区别 索引具体采用的哪种数据结构 InnoDB使用的B+ Tree的索引模型,那么为什么采用B+ 树?这和Hash索引比较起来有什么优缺点? B+ Tree的叶子节点都可以存哪些东西? 聚簇索引和非聚簇索引,在查询数据的时候有区别? Index Condition Pushdown(索引下推) 查询优化器 关于索引的题 什么是索引 MySql官方索引的定义:索引(Index)是帮助MySql高效获取数据的数据结构,索引的目的在于提高查询效率,类比字典:实际上

随机推荐