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:从上面的图形可以看出:
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等。
联系客服