MySQL分页查询优化

CREATE TABLE teacher(
 	`id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`teacher_id` CHAR(30) NOT NULL UNIQUE KEY,
 	`name` VARCHAR(30) NOT NULL
)ENGINE=INNODB;

insert into teacher(teacher_id, name) values('aaa', 'aaa');
insert into teacher(teacher_id, name) values('bbb', 'bbb');
insert into teacher(teacher_id, name) values('ccc', 'ccc');
insert into teacher(teacher_id, name) values('ddd', 'ddd');
insert into teacher(teacher_id, name) values('eee', 'eee');

# 查询得到a+b条记录并回表
select * from teacher order by id desc limit 3, 2;

# 减少扫描行数
# teacher_id是分页查询请求参数，表示上一页最后一条记录，只有查询第一页不带
select id from teacher where teacher_id = 'ccc' limit 1;
select * from teacher where id < 3 order by id desc limit 2;