2019獨角獸企業重金招聘Python工程師標準>>>

MySQL從8.0.13版本開始支持一種新的range scan方式，稱為Loose Skip Scan。該特性由Facebook貢獻。我們知道在之前的版本中，如果要使用到索引進行掃描，條件必須滿足索引前綴列，比如索引idx(col1,col2), 如果where條件只包含col2的話，是無法有效的使用idx的, 它需要掃描索引上所有的行，然后再根據col2上的條件過濾。

新的優化可以避免全量索引掃描，而是根據每個col1上的值+col2上的條件，啟動多次range scan。每次range scan根據構建的key值直接在索引上定位，直接忽略了那些不滿足條件的記錄。

示例

下例是從官方文檔上摘取的例子:

root@test 11:03:28>CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2)); Query OK, 0 rows affected (0.00 sec)root@test 11:03:29>INSERT INTO t1 VALUES-> (1,1), (1,2), (1,3), (1,4), (1,5),-> (2,1), (2,2), (2,3), (2,4), (2,5); Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0root@test 11:03:29>INSERT INTO t1 SELECT f1, f2 + 5 FROM t1; Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0root@test 11:03:29>INSERT INTO t1 SELECT f1, f2 + 10 FROM t1; Query OK, 20 rows affected (0.00 sec) Records: 20 Duplicates: 0 Warnings: 0root@test 11:03:29>INSERT INTO t1 SELECT f1, f2 + 20 FROM t1; Query OK, 40 rows affected (0.00 sec) Records: 40 Duplicates: 0 Warnings: 0root@test 11:03:29>INSERT INTO t1 SELECT f1, f2 + 40 FROM t1; Query OK, 80 rows affected (0.00 sec) Records: 80 Duplicates: 0 Warnings: 0root@test 11:03:29>ANALYZE TABLE t1; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t1 | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.00 sec)root@test 11:03:29>EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+ | 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 53 | 100.00 | Using where; Using index for skip scan | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+ 1 row in set, 1 warning (0.00 sec)

也可以從optimizer trace里看到如何選擇的skip scan:

"skip_scan_range": {"potential_skip_scan_indexes": [{"index": "PRIMARY","tree_travel_cost": 0.4,"num_groups": 3,"rows": 53,"cost": 10.625}]},"best_skip_scan_summary": {"type": "skip_scan","index": "PRIMARY","key_parts_used_for_access": ["f1","f2"],"range": ["40 < f2"],"chosen": true},

我們從innodb的角度來看看這個SQL是如何執行的，我們知道每個index scan都會走到ha_innobase::index_read來構建search tuple，上述查詢的執行步驟：

• 第一次從Index left side開始scan
• 第二次使用key(1,40) 掃描index，直到第一個range結束
• 使用key(1), find_flag =HA_READ_AFTER_KEY, 找到下一個Key值2
• 使用key(2,40)，掃描Index， 直到range結束
• 使用Key(2)，去找大于2的key值，上例中沒有，因此結束掃描

筆者在代碼注入了日志，打印search_tuple(dtuple_print())

STEP 1: no search_tupleSTEP 2: DATA TUPLE: 2 fields;0: len 4; hex 80000001; asc ;;1: len 4; hex 80000028; asc (;;STEP 3: DATA TUPLE: 1 fields;0: len 4; hex 80000001; asc ;;STEP 4: DATA TUPLE: 2 fields;0: len 4; hex 80000002; asc ;;1: len 4; hex 80000028; asc (;;STEP 5: DATA TUPLE: 1 fields;0: len 4; hex 80000002; asc ;;

從上述描述可以看到使用skip-scan的方式避免了全索引掃描，從而提升了性能，尤其是在索引前綴列區分度比較低的時候

條件

skip scan可以通過Hint或者optimizer_switch來控制(skip_scan)，默認是打開的。根據worklog的描述，對于如下query:

SELECT A_1,...,A_k, B_1,...,B_m, CFROM TWHEREEQ(A_1,...,A_k)AND RNG(C);

需要滿足如下條件才能使用skip scan:

A) Table T has at least one compound index I of the form:I = <A_1,...,A_k, B_1,..., B_m, C ,[D_1,...,D_n]>Key parts A and D may be empty, but B and C must be non-empty. B) Only one table referenced. C) Cannot have group by/select distinct D) Query must reference fields in the index only. E) The predicates on A_1...A_k must be equality predicates and they needto be constants. This includes the 'IN' operator. F) The query must be a conjunctive query.In other words, it is a AND of ORs:(COND1(kp1) OR COND2(kp1)) AND (COND1(kp2) OR ...) AND ... G) There must be a range condition on C. H) Conditions on D columns are allowed. Conditions on D must be inconjunction with range condition on C.

ref:?get_best_skip_scan()

當skip scan擁有更低的cost時，會被選擇，計算cost的函數是cost_skip_scan()，由于索引統計信息中已經基于不同的前綴列值估算了distinct value的個數(rec_per_key), 可以基于此去預估可能需要讀的行數。 更具體的可以參考wl#11322中的描述，筆者對此不甚了解，故不做筆墨
ref:?cost_skip_scan()

?

原文鏈接

本文為云棲社區原創內容，未經允許不得轉載。

轉載于:https://my.oschina.net/u/3889140/blog/3041868

總結

以上是生活随笔為你收集整理的干货 | 解读MySQL 8.0新特性：Skip Scan Range的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。