先来个示例：
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)
参考链接
