mysql 深分页问题（limit）

原因

偏移量越大，mysql就会扫描越多的行，然后在进行回表，然后在抛弃掉偏移量之前的行，之后在返回偏移量后边的行，这样就导致查询性能下降。
表结构

CREATE TABLE account (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  name varchar(255) DEFAULT NULL COMMENT '账户名',
  balance int(11) DEFAULT NULL COMMENT '余额',
  create_time datetime NOT NULL COMMENT '创建时间',
  update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (id),
  KEY idx_name (name),
  KEY idx_update_time (update_time) //索引
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
1
2
3
4
5
6
7
8
9
10

执行select * from account where update_time > "2010-09-18" limit 100000,10明显比执行select * from account where update_time > "2010-09-18" limit 0,10时间要久是因为，在通过二级索引查找到满足条件的记录的id时，需要在进行一次回表来获取所需要返回的字段的信息，之后在执行limit命令截取响应的记录。 limit 100000,10相较于 limit 0,10而言多回表了100000次。所以导致深分页变慢。

优化方案

一. 通过把条件转移到主键索引上，减少回表次数

1. 通过子查询减少回表次数

select * from account where id in (select id from (select id from account where update_time > "2010-09-18" limit 100000,10) a)
1

我们可以使用`select id from account where update_time > "2010-09-18" limit 100000,10`来获取到满足条件的记录的主键id,然后在通过select * from account 来获取要返回字段的信息。这样就避免了多次回表操作。
1

2. inner join延迟关联

select * from account account inner join (select id from account where update_time > "2010-09-18" limit 100000,10) er on er.id = account.id
1

3. 标签记录法
   上次扫描到那，下次来的时候在从那个位置开始扫描。相当于要使用一个连续的且不重复的id可以使用.

select * from account where id> 396655 and update_time > "2010-09-18" order by id limit 10;
1

想办法让标记的位置的列是有序的，并且不重复。然后就可以将偏移量转义到where语句中，来减少回表次数。