MySQL中應用索引對數據停止排序的基本教程。本站提示廣大學習愛好者：（MySQL中應用索引對數據停止排序的基本教程）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL中應用索引對數據停止排序的基本教程正文
MySQL中，有兩種方法生成有序成果集：一是應用filesort，二是按索引次序掃描。應用索引停止排序操作長短常快的，並且可以應用統一索引同時停止查找和排序操作。當索引的次序與ORDER BY中的列次序雷同且一切的列是統一偏向(全體升序或許全體降序)時，可使用索引來排序。假如查詢是銜接多個表，僅當ORDER BY中的一切列都是第一個表的列時才會應用索引。其它情形都邑應用filesort。
MySQL索引平日是被用於進步WHERE前提的數據行婚配或許履行聯絡操作時婚配其它表的數據行的搜刮速度。
MySQL也能應用索引來疾速地履行ORDER BY和GROUP BY語句的排序和分組操作。
經由過程索引優化來完成MySQL的ORDER BY語句優化：
create table actor(
actor_id int unsigned NOT NULL AUTO_INCREMENT,
name varchar(16) NOT NULL DEFAULT '',
password varchar(16) NOT NULL DEFAULT '',
PRIMARY KEY(actor_id),
KEY (name)
) ENGINE=InnoDB
insert into actor(name,password) values('cat01','1234567');
insert into actor(name,password) values('cat02','1234567');
insert into actor(name,password) values('ddddd','1234567');
insert into actor(name,password) values('aaaaa','1234567');
mysql> explain select actor_id from actor order by actor_id \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 4
Extra: Using index
1 row in set (0.00 sec)
mysql> explain select actor_id from actor order by password \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using filesort
1 row in set (0.00 sec)
mysql> explain select actor_id from actor order by name \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: index
possible_keys: NULL
key: name
key_len: 18
ref: NULL
rows: 4
Extra: Using index
1 row in set (0.00 sec)
上面來枚舉一些罕見的索引對ORFER BY的優化情形：
1、假如一個SQL語句形如：
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
在[sort]這個欄位上樹立索引便可以完成應用索引停止order by 優化。
2、WHERE + ORDER BY的索引優化，形如：
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
樹立一個結合索引(columnX,sort)來完成order by 優化。
留意：假如columnX對應多個值，以下面語句就沒法應用索引來完成order by的優化
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];
3、WHERE+ 多個字段ORDER BY
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
樹立索引(uid,x,y)完成order by的優化,比樹立(x,y,uid)索引後果要好很多。
MySQL Order By不克不及應用索引來優化排序的情形
* 對分歧的索引鍵做 ORDER BY ：(key1,key2分離樹立索引)
SELECT * FROM t1 ORDER BY key1, key2;
* 在非持續的索引鍵部門上做 ORDER BY：(key_part1,key_part2樹立結合索引;key2樹立索引)
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
* 同時應用了 ASC 和 DESC：(key_part1,key_part2樹立結合索引)
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
* 用於搜刮記載的索引鍵和做 ORDER BY 的不是統一個：(key1,key2分離樹立索引)
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
* 假如在WHERE和ORDER BY的欄位上運用表達式(函數)時，則沒法應用索引來完成order by的優化
SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;
當MySQL不克不及應用索引停止排序時，就會應用本身的排序算法(疾速排序算法)在內存(sort buffer)中對數據停止排序，假如內存裝載不下，它會將磁盤上的數據停止分塊，再對各個數據塊停止排序，然後將各個塊歸並成有序的成果集（現實上就是外排序）。關於filesort，MySQL有兩種排序算法。
1.兩遍掃描算法(Two passes)
完成方法是先將需要排序的字段和可以直接定位到相干行數據的指針信息掏出，然後在設定的內存（經由過程參數sort_buffer_size設定）中停止排序，完成排序以後再次經由過程行指針信息掏出所需的Columns。
注：該算法是4.1之前采取的算法，它須要兩次拜訪數據，特別是第二次讀取操作會招致年夜量的隨機I/O操作。另外一方面，內存開支較小。
2. 一次掃描算法(single pass)
該算法一次性將所需的Columns全體掏出，在內存中排序後直接將成果輸入。
注：從 MySQL 4.1 版本開端應用該算法。它削減了I/O的次數，效力較高，然則內存開支也較年夜。假如我們將其實不須要的Columns也掏出來，就會極年夜地糟蹋排序進程所須要的內存。在 MySQL 4.1 以後的版本中，可以經由過程設置 max_length_for_sort_data 參數來掌握 MySQL 選擇第一種排序算法照樣第二種。當掏出的一切年夜字段總年夜小年夜於 max_length_for_sort_data 的設置時，MySQL 就會選擇應用第一種排序算法，反之，則會選擇第二種。為了盡量地進步排序機能，我們天然更願望應用第二種排序算法，所以在 Query 中僅僅掏出須要的 Columns 長短常有需要的。
當對銜接操作停止排序時，假如ORDER BY僅僅援用第一個表的列，MySQL對該表停止filesort操作，然落後行銜接處置，此時，EXPLAIN輸入“Using filesort”；不然，MySQL必需將查詢的成果集生成一個暫時表，在銜接完成以後停止filesort操作，此時，EXPLAIN輸入“Using temporary;Using filesort”。
