MySQL 8.0中skip scan成功觸發(fā)方法

本文主要給大家簡單講講MySQL 8.0中skip scan成功觸發(fā)方法，相關(guān)專業(yè)術(shù)語大家可以上網(wǎng)查查或者找一些相關(guān)書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL 8.0中skip scan成功觸發(fā)方法這篇文章可以給大家?guī)硪恍嶋H幫助。

先來個示例：

mysql> CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
mysql> INSERT INTO t1 VALUES
    ->   (1,1), (1,2), (1,3), (1,4), (1,5),
    ->   (2,1), (2,2), (2,3), (2,4), (2,5);
mysql> INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
mysql> INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
mysql> INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
mysql> INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;

mysql> desc 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)

上述skip scan過程：

(1)Get the first distinct value of the first key part (f1 = 1).

(2)Construct the range based on the first and second key parts (f1 = 1 AND f2 > 40).

(3)Perform a range scan.

(4)Get the next distinct value of the first key part (f1 = 2).

(5)Construct the range based on the first and second key parts (f1 = 2 AND f2 > 40).

(6)Perform a range scan.

skip scan觸發(fā)條件

(1)必須是聯(lián)合索引

(2)只能是一個表

(3)不能使用distinct或group by ;

(4)SQL不能回表，即select列和where條件列都要包含在一個索引中

(5)默認optimizer_switch='skip_scan=on'開啟；

mysql> desc SELECT distinct 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       | index | PRIMARY       | PRIMARY | 8       | NULL |  160 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc SELECT /*+ set_var(optimizer_switch='skip_scan=off') */ 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       | index | NULL          | PRIMARY | 8       | NULL |  160 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select dept_no,emp_no from dept_emp2 where emp_no>30000;
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key         | key_len | ref  | rows   | filtered | Extra                                  |
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------------+
|  1 | SIMPLE      | dept_emp2 | NULL       | range | ix_dept_emp   | ix_dept_emp | 16      | NULL | 110324 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select dept_no,emp_no,to_date from dept_emp2 where emp_no>30000;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | dept_emp2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 331008 |    33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

MySQL 8.0中skip scan成功觸發(fā)方法就先給大家講到這里，對于其它相關(guān)問題大家想要了解的可以持續(xù)關(guān)注我們的行業(yè)資訊。我們的板塊內(nèi)容每天都會捕捉一些行業(yè)新聞及專業(yè)知識分享給大家的。