MySQL 索引优化 Using where, Using filesort
来源:互联网 发布:淘宝导航图片怎么放 编辑:程序博客网 时间:2024/05/18 03:05
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 的特性:
一条 SQL 语句只能使用 1 个索引 (5.0-),MySQL 根据表的状态,选择一个它认为最好的索引用于优化查询
联合索引,只能按从左到右的顺序依次使用
从上边可以看到结合索引,也可以叫多列索引,形如 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 的数据,太慢了。
修改表结构,删除 user_id 和 item_id 的 INDEX 索引,建立一个名为 user_item 的联合 UNIQUE 索引,顺序是先 user_id 后 item_id,再 EXPLAIN,这回只有 Using where 了。
再刷新页面,观察任务管理器,mysqld-nt.exe 只读取了 2K 的数据,页面咔的一下就出来了……
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 的特性:
一条 SQL 语句只能使用 1 个索引 (5.0-),MySQL 根据表的状态,选择一个它认为最好的索引用于优化查询
联合索引,只能按从左到右的顺序依次使用
从上边可以看到结合索引,也可以叫多列索引,形如 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 的数据,太慢了。
修改表结构,删除 user_id 和 item_id 的 INDEX 索引,建立一个名为 user_item 的联合 UNIQUE 索引,顺序是先 user_id 后 item_id,再 EXPLAIN,这回只有 Using where 了。
再刷新页面,观察任务管理器,mysqld-nt.exe 只读取了 2K 的数据,页面咔的一下就出来了……
0 0
- MySQL 索引优化 Using where, Using filesort
- mysql优化索引 —— Using filesort
- mysql优化索引 —— Using filesort
- mysql优化(四)mysql优化索引--Using filesort
- using where和 using filesort
- mysql using filesort
- mysql explain中的using filesort
- mysql using filesort 的解决方案
- mysql explain中的using filesort
- MySql中explain的时候出现using filesort,优化之
- MySql中explain的时候出现using filesort,优化之
- MySql中explain的时候出现using filesort,优化之
- 优化MySql中explain的时候出现using filesort
- MySQL explain 的Using temporary; Using filesort
- mysql查询优化--临时表和文件排序(Using temporary; Using filesort问题解决)
- MySQL查询优化概念辨析---Using where 和 Using index
- Mysql之EXPLAIN显示using filesort
- Mysql之EXPLAIN显示using filesort
- 开源夏令营(2)
- STL vector实现
- 【移动】chrome浏览器模仿移动浏览器效果
- cocos2dx学习笔记(CCHttpRequest和CURL两个方式)
- HDU 2003 求绝对值
- MySQL 索引优化 Using where, Using filesort
- listview中增加checkbox导致item点击事件失效
- linux
- 事务
- J2EE5(Servlet2.5)对EL表达式的支持
- Flex设置外部浏览器
- JAVA环境变量配置
- Java 字节流实现文件读写操作(InputStream-OutputStream)
- Java 字符流实现文件读写操作(FileReader-FileWriter)