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

創(chuàng)新互聯(lián)公司是一家專業(yè)提供奉化企業(yè)網(wǎng)站建設(shè),專注與網(wǎng)站制作、成都網(wǎng)站建設(shè)、H5建站、小程序制作等業(yè)務(wù)。10年已為奉化眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站制作公司優(yōu)惠進(jìn)行中。

先來(lái)個(gè)示例：

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)只能是一個(gè)表

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

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

(5)默認(rèn)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ā)方法就先給大家講到這里，對(duì)于其它相關(guān)問題大家想要了解的可以持續(xù)關(guān)注我們的行業(yè)資訊。我們的板塊內(nèi)容每天都會(huì)捕捉一些行業(yè)新聞及專業(yè)知識(shí)分享給大家的。