关于order by limit执行计划索引使用不同 基于5.6版本

SELECT * from test where user_id = '21' AND STATUS = '1' ORDER BY create_time LIMIT 0,20

语句中user_id 和create_time 均有单独索引

最近发现一个语句使用 order by create_time limit 0,20 效率奇差，通过对比执行过程发现加limit时和不加limit时使用所用不一致

在使用limit时 执行计划中显示索引使用的时create_time索引，而在Extra显示中Using where

表明该逻辑下默认通过create_time索引 根据索引树从最右侧叶子节点依次按顺序获取n条与where条件进行过滤，直到获取20条数据，并没有真正通过索引去筛选数据,而是作为where条件筛选，不过该执行计划应为通过create_time为索引order by 筛选不进使用 Using filesort文件排序，只是进行where获取匹配数据，但是由于根据时间排序筛选从最右侧叶子节点反向获取，如果该数据条件均不满足where会继续获取，直到满足20条，这样就会倒是访问数据量过大

执行顺序是：order by --> limit --> where

注：该语句中create_time只进行order by排序，并没有任何条件判断

在没有使用limit 时执行计划会使用user_id索引，进行条件筛选，在Extra中显示Using index condition;Using where ;Using filesort;  优化器首先解析索引列；通过索引列（user_id）找出表中行数据，在获取其他where条件进行筛选(索引向下)，获取到与条件匹配行后进行order by排序，由于没有使用排序索引索引使用了Using filesort进行文件排序，【一般情况下文件排序会导致增加消耗，查询时缓慢，不过不能一概而论】，最后根据limit 获取指定行数

执行顺序是：where --> order by --> limit

每个索引在数据库中都是一个索引树，其数据节点存储了指向实际数据的指针，如果用一个索引来查询，其原理就是从索引树上去检索，并获得这些指针，然后去取出数据，

如果通过一个索引，得到过滤后的指针，这时，你的另一个条件索引如果再过滤一遍，将得到2组指针的集合，

如果这时候取交集，未必就很快，因为如果每个集合都很大的话，取交集的时候，等于扫描2个集合，效率会很低，所以没法用2个索引。

当然有时候mysql会考虑临时建立一个联合索引，将2个索引联合起来用，但是并不是每种情况都能奏效，同样的道理，用一个索引检索出结果集之后，排序时，也无法用上另一个索引了。

同样，在我的语句中，只有一个索引条件，而时间仅用于排序

在不同情况下优化器选择索引不同，优化需根据不同场景进行优化，测试语句中，查询的总数据量是2条，但如果索引选择create_time 进行根据时间获取耗时时间过长，但如果使用dealpersonId索引，这样根据索引获取数据相对会快很多，虽然最后因为要是filesort，但相对数据量较小，消耗不大

如果有什么错误问题，希望指明