mysql 分页优化解析

如果你的数据量有几十万条，用户又搜索一些很通俗的词，然后要依次读最后几页重温旧梦。mysql该很悲壮的不停操作硬盘。

所以，可以试着让mysql也存储分页，当然要程序配合。（这里只是提出一个设想，欢迎大家一起讨论）

ASP的分页：在ASP系统中有Recordset对象来实现分页，但是大量数据放在内存中，而且不知道什么时候才失效(请ASP高手指点). SQL数据库分页：用存储过程+游标方式分页，具体实现原理不是很清楚，设想如果用一次查询就得到需要的结果，或者是id集，需要后续页时只要按照结果中的IDs读出相关记录。这样只要很小的空间保留本次查询的所有IDs. (SQL中的查询结果不知道怎样清楚过期垃圾?)

这样，可以让mysql模拟存储分页机制：

1. select id from $table where $condition order by $field limit $max_pages*$count;查询符合条件的IDs.限定最大符合条件的记录数量，也可以不加。

2. 因为php在执行结束后所有变量都要lost，所以可以考虑:方案a. 在mysql建立临时表，查询结果用一个时间或随机数作为唯一标志插入。其中建立page1~pagen个字段，每个字段保存该页中需要的ids, 这样一个id对一条记录.方案b. 如果打开session,也可以放在session中保存，实际上是放在文件中保存。建立一个$IDs数组，$IDs[1]~$IDs[$max_pages]. 考虑到有时候用户会开几个窗口同时查询，要为$ids做一个唯一标志，避免查询结果相互覆盖。二维数组和$$var都是好办法。

3. 在每页页的请求中，直接找到对应的IDs，中间以","间隔：select * from $table where id in ($ids); 速度绝对快

4. 收尾要考虑查询结果的自动清除，可以设置定时或者按比例随机清楚。如果用mysql临时表要加上一个时间标志字段，session中要加入$IDs["time"]=time(); 在一定时间以后不操作视为过期数据。

5. 如果要优化，可以考虑用把1和2.a中的语句合并成select ...... into ....

Note:1.以上只是针对mysql的修补方案，希望mysql哪天能把这些功能加进去2.其它数据库也可以套用。3.如果其它数据库还有更先进的分页方式，请告诉我或mailto: steeven@kali.com.cn4.如果真的有很多数据要查询，还是和mysql再见吧，sql,oracle都提供了更先进的关键词索引查询。

精益求精，以上只是抛砖引玉，欢迎共同探讨分页问题。（也可关于其它数据库）希望有一天能把各种分页方式整理出来供新手参考。

赞 (0)

相关推荐

  • mysql分页时offset过大的Sql优化经验分享

    发现问题 当我们展示一个列表中的内容时,难免会遇到分页问题,因为列表中的内容数量可能很多,但是用户能一次看到的界面大小是有限的,不可能一个界面展示所有的内容,从后端一次性取太多的数据也会给后端造成额外的压力. 通常分页查询的时候会使用这样的语句: SELECT * FROM table where condition1 = 0 and condition2 = 0 and condition3 = -1 and condition4 = -1 order by id asc LIMIT 2000

  • 如何优化Mysql千万级快速分页

    看例子: 数 据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引.这是一个基本的新闻系统的简单模型.现在往里面填充数据,填充10万篇新闻. 最后collect 为 10万条记录,数据库表占用硬盘1.6G. OK ,看下面这条sql语句: select id,title from collect limit 1000,10; 很快:基本上0.01秒就OK

  • 优化mysql的limit offset的例子

    经常碰到的一个问题是limit的offset太高,如:limit 100000,20,这样系统会查询100020条,然后把前面的100000条都扔掉,这是开销很大的操作,导致查询很慢.假设所有分页的页面访问频率一样,这样的查询平均扫描表的一半数据.优化的方法,要么限制访问后面的页数,要么提升高偏移的查询效率. 一个简单的优化办法是使用覆盖查询(covering index)查询,然后再跟全行的做join操作.如: 复制代码 代码如下: SQL>select * from user_order_i

  • 8种MySQL分页方法总结

    MySQL的分页似乎一直是个问题,有什么优化方法吗?网上看到网上推荐了一些分页方法,但似乎不太可行,你能点评一下吗? 方法1: 直接使用数据库提供的SQL语句 ---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N. ---适应场景: 适用于数据量较少的情况(元组百/千级). ---原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3).Limit限制的是从结果集的M位置处取出N条输出,

  • mysql 分页优化解析

    如果你的数据量有几十万条,用户又搜索一些很通俗的词,然后要依次读最后几页重温旧梦.mysql该很悲壮的不停操作硬盘. 所以,可以试着让mysql也存储分页,当然要程序配合.(这里只是提出一个设想,欢迎大家一起讨论) ASP的分页:在ASP系统中有Recordset对象来实现分页,但是大量数据放在内存中,而且不知道什么时候才失效(请ASP高手指点). SQL数据库分页:用存储过程+游标方式分页,具体实现原理不是很清楚,设想如果用一次查询就得到需要的结果,或者是id集,需要后续页时只要按照结果中的I

  • MySQL分页优化

    MySQL分页优化

    最近,帮同事重写了一个MySQL SQL语句,该SQL语句涉及两张表,其中一张表是字典表(需返回一个字段),另一张表是业务表(本身就有150个字段,需全部返回),当然,字段的个数是否合理在这里不予评价.平时,返回的数据大概5w左右,系统尚能收到数据.但12月31日那天,数据量大概20w,导致SQL执行时间过长,未能在规定的时间内反馈结果,于是系统直接报错. 一般的思路是用MySQL的分页功能,即直接在原SQL语句后面增加LIMIT子句.但请注意,虽然你看到的反馈结果只是LIMIT后面指定的数量,

  • MySQL优化案例系列-mysql分页优化

    通常,我们会采用ORDER BY LIMIT start, offset 的方式来进行分页查询.例如下面这个SQL: SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10; 或者像下面这个不带任何条件的分页SQL: SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10; 一般而言,分页SQL的耗时随着 start 值的增加而急剧增加,我们来看下面这2个不同起始值的分页SQL执行

  • 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 百万级分页优化(Mysql千万级快速分页)

    以下分享一点我的经验 一般刚开始学SQL的时候,会这样写 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000, 10; 但在数据达到百万级的时候,这样写会慢死 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000000, 10; 也许耗费几十秒 网上很多优化的方法是这样的 复制代码 代码如下: SELECT * FROM table WHERE id >= (SELECT id FROM

  • 19个MySQL性能优化要点解析

    以下就是跟大家分享的19个MySQL性能优化主要要点,一起学习学习. 1.为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存.这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的.当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果了. 这里最主要的问题是,对于程序员来说,这个事情是很容易被忽略的.因为,我们某些查询语句会让MySQL不使用缓存.请看下面的示例: // 查询缓存不开启 $r = mysq

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

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

  • MySQL分页Limit的优化过程实战

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

  • 浅谈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

随机推荐