MySQL中使用延迟关联优化order by加limit超大分页

创建一个student表插入200万条数据

CREATE TABLE `student` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(30) NOT NULL,
  `SEX` char(2) NOT NULL,
  `AGE` int(11) NOT NULL,
  `CLASS` varchar(10) NOT NULL,
  `GRADE` varchar(20) NOT NULL,
  `HOBBY` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

没有添加索引时以下语句执行需要1.263s，添加SEX, HOBBY索引后只需要0.368s，说明用到索引排序时效率提升比较明显

select name from student where sex='女' order by hobby limit 10;

但是当limit的偏移量很大时，执行效率依然很慢，如下语句需要执行5.177s

select name from student where sex='女' order by hobby limit 500000,10;

使用延迟关联优化后，只需要执行0.503s

select name from student s inner join (select id from student where sex='女' order by hobby limit 500000,10) d using(id)

延迟关联的原理：通过在二级索引上使用索引覆盖快速筛选出符合条件的10个id，再通过聚族索引回表查询这十条数据，这样就大大的减少存储引擎需要检索的数据量。而前面的语句就需要检索50万条数据丢掉再检索10条返回。