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

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

先來(lái)了解下MySQL關(guān)于索引的一些基礎(chǔ)知識(shí)要點(diǎn)：

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

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

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

因此，key_len長(zhǎng)度的計(jì)算公式

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

附，關(guān)于 filesort 排序算法：
光看 filesort 字面意思，可能以為是要利用磁盤文件進(jìn)行排序，實(shí)則不全然。
當(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ò)行指針信息取出所需的列。
注：該算法是4.1之前只有這種算法，它需要兩次訪問(wèn)數(shù)據(jù)，尤其是第二次讀取操作會(huì)導(dǎo)致大量的隨機(jī)I/O操作。不過(guò)，這種方法內(nèi)存開(kāi)銷較小。

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

注：從 MySQL 4.1 版本開(kāi)始支持該算法。它減少了I/O的次數(shù)，效率較高，但是內(nèi)存開(kāi)銷也較大。如果我們將并不需要的列也取出來(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ì)選擇第二種。為了盡可能地提高排序性能，我們自然更希望使用第二種排序算法，所以在SQL中僅僅取出需要的列是非常有必要的。

當(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”。

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

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

創(chuàng)建一個(gè)測(cè)試表，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條測(cè)試數(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:

對(duì) a1、a2 正序排序，同時(shí)取a1、a2兩個(gè)字段，可以直接使用該聯(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

對(duì) a1、a2 倒序排序，同時(shí)取a1、a2兩個(gè)字段，可以直接使用該聯(lián)合索引取回結(jié)果，并且排序完成
由于同時(shí)對(duì)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

對(duì) 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

對(duì) 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

只對(duì) a1 正序排序，同時(shí)取a1、a2兩個(gè)字段，可以直接使用該聯(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

對(duì) a1 正序排序，對(duì) a2 倒序排序，只取a1字段，可以直接使用該聯(lián)合索引取回結(jié)果，但排序時(shí)需要進(jìn)行filesort排序，不能利用索引直接得到排序結(jié)果
這時(shí)雖然只讀取一個(gè)字段，但實(shí)際還是掃描了整個(gè)索引，并非使用前綴索引
符合規(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字段，同時(shí)只對(duì) a1 字段正序排序，這時(shí)可用聯(lián)合索引取得結(jié)果，同時(shí)也可以利用前綴索引的原則進(jìn)行排序
符合規(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字段，同時(shí)只對(duì) a2 字段正序排序，這時(shí)雖然可用聯(lián)合索引取得結(jié)果，但排序時(shí)需要進(jìn)行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

對(duì) a1 正序排序，對(duì)a2 倒序排序，只取a1字段，可以直接使用該聯(lián)合索引取回結(jié)果，但排序時(shí)需要進(jìn)行filesort排序，不能利用索引直接得到排序結(jié)果
這時(shí)雖然只讀取一個(gè)字段，但實(shí)際還是掃描了整個(gè)索引，并非使用前綴索引
符合規(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

對(duì) a1 正序排序，對(duì)a2 倒序排序，只取a2字段，可以直接使用該聯(lián)合索引取回結(jié)果，但排序時(shí)需要進(jìn)行filesort排序，不能利用索引直接得到排序結(jié)果
這時(shí)雖然只讀取一個(gè)字段，但實(shí)際還是掃描了整個(gè)索引，并非使用前綴索引
符合規(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

對(duì) a1 正序排序，對(duì)a2 倒序排序，只取a2字段，可以直接使用該聯(lián)合索引取回結(jié)果，但排序時(shí)需要進(jìn)行filesort排序，不能利用索引直接得到排序結(jié)果
這時(shí)雖然只讀取一個(gè)字段，但實(shí)際還是掃描了整個(gè)索引，并非使用前綴索引
符合規(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

對(duì) a1 、a2順序排序，取得主鍵id字段，可以直接使用該聯(lián)合索引取回結(jié)果并完成排序。
這里需要注意下，二級(jí)索引其實(shí)是包括主鍵索引的，因此用idx索引即可取到全部結(jié)果。
下面這個(gè)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

對(duì) a1 正序排序，對(duì)a2 倒序排序，取得主鍵id字段，可以直接使用該聯(lián)合索引取回結(jié)果，但需要進(jìn)行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

對(duì) a1 倒序排序，對(duì)a2 正序排序，取得主鍵id字段，可以直接使用該聯(lián)合索引取回結(jié)果，但需要進(jìn)行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

過(guò)濾條件a1字段（使用前綴索引掃描，key_len為4），對(duì)a2字段進(jìn)行正序排序，取得主鍵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