锐客网

  1. 首页 > 睿知 > it技术 > >

mysql分页怎么实现 mysql分页实现原理( 三 )

IT技术位域


这将主要与 MySQL 对话，因为那是我有经验的地方 。其他数据库中的情况可能会有所不同 。
大多数开发人员习惯于为单列添加索引，但没有什么能阻止您向多列添加索引 。事实上 ， 如果您的目标是为昂贵的分页查询创建覆盖索引，您几乎肯定需要一个多列索引 。
当你试图为分页优化一个索引时，一定要把按列排序放在最后 。如果你的用户要按update_at排序，这应该是你复合索引中的最后一列 。
看看下面这个包括三列的索引 。
在MySQL中，复合索引是从左到右访问的 ， 如果一个列缺失，或者在第一个范围条件之后，MySQL会停止使用一个索引 。
MySQL 将能够在以下场景中使用该索引：
如果你跳过is_archived ， MySQL将无法访问update_at，将不得不诉诸于没有该索引的排序 ， 或者根本不使用该索引，所以要确保你有相应的计划 。
主键始终存在
在MySQL的InnoDB中，所有的索引都附加了主键 。这意味着（email）的索引实际上是（email，id）的索引，当涉及到覆盖索引和延迟连接时，这是相当重要的 。
查询select email from contacts order by id完全被email上的一个索引所覆盖，因为InnoDB将id附加到了该索引上 。
使用我们上面的综合例子 ， 你可以看到这有什么好处 。
因为复合索引涵盖了is_deleted, is_archived, updated_at, 和（通过InnoDB的功能）id，整个查询可以仅由索引来满足 。
降序索引
大多数时候，用户都在寻找 "最新的 "项目，即最近更新或创建的项目，这可以通过按update_at DESC排序来满足 。
如果你知道你的用户主要是以降序的方式对他们的结果进行排序，那么特别将你的索引设为降序索引可能是有意义的 。
MySQL 8是第一个支持降序索引的MySQL版本 。
如果你在explain的Extra部分看到向后索引扫描，你也许可以配置一个更好的索引 。
前向索引扫描比后向扫描快~15%，所以你要按照你认为你的用户最常使用的顺序添加索引，并为少数使用情况承担惩罚 。
太阳底下无新事
这种使用偏移/限制分页与延迟连接和覆盖索引的方法并不是银弹 。
仅仅是递迟连接就可以让你的速度得到很好的提升，但是需要花一些额外的心思来设计正确的索引以获得最大的好处 。
有一种观点认为 ， 递延连接应该是框架中默认的偏移offset/限制limit方法，而任何时候覆盖索引的出现都只是一种奖励 。我还没有在足够多的生产环境中测试过，所以还没有强烈主张这样做 。
使用MySQL的递延Join连接实现高效分页 - Aaron
mysql如何做分页查询？很多应用往往只展示最新或最热门的几条记录，但为了旧记录仍然可访问，所以就需要个分页的导航栏 。然而，如何通过MySQL更好的实现分页，始终是比较令人头疼的问题 。虽然没有拿来就能用的解决办法，但了解数据库的底层或多或少有助于优化分页查询 。
我们先从一个常用但性能很差的查询来看一看 。
SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15
这个查询耗时0.00sec 。So，这个查询有什么问题呢？实际上 ， 这个查询语句和参数都没有问题，因为它用到了下面表的主键 ， 而且只读取15条记录 。
CREATE TABLE city (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
city varchar(128) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
真正的问题在于offset(分页偏移量)很大的时候，像下面这样：
SELECT *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
上面的查询在有2M行记录时需要0.22sec，通过EXPLAIN查看SQL的执行计划可以发现该SQL检索了100015行，但最后只需要15行 。大的分页偏移量会增加使用的数据，MySQL会将大量最终不会使用的数据加载到内存中 。就算我们假设大部分网站的用户只访问前几页数据，但少量的大的分页偏移量的请求也会对整个系统造成危害 。Facebook意识到了这一点 ， 但Facebook并没有为了每秒可以处理更多的请求而去优化数据库，而是将重心放在将请求响应时间的方差变小 。

推荐阅读


上一篇：oracle中循环查询语句，oracle 循环查询结果集

下一篇：linux服务器分区，linux服务器分区使用率突然增涨