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中的字段全部來源于查詢中最外面的表時，才能使用索引做排序
轉載于:https://blog.51cto.com/dwchaoyue/1556398
總結
以上是生活随笔為你收集整理的MySQL 如何利用做排序的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Linux学习笔记2-文件读写操作
- 下一篇： flask 进阶