MySQL中利用索引對(duì)數(shù)據(jù)進(jìn)行排序的基礎(chǔ)教程

MySQL中，有兩種方式生成有序結(jié)果集：一是使用filesort，二是按索引順序掃描。利用索引進(jìn)行排序操作是非常快的，而且可以利用同一索引同時(shí)進(jìn)行查找和排序操作。當(dāng)索引的順序與ORDER BY中的列順序相同且所有的列是同一方向(全部升序或者全部降序)時(shí)，可以使用索引來(lái)排序。如果查詢是連接多個(gè)表，僅當(dāng)ORDER BY中的所有列都是第一個(gè)表的列時(shí)才會(huì)使用索引。其它情況都會(huì)使用filesort。

MySQL索引通常是被用于提高WHERE條件的數(shù)據(jù)行匹配或者執(zhí)行聯(lián)結(jié)操作時(shí)匹配其它表的數(shù)據(jù)行的搜索速度。
MySQL也能利用索引來(lái)快速地執(zhí)行ORDER BY和GROUP BY語(yǔ)句的排序和分組操作。
通過(guò)索引優(yōu)化來(lái)實(shí)現(xiàn)MySQL的ORDER BY語(yǔ)句優(yōu)化：

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)

下面來(lái)羅列一些常見(jiàn)的索引對(duì)ORFER BY的優(yōu)化情況：

1、如果一個(gè)SQL語(yǔ)句形如：

SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];

在[sort]這個(gè)欄位上建立索引就可以實(shí)現(xiàn)利用索引進(jìn)行order by 優(yōu)化。
2、WHERE + ORDER BY的索引優(yōu)化，形如：

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];

建立一個(gè)聯(lián)合索引(columnX,sort)來(lái)實(shí)現(xiàn)order by 優(yōu)化。
注意：如果columnX對(duì)應(yīng)多個(gè)值，如下面語(yǔ)句就無(wú)法利用索引來(lái)實(shí)現(xiàn)order by的優(yōu)化

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];

3、WHERE+ 多個(gè)字段ORDER BY

SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

建立索引(uid,x,y)實(shí)現(xiàn)order by的優(yōu)化,比建立(x,y,uid)索引效果要好得多。
MySQL Order By不能使用索引來(lái)優(yōu)化排序的情況
* 對(duì)不同的索引鍵做 ORDER BY ：(key1,key2分別建立索引)

SELECT * FROM t1 ORDER BY key1, key2;

* 在非連續(xù)的索引鍵部分上做 ORDER BY：(key_part1,key_part2建立聯(lián)合索引;key2建立索引)

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

* 同時(shí)使用了 ASC 和 DESC：(key_part1,key_part2建立聯(lián)合索引)

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

* 用于搜索記錄的索引鍵和做 ORDER BY 的不是同一個(gè)：(key1,key2分別建立索引)

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

* 如果在WHERE和ORDER BY的欄位上應(yīng)用表達(dá)式(函數(shù))時(shí)，則無(wú)法利用索引來(lái)實(shí)現(xiàn)order by的優(yōu)化

SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;

當(dāng)MySQL不能使用索引進(jìn)行排序時(shí)，就會(huì)利用自己的排序算法(快速排序算法)在內(nèi)存(sort buffer)中對(duì)數(shù)據(jù)進(jìn)行排序，如果內(nèi)存裝載不下，它會(huì)將磁盤上的數(shù)據(jù)進(jìn)行分塊，再對(duì)各個(gè)數(shù)據(jù)塊進(jìn)行排序，然后將各個(gè)塊合并成有序的結(jié)果集（實(shí)際上就是外排序）。對(duì)于filesort，MySQL有兩種排序算法。
1.兩遍掃描算法(Two passes)
實(shí)現(xiàn)方式是先將須要排序的字段和可以直接定位到相關(guān)行數(shù)據(jù)的指針信息取出，然后在設(shè)定的內(nèi)存（通過(guò)參數(shù)sort_buffer_size設(shè)定）中進(jìn)行排序，完成排序之后再次通過(guò)行指針信息取出所需的Columns。
注：該算法是4.1之前采用的算法，它需要兩次訪問(wèn)數(shù)據(jù)，尤其是第二次讀取操作會(huì)導(dǎo)致大量的隨機(jī)I/O操作。另一方面，內(nèi)存開(kāi)銷較小。
2. 一次掃描算法(single pass)
該算法一次性將所需的Columns全部取出，在內(nèi)存中排序后直接將結(jié)果輸出。
注：從 MySQL 4.1 版本開(kāi)始使用該算法。它減少了I/O的次數(shù)，效率較高，但是內(nèi)存開(kāi)銷也較大。如果我們將并不需要的Columns也取出來(lái)，就會(huì)極大地浪費(fèi)排序過(guò)程所需要的內(nèi)存。在 MySQL 4.1 之后的版本中，可以通過(guò)設(shè)置 max_length_for_sort_data 參數(shù)來(lái)控制 MySQL 選擇第一種排序算法還是第二種。當(dāng)取出的所有大字段總大小大于 max_length_for_sort_data 的設(shè)置時(shí)，MySQL 就會(huì)選擇使用第一種排序算法，反之，則會(huì)選擇第二種。為了盡可能地提高排序性能，我們自然更希望使用第二種排序算法，所以在 Query 中僅僅取出需要的 Columns 是非常有必要的。

當(dāng)對(duì)連接操作進(jìn)行排序時(shí)，如果ORDER BY僅僅引用第一個(gè)表的列，MySQL對(duì)該表進(jìn)行filesort操作，然后進(jìn)行連接處理，此時(shí)，EXPLAIN輸出“Using filesort”；否則，MySQL必須將查詢的結(jié)果集生成一個(gè)臨時(shí)表，在連接完成之后進(jìn)行filesort操作，此時(shí)，EXPLAIN輸出“Using temporary;Using filesort”。

您可能感興趣的文章:

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com