使用场景
1.存在无法走索引的左通配符的模糊查询
2.高偏移值（limit）的查询
优化案例
eg.
原语句
SELECT * FROM t
WHERE
settle_serial_no LIKE '%xxxxxx%'
ORDER BY id DESC
limit 10000,5000 ;
执行计划如下图
采用延迟关联对原语句进行修改
SELECT * FROM t as a
JOIN
( SELECT id FROM t WHERE settle_serial_no LIKE '%xxxxxx%' order by id desc limit 10000,5000) as b
ON a.id = b.id ;
执行计划如下图
当子查询中存在limit偏移量时，执行计划显示子查询并不会走索引列上的索引，而是走主键
经过测试，当表数据量较小时无论是否走索引，执行耗时相近；但当表数据量较大时（过100W行就很明显了），强制走索引的效果比起让优化器选择走主键的效果更好，强制走索引的语句和执行计划如下
SELECT * FROM t as a
JOIN
( SELECT id FROM t force index(idx_settle) WHERE settle_serial_no LIKE '%xxxxxx%' order by id desc limit 10000,5000) as b
ON a.id = b.id ;
执行时间对比
不走延迟关联，执行时间：3.449s；
走延迟关联但不强制走索引，执行时间：0.91s；
走延迟关联且强制走索引，执行时间：0.334s；
eg.
原语句
SELECT *
FROM t
WHERE (t.`sender` LIKE '%xxxx%'
OR t.`receiver` LIKE '%xxxx%'
OR t.`goodses_no` LIKE '%xxxx%')
AND `company_id` = 41
AND t.`deleted_at` IS NULL
ORDER BY `id` DESC
LIMIT 0, 15;
增加索引
alter table t add index idx_ci_sender_da(company_id,sender,deleted_at);
alter table t add index idx_ci_gn_da(company_id,goodses_no,deleted_at);
alter table t add index idx_ci_receiver_da(company_id,receiver,deleted_at);
修改语句（or改union，延迟关联）
select * from
(
SELECT a.*
FROM t a
join
(select id from t where
t.`goodses_no` LIKE '%xxxx%'
AND `company_id` = 15
AND t.`deleted_at` IS NULL
ORDER BY `id` DESC
) b on a.id=b.id
union
SELECT a.*
FROM t a
join
(select id from t where
t.`receiver` LIKE '%xxxx%'
AND `company_id` = 15
AND t.`deleted_at` IS NULL
ORDER BY `id` DESC
) b on a.id=b.id
union
SELECT a.*
FROM t a
join
(select id from t where
t.`sender` LIKE '%xxxx%'
AND `company_id` = 15
AND t.`deleted_at` IS NULL
ORDER BY `id` DESC
) b on a.id=b.id
) c order by id desc
limit 0,15
执行计划如下
这个语句由于涉及到or改写成union，所以不能把limit写在连接的子查询语句里（需要对union完成之后的所有结果集进行排序后采用limit取值）；当子查询中没有limit的时候，优化器会自动去走这3个覆盖索引，无需强制索引
执行时间对比
不走延迟关联的执行时间为3.34s
走延迟关联，时间为0.162s
