这篇文章主要为大家详细介绍了 Mysql 高效分页的相关资料,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
MySQL 是一个开放源码的小型关联式数据库管理系统,开发者为瑞典 MySQL AB 公司。MySQL 被广泛地应用在 Internet 上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。
前言
通常针对 MySQL 大数据量的查询采取 "分页" 策略,但是如果翻页到比较靠后的位置时查询将变得很慢,因为 mysql 将花费大量的时间来扫描需要丢弃的数据。
基本分页技巧
通常情况下,为了实现高效分页,需要在查询中 WHERE 条件列和排序列应用组合索引。
例如,建立索引 (a,b,c) 使得以下查询可以使用索引,提高查询效率:
1、字段排序
- ORDER BY a ORDER BY a,
- b ORDER BY a,
- b,
- c ORDER BY a DESC,
- b DESC,
- c DESC
2、筛选和排序
- WHERE a = const ORDER BY b, c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b, c
- WHERE a = const AND b > const ORDER BY b, c
3、下面查询是无法使用以上索引的
- ORDER BY a ASC,
- b DESC,
- c DESC //排序方向不一致
- WHERE g = const ORDER BY b,
- c // 字段g不是索引一部分
- WHERE a = const ORDER BY c //没有使用字段b
- WHERE a = const ORDER BY a,
- d // 字段d不是索引的一部分
解决大数据量翻页问题
1、将 LIMIT M,N 的查询改为 LIMIT N
例如,使用 LIMIT 10000,20,Mysql 将需要读取前 10000 行,然后获取后面的 20 行 ,这是非常低效的,使用 LIMIT N 的方式,通过每页第一条或最后一条记录的 id 来做条件筛选,再配合降序和升序获得上 / 下一页的结果集 。
2、限制用户翻页数量
产品实际使用过程中用户很少关心搜索结果的第 1 万条数据。
3、使用延迟关联
通过使用覆盖索引来查询返回需要的主键,再根据返回的主键关联原表获得需要的行,这样可以减少 Mysql 扫描那些需要丢弃的行数。
实例:
使用索引 (sex,rating) 进行查询:
- mysql> SELECT <cols> FROM profiles INNER JOIN (
- -> SELECT <primary key cols> FROM profiles
- -> WHERE x.sex='M' ORDER BY rating LIMIT 100000, 10
- -> ) AS x USING(<primary key cols>);
来源: