关闭

MySql索引过滤+排序的一个技巧

934人阅读 评论(0) 收藏 举报
分类:

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

看下面的查询:

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

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

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

explain一下:

  1. mysql> explain select * from ddd where id>1 order by score;  
  2. +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+  
  3. | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                    |  
  4. +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+  
  5. |  1 | SIMPLE      | ddd   | range | id            | id   | 4       | NULL |   12 | Using where; Using index; Using filesort |  
  6. +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+  
  7. 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)索引。

  1. mysql> alter table ddd drop index id;  
  2. Query OK, 0 rows affected (0.98 sec)  
  3. Records: 0  Duplicates: 0  Warnings: 0  
  4.   
  5. mysql> alter table ddd add  index(score,id);  
  6. Query OK, 0 rows affected (0.11 sec)  
  7. Records: 0  Duplicates: 0  Warnings: 0  

再次explain:

  1. mysql> explain select * from ddd where id>1 order by score;  
  2. +----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+  
  3. | id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |  
  4. +----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+  
  5. |  1 | SIMPLE      | ddd   | index | NULL          | score | 9       | NULL |   28 | Using where; Using index |  
  6. +----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+  
  7. 1 row in set (0.00 sec)  
我们可以看到,explain结果就已经没有了using filesort。

这是因为,我们要取得id大于1的学生的score,最后按照score排序,那么我们扫描一遍(score,id)索引,找到id大于1的学生,然后直接取出信息即可。由于(score,id)索引已经排序好了,所以免去了排序的过程。

from:http://blog.csdn.net/imzoer/article/details/8524187

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:388252次
    • 积分:3693
    • 等级:
    • 排名:第9085名
    • 原创:47篇
    • 转载:213篇
    • 译文:3篇
    • 评论:5条
    最新评论