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,但相对数据量较小,消耗不大
如果有什么错误问题,希望指明