MySQL8.0新特性~最左前缀匹配原则被打破了
测试
在MySQL8.0.25和mysql5.7.33中创建如下
CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
(1,1), (1,2), (1,3), (1,4), (1,5),
(2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;
在mysql8.0.25中执行
mysql> EXPLAIN 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)
在mysql5.7.33中执行
mysql> EXPLAIN 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 | index | NULL | PRIMARY | 8 | NULL | 160 | 33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
可以看到在8.0版本中使用了range的扫描方式
Using where; Using index for skip scan
原理
在MySQL-8.0.13版本增加的跳跃范围扫描特性，就是针对类似的场景的优化，跳跃范围扫描在这个示例中实际是针对每一个f1字段的值，进行了范围扫描，即进行了多次范围扫描。
针对这个示例，具体的跳跃范围扫描过程如下：
1.获取联合索引中第一个字段f1的第一个值：f1 = 1
2.将获取到的值和WHERE条件中的f2的条件组合：f1 = 1 AND f2 > 40
3.执行这个范围扫描查询
4.获取联合索引中第一个字段f1的第二个值：f1 = 2
5.将获取到的值和WHERE条件中的f2的条件组合：f1 = 2 AND f2 > 40
6.执行这个范围扫描查询
7.将两次范围扫描查询的结果合并返回给客户端
总结起来就是，把所有前置索引的值进行了穷举，再把每一个值都当做一个条件，拼成一个sql，再把这些sql union起来。
限制以及场景
-
表上至少存在一个联合索引([A_1,A_2...A_k],B_1,B_2...B_m,C,[,D_1,...,D_n])，其中A部分以及D部分可以为空，但是B和C部分不能为空。A_1,A_2..等代表字段值 -
只针对单表查询 -
查询中不包含GROUP BY或者DISTINCT -
SELECT查询的字段全部被包含在索引组成部分，即符合覆盖索引规范 -
前缀A_1,A_2...A_k部分必须是可以被相等的常量 -
字段C上必须是一个范围条件，大于或大于等于，小于或小于等于 -
允许在D字段上有过滤条件，但是必须和C上的范围条件一起使用
跳跃范围扫描适用于联合索引中前导列distinct值较少，后续字段选择过滤性又比较好的场景，能更好的发挥跳跃范围扫描的作用。
开启或关闭
跳跃范围扫描默认是开启的，有两种方式可以关闭跳跃范围扫描特性：
通过修改optimizer_switcher变量值，默认MySQL是将optimizer_switcher中的skip_scan设置为on的，可以通过将skip_scan设置为off关闭跳跃范围扫描
通过Hint的方式关闭跳跃范围扫描特性：SELECT/*+ NO_SKIP_SCAN(t1 PRIMARY) */ f1, f2 FROM t1 WHERE f2 > 40;
原文地址：https://blog.csdn.net/DBA_ChenJR/article/details/140610784
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！