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

转载 2013年12月02日 00:23:24

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

看下面的查询:

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

MySQL 过滤数据——MySQL系列(三)

1、使用Where 子句 Where 子句位于Order by 前面 Where子句操作符 操作符 说明 = 等于 不等于 != 不等于 小于 小于等于 > 大于 ...
  • vvhesj
  • vvhesj
  • 2014年03月10日 19:15
  • 6076

mysql必知必会 - 过滤数据(单字段过滤)

已经学习完查询某个字段的数据,但是这些数据是所有行的数据;在所有行的数据的基础上,学习了排序,学习了限制返回个数,学习了返回不同的数据;上面这些情况都是在不加行过滤的查询,所以查询的结果都是所有行的数...
  • mediocre117
  • mediocre117
  • 2016年12月04日 16:17
  • 3455

【MySql】过滤数据where语句

1、使用where子句
  • u011201998
  • u011201998
  • 2016年12月11日 10:37
  • 519

MySQL如何利用索引优化ORDER BY排序语句

官网排序优化:http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html MySql创建索引时支持ASC或DESC排序。 ...
  • z69183787
  • z69183787
  • 2016年11月29日 10:30
  • 5134

mysql先排序后去重

mysql先排序后去重的写法: SELECT * FROM ( SELECT DISTINCT * FROM A ORDER BY `created` DESC ) A GROUP BY use...
  • licyXiaobaiyang
  • licyXiaobaiyang
  • 2017年05月22日 09:58
  • 1709

MySQL索引类型总结和使用技巧以及注意事项

在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表: 复制代码 代码如下: CREATE TABLE mytable(   ID INT NOT NULL,...
  • shixiaoguo90
  • shixiaoguo90
  • 2014年08月10日 16:44
  • 1101

MySql索引的一个技巧

索引的建立,直接会影响到查询性能。 看下面的查询: select * from ddd where id>1 order by score; 我们查询学号大于1的学生的各科成绩得分。...
  • climb_up
  • climb_up
  • 2013年12月20日 01:20
  • 449

mysql索引的一个技巧

 针对select * from table where col1 > number order by col2 desc。其实按照常规的方法可以这样设计:key(col1, col2)但是这种办法在...
  • kangojian
  • kangojian
  • 2009年11月10日 11:54
  • 425

Mysql索引扫描排序

使用索引扫描来做排序生成有序结果Mysql有两种操作可以用来生成有序结果: + 排序操作: 将查找出来的结果使用排序算法进行排序 + 按索引顺序扫描: ORDER BY语句后跟着一个被索引的列,如...
  • u013298300
  • u013298300
  • 2016年09月02日 17:30
  • 502

mysql索引排序

在建立索引时有时要考虑的排序字段,将排序字段加入索引也可以缩小搜索范围 explain select * from table_name where skin_id=4 order menu_ord...
  • u011478276
  • u011478276
  • 2016年12月21日 14:01
  • 203
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySql索引过滤+排序的一个技巧
举报原因:
原因补充:

(最多只允许输入30个字)