分页常适用于性能优化,如以下场景:
- 业务只需要获取部分数据,如top 10,此时查询所有数据到客户端,浪费数据库磁盘IO/网络IO、应用内存
- 数据量太大,传输时间可能过长甚至中断、查询到客户端内存开销巨大,每次只查询较小数量的行,分多次查询
MySQL中使用limit
实现分页查询。
基本语法
SELECT * FROM table
LIMIT {[offset,] row_count | row_count OFFSET offset}
如上所示,limit
可接受1或2个参数,参数必须是非负整数。
LIMIT offset, row_count
释义:从指定偏移位置开始返回,一共返回多少行
注意:第一行的偏移量为0,而不是1
例:SELECT * FROM tbl LIMIT 5,10
,表示从第6行开始返回、一共返回10行。
这是MySQL
分页最常见的写法。
LIMIT row_count
等价于LIMIT 0, row_count
。
LIMIT row_count OFFSET offset
为了兼容PostgreSQL,参见《PostgreSQL Limit分页》
例:LIMIT 10 OFFSET 5
等价于 LIMIT 5,10
推荐采用该方法,语义更明确、兼容性更好。
性能优化
执行器自动优化
MySQL
执行器会自动优化含LIMIT
的语句,包括但不限于如下情况:
limit
较少行时,可能会直接进行全表扫描、而不走索引limit
和order by
、group by
、distinct
同时使用时,排序/聚合后的行数,一旦达到limit
的row_count
,排序/聚合就会停止。因为这个特性,如果多个行的排序列值相同,那么使用LIMIT时其返回的顺序可能不同。
limit 0
会快速返回一个空结果集MySQL
一旦向客户端发送了所需的行数,它将立即终止查询
主动优化执行计划
加排序索引
limit
和order by
同时使用的概率非常高。如果排序列有索引,结合limit
找到所需行数会立刻返回的特性,查询效率非常高
例:
SELECT * FROM tbl WHERE rid = 123 ORDER BY id LIMIT 50, 10
建议增加rid、id的联合索引(rid在前,这样索引会先匹配rid、然后再按id的顺序获取数据)。
偏移量较大时,使用索引
偏移量较大时,MySQL
需要扫描过偏移量之前的所有行,然后再读取所需行返回,磁盘IO(随机IO)很多。因此分页查询越往后越慢。
如
SELECT * FROM tbl WHERE rid = 123 ORDER BY id LIMIT 100000, 10
实际上只需要10行数据,但是要在磁盘中遍历100000行后,才真正开始获取数据。
该情况可通过子查询优化:
SELECT * FROM
(SELECT id FROM tbl WHERE rid = 123 ORDER BY id LIMIT 100000, 10) temp
LEFT JOIN tbl t ON temp.id = t.id
通过子查询,可以直接在索引中获取到匹配行的主键。MySQL的索引是B+树,本身有序、并且节点中存放了主键,所以检索效率非常高。
这是索引覆盖的一个特例。当
explain
时extra
中出现using index
时,即表示实现了索引覆盖。
以上。感谢您的阅读。