Mysql order by索引的问题

作者:admin 发布时间:November 7, 2013 分类:默认

网上看了一篇关于order by ,说是只要字段设置了索引,直接采用该字段单一排序并且select 内容是主键或者是该索引字段就能使用索引,而不是filesort。  自己试了一下完全不是那么回事,order by 完全还是不能使用索引。 必须使用 order by 的字段+主键字段,做以先字段后主键为序做联合索引才能让该查询使用索引。

看网上教程例子和代码都很好,不像是胡编乱造的。仔细的看了下,例子上建表用的是innodb,难道是因为我的表示myisam的问题。  立马去把自己的表转成innodb。再试果然可以了。 看来myisam 索引上和innodb还是有差异的。

2013,11.8 今日继续研究,设置了联合所以(w1+w2+o1)  说明w*表示where查询用的字段, o*表示排序字段。

用该查询select id where w1=1 and w2=1 order by o1 成功使用所以无filesort, 改为 select * 仍然很顺利使用索引无filesort。 看来这种情况下select 不影响索引使用。 接着去掉查询中去掉w2 ,果然无法使用索引了。 看来必须完整使用索引中顺序并全部使用才能顺利使用。

接着因为实际需要简化了下索引设置为(w1+o1),去掉了w2 。同时查询也一样简化 ,嘿 ,问题来了,filesort出现了。它真的出现了,这就怪了,明明和上面一样啊,只是更简单了,反而就不能用索引了。查询改成 where w1=0 奇迹出现了,又正常了,因为表里w1字段值全部是1 所以w1=0 其实是什么都查不到的,但索引工作却正常了,接着改动一条数据把w1改成0 ,用w1=1查询还是不能使用索引。接着设置更改一半w1值为0 ,再次测试发现索引正常了。看来这个索引使用还是要看数据的,如果查询内容已经包含或者包含了99%的内容,那么这个索引用不用其实没有多大必要了,默认也就不用了。 所以重要一点是索引一定要用在点子上,否则只是徒增长磁盘空间而已。不过这点也很容易让数据调试的时候产生误会。

再次测试。如果w 在非固定值的时候,而是w>2 这样情况下,所以能用,filesort无法避开。

经测试mysql myisam引擎下,> < between ,都无法使用索引,innodb则能使用索引。 由innodb切换到myisam后发现又能使用索引了,我觉得mysql在玩我。

暂时测试 在(w1+w2+o1)的情况下,只要w字段不使用固定值,filesort均无法避免。

找到个避免filesort的法子,innodb下有效,where w1=1 and w2>1 order by o1 是无法避免filesort的。

但是 where w1=1 and w2>1 AND O1 > 1 order by o1 FILESORT居然消失了。