Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief talk about MySQL 8.0 skip scan successful trigger method, related technical terms you can check online or find some related books to supplement, here is not involved, we will go straight to the topic, I hope MySQL 8.0 skip scan successful trigger method This article can bring you some practical help.

Let's start with an example:

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)

The above skip scan process:

(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 trigger condition

(1)Must be a federated index

(2)It can only be a table.

(3)You cannot use distinct or group by ;

(4)SQL cannot return to the table, that is, select columns and where condition columns must be included in an index.

(5)default 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 success trigger method will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section content captures some industry news and expertise to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.