mysql索引（ 转载）http://blog.csdn.net/wenniuwuren

MySql索引的一个技巧
2013-01-21 12:02 1953人阅读 评论(8) 收藏 举报
分类： 笔试面试（102）   mysql（58） 
版权声明：本文为博主原创文章，未经博主允许不得转载。

索引的建立，直接会影响到查询性能。

看下面的查询：

select * from ddd where id>1 order by score;

我们查询学号大于1的学生的各科成绩得分。

那么按照一般的思路，是这样建立索引的(id,score)。

explain一下：

[sql] view plain copy
mysql> explain select * from ddd where id>1 order by score; 
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ 
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                    | 
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ 
|  1 | SIMPLE      | ddd   | range | id            | id   | 4       | NULL |   12 | Using where; Using index; Using filesort | 
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ 
1 row in set (0.00 sec) 
查看explain的结果，我们发现，

查询过程使用了order by，出现了using filesort。也就是说，MySQL在查询到符合条件的数据之后，做了排序。

【---------------------------------------------------------】

这是因为，当使用(id,score)索引的时候，查询where id > 1 order by score使用了一个非常量来限定索引的前半部分，所以只用到了索引的前半部分，后半部分没有使用。所以，排序还要mysql另外来做。如果这里的查询是where id = 1 order by score，那么必然就不会出现filesort了。
【---------------------------------------------------------】

怎么优化掉排序过程呢？

我们删掉(id,score)这个索引，新建一个(score,id)索引。

[sql] view plain copy
mysql> alter table ddd drop index id; 
Query OK, 0 rows affected (0.98 sec) 
Records: 0  Duplicates: 0  Warnings: 0 
 
mysql> alter table ddd add  index(score,id); 
Query OK, 0 rows affected (0.11 sec) 
Records: 0  Duplicates: 0  Warnings: 0 
再次explain：

[sql] view plain copy
mysql> explain select * from ddd where id>1 order by score; 
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ 
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                    | 
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ 
|  1 | SIMPLE      | ddd   | index | NULL          | score | 9       | NULL |   28 | Using where; Using index | 
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ 
1 row in set (0.00 sec) 
我们可以看到，explain结果就已经没有了using filesort。
这是因为，我们要取得id大于1的学生的score，最后按照score排序，那么我们扫描一遍(score,id)索引，找到id大于1的学生，然后直接取出信息即可。由于(score,id)索引已经排序好了，所以免去了排序的过程。