首页 > 代码库 > MySQL 如何利用做排序
MySQL 如何利用做排序
利用索引优化排序：order by
单表查询：
索引列的顺序和order by的字句的顺序完全一样，并且所有列的排序方向都一样，
MySQL才能使用索引对结果进行排序。order by 子句与查找型查询的的限制是一样的，必须满足索引的最左前缀
创建测试表：
drop table t_index ;
create table t_index(
tid int not null PRIMARY key auto_increment ,
tname varchar(100) not null ,
tage TINYINT default 0 ,
tadd varchar(100) default ‘‘ ,
tel int default 0,
tmob varchar(20) DEFAULT ‘‘ ,
tsfz varchar(100) default ‘‘
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入测试数据：
insert into t_index(tname,tage,tadd,tel,tmob,tsfz)
VALUES(‘张三风‘,110,‘恒山‘ ,18099001122,‘012-46319976‘,‘‘) ;
insert into t_index(tname,tage,tadd,tel,tmob,tsfz)
VALUES(‘朱元璋‘,56,‘北京‘ ,18112401122,‘012-40119976‘,‘‘) ;
insert into t_index(tname,tage,tadd,tel,tmob,tsfz)
VALUES(‘杨过‘,25,‘武汉‘ ,18099112122,‘012-46340116‘,‘‘) ;
insert into t_index(tname,tage,tadd,tel,tmob,tsfz)
VALUES(‘郭靖‘,45,‘长沙‘ ,13149001122,‘012-46900176‘,‘‘) ;
insert into t_index(tname,tage,tadd,tel,tmob,tsfz)
VALUES(‘黄老邪‘,100,‘河北‘ ,13129001122,‘012-49001976‘,‘‘) ;
insert into t_index(tname,tage,tadd,tel,tmob,tsfz)
VALUES(‘周伯通‘,102,‘河南‘ ,15679001122,‘012-46319001‘,‘‘) ;
insert into t_index(tname,tage,tadd,tel,tmob,tsfz)
VALUES(‘洪七公‘,78,‘合肥‘ ,11243001122,‘012-46319976‘,‘‘) ;
insert into t_index(tname,tage,tadd,tel,tmob,tsfz)
VALUES(‘欧阳峰‘,67,‘广西‘ ,13214001122,‘012-14009976‘,‘‘) ;
insert into t_index(tname,tage,tadd,tel,tmob,tsfz)
VALUES(‘欧阳可‘,27,‘深圳‘ ,15123001122,‘012-46314006‘,‘‘) ;
insert into t_index(tname,tage,tadd,tel,tmob,tsfz)
VALUES(‘尼玛‘,10,‘上海‘ ,13125001122,‘012-41400976‘,‘‘) ;
insert into t_index(tname,tage,tadd,tel,tmob,tsfz)
VALUES(‘杨康‘,30,‘西藏‘ ,15798001122,‘012-46311400‘,‘‘) ;
创建一个测试索引：
mysql> alter table t_index add key tage(tage,tname,tel) ;
1.查询使用到索引并且order by的字段和索引中的字段完全一样(列的顺序和排序方向)
mysql> explain select * from t_index force index (tage) where tage <> 120 and tname =‘张三风‘ order by tage ,tname,tadd ;
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_index | range | tage | tage | 5 | NULL | 10 | Using where |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
mysql> explain select * from t_index force index (tage) where tage > 120 order by tage ,tname,tadd ;
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_index | range | tage | tage | 5 | NULL | 2 | Using where |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
这样的查询任何时候都能使用到索引进行排序，与索引列给定的值无关
2.查询使用了索引并且order by的字段包含索引中的最左列或最左几列
mysql> explain select * from t_index force index (tage) where tage = 120 order by tage ;
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | t_index | ref | tage | tage | 5 | const | 2 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
mysql> explain select * from t_index force index (tage) where tage = 120 order by tage ,tname ;
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | t_index | ref | tage | tage | 5 | const | 2 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
这样的查询任何时候都能使用到索引进行排序，与索引列给定的值无关
3.查询使用了索引并且order by的字段只包含索引中的非最左列或最左几列,
第三种类型的查询要想使用到索引排序添加比较严格
mysql> explain select * from t_index force index (tage) where tage = 120 order by tname ;
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | t_index | ref | tage | tage | 5 | const | 2 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
比如上面的查询，查询根据索引的得到数据，那么根据索引查到的数据的顺序已经根据tage，tname，tadd的顺序排列了。
在上面这个查询中tage是一个给定的值,无需排序，那么查询出来的结果集的顺序是按照tname，tadd的顺序排列，
即使按照order by tname,tadd 排序，查询也不要MySQL另外排序
所以以上查询不需要MySQL另外作排序。
以下查询的也可以根据索引来排序，原理和以上相同
mysql> explain select * from t_index force index (tage) where tage = 100 and tname =‘张三风‘ order by tadd ;
+----+-------------+---------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+-------------+------+-------------+
| 1 | SIMPLE | t_index | ref | tage | tage | 307 | const,const | 1 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------------+------+-------------+
多表关联：
如果查询中需要关联多张表，并且order by中的字段全部来源于查询中最外面的表时，才能使用索引做排序
本文出自 “SQLServer MySQL” 博客，请务必保留此出处http://dwchaoyue.blog.51cto.com/2826417/1556398
MySQL 如何利用做排序