[分享]基于mysql的分页程序完全解决方案(含普通分页/分段分页/原始分页/微博的since_id类分页)

[goosman]

本文博客地址: http://blog.csdn.net/lgg201/article/details/7757494

本文涉及的源代码可在http://download.csdn.net/user/lgg201下载, 也可在对应博文中查看.

SQL解析有很多不足之处, 请各位大牛指正.

0. 下载:
本程序可自由修改, 自由分发, 可在http://download.csdn.net/user/lgg201下载
1. 分页的需求
信息的操纵和检索是当下互联网和企业信息系统承担的主要责任. 信息检索是从大量的数据中找到符合条件的数据以用户界面展现给用户.
符合条件的数据通常会有成千上万条, 而用户的单次信息接受量是很小的, 因此, 如果一次将所有符合用户条件的数据展现给用户, 对于多数场景, 其中大部分数据都是冗余的.
信息检索完成后, 是需要经过传输(从存储介质到应用程序)和相关计算(业务逻辑)的, 因此, 我们需要一种分段的信息检索机制来降低这种冗余.
分页应运而生.
2. 分页的发展
基本的分页程序, 将数据按照每页记录数(page_size)将数据分为ceil(total_record / page_size)页, 第一次为用户展现第一段的数据, 后续的交互过程中, 用户可以选择到某一页对数据进行审阅.
后来, 主要是在微博应用出现后, 由于其信息变化很快, 而其特性为基于时间线增加数据, 这样, 基本的分页程序不能再满足需求了: a) 当获取下一页时, 数据集可能已经发生了很多变化, 翻页随时都可能导致数据重复或跳跃; b) 此类应用采用很多采用一屏展示多段数据的用户界面, 更加加重了数据重复/跳跃对用户体验的影响. 因此, 程序员们开始使用since_id的方式, 将下一次获取数据的点记录下来, 已减轻上述弊端.
在同一个用户界面, 通过用户阅读行为自动获取下一段/上一段数据的确比点击"下一页"按钮的用户体验要好, 但同样有弊端: a) 当用户已经到第100页时, 他要回到刚才感兴趣的第5页的信息时, 并不是很容易, 这其实是一条设计应用的规则, 我们不能让用户界面的单页屏数过多, 这样会降低用户体验; b) 单从数据角度看, 我们多次读取之间的间隔时间足够让数据发生一些变化, 在一次只展示一屏时, 我们很难发现这些问题(因此不影响用户体验), 然而当一页展示100屏数据时, 这种变化会被放大, 此时, 数据重复/跳跃的问题就会再次出现; c) 从程序的角度看, 将大量的数据放置在同一个用户界面, 必然导致用户界面的程序逻辑受到影响. 基于以上考虑, 目前应用已经开始对分页进行修正, 将一页所展示的屏数进行的限制, 同时加入了页码的概念, 另外也结合since_id的方式, 以达到用户体验最优, 同时保证数据逻辑的正确性(降低误差).
3. 分页的讨论
感谢xp/jp/zq/lw四位同事的讨论, 基于多次讨论, 我们分析了分页程序的本质. 主要的结论点如下:
1) 分页的目的是为了分段读取数据
2) 能够进行分页的数据一定是有序的, 哪怕他是依赖数据库存储顺序. (这一点换一种说法更容易理解: 当数据集没有发生变化时, 同样的输入, 多次执行, 得到的输出顺序保持不变)
3) 所有的分段式数据读取, 要完全保证数据集的一致性, 必须保证数据集顺序的一致性, 即快照
4) 传统的分页, 分段式分页(每页内分为多段)归根结底是对数据集做一次切割, 映射到mysql的sql语法上, 就是根据输入求得limit子句, 适用场景为数据集变化频率低
5) since_id类分页, 其本质是假定已有数据无变化, 将数据集的某一个点的id(在数据集中可以绝对定位该数据的相关字段)提供给用户侧, 每次携带该id读取相应位置的数据, 以此模拟快照, 使用场景为数据集历史数据变化频率低, 新增数据频繁
6) 如果存在一个快照系统, 能够为每一个会话发起时的数据集产生一份快照数据, 那么一切问题都迎刃而解
7) 在没有快照系统的时候, 我们可以用since_id的方式限定数据范围, 模拟快照系统, 可以解决大多数问题
8) 要使用since_id方式模拟快照, 其数据集排序规则必须有能够唯一标识其每一个数据的字段(可能是复合的)
4. 实现思路
1) 提供SQL的转换函数
2) 支持分段式分页(page, page_ping, ping, ping_size), 传统分页(page, page_size), 原始分页(offset-count), since_id分页(prev_id, next_id)
3) 分段式分页, 传统分页, 原始分页在底层均转换为原始分页处理
5. 实现定义
ping_to_offset
输入:
page #请求页码, 范围: [1, total_page], 超过范围以边界计, 即0修正为1, total_page + 1修正为total_page
ping #请求段号, 范围: [1, page_ping], 超过范围以边界计, 即0修正为1, page_ping + 1修正为page_ping
page_ping #每页分段数, 范围: [1, 无穷]
count #要获取的记录数, 当前应用场景含义为: 每段记录数, 范围: [1, 无穷]
total_record #总记录数, 范围: [1, 无穷]
输出:
offset #偏移量
count #读取条数
offset_to_ping
输入:
offset #偏移量(必须按照count对齐, 即可以被count整除), 范围: [0, 无穷]
page_ping #每页分段数, 范围: [1, 无穷]
count #读取条数, 范围: [1, 无穷]
输出:
page #请求页码
ping #请求段号
page_ping #每页分段数
count #要获取的记录数, 当前应用场景含义为: 每段记录数
page_to_offset
输入:
page #请求页码, 范围: [1, total_page], 超过范围以边界计, 即0修正为1, total_page + 1修正为total_page
total_record #总记录数, 范围: [1, 无穷]
count #要获取的记录数, 当前应用场景含义为: 每页条数, 范围: [1, 无穷]
输出:
offset #偏移量
count #读取条数
offset_to_page
输入:
offset #偏移量(必须按照count对齐, 即可以被count整除), 范围: [0, 无穷]
count #读取条数, 范围: [1, 无穷]
输出:
page #请求页码
count #要获取的记录数, 当前应用场景含义为: 每页条数
sql_parser #将符合mysql语法规范的SQL语句解析得到各个组件
输入:
sql #要解析的sql语句
输出:
sql_components #SQL解析后的字段
sql_restore #将SQL语句组件集转换为SQL语句
输入:
sql_components #要还原的SQL语句组件集
输出:
sql #还原后的SQL语句
sql_to_count #将符合mysql语法规范的SELECT语句转换为获取计数
输入:
sql_components #要转换为查询计数的SQL语句组件集
alias #计数字段的别名
输出:
sql_components #转换后的查询计数SQL语句组件集
sql_add_offset
输入:
sql_components #要增加偏移的SQL语句组件集, 不允许存在LIMIT组件
offset #偏移量(必须按照count对齐, 即可以被count整除), 范围: [0, 无穷]
count #要获取的记录数, 范围: [1, 无穷]
输出:
sql_components #已增加LIMIT组件的SQL语句组件集
sql_add_since #增加since_id式的范围
输入:
sql_components #要增加范围限定的SQL语句组件集
prev_id #标记上一次请求得到的数据左边界
next_id #标记上一次请求得到的数据右边界
输出:
sql_components #增加since_id模拟快照的范围限定后的SQL语句组件集
datas_boundary #获取当前数据集的边界
输入:
sql_components #要读取的数据集对应的SQL语句组件集
datas #结果数据集
输出:
prev_id #当前数据集左边界
next_id #当前数据集右边界
mysql_paginate_query #执行分页支持的SQL语句
输入:
sql #要执行的业务SQL语句
offset #偏移量(必须按照count对齐, 即可以被count整除), 范围: [0, 无穷]
count #读取条数, 范围: [1, 无穷]
prev_id #标记上一次请求得到的数据左边界
next_id #标记上一次请求得到的数据右边界
输出:
datas #查询结果集
offset #偏移量
count #读取条数
prev_id #当前数据集的左边界
next_id #当前数据集的右边界
6. 实现的执行流程
分段式分页应用(page, ping, page_ping, count):
total_record = sql_to_count(sql);
(offset, count) = ping_to_offset(page, ping, page_ping, count, total_record)
(datas, offset, count) = mysql_paginate_query(sql, offset, count, NULL, NULL);
(page, ping, page_ping, total_record, count) = offset_to_ping(offset, page_ping, count, total_record);
return (datas, page, ping, page_ping, total_record, count);
传统分页应用(page, count):
total_record = sql_to_count(sql);
(offset, count) = page_to_offset(page, count, total_record)
(datas, offset, count) = mysql_paginate_query(sql, offset, count, NULL, NULL);
(page, total_record, count) = offset_to_page(offset, count, total_record);
return (datas, page, total_record, count);
since_id分页应用(count, prev_id, next_id):
total_record = sql_to_count(sql);
(datas, offset, count, prev_id, next_id) = mysql_paginate_query(sql, NULL, count, prev_id, next_id);
return (count, prev_id, next_id);
复合型分段式分页应用(page, ping, page_ping, count, prev_id, next_id):
total_record = sql_to_count(sql);
(offset, count) = ping_to_offset(page, ping, page_ping, count, total_record)
(datas, offset, count, prev_id, next_id) = mysql_paginate_query(sql, offset, count, prev_id, next_id);
(page, ping, page_ping, total_record, count) = offset_to_ping(offset, page_ping, count, total_record);
return (datas, page, ping, page_ping, total_record, count, prev_id, next_id);
复合型传统分页应用(page, count, prev_id, next_id):
total_record = sql_to_count(sql);
(offset, count) = page_to_offset(page, count, total_record)
(datas, offset, count, prev_id, next_id) = mysql_paginate_query(sql, offset, count, prev_id, next_id);
(page, total_record, count) = offset_to_page(offset, count, total_record);
return (datas, page, total_record, count, prev_id, next_id);
mysql_paginate_query(sql, offset, count, prev_id, next_id)
need_offset = is_null(offset);
need_since = is_null(prev_id) || is_null(next_id);
sql_components = sql_parser(sql);
if ( need_offset ) :
sql_components = sql_add_offset(sql_components, offset, count);
endif
if ( need_since ) :
sql_components = sql_add_since(sql_components, prev_id, next_id);
endif
sql = sql_restore(sql_components);
datas = mysql_execute(sql);
(prev_id, next_id) = datas_boundary(sql_components, datas);
ret = (datas);
if ( need_offset ) :
append(ret, offset, count);
endif
if ( need_since ) :
append(ret, prev_id, next_id);
endif
return (ret);
7. 测试点
1) 传统分页
2) 分段分页
3) 原始分页
4) since_id分页
5) 复合型传统分页
6) 复合型分段分页
7) 复合型原始分页
8. 测试数据构建
DROP DATABASE IF EXISTS `paginate_test`;
CREATE DATABASE IF NOT EXISTS `paginate_test`;
USE `paginate_test`;

DROP TABLE IF EXISTS `feed`;
CREATE TABLE IF NOT EXISTS `feed` (
`feed_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '微博ID',
`ctime` INT NOT NULL COMMENT '微博创建时间',
`content` CHAR(20) NOT NULL DEFAULT '' COMMENT '微博内容',
`transpond_count` INT NOT NULL DEFAULT 0 COMMENT '微博转发数'
) COMMENT '微博表';

DROP TABLE IF EXISTS `comment`;
CREATE TABLE IF NOT EXISTS `comment` (
`comment_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '评论ID',
`content` CHAR(20) NOT NULL DEFAULT '' COMMENT '评论内容',
`feed_id` INT NOT NUL COMMENT '被评论微博ID'
) COMMENT '评论表';

DROP TABLE IF EXISTS `hot`;
CREATE TABLE IF NOT EXISTS `hot` (
`feed_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '微博ID',
`hot` INT NOT NULL DEFAULT 0 COMMENT '微博热度'
) COMMENT '热点微博表';
9. 测试用例:
1) 搜索最热微博(SELECT f.feed_id, f.content, h.hot FROM feed AS f JOIN hot AS h ON f.feed_id = h.feed_id ORDER BY hhot DESC, f.feed_id DESC)
2) 搜索热评微博(SELECT f.feed_id, f.content, COUNT(c.*) AS count FROM feed AS f JOIN comment AS c ON f.feed_id = c.feed_id GROUP BY c.feed_id ORDER BY count DESC, f.feed_id DESC)
3) 搜索热转微博(SELECT feed_id, content, transpond_count FROM feed ORDER BY transpond_count DESC, feed_id DESC)
4) 上面3种场景均测试7个测试点
10. 文件列表
readme.txt 当前您正在阅读的开发文档
page.lib.php 分页程序库
test_base.php 单元测试基础函数
test_convert.php 不同分页之间的转换单元测试
test_parse.php SQL语句解析测试
test_page.php 分页测试

[tottyandbaty]

不错~ 学习了

[goosman]

[Quote=引用 10 楼 的回复:]

其实我一直都不明白你弄了那么一大堆代码是在干什么
既然 since_id 方式是为了只查询历史数据而防止新增数据对其的干扰
那么只需在 sql 中加入 id < since_id 不就可以了吗？
[/Quote]

因为since_id其实只是对排序规则的描述, since_id在很多情况下并不是唯一列.
我们需要的since_id是能够唯一标识顺序的.
比如: 热点微博的应用中, 热度值就不是这样的列, 如果仅用热度值做这个since_id, 那么下一次来的时候, 如果该热度有1000条feed, 你应该读哪一条呢?
因此,since_id是需要根据order by子句进行动态构建的

[goosman]

[Quote=引用 10 楼 的回复:]

其实我一直都不明白你弄了那么一大堆代码是在干什么
既然 since_id 方式是为了只查询历史数据而防止新增数据对其的干扰
那么只需在 sql 中加入 id < since_id 不就可以了吗？
[/Quote]

因为since_id其实只是对排序规则的描述, since_id在很多情况下并不是唯一列.
我们需要的since_id是能够唯一标识顺序的.
比如: 热点微博的应用中, 热度值就不是这样的列, 如果仅用热度值做这个since_id, 那么下一次来的时候, 如果该热度有1000条feed, 你应该读哪一条呢?
因此,since_id是需要根据order by子句进行动态构建的

[xuzuning]

是的，我并没有理解你的思路
不过只要前提是“防止新增数据对其的干扰”，那么只要后续页只在 <= max(id) 范围里检索就可以了

[goosman]

[Quote=引用 14 楼 的回复:]

是的，我并没有理解你的思路
不过只要前提是“防止新增数据对其的干扰”，那么只要后续页只在 <= max(id) 范围里检索就可以了
[/Quote]

比如:
每页请求两条数据, 有如下数据表A:
a | b | c
------
9 | 3 | 2
9 | 3 | 1
9 | 3 | 0
8 | 4 | 5
8 | 3 | 5
8 | 2 | 6
7 | 1 | 5
------
那么
第一页的查询语句为:
SELECT * FROM A ORDER BY a DESC, b DESC, c DESC;
第一页得到的数据为
9 | 3 | 2
9 | 3 | 1
当需要请求第二页时, 此时的next_id(即since_id)应该需要携带9, 3, 1这3个信息才能唯一确认位置(在某些情况下甚至还不能确定唯一位置).
因此这个since_id在我写的代码中将被生成为: "__o_0:9|__o_1:3|__o_2:1".
当以它作为next_id传入时, 解析后转换得到的SQL语句应该是:
SELECT *, a AS __o_0, b AS __o_1, c AS __o_2 FROM A ORDER BY __o_0 DESC, __o_1 DESC, __o_2 DESC HAVING (__o_0 < 9) OR (__o_0 = 9 AND __o_1 < 3) OR (__o_0 = 9 AND __o_1 = 3 AND __o_2 < 1);
这种since_id的生成和SQL语句的构建如果在每种应用中做, 显然是一种重复, 麻烦的事情, 因此, 我们需要通过一层封装, 把这种行为隐藏起来, 让应用开发工程师更容易的使用各种分页, 而不关心细节.

[goosman]

[Quote=引用 14 楼 的回复:]

是的，我并没有理解你的思路
不过只要前提是“防止新增数据对其的干扰”，那么只要后续页只在 <= max(id) 范围里检索就可以了
[/Quote]
上面我列的例子里ORDER BY子句只有3列, 那么当有10列的时候呢? 我们还要手动干这件事吗?
这就是这段代码的由来.

此外, 代码中提供了sql_to_count的支持, 我们在分页时, 很多情况下需要知道总记录数, 如果考虑从数据库读取, 那么就需要单独构造一条count(xxx)的sql语句, 而这件事其实也可以是根据SQL的语法自动构造的.

[xuzuning]

这是奇怪的思路

[goosman]

[Quote=引用 17 楼 的回复:]

这是奇怪的思路
[/Quote]

可能徐老大还没有彻底理解我所说的场景.
不过这样做确实能够解决问题, 而且可以解决的很漂亮.
不过限于我编译知识的欠缺, 所做的sql解析部分只能处理不很复杂的SQL语句.

[yiwusuo]

这个可是好东东，先赞一个。

[蹲坑看月亮]

什么也不说了，先收在说~~

[蹲坑看月亮]

刚刚看完 ，哇卡卡，LZ高才呀~~~

[easywang369]

技术在于分享！～

[helloyou0]

嗯, 鼓个小掌...楼主一向是csdn一条好汉,就是平时来得不多..

我觉得这问题还是sql复杂了分析起来很难.....
比如要根据别的join上的表里的数据做排序之类
order by (select count(*) from othertable where .....) 这样的...

[goosman]

[Quote=引用 19 楼 的回复:]

嗯, 鼓个小掌...楼主一向是csdn一条好汉,就是平时来得不多..

我觉得这问题还是sql复杂了分析起来很难.....
比如要根据别的join上的表里的数据做排序之类
order by (select count(*) from othertable where .....) 这样的...
[/Quote]
呵呵, 上学的时候没有学好编译原理, 只是自己随便做做.
楼上列的order by (select count(*) from othertable where .....)这种子查询在语法分析阶段可以递归去做.

[goosman]

我没有推介产品!!!
不想多说了...

[xuzuning]

无聊了
本想测试一下你的代码，结果发现原来是残废的
非但没有测试数据，连代码都还有错

[goosman]

[Quote=引用 22 楼 的回复:]

无聊了
本想测试一下你的代码，结果发现原来是残废的
非但没有测试数据，连代码都还有错
[/Quote]

不是吧?
我的test_page.php, test_convert.php, test_parser.php都是完整可运行的测试用例, 其中test_page.php每次运行都会自动创建运行数据, 保证数据多样性.

还请徐老大多再细看看...我发东西还不至于发没经过测试的东西.

[xuzuning]

test_page.php 中有
require dirname(__FILE__) . '/test_base.php';
test_base.php 中有
require dirname(__FILE__) . '/page0.lib.php';
而你的包中没有 page0.lib.php 只有 page.lib.php
虽然可以假定两者是一样的，但除了你谁能认定呢？

运行时还会报 /tmp/__paginate_test_tmp.sql 不存在
事实上也不可能存在的，他只在你的机器中

[goosman]

page0.lib.php这个文件名的问题, 是我写错了, 因为我还在往我们的应用上用..
但是这个问题作为一个开发是应该很容易解决的.

/tmp/__paginate_test_tmp.sql是运行时生成的文件, 如果没有说明权限不正确

你是一个研发, 代码都给你了, 你还搞不定?
每个人在用那些开源框架的时候, 都会碰到各种问题, 难道都去找原作者解决? 研发人员自身就没有能力解决? 为什么别人没问题? 您作为php大版主, 技术能力应该不差吧? 这也解决不了?

是挑毛病呢? 还是挑毛病呢?
要挑您也挑一些程序逻辑上的有技术含量的好吗?

崩溃....!!!!!!

[xuzuning]

笑话！你是在推介你的产品呀，连用户提出疑问都不可以的吗？

毛病总是要挑的，但也的确然我预想的结果是和你运行的一样才行
否则你不又要说我“没有能力”了

你的代码在我这里确实是运行不起来。如果我把它修正成可以运行的话。很有可能就不是你期望的结果了，而且连讨论的基础也没有了