注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

zjcjack的博客

 
 
 

日志

 
 

MySQL 索引优化 Using where, Using filesort  

2012-02-29 23:55:47|  分类: mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

MySQL 数据库,MyISAM 类型的表 table_item,有 5、6 个字段,主键是 id。

user_id 和 item_id 两个字段都是单独的 INDEX 类型的索引。

问题是如何发现的?

在自己的开发环境下,打开一个简单的页面都要好长时间,不知道问题出在哪里,只发现硬盘灯闪个不停。

观察 Windows 的任务管理器,看到 mysqld-nt.exe 这个进程的 "I/O 读取字节" 高达十几 G!!!

再次测试,发现每次刷新页面,这个进程要读取几十 M 的数据。

奇了怪了,查看 SQL 语句,还有表结构,字段都建了索引了呀。

后来 EXPLAIN 了一下,看到结果是 Using where; Using filesort。

explain SELECT * FROM table_item WHERE user_id = 2 ORDER BY item_id LIMIT 0, 5

翻了 MySQL 手册,仔细看下 filesort 的说明,知道了 Using filesort 是一种速度很慢的外部排序。

不过我不理解为什么会使用 filesort 排序,WHERE 和 ORDER BY 用到的字段都是有索引的呀。

赶紧 Google之,得到的启示就是索引定义不当导致MySQL 没有用到索引。

需要了解MySQL 的特性:

  1. 一条 SQL 语句只能使用 1 个索引 (5.0-),MySQL 根据表的状态,选择一个它认为最好的索引用于优化查询
  2. 联合索引,只能按从左到右的顺序依次使用
  3. 从上边可以看到结合索引,也可以叫多列索引,形如 key ('B1','B2','B3' ,'B4')等的,排序的思路通常为,先按照B1来排序,B1相同,然后按照B2排序,以此类推,这样对于(B1),(B1,B2), (B1,B2,B3)的索引都是有效的,可是对于(B2,B3)这样的索引就无效了。

根据这个特性就可以解决问题:

user_id 和 item_id 是 2 个索引,我的语句中,MySQL 选择了 user_id,那么 item_id 的索引没有起到任何用处,所以,当我要排序的时候,由于记录数较多,内存中的排序 buffer 满了,只能 Using filesort 进行外部排序,因此每次查询要从磁盘读取几十 M 的数据,太慢了。(按这么说如果buffer开得比较小,加上有存在using filesort就很容易出现外部排部,需要从磁盘读取数据的情况)

修改表结构,删除 user_id 和 item_id 的 INDEX 索引,建立一个名为 user_item 的联合 UNIQUE 索引,顺序是先 user_id 后 item_id,再 EXPLAIN,这回只有 Using where 了。

再刷新页面,观察任务管理器,mysqld-nt.exe 只读取了 2K 的数据,页面咔的一下就出来了……

  评论这张
 
阅读(448)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017