MySQL 聯(lián)合索引是否支持不同排序規(guī)則

篇首語：
截止到目前的5.7.4版本為止，MySQL的聯(lián)合索引仍無法支持聯(lián)合索引使用不同排序規(guī)則，例如：ALTER TABLE t ADD INDEX idx(col1, col2 DESC)。

先來了解下MySQL關(guān)于索引的一些基礎(chǔ)知識要點：

? a、EXPLAIN結(jié)果中的key_len只顯示了條件檢索子句需要的索引長度，但 ORDER BY、GROUP BY 子句用到的索引則不計入 key_len 統(tǒng)計值；
? b、聯(lián)合索引(composite index)：多個字段組成的索引，稱為聯(lián)合索引；
例如：ALTER TABLE t ADD INDEX `idx` (col1, col2, col3)
? c、覆蓋索引(covering index)：如果查詢需要讀取到索引中的一個或多個字段，則可以從索引樹中直接取得結(jié)果集，稱為覆蓋索引；
例如：SELECT col1, col2 FROM t;
? d、最左原則(prefix index)：如果查詢條件檢索時，只需要匹配聯(lián)合索引中的最左順序一個或多個字段，稱為最左索引原則，或者叫最左前綴；
例如：SELECT * FROM t WHERE col1 = ? AND col2 = ?;
? e、在老版本（大概是5.5以前，具體版本號未確認核實）中，查詢使用聯(lián)合索引時，可以不區(qū)分條件中的字段順序，在這以前是需要按照聯(lián)合索引的創(chuàng)建順序書寫SQL條件子句的；
例如：SELECT * FROM t WHERE col3 = ? AND col1 = ? AND col2 = ?;
? f、MySQL截止目前還只支持多個字段都是正序索引，不支個別字段持倒序索引；
例如：ALTER TABLE t ADD INDEX `idx` (col1, col2, col3 DESC)，這里的DESC只是個預(yù)留的關(guān)鍵字，目前還不能真正有作用
? g、聯(lián)合索引中，如果查詢條件中最左邊某個索引列使用范圍查找，則只能使用前綴索引，無法使用到整個索引；
例如：SELECT * FROM t WHERE col1 = ? AND col2 >= ? AND col3 = ?; 這時候，只能用到 idx 索引的最左2列進行檢索，而col3條件則無法利用索引進行檢索
? h、InnoDB引擎中，二級索引實際上包含了主鍵索引值；

關(guān)于 key_len 的計算規(guī)則：

? 當索引字段為定長數(shù)據(jù)類型，比如：char，int，datetime，需要有是否為空的標記，這個標記需要占用1個字節(jié)；
? 當索引字段為變長數(shù)據(jù)類型，比如：varchar，除了是否為空的標記外，還需要有長度信息，需要占用2個字節(jié)；
? 當字段定義為非空的時候，是否為空的標記將不占用字節(jié)；
? 同時還需要考慮表所使用字符集的差異，latin1編碼一個字符1個字節(jié)，gbk編碼一個字符2個字節(jié)，utf8編碼一個字符3個字節(jié)；

因此，key_len長度的計算公式

? varchr(10)變長字段且允許NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL標記位)+2(變長字段)
? varchr(10)變長字段且不允許NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+2(變長字段)
? char(10)固定字段且允許NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL標記位)
? char(10)固定字段且不允許NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)

附，關(guān)于 filesort 排序算法：
光看 filesort 字面意思，可能以為是要利用磁盤文件進行排序，實則不全然。
當MySQL不能使用索引進行排序時，就會利用自己的排序算法(快速排序算法)在內(nèi)存(sort buffer)中對數(shù)據(jù)進行排序，如果內(nèi)存裝載不下，它會將磁盤上的數(shù)據(jù)進行分塊，再對各個數(shù)據(jù)塊進行排序，然后將各個塊合并成有序的結(jié)果集（實際上就是外排序）。

對于filesort，MySQL有兩種排序算法：
1、兩遍掃描算法(Two passes)
實現(xiàn)方式是先將須要排序的字段和可以直接定位到相關(guān)行數(shù)據(jù)的指針信息取出，然后在設(shè)定的內(nèi)存（通過參數(shù) sort_buffer_size 設(shè)定）中進行排序，完成排序之后再次通過行指針信息取出所需的列。
注：該算法是4.1之前只有這種算法，它需要兩次訪問數(shù)據(jù)，尤其是第二次讀取操作會導致大量的隨機I/O操作。不過，這種方法內(nèi)存開銷較小。

2、一次掃描算法(single pass)
該算法一次性將所需的列全部取出，在內(nèi)存中排序后直接將結(jié)果輸出。

注：從 MySQL 4.1 版本開始支持該算法。它減少了I/O的次數(shù)，效率較高，但是內(nèi)存開銷也較大。如果我們將并不需要的列也取出來，就會極大地浪費排序過程所需要的內(nèi)存。在 MySQL 4.1 之后的版本中，可以通過設(shè)置 max_length_for_sort_data 參數(shù)來控制 MySQL 選擇第一種排序算法還是第二種。當取出的所有大字段總大小大于max_length_for_sort_data 的設(shè)置時，MySQL 就會選擇使用第一種排序算法，反之，則會選擇第二種。為了盡可能地提高排序性能，我們自然更希望使用第二種排序算法，所以在SQL中僅僅取出需要的列是非常有必要的。

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

