> 篇首語： > 截止到目前的5.7.4版本為止，MySQL的聯合索引仍無法支持聯合索引使用不同排序規則，例如：ALTER TABLE t ADD INDEX idx(col1, col2 DESC)。 先來了解下MySQL關于索引的一些基礎知識要點： ~~~ ? a、EXPLAIN結果中的key_len只顯示了條件檢索子句需要的索引長度，但 ORDER BY、GROUP BY 子句用到的索引則不計入 key_len 統計值； ? b、聯合索引(composite index)：多個字段組成的索引，稱為聯合索引； 例如：ALTER TABLE t ADD INDEX `idx` (col1, col2, col3) ? c、覆蓋索引(covering index)：如果查詢需要讀取到索引中的一個或多個字段，則可以從索引樹中直接取得結果集，稱為覆蓋索引； 例如：SELECT col1, col2 FROM t; ? d、最左原則(prefix index)：如果查詢條件檢索時，只需要匹配聯合索引中的最左順序一個或多個字段，稱為最左索引原則，或者叫最左前綴； 例如：SELECT * FROM t WHERE col1 = ? AND col2 = ?; ? e、在老版本（大概是5.5以前，具體版本號未確認核實）中，查詢使用聯合索引時，可以不區分條件中的字段順序，在這以前是需要按照聯合索引的創建順序書寫SQL條件子句的； 例如：SELECT * FROM t WHERE col3 = ? AND col1 = ? AND col2 = ?; ? f、MySQL截止目前還只支持多個字段都是正序索引，不支個別字段持倒序索引； 例如：ALTER TABLE t ADD INDEX `idx` (col1, col2, col3 DESC)，這里的DESC只是個預留的關鍵字，目前還不能真正有作用 ? g、聯合索引中，如果查詢條件中最左邊某個索引列使用范圍查找，則只能使用前綴索引，無法使用到整個索引； 例如：SELECT * FROM t WHERE col1 = ? AND col2 >= ? AND col3 = ?; 這時候，只能用到 idx 索引的最左2列進行檢索，而col3條件則無法利用索引進行檢索 ? h、InnoDB引擎中，二級索引實際上包含了主鍵索引值； ~~~ 關于 key_len 的計算規則： ~~~ ? 當索引字段為定長數據類型，比如：char，int，datetime，需要有是否為空的標記，這個標記需要占用1個字節； ? 當索引字段為變長數據類型，比如：varchar，除了是否為空的標記外，還需要有長度信息，需要占用2個字節； ? 當字段定義為非空的時候，是否為空的標記將不占用字節； ? 同時還需要考慮表所使用字符集的差異，latin1編碼一個字符1個字節，gbk編碼一個字符2個字節，utf8編碼一個字符3個字節； ~~~ 因此，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) ~~~ 附，關于?filesort?排序算法： 光看 filesort 字面意思，可能以為是要利用磁盤文件進行排序，實則不全然。 當MySQL不能使用索引進行排序時，就會利用自己的排序算法(快速排序算法)在內存(sort buffer)中對數據進行排序，如果內存裝載不下，它會將磁盤上的數據進行分塊，再對各個數據塊進行排序，然后將各個塊合并成有序的結果集（實際上就是外排序）。 對于filesort，MySQL有兩種排序算法： 1、兩遍掃描算法(Two passes) 實現方式是先將須要排序的字段和可以直接定位到相關行數據的指針信息取出，然后在設定的內存（通過參數?sort_buffer_size?設定）中進行排序，完成排序之后再次通過行指針信息取出所需的列。 注：該算法是4.1之前只有這種算法，它需要兩次訪問數據，尤其是第二次讀取操作會導致大量的隨機I/O操作。不過，這種方法內存開銷較小。 2、一次掃描算法(single pass) 該算法一次性將所需的列全部取出，在內存中排序后直接將結果輸出。 注：從 MySQL 4.1 版本開始支持該算法。它減少了I/O的次數，效率較高，但是內存開銷也較大。如果我們將并不需要的列也取出來，就會極大地浪費排序過程所需要的內存。在 MySQL 4.1 之后的版本中，可以通過設置?max_length_for_sort_data?參數來控制 MySQL 選擇第一種排序算法還是第二種。當取出的所有大字段總大小大于max_length_for_sort_data?的設置時，MySQL 就會選擇使用第一種排序算法，反之，則會選擇第二種。為了盡可能地提高排序性能，我們自然更希望使用第二種排序算法，所以在SQL中僅僅取出需要的列是非常有必要的。 當對連接操作進行排序時，如果ORDER BY僅僅引用第一個表的列，MySQL對該表進行filesort操作，然后進行連接處理，此時，EXPLAIN輸出“Using filesort”；否則，MySQL必須將查詢的結果集生成一個臨時表，在連接完成之后進行filesort操作，此時，EXPLAIN輸出“Using temporary;Using filesort”。 后面是幾個幾個測試結果，有興趣不怕累的可以看看，哈哈。 測試MySQL版本：5.5.37-log MySQL Community Server (GPL) 創建一個測試表，id是主鍵字段，(a1, a2) 組成聯合索引 ~~~ (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條測試數據 ~~~ (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兩個字段，可以直接使用該聯合索引取回結果，并且排序完成 #符合規則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兩個字段，可以直接使用該聯合索引取回結果，并且排序完成 由于同時對a1、a2都是倒序排序，因此完全可以用到索引的順序，只是反向掃描而已 符合規則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字段，可以直接使用該聯合索引取回結果，并且排序完成 匹配規則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字段，可以直接使用該聯合索引取回結果，并且排序完成 符合規則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兩個字段，可以直接使用該聯合索引取回結果，并且排序完成 符合規則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字段，可以直接使用該聯合索引取回結果，但排序時需要進行filesort排序，不能利用索引直接得到排序結果 這時雖然只讀取一個字段，但實際還是掃描了整個索引，并非使用前綴索引 符合規則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 字段正序排序，這時可用聯合索引取得結果，同時也可以利用前綴索引的原則進行排序 符合規則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 字段正序排序，這時雖然可用聯合索引取得結果，但排序時需要進行filesort排序，不能利用索引直接得到排序結果 符合規則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字段，可以直接使用該聯合索引取回結果，但排序時需要進行filesort排序，不能利用索引直接得到排序結果 這時雖然只讀取一個字段，但實際還是掃描了整個索引，并非使用前綴索引 符合規則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字段，可以直接使用該聯合索引取回結果，但排序時需要進行filesort排序，不能利用索引直接得到排序結果 這時雖然只讀取一個字段，但實際還是掃描了整個索引，并非使用前綴索引 符合規則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字段，可以直接使用該聯合索引取回結果，但排序時需要進行filesort排序，不能利用索引直接得到排序結果 這時雖然只讀取一個字段，但實際還是掃描了整個索引，并非使用前綴索引 符合規則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字段，可以直接使用該聯合索引取回結果并完成排序。 這里需要注意下，二級索引其實是包括主鍵索引的，因此用idx索引即可取到全部結果。 下面這個SQL也是一樣的效果：select a1,a2,id from t order by a1, a2; 符合規則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字段，可以直接使用該聯合索引取回結果，但需要進行filesort排序。 符合規則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字段，可以直接使用該聯合索引取回結果，但需要進行filesort排序。 符合規則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字段，可以直接使用聯合索引取回結果 符合規則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 ~~~