MySQL 8.0中skip scan成功触发方法

本文主要给大家简单讲讲MySQL 8.0中skip scan成功触发方法，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望MySQL 8.0中skip scan成功触发方法这篇文章可以给大家带来一些实际帮助。

先来个示例：

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触发条件

(1)必须是联合索引

(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成功触发方法就先给大家讲到这里，对于其它相关问题大家想要了解的可以持续关注我们的行业资讯。我们的板块内容每天都会捕捉一些行业新闻及专业知识分享给大家的。