后面是幾個幾個測試結(jié)果，有興趣不怕累的可以看看，哈哈。

測試MySQL版本：5.5.37-log MySQL Community Server (GPL)

創(chuàng)建一個測試表，id是主鍵字段，(a1, a2) 組成聯(lián)合索引

(yejr@imysql.com)> show create table t\G
*************************** 1\. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`a1` int(10) unsigned NOT NULL DEFAULT '0',
`a2` int(10) unsigned NOT NULL DEFAULT '0',
`aa` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx` (`a1`,`a2`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8

填充了64條測試數(shù)據(jù)

(yejr@imysql.com)> show table status like 't'\G
*************************** 1\. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 64
Avg_row_length: 256
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 122
Create_time: 2014-09-15 17:17:09
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:

對 a1、a2 正序排序，同時取a1、a2兩個字段，可以直接使用該聯(lián)合索引取回結(jié)果，并且排序完成

符合規(guī)則c

(yejr@imysql.com)> explain select a1, a2 from t order by a1, a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

對 a1、a2 倒序排序，同時取a1、a2兩個字段，可以直接使用該聯(lián)合索引取回結(jié)果，并且排序完成
由于同時對a1、a2都是倒序排序，因此完全可以用到索引的順序，只是反向掃描而已
符合規(guī)則c

(yejr@imysql.com)> explain select a1, a2 from t order by a1 desc, a2 desc\G
*************************** 1\. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

對 a1、a2正序排序，只取a1字段，可以直接使用該聯(lián)合索引取回結(jié)果，并且排序完成
匹配規(guī)則c

(yejr@imysql.com)> explain select a1 from t order by a1, a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

對 a1、a2 正序排序，只取a2字段，可以直接使用該聯(lián)合索引取回結(jié)果，并且排序完成
符合規(guī)則c

(yejr@imysql.com)> explain select a2 from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

只對 a1 正序排序，同時取a1、a2兩個字段，可以直接使用該聯(lián)合索引取回結(jié)果，并且排序完成
符合規(guī)則c

(yejr@imysql.com)> explain select a1, a2 from t order by a1\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

對 a1 正序排序，對 a2 倒序排序，只取a1字段，可以直接使用該聯(lián)合索引取回結(jié)果，但排序時需要進行filesort排序，不能利用索引直接得到排序結(jié)果
這時雖然只讀取一個字段，但實際還是掃描了整個索引，并非使用前綴索引
符合規(guī)則c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

只取a1字段，同時只對 a1 字段正序排序，這時可用聯(lián)合索引取得結(jié)果，同時也可以利用前綴索引的原則進行排序
符合規(guī)則c

(yejr@imysql.com)> explain select a1 from t order by a1\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

只取a1字段，同時只對 a2 字段正序排序，這時雖然可用聯(lián)合索引取得結(jié)果，但排序時需要進行filesort排序，不能利用索引直接得到排序結(jié)果
符合規(guī)則c、f

(yejr@imysql.com)> explain select a1 from t order by a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

對 a1 正序排序，對a2 倒序排序，只取a1字段，可以直接使用該聯(lián)合索引取回結(jié)果，但排序時需要進行filesort排序，不能利用索引直接得到排序結(jié)果
這時雖然只讀取一個字段，但實際還是掃描了整個索引，并非使用前綴索引
符合規(guī)則c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

對 a1 正序排序，對a2 倒序排序，只取a2字段，可以直接使用該聯(lián)合索引取回結(jié)果，但排序時需要進行filesort排序，不能利用索引直接得到排序結(jié)果
這時雖然只讀取一個字段，但實際還是掃描了整個索引，并非使用前綴索引
符合規(guī)則c、f

(yejr@imysql.com)> explain select a2 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

對 a1 正序排序，對a2 倒序排序，只取a2字段，可以直接使用該聯(lián)合索引取回結(jié)果，但排序時需要進行filesort排序，不能利用索引直接得到排序結(jié)果
這時雖然只讀取一個字段，但實際還是掃描了整個索引，并非使用前綴索引
符合規(guī)則c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

對 a1 、a2順序排序，取得主鍵id字段，可以直接使用該聯(lián)合索引取回結(jié)果并完成排序。
這里需要注意下，二級索引其實是包括主鍵索引的，因此用idx索引即可取到全部結(jié)果。
下面這個SQL也是一樣的效果：select a1,a2,id from t order by a1, a2;
符合規(guī)則c、h

(yejr@imysql.com)> explain select id from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

對 a1 正序排序，對a2 倒序排序，取得主鍵id字段，可以直接使用該聯(lián)合索引取回結(jié)果，但需要進行filesort排序。
符合規(guī)則c、f、h

(yejr@imysql.com)> explain select id from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

對 a1 倒序排序，對a2 正序排序，取得主鍵id字段，可以直接使用該聯(lián)合索引取回結(jié)果，但需要進行filesort排序。
符合規(guī)則c、f、h

(yejr@imysql.com)> explain select id from t order by a1 desc, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

過濾條件a1字段（使用前綴索引掃描，key_len為4），對a2字段進行正序排序，取得主鍵id字段，可以直接使用聯(lián)合索引取回結(jié)果
符合規(guī)則a、c、d、h

(yejr@imysql.com)> explain select id from t where a1 = 219 order by a2\G
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: idx
key: idx
key_len: 4
ref: const
rows: 2
Extra: Using where; Using index