Mysql 延迟关联

引用《高性能Mysql》中的一段话作为开始：

通过上述话可以看出，延迟关联在大分页查询的场景下，可以提高查询效率

 

延迟关联是什么？

通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据

select id,a,b,c from table INNER JOIN (select id from table where xxx limit i,j) Using(id);

关键点：

1. 需要覆盖索引【简单解释一下覆盖索引：select字段和where字段在一棵索引树上全覆盖，不用回表，速度更快---毕竟是内存处理；回表可能是读磁盘，很慢的】，而这里覆盖指的是where全覆盖即可，如果连select都覆盖了，那也就不用再使用延迟联合进行优化了
2. 存在大分页查询的业务场景【在做分页时会用到Limit关键字去筛选所需数据，当偏移量很大时，如limit 100000,10 取第100001-100010条记录，mysql会取出100010条记录然后将前100000条记录丢弃，这无疑是一种巨大的性能浪费】

 

为什么延迟关联能够针对此类场景进行优化？

如果按照以下sql语句进行查询

select id,a,b,c from table where xxx limit 100000,100010;

mysql的执行逻辑

1. 通过索引找到100010条符合查询条件的记录id（在内存中操作很快）
2. 再通过100010个id回表查询这100010条记录（磁盘操作）--- 回表100010次
3. 最后丢弃100000条数据，按分页条件给用户返回10条记录

 

如果使用延迟关联进行改进

select id,a,b,c from table INNER JOIN (select id from table where xxx limit 100000,100010) Using(id);

mysql的执行逻辑

1. 通过索引找到100010条符合查询条件的记录id（在内存中操作很快）
2. 在内存中按分页条件取10个记录id，然后回表查询这10条记录（磁盘操作）--- 回表10次
3. 最后给用户返回10条记录

优化点：延迟关联通过减少了大量的回表查询，大大的提升了sql查询效率

注：如果不在覆盖索引的条件下，这种优化是没有效果的，因为都是在进行磁盘操作，两种方式无异