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必知必会-5去重,截取和排序

MySQL去重,截取和排序
  • zoroday
  • zoroday
  • 2017年02月02日 21:42
  • 659

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

在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表: 复制代码 代码如下: CREATE TABLE mytable( ID INT NOT NULL, u...

mysql创建索引技巧

1,创建MySql索引  对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加  索引的话,那么查找任何...

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

MySQL索引类型总结和使用技巧以及注意事项 索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型 在数据库表中,...

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

在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表: 复制代码代码如下: CREATE TABLE mytable(   ID INT NOT ...

MySQL索引使用方法及技巧大全

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

浅谈MySQL优化索引技巧

mysql的优化可以从硬件设备的选择、操作系统、数据库结构设计、SQL查询、应用程序各个方面进行优化,这里只从数据库的设计及查询语句方面进行优化。 1、创建索引 对于查询占主要的应用来说,索引显得尤...
  • cwqcwk1
  • cwqcwk1
  • 2014年02月20日 21:01
  • 1901

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

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。 在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytabl...

MYSQL order by排序与索引关系总结

我们平常所说的索引,如果没有特别指明,都是指B+树结构组织的B-Tree索引。其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然,除了B+树这种类型的...

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

MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。 MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySql索引过滤+排序的一个技巧
举报原因:
原因补充:

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