using_where, Using temporary ,using_filesort 分享

Using filsort文档中的解释：
Mysql需要额外的一次传递，以找出如何按排序顺序检索行，通过根据联接类型浏览所有行并为所有匹配where子句的行保存排序关键字和行的指针来完成排序，然后关键字被排序，并按排序顺序检索行。额外的传递是指什么？

Mysql> show create table test_filesortG;
*************************** 1. row ***************************
   Table: test_filesort
Create Table: CREATE TABLE `test_filesort` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
KEY `a_2` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
Mysql> explain select * from test_filesort where a=1 order by b;
+----+-------------+---------------+------+---------------+------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | test_filesort | ref | a_2 | a_2 | 5 | const | 1 | Using where; Using filesort |
+----+-------------+---------------+------+---------------+------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)
Mysql> alter table test_filesort add index(a,b);
Query OK, 6 rows affected (0.04 sec)
Records: 6 Duplicates: 0 Warnings: 0
Mysql> explain select * from test_filesort where a=1 order by b;
+----+-------------+---------------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | test_filesort | ref | a_2,a | a | 5 | const | 1 | Using where |
+----+-------------+---------------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

额外传递指的是多做了一次排序，很少人会把所有需要排序的字段都放到索引中，出现Using filesort不一定就会有什么性能问题。当然，我是希望尽量在应用中实现order by排序，如果放在数据库这边实现，查询次数又非常大的话，尽量考虑把字段直接冗余到索引中去，避免Mysql自身的排序机制可能会引起的性能下降。

1.order by时可能会出现Using filesort。
2.order by b，如果b列不在索引中，不管b值是否相同，总会出现Using filesort。
3.并不是说所有的索引都可以避免Using filesort，hash索引是不按顺序来保存数据的。

1.如果order by 没有利用到索引，那么将会出现fileSort，如果sort_buffer不够大，fileSort过程则需要使用临时文件,fileSort优化，主要通过调整环境来达到，如下
2.设置参数，优化order by 时可能出现的file sort：
   将sort_buffer_size = 1M  read_rnd_buffer_size = 1M
   修改为sort_buffer_size = 16M read_rnd_buffer_size = 16M
避免order by 过程 进行fileSort排序过程临时文件的产生。从3秒->0.7秒左右

参考：MySQL Order By实现原理分析和Filesort优化

3. 优化Filesort

当无法避免排序操作时，又该如何来优化呢？很显然，应该尽可能让 MySQL 选择使用第二种单路算法来进行排序。这样可以减少大量的随机IO操作，很大幅度地提高排序工作的效率。

1. 加大 max_length_for_sort_data 参数的设置

在 MySQL 中，决定使用老式排序算法还是改进版排序算法是通过参数 max_length_for_ sort_data 来决定的。当所有返回字段的最大长度小于这个参数值时，MySQL 就会选择改进后的排序算法，反之，则选择老式的算法。所以，如果有充足的内存让MySQL 存放须要返回的非排序字段，就可以加大这个参数的值来让 MySQL 选择使用改进版的排序算法。

2. 去掉不必要的返回字段

当内存不是很充裕时，不能简单地通过强行加大上面的参数来强迫 MySQL 去使用改进版的排序算法，否则可能会造成 MySQL 不得不将数据分成很多段，然后进行排序，这样可能会得不偿失。此时就须要去掉不必要的返回字段，让返回结果长度适应 max_length_for_sort_data 参数的限制。

3. 增大 sort_buffer_size 参数设置

增大 sort_buffer_size 并不是为了让 MySQL选择改进版的排序算法，而是为了让MySQL尽量减少在排序过程中对须要排序的数据进行分段，因为分段会造成 MySQL 不得不使用临时表来进行交换排序。

注：
key：显示Mysql实际决定使用的索引
Using index:只从索引中检索数据，不回表
key_len：显示Mysql决定使用的键长度
rows：显示Mysql认为它执行查询时必须检查的行数
ref：使用哪个列或常数与key一起从表中选择行
possible_keys：指出Mysql可以使用哪个索引在该表中找到行

网上案例分析：

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-)[5.0+MYSQ会分析是否做index megre]，MySQL 根据表的状态，选择一个它认为最好的索引用于优化查询【use index 指定索引，纠正MYSQL错误判断】
2.联合索引，只能按从左到右的顺序依次使用，包括了group order
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 的索引没有起到任何用处，所以，当我要排序的时候，由于user_id索引取得的记录数较多，内存中的排序 buffer 满了，只能 Using filesort 进行外部排序，因此每次查询要从磁盘读取几十 M 的数据，太慢了。
修改表结构，删除 user_id 和 item_id 的 INDEX 索引，建立一个名为 user_item 的联合 UNIQUE 索引，顺序是先 user_id 后 item_id，再 EXPLAIN，这回只有 Using where 了。
再刷新页面，观察任务管理器，mysqld-nt.exe 只读取了 2K 的数据，页面咔的一下就出来了……