MySQL8.0-新特性-DescendingIndex

zhaiwx_yinfeng發表於2019-01-06
MySqlIndex

前言

在MySQL8.0之前的版本中，innodb btree索引中的記錄都是嚴格按照的key的順序來儲存的，但有些時候當我們需要倒序掃描時，效率就會很低。為了解決這個問題，從MySQL8.0版本開始支援在索引Key中倒序儲存。你可以按照實際的sql負載來決定如何建立索引，例如你的查詢中有Order by a desc, b asc，就可以建立索引key(a desc, b asc)，而在8.0之前的版本中則可能需要代價比較大的filesort來進行, 此外逆序掃描Btree也有額外的開銷，例如掃描時的page切換，page內掃描，都比正序掃描的開銷要大。

本文簡單介紹下用法，並分析下對應的程式碼實現

以下基於當前最新MySQL8.0.13版本

使用

其實對應的語法一直是存在的，只是沒有做具體的實現，直到8.0版本才真正實現，使用也很簡單，在建立索引時，對索引列加asc/desc關鍵字，舉個簡單的例子：

mysql> CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY a_idx(a DESC, b ASC));
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t1 VALUES(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT b FROM t1 FORCE INDEX(a_idx);
+------+
| b    |
+------+
|    3 |
|    2 |
|    1 |
+------+
3 rows in set (0.00 sec)

mysql> SELECT b FROM t1 FORCE INDEX(PRIMARY);
+------+
| b    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

如上例，可以看到指定不同的索引給出的結果順序也是不一樣的。

mysql> EXPLAIN SELECT * FROM t1 ORDER BY a DESC, b;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | a_idx | 9       | NULL |    3 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

上例中可以看到explain的結果中沒有filesort, 而在之前的版本中對於這樣的sql是需要進行排序的。

優化器在選擇索引時也會考慮到索引列的順序，目前還有些條件限制：

  • 由於涉及到資料的儲存，目前只支援InnoDB
  • Descending index 無法使用change buffer
  • Descneding index不支援fulltext或spatial index, 選擇desc關鍵字會報錯
  • GROUP BY不在隱式的保證順序性，只有明確的指定asc/desc，才去確保順序

實現

筆者主要工作是在innodb引擎，對server層不甚瞭解，本文也主要關注innodb的改動。實際上這個特性的改動主要在server層的優化器和執行器，對於innodb來說，儘管資料儲存發生了變化，但改動反而很少。

資料詞典：
索引上的列屬性被持久化到資料詞典表(dd::Index)

dd::fill_dd_indexes_from_keyinfo
    dd::fill_dd_index_elements_from_key_parts

key_rec_cmp:
比較的兩個key不是大小關係，而是在索引上的前後關係，因此需要考慮鍵值列上是asc還是desc的
對於range查詢，在之前的版本中總是min_Key被傳到innodb作為search_tuple來定位btree，但如果是descending index，則需要選擇max_key來作為search tuple (ref: SEL_ARG::get_min_flag(), SEL_ARG::get_max_flag(), SEL_ROOT::store_min_key)

InnoDB record compare:
為了支援這個特性,innodb的改動實際上並不大，大部分程式碼都是沒有變化的，這主要是因為InnoDB使用了統一的比較函式來決定key值位置，索引物件傳遞到底層的比較函式中，以獲取是否存在descending column.

相關函式：

cmp_dtuple_rec_with_match_low
cmp_whole_field
cmp_data

判斷是否是descending index:
dict_index_has_desc(): 這個函式會掃描索引上所有的列，確保沒有desc column, 這個函式看起來有點效率問題，我們可以給dict_index_t加個flag來判斷，無需每次遍歷

參考文件：

1.官方文件
2. wl#1074: Add Descending indexes support
3.MySQL 8.0 Labs – Descending Indexes in MySQL
4.MySQL 8.0: Descending Indexes Can Speed Up Your Queries
5.相關程式碼


相關文章