• 博客访问： 386931
• 博文数量： 43
• 博客积分： 613
• 博客等级： 中士
• 技术积分： 756
• 用 户 组： 普通用户
• 注册时间： 2008-09-01 11:25

2016年（1）

2015年（5）

2014年（5）

2013年（14）

2012年（18）

2012-10-26 12:19:54

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;

1. show create table a\G;;
2. *************************** 1. row ***************************
3.        Table: a
4. Create Table: CREATE TABLE `a` (
5.   `c1` int(11) NOT NULL AUTO_INCREMENT,
6.   `c2` char(2) DEFAULT NULL,
7.   `c3` char(4) DEFAULT NULL,
8.   PRIMARY KEY (`c1`),
9.   KEY `a_c2_c3_idx` (`c2`,`c3`)
10. ) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

11. explain select * from a force index(a_c2_c3_idx) order by c2,c3;
12. +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+
13. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
14. +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+
15. | 1 | SIMPLE | a | index | NULL | a_c2_c3_idx | 20 | NULL | 6 | |
16. +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+

SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;

1. explain select * from a force index(a_c2_c3_idx) where c2=3 order by c2,c3;
2. +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
5. | 1 | SIMPLE | a | index | a_c2_c3_idx | a_c2_c3_idx | 20 | NULL | 6 | Using where |
6. +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

1. explain select * from a force index(a_c2_c3_idx) order by c2 desc,c3 desc;
2. +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+
5. | 1 | SIMPLE | a | index | NULL | a_c2_c3_idx | 20 | NULL | 6 | |
6. +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+

SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

1. explain select * from a force index(a_c2_c3_idx) where c2=3 order by c2 desc,c3 desc;
2. +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
5. | 1 | SIMPLE | a | index | a_c2_c3_idx | a_c2_c3_idx | 20 | NULL | 6 | Using where |
6. +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+

————————————————————分割线———————————————————————

SELECT * FROM t1 ORDER BY key1, key2

1. explain select * from a order by c1,c3;
2. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
5. | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 6 | Using filesort |
6. +----+-------------+-------+------+---------------+------+---------+------+------+----------------+

MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。

MySQL 中 filesort 的实现算法实际上是有两种的，一种是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在 sort buffer 中进行排序。另外一种是一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序。

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC

SELECT * FROM t1 WHERE key2=constant ORDER BY key1