一、业务场景

先看看以下关于查询用户订单的慢SQL的问题该如何优化：

sql性能优化问题，以下是在1000w+的订单表中都是查询50条数据，但是随着offset的增加,就出现了查询时间越来越长（其中 在t_order表中 user_id字段已创建索引）：

SELECT * FROM t_order WHERE user_id = 1001 LIMIT 5, 50;
查询时间:0.25s
-------------------------------------------------------
SELECT	* FROM	t_order WHERE user_id = 1001 LIMIT 500000, 50
查询时间:57.252s
-------------------------------------------------------
SELECT	* FROM	t_order WHERE user_id = 1001 LIMIT 1000000, 50
查询时间:89.15s

先不要往下面看，思考一下为什么user_id创建了索引且都是查50条数据为什么性能差距会那么大？

二、问题分析

从上面3条sql语句上分析很明显发现明显是limit问题，先来了解一下limit的工作原理：
1.先查询offset+count条数据；
2.再抛弃前offset条数据。
看起来是查询50条，但是随着offset越大，那么扫描的行数就越多，耗时也及越长。刚好和上面3条sql结果吻合。
那还会有其他原因么，来看一下下面这条sql及它的执行时间：
-------------------------------------------------------
SELECT	* FROM	t_order LIMIT 1000000, 50
查询时间:19.75s
-------------------------------------------------------
同样的limit用user_id索引条件检索反而比没有条件检索的慢了将近4倍多，这是什么原因呢？
先给出结论 是回表的问题

三、回表代价

回表的代价：
1. 索引分聚簇索引和辅助索引，其中聚簇索引的叶子节点是数据，而辅助索引的叶子节点是主键；
2. 那么用辅助索引来检索数据的过程就是：先在辅助索引的B+树上检索到目标的叶子节点即获取到主键值，
然后再用主键值到聚簇索引的B+树上查询完整的数据，这个过程就叫做回表；
3. 其中辅助索引上的检索是顺序I/O，访问聚簇索引使用随机I/O；
4. 具体过程如下图所示：

根据以上分析，以上sql慢的主要原因是超多次的回表问题，解决方案就要减少它回表的次数。

四、解决方案

优化后sql及优化后的执行结果：
-----------------------------------------------
SELECT	* FROM	t_order t
RIGHT JOIN (
	SELECT id
	FROM t_order
	WHERE user_id = 1001
	LIMIT 1000000,50
) tmp ON tmp.id = t.id
查询时间：0.64
-----------------------------------------------
对以上优化说明：
1. 在tmp中 直接用 user_id 在辅助索引上就可以找到id，这个过程是顺序IO效率非常高，不会有回表的开销，这个也叫索引覆盖；
2. 再用临时表tmp和t_order表通过id查询数据，这个过程是在聚簇索引就能直接找完整的数据。

总结

1. 要警惕大offset的limit分页，它是非常消耗性能，它也是黑客攻击系统的一种方式，如果工作中有遇到该问题建议是从业务上优化（如 只有上一页、下一页等）；
2. 回表开销非常大，它会有2次I/O开销（辅助索引顺序I/O、聚簇索引随机I/O），当数据量够大时它的效率甚至比全表扫描都要慢，也是索引失效的一种场景；
3. 回表的优化方式就是索引覆盖，索引覆盖。