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中的字段全部来源于查询中最外面的表时，才能使用索引做排序

本文出自 “” 博客，请务必保留此出处