explain 关于 order by 与索引

create table if not exists log_role_gold_change(

`id` int(11) primary key auto_increment,

`username` varchar(60) character set utf8 not null,

`roleid` int(11) not null,

`newcount` int(11) not null,

`create_time` int(11) not null

)engine=myisam deafult charset = utf8;

alter table log_role_gold_change add index(`roleid`)

图SS:

从上面的图形可以看出：

1.当只有order by 时 会出现  “using filesort”  连接类型（type)为all  性能最差，possible_keys用到的索引为null,也就是说 在执行sql时  order by 后面的字段没有被解析为是表中的索引字段，key:代表实际用到的索引地段，

2.当执行这个sql时  要遍历 所有的行。

1） 如果这个图select 只查询索引字段，order by 索引字段会用到索引，要不然就是全表排列；(图SS)

2） 如果有where 条件，比如where roleid=6000256 order by roleid asc . 这样order by 也会用到索引！(如果要是这样的where 条件  就没有实际意义了，这里只是做测试)

注意：

1.  当这个图的sql 后面 跟上  limit 开始，结束；时    一般会默认使用roleid 索引  ，连接类型（type）一般为index  .

2.
 order by m,n  不要轻易写这种语句，一般的order by前面的m才是order by的重点，后面的n为配角，如果没有必要，尽量去掉

从上面的图形可以看出：

第二个sql  和第三个个sql 可以看出 

当where 条件里的roleid 没有一个固定范围时  第二个sql会出现  “using filesort”

所以从第一张图 和第二张图可以得出结论： 当order by 的字段在  where 条件中出现 况且 字段有固定值 或者 有固定返回时  会用到roleid字段的索引。

有一种情况

我的这张表有2020030 条数据  

图S1:

=============================================================

图S2:

通过 图S1:

得知roleid 字段唯一的数据有 6697 条

create_time 字段唯一的数据有 1088425 条

通过图s2:

可以看出 第一个sql: 当固定好roleid 返回后  通过 roleid 排序（order by ）,则会用到索引（roleid）， 查询数据得到了优化，说明查询时 用索引的排序 去获取数据

第二个sql   当固定 好时间的返回 （这里的时间返回为 第一条数据插入的时间，到最后一条数据插入的时间） 最后 这个sql没有得到索引的优化。

第三个 sql  这一次 把 时间返回缩小 最后 这个sql 得到了索引的优化了 ，

为什么 第二个sql 没有得到优化，我的猜测是  一个索引节点如果对应多条数据，那么 最后这个索引列生成的索引文件比create_time（一个节点对应一条数据） 索引列生成的文件较小，还是和查询的数量有关系啊。【没有验证】，感觉是后者 ，希望有知道的给我讲讲？

结论：（引用于http://www.cnblogs.com/zhaoyl/archive/2012/05/04/2483513.html）

当order by 字段出现在where条件中时，才会利用索引而无需排序操作。其他情况，order by不会出现排序操作。 

分析： 

为什么只有order by 字段出现在where条件中时,才会利用该字段的索引而避免排序。这要说到数据库如何取到我们需要的数据了。

一条SQL实际上可以分为三步。

1.得到数据

2.处理数据 

3.返回处理后的数据

$sql=select sid from log_role_gold_change where id > 1000 and id < 2000 order by id desc;

比如上面的这条语句$sql;

第一步：根据where条件和统计信息生成执行计划，得到数据。 

第二步：将得到的数据排序。 

当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则排序操作。 

第三步：返回排序后的数据。 

另外： 

上面的2百多万的数据sort只用了0.8ms，也许大家觉得sort不怎么占用资源。可是，由于上面的表的数据是有序的，所以排序花费的时间较少。如果 是个比较无序的表，sort时间就会增加很多了。另外排序操作一般都是在内存里进行的，对于数据库来说是一种CPU的消耗，由于现在CPU的性能增强，对 于普通的几十条或上百条记录排序对系统的影响也不会很大。但是当你的记录集增加到上百万条以上时，你需要注意是否一定要这么做了，大记录集排序不仅增加了 CPU开销，而且可能会由于内存不足发生硬盘排序的现象，当发生硬盘排序时性能会急剧下降。 

注：ORACLE或者DB2都有一个空间来供SORT操作使用（上面所说的内存排序），如ORACLE中是用户全局区（UGA），里面有SORT_AREA_SIZE等参数的设置。如果当排序的数据量大时，就会出现排序溢出（硬盘排序），这时的性能就会降低很多了。

总结： 

当order by 中的字段出现在where条件中时，才会利用索引而不排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union t等。