Advanced Pagination for MySQL(mysql高级分页)

在叶子的文章里谈到了使用inner join 从而减少了对page的扫描也就是减少了所谓的回表 例如:

代码如下:

SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1`ORDER BY id DESC LIMIT 935500,10) t2 USING (id)

通过直接对id的操作 而不是整张表的扫描 通过id 的join 抓出符合条件id 然后通过ID 再去做数据的抓取。这样就避免了对不需要的页面的扫描。

不过这样也不是最佳的方法 还可以通过对id 的 range更加缩小范围 例如:

我们要分100条记录分一页 可以写成

代码如下:

$page_size=100 select * from t where id > 99 order by id asc limit $page_size ; select * from  t where id >199 order by id asc limit $page_size;

尽量避免limit M,N 这种写法 mysql在对M值很大 而offset很小的时候的处理方式很不人性化 ,所以尽量不要使用offset来取得特定行数。

在这里有一个问题 比如根据不是唯一索引的column分页 那么可能存在一个问题,例如一个列column1存在11个key=100的值 那么你使用limit N 之后取到的min value还是同一个值
这种情况如何处理? 给个例子:

比如要每10条记录分一页

代码如下:

select * from t  order by column1 desc  limit 10

注意这里取到的min value还是100 (11个连续的100) 对下面的分页会产生影响,如何处理?

雅虎给出的方案非常好 取一个extra的column 例如PK 或者unique index key 例如:

代码如下:

select * from t  order by column1 desc, id desc   limit 10 -- 第一个页
select * from t  where column1 <=minvalue_col1 and (id < minvalue_id or column1 < minvalue_col1) limit 10  ---第二个页

这样就确保了唯一性 保证了每页的数据不会重复 思想就是通过add一个唯一的extra 取得这个extra的边界值 结合range column来进行分页。

这个SQL 还能被优化成:

代码如下:

SELECT m2.* FROM t m1, t m2  WHERE m1.id = m2.id  AND m1.column1 <= minvalue_col1
AND (m1.id < minvalue_id OR m1.column1 < minvalue_col1)  ORDER BY m1.column1 DESC, m1.id DESC  LIMIT 10;

核心思想: 通过extra过滤 配合ID扫描 避免大量的回表操作 这样就达到了要取多少条 就扫描多少条 (in page)

(0)

相关推荐

  • golang通过mysql语句实现分页查询

    目录 1.前端接口调用 2.register访问入口 3.解析参数 4.service实现 5.mapper实现 1.前端接口调用 2.register访问入口 //查询一个用户下所有的subnet ws.Route(ws.GET("/subnets"). To(sc.ListSubnet). Doc("List subnets authorized to the login user."). Param(ws.QueryParameter(query.Parame

  • Mysql数据库高级用法之视图、事务、索引、自连接、用户管理实例分析

    本文实例讲述了Mysql数据库高级用法之视图.事务.索引.自连接.用户管理.分享给大家供大家参考,具体如下: 视图 视图是对若干张基本表的引用,一张虚表,只查询语句执行结果的字段类型和约束,不存储具体的数据(基本表数据发生了改变,视图也会跟着改变),方便操作,特别是查询操作,减少复杂的SQL语句,增强可读性. 1.----创建视图: create view 视图名称(一般以v_开头) as 查询语句; 2.----查看视图: select * from 视图名称; 3.----删除视图: dro

  • MySQL数据库高级查询和多表查询

    MySQL多表查询 添加练习表 -- 用户表(user) CREATE TABLE `user`( `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户id(主键)', `username` VARCHAR(50) COMMENT '用户姓名', `age` CHAR(3) COMMENT '用户年龄' ); -- 订单表(orders) CREATE TABLE `orders`( `id` INT AUTO_INCREMENT PRIMARY KEY

  • Mysql排序和分页(order by&limit)及存在的坑

    排序查询(order by) 电商中:我们想查看今天所有成交的订单,按照交易额从高到低排序,此时我们可以使用数据库中的排序功能来完成. 排序语法: select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc|desc]; 需要排序的字段跟在order by之后: asc|desc表示排序的规则,asc:升序,desc:降序,默认为asc: 支持多个字段进行排序,多字段排序之间用逗号隔开. 单字段排序 mysql> create table test2(

  • MySQL排序与分页讲解

    目录 一.order by 二.二级排序 三.分页limit 一.order by 使用 ORDER BY 对查询到的数据进行排序操作.升序:ASC (ascend)                    从低到高降序:DESC (descend)               从高到低 eg: 1.对stu表中的成绩(grade)从高到低排序 2.对stu表中的成绩(grade)从低到高排序 1.select * from stu order by drade desc; 2.select * 

  • 浅谈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数据库高级数据操作之新增数据

    目录 多数据插入 主键冲突 1.主键冲突更新 2.主键冲突替换 蠕虫复制 多数据插入 只要写一次insert,可以插入多条数据 基本语法: insert into 表名 [(字段列表)] values (值列表), (值列表)...; create table my_student( id int primary key auto_increment, name varchar(10) ); insert into my_student (name) values ('张三'), ('李四'),

  • MySQL中实现分页操作的实战指南

    目录 一. 背景 二. 实现规则 2.1 关键字 LIMIT 2.2 查询数据库表的第 x x x 条记录 2.3 WHERE…ORDER BY…LIMIT… 的声明顺序 (非执行顺序) 三. 拓展 总结 一. 背景 什么是分页,就是查询时候数据量太大,一次性返回所有查询结果既耗费网络资源.又降低了查询效率,用户也不可能一下子看完成千上万条数据.所以分页的技术就应运而生.分页可以只显示指定数量的数据. 分页在我们的生活中随处可见,如下图所示的电商网站: 二. 实现规则 2.1 关键字 LIMIT

  • Mysql给普通分页查询结果加序号实操

    目录 一.效果展示 1.普通查询加序号 2.分页查询加序号 二.表结构以及数据 三.解释说明 一.效果展示 1.普通查询加序号 SELECT t1.NAME,( @i := @i + 1 ) AS '序号' FROM t1,( SELECT @i := 0 ) AS itable; 这种情况遇上分页时会出现每次翻页都从1开始重新计算,这种情况可以使用分页偏移量作为开始计算数,解决方案如下: 2.分页查询加序号 SELECT ( @i := @i + 1 ) AS '序号' ,t1.NAME FR

  • 浅析Mysql和Oracle分页的区别

    目录 MySQL使用limit进行分页 Oracle使用rownum进行分页 Mysql与Oracle级联查询 Mysql省市区级联查询 Mysql与Oracle插入数据存在修改不存在新增 MySQL使用limit进行分页 select * from stu limit m,n; // m=(pageIndex-1)*pageSize,n=pageSize -- 返回总条,查询表添加字段sql_calc_found_rows select sql_calc_found_rows a.* from

随机推荐