MySQL 分页查询优化

分页实现

MySQL 使用 Limit 子句实现分页

select * from user limit [offset,] rows

• 第一个参数 offset 是可选的，指定第一个返回记录行的偏移量，如果不指定则默认从 0 开始
• 第二个参数 rows 指定返回记录行的最大数目

另一种实现语句如下

select * from user limit rows offset offset

参数作用同上

select * from user where limit 1000,10;

上述语句表示查询 1000 开始之后的 10 条记录，也就是第 1001 条记录到第 1010 条记录的数据

分页优化

上述的分页查询方式，随着偏移量的增大，查询效率会越来越慢，因为这种分页查询方式会从数据库的第一条记录开始扫描，再把前面不符合条件的记录抛弃，因此记录越往后，查询的速度就会越慢

针对上述情况，主要从两方面着手优化：

• 避免回表
• 缩小查询结果集

1. 使用覆盖索引

如果一条 SQL 语句，通过索引可以直接获取查询的结果，不再需要回表查询，就称这个索引为覆盖索引

对 user 表的 name 字段添加索引，SQL 语句如下：

select id,name from user order by name limit 1000000, 10;

2. 记录上次查询结束的位置

记录上一次分页查询最后数据的位置，下次分页查询直接从该位置开始扫描，避免扫描大量的数据再抛弃的问题，当然这要求 id 是有序的

select * from user where id>=1000000 limit 10;

#### 3. 使用子查询

先使用子查询定位偏移量位置的 id，然后再往后查询，同样要求 id 有序

```sql
select * from user where id>=(
  select id from user limit 1000000, 1) limit 10;