引用《高性能Mysql》中的一段话作为开始:
通过上述话可以看出,延迟关联在大分页查询的场景下,可以提高查询效率
延迟关联是什么?
通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据
select id,a,b,c from table INNER JOIN (select id from table where xxx limit i,j) Using(id);
关键点:
- 需要覆盖索引【简单解释一下覆盖索引:select字段和where字段在一棵索引树上全覆盖,不用回表,速度更快---毕竟是内存处理;回表可能是读磁盘,很慢的】,而这里覆盖指的是where全覆盖即可,如果连select都覆盖了,那也就不用再使用延迟联合进行优化了
- 存在大分页查询的业务场景【在做分页时会用到Limit关键字去筛选所需数据,当偏移量很大时,如limit 100000,10 取第100001-100010条记录,mysql会取出100010条记录然后将前100000条记录丢弃,这无疑是一种巨大的性能浪费】
为什么延迟关联能够针对此类场景进行优化?
如果按照以下sql语句进行查询
select id,a,b,c from table where xxx limit 100000,100010;
mysql的执行逻辑
- 通过索引找到100010条符合查询条件的记录id(在内存中操作很快)
- 再通过100010个id回表查询这100010条记录(磁盘操作)--- 回表100010次
- 最后丢弃100000条数据,按分页条件给用户返回10条记录
如果使用延迟关联进行改进
select id,a,b,c from table INNER JOIN (select id from table where xxx limit 100000,100010) Using(id);
mysql的执行逻辑
- 通过索引找到100010条符合查询条件的记录id(在内存中操作很快)
- 在内存中按分页条件取10个记录id,然后回表查询这10条记录(磁盘操作)--- 回表10次
- 最后给用户返回10条记录
优化点:延迟关联通过减少了大量的回表查询,大大的提升了sql查询效率
注:如果不在覆盖索引的条件下,这种优化是没有效果的,因为都是在进行磁盘操作,两种方式无异