mysql 索引和order by_mysql中order by与索引

近日在开发程序中，遇到一个问题，就是在不进行任何where条件下，对字段进行排序，

发现使用了filesort 文件排序的方法，

如果数据量比较庞大，则会影响整个系统的性能，能不能使用上索引排序呢？

order by id 会不会使用索引？

不会

在select中有id where中没有id条件，order by id  会使用索引吗？

不会

第一种情况 :

order by的字段不在where条件也不在select中

select sid from zhuyuehua.student where sid < 50000 order by id;

不会使用索引mysql> explain select title,nav from news where nav>3 order by id desc \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: news

type: range

possible_keys: nt

key: nt

key_len: 5

ref: NULL

rows: 504817

Extra: Using where; Using index; Using filesort

1 row in set (0.00 sec)

第二种情况 :

order by的字段不在where条件但在select中。

select id,sid from zhuyuehua.student where sid < 50000 order by id;

看sid 是否存在索引，如果存在索引，会使用索引，但不会使用id的索引

mysql> explain select id,title,nav from news where nav<5 order by id desc \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: news

type: range

possible_keys: nt

key: nt

key_len: 5

ref: NULL

rows: 504817

Extra: Using where; Using index; Using filesort

1 row in set (0.01 sec)

第三种情况 :

order by的字段在where条件但不在select中。

select sid from zhuyuehua.student where sid < 50000 and id < 50000 order by id;

会使用索引mysql> explain select title,nav from news where id<1000 and nav>3 order by id desc \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: news

type: range

possible_keys: PRIMARY,nt

key: PRIMARY

key_len: 4

ref: NULL

rows: 2044

Extra: Using where

1 row in set (0.00 sec)

第四种情况 :

order by的字段在where条件但不在select中。倒序排列

select sid from zhuyuehua.student where sid < 50000 and id < 50000 order by id desc;

测试结果：

order by的字段不在where条件不在select中     有排序操作

order by的字段不在where条件但在select中     有排序操作

order by的字段在where条件但不在select中     无排序操作

order by的字段在where条件但不在select中(倒序)     无排序操作

结论：

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

分析：

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

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

1.得到数据

2.处理数据

3.返回处理后的数据

如上面的这条语句select sid from zhuyuehua.student where sid < 50000 and id < 50000 order by id desc

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

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

当执行处理数据(order by)时，数据库会先查看第一步的执行计划，

看order by 的字段是否在执行计划中利用了索引。

如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则排序操作。

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

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

总结：

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

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