1.利用有序索引獲取有序資料
取出滿足過濾條件作為排序條件的欄位,以及可以直接定位到行資料的行指標資訊,在 Sort Buffer 中進行實際的排序操作,然後利用排好序的資料根據行指標資訊返回表中取得用戶端請求的其他欄位的資料,再返回給用戶端.



1.1 按照索引對結果進行排序:order by 使用索引是有條件
1) 只包括在有選擇的此列上(select後面的欄位),不一定適應*的情況):
CREATE TABLE `test` (
`id` int(11) NOT NullAUTO_INCREMENT,
`rdate` datetime NOT Null,
`inventid` int(11) NOT Null,
`customerid` int(11) NOT Null,
`staffid` int(11) NOT Null,
`data` Varchar(20) NOT Null,
PRIMARY KEY (`id`),
UNIQUE KEY `rdate`(`rdate`,`inventid`,`customerid`),
KEY `inventid` (`inventid`),
KEY `customerid` (`customerid`),
KEY `staffid` (`staffid`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1
如:
mysql> explain select inventid from test where rdate='2011-12-1400:00:00' order by inventid , customerid;
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| 1 | SIMPLE | test | ref | rdate | rdate | 8 | const | 10 | Using where; Using index |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
Select選擇的列使用索引
而下面不使用索引
mysql> explain select * from test where rdate='2011-12-14 00:00:00'order by inventid , customerid ;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | test | ALL | rdate | Null | Null | Null | 13 | Using where;Using filesort|
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
2) 只有當ORDER BY中所有的列必須包含在相同的索引,並且索引的順序和order by子句中的順序完全一致,並且所有列的排序方向(昇冪或者降冪)一樣才有,(混合使用ASC模式和DESC模式則不使用索引)
mysql> explain select inventid from test order byrdate, inventid ;
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | test | index | Null | rdate |16 | Null | 13 |Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select inventid from test whererdate="2011-12-16" order by inventid ,staffid;
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------
| id | select_type | table | type | possible_keys |key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------
| 1 | SIMPLE | test | ref | rdate | rdate | 8 | const | 1 |Using where; Using filesort
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------
1 row in set (0.00 sec)

 

由於rdate, inventid使用了同一個索引。排序使用到了索引。這個也是滿足了首碼索引。但是order by inventid ,staffid;就不是使用了索引,因為staffid和inventid不是同一個索引
3) where 語句與ORDER BY語句組合滿足最左首碼:
mysql> explain select inventid from test whererdate="2011-12-16" order by inventid ;
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| 1 | SIMPLE | test | ref | rdate | rdate | 8 | const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
4) 如果查詢聯接了多個表,只有在order by子句的所有列引用的是第一個表的列才可以。
5) 在其他的情況下,mysql使用檔排序
例如:
1) where語句與order by語句,使用了不同的索引
2) 檢查的行數過多,且沒有使用覆蓋索引
3) ORDER BY中的列不包含在相同的索引,也就是使用了不同的索引
4) 對索引列同時使用了ASC和DESC
5) where語句或者ORDER BY語句中索引列使用了運算式,包括函數運算式
6) where 語句與ORDER BY語句組合滿足最左首碼,但where語句中使用了條件查詢。查見第10句,雖然where與order by構成了索引最左有綴的條件,但是where子句中使用的是條件查詢。

 


mysql> explain select inventid from test whererdate>"2011-12-16" order by inventid;
+----+-------------+-------+-------+---------------+-------+---------+------+------+----------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+-------+-------+---------------+-------+---------+------+------+----------------
| 1 |SIMPLE | test | range | rdate | rdate | 8 | Null | 1 | Using where; Using index; Usingfilesort |
+----+-------------+-------+-------+---------------+-------+---------+------+------+----------------
1 row in set (0.00sec)

7) 當使用left join,使用右邊的表字段排序

2.檔排序
這個 filesort 並不是說通過磁片檔進行排序,而只是告訴我們進行了一個排序操作。即在MySQL Query Optimizer 所給出的執行計畫(通過 EXPLAIN 命令查看)中被稱為檔排序(filesort)
是通過相應的排序演算法,將取得的資料在記憶體中進行排序: MySQL需要將資料在記憶體中進行排序,所使用的記憶體區域也就是我們通過sort_buffer_size 系統變數所設置的排序區。這個排序區是每個Thread 獨享的,所以說可能在同一時刻在MySQL 中可能存在多個 sort buffer 記憶體區域。
在MySQL中filesort 的實現演算法實際上是有兩種:
雙路排序:是首先根據相應的條件取出相應的排序欄位和可以直接定位行資料的行指標資訊,然後在sort buffer 中進行排序。
單路排序:是一次性取出滿足條件行的所有欄位,然後在sort buffer中進行排序。
在MySQL4.1版本之前只有第一種排序演算法雙路排序,第二種演算法是從MySQL4.1開始的改進演算法,主要目的是為了減少第一次演算法中需要兩次訪問表資料的 IO 操作,將兩次變成了一次,但相應也會耗用更多的sortbuffer 空間。當然,MySQL4.1開始的以後所有版本同時也支援第一種演算法,
MySQL主要通過比較我們所設定的系統參數 max_length_for_sort_data的大小和Query 語句所取出的欄位類型大小總和來判定需要使用哪一種排序演算法。如果 max_length_for_sort_data更大,則使用第二種優化後的演算法,反之使用第一種演算法。所以如果希望 ORDER BY 操作的效率盡可能的高,一定要主義max_length_for_sort_data 參數的設置。曾經就有同事的資料庫出現大量的排序等待,造成系統負載很高,而且回應時間變得很長,最後查出正是因為MySQL 使用了傳統的第一種排序演算法而導致,在加大了max_length_for_sort_data 參數值之後,系統負載馬上得到了大的緩解,回應也快了很多。
2.1 MySQL 需要使用filesort 實現排序的實例
假設有 Table A 和 B 兩個表結構分別如下:
# mysql
>show create table A\G
*************************** 1. row ***************************
Table: A
Create Table: CREATE TABLE `A` (
`id` int(11) NOT Null default '0',
`c2` char(2) default Null,
`c3` Varchar(16) default Null,
`c4` datetime default Null,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
#:mysql
> show create table B\G
*************************** 1. row *************************** Table: B
Create Table: CREATE TABLE `B` (
`id` int(11) NOT Null default '0',
`c2` char(2) default Null,
`c3` Varchar(16) default Null,
PRIMARY KEY (`id`),
KEY `B_c2_ind` (`c2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

A.c2不是索引將使用:
sky@localhost : example 01:54:23> EXPLAIN SELECT A.* FROM A,B WHERE A.id >2 AND A.c2 <5 AND A.c2 = B.c2 ORDER BY A.c2\G

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: A
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: Null
rows: 3
Extra: Using where; Using filesort

*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: B
type: ref
possible_keys: B_c2_ind
key: B_c2_ind
key_len: 7
ref: example.A.c2
rows: 2
Extra: Using where; Using index

MySQL 從 Table A 中取出了符合條件的資料,由於取得的資料並不滿足 ORDER BY 條件,所以 MySQL 進行了 filesort 操作,其整個執行過程如下圖所示:
102T33325-0  


2.2 MySQL 需要使用Using temporary 臨時表來filesort
如果order by的子句只引用了聯接中的第一個表,MySQL會先對第一個表進行排序,然後進行聯接。也就是expain中的Extra的Using Filesort.否則MySQL先把結果保存到臨時表(Temporary Table),然後再對臨時表的資料進行排序.此時expain中的Extra的顯示Using temporary Using Filesort.
例如如果我們的排序資料如果是兩個(或者更多個) Table 通過 Join所得出的,如下例所示:
sky@localhost : example 02:46:15> explain select A.* from A,B
where A.id > 2 and A.c2 < 5 and A.c2 = B.c2 order by B.c3\G

*************************** 1. row***************************

id: 1
select_type: SIMPLE
table: A
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: Null
rows: 3
Extra: Using where; Using temporary; Using filesort

*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: B
type: ref
possible_keys: B_c2_ind
key: B_c2_ind
key_len: 7
ref: example.A.c2
rows: 2
Extra: Using where

實際執行過程應該是如下圖所示:
102T32053-1  



3. 優化Filesort
當無法避免排序操作時,又該如何來優化呢?很顯然,應該盡可能讓 MySQL 選擇使用第二種單路演算法來進行排序。這樣可以減少大量的隨機IO操作,很大幅度地提高排序工作的效率。
1. 加大 max_length_for_sort_data 參數的設置
在 MySQL 中,決定使用老式排序演算法還是改進版排序演算法是通過參數 max_length_for_ sort_data 來決定的。當所有返回欄位的最大長度小於這個參數值時,MySQL 就會選擇改進後的排序演算法,反之,則選擇老式的演算法。所以,如果有充足的記憶體讓MySQL 存放須要返回的非排序欄位,就可以加大這個參數的值來讓 MySQL 選擇使用改進版的排序演算法。
2. 去掉不必要的返回欄位
當記憶體不是很充裕時,不能簡單地通過強行加大上面的參數來強迫 MySQL 去使用改進版的排序演算法,否則可能會造成 MySQL 不得不將資料分成很多段,然後進行排序,這樣可能會得不償失。此時就須要去掉不必要的返回欄位,讓返回結果長度適應 max_length_for_sort_data 參數的限制。
3. 增大 sort_buffer_size 參數設置
增大 sort_buffer_size 並不是為了讓 MySQL選擇改進版的排序演算法,而是為了讓MySQL儘量減少在排序過程中對須要排序的資料進行分段,因為分段會造成 MySQL 不得不使用臨時表來進行交換排序。
創作者介紹
創作者 shadow 的頭像
shadow

資訊園

shadow 發表在 痞客邦 留言(0) 人氣